数据库常用命令（三）

新建一张courses课程表
DROP TABLE IF EXISTS courses;
CREATE TABLE courses (
courseNo INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
);

INSERT INTO courses VALUES(0,'数据库'),
(0,'QTP'),
(0,'python'),
(0,'Linux'),
(0,'功能测试'),
(0,'自动化测试'),
(0,'性能测试');

新建一张scores成绩表
DROP TABLE IF EXISTS scores;
CREATE TABLE scores (
id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
courseNo INT(10),
studentNo VARCHAR(10),
score TINYINT(4)
);

INSERT INTO scores VALUES(0,1,001,90),(0,1,002,75),(0,2,002,98),(0,3,001,86),(0,3,003,80),(0,4,004,79),(0,5,005,96),(0,6,006,80)

内连接查询---inner join

当查询结果的列源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列表返回；

等值连接查询

查询的结果为两个表匹配到的数据

eg：

SELECT * FROM courses,scores-----先将两张表拼接成一张表
SELECT * FROM courses,scores WHERE courses.`courseNo`=scores.`courseNo`-----添加条件过滤正确组合
SELECT * FROM courses AS c,scores AS s WHERE c.`courseNo`=s.`courseNo`------给课程表和成绩表起别名

方式一

eg：查询课程信息及学生的成绩
SELECT c.courseNo AS 课程表课程表号,c.name AS 课程名,s.courseNo AS 成绩表课程号,s.score AS 成绩表成绩
FROM courses AS c,scores AS s  WHERE c.`courseNo`=s.`courseNo`

方式二内连接

• 语法：select * from 表1
• inner join 表2 on 表1.列=表2.列

eg：

SELECT * FROM courses INNER JOIN scores ON courses.`courseNo`=scores.`courseNo`
SELECT * FROM courses AS c INNER JOIN scores AS s ON c.`courseNo`=s.`courseNo`
SELECT c.name AS 课程名称,s.score AS 成绩表成绩 FROM courses AS c INNER JOIN scores AS s ON c.`courseNo`=s.`courseNo`

查询结果（一）

查询结果（二）

三张表联查

方式一 ----where

eg：查询学生信息及学生的课程对应的成绩----用where过滤
SELECT * FROM students ,courses ,scores  WHERE students.`studentsNo` = scores.`studentNo` AND scores.`courseNo`= courses.`courseNo`
SELECT * FROM students AS stu,courses AS c,scores AS s WHERE stu.`studentsNo` = s.`studentNo` AND s.`courseNo` = c.`courseNo`

SELECT stu.name AS 学生姓名,c.name AS 课程名,s.score AS 成绩 
FROM students AS stu,courses AS c,scores AS s 
WHERE stu.`studentsNo` = s.`studentNo` AND s.`courseNo` = c.`courseNo`

查询结果

 

方式二---inner join

语法：select * from 表名1 inner join 表名2 on查询条件

eg：查询学生信息及学生的课程对应的成绩

SELECT * FROM students AS stu 
INNER JOIN scores AS s ON stu.`studentsNo` = s.`studentNo`
INNER JOIN courses AS c ON s.`courseNo` = c.`courseNo`

as 给列起别名

SELECT stu.name 姓名,c.name 课程名,s.score 成绩 FROM students AS stu 
INNER JOIN scores AS s ON stu.`studentsNo` = s.`studentNo`
INNER JOIN courses AS c ON s.`courseNo` = c.`courseNo`

查询结果

 

查询王昭君的成绩，要求显示姓名、课程号、成绩
select stu.name 姓名,c.name 课程名,s.score 成绩 from students as stu
inner join scores as s on stu.`studentsNo`=s.`studentNo`
inner join courses as c on s.`courseNo`=c.`courseNo`
where stu.name='王昭君'

查询结果

eg：查询王昭君的数据库成绩，要求显示姓名、课程名、成绩
SELECT stu.name 姓名,c.name 课程名,s.score 成绩 FROM students AS stu
INNER JOIN scores AS s ON stu.studentsNo=s.studentNo
INNER JOIN courses AS c ON s.courseNo=c.courseNo
WHERE stu.name='王昭君' AND c.name = '数据库'

查询结果

方式一-----inner join 表名 on 查询条件+where过滤条件

