数据库-MySQL（六）

一 、 数据库介绍

1.1 什么是数据库

数据库顾名思义就是存储数据的仓库， 本质就是一个文件系统， 数据是按照特定的格式将数据存储起来的。

1.2 作用

可以方便的对文件进行增删改查操作
如何对文件进行增删改查？
通过一些控制命令进行操作

为什么要学习数据库？
原始IO操作：

数据库的操作

二 、数据库安装和配置

安装前准备：

为了保证安装过程的顺利进行，请将杀毒软件关闭。360、腾讯管家退出
为了保证MySQL的正常使用，请将防火墙也一并关闭

2.1 压缩式安装

点我下载压缩版安装包

安装步骤：可参考

1. 下载后得到zip压缩包.

2. 解压到自己想要安装到的目录，本人解压到的是D:\develop\mysql\mysql-5.7.34

3. 添加环境变量：我的电脑->属性->高级->环境变量

4. 选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹（如：D:\develop\mysql\mysql-5.7.34\bin）

5. 编辑mysql-5.7.34下的my.ini （自己新建）文件 ,注意替换路径位置

   [mysqld]
   basedir=D:\Program Files\mysql-5.7\
   datadir=D:\Program Files\mysql-5.7\data\
   port=3306
   skip-grant-tables
   

6. 必须启动管理员模式下的CMD，并将路径切换至mysql下的bin目录（打开黑窗口cd /d粘贴路径），然后输入mysqld –install (安装mysql)

7. 再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
   如果报下面错（证明已安装过可直接跳转第7步）：

8. 然后再次启动mysql(net start mysql) 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）

9. 进入界面后更改root密码

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

10. 刷新权限

flush privileges;

11. 修改 my.ini文件删除最后一句skip-grant-tables
12. 重启mysql即可正常使用

net stop mysql

net start mysql

2.2 普通安装

详见文件文档

2.3 数据库登录

MySQL是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的root账号，使用安装时设置的密码即可登录。

数据库图形化工具 SQLYog、Navicat

三 、SQL语句

3.1 SQL语句概述

3.2 SQL语句分类

