SQL Server additions and deletions change operation

Author: GW Load Please specify the source

--------------------------------------

I. Inserting data

(1) inserting a single tuple

INSERT into < table name >[(< column name 1>[,< column name 2> ...)]

VALUES (< value 1>[,< value 2>].);

(2) Inserting sub-query results

INSERT into < table name >[(< column name 1>[,< column name 2> ...)]

Sub-query;

-------------------------------------

--View the details of a table if you don't know the structure and constraints of the table beforehand, you can view it through Sp_hele.

Sp_help SC;

--Inserting a single piece of data

--Insert attention to data type matching in order and to meet constraints

INSERT into student values (' 2014019 ', ' d ' ma ', ' Male ', ' 20 ', ' 005 ');

INSERT into student (Sno,sname) VALUES (' 2014020 ', ' Prince ');

Insert into course values (' 009 ', ' Test class ', null,4);

--Import multiple data at once

- -Create a table

CREATE TABLE Test (Sno char (9) Primary Key,sname char (10));

--Import names and school numbers from the student table into the test table

INSERT into Test (sno,sname) select Sno,sname from student;

--View Results

SELECT * from Test;

Two. Updating data

--Grammar

UPDATE < table name >

Set < column name > = < expression >[,< Column name > = < expression;] ... [WHERE < conditions >];

Note

(1) The expression can be a specific value, calculation result, sub-query;

(2) Omit where to modify all tuples;

(3) Pay attention to ensure the consistency of data.

--View the data in the student table

SELECT * from student;

--It's a little bit of a problem when you enter this watch.

--fuck!! When did Xiahouyuan become a woman?

Update student set ssex= ' man ' where sno= ' 2014003 ';

--in fact, modifying the data in the table can also remove the data before adding

Three. Delete data

--Grammar

DELETE from < table name > [WHERE < Condition >];

Note

(1) Omit where to delete all tuples in the table

(2) Delete only the data of the table, without deleting the definition of the table

(3) Ensure that data consistency is ensured

--Delete data

Delete from student where sno= ' 2014019 ';

---------------------------------------------------------------------------------------------------------------

For beginners, sometimes when inserting, deleting, updating, there will be some exceptions and the corresponding actions will fail.

Can be caused by some constraints, see the SQL Server Constraint section for a solution to these issues.

SQL Server additions and deletions change operation