MySQL常用SQL（含复杂SQL查询）

1、复杂SQL查询

 

1.1、单表查询

(1)选择指定的列

[例]查询全体学生的学号和姓名

 

1.  
   select Sno as 学号,Sname as 姓名 from student;
2.  
   select Sno,Sname from student;

(2）查询全部列

[例]查询全体学生的详细信息

select * from student;

(3）对查询后的指定列进行命名

[例]查询全部学生的“姓名”及其“出生年”两列

1.  
   select Sname as 姓名,(2014-Sage) as 出生年 from student;
2.  
   select Sname ,(2014-Sage) from student;

第一种:

一张人员信息表里有一人生日(Birthday)列,跟据这个列,算出该人员的年龄

datediff(year,birthday,getdate())

例:birthday = '2003-3-8'

getDate()= '2008-7-7'

结果为:5

这样结果是会返回该人员的大概年龄,但不精确.不会精确到月或日.

按照上面测试的日期,该人员的实际年龄应该还不满5岁。在需要精确年龄的时候，就会有错.

第二种：

　　FLOOR(datediff(DY,birthday,getdate())/365.25)

FLOOR函数：

FLOOR(expr) 返回小于或等于expr的最大整数.FLOOR(1.1)返回1,FLOOR(-1.1)返回-2,FLOOR(1)返回1

这样就可以精确算出，该人员当前实际年龄了.

测试:

birthday = '2000-7-8'

getdate()= '2007-7-7'

算出结果为:6

(4）消除取值重复的行

[例]查询选修了课程的学生学号

1.  
   select distinct Sno as 选修了课程的学生学号 from SC;
2.  
   select distinct Sno from SC;

 

(5）选择表中若干元组（满足条件的）

 

1.2、大小比较

    [例]查询计算机系（IS）全体学生名单

select Sname as 学生姓名 from student where Sdept='IS';

 

    [例]查询全体20岁以下的学生姓名和年龄

 

select Sname as 姓名,Sage as 年龄 from student where Sage<20;

1.3、确定范围

 

    [例]查询所有在20到23岁（含20和23）的学生姓名、系别和年龄

 

select Sname as 姓名,Sdept as 系别,Sage as 年龄 from student where Sage between20 and 23;

注意between 小数 and 大数。

 

1.4、in和not in确定集合

 

    [例]查询IS系和CS系的全体学生姓名和性别

1.  
   select Sname as 姓名,Ssex as 性别 from student where Sdept='IS' or Sdept='CS';
2.  
   select Sname as 姓名,Ssex as 性别 from student where Sdept in ('IS','CS');

    [例]查询既不属于IS系，也不属于MA系的学生姓名和年龄

1.  
   select Sname as 姓名,Sage as 年龄 from student where Sdept !='IS'and Sdept!='CS';
2.  
   select Sname as 姓名,Sage as 年龄 from student where Sdept not in('IS','MA');

1.5、字符匹配（like % _ ）

 

    [例]查询所有姓李的学生姓名和性别

select Sname as 姓名,Ssex as 性别 from student where Sname like '李%';

    [例]查询所有“2002”年入学的学生学号、姓名和系别

select Sno as 学号,Sname as 姓名,Sdept as 系别 from student where Sno like'2002%';

    [例]查询所有不姓“刘”的学生信息

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

    [例]查询名称含有“数据”的课程号、课程名及学分

select Cno as 课程号,Cname as 课程名,Ccredit as 学分 from course where Cname like '%数据%';

总结：

1.  
   select * from course where cname like '%数据%';包含数据的字符串
2.  
   select * from course where cname like '数据%';以数据开头的字符串
3.  
   select * from course where cname like '%数据'; 以数据结尾的字符串

 

1.6、涉及空值的查询（is null）

 

    [例]查询没有先修课的课程号和课程名

select Cno as 课程号,Cname as 课程名,Cpno from course where Cpno is null;

    [例]查询所有有成绩的学生学号、课程号及成绩

select Sno as 学号,Cno as 课程号,Grade as 成绩 from SC where Grade is not null;

 

1.7、查询结果排序（order by ）

 

[例]查询选修了3号课程的学生学号和成绩，结果按成绩降序排列。

select Sno as 学号,Grade as 成绩 from SC where Cno=3 order by Grade desc;

[例]查询选修了3号课程的学生学号和成绩，结果按成绩升序排列。

select Sno as 学号,Grade as 成绩 from SC where Cno=3 order by Grade asc;

1.8、聚集函数

count、sum、avg、max、min

[例]查询学生总数

select count(*) as 学生总数 from student;

[例]查询所有课程的总学分

select sum(Ccredit) as 所有课程总学分 from course;

[例]查询全体学生平均年龄

select avg(Sage) as 平均年龄 from student;

[例]查询1号课程的最高分

select max(Grade) as 1号课程的最高分 from SC where Cno=1;

1.9、分组统计（group by）

[例]查询男女学生各有多少人。

select Ssex as 性别,count(*) as 人数 from student group by Ssex;

[例]查询每个课程的课程号和平均分。

select Cno as 课程号,avg(Grade) as 平均分 from SC group by Cno;

【例】查询选修了3门课程以上（含3门）的学生学号和选修课程数。

1.  
   select Sno as 学号 ,count(course.Cno) as 选修课程数
2.  
   From SC,course
3.  
   Where course.Cno=SC.Cno
4.  
   Group by Sno
5.  
   Having Count(course.Cno)>=3;

having 关键字后面直接跟聚集函数

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

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

【例】查询选修了2门课程以上（含2门，但不含1号课程）,学生学号和选修课程数。

1.  
   select Sno as 学号 ,count(course.Cno) as 选修课程数
2.  
   From SC,course
3.  
   Where course.Cno=SC.Cno and course.Cno !=1
4.  
   Group by Sno
5.  
   Having Count(course.Cno)>=2;

【例】查询不及格门数2门以上的学生学号。

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

 

【例】查询有2名以上（含2名）学生选修了的课程号和选修人数。

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

 

2、连接查询

（1）等值与非等值连接查询

[例]查询每个学生及其的选修课程情况

1.  
   select student.Sno as 学号,course.Cno as 选修课号,SC.Grade as 成绩
2.  
   from student,course,SC
3.  
   where student.Sno=SC.Sno and course.Cno=SC.Cno ;

 

（2）自身连接

[例]查询每个学生的间接选修课

select SC.Sno as 学号,
FIRST.Cname as 直接选修课,
SECOND.Cname as 间接选修课
from SC,
course as FIRST,
course as SECOND
where FIRST.Cno=SC.Cno
and FIRST.Cpno=SECOND.Cno;

（3）外连接

[例]查询所有学生选修课程情况（含没选修课程的学生）

select student.Sno as 学号,
Sname as 姓名,
sc.Cno as 选修课程号
from student 
LEFT OUTER JOIN SC ON student.Sno=SC.Sno;

 join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据

1.  
   JOIN: 如果表中有至少一个匹配，则返回行
2.  
   LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
3.  
   RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
4.  
   FULL JOIN: 只要其中一个表中存在匹配，就返回行

1.  
   UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
2.  
   请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

 

3 、嵌套查询

（1）带有IN谓词的子查询（ 属性 in （子查询的查询结果） ）

【例】查询与王敏同学在同一个系的学生信息。

1.  
   select *
2.  
   from student
3.  
   where Sdept in (
4.  
   select Sdept
5.  
   from student
6.  
   where Sname='王敏'
7.  
   );

 

【例】查询不与王敏同学不在同一个系的学生信息。

1.  
   select *
2.  
   from student
3.  
   where Sdept not in (
4.  
   select Sdept
5.  
   from student
6.  
   whereSname='王敏'
7.  
   );

【例】查询选修了课程名是“信息系统”的学生学号和姓名。

1.  
   select student.Sno as 学号, Sname as 姓名
2.  
   from student,SC
3.  
   where student.Sno=SC.Sno and Cno in (
4.  
   select Cno
5.  
   from course
6.  
   where Cname='信息系统'
7.  
   )

 

【例】查询曾与刘晨一同上课的学生学号和姓名。（假设：一个课程只有一个上课班）

1.  
   select distinct student.Sno as 学号, Sname as 姓名
2.  
   from student,SC
3.  
   where student.Sno=SC.Sno and Cno in (
4.  
   select Cno
5.  
   from SC,student
6.  
   where SC.Sno=student.Sno and student.Sno in (
7.  
   select Sno
8.  
   from student
9.  
   where student.Sname='刘晨'
10.  
    )
11.  
    )

 

• 内层in 查出刘晨的学号sno，外层in查出刘晨所上课程的课程号。

（2）带有比较运算符的子查询（=，>=，<=，<>或!=）

【例】查询与王敏同学在同一个系的所有学生信息  （=判断）

1.  
   select *
2.  
   from student
3.  
   where Sdept=(
4.  
   select Sdept
5.  
   from student
6.  
   where Sname='王敏'
7.  
   )

 

【例】查询每个学生超过该课程最低分的课程号。（同类课程不是最低分的），子查询的结果返回一个数的时候，这个子查询就可以当一个数用？可以使用in符号，或者大于小于符号。

1.  
   select Cno
2.  
   from SC a
3.  
   where Grade> (
4.  
   select min(Grade)
5.  
   from SC b
6.  
   where a.Cno=b.Cno
7.  
   )

 

【例】查询每个学生超过他选修课程平均成绩的课程号。

1.  
   select Cno
2.  
   from SC a
3.  
   where Grade> (
4.  
   select avg(Grade)
5.  
   from SC b
6.  
   where a.Sno=b.Sno
7.  
   )

 

（3）带有ANY或ALL谓词的子查询

• ANY表示任何一个，ALL表示所有，可以用在子查询的括号前面

【例】查询其他系中比计算机系某一学生年龄小的学生姓名，性别、年龄和所在系。

1.  
   select Sname as 姓名,Ssex as 性别, Sage as 年龄, Sdept as 所在系
2.  
   from student
3.  
   where Sage <(
4.  
   select Sage
5.  
   from student
6.  
   where Sdept='CS'
7.  
   );

 

【例】查询其他系中比计算机系所有年龄都小的学生姓名和年龄。

1.  
   select Sname as 姓名, Sage as 年龄
2.  
   from student
3.  
   where Sdept<>'CS' and Sage <ALL (
4.  
   select Sage
5.  
   from student
6.  
   where Sdept='CS'
7.  
   );

 

（4 ）带有Exists谓词的子查询

【例】查询所有选修了1号课程的学生姓名。

1.  
   select Sname as 姓名
2.  
   from student
3.  
   where Exists (
4.  
   select *
5.  
   from SC
6.  
   where Cno=1 and Sno=Student.Sno
7.  
   );

 

 

4、集合查询

（1)并UNION

【例】 查询计算机系的学生及年龄不大于19岁的学生详细信息。

1.  
   select *
2.  
   from student
3.  
   where student.Sdept='CS'
4.  
   union
5.  
   select *
6.  
   from student
7.  
   where student.Sage<=19;

 

（2)交INTERSECT

【例】查询选修了1号课程的与年龄不大于19岁的 学生 详细信息 的交集。

1.  
   Select *
2.  
   from student,SC
3.  
   where student.Sno=SC.Sno and SC.Cno=1
4.  
   INTERSECT
5.  
   Select *
6.  
   from student
7.  
   where student.Sage<=19;

 

（3)差EXCEPT

【例】查询计算机科学系的学生与年龄不大于19岁的学生详细信息的差集。

1.  
   select *
2.  
   from student
3.  
   where student.Sdept='SC'
4.  
   EXCEPT
5.  
   select *
6.  
   from student
7.  
   where student.Sage<=19;