数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等 结构上的操作
数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等 不涉及到结构的变化、但是数据会发生变化
数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等 结构不会发生变化，数据也不会发生变化
数据控制语言：简称DCL(Daat Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。
（DBA数据库管理员）（了解）

3.3 常用sql语句(DDL)

几个基本的数据库操作命令 :

连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码

update user set password=password(‘123456’)where user=‘root’; 修改密码

flush privileges; 刷新数据库

show databases; 显示所有数据库

use dbname；打开某个数据库

show tables; 显示数据库mysql中所有的表

describe user; 显示表mysql数据库中user表的列信息

初识数据库（注：“[ ]内的可以不写”）

create database [if not exists] name [character set 字符集]; 创建数据库

删除数据库 : drop database [if exists] 数据库名;

use databasename; 选择数据库

exit; 退出Mysql

? 命令关键词 : 寻求帮助

 -- 表示注释

#创建数据库 demo1 数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8
CREATE DATABASE demo1 CHARACTER SET utf8
#创建数据库demo2 并指定数据库中数据的编码为gbk
CREATE DATABASE demo2 CHARACTER SET gbk
#切换到demo1数据库
USE demo1
#查看当前正在操作哪个数据库
SELECT DATABASE();
#查看服务器下的所有数据库
SHOW DATABASES;
#查看demo1数据库的定义信息
SHOW CREATE DATABASE demo1;
#删除demo2数据库
DROP DATABASE demo2;

操作数据表结构

创建表格式：
Create table 表明（
字段名1 类型（长度） 约束，（以逗号结尾）
字段名2 类型（长度）
）

/*
1、创建分类表
分类表名称：category
分类表中有：分类ID和分类名称两个字段
分类ID：cid,为整型，
分类名称：cname,为字符串类型，最大允许填写100长度的字符串
*/
USE demo1;
CREATE TABLE category(
	cid INT,
	cname VARCHAR(100)
);

/*
2、创建测试表
测试表名称：test1
测试表中有：测试ID和测试时间两个字段
测试ID：tid,为整型
测试时间：tdate,为年月日的日期类型 
*/
CREATE TABLE test1(
	tid INT,
	tdate DATE
);

show tables; 查看当前数据库中的所有表名;

desc 表名; 查看某张数据表的表结构

#查看当前数据库中有哪些表
SHOW DATABASES;
#查看category表的表结构
DESC category;

drop table 表名; 从当前数据库中永久删除某张表

#删除测试表test1
DROP TABLE test1

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库

3.4 更新表记录（ DML）

3.4.1 插入表记录：insert into

sql准备
#创建分类表，用以练习
CREATE TABLE category(
cid INT PRIMARY KEY,
cname VARCHAR(100)
);

CREATE TABLE category(
	cid INT PRIMARY KEY,
	cname VARCHAR(100)
);
#插入单个值（cid)
INSERT INTO category(cid) VALUES (1);
#插入单个值（cname)
INSERT INTO category(cname) VALUES ('Daniel');
#插入两个值（cid,cname) 第一种方式
INSERT INTO category(cid,cname) VALUES(2,'hollow');
#插入两个值（cid,cname)  第二种方式
INSERT INTO category VALUES (3,'Wendy');

INSERT INTO category VALUES(4);#不可以
INSERT INTO category VALUES('Kendy',5);#不可以  值的顺序和字段没有保持一致

注意：
值与字段必须对应，个数相同，类型相同
值的数据大小必须在字段的长度范围内
除了数值类型外(int double)，其它的字段类型的值必须使用单引号/双引号引起。
如果要插入空值，可以忽略不写字段，或者插入 null。

3.4.2 更改表记录：update

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值
语法：
–更新所有记录的指定字段
Update 表明 set 字段 = ‘值’
–更新符合条件记录的指定字段
Update 表明 set 字段 = ‘值’where 条件

#更新表所有的字段
UPDATE  category SET cname = '手表';
#更新指定字段
UPDATE category SET cname = '智能手环' WHERE cid = 2;

注意：
列名的类型与修改的值要一致.
修改值得时候不能超过最大长度.
除了数值类型外，其它的字段类型的值必须使用引号引起

3.4.3 删除表记录：delete from

语法：
–逐条删除表中所有记录
Delete from 表名
–逐条删除表中符合条件的记录
Delete from 表名 where 条件

#删除指定的一条数据
DELETE FROM category WHERE cid = 3;
#删除所有的数据
DELETE FROM category;

扩展：
#Truncate 表名 删除整张表 并重新创建一个一模一样表
TRUNCATE category
delete from 表名 仅仅删除表中的数据 不对表结构有任何改变

3.5、DQL-查询数据

语法

--查询并展示表中所有记录
	Select * from 表名
	--查询并展示表中符合要求的记录
	Select * from 表名 where 条件

3.5.1 简单查询

1.别名查询.使用的关键字是as（as可以省略的）.
表别名：（用于多表查询中）
列别名
2.去掉重复值(distinct).
SELECT DISTINCT(category_name) FROM product
3.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
SELECT price+10 ‘计算后的总价’ FROM product

3.5.2 条件查询-where

案例准备：

#创建商品表：
CREATE TABLE product(
	pid INT PRIMARY KEY, #主键ID
	pname VARCHAR(20), #商品名称
	price DOUBLE, #商品价格
	category_name VARCHAR(32) #商品分类名称
);

INSERT INTO product(pid,pname,price,category_name) VALUES(1,'华为电脑',5000,'电脑办公');
INSERT INTO product(pid,pname,price,category_name) VALUES(2,'海尔电脑',3000,'电脑办公');
INSERT INTO product(pid,pname,price,category_name) VALUES(3,'雷神电脑',5000,'电脑办公');

INSERT INTO product(pid,pname,price,category_name) VALUES(4,'Daniel Jenny',800,'服装');
INSERT INTO product(pid,pname,price,category_name) VALUES(5,'hollow',200,'服装');
INSERT INTO product(pid,pname,price,category_name) VALUES(6,'Wendy',440,'服装');
INSERT INTO product(pid,pname,price,category_name) VALUES(7,'Kendy',2000,'服装');

INSERT INTO product(pid,pname,price,category_name) VALUES(8,'香奈儿',800,'女士用品');
INSERT INTO product(pid,pname,price,category_name) VALUES(9,'相宜本草',200,'女士用品');
INSERT INTO product(pid,pname,price,category_name) VALUES(10,'面霸',5,'女士用品');

INSERT INTO product(pid,pname,price,category_name) VALUES(11,'雪碧',56,'饮料饮品');
INSERT INTO product(pid,pname,price,category_name) VALUES(12,'香飘飘奶茶',1,'饮料饮品');

INSERT INTO product(pid,pname,price,category_name) VALUES(13,'iPhone9',8000,NULL);
INSERT INTO product(pid,pname,price,category_name) VALUES(14,'联想电脑',3000,'电脑办公');

#查询product表中所有记录
#查询product表中所有记录，仅显示pid和pname字段
#查询product表中所有的电脑办公记录
#查询商品名称为“Daniel Jenny”的商品所有信息：
#查询价格为800商品
#查询价格不是800的所有商品
#查询商品价格大于60元的所有商品信息
#查询商品价格在200到1000之间所有商品
#查询商品价格是200或800或者2000的所有商品
#查询含有'霸'字的所有商品
#查询以'香'开头的所有商品
#查询第二个字为'想'的所有商品
#商品没有分类的商品
#查询有分类的商品

案例实现：

#查询product表中所有记录
SELECT * FROM product;
#查询product表中所有记录，仅显示pid和pname字段
SELECT pid,pname FROM product;
#查询product表中所有的电脑办公记录
SELECT * FROM product AS p WHERE p.category_name = '电脑办公';
#查询商品名称为“Daniel Jenny”的商品所有信息：
SELECT * FROM product WHERE pname='Daniel Jenny';
#查询价格为800商品
SELECT * FROM product WHERE price=800;
#查询价格不是800的所有商品
SELECT * FROM product WHERE price > 800 OR price < 800;
SELECT * FROM product WHERE price != 800;
SELECT * FROM product WHERE price <> 800;
#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;
#查询商品价格在200到1000之间所有商品
SELECT * FROM product  WHERE 200 <= price AND price < 1000;
SELECT * FROM product WHERE price BETWEEN  200 AND 1000;
#查询商品价格是200或800或者2000的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800 OR price = 2000;
SELECT * FROM product WHERE price IN(200,800,2000);
#查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';
#查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
#查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';
#商品没有分类的商品
SELECT * FROM product WHERE category_name IS NULL;
#查询有分类的商品
SELECT * FROM product WHERE category_name IS NOT NULL;

3.6、单表操作

3.6.1、排序

应用场景：

查询时，我们通过order by语句，可以将查询出的记录进行排序。放置在select语句的最后。
语法格式：select * fom 表名 where 条件 order by 语句 asc/desc(默认升序)
两种：升序asc
降序desc

案例准备(注：上述product表)：

#1.使用价格排序(降序)
#2.在价格排序(降序)的基础上，以主键排序(降序)
#即若价格相同，相同价格的数据以pid降序排序
#3.显示商品的价格(去重复)，并排序(降序)

案例实现：

#1.使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
#2.在价格排序(降序)的基础上，以主键排序(降序)
#即若价格相同，相同价格的数据以pid降序排序
SELECT * FROM (SELECT * FROM product ORDER BY price DESC) AS a ORDER BY a.pid DESC ;
SELECT * FROM product ORDER BY price DESC,pid DESC;
#3.显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT(price) FROM product ORDER BY price DESC;

3.6.2、聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略null空值。
今天我们学习如下五个聚合函数：
count(字段)：统计指定列不为NULL的记录行数；、
sum(字段)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
max(字段)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min(字段)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg(字段)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0；

案例准备（注：上述product表）：

#1 查询商品的总条数
#查看商品总价格、最大价格、最小价格、价格的平均值
#2 查询价格大于200商品的总条数
#3 查询分类为'电脑办公'的所有商品的总记录
#4 查询分类为'服装'所有商品的平均价格

#1 查询商品的总条数
SELECT COUNT(*) FROM product;
SELECT COUNT(pid) FROM product;
SELECT COUNT(pname) FROM product;
SELECT COUNT(category_name) FROM product;
#查看商品总价格、最大价格、最小价格、价格的平均值
SELECT SUM(price) 总价格 FROM product;
SELECT MAX(price) 最大价格 FROM product;
SELECT MIN(price) 最小价格 FROM product;
SELECT AVG(price) 平均价格 FROM product;
#2 查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;
#3 查询分类为'电脑办公'的所有商品的总记录
SELECT COUNT(*) FROM product WHERE category_name = '电脑办公';
#4 查询分类为'服装'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_name = '服装';

3.7、 分组

分组查询是指使用group by字句对查询记录进行分组运算。
格式：
SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段;
SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;
分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
Having与where是有区别：
Having：是在分组后进行条件过滤
Where：是在分组前进行条件过滤

案例准备：

#查看商品列表中有哪些分类
#1 统计各个分类下的商品的个数
#2 统计各个分类商品的个数,且只显示分类名不为空值的数据

案例实现：

#查看商品列表中有哪些分类
SELECT * FROM product GROUP BY category_name;
#1 统计各个分类下的商品的个数
SELECT COUNT(*) category_name FROM product GROUP BY category_name;
#2 统计各个分类商品的个数,且只显示分类名不为空值的数据
SELECT COUNT(*) category_name FROM product GROUP BY category_name HAVING category_name IS NOT NULL;

四、SQL约束&策略

代码准备

CREATE TABLE p0
(
id int,
name varchar(200),
idCard varchar(50)
)

4.1、主键约束

PRIMARY KEY 约束，简称PK，用于标识数据库表中的每条记录是唯一不重复的。

4.1.1、添加主键约束

方式一：创建表时，在字段描述处，声明指定字段为主键：

CREATE TABLE p1
(
id int primary key,
name varchar(200),
idCard varchar(50)
)

主键特点：唯一且不能为空 在表中只能有一个主键

方式二：创建表时，在constraint约束区域，声明指定字段为主键：

格式：[constraint 名称] primary key (字段列表)
关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。

CREATE TABLE p2
(
id int,
name varchar(200),
idCard varchar(50),
PRIMARY KEY(id)
)

联合主键：多个字段结合在一起工作组成的主键
方式三：创建表之后，通过修改表结构，声明指定字段为主键：

CREATE TABLE p3
(
id int,
name varchar(200),
idCard varchar(50)
)
#因为一般主键都是提前确定好的，都是跟随表一起创建
ALTER TABLE p3 ADD PRIMARY KEY(id)

4.1.2、删除主键约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：
ALTER TABLE 表名 DROP PRIMARY KEY;
注意：主键约束一旦确定 开发中是不会轻易去删除

4.2、非空约束

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

CREATE TABLE p4
(
id int,
name varchar(200) not null,
idCard varchar(50)
)

4.3、唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束都可以标识当前字段唯一，不重复。
区别：1、一个表中可以有多个唯一约束，但是主键约束只能有一个
2、唯一约束可以有null值，但主键约束不允许有null值

4.3.1、添加唯一约束

与主键添加方式相同，共有3种，

方式1：创建表时，在字段描述处，声明唯一：

CREATE TABLE p5
(
id int,
name varchar(200),
idCard varchar(50) UNIQUE
)

方式2：创建表时，在约束区域，声明唯一：

CREATE TABLE p6
(
id int,
name varchar(200),
idCard varchar(50)
UNIQUE(NAME)
)

4.4、外键约束

FOREIGN KEY 表示外键约束，将在多表中学习。

4.5、自动增长-策略

我们通常希望在每次插入新记录时，数据库自动生成字段的值。
我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键（可以是唯一约束，可以是主键约束）。

下列 SQL 语句把 “Persons” 表中的 “P_Id” 列定义为 auto_increment 主键

CREATE TABLE p7
(
id int PRIMARY KEY,
name varchar(200),
idCard varchar(50)
)

Id增长的时候 它会先计算出id的最大值 然后根据最大值向后新增id

Tuncate 和 delete from

五、多表操作

5.1、多表简述

开发中，一个项目通常需要很多张表才能完成。
例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来将在单表的基础上，一起跟Daniel学习多表方面的知识。

已知：
我们是有一张商品表（product）

需求：

现在我们需要修改分类名称，将所有的“电脑办公” 修改为 “笔记本电脑”
UPDATE product SET category_name = ‘笔记本电脑’ WHERE category_name = ‘电脑办公’
从这个表可以看出 修改了三条数据

需求变型：

假如商品表中有十几万条电脑办公的数据
修改十几万条数据
导致：服务器执行效率低，有可能会导致数据库宕机（所谓宕机就是服务器不工作了）

解决：

为了数据的使用和维护更为方便，我们将单表数据划分为多表。
商品数据保存在商品表中，
分类数据保存在分类表中。

Update category set cname = ‘笔记本电脑’ where cname = ‘电脑办公’
修改了一条数据

5.2、外键

外键作用：将多个表之间的数据进行关联
主表：数据提供方（一方）
从表：数据使用方（多方）

5.3、外键约束

数据准备：

create database demo2;
use demo2;
#创建分类表：
CREATE TABLE category(
	cid varchar(32) PRIMARY KEY,#主键ID
	cname VARCHAR(20)#分类名称
);
INSERT INTO category VALUES('c001','电脑办公');
INSERT INTO category VALUES('c002','服装');
#创建商品表：
CREATE TABLE product(
	pid INT PRIMARY KEY,#主键ID
	pname VARCHAR(20),#商品名称
	price DOUBLE,#商品价格
	category_cid VARCHAR(32)#外键
);
#导入数据
INSERT INTO product(pid,pname,price,category_cid) VALUES(1,'联想电脑',5000,'c001');
INSERT INTO product(pid,pname,price,category_cid) VALUES(2,'海尔电脑',3000,'c001');
INSERT INTO product(pid,pname,price,category_cid) VALUES(3,'雷神电脑',5000,'c001');
INSERT INTO product(pid,pname,price,category_cid) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_cid) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_cid) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_cid) VALUES(7,'劲霸',2000,'c002'); 

