MySQL基础篇之多表查询

前言

MySQL在我们工作中都会用到，那么我们最常接触的就是增删改查，而对于增删改查来说，我们更多的是查询。但是面试中，面试官又不会问你什么查询是怎么写的，都是问一些索引啊，事务啊， 底层结构这些东西，所以我打算分四篇去逐一的过一遍MySQL的知识点。
以下为四篇的传送门，有需要直接点击进入，目前还在建设当中，可以关注作者或专栏，持续更新，永不收费。

篇章	地址
DDL查询篇	传送门
MySQL函数及视图、存储过程	–
MySQL索引及优化	–
MySQL事务及架构	–

一、SQL的分类

• DDL：数据定义语言（CREATE、ALTER、DROP、RENAME、TRUNCATE）
• DML：数据操作语言（INSERT、DELETE、UPDATE、SELECT）
• DCL：数据控制语言（COMMIT、ROLLBACK、SAVEPOINT、GRANK、REVOKE）

二、DDL数据定义语言

我们在工作中写的最多的是查询语句，像数据库和数据表可能组内大佬已经给建好了，我们一般不会进行改动，所以一些基本的DDL语言我们不是很熟悉，再此还是复习一下吧~

1.MySQL中的数据类型

类型	数据变量
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON； 集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

1.针对数据库操作

1.创建数据库

CREATE DATABASE test; # 最简单的方式
CREATE DATABASE test2 CHARACTER SET 'gbk'; # 指定字符集
CREATE DATABASE IF NOT EXISTS test3 CHARACTER SET 'gbk'; # 避免sql报错并指定字符集

2.使用数据库

USE test; # 使用数据库test
SHOW DATABASES; # 查看当前所有的数据库
SELECT DATABASE(); # 查看当前正在使用的数据库
SHOW TABLES FROM test; # 查看当前使用的数据库中的所有表
SHOW CREATE DATABASE test; # 查看数据库的创建信息

3.修改数据库

ALTER DATABASE test CHARACTER SET 'utf8'; # 更改数据库test的字符集为utf8

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删 旧库完成的。

4.删除数据库

DROP DATABASE IF EXISTS test; # 删除数据库test，不要做删库跑路的事哦

2.针对数据表操作

1.创建数据表

# 1.这是很基础的创建表
CREATE TABLE IF NOT EXISTS employees(
 id INT,
`name` VARCHAR(15),
`hire_date` DATE,
`salary` DOUBLE(10,2)
);

# 2.类似克隆表，不要数据
CREATE TABLE employees_back AS SELECT * FROM employees WHERE 1=2;

# 3.工作中标准的表创建（可以使用作为模板）
CREATE TABLE IF NOT EXISTS `role` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '角色名称',
  `desc` varchar(32) NOT NULL DEFAULT '' COMMENT '角色描述',
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COMMENT='用户角色表';

# 4.查看数据表结构
SHOW CREATE TABLE `role`;

2.修改数据表

# 1.给数据表添加字段
ALTER TABLE employees_back  ADD status TINYINT; 

# 2.添加字段并指定其位置
ALTER TABLE employees_back  ADD age TINYINT AFTER department_id; 

# 3.修改status字段设默认值
ALTER TABLE employees_back MODIFY status TINYINT DEFAULT(1); 

# 4.删除字段
ALTER TABLE employees_back DROP COLUMN sex; 

# 5.重命名字段
ALTER TABLE employees_back CHANGE age sex VARCHAR(2);

# 6.重命名数据表 
RENAME TABLE employees_back TO employees1; 

3.删除数据表

DROP TABLE employees_back; # 删除数据表

4. 清空数据表

TRUNCATE TABLE employees_back; # 清空数据表

二、DML数据操作语言

1. INSERT（插入数据）

INSERT INTO empl VALUES(1,'Tom','2022-12-06',4500); # 插入一条数据
INSERT INTO empl VALUES(3,'Scott','2022-12-10',12000),(4,'Alex','2022-12-10',6500); # 插入多条数据
INSERT INTO empl(id,hire_date,salary,`name`) VALUES(2,'2022-12-07',4500,'Kate'); # 插入指定字段数据
INSERT INTO empl (SELECT employee_id,last_name,hire_date,salary FROM employees WHERE department_id IN (70,60)) # 以查询结果作为插入数据

2. UPDATE（修改数据）

