Summary of query statements based on data in SQL

where conditional expressions
-- statistical function

Select count(1) from student;

--like fuzzy query
-- count the number of people surnamed zhang in the class

select count(*) from student where realName like ' zhang %';

-- count the number of people with the surname zhang in the class

select count(*) from student where realName like ' zhang _';

-- count the number of students from hangzhou in the class

select count(*) from student where home like '% hangzhou %';

-- check the age of every student in the class

select realName,year(now())-year(birthday) as age from student;

-- look up students born in 1990

select realName from student where year(birthday)>='1990';

-- students born from 1987 to 1990

select realName from student where year(birthday)<='1990' and year(birthday)>='1987';
select * from student where year(birthday) between '1987' and '1990';

-- check the number of boys and girls in the class

select sex,count(*) from student group by sex;

--in clause for students with type B or O blood in the class

select realName,blood from student where blood in('B','O');  

The subquery
Subqueries can also be called nested queries. Sometimes, one query does not solve the problem and requires multiple queries.

According to the number of record rows returned by subquery, it can be divided into single-row subquery and multi-row subquery.

select * from emp where sal>(       select sal from emp where ename='ALLEN '  or ename = ' KING') 

The above example is to find all employees who earn more than allen

A. Subquery 1 normally runs before the subject sentence
B. There must be () to represent a whole
C. It is customary to place a subquery to the right of a condition
Multi-row subqueries: some,any,all

Join statements (applied to multiple table queries)
Include: inline, outreach (left outreach and right outreach)
Inline (inner join) : look up the rows that match the two tables.

-- check each student's grade, and display the three columns of "name", "course name" and "score"

select a.realname,c.courseName,b.score from stu_student as a inner join stu_score as b on a.sid=b.sid inner join stu_course c on b.cid=c.cid

There is one other method that does not use inner join:

select a.realname,c.courseName,b.score from student a,score b,course c where a.sid=b.sid and c.cid=b.cid

External liaison can be divided into left external liaison and right external liaison:
Left outer join: query the matching records of both tables, and query the mismatch records of the left table.
Right outer join: wait, query the mismatched records in the right table as well.

select a.realname,b.score from stu_student as a left outer join stu_score as b on a.sid=b.sid