eg：查询男生中最高成绩，要求显示姓名、课程名、成绩
SELECT stu.name 姓名,c.name 课程名,s.score 成绩 FROM students AS stu
INNER JOIN scores AS s ON stu.studentsNo=s.studentNo
INNER JOIN courses AS c ON s.courseNo=c.courseNo
WHERE stu.sex='男' ORDER BY s.score DESC LIMIT 1

方式二-----用where 查询
SELECT stu.name,c.name,s.score FROM students AS stu,scores AS s,courses AS c WHERE stu.studentsNo = s.studentNo AND s.courseNo = c.courseNo
AND stu.sex='男'
ORDER BY s.score DESC
LIMIT 1

查询结果

 

左连接查询

• 查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null
• 左连接，返回左表全部，右表无匹配项则返回null

eg：查询所有学生的成绩，包括没有成绩的，要求显示姓名、课程号、成绩
SELECT stu.name,c.name,s.score FROM students AS stu
LEFT JOIN scores AS s ON stu.`studentsNo`=s.`studentNo`
LEFT JOIN courses  AS c ON s.`courseNo`=c.`courseNo`

查询结果

右连接查询

• 查询的结果为两个匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null
• 右连接，返回右表全部，左右无匹配项则返回null

插入数据

INSERT INTO courses 
VALUES(0,'黑盒测试'),
(0,'白盒测试')

eg：查询所有课程的成绩，包括没有成绩的课程
SELECT s.score,c.name FROM scores AS s
RIGHT JOIN courses AS c ON s.`courseNo`=c.`courseNo`

查询结果

eg：查询所有课程的成绩，包括没有成绩的课程,包括学生信息 s.score,c.name
SELECT  stu.name,s.score,c.name FROM scores AS s
RIGHT JOIN courses AS c ON s.`courseNo`=c.`courseNo`
LEFT JOIN students AS stu ON stu.`studentsNo`=s.`studentNo`

查询结果

 

自关联----数据有层级关系时使用，虚拟出多张表

 

新增表
CREATE TABLE areas(
aid INT PRIMARY KEY,
atitle VARCHAR(20),
pid INT
)

表中插入数据

INSERT INTO areas VALUES
('130000','河北省',NULL),
('130100','石家庄市','130000'),
('130400','邯郸市','130000'),
('130600','保定市','130000'),
('130700','廊坊市','130000'),
('130800','张家口市','130000'),
('130900','承德市','130000'),
('410000','河南省',NULL),
('410100','新乡市','410000'),
('410200','洛阳市','410000'),
('410300','郑州市','410000'),
('410400','安阳市','410000'),
('410500','焦作市','410000'),
('410600','信阳市','410000')

 

eg：查询一共有多少个省---查询个数用count（*）

SELECT COUNT(*)FROM areas

WHERE pid IS NULL

查询结果

插入郑州市下面的区
INSERT INTO areas VALUES
('210001','新乐区','410300'),
('210002','都乐区','410300'),
('210003','中原区','410300')

eg：查询郑州所有区
SELECT * FROM areas AS p
INNER JOIN areas AS a ON p.aid = a.pid
WHERE p.atitle ='郑州市'

查询结果

eg：查询河南省所有区县
SELECT sheng.atitle AS '省',qu.atitle AS '区',xian.atitle AS '县' FROM areas AS sheng
INNER JOIN areas qu ON sheng.aid=qu.pid
INNER JOIN areas AS xian ON qu.aid=xian.pid
WHERE sheng.atitle='河南省'

查询结果

 

eg：查询河南省所有区县,包括没有县的区
SELECT sheng.atitle AS '省',qu.atitle AS '区',xian.atitle AS '县' FROM areas AS sheng
INNER JOIN areas qu ON sheng.aid=qu.pid
LEFT JOIN areas AS xian ON qu.aid=xian.pid
WHERE sheng.atitle='河南省'

查询结果

 

子查询定义

• 在一个select语句中，嵌入了另外一个select语句，那么被嵌入的select语句称之为子查询语句

主查询

• 主要查询的对象，第一条select语句

主查询和子查询的关系

• 子查询是嵌入到主查询中
• 子查询是辅助查询的，要么充当条件，要么充当数据源
• 子查询时可以独立存在的语句，是一条完整的select语句

子查询分类

• 标量子查询：子查询返回的结果是一个数据（一行一列）
• 列级子查询：子查询返回的结果是一列（一列多行）
• 行子查询：子查询返回的结果是一行（一行多列）
• 表级子查询：子查询返回的结果是多行多列

