1连接查询

  • 内连接

    • 把两张表相同的地方查询出来

MySQL图1-9

  • 左连接

    • 包括了内连接,同时还查询左表特有的内容

MySQL图1-10

  • 右连接

    • 包括了内连接,同时还查询右表特有的内容

MySQL图1-11

2.内连接

  • 语法:

    • select * from 表1 inner join 表2 on 表1.字段 = 表2.字段
    • 内连接最重要的是,找对两张表要关联的字段
SELECT * from a INNER JOIN b on a.id = b.id;
  • students表和scores内连接查询结果
SELECT * from students INNER JOIN scores on students.studentNo = scores.studentNo;

MySQL图1-12

  • 隐式内连接语法

    • 语法:select * from 表1,表2 where 两个表的连接条件
-- 隐式内连接
SELECT * from students, scores where students.studentNo = scores.studentNo;
  • 内连接查询,显示指定的字段
-- students表与socres内连接,只显示name 课程号 成绩
SELECT name, courseNo, score from students
INNER JOIN scores on students.studentNo = scores.studentNo;
  • 表的别名在查询中的使用
SELECT name 姓名, courseNo 课程编号, score 成绩 from students st
INNER JOIN scores sc on st.studentNo = sc.studentNo;
  • 课堂练习答案
-- 练习 1: 查询成绩表信息,同时显示成绩对应的课程名称
SELECT * from scores s INNER JOIN courses c on s.courseNo = c.courseNo;
  • 带有where的内连接

    • 语法select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件
-- 例 5:查询王昭君的信息,要求只显示姓名、课程号、成绩
select name, courseNo, score from students s1 
INNER JOIN scores s2 on s1.studentNo = s2.studentNo
where s1.name = '王昭君';
  • 带有and的where条件
-- 例 6:查询姓名为’王昭君’,并且成绩小于 90 的信息,要求只显示姓名、成绩
select name, score from students s1 
INNER JOIN scores s2 on s1.studentNo = s2.studentNo
where s1.name = '王昭君' and s2.score < 90;
  • 多表内连接
-- 例 7:查询学生信息和成绩以及成绩对应的课程名称
SELECT * from students inner join scores on students.studentNo = scores.studentNo
inner join courses on scores.courseNo = courses.courseNo;

写SQL三步法

  • 搭框架

    • 基本的select语句框架搭建起来,如果有多表,把相应的多表也联合进来
  • 看条件

    • 决定where后面的具体条件
  • 显示的字段

    • select后面到底要显示什么字段
-- 练习 2:查询所有学生的’linux’课程成绩,要求只显示姓名、成绩、课程名
-- 第一步:搭框架
-- SELECT * from students INNER JOIN scores 
-- on students.studentNo = scores.studentNo
-- INNER JOIN courses on scores.courseNo = courses.courseNo;
-- 第二步:看条件
-- SELECT * from students INNER JOIN scores 
-- on students.studentNo = scores.studentNo
-- INNER JOIN courses on scores.courseNo = courses.courseNo
-- where courseName = 'linux';
-- 第三步:返回字段名
SELECT name, score, courseName from students INNER JOIN scores 
on students.studentNo = scores.studentNo
INNER JOIN courses on scores.courseNo = courses.courseNo
where courseName = 'linux';
  • 带有order by的联合查询
-- 例 8:查询成绩最高的男生信息,要求显示姓名、课程名、成绩
SELECT name, score, courseName from students INNER JOIN scores 
on students.studentNo = scores.studentNo
INNER JOIN courses on scores.courseNo = courses.courseNo
where sex = '男'
order by score desc LIMIT 1;

3.左连接

  • 语法

    • select * from 表1 left join 表2 on 表1.字段 = 表2.字段
-- 例 1:查询所有学生的信息以及成绩,包括没有成绩的学生
SELECT * from students left JOIN scores ON
students.studentNo = scores.studentNo;

4.右连接

  • 语法

    • select * from 表1 right join 表2 on 表1.字段 = 表2.字段
-- 例 1:查询所有课程的信息,包括没有成绩的课程
SELECT * from scores RIGHT JOIN courses ON
scores.courseNo = courses.courseNo;

