mysql数据查询

数据查询

一般格式：

SELECT [ALL|DISTINCT] <目标表达式> [,<目标表达式>] …

FROM <表名或视图名> [,<表名或视图名>…] |(语句) [AS] <别名>

[WHERE <条件表达式>]

[GROUP BY <列名1>] [HAVING <条件表达式>]

[ORDER BY <列名2>] [ASC|DESC>];

一、单表查询

建表

drop table SC;
drop table Course;
drop table Student;

create table Student (
Sno varchar(12) PRIMARY KEY,
Sname varchar(20) UNIQUE,
Ssex varchar(2),
Sage smallint,
Sdept varchar(20)
);

insert into Student values ('201913138001','张三','男',20,'CS');
insert into Student values ('201913138002','刘星','女',19,'CS');
insert into Student values ('201913138003','欧阳娜娜','女',20,'MA');
insert into Student values ('201913138004','欧阳修','男',20,'IS');
insert into Student values ('201913138005','欧尅','男',18,'IS');

create table Course (
Cno varchar(4) PRIMARY KEY,
Cname varchar(40) NOT NULL,
Cpno varchar(4),
Ccredit smallint,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);

insert into Course values ('2','数学',NULL,2);
insert into Course values ('6','数据处理',NULL,2);
insert into Course values ('7','PASCAL语言','6',4);
insert into Course values ('5','数据结构','7',4);
insert into Course values ('1','数据库','5',4);
insert into Course values ('3','信息系统','1',4);
insert into Course values ('4','操作系统','6',3);

