Database Collation One

1. Annotation syntax:--,#
2. The suffix is. sql files are database query files
3. In the database column called the field row called the record

CRUD Operations:
Create Creation (ADD)
Read Reads
Update modification
Delete Deletes

1. Add Data

Insert into table name values (value)

Insert into table name (Field 1, Field 2) VALUES (Field 1 value, field 2 value)

2. Delete data
Delete all data
Delete * from family
Delete a specific data

Delete info from table name where condition

3. Modify the data
Update * Set field = field value

Update table name set the content to be modified where condition

4. Read data
(1) Simple read, query all columns (*) All rows (no add condition)
SELECT * FROM table name
(2) reading a specific column
Select what to read from table name where condition
(3) Multi-criteria Query
SELECT * from Info where field 1 = Field 1 value or field 2 = Field 2 value #或的关系
SELECT * from Info where field 1 = Field 1 value and field 2 = Field 2 value #与的关系
(5) keyword query (fuzzy query)
Check all cars that contain Audi
SELECT * from car where name like ' Crown% '; #百分号% represents any number of characters
Check all the cars that start with ' crown '
SELECT * from car where name like ' Crown% ';
Query car Name The second character is ' horse '
SELECT * from car where name like ' _ Ma% '; #下划线_代表任意一个字符
(6) Sort query
SELECT * from Car order by powers #默认升序排列
SELECT * from car ORDER by powers Desc #升序asc Descending desc
Rank by brand in ascending order, and then
SELECT * FROM car ORDER BY brand,price Desc

(7) Scope query
SELECT * from car where price>40 and price<60
SELECT * from car where price between and 60 note: for different databases, may include 50, 60, may not include, or may contain only 1;

(8) Discrete query
SELECT * from car where price=30 or price=40 or price=50 or price=60;
SELECT * from car where price in (30,40,50,60)
SELECT * from car where price not in (30,40,50,60)

(9) Aggregate function (statistical query)

Select COUNT (*) as total from car# fetch all data bar number

Select SUM (price) as the sum from car #求价格总和
Select AVG (price) from car #求价格的平均值
Select Max from Car #求最大值
Select min (price) from car #求最小值

Note: Take full information of the highest priced car: Select *from car where price= (select Max (price) from car);

(10) Paging query
SELECT * FROM car limit 0,10 #分页查询, skip a few data (0) take a few (10)
Specify a number of bars to display per page: M
Current page: N
SELECT * FROM car limit (n-1) *m,m Skip (n-1) *m strip data take M bar

(11) Go to re-query
Select distinct brand from car

(12) Group query
Check the number of cars under each series in the car table
Select Brand,count (*) from car GROUP by brand
After grouping, only the column or aggregate function can be queried

Take the series code of the series price average greater than 40
Select brand from car GROUP by Brand had AVG (price) >40

Take the series code of the series with maximum fuel consumption greater than 8
Select brand from car GROUP by Brand have Max (oil) >8

Database Collation One