insert data
Insert a single tuple
General format:
insert into <Table name> [<Column name 1>, <List 2>, ......] -- Indicates the column of the newly inserted value in the table, values(<Constant 1>, <Constant 2>, ......) -- Indicates the specific value of the column in which the new value is inserted in the table
- Each constant data type in the values statement must be compatible with the data type of the corresponding column in the into clause, and the number of constants must be the same as the number of columns in the into clause.
- For columns that do not appear in the into clause, newly inserted tuples have null values on these columns.
- If the into clause omits the column specifying the insertion of new values, each column of the newly inserted tuple must have a value corresponding in the values clause.
- If a column is defined as not null, its value must appear in the list of constants in the values clause.
- This method can only insert one row of data at a time.
-
Chestnut 1: insert a new book tuple into the book table.
insert into books values('A019', 'database', 'Wang Shan', 'Higher Education Press', 33.8)
In this example, the column name list in the into clause is omitted, so the data type and number of constants in the values clause must match the data type and number of columns in the book basic table.
-
Chestnut 2: insert a borrowing record ('140128 ',' a002 ').
insert into Borrow (Reader number, Book number) values ('140128', 'A002')
It can also be written as:
insert into Borrow values ('140128', 'A002', NULL, NULL)
Insert subquery results
Instead of the values clause, you can insert multiple tuples into the basic table at one time using the results of the query in the select statement.
The general format is:
insert into <Table name> [<Column name 1>, <List 2>, ......] <Subquery>
- The number of columns, column order and data type in the < subquery > resu lt set must match or be compatible with the corresponding items in the basic table referred to by < table name >.
Chestnut: find the total number of books of each publishing house and store them in a new table CBS.
-
Table creation:
create table CBS (press varchar(15), total int)
-
Insert:
insert into CSB (press, total) select press, count(*) from books group by press
Modify data
General format:
update <Table name> set <Listing> = <expression> [, <Listing> = <expression>]...... [where <condition>] -- If this clause is omitted, all tuples in the base table are modified
Modify the value of a tuple
Change the unit price of book A002 to 28:
update books set Unit Price = 28 where Book number = 'A002'
Modify the value of multiple tuples
Reduce the unit price of more books by one yuan:
update books set Unit Price = Unit Price - 1
Modified statement with query
Set the borrowing date of all borrowing records of reader Li Ming as the current date of the system and the return date as NULL:
update Borrow set Borrowing date=getdate(), Return date=NULL where 'Li Ming' = ( select Reader name from readers where Borrow.Reader number = readers.Reader number )
Equivalent to:
update Borrow set Borrowing date=getdate(), Return date=NULL where Reader number in ( select Reader number from readers where Reader name='Li Ming' )
Delete data
General format:
delete from <Table name> [where <condition>]
Delete all tuples that meet the conditions of the where clause.
- delete deletes the data in the basic table, not the definition of the table.
- Omitting the where clause will delete all tuples.
- Subqueries can also be embedded in the where clause.
- Once the data is deleted, it cannot be recovered unless there is a backup.
Delete the value of a tuple
Delete book record No. A002:
delete from books where Book number='A002'
Delete values of multiple tuples
delete from Borrow
All tuples in the borrowing table are deleted, but the table is still there, but it has become an empty table.
Delete statement with query
Delete all borrowing records of the books written by Mo Yan:
delete from Borrow where 'Mo Yan' = ( select Chief editor from books where Borrow.Book number = books.Book number )
Equivalent to:
delete from Borrow where Book number in ( select Book number from books where Chief editor = 'Mo Yan' )