1. Insert data into the table

The known table is shown in the figure :

1.1 Single row data insertion

blog.csdnimg.cn/14d8488f1cc44d52b555b1e2888f02e2.png)

Insert a piece of data in the product table ： The name is “ Schoolbag ”、 Price 18.91、 stock 101、 Description is empty
insert into goods values(‘ Schoolbag ’,18.91,101,null);

1.2 Multi row data insertion

insert into goods values(‘ The bed ’,500.99,200,null),(‘ Table ’,200.99,100,null);

1.3 Specify column insertion

Insert a piece of data in the product table ： The name is “ laptop ”、 Price 6566.99.
insert into goods(name,price) values(‘ laptop ’,6566.99);

2. Query the data in the table

2.1 Look up all the data in the table

select * from goods;

2.2 Specified condition query (where)

Comparison operator :

Logical operators :

2AND Has a higher priority than OR, When used at the same time , You need to use parentheses () The priority part of the package

student In the student list , Field has name name, Age age, Ask for the surname Zhang , And at the age of 18 To 25 Students aged between

2.3 Alias query

() as () Put the expression result of the previous bracket use The name in parentheses indicates
select id, name, chinese + math + english as Total score from student;

2.4 Go to check again (distinct)

Remove the repeated data query in students' math scores
select distinct math from student;

2.5 Sort query (order by)

– ASC In ascending order （ From small to large ）
– DESC For the descending order （ From big to small ）

select name, qq_mail from student order by qq_mail;( Default ascending order )
select name, qq_mail from student order by qq_mail DESC;( Descending )

Example

Inquire about article In the article table , Article title title It's empty , Or meet the publication date create_date stay 2019 year 1 month 1 After the day
select * from article where title is null or create_date > ‘2019-1-1 00:00:00’;

3 Modify the operating

update Table name set
Modify all inventories greater than 30 Product records for , Increase the price 50 block
update goods set price = price + 50 where stock > 30;

modify “Java The core technology ” Book information of , Change the price to 61
update book set price = 61 where name = ‘java The core technology ’;

4. Delete operation

Delete the item table , The price is greater than 60, Or the inventory is less than 200 The record of
delete from goods where price > 60 or stock < 200;