5.多表联合查询,同名字段的处理方式

  • 如果一条select要用到多个表,表中有同名字段.就需要 表名.字段名 加以区分
select students.studentNo from students INNER JOIN scores ON
students.studentNo = scores.studentNo;

6.自关联

-- 查询一共有多少个省
SELECT count(*) from areas where pid is null;
-- 查询有多少市
SELECT count(*) from areas where pid is not null;
  • 自关联,是同一张表做连接查询,
  • 自关联下,一定找到同一张表可关联的不同字段
-- 例 2:查询广东省的所有城市
SELECT * from areas a1 INNER JOIN areas a2
on a1.id = a2.pid
WHERE a1.name = '广东省';

7.子查询

  • 子查询是嵌套到主查询里面的
  • 子查询做为主查询的数据源或者条件
  • 子查询是独立可以单独运行的查询语句
  • 主查询不能独立独立运行,依赖子查询的结果
-- 例 1:查询大于平均年龄的学生记录
-- SELECT avg(age) from students;
-- 
-- select * from students where age > 30.1667;

-- 用子查询实现
select * from students where age > (SELECT avg(age) from students);
  • 标量子查询------子查询返回结果只有一行,一列
-- 例 2:查询 30 岁的学生的成绩
-- 1,查询30岁学生的studentNO
-- select studentNo from students where age = 30;
-- 
-- SELECT * from scores where studentNo in ('001', '003', '011');

-- 用子查询实现
SELECT * from scores where studentNo in 
(select studentNo from students where age = 30);
  • 列子查询------子查询返回一列多行
-- 例 3:用子查询,查询所有女生的信息和成绩
-- 用内连接实现
SELECT * from students INNER JOIN scores ON
students.studentNo = scores.studentNo
where sex = '女';
-- 用子查询实现
select * from (SELECT * from students where sex = '女') stu
INNER JOIN scores sc on stu.studentNo = sc.studentNo;
  • 表级子查询------子查询返回结果为多行,多列
  • 课件提问问题及答案
-- 查询各个年龄段学生的数量,按照数量从大到小排序
select age, count(*) from students GROUP BY age ORDER BY count(*) desc;

-- 查找年龄大于等于25,小于等于30的男同学
select * from students where (age BETWEEN 25 and 30) and sex = '男';

-- 显示2班和3班的女同学
SELECT * from students where sex = '女' and class in ('2班', '3班');

-- 显示所有有效的card学生记录,null和''都是无效的
SELECT * FROM students where card is not null and card != '';


-- 查找老家不在河北和北京的同学的age总和
SELECT sum(age) from students where hometown <> '北京' and hometown <> '河北'; 
SELECT sum(age) from students where not hometown in ('北京', '河北');

-- 查找不姓'白'的同学
SELECT * from students where not name like '白%';

-- 查询每个班有分别多少同学
SELECT class, count(*) from students GROUP BY class;

-- 这个sql能不能显示年龄最大的同学的姓名
select name,max(age) from students;

-- 显示年龄最大的同学的姓名
SELECT name from students order by age desc limit 1;
SELECT name from students where age = (select max(age) from students);

-- 查询年龄最大的女同学和年龄最小的女同学差多少岁
SELECT max(age) -  min(age) from students where sex = '女';

-- 分别求男女同学的平均年龄
select sex,avg(age) from students GROUP BY sex;

-- 只显示班级人数为3个同学的班级名称
SELECT class from students GROUP BY class HAVING count(*) = 3; 

--  查询姓名只有2个字的女同学的数量
SELECT count(*) from students where sex = '女' and name like '__';

-- 查询年龄大于25,老家不在河北的男同学
SELECT * from students where age > 25 and hometown != '河北' and sex = '男' ;

-- 查询1班加3班所有同学的年龄总和
SELECT sum(age) from students where class = '1班' or class = '3班';
END
本文作者: 文章标题:MySQL学习笔记(三)
本文地址:https://plaidweb.cn/Test_basis/019.html
版权说明:若无注明,本文皆时光荏苒web原创,转载请保留文章出处。
最后修改:2022 年 06 月 18 日
感谢您的赞赏,将用于联合国儿童基金会及服务器维护