One, sub-query
Use a query statement as a value
The query result of a clause must be a column
Clause can return multiple rows of data, but must be a column
SELECT * FROM table name where column name =,>,< (SELECT * from table name where column name ...)
In/not in
Column name in (value, value, value) indicates that the value is within the value provided below, equivalent to column name = value or column name = value or column name = value
Example: SELECT * from car where oil=7.4 or oil=8 or oil=8.3 is identical to select *from car where oil in (7.4,8,8.3) execution results
Column name not in (value, value, value) means that the value is not within the value provided below, which is equivalent to the column name! = value and column name! = value and column name! = value
Example: SELECT * from car where oil not in (7.4,8,8.3) with select *from Cat where oil!=7.4 and oil!=8 and oil!=8.3
Between and
The value between A and B is equivalent to the value >=a and value <=b, which represents a value between a, B and both
Any
Value >any (a,b,c) is the minimum value greater than the number of A,B,C3
Value <any (a,b,c) is the maximum value less than the number of A,B,C3
All
Value >all (A,B,C) is greater than the maximum value in the number of A,B,C3
The value <all (A,B,C) is less than the minimum value in the A,B,C3 number
* Query two columns of data from two different tables
Make contact with the same column in the two tables through a subquery
Example: Query all students for Sname, CNO, and degree columns.
Select (select Sname from Student where Student.sno=score.sno), Cno,degree from score
--where sname is in the Student table, CNO and degree in the score table, where two tables have Student.sno=score.sno connections
-- use a table with more data to query the main table, where score is the main table
Second, aggregate function
1.Max (max), min (min)
2.avg Average
3.count counts rows
4.Sum sum
* Data queried using an aggregate function does not have a column name, you can add the column name by adding the as name after the aggregate function
Group queries and count ascending and descending order
Select Column name, COUNT (*) from table name Group By column name order by COUNT (*) Asc/desc
Group Query and Count filter grouping after filtering requires having to replace where
Select Column name, COUNT (*) from table name Group BY column name having COUNT (*) >,<,= value
"2017-03-12" SQL Sever subquery, aggregate function