五天搞定Mysql基础知识-Day04

畅畅菜鸟

已于 2024-06-11 16:26:12 修改

阅读量1k

点赞数 32

文章标签： mysql 数据库

于 2024-06-11 16:25:05 首次发布

本文链接：https://blog.csdn.net/weixin_46095822/article/details/139602035

版权

学习目标：

1、掌握内连接

2、掌握左连接和右连接

3、掌握自关联和子查询·

第一章数据准备

一、创建表，并向表插入数据

第二章连接查询

一、基本概念

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

2、连接查询可以通过链接运算符（连接条件）可以实现多个表查询；

3、内连接：查询的结果只显示两个表中满足链接条件的部分；

4、左连接：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null填充；

5、右连接：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null填充；

二、内连接

语法一：

select * from 表1

inner join 表2 on 表1.字段 = 表2.字段

内连接最重要的是找对两张表要关联的字段

-- 创建表 a

DROP TABLE if EXISTS a;

CREATE TABLE a(

id INT,

name VARCHAR(10)

);

-- 向表a插入数据

INSERT INTO a VALUES

('1', '张三'),

('2', '李四'),

('3', '王五');

-- 创建表 b

DROP TABLE if EXISTS b;

CREATE TABLE b(

id INT,

score int

);

-- 向表 b 插入数据

INSERT INTO b VALUES

('1', '30'),

('1', '40'),

('2', '50'),

('2', '60');

SELECT * FROM a INNER JOIN b ON a.id = b.id; （查询结果如下图）

查询结果列说明：

表a和表b的列都会出现在查询结果中，由于表a和表b都有列名叫id，为了区分，表b的id列名显示为id1

查询结果行说明：

记录只保留表a和表b中id字段相同的记录。

表students和score表内连接查询结果：

SELECT * FROM students INNER JOIN scores ON students.studentNo = scores.studentNo;

语法二：隐式内连接

select * from 表1, 表2

where 表1.字段 = 表2.字段

查询学生信息和成绩，只显示有成绩的学生：

SELECT * FROM students, scores WHERE students.studentNo = scores.studentNo;

选择部分字段：

-- 查询学生信息，要求只显示姓名、课程号、成绩

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 name, courseNo, score FROM students s1

INNER JOIN scores s2 ON s1.studentNo = s2.studentNo

WHERE s1.name = '王昭君';

带有and逻辑运算符的内连接查询：

-- 查询姓名为王昭君，且成绩小于90的信息，要求只显示姓名，成绩

SELECT name, score FROM students s1

INNER JOIN scores s2 ON s1.studentNo = s2.studentNo

WHERE s1.name = '王昭君' AND s2.score < 90;

多表内连接查询：

-- 查询学生信息和成绩以及成绩对应的课程名称

SELECT * FROM students s

INNER JOIN scores sc ON s.studentNo = sc.studentNo

INNER JOIN courses cs ON sc.courseNo = cs.courseNo;

练习：

练习2：查询所有学生的linux课程成绩，要求只显示姓名，成绩，课程名

SELECT name, score, coursename FROM students

INNER JOIN scores ON students.studentNo = scores.studentNo

INNER JOIN courses ON scores.courseNo = courses.courseNo

WHERE courses.coursename = 'Linux';

写SQL三步法：

1、搭框架：

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

2、看条件：

决定where后面的具体条件

3、显示的字段

select后面到底要显示什么字段

4、写sql时，写完一句运行一句吗、，避免出错

带有order by排序的内连接查询

-- 查询成绩最高的男生信息，要求只显示姓名，课程名，成绩、

SELECT name, coursename, score FROM students s

INNER JOIN scores sc on s.studentNo = sc.studentNo

INNER JOIN courses cs ON sc.courseNo = cs.courseNo

WHERE s.sex = '男'

ORDER BY sc.score DESC LIMIT 1;

三、左连接

语法：

select * from 表1

left join 表2 on 表1.字段 = 表2.字段

-- 例1：查询所有学生的信息以及成绩，包括没有成绩的学生

SELECT * FROM students

LEFT JOIN scores ON students.studentNo = scores.studentNo;

四、右连接

语法：

select * from 表1

right join 表2 on 表1.字段 = 表2.字段

-- 例1：查询所有课程信息，包括没有成绩的课程