UPDATE empl SET id = 5 WHERE id = 103;
UPDATE empl SET id = 6,hire_date = '2022-12-23',salary = 4500 WHERE id = 104;

3. DELETE（删除数据）

DELETE FROM empl WHERE id = 111; # 删除id是111的数据

面试题：DELETE和TRUNCATE有什么区别？
相同点：都可以对数据进行删除，保留数据表结构
不同点：TRUNCATE一旦操作，表数据全部清除，并且不能回滚。DELETE可以删除部分数据，可以回滚

三、查询（重点、常用）

对于我们程序员来说，写的最多的就是查询，比较重要，内容也比较多，所以单拉出来记录。

1.简单的查询语句

SELECT 字段1,字段2,... FROM 表名  *表示所有字段
SELECT * FROM employees; # 查询employees表所有数据
SELECT * FROM `ORDER`; # 当表名与保留字重复时，可以使用着重号
SELECT '哈哈哈',employee_id FROM employees; # 查询常数

1.1 DISTINCT去重

SELECT DISTINCT department_id FROM employees; # 去除重复department_id

1.2 AS别名

SELECT employee_id as empId FROM employees; # as起别名，也可以省略as

1.3 WHERE条件过滤

SELECT * FROM employees WEHRE emp_id = 1; # 查询emp_id是1的员工

1.4 IN 和 NOT IN（相当于多个条件用or连接）

SELECT last_name,salary,department_id 
FROM employees 
WHERE department_id IN(10,20,30)

1.5 BETWEEN … AND…（>=,<=）

SELECT employee_id ,last_name,salary 
FROM employees 
WHERE salary BETWEEN 6000 AND 8000

1.6 LIKE模糊查询

SELECT last_name FROM employees WHERE last_name LIKE '%a%'; # 查询名字中包含a的
SELECT last_name FROM employees WHERE last_name LIKE '_a%'; #查询第二个字符是a的
SELECT last_name FROM employees WHERE last_name LIKE '_\_a%'; # 转义字符 查询第二个字符是_并且第三个字符是a的

1.7 ORDER BY 排序

SELECT * FROM employees ORDER BY salary DESC; # 按照工资从大到小排序
SELECT * FROM employees ORDER BY salary ASC; # 按照工资从小到大排序

1.8 LIMIT 分页

# （pageNum-1）* pageSize,pageSize
SELECT employee_id,last_name FROM employees LIMIT 0,20; # 每页显示20条，当前是第一页
SELECT employee_id,last_name FROM employees LIMIT 20,20; # 每页显示20条，当前是第二页

2. 多表查询

如果条件没有相等的，就不会显示出来（从sql优化的角度考虑，多表查询时都指明所在的表）

2.1 内连接 JOIN…ON（中图）：结果集中不包含一个表与另一个表不匹配的行

SELECT employee_id,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;

2.2 左外连接 LEFT JOIN…ON（左上图）

# 两个表在连接过程中除了返回满足连接条件的行以外，还返回在左表中不满足条件的行
SELECT employee_id,department_name
FROM employees LEFT JOIN departments
ON employees.department_id = departments.department_id;

2.3 右外连接 （右上图）

# 两个表在连接过程中除了返回满足链接条件的行以外，还返回在右表中不满足条件的行
SELECT employee_id,department_name
FROM employees RIGHT JOIN departments
ON employees.department_id = departments.department_id;

2.4 左外连接（左中图）

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

2.5 右外连接（右中图）

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

2.6 满外连接（左下图）

# UNION 和 UNION ALL ，UNION是去重后的，UNION ALL是两个结果的并集，但是一般用后者，因为sql优化
SELECT employee_id,department_name
FROM employees LEFT JOIN departments
ON employees.department_id = departments.department_id
WHERE departments.department_id IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees RIGHT JOIN departments
ON employees.department_id = departments.department_id

2.7 满外连接（右下图）

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

2.8 自然连接 NATURAL JOIN

# 自动查询两张表中所有相同的字段，然后进行等值连接
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e NATURAL JOIN departments d

2.9 UNION的使用

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

UNION操作符

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

✨$原创不易，还希望各位大佬支持一下$

👍 $点赞，你的认可是我创作的动力！$

⭐️ $收藏，你的青睐是我努力的方向！$

✏️ $评论，你的意见是我进步的财富！$