DDL, DML, and DCL (1-2. DML: Data Operations)

1. Add operation

A> insert a single row record

Format: insert into <Table Name> [(<column name 1> [, <column name 2>] [, <column name 3>])] values (<value>)

For example, insert a record ('s7 ', 'c1') in the SC (s_no, c_no) table ').

Insert into SC (s_no, c_no) values ('s7 ', 'c1 ')

B> insert multiple rows of records

Explanation:Add multi-row records for copying between tablesTo add data extraction rows from one table to another.

Format:Insert into <Table Name> [(<column name 1> [, <column name 2>] [, <column name 3>])] subquery 

Example: the existing tables are: Teacher Information table teacher_info ('t_ no', 't_ name', 't_ salary ', 't_ title ', 't_dept'), requires that the average salary of teachers of each department be obtained, and the result is stored in the new table averagesalary.

Create a table averagesalary: Create Table averagesalary (Department varchar (20), averge smallint)

Copy Data: insert into averagesalary select t_dept, AVG (t_salary) from teacher_info group by t_dept

 

2. modify data

A> Format: Update <Table Name> set <column name >=< expression> [, <column name >=< expression>]…… [Where <condition>]

B> explanation: the format of modifying a row is the same as that of modifying multiple rows.

C> example: increase the age of all students by 1. The table is student_info and the column name is age.

Update student_info set age = age + 1

3. delete data

A> delete records that are not repeated

Format: delete from <Table Name> [where <condition>]

Explanation: the format for deleting a row is the same as that for deleting multiple rows.

Example: Delete the record of instructor Liu Wei. The table is teacher_info and the column field is t_name.

Delete from teacher_info where t_name = 'Liu wei'

Delete all instructor records. The table is teacher_info.

Delete from teacher_info

B>Delete duplicate records

Delete Table1 where id_1 in (select id_1 from Table1 group by id_1 having count (*)> 1)

Note: Table1 is the table name and id_1 is the field in the table.

C> Delete the same record (keep one record)

Select distinct id_1, name into studenttemp from table_1 -- create a temporary table studenttemp
Delete Table1 where id_1 in (select id_1 from Table1 group by id_1 having count (*)> 1)-- Delete all duplicate records in the base table
Insert into table_1 select * From studenttemp -- insert data from the temporary table to the base table
Drop table studenttemp -- delete a temporary table
Go