MySQL Common SQL Statement Summary Contains Complex SQL Queries

1. Complex SQL query

1.1. Single table query

(1) Select the specified column

Query the student numbers and names of all students

select Sno as  Student number ,Sname as  Name  from student;
select Sno,Sname from student;

(2) Query all columns

Query the details of all students

select * from student;

(3) Name the specified column after the query

[Example] Query the "name" and "year of birth" of all students

select Sname as  Name ,(2014-Sage) as  Year of birth  from student;
select Sname ,(2014-Sage) from student;

(4) Eliminate lines with duplicate values

Query the student number of the elective course

select distinct Sno as  Student number of students who have taken elective courses  from SC;
select distinct Sno from SC;

(5) Select several tuples in the table (those that meet the conditions)

1.2. Size comparison

Query the list of all students in the computer department (IS)

select Sname as  Student name  from student where Sdept='IS';

Query the names and ages of all students under the age of 20

select Sname as  Name ,Sage as  Age  from student where Sage<20;

1.3. Determine the scope

Query the name, department and age of all students between the ages of 20 and 23 (including 20 and 23)

select Sname as  Name ,Sdept as  Department ,Sage as  Age  from student where Sage between20 and 23;

Note that between decimal and large number.

1.4, in and not in Determine Set

[Example] Query the names and genders of all students in IS and CS departments

select Sname as  Name ,Ssex as  Gender  from student where Sdept='IS' or Sdept='CS';
select Sname as  Name ,Ssex as  Gender  from student where Sdept in ('IS','CS');

[Example] Query the names and ages of students who are neither IS nor MA

select Sname as  Name ,Sage as  Age  from student where Sdept !='IS'and Sdept!='CS';
select Sname as  Name ,Sage as  Age  from student where Sdept not in('IS','MA');

1.5, Character Matching (like% _)

Enquire the names and genders of all students surnamed Li

select Sname as  Name ,Ssex as  Gender  from student where Sname like ' Li %';

Enquire the student number, name and department of all students enrolled in "2002"

select * from student;

0

[Example] Query the information of all students who are not surnamed "Liu"

select * from student where Sname not like' Liu %';

[Example] The query name contains the course number, course name and credit of "data"

select Cno as  Course number ,Cname as  Course name ,Ccredit as  Credit  from course where Cname like '% Data %';

Summary:

select * from student;

3

1.6 Queries involving null values (is null)

[Example] Query the course number and course name without a prerequisite course

select * from student;

4

[Example] Query the student number, course number and grades of all students with achievements

select * from student;

5

1.7. Sort of query results (order by)

[Example] Query the student numbers and grades of the students who took the No.3 course, and the results are arranged in descending order of grades.

select * from student;

6

[Example] Query the student numbers and grades of the students who took the No.3 course, and the results are arranged in ascending order of grades.

select Sno as  Student number ,Grade as  Achievement  from SC where Cno=3 order by Grade asc;

1.8. Aggregation function

count, sum, avg, max, min

Query the total number of students

select count(*) as  Total number of students  from student;

Query the total credits of all courses

select * from student;

9

Query the average age of all students

select avg(Sage) as  Average age  from student;

[Example] Query the highest score of Course 1

select max(Grade) as 1 The highest score of course number  from SC where Cno=1;

1.9. Group Statistics (group by)

Inquire how many boys and girls there are.

select Ssex as  Gender ,count(*) as  Number of people  from student group by Ssex;

Query the course number and average score of each course.

select Cno as  Course number ,avg(Grade) as  Average score  from SC group by Cno;

[Example] Inquire about the student number and the number of elective courses of students who have taken more than 3 courses.

select Sno as  Student number  ,count(course.Cno) as  Number of elective courses 
From SC,course
Where course.Cno=SC.Cno
Group by Sno
Having Count(course.Cno)>=3;

The having keyword is followed directly by the aggregation function

The reason for adding the HAVING clause to SQL is that the WHERE keyword cannot be used with the sum function 1.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

[Example] Inquire about taking more than 2 courses (including 2 courses, but excluding No.1 course), student numbers and the number of elective courses.

select Sno as  Student number  ,count(course.Cno) as  Number of elective courses 
From SC,course
Where course.Cno=SC.Cno and course.Cno !=1
Group by Sno
Having Count(course.Cno)>=2;

Enquire the student number of students who fail more than 2 courses.

Select Sno
from sc
Where sc.Grade<60
Group by Sno
Having count(Cno)>=2;

[Example] Inquire about the course number and the number of elective courses taken by more than 2 students.

