0011 "SQL must know" note 07 inserting, updating, and deleting data

1. Insert full line or part: INSERT into table name (column name 1~n) values (corresponding value 1~n)

INSERT  into Products (prod_id,                    vend_id,                    prod_name,                    prod_price,                    prod_desc)VALUES('  RYL03',       'FNG01',        ' Monkey Doll ' ,        9.45 ,        null);

Note: 1. Omitting column names can also be inserted, but after a change in the future table structure, it can result in errors, so generally not used;

2. If you insert data for only a subset of columns, the omitted columns can either be null or have default values, or an error will occur;

3. The character type data should be enclosed in '.

2. Insert the data retrieved from select. For example, create new table dll01_products, insert vend_id=dll01 data (except PROD_DESC) into new table

INSERT  into dll01_products (new_prod_id,                           new_vend_id,                           new_prod_name,                           new_prod_price)SELECT prod_id,       vend_id,       prod_name,       prod_pricefrom Products  WHERE vend_id='DLL01';

Note: 1. The column names of the two tables are not related, just insert the nth column of select INTO column N of INSERT, of course the data type must be compatible

2. The inserted data and the original data cannot have duplicate data in a column with unique constraints

3. Copy a table (some or all columns)

CREATE TABLE  as SELECT  from the products;

Note: Use the test SQL statement to copy the data first, test the SQL statement, and then run it to the actual data to ensure security

4, modify the data: The Prod_id=rgan01 of the Custcopy table vend_id changed to fng01,prod_name changed to ' bean bag toy '. Special Note: If there is a WHERE clause, the entire column will be updated

UPDATE custcopy SET vend_id='FNG01',    prod_name=' Bean bag Toy ' WHERE prod_id='RGAN01';

5, delete data: Delete custcopy table in prod_id for ' RYL01 ', ' RYL02 ', ' RYL03 ' line. Special NOTE: The WHERE clause cannot be absent, otherwise all rows will be deleted.

DELETE  from custcopy WHERE inch ('RYL01','RYL02','RYL03' );

Note: 1. Deleted data can not be deleted if a foreign key is defined, such as prod_id in the OrderItems table.

2. If you want to delete all rows, TRUNCATE TABLE is more efficient than delete.

6, update and delete the use of the principle:

61 million there should be a WHERE clause;

6.2 Ensure that each table has a primary key, update and delete with the where and the primary key to filter;

6.3 Implement referential integrity to ensure that rows associated with other tables are not deleted;

6.4 Before update and delete, test with Select to ensure the data is correct;

6.5 DBAs should try to ensure that the system cannot execute update and delete without where.

0011 "SQL must know" note 07 inserting, updating, and deleting data