声明外键约束：（保证数据的完整性）
格式：
alter table 从表 add [constraint 外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

为从表添加外键约束，必须保证：

从表外键的数据类型和长度，必须和主表主键数据类型和长度 一致
从表和主表为空，或者数据必须完整

删除外键约束：（了解，开发慎用）

alter table 从表 drop foreign key 外键名称

5.4、多表关系

通过外键可以将多表建立关系

5.4.1、多表关系简述

5.4.2、一对多

应用场景：商品与分类 省份与城市 城市与人
建表原则：

5.4.3、多对多（实际就是多个一对多）

应用场景：老师与学生 演员与角色
建表原则：

5.4.4、一对一(少见)

在开发中应用不多.因为一对一可以合成一张表。
建表方式：
外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。

六、多表关系实战

6.1 实战1：省和市

方案1：多张表，一对多

代码准备：（请在准备代码中填入外键）

CREATE TABLE province(
	id INT PRIMARY KEY,
	NAME VARCHAR(20),
	description VARCHAR(20)
);
CREATE TABLE city(
	id INT PRIMARY KEY,
	NAME VARCHAR(20),
	description VARCHAR(20),
Pid INT 
);

ALTER TABLE city ADD FOREIGN KEY(pid) REFERENCES province(id)

6.2 实战2：用户和角色

多对多关系

代码准备：（请在准备代码中填入中间表及外键）

create table actor(
aid int primary key,
name varchar(30)
);
create table role(
rid int primary key,
name varchar(30)
);

(1)添加中间表

CREATE TABLE actor_role(
	arid INT PRIMARY KEY,
	aid INT,
	rid INT
);

（2）添加外键

ALTER TABLE actor_role ADD FOREIGN KEY(aid) REFERENCES actor(aid);
ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(rid);

七、多表查询

7.1 什么是多表查询

同时查询多张表获取到需要的数据 比如：我们想查询到开发部有多少人，需
要将部门表和员工表同时进行查询

7.2 多表查询的分类

7.3 笛卡尔积现象

准备数据：

#创建部门表
CREATE TABLE dept (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
#创建员工emp表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), 
salary DOUBLE, 
join_date DATE, 
dept_id INT
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2022-02-04',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2022-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2022-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2022-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2022-03-14',1)

7.3.1什么是笛卡尔积现象

需求：查询每个部门下有哪些人

SELECT * FROM emp,dept

通过图可知：

Emp与dept去查询 emp查询表中的每条数据与dept表中的数据进行一一匹配（5*3=15条）

7.3.2如何清除笛卡尔积

将不符合条件的进行过滤

 SELECT * FROM emp e,dept d WHERE e.dept_id = d.id

7.4、内连接

隐式内连接：
写法格式：select * from 表1， 表2 where 条件
显示内连接：
写法格式：select * from 表1 inner join 表2 on 条件
需求：查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
多表查询的规律：（重点记忆）
1.首先确定哪几张表
2.确定查询条件是什么
3.确定显示哪些字段

# 显示内连接
#查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
SELECT e.id 员工id, e.name 姓名,e.gender 性别, d.* FROM emp e INNER JOIN dept d ON e.dept_id = d.id AND e.name = '唐僧'

# 隐式内连接
#查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
SELECT e.id 员工id,e.name 姓名,e.gender 性别,e.salary 工资,d.name 部门名称 FROM emp e,dept d WHERE dept_id = d.id AND e.name = '唐僧';

7.5、外连接

左外连接：

写法格式：select * from 表1 left join 表2 on 条件

右外连接：

写法格式：select * from 表1 right join 表2 on 条件

需求：查询所有员工信息以及部门名称(没有部门的员工也显示)

#查询所有员工信息以及部门名称(没有部门的员工也显示) 
SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id=d.id;
SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id=d.id;

7.6、子查询 （重点）

概述：将上一条SELECT语句结果作为另一条SELECT语法一部分

子查询的三种情况：

1、子查询的结果是一个值的时候

需求：

1、查询工资最高的员工是谁？

#查询工资最高的员工是谁？
SELECT MAX(salary) FROM emp;
SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);

