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.

1. 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.

2. 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.

1. Table creation:

   create table CBS
   (press varchar(15), total int)
   

2. 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'
)