create table SC (
Sno varchar(12),
Cno varchar(4),
Grade smallint,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

insert into SC values ('201913138001','1',92);
insert into SC values ('201913138001','2',85);
insert into SC values ('201913138001','3',88);
insert into SC values ('201913138002','2',90);
insert into SC values ('201913138002','3',80);

select * from Student;
select * from Course;
select * from SC;

desc Student;
desc Course;
desc SC;

1.查询指定列

查询全体学生的学号与姓名

select Sno,Sname from Student;

查询全体学生的姓名，学号，所在系

select  Sname,Sno,Sdept from Student;

2.查询全部列

select * from Student;

等价于

select Sno,Sname,Ssex,Sage,Sdept from Student;

3.查询经过计算的值

查询全体学生的姓名及其出生年份

select Sname,2021-Sage from Student;

查询全体学生的姓名，出生年份和所在院系，要求用小写字母表示系名

select Sname,'Year of Birth:',2021-Sage,LOWER(Sdept) from Student;

取别名

select Sname,'Year of Birth:' BIRTH,2021-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT from Student;

二、选择表上的若干元组

1.消除取值重复的行

查询选修了课程的学生学号

select Sno from SC;

使用distinct消除重复的行

select DISTINCT Sno from SC;

默认为ALL

select ALL Sno from SC;

2.查询满足条件的元组

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符
确定范围	BETWEEN AND,NOT BETWEEN AND
确定集合	IN,NOT IN
字符匹配	LIKE,NOT LIKE
空值	IS NULL,IS NOT NULL
多重条件（逻辑运算）	AND,OR,NOT

1）比较大小

查询计算机科学系全体学生名单

select Sname from Student where Sdept='CS';

查询所有年龄在20岁以下的学生姓名

select Sname,Sage from Student where Sage<20;

查询考试成绩不及格的学生学号

select DISTINCT Sno from SC where Grade<60;

90及以上

select DISTINCT Sno from SC where Grade>=90;

2）确定范围

谓词between…and…

查询年龄在18-19岁，包括18和19岁，之间的学生姓名，系别和年龄

select Sname,Sdept,Sage from Student where Sage between 18 and 19;

查询年龄不在18-19岁之间的学生姓名，系别和年龄

select Sname,Sdept,Sage from Student where Sage not between 18 and 19;

3）确定集合

谓词in

查询计算机科学系(CS)，数学系(MA)学生的姓名和性别

select Sname,Ssex from Student where Sdept in ('CS','MA');

查询既不是计算机科学系也不是数学系的学生的姓名和性别

select Sname,Ssex from Student where Sdept not in ('CS','MA');

4）字符匹配

谓词like

查询学号为201913138001的学生的详细情况

select * from Student where Sno like '201913138001';

等价于

select * from Student where Sno='201913138001';

查询所有姓刘的学生的姓名、学号和性别

SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE '刘%';

查询姓“欧阳”且全名为三个汉字的学生的姓名

SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳_';

PS:数据库字符集为ASCII时一个汉字需要两个_;当字符集为GBK时只需要一个_。

查询名字中第二个字为“阳”的学生的姓名和学号

SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '_阳%';

查询所有不姓刘的学生的姓名、学号和性别

SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';

PS:如果用户本身查询的字符串本身就含有通配符%或_，这时就要使用ESCAPE’<换码字符>'短语对通配符进行转义了。

查询DB_Design课程的课程号和学分

SELECT Cno,Ccredit
FROM C
WHERE Cname LIKE 'DB\_Design' ESCAPE '\';

查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况

SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i__' ESCAPE'\';

PS:这里的匹配串为“DB_%i__”。第一个_前面有换码字符\，所以它被转义为普通_字符。而i后面的两个_的前面均没有换码字符\，所以它们仍作为通配符。

查询缺少成绩的学生的学号和相应的课程号

SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;

/分数Grade是空值/
PS:此处的“IS”不能用等号（=）代替。

查询所有有成绩的学生学号和课程号

SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL; 

查询计算机科学系年龄在20岁以下的学生姓名

SELECT Sname
FROM Student
WHERE Sdept='CS' AND Sage<20;

例3.27中的IN谓词实际上是多个OR运算符的缩写，因此该例（查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别）中的查询也可以用OR运算符写成如下等价形式：

SELECT Sname，Ssex
FROM Student
WHERE Sdept='CS' OR Sdept='MA' OR Sdept='IS';

查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;

查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列

SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;

查询学生总人数

SELECT COUNT(*)
FROM Student;

查询选修了课程的学生人数

SELECT COUNT(DISTINCT Sno)
FROM SC;

计算选修1号课程的学生平均成绩

SELECT AVG(Grade)
FROM SC
WHERE Cno='1';

查询选修1号课程的学生最高分数

SELECT MAX(Grade)
FROM SC
WHERE Cno='1';

查询学生20125012选修课程的总学分数

SELECT SUM(Grade)
FROM SC,Course
WHERE Sno='20125012'AND SC.Cno=Course.Cno;

当聚集函数遇到空值时，除COUNT(*)外，都跳过空值而只处理非空值。
聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。

分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

求各个课程号及相应的选课人数

SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;

查询选修了三门以上课程的学生学号

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;

查询平均成绩大于等于90分的学生学号和平均成绩。
由于WHERE子句中是不能用聚集函数作为条件表达式的，正确的查询语句如下：

SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

三、连接查询

1.等值与非等值连接查询

连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为

[<表名1>.] <列名1><比较运算符> [<表名2>.] <列名2>

连接谓词还能使用下面形式：

[<表名1>.] <列名1>BETWEEN [<表名2>.] <列名2>AND[<表名2>.] <列名3>

当连接运算符为=时，称为等值连接。使用其它运算符为非等值连接。

连接谓词中的列名称为连接自身字段。连接条件中的各连接字段类型必须是可比的，名字不必相同。

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

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;                        /*将Student与SC中同一学生的元组连接起来*/

[例3.50] 对例3.49用自然连接完成(把目标中重复的属性列去掉则为自然连接)

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

[例3.51]查询选修2号课且成绩在90分以上的所有学生的学号和姓名。

SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND                        /*连接谓词*/
SC.Cno='2' AND SC.Grade>90;          /*其他限定条件*/

2.自身连接

查询每一门课的间接先修课（即先修课的先修课）

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

3.外连接(改写例3.49，把Student的悬浮元组保存在结果关系中)

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
/*也可以使用USING来去掉结果中的重复值：FROM Student LEFT OUTER JOIN SC USING(Sno);*/

4.多表连接

[例3.54]查询每个学生的学号，姓名，选修的课程名及成绩。

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

四、嵌套查询

例如：

SELECT Sname

FROM Student

WHERE Sno IN

(

​ SELECT Sno

​ FROM SC

​ WHERE Cno=‘2’

);

1.带有谓词in的子查询

查询与“刘星”在同一个系学习的学生

select Sno,Sname,Sdept from Student where Sdept in
(select Sdept from Student where Sname='刘星');

或自身连接

select S1.Sno,S1.Sname,S1.Sdept from Student S1,Student S2
where S1.Sdept=S2.Sdept and S2.Sname='刘星';

查询选修了课程名为“信息系统”的学生学号和姓名

select Sno,Sname from Student where Sno in
(select Cno from Course where Cname='信息系统');

或连接查询

select Student.Sno,Sname from Student,SC,Course
where Student.Sno=SC.Sno and
SC.Cno=Course.Cno and
Course.Cname='信息系统';

2.带有比较运算符的子查询

查询与“刘星”在同一个系学习的学生

用=代替in

select Sno,Sname,Sdept from Student where Sdept =
(select Sdept from Student where Sname='刘星');

找出每个学生超过他自己选修课程平均成绩的课程号

select Sno,Cno from SC X
where Grade >=(select AVG(Grade) from SC y where y.Sno=x.Sno)

3.带有ANY(SOME)或ALL谓词的子查询

谓词	语义
>ANY	大于子查询结果的中的某个值
>ALL	大于子查询结果的中的所有值
<ANY	小于子查询结果的中的某个值
<ALL	小于子查询结果的中的所有值
>=ANY	大于等于查询结果的中的某个值
>=ALL	大于等于查询结果的中的所有值
<=ANY	小于等于查询结果的中的某个值
<=ALL	小于等于查询结果的中的所有值
=ANY	等于子查询结果的中的某个值
=ALL	等于子查询结果的中的所有值
!=(或<>)ANY	不等于子查询结果的中的某个值
!=(或<>)ALL	不等于子查询结果的中的所有值

查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

select Sname,Sage from Student
where Sage<ANY(select Sage from Student where Sdept='CS')
and Sdept<>'CS';

或者用聚集函数

select Sname,Sage from Student
where Sage < (select MAX(Sage) from Student where Sdept='CS')
and Sdept<>'CS';