Summary of MySQL common SQL statements including complex SQL queries

1. Complex SQL queries

1.1. Single table query

(1) Select the specified column

[Example] Query the student ID and name of all students

select Sno as student number, Sname as name from student;
select Sno,Sname from student;

(2) Query all columns

[Example] Query detailed information of all students

select * from student;

(3) Name the specified columns after the query

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

select Sname as name,(2014-Sage) as birthyear from student;
select Sname ,(2014-Sage) from student;

(4) Eliminate rows with duplicate values

[Example] Query the student ID of the student who has taken the course

select distinct Sno as student number of the student who took the course from SC;
select distinct Sno from SC;

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

1.2 Size Comparison

[Example] Query the list of all students in the Department of Computer Science (IS)

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

[Example] Query the names and ages of all students under 20 years old

select Sname as name, Sage as age from student where Sage<20;

1.3 Determine the scope

[Example] Query the name, department and age of all students between 20 and 23 years old (inclusive)

select Sname as name, Sdept as department, Sage as age from student where Sage between20 and 23;

Note the space between small numbers and large numbers.

1.4. in and not in determine the set

[Example] Query the names and genders of all students in the 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 in the IS department nor in the MA department

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 % _ )

[Example] Query the name and gender of all students with the surname Li

select Sname as name, Ssex as gender from student where Sname like '李%';

[Example] Query the student ID, name and department of all students enrolled in 2002

select Sno as student number, Sname as name, Sdept as department from student where Sno like'2002%';

[Example] Query all student information whose last name is not "Liu"

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

[Example] Search for course numbers, course names, and credits that contain "data"

select Cno as course number, Cname as course name, Ccredit as credits from course where Cname like '% data%';

Summarize:

select * from course where cname like '%数据%'; string containing dataselect * from course where cname like '数据%'; string starting with dataselect * from course where cname like '%数据'; string ending with data

1.6. Queries involving null values ​​(is null)

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

select Cno as course number, Cname as course name, Cpno from course where Cpno is null;

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

select Sno as student number, Cno as course number, Grade as grade from SC where Grade is not null;

1.7. Sorting query results (order by)

[Example] Query the student ID and grades of students who have taken course 3, and sort the results in descending order by grade.

select Sno as student number, Grade as grade from SC where Cno=3 order by Grade desc;

[Example] Query the student ID and grades of students who have taken course 3, and sort the results in ascending order by grades.

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

1.8 Aggregate Functions

count, sum, avg, max, min

[Example] Query the total number of students

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

[Example] Query the total credits of all courses

select sum(Ccredit) as total credits for all courses from course;

[Example] 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 the highest score of course 1 from SC where Cno=1;

1.9. Group statistics (group by)

[Example] Check how many male and female students there are.

select Ssex as gender, count(*) as number from student group by Ssex;

[Example] 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] Query the student ID and number of elective courses for students who have taken 3 or more 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 an aggregate function.

The reason for adding the HAVING clause in SQL is that the WHERE keyword cannot be used with aggregate functions.

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] Query the student ID and number of elective courses for students who have taken more than 2 courses (including 2 but excluding course 1).

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;

[Example] Find the student ID number of students who have failed more than 2 subjects.

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

[Example] Query the course numbers and number of students enrolled in courses that have more than 2 (including 2) students.

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

2. Connection query

(1) Equivalent and non-equivalent join queries

[Example] Query each student and their elective courses

select student.Sno as student number,course.Cno as elective course number,SC.Grade as grade from student,course,SC 
where student.Sno=SC.Sno and course.Cno=SC.Cno;

(2) Self-connection

[Example] Query each student's indirect elective courses

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) Outer join

[Example] Query the elective courses of all students (including students who have not taken 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 the columns in these tables.

JOIN: Returns rows if there is at least one match in the table 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: Returns rows as long as there is a match in either table

The UNION operator is used to combine the result sets of two or more SELECT statements.
Note that the SELECT statements inside the UNION must have the same number of columns. Columns must also have similar data types. Also, the order of the columns in each SELECT statement must be the same.

3. Nested queries

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

[Example] Query the student information of Wang Min who is in the same department.

select *
from student
where Sdept in (
 select Sdept
 from student
 where Sname='王敏'
);

[Example] Query the student information that is not in the same department as Wang Min.

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

[Example] Query the student ID and name of the students who have taken the course "Information Systems".

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'
)

[Example] Find the student ID and name of the students who have taken classes with Liu Chen. (Assumption: There is only one class for a 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 query finds out Liu Chen’s student ID number sno, and the outer in query finds out the course number of the course Liu Chen is taking.

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

[Example] Query all student information in the same department as Wang Min (= judgment)

select *
from student
where Sdept=(
 select Sdept
 from student
 where Sname='王敏'
)

[Example] Find the course number for each student whose score exceeds the minimum score of the course. (Not the lowest score among similar courses), when the result of a subquery returns a number, can this subquery be used as a number? You can use the in symbol, or the greater than or less than symbol.

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

[Example] Find the course number of each student whose score exceeds the average score of his elective courses.

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

(3) Subqueries with ANY or ALL predicates

• ANY means any one, ALL means all, and can be used before the parentheses of the subquery.
  

[Example] Query the name, gender, age and department of students from other departments who are younger than a student in the Computer Science Department.

select Sname as name, Ssex as gender, Sage as age, Sdept as department from student
where Sage <(
 select Sage
 from student
 where Sdept='CS'
);

[Example] Find the names and ages of students in other departments who are younger than all the students in the Computer Science Department.

select Sname as name, Sage as age from student
where Sdept<>'CS' and Sage <ALL (
 select Sage
 from student
 where Sdept='CS'
);

(4) Subquery with Exists predicate

[Example] Query the names of all students who have taken course 1.

select Sname as name from student
where Exists (
 select *
 from SC
 where Cno=1 and Sno=Student.Sno
);

4. Collection Query

(1) UNION

[Example] Query the detailed information of students in the Department of Computer Science and students under the age of 19.

select *
from student
where student.Sdept='CS'
union
select *
from student
where student.Sage<=19;

(2) INTERSECT

[Example] Query the intersection of detailed information of students who have taken course 1 and whose age is not older than 19 years old.

Select *
from student,SC
where student.Sno=SC.Sno and SC.Cno=1
INTERSECT
Select *
from student
where student.Sage<=19;

(3) EXCEPT

[Example] Query the difference between the detailed information of students in the Department of Computer Science and students who are not older than 19 years old.

select *
from student
where student.Sdept='SC'
EXCEPT
select *
from student
where student.Sage<=19;

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links