6.3 Database Deletion and modification

Add Table Data

1 INSERT into car values (', ', ', ', ', ');

2 INSERT into car (field 1, Field 2, ...) VALUES (value 1, value 2, ...);

3 INSERT INTO car set field 1= value One, field 2= value 2, ... ；

modifying table Data

Update car SET field 1= value where field 2= value

Delete table Data

Delete from table name where field 1 = value

================ Simple Query ===============

1 Most simple queries

SELECT * from Car;

2 Querying the specified column

Select Name,oil,powers from Car;

3 Change the name of the identified column names

Select name as ' car name ', oil as ' fuel consumption ', powers as ' power ' from car;

4 Query by condition

Select name, oil,powers from car where oil = 8.7;

More than 5 pieces of query

Select name, oil, powers from car where oil=8.7 or powers = 160;

Select Name,oil, powers from car where oil = 8.7 and powers = 160;

6 Range queries

SELECT * from car where Price>=40and price<=60;

SELECT * from car where price between and 60;

7 Discrete queries

SELECT * from car where powers = powers=150 or powers=170;

Select *from Car where powers in (130,150,170);

SELECT * from car where powers not in (130,150,170);

8 Fuzzy Query

Select *from car where name like '% BMW% ';

SELECT * from car where name like "_ BMW%"; _ Represents a character

9 Sort query ASC Ascending desc Descending order Queueing

SELECT * FROM car order by oil ASC;

SELECT * FROM Car ORDER by brand Asc,price desc;

10 go to duplicate query distinct

Select distinct brand from car;

11 Paged Query

select* from car limit 10, 5;

N:m=5

Limit (n-1) *m,m;

12 Aggregate function queries

Count (column name) sum (column name) AVG (column name) max (column name) min (column name)

Select COUNT (*) from car;

Select SUM (price) from car;

Select AVG (price) from car;

Select Max (price) from car;

Select min (price) from car;

6.3 Database Deletion and modification