计算机二级MySQL笔记（第四章 数据查询）

---

单表查询

---

选择字段

--查询所有字段
select * from tb_student;
--查询指定字段
select classNo, department from tb_class;
--查询经过计算的列
select sutudentName, sex, 'Age:', year(now())-year(birthday) from tb_student;	--select后可以是字段、字符串常量、函数
--定义字段别名
select studentName '姓 名',sex as 性别,year(now())-year(birthday) 年龄 from tb_student;	--自定义别名中含有空格，需通单引号。as为可选参数

选择指定记录

--比较大小
select courseName from tb_course where courseHour>=48;	--等价于'not courseHour<48'
select studentName from tb_student where nation != '汉';  --等价于'not nation='汉''

--带between...and关键字的范围查询
select studentName,birthday from tb_student where birthday between '1997-01-01' and '1997-12-31';

--带in关键字的集合查询
select * from tb_student where native in ('北京','天津','上海');

--带like关键字的字符串匹配查询
select studentNo from tb_student where studentName like '王%';	--% 代表任意长度字符串，无法匹配空值
select studentNo from tb_student where studentName like '李_';	--_代表任意单个字符

--带is null关键字的空值查询
select * from tb_course where courseNo is null;		--反义是'is not null'，不可用'='代替

--带and或or的多条件查询
select courseName,credit,courseHour from tb_course where credit>=3 and courseHour>32;
select studentName,native from tb_student where native='北京' or native='上海';	--此处等价于'in ('北京','上海')' 

对结果进行排序

--查询tb_score表中的大于85分的记录，按学号升序排序，按成绩降序排序。
select * from tb_score where score>85 order by studentNo, score desc;

限制查询结果的数量

--查询成绩排名第3到第5的学生学号、课程号和成绩
select studentNo,courseNo,score from tb_score order by score desc limit 3 offset 2;
--语法：limit 行数 offset 位置偏移量
--代码含义：从第2个位置开始，返回3条记录。即第3到第5条记录

---

分组聚合查询

---

--查询平均分在80分以上的每个同学的选课门数、平均分、和最高分
select studentNo, count(*) 选课门数, avg(score) 平均分, max(score) 最高分 
from tb_score 
group by studentNo
having avg(score)>=80;  
--注：
--group by 子句中的字段必须为有效的表达式，不能是聚合函数，也不能使用别名。
--除聚合函数外，select中的每个列都必须在group by子句中给出。
--NULL值会单独作为一个分组。

---

连接查询（多表）

---

交叉连接（cross join）

--cross join又称笛卡尔积，返回的是两个表记录行数的乘积,实际很少用。
select * from tb_student cross join tb_score;
或
select * from tb_student, tb_score;

内连接（inner join）

-- 查询“会计学院”全体同学的学号、姓名、籍贯、班级编号和所在班级名称
-- 默认内连接，所以 inner 可省略
-- 两种写法均可
select studentNo, studentName, native, courseNo, className
from tb_student join tb_class
on tb_student.classNo = tb_class.classNo
where department='会计学院';
或
select studentNo, studentName, native, courseNo, className
from tb_student, tb_class
where tb_student.classNo = tb_class.classNo 
and department='会计学院';


--查询课程名称为“程序设计”的学生学号、姓名和成绩
--多表字段相同时，可以指定别名，简化代码
select a.studentNo, studentName, score
from tb_student as a, tb_course as b, tb_score as c
where a.studentNo=c.studentNo and b.courseNo=c.courseNo
and courseName='程序设计';
或
select a.studentNo, studentName, score
from tb_student as a join tb_course as b join tb_score as c
on a.studentNo=c.studentNo and b.courseNo=c.courseNo
where courseName='程序设计';

自连接

--自连接是内连接的一种特殊形式：表与自身连接
--必须指定别名，否则查询失败
--查询“数据库”这门课学分相同的课程信息
select c1.*
from tb_course c1, tb_course c2
on c1.credit=c2.credit
where c2.courseName='数据库';
或
select c1.*
from tb_course c1, tb_course c2
where c1.credit=c2.credit
and c2.courseName='数据库';

外连接（outer join）

--左外连接（left outer join）:左边所有记录 + 右边满足条件的距离
select a.studentNo, studentName, sex, classNo, courseNo, score
from tb_student a left outer join tb_score b
on a.studentNo=b.studentNo;
--右外连接（right outer join）:右边所有记录 + 左边满足条件记录，左外连接的对立

---

子查询

---

子查询

--带 in 关键字的子查询
select studentName
from tb_student
where tb_student.studentNo in (seclect distinct tb_score.studentNo from tb_score);	--查询了选修课程的学生姓名
--可用内连接查询改写
select studentName
from tb_student a join tb_score b
on a.studentNo = b.studentNo;

--带比较运算符的子查询
select studentNo,studentName from tb_student
where classNo = (select classNo from tb_class where className='计算机');	--查询计算机班所有学生的学号、姓名
--可用内连接查询改写
select studentNo,studentName 
from tb_student a join tb_class b
on a.classNo=b.classNo
where className='计算机';

--带exists关键字的子查询
select studentName from tb_student a
where exists (select * from tb_score b where a.studentNo=b.studentNo and courseNo='31002');
--可用 in 关键字改写
select studentName from tb_student
where studentNo in (select studentNo from tb_score where courseNo='31002');

---

联合查询

---

--查询选修了管理学或计算机基础的学生学号
select studentNo from tb_score a, tb_course b
where a.courseNo=b.courseNo and courseName='管理学'
union
select studentNo from tb_score a, tb_course b
where a.courseNo=b.courseNo and courseName='计算机基础';
--unoin:去除重复
--union all:不去除重复

---

课后习题

---

1、查询供应零件号为P1的供应商号码

select SNO from SP where PNO='P1';

2、查询供货量在300~500之间的所有供货情况

select * from SP where QTY between 300 and 500;

3、查询供应红色零件的供应商号码和供应商名称

select S.SNO, S.SNAME 
from S join P join SP
on S.SNO=SP.SNO and P.PNO=SP.PNO
where P.COLOR='Red';

4、查询重量在15以下，Paries供应商供应的零件代码和零件名

select P.PNO, P.PNAME 
from P join S join SP
on S.SNO=SP.SNO and P.PNO=SP.PNO
where S.CITY='Paris' and P.WEIGHT<=15;

5、查询由London供应商供应的零件名称

select PNAME 
from P join S join SP
on S.SNO=SP.SNO and P.PNO=SP.PNO
where CITY='London';

6、查询不供应红色零件的供应商名称

select SNAME from S
where SNO not in
(select SNO from SP, P where P.PNO=SP.PNO and COLOR='Red');

7、查询供应商S3没有供应的零件名称

select PNAME from P
where PNO not in 
(select PNO from SP where SNO='S3');

8、查询供应零件代码为P1和P2两种零件的供应商名称

select SNAME from S join SP
on S.SNO=SP.SNO
where PNO='P1' and S.SNO in
(select SNO from SP where PNO='P2');

9、查询与零件名Nut颜色相同的零件代码和零件名称

select P1.PNO, P1.PNAME 
from P P1 join P P2
on P1.COLOR=P2.COLOR
where P2.PNAME='Nut' and P1.PNAME != 'Nut';

10、查询供应了全部零件的供应商名称

select SNO from S
where not exists
(select * from P
where not exists
(select * from SP
where S.SNO=SP.SNO and SP.PNO=P.PNO));