SQL-based data query statement Summary

The following is a summary of the SQL data query statements. If you need a friend, refer to the where condition expression. The code for copying the statistical function is as follows: Selectcount (1) fromstudent; -- like fuzzy query -- the code for copying the number of people surnamed Zhang in the statistics class is as follows: selectcount (*) fromstudentwhererea1.

The following is a summary of the SQL data query statements. For more information, see the where condition expression. The statistical function code is as follows: Select count (1) from student; -- like fuzzy query -- code for counting the number of people surnamed Zhang in the class: select count (*) from student where realN

The following is a summary of the SQL data query statements. For more information, see

Where condition expression
-- Statistical functions

The Code is as follows:

Select count (1) from student;

-- Like fuzzy query
-- Count the number of people surnamed Zhang in the class

The Code is as follows:

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

-- Count the number of students with the surname Zhang in the class

The Code is as follows:

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

-- Count the number of Hangzhou students in the class

The Code is as follows:

Select count (*) from student where home like '% Hangzhou % ';

-- Query the age of each student in the class

The Code is as follows:

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

-- Query 90-Year-Old Students

The Code is as follows:

Select realName from student where year (birthday)> = '2013 ';

-- Query students born from

The Code is as follows:

Select realName from student where year (birthday) <= '000000' and year (birthday)> = '000000 ';
Select * from student where year (birthday) between '000000' and '000000 ';

-- Query the number of male and female in the class

The Code is as follows:

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

-- In Clause: queries B or O-blood students in the class.

The Code is as follows:

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

Subquery
Subqueries can also be called nested queries. Sometimes, a single query cannot solve the problem and multiple queries are required.

The records returned by the subquery are divided into single-row subqueries and multi-row subqueries;

The Code is 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 are higher than allen's salary.

A. subqueries generally run before the subject sentence
B. There must be (), indicating a whole
C. Traditionally place the subquery on the right of the condition
Multi-row subquery: some, any, all

Connection Statement (used for multi-table queries)
Including: inline, external (left Outer and right outer)
Inner join: queries the rows matching the two tables.

-- Query the scores of each student's subject and display the "name", "Course name", and "score" columns.

The Code is as follows:

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

There is another way to avoid inner join:

The Code is 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

The outer links are divided into left outer links and right outer links:
Left outer join: queries the matching records of both tables, and queries the non-matching records of the Left table.
Right outer join: equal to top, the records that do not match in the Right table are also queried.

The Code is as follows:

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