2、查询工资小于平均工资的员工有哪些

#查询工资小于平均工资的员工有哪些
SELECT AVG(salary) FROM emp;
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

3、子查询结果是单列多行的时候

需求：
1、查询工资大于5000的员工，来自于哪些部门的名字

#查询工资大于5000的员工，来自于哪些部门的名字
SELECT dept_id FROM emp WHERE salary > 5000;
SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 5000);

2、查询开发部与财务部所有员工的信息

#查询开发部与财务部所有员工的信息
SELECT id FROM dept WHERE NAME='开发部' OR NAME = '财务部';
SELECT * FROM emp e WHERE e.dept_id IN (SELECT id FROM dept WHERE NAME='开发部' OR NAME = '财务部');

3、子查询的结果是多行多列

需求：

1、查询出2022年以后入职的员工信息，包括部门名称

#查询出2022年以后入职的员工信息，包括部门名称
SELECT * FROM emp WHERE join_date > 2022-01-01;
SELECT e.*,d.name FROM (SELECT * FROM emp WHERE join_date > 2022-01-01) e,dept d WHERE d.id = e.dept_id;

结论：

子查询结果只要是 单列 ，肯定在 WHERE 后面作为 条件
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
子查询结果只要是 多列 ，肯定在 FROM 后面作为 表
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件