Select Cno,count(Sno)
From SC
Group by Cno
Having count(sno)>=2

2. Connect the query

(1) Equivalent and non-equivalent join queries

[Example] Inquire about each student and his elective courses

select student.Sno as  Student number ,course.Cno as  Elective course number ,SC.Grade as  Achievement  
from student,course,SC 
where student.Sno=SC.Sno and course.Cno=SC.Cno ;

(2) Self-connection

Query indirect elective courses for each student

select SC.Sno as  Student number ,
FIRST.Cname as  Direct elective course ,
SECOND.Cname as  Indirect elective course 
from SC,
course as FIRST,
course as SECOND
where FIRST.Cno=SC.Cno
and FIRST.Cpno=SECOND.Cno;

(3) External connection

[Example] Inquire about the elective courses of all students (including those who do not take elective courses)

select student.Sno as  Student number ,
Sname as  Name ,
sc.Cno as  Elective course number 
from student 
LEFT OUTER JOIN SC ON student.Sno=SC.Sno;

join is used to query data from two or more tables based on the relationship between columns in these tables

JOIN:  If the table has at least 1 A match, the row is returned 
LEFT JOIN:  Returns all rows from the left table even if there is no match in the right table 
RIGHT JOIN:  Returns all rows from the right table even if there is no match in the left table 
FULL JOIN:  As long as one of them 1 If there is a match in the table, the row is returned 

UNION  Operator is used to merge two or more  SELECT  The result set of the statement. 
 Please note that, UNION  Internal  SELECT  Statement must have the same number of columns. Columns must also have similar data types. At the same time, each article  SELECT  Statement must be in the same order. 

3. Nested queries

(1) Subquery with IN predicate (attribute in (query result of subquery))

Inquire about students' information in the same department as Wang Min.

select *
from student
where Sdept in (
 select Sdept
 from student
 where Sname=' Wang Min '
);

Inquire about the information of students who are not in the same department as Wang Min.

select *
from student
where Sdept not in (
 select Sdept
 from student
 whereSname=' Wang Min '
);

Inquire the student number and name of the student whose elective course name is "Information System".

select student.Sno as  Student number , Sname as  Name 
from student,SC
where student.Sno=SC.Sno and Cno in (
 select Cno
 from course
 where Cname=' Information system '
)

Inquire about the student number and name of the student who attended class with Liu Chen 1. (Assumption: There is only one class in one course)

select distinct student.Sno as  Student number , Sname as  Name 
from student,SC
where student.Sno=SC.Sno and Cno in (
 select Cno
 from SC,student
 where SC.Sno=student.Sno and student.Sno in (
 select Sno
 from student
 where student.Sname=' Liu Chen '
 )
)

The inner in finds out Liu Chen's student number sno, and the outer in finds out the course number of Liu Chen's course.

(2) Subqueries with comparison operators (=, > =, < =, < > Or! =)

[Example] Inquire about all students' information in the same department as Wang Min (= judgment)

select *
from student
where Sdept=(
 select Sdept
 from student
 where Sname=' Wang Min '
)

Query the course number of each student that exceeds the minimum score of the course. (The same kind of course is not the lowest score), when the result of subquery returns 1 number, this subquery can be used as 1 number? You can use the in symbol, or the greater than less than the symbol.

select Cno
from SC a
where Grade> (
 select min(Grade)
 from SC b
 where a.Cno=b.Cno
)

Query the course number of each student that exceeds the average grade of his elective course.

select Sname as  Student name  from student where Sdept='IS';

0

(3) Subqueries with ANY or ALL predicates

ANY for any 1, ALL for all, can be used before parentheses in a subquery

Inquire the name, gender, age and department of a student younger than a student in the computer department in other departments.

select Sname as  Student name  from student where Sdept='IS';

1

Query the names and ages of students in other departments who are younger than all the computer departments.

select Sname as  Student name  from student where Sdept='IS';

2

(4) Subqueries with Exists predicates

Inquire the names of all the students who have taken Course 1.

select Sname as  Student name  from student where Sdept='IS';

3

4. Set query

(1) UNION

Inquire about the details of students in computer science department and students not older than 19 years old.

select Sname as  Student name  from student where Sdept='IS';

4

(2) Cross INTERSECT

Query the intersection of detailed information of students who have taken Course 1 and are not older than 19 years old.

select Sname as  Student name  from student where Sdept='IS';

5

(3) Difference EXCEPT

Query the difference set of detailed information between students in computer science department and students not older than 19 years old.

select Sname as  Student name  from student where Sdept='IS';

6

Summarize