Summary of data query statements based on SQL _mysql

Where Condition expression
--Statistical function

Copy Code code as follows:

Select count (1) from student;

--like Fuzzy Query
--statistics on the number of people in the class surnamed Zhang

Copy Code code as follows:

Select COUNT (*) from the student where realname like ' Zhang% ';

- -The number of two characters in the class

Copy Code code as follows:

Select COUNT (*) from the student where Realname like ' Zhang _ ';

--statistics on the number of students in Hangzhou in the class

Copy Code code as follows:

Select COUNT (*) from student where home like '% hangzhou% ';

--Check the age of each student in the class

Copy Code code as follows:

Select Realname,year (now ())-year (birthday) as age from student;

--Enquiries about 90-born students

Copy Code code as follows:

Select Realname from student where year (birthday) >= ' 1990 ';

--Enquiries about 1987-1990-born students

Copy Code code as follows:

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

Copy Code code as follows:

Select Sex,count (*) from student group by sex;

--in clause for students of Class B or O type blood

Copy Code code as follows:

Select Realname,blood from student where blood in (' B ', ' O ');

Child Query
Subqueries can also be called nested queries, and sometimes a query does not solve the problem and requires multiple queries.

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

Copy Code code as follows:

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

The above example is to find all employees who have a higher salary than Allen

A. Subqueries are typically run before the main statement
B. Must have (), represent a whole
C. Accustomed to the handle of the query placed on the right side of the condition
Multi-line subquery: Some,any,all

Connection statements (applied to multiple table queries)
Includes: inline, outreach (left outer and right outer link)
Inline (inner JOIN): Query the rows that match the two tables.

--Check each student's scores, display "name" "Course name" "Score" three columns

Copy Code code as follows:

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_cours E C on B.cid=c.cid

There is also a way to not adopt INNER join:

Copy Code code as follows:

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 and left outer and right outer-union:
LEFT OUTER join: queries the matching records on both sides of the table and queries the mismatched records of the left-hand table.
Right outer join: On the other hand, the table does not match the record is also queried.

Copy Code code as follows:

Select A.realname,b.score from stu_student as a left outer join Stu_score as B on A.sid=b.sid