SELECT * FROM scores

RIGHT JOIN courses ON scores.courseNo = courses.courseNo;

多表联合查询，同名字段的处理方式：

如果一条 select 要用到多个表，表中有相同字段名，就需要表名.字段名加以区分

SELECT students.studentNo FROM students

INNER JOIN scores ON students.studentNo = scores.studentNo;

第三章自关联

一、背景思考

1、设计省信息的表结构 provinces：

proid：省编号

pname：省名称

2、设计市信息的表结构 citys：

cityid：市编号

cname：市名称

proid：市所属的省编号；

3、citys 表的 proid表示城市所属的省，对应着 provinces 表的 proid 值；

4、问题：能不能将两个表合成一张表呢？

5、思考：观察两张表发现：citys 表比 provinces 表多一个字段 proid，其他字段的类型都是一样的；

6、意义：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大；

7、答案：定义表 areas，结构如下：

id；

name；

pid；

8、表中的一条记录，可以省略省，也可以省略市

9、记录为省的时候，字段说明：

id：代表省id

name：代表省名称

pid：因为省没有所属的省份，所以pid为null

10、记录为市的时候，字段说明：

id：代表市id

name：代表市名称

pid：代表市所属省id

11、这就是自关联，表中的某一字段，关联了这个表中的另外一个字段，但是他们的业务逻辑含义是不一样的，城市信息 pid 引用的是省信息的id；

12、在这个表中，结构不变，以后还可以扩充区县、乡镇街道、村社区等信息

二、创建区域表 areas，向表插入数据

-- 例1：查询一共有多少个省

SELECT COUNT(*) FROM areas WHERE pid IS NULL;

-- 例2：查询一共有多少个市

SELECT COUNT(*) FROM areas WHERE pid IS NOT NULL;

自关联是同一张表做链接查询；自关联下，一定能找到同一张表可关联的不同字段

-- 例3：查询广东省所有城市

SELECT * FROM areas a1

INNER JOIN areas a2 ON a1.id = a2.pid

WHERE a1.NAME = '广东省';

第四章子查询

一、定义

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

二、主查询

外层的第一条 select 语句为主查询

三、主查询和子查询的关系

子查询是嵌入到主查询中

子查询是辅助主查询的，要么充当条件，要么充当数据源

子查询是可以独立存在的语句，是一条完整的 select 语句；

-- 例1：查询大于平均年龄的学生记录

SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);

标量子查询：例1中，子查询返回结果只有一个值（一行一列），这种称为标量子查询

-- 例2：查询30岁的学生的成绩

SELECT score FROM scores WHERE studentNo IN (SELECT studentNo FROM students WHERE age = 30);

列子查询：例2中，子查询返回的结果是一列（一列多行），这种称之为列子查询

-- 例3：用子查询，查询所有女生的信息和成绩

SELECT * FROM(SELECT * FROM students WHERE sex='女')stu

INNER JOIN scores sc ON stu.studentNo = sc.studentNo;

表级子查询：例3中，子查询返回的结果是多行多列（一个表），这种称之为表级子查询

数据准备:

/* 如果区域表areas存在，删除areas */
drop table if exists areas;

/* 创建区域表areas */
create table areas(
id int primary key,
name varchar(20),
pid int);

/* 向区域表areas插入数据 */
insert into areas
values ('130000', '河北省', NULL),
('130100', '石家庄市', '130000'),
('130400', '邯郸市', '130000'),
('130600', '保定市', '130000'),
('130700', '张家口市', '130000'),
('130800', '承德市', '130000'),
('440000', '广东省', NULL),
('440300', '深圳市', '440000'),
('440100', '广州市', '440000'),
('440500', '汕头市', '440000'),
('440600', '佛山市', '440000'),
('440200', '韶关市', '440000');

1.1 数据说明

1、部门表 departments；

部门表的 deptid 字段与员工表的 deptid 字段关联

2、员工表 employees

员工表 empid 与工资表的 empid 字段关联

leader 字段为空代表领导岗

3、工资表 salary

1.2 建表与数据插入

练习：

-- 1.1 列出男职工的总数和女职工的总数

SELECT sex, COUNT(*) FROM employees GROUP BY sex;

-- 1.2 列出非党员职工的总数