标量子查询（子查询返回单个值，一行一列）

eg：查询班级学生的平均年龄
SELECT AVG(age) AS 平均年龄 FROM students

eg：查询大于平均年龄的学生
select * FROM students WHERE age >22----子查询
SELECT * FROM students WHERE age>(SELECT AVG(age) FROM students)

查询结果

eg：查询王昭君的成绩，要求只显示成绩
先查找王昭君的学号----子查询
SELECT students.`studentsNo` FROM students WHERE NAME='王昭君'
通过王昭君的学号再查找她的成绩
SELECT * FROM scores WHERE studentNo=(SELECT students.`studentsNo` FROM students WHERE NAME='王昭君')

查询结果

列级子查询（子查询返回的结果时一列多行）

eg：查询18岁的学生的成绩，要求只显示成绩
SELECT students.`studentsNo` FROM students WHERE age=18-----子查询返回的结果是一列多行
SELECT * FROM scores WHERE studentNo IN (SELECT students.`studentsNo` FROM students WHERE age=18)

查询结果

行级子查询（子查询返回的结果时一行多列）

eg：查询男生中年龄最大的学生信息
方式（一）
SELECT MAX(age) FROM students WHERE sex='男'
SELECT * FROM students WHERE age=(SELECT MAX(age) FROM students WHERE sex='男')

方式（二）
SELECT MAX(age) FROM students WHERE sex='男'
SELECT * FROM students WHERE (sex,age)=('男',31)

方式（三）
SELECT sex,age FROM students WHERE sex ='男' ORDER BY age DESC LIMIT 1   ------子查询 
SELECT * FROM students WHERE (sex,age)=(SELECT sex,age FROM students WHERE sex ='男' ORDER BY age DESC LIMIT 1)

 查询结果

表级子查询（子查询返回的结果时多行多列）

成绩表                                                                              课程表

                      

 

eg：查询数据库和系统测试的课程成绩
方式（一）
SELECT * FROM scores
INNER JOIN courses ON scores.`courseNo`=courses.`courseNo`
WHERE courses.`NAME` IN('数据库','系统测试')
或者写成
WHERE courses.`NAME`='数据库' OR courses.`NAME`='系统测试'

方式（二）
SELECT * FROM courses WHERE NAME IN('数据库','系统测试')----子查询

SELECT * FROM scores AS s
INNER JOIN (SELECT * FROM courses WHERE NAME IN('数据库','系统测试')) AS c
ON s.courseNo=c.courseNo

查询结果

子查询中的特定关键字使用

• in范围

格式：主查询where条件in（列级子查询）

SELECT age FROM students WHERE age BETWEEN 18 AND 20

SELECT * FROM students 
WHERE age IN(SELECT age FROM students WHERE age BETWEEN 18 AND 20)

• any|some 任意一个

1. 格式：主查询 where 列= any（列级子查询）
2. 在条件查询的结果中匹配任意一个即可，等价于in

SELECT * FROM students 
WHERE age = SOME(SELECT age FROM students WHERE age BETWEEN 18 AND 20)

SELECT * FROM students 
WHERE age = ANY(SELECT age FROM students WHERE age BETWEEN 18 AND 20)

大于18/19/20，大于其中最小值
SELECT * FROM students 
WHERE age > ANY(SELECT age FROM students WHERE age BETWEEN 18 AND 20)

小于其中最大值
SELECT * FROM students 
WHERE age < ANY(SELECT age FROM students WHERE age BETWEEN 18 AND 20)

• all

1. 格式：主查询where 列 = all（列级子查询）：等于里面所有
2. 格式：主查询where 列 <>  all（列级子查询）：不等于其中所有

大于18、19、20，大于其中所有值
SELECT * FROM students 
WHERE age > ALL(SELECT age FROM students WHERE age BETWEEN 18 AND 20)

小于18、19、20，小于其中所有值
SELECT * FROM students 
WHERE age < ALL(SELECT age FROM students WHERE age BETWEEN 18 AND 20)

基本不使用
SELECT * FROM students 
WHERE age = ALL(SELECT age FROM students WHERE age BETWEEN 18 AND 20)

用法相当于 NOT IN
SELECT * FROM students 
WHERE age != ALL(SELECT age FROM students WHERE age BETWEEN 18 AND 20)