1. Alias as

select * from classinfo as ui

Query all columns, specify columns
Alias as for column

select ui.cid from classinfo ui

2. Part of data before query: top n column name: indicates the first n rows to view

select top 3 * from classinfo -- query all columns in the first three rows

top n percent column name: indicates the top percentage of data viewed

select top 5 percent *from classinfo -- query the top 5 percent of all column data

3. Sort: order by column name 1 ASC (ascending) | desc (descending), column name 1 asc|desc

select * from Userinfo
order by cid desc ,cname desc--First according to cis In descending order according to cname Ascending row

4. De duplicate lines: distinct

select distinct cname from classinfo -- eliminate the same cname in classinfo (it is only eliminated during query, not in the database)

5. Condition query: write after where

Filter the row and return the value of bool type. If the column data in a row meets the conditions, it will be added to the result set. Otherwise, it will not appear in the result set
 Comparison operators: =, >

select * from classinfo
where cid>3

Between... And... Means in a continuous range

select * from classinfo
where cid between 2 and 4

In means in a discontinuous range

select * from classinfo
where cid in(3,5)

Logical operators: and, or, not

select * from classinfo
where cid=3 or cid=5

select * from classinfo
where not cid>=2 and cid<=5

6. Fuzzy query: used to process value of string type, operators include: like% []^

% of several

select * from studentinfo
where sphone like '%4%'--4 in phone number

Single

select * from studentinfo
where sname like 'tiger_'--Tiger Moumou

[scope]
In [] indicates a continuous range can be used-

select * from studentinfo
where sphone like '1[3-5]%'--Phone number in the range of 3 to 5 when the second number starts with phone number 1

%The meaning of "and" written in [] "
^The beginning written inside [] means no internal characters are used

select * from studentinfo
where sphone like '1[^553]%'--Phone number 1 starts with a phone number other than 553

7. Null judgment: use is null or is not null, return null when calculating with other values, and null is regarded as the minimum when sorting

select * from studentinfo
where sphone is null--The inquiry call is empty

8. Join: join table name on Association condition

Inner join: inner join, exactly matched data in two tables
 Left outer join: left outer join, the data exactly matched in the two tables, and the unique data in the left table
 Right outer join: right outer join, the data exactly matched in the two tables, and the unique data in the right table
 Full outer join: full outer join, data in the two tables that match exactly, data in the left table and data in the right table

select * 
from studentinfo
inner join classinfo on studentinfo.cid=classinfo.cid 

select studentinfo.sname,classinfo.cname
from studentinfo
inner join classinfo on studentinfo.cid=classinfo.cid 

select so.sname,co.cname--Aliases are recommended
from studentinfo as so
inner join classinfo as co on so.cid=co.cid 

select so.sname,co.cname
from studentinfo as so
right join classinfo as co on so.cid=co.cid 

select so.sname,co.cname
from studentinfo as so
left join classinfo as co on so.cid=co.cid 

select *
from studentinfo as so
full join classinfo as co on so.cid=co.cid 

Multi table query:

select st.sname,cl.cname,sc.score,su.sucourse
from sc
inner join  studentinfo as st on sc.cid=st.cid
inner join  subjectinfo as su on sc.sid=su.suid
inner join  classinfo as cl on st.cid=cl.cid

9. Aggregate function: merge row data

sum,avg,count,max,min
 Generally, it operates on columns of numeric type
 Multiple aggregate functions can be written in one query at the same time, but they cannot be mixed with common columns
 null problem in aggregation: do not participate in calculation

select count(*) from studentinfo

select sum(score) 
from sc
inner join subjectinfo as su on sc.sid=su.suid
where su.sucourse='Chinese' 

select avg(score) 
from sc
inner join subjectinfo as su on sc.sid=su.suid
where su.sucourse='Chinese' 

select max(score) 
from sc
inner join subjectinfo as su on sc.sid=su.suid
where su.sucourse='Chinese' 

select min(score) 
from sc
inner join subjectinfo as su on sc.sid=su.suid
where su.sucourse='Chinese' 

10. Windowing function: over()

Distribute the statistical data to each row of the original table
 Combined with aggregate function and ranking function

select sc.*,avg(score) over()
from sc
where sid=1

11. Group: group by column name 1, column name 2

Aggregate functions are generally used in combination with grouping to make statistics on the data within the grouping
 Group by specified column

select COUNT(*)
from studentinfo
group by sgender--Calculate the total number of men and women

select studentinfo.sgender ,studentinfo.cid ,classinfo.cname,COUNT(*) 
from studentinfo
inner join classinfo on classinfo.cid=studentinfo.cid
group by studentinfo.sgender,studentinfo.cid,classinfo.cname
--Ask for the number of men and women in each class and display the class (first male and female, then class)

Note: select can only be followed by group by

--Count the number of students of each gender whose student number is greater than 2 in each class
select sgender,cid,COUNT(*)
from studentinfo
where sid>2
group by sgender,cid

Filter after grouping: having

--Count the information that the number of students of each gender in each class with student number greater than 2 is greater than 1
select sgender,cid,COUNT(*)
from studentinfo
where sid>2
group by sgender,cid having count(*)>1