八、多表查询综合练习(四张表联查)

准备sql:

#部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, 
  dname VARCHAR(50), 
  loc VARCHAR(50) 
);

#添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

#职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

#添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

#员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

#添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

#工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

#添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

案例要求：

#练习1查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述

#练习2查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

#练习3查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

#练习4查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

#练习5查询出部门编号、部门名称、部门位置、部门人数

#练习6查询所有员工信息。显示员工信息和部门名称，没有员工的部门也要显示

#练习7查询所有员工信息。显示员工姓名，员工工资，职务名称，工资等级，并按工资升序排序

#练习8列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示

#练习9查询入职期早于直接上级的所有员工编号、姓名、部门名称

#练习10查询工资高于公司平均工资的所有员工信息。显示员工信息，部门名称，上级领导，工资等级

参考答案：

#练习1查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述
SELECT e.id 员工编号,e.ename 员工姓名,e.salary 工资, j.jname 职务名称,
j.description 职务描述 FROM emp e,job j WHERE e.job_id =j.id;

#练习2查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT e.id 员工编号,e.ename 员工姓名,e.salary 工资, j.jname 职务名称,j.description 职务描述,
d.dname 部门名称,d.loc 部门位置 FROM emp e,job j,dept d WHERE e.job_id =j.id AND e.dept_id =d.id; 

#练习3查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

SELECT e.ename 员工姓名,e.salary 工资,j.jname 职务名称,j.description 职务描述,d.dname 部门名称,
d.loc 部门位置,s.grade 工资等级 FROM emp e,dept d,job j,salarygrade s WHERE e.job_id =j.id AND 
e.dept_id =d.id AND e.salary BETWEEN losalary AND hisalary;

#练习4查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT e.ename 员工姓名,e.salary 工资,j.jname 职务名称,j.description 职务描述,d.dname 部门名称,
d.loc 部门位置,s.grade 工资等级 FROM emp e,dept d,job j,salarygrade s WHERE e.job_id =j.id AND 
e.dept_id =d.id AND e.salary BETWEEN losalary AND hisalary AND j.jname = '经理';

#练习5查询出部门编号、部门名称、部门位置、部门人数
SELECT d.id 部门编号,d.dname 部门名称,d.loc 部门位置,COUNT(*) 部门人数 FROM emp e,dept d WHERE
e.dept_id = d.id GROUP BY d.id;

#练习6查询所有员工信息。显示员工信息和部门名称，没有员工的部门也要显示
SELECT e.*,d.dname 部门名称 FROM emp e RIGHT JOIN dept d ON e.dept_id =d.id; 

#练习7查询所有员工信息。显示员工姓名，员工工资，职务名称，工资等级，并按工资升序排序
SELECT e.ename 员工姓名,e.salary 员工工资,j.jname 职务名称,s.grade 工资等级 FROM emp e,job j,salarygrade s
WHERE e.job_id = j.id AND e.salary BETWEEN s.losalary AND s.hisalary ORDER BY e.salary;

#练习8列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示
SELECT e.ename,m.ename FROM emp e LEFT JOIN emp m ON m.mgr = e.id;

#练习9查询入职期早于直接上级的所有员工编号、姓名、部门名称
SELECT e.id 员工编号,e.ename 员工姓名,d.dname 部门名称 FROM emp e,emp m,dept d WHERE e.mgr = m.id AND
e.dept_id = d.id AND e.joindate > m.joindate;

#练习10查询工资高于公司平均工资的所有员工信息。显示员工信息，部门名称，上级领导，工资等级
SELECT e.*,d.dname 部门名称,m.ename,s.grade FROM emp e,emp m,dept d,salarygrade s WHERE 
e.mgr = m.id AND e.dept_id = d.id AND e.salary BETWEEN s.losalary AND hisalary AND 
e.salary >(SELECT AVG(salary) FROM emp);

九、Mysql中的分页

点击👉🏾我查看