1、MySQL基础
1.1 数据库基本介绍
数据库是存储数据的仓库，本质上是一个文件系统，以文件的方式存储数据到计算机上。所有的关系型数据库都可以使用通用的 SQL 语句进行管理。
数据的存储方式：
Java 中创建对象： Student s = new Student(1, “张三”) 存在内存中
Java IO 流：把数据保存到文件中
| 存储位置 | 优点 | 缺点 |
|---|---|---|
| 内存 | 速度快 | 不能永久保存，数据是临时状态。 |
| 文件 | 数据可以永久保存 | 操作数据不方便，查询某个数据。 |
| 数据库 | 1. 数据可以永久保存 2. 查询速度快3. 对数据的管理方便 | 占用资源，需要购买。 |
1.2 数据库的登录
MySQL 是一个需要账户名密码登录的数据库，登录后使用，它提供了一个默认的 root 账号，使用安装时设置的密码即可登录。
1.2.1 MySQL服务的启动
- 方式一：计算机——右击管理——服务
- 方式二：通过管理员身份启动控制台，否则可能回报系统错误5
net start 服务名（启动服务） net stop 服务名（停止服务）
1.2.2 DOS命令方式启动
-
登录Mysql:
- 登录格式1：mysql -u用户名 -p密码
- 登录格式2：mysql -h地址 -u用户名 -p密码
- 登录格式3：mysql --host=ip地址 --user= 用户名 --password= 密码
-
退出 MySQL： quit或 exit
1.3 MySQL目录结构
| MySQL的目录结构 | 描述 |
|---|---|
| bin文件夹 | 所有mysql的可执行文件，如：mysql.exe |
| MySQLInstanceConfig.exe | 数据库的配置向导，在安装时出现的内容 |
| data文件夹 | 系统必须的数据库所在的目录 |
| my.ini 文件 | mysql 的配置文件，一般不建议去修改 |
| c:\ProgramData\MySQL\MySQL Server 5.5\data | 我们自己创建的数据库所在的文件夹 |
1.4 数据库管理系统
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。
数据库管理程序(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。
数据库管理系统、数据库和表的关系如图所示：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ESwZV6wT-1594282103296)(C:\Users\gnoll\AppData\Roaming\Typora\typora-user-images\1594084225766.png)]
一个数据库服务器包含多个数据库，一个数据库包含多张表，一张表包含多条记录。数据库在计算机上以文件夹的形式存在，表以文件的形式存在。
1.5 SQL的概念
1.5.1 什么是 SQL
Structured Query Language 结构化查询语言，是一种所有关系型数据库的查询规范，不同的数据库都支持。通用的数据库操作语言，可以用在不同的数据库中。不同的数据库 SQL 语句有一些区别。
1.5.2 SQL 作用
- 是一种所有关系型数据库的查询规范，不同的数据库都支持。
- 通用的数据库操作语言，可以用在不同的数据库中。
- 不同的数据库 SQL 语句有一些区别。
1.5.3 SQL语句分类
- Data Definition Language (DDL 数据定义语言) 如：建库，建表
- Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改
- Data Query Language(DQL 数据查询语言)，如：对表中的查询操作
- Data Control Language(DCL 数据控制语言)，如：对用户权限的设置。即对于数据库服务器的使用，无非就是操作数据库（文件夹）、操作数据表（文件）。
1.6 MySQL语法和数据类型
1.6.1 基础语法
1) 每条语句以分号结尾，如果在 SQLyog 中不是必须加的。
2) SQL 中不区分大小写，关键字中认为大写和小写是一样的
3) 3 种注释：
| 注释的语法 | 说明 |
|---|---|
| **-- ** 空格 | 单行注释 |
| /* */ | 多行注释 |
| # | 这是 mysql 特有的注释方式 |
1.6.2 MySql数据类型
Data Definition Language (DDL 数据定义语言) 就是建立数据库（文件夹）和建立数据表（文件）的语法，详细介绍如下：
1.7.1 创建数据库、数据表
创建数据库的三种格式：
-- 直接创建数据库
-- CREATE DATABASE 数据库名;
CREATE DATABASE db1;
-- 判断是否存在，如果不存在则创建数据库
-- CREATE DATABASE IF NOT EXISTS 数据库名;
CREATE DATABASE IF NOT EXISTS db2;
-- 创建数据库并指定字符集为 gbk
-- CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE db3 CHARACTER SET gbk;
创建数据表的格式：
-- CREATE TABLE 表名 (
-- 字段名 1 字段类型 1,
-- 字段名 2 字段类型 2
-- );
CREATE TABLE student (
id INT, -- 整数
name VARCHAR(20), -- 字符串
birthday DATE -- 生日，最后没有逗号
);
-- 快速创建一个表结构相同的表:CREATE TABLE 新表名 LIKE [数据库名.]旧表名;
-- 当两张表位于同一数据库时，可以省略数据库名称;
CREATE TABLE s1 LIKE student;
技巧： 按 tab 键可以自动补全关键字
1.7.2 查看数据库、数据表
查看数据库：
-- 查看所有的数据库
SHOW DATABASES;
-- 查看正在使用的数据库
SELECT DATABASE();
-- 使用/切换数据库：USE 数据库名;
USE db1;
-- 查看某个数据库的定义信息
SHOW CREATE DATABASE db3;
SHOW CREATE DATABASE db1;
查看数据表：
-- 查看数据库中的所有表
SHOW TABLES;
-- 查看表结构
DESC 表名;
-- 先进入数据库：USE 数据库名;
USE db1;
-- 查看创建表的 SQL语句
SHOW CREATE TABLE 表名;
1.7.3 修改数据库、数据表
修改数据库：
-- 修改数据库默认的字符集：
-- ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
ALTER DATABASE db3 CHARACTER SET utf8;
修改数据表：
-- 修改字符集：ALTER TABLE 表名 CHARACTER SET 字符集;
ALTER TABLE student CHARACTER SET gbk;
-- 修改表名 RENAME：
-- RENAME TABLE 表名 TO 新表名;
RENAME TABLE student TO student2;
-- 添加列 ADD：
-- ALTER TABLE 表名 ADD 列名;
ALTER TABLE student ADD remark VARCHAR(20);
ALTER TABLE student MODIFY NAME VARCHAR (30) not null;
-- 注：对列的修改只能通过alter table的方式
-- 修改列类型 MODIFY：
-- ALTER TABLE 表名 MODIFY 【column】 列名 新的类型 新约束;
ALTER TABLE student MODIFY remark VARCHAR(100);
-- 注： modify只能修改列的类型
-- 修改列名及类型 CHANGE:
-- ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
ALTER TABLE student CHANGE remark intro VARCHAR(30);
-- 注：change同时修改列名和列的类型
-- 删除列 DROP：
-- ALTER TABLE 表名 DROP 列名;
ALTER TABLE student DROP intro;
1.7.4 删除数据库、数据表
删除数据库：
-- DROP DATABASE 数据库名;
drop database db2;
删除数据表：
-- 直接删除表：
-- DROP TABLE 表名;
DROP TABLE s1;
-- 判断表是否存在并删除 s1 表：
-- DROP TABLE IF EXISTS 表名;
DROP TABLE IF EXISTS s1;
1.8 DML（数据操纵语言）
DML用于对数据表中的记录进行增删改。
1.8.1 插入记录
-
语法：
insert into 表名(字段名，…)
values(值1，…); -
特点：
1、字段类型和值类型一致或兼容，而且一一对应
2、可以为空的字段，可以不用插入值，或用null填充
3、不可以为空的字段，必须插入值
4、字段个数和值的个数必须一致
5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致
INSERT INTO student
(id,NAME,age,sex,address)
VALUES
(1,'学哥斌',20,'男','广东广州');
-- 插入全部字段
-- 方式一：所有的字段名都写出来
-- 格式：INSERT INTO 表名 (字段名1,字段名2,字段名3…) VALUES (值1,值2,值3);
INSERT INTO student (id,name,age,sex) VALUES (1, '孙悟空', 20, '男','水帘洞');
-- 没有添加的字段会使用 NULL
INSERT INTO student (id,name,age,sex) VALUES (1, '孙悟空', 20, '男');
-- 方式二：不写字段名（默认添加所有字段）
-- 格式：INSERT INTO 表名 VALUES (值1,值2,值3…);
INSERT INTO student VALUES (3, '孙悟饭', 18, '男', '龟仙人洞中');
注意事项：插入的数据应与字段的数据类型相同，数据的大小应在列的规定范围内，在values中列出的数据位置必须与被加入的列的排列位置相对应，字符和日期型数据应包含在单引号中，不指定列或使用 null，表示插入空值。
1.8.2 蠕虫复制
蠕虫复制将查询出来的数据插入到指定表中 ,能快速的往表中增添数据，一般用于表结构稳定性测试。
-- 将表名2中的所有的列复制到表名1中
-- INSERT INTO 表名1 SELECT * FROM 表名2;(不需要values)
INSERT INTO student2 SELECT * FROM student;
-- INSERT INTO 表名1(列1,列2) SELECT 列1,列2 FROM student;
INSERT INTO student2 (name,age) SELECT name,age FROM student;
蠕虫复制的意义：
- 从已有的数据表中拷贝数据到新的数据表；
- 可以迅速的让表中的数据膨胀到一定的数量级，多用于测试表的压力及效率。
mysql> insert into student2 select * from student;
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student2;
+------+------------+---------+
| id | birthday | NAME |
+------+------------+---------+
| 1 | 2015-12-12 | lilair |
| 2 | 1995-12-12 | lilair2 |
+------+------------+---------+
2 rows in set (0.00 sec)
mysql> insert into student2 select * from student2;
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into student2 select * from student2;
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into student2 select * from student2;
Query OK, 8 rows affected (0.06 sec)
Records: 8 Duplicates: 0 Warnings: 0
这里我们就可以看到，数据以2，4，8，16，32…的规律来增长的，也就是我们熟悉的以指数形式增加的。那么一直操作下去，就能在短时间内使得表中数据膨胀到一定数量级，从而达到测试出表压力的目的。
1.8.3 更新表记录
-
修改单表语法：
update 表名 set 字段=新值,字段=新值
【where 条件】
-
修改多表语法：
update 表1 别名1,表2 别名2
set 字段=新值，字段=新值
where 连接条件
and 筛选条件
-- 不带条件修改数据，将所有的性别改成女
UPDATE student SET sex = '女';
-- 带条件修改数据，将 id 号为 2 的学生性别改成男
UPDATE student SET sex='男' WHERE id=2;
-- 一次修改多个列，把 id 为 3 的学生，年龄改成 26 岁，address 改成北京
UPDATE student SET age=26, address='北京' WHERE id=3;
1.8.4 删除表记录
删除表中数据的方式：
-
方式1：delete语句
- 单表的删除：
delete from 表名 【where 筛选条件】 - 多表的删除：
delete 别名1，别名2
from 表1 别名1，表2 别名2
where 连接条件
and 筛选条件;
- 单表的删除：
-
方式2：truncate语句
truncate table 表名
-
两种方式的区别【面试题】
- truncate不能加where条件，而delete可以加where条件
- truncate的效率高一丢丢
- truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
- delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
- truncate删除不能回滚，delete删除可以回滚
-- 带条件删除数据，删除 id 为 1 的记录
DELETE FROM student WHERE id=1;
-- 不带条件删除数据,删除表中的所有数据
DELETE FROM student;
-- 使用 TRUNCATE 删除表中所有记录（TRUNCATE 相当于删除表的结构，再创建一张表。）
TRUNCATE TABLE 表名;
1.9 DQL（数据查询语言）
查询不会对数据库中的数据进行修改.只是一种显示数据的方式。
1.9.1 基础查询
- 语法：
SELECT 要查询的东西
【FROM 表名】;
类似于Java中 :System.out.println(要打印的东西),就是起着打印的效果;
- 特点：
①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数
函数或表达式：
-- 1.显示系统时间(注：日期+时间)
SELECT NOW();
-- 2.展示当前所在的数据库
SELECT DATABASE();
-- 3.常量
select 500;
-- 4.某列数据和固定值运算
-- 格式：SELECT 列名 1 + 固定值 FROM 表名;
SELECT math+5 FROM student;
-- 5.某列数据和其他列数据参与运算
-- 格式：SELECT 列名 1 + 列名 2 FROM 表名;
SELECT *,(math+english) AS 总成绩 FROM student;
指定列的别名进行查询
-- 对列指定别名
-- 格式：SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
SELECT name as 姓名,age AS 年龄 FROM student;
-- 对列和表使用别名
-- 格式：SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;
SELECT st.name AS 姓名,age AS 年龄 FROM student AS st
清除重复值
-- 查询指定列并且结果不出现重复数据
-- 格式：SELECT DISTINCT 字段名 FROM 表名;
SELECT DISTINCT address FROM student;
注意事项:
- 在mysql当中,字符串类型间进行加减乘除运算的时候,会截取字符串以数字开头的那一部分数字进行运算,如果字符串前面没有数字,那么就只能截取的数值为0,那么进行加减的时候结果都是0,进行乘除的时候结果都是NULL,
- 对于数字与非数字混合的字符串，在进行大小比较的时候，如果两字符串长度相等，那么两字符串就会比较相同位置的字符，如日期类型。比较时若字符是数字，则直接比较，若字符是非数字那么会转换为ascii码进行比较，若在某位置上已经有大小之分，那么就不会再进行比较。
1.9.2 条件查询
条件查询：条件查询就是取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回。
-
语法：
select
要查询的字段|表达式|常量值|函数
from
表
where
条件 ; -
分类：
一、条件表达式
示例：salary>10000
使用比较运算符对这个表进行条件查询：
| 比较运算符 | 说明 |
|---|---|
| >、>、< 、<= 、>= 、= 、<> | <>在 SQL 中表示不等于，在 mysql 中也可以使用!=，没有==运算符 |
| BETWEEN…AND | 在一个范围之内，如：between 100 and 200相当于条件在 100 到 200 之间，包头又包尾 |
| IN( 集合) | 集合表示多个值，使用逗号分隔 |
| LIKE ’ 张%’ | 模糊查询，%匹配任意多个字符，_匹配一个字符 |
| IS NULL | 查询某一列为 NULL 的值，注：不能写=NULL |
二、逻辑表达式
示例：salary>10000 && salary<20000
逻辑运算符：
| 逻辑运算符 | 说明 |
|---|---|
| and（&&） | 两个条件如果同时成立，结果为true，否则为false。SQL 中建议使用前者，后者并不通用。 |
| or(||） | 两个条件只要有一个成立，结果为true，否则为false |
| not(!) | 如果条件成立，则not后为false，否则为true |
三、模糊查询
示例：last_name like ‘a%’
MySQL 通配符：
| 通配符 | 说明 |
|---|---|
| % | 匹配任意多个字符串 |
| _ | 匹配一个字符 |
1.9.3 排序查询
-
语法：
select 要查询的东西
from 表
where 条件order by 排序的字段|表达式|函数|别名 【asc|desc】
-- DESC:降序 ASC：升序
-- 单列排序：只按某一个字段进行排序，单列排序。
SELECT * FROM student ORDER BY age DESC;
-- 组合排序:同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推。
SELECT * FROM student ORDER BY age DESC, math ASC;
1.9.6 分组查询
-
语法：
select 查询的字段，分组函数
from 表
group by 分组的字段 -
特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
针对的表 位置 关键字
分组前筛选： 原始表 group by的前面 where
分组后筛选： 分组后的结果集 group by的后面 having4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名
案例：
【案例1】查询男女各多少人
1) 查询所有数据,按性别分组
2) 统计每组人数
select sex, count(*) from student3 group by sex;
注意：当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的
【案例2】查询年龄大于 25 岁的人,按性别分组,统计每组的人数
1) 先过滤掉年龄小于 25 岁的人。
2) 再分组。
3) 最后统计每组的人数
select sex, count(*) from student3 where age > 25 group by sex ;
【案例3】查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据。
-- 对分组查询的结果再进行过滤
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
- having 与 where 的区别：
where 子句 ：
-
对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。
-
where 后面不可以使用聚合函数
having 子句：
-
having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
-
having 后面可以使用聚合函数
补充：select中字段是否必须出现在groupby中？
经过查阅大部分资料：
这在MySQL中可以是合法的，但是不一定有意义。在ORACLE中则会报错。
在MySQL升级到5.7.X或者更高的版本之后，出现了only_full_group_by这种sql模式，在only_full_group_by这个模式下，我们使用分组查询时，出现在select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段。
一些以前看上去不会出错的group by 操作在这个版本以后就会出现语法报错的情况：
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column list which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
可以通过修改配置的方式修改，让select中出现group by 中不存在的字段合法：mysql5.7.X版本only_full_group_by问题解决
但是要以实际情况决择，可能会出现一些没有意义的数据。
举个例子：
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO user VALUES ("1", "张三丰", 13);
INSERT INTO user VALUES ("2", "刘诗诗", 13);
INSERT INTO user VALUES ("3", "吴奇隆", 13);
INSERT INTO user VALUES ("4", "杨过", 14);
INSERT INTO user VALUES ("6", "杨迪", 12);
INSERT INTO user VALUES ("7", "欧阳锋", 12);
INSERT INTO user VALUES ("8", "尹志平", 14);
INSERT INTO user VALUES ("9", "郭靖", 13);
INSERT INTO user VALUES ("10", "慕容复", 14);
INSERT INTO user VALUES ("5", "小龙女", 12);
按照age分组查询：
mysql> select * from user GROUP BY age;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | 小龙女 | 12 |
| 1 | 张三丰 | 13 |
| 4 | 杨过 | 14 |
+----+-----------+------+
3 rows in set (0.00 sec)
执行sql,发现能正常执行,而且group by后默认取每组第一条数据(如age=12的分组中，本来有三个人，但是取了id最小的小龙女)。
得知这是mysql的设计，当group by 字段和select 多字段是唯一的一个分组时，这样能提高效率。
1.9.7 分页查询
-
应用场景：
实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
-
语法：
select 字段|表达式,…
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
LIMIT offset,length; -
特点：
1.offset：起始行数，从 0开始计数，如果省略，默认就是0
2.limit 子句放在查询语句的最后
3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page
1.9.8 联合查询
-
语法：
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
…
select 字段|常量|表达式|函数 【from 表】 【where 条件】 -
特点：
1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重
1.10 常见函数
1.10.1 字符串函数
- length(str) 函数的返回值为字符串的字节长度
mysql> select length('lilair'),length('李莱尔');
+------------------+---------------------+
| length('lilair') | length('李莱尔') |
+------------------+---------------------+
| 6 | 9 |
+------------------+---------------------+
- concat(sl，s2，…) 函数返回结果为连接参数产生的字符串 若有任何一个参数为 NULL，则返回值为 NULL
mysql> SELECT concat('我','是不是','煞笔' ) as descripetion1,
-> concat('我','是不是',null ) as descripetion2;
+--------------------+---------------+
| descripetion1 | descripetion2 |
+--------------------+---------------+
| 我是不是煞笔 | NULL |
+--------------------+---------------+
1 row in set (0.00 sec)
- upper,lower是大小写转换函数
mysql> SELECT LOWER('BLUE'),LOWER('Blue'),UPPER('green'),UPPER('Green');
+---------------+---------------+----------------+----------------+
| LOWER('BLUE') | LOWER('Blue') | UPPER('green') | UPPER('Green') |
+---------------+---------------+----------------+----------------+
| blue | blue | GREEN | GREEN |
+---------------+---------------+----------------+----------------+
- left,right是截取左边或右边字符串函数
mysql> select left('abcdefgh',3),right('abcdefg',2);
+--------------------+--------------------+
| left('abcdefgh',3) | right('abcdefg',2) |
+--------------------+--------------------+
| abc | fg |
+--------------------+--------------------+
- replace(s，s1，s2) 使用字符串 s2 替换字符串 s 中所有的字符串 s1
mysql> select replace ('I love Teacher Yu','Yu','yu');
+-----------------------------------------+
| replace ('I love Teacher Yu','Yu','yu') |
+-----------------------------------------+
| I love Teacher yu |
+-----------------------------------------+
- substring(str，pos ，len)，其中三个参数分别表示：参数1表示需要截取的字符串，参数2表示从字符串的那个位置开始截取（字符串下标从1开始），参数3表示要截取多少位，如果不写，表示截取从参数2指定的位置开始剩下的全部字符。pos 可以是负值，但 len 不能取负值。
-- 从字符串的第5个字符位置开始取，直到结束。
mysql> select substring('www.lilair.com',5);
+-------------------------------+
| substring('www.lilair.com',5) |
+-------------------------------+
| lilair.com |
+-------------------------------+
-- 从字符串的第5个字符位置开始取，取 6 个字符。
mysql> select substring('www.lilair.com',5,6);
+---------------------------------+
| substring('www.lilair.com',5,6) |
+---------------------------------+
| lilair |
+---------------------------------+
-- 从字符串的倒数第 3 个字符位置开始取，直到结束。
mysql> select substring('www.lilair.com',-3);
+--------------------------------+
| substring('www.lilair.com',-3) |
+--------------------------------+
| com |
+--------------------------------+
-- 从字符串的倒数第 3 个字符位置开始取,取两个字符。
mysql> select substring('www.lilair.com',-3,2);
+----------------------------------+
| substring('www.lilair.com',-3,2) |
+----------------------------------+
| co |
+----------------------------------+
- trim去前后指定的空格和字符, ltrim去左边空格,rtrim去右边空格
mysql> select trim(' lilair '),ltrim(' lialir '),rtrim(' lilair ');
+---------------------+----------------------+----------------------+
| trim(' lilair ') | ltrim(' lialir ') | rtrim(' lilair ') |
+---------------------+----------------------+----------------------+
| lilair | lialir | lilair |
+---------------------+----------------------+----------------------+
- lpad(str1,length,str2)。其中str1是第一个字符串，length是结果字符串的长度，str2是一个填充字符串。如果str1长度小宇length长度，则使用str2填充；如果str1的长度大于length，则截断。RPAD()同理。
mysql> select lpad ('lilair',3,'A'),lpad('lilair',16,'A');
+-----------------------+-----------------------+
| lpad ('lilair',3,'A') | lpad('lilair',16,'A') |
+-----------------------+-----------------------+
| lil | AAAAAAAAAAlilair |
+-----------------------+-----------------------+
1 row in set (0.00 sec)
- reverse(str) 返回颠倒字符串str的结果
1.10.2 数学函数
-
ABS() 函数求绝对值
-
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
-
MOD(x,y) 取模函数 , 返回 x 被 y 除后的余数
-
RAND() 随机函数，函数被调用时，可以产生一个在 0 和 1 之间的随机数
mysql> select rand(),rand(),rand();
+--------------------+--------------------+--------------------+
| rand() | rand() | rand() |
+--------------------+--------------------+--------------------+
| 0.4670583936079018 | 0.4992880024940595 | 0.0952648623802372 |
+--------------------+--------------------+--------------------+
- FLOOR向下取整， CEIL(x)向上取整
mysql> select ceil(1.666),floor(1.666),ceil(-1.5),floor(-1.5);
+-------------+--------------+------------+-------------+
| ceil(1.666) | floor(1.666) | ceil(-1.5) | floor(-1.5) |
+-------------+--------------+------------+-------------+
| 2 | 1 | -1 | -2 |
+-------------+--------------+------------+-------------+
- TRUNCATE(X,D) x代表数字，D代表的需要保留的小数位数
mysql> select truncate(1.3333333,3),truncate(1.2222222,2),truncate(1.0,3);
+-----------------------+-----------------------+-----------------+
| truncate(1.3333333,3) | truncate(1.2222222,2) | truncate(1.0,3) |
+-----------------------+-----------------------+-----------------+
| 1.333 | 1.22 | 1.000 |
+-----------------------+-----------------------+-----------------+
1.10.3 日期函数
| 函数 | 描述 |
|---|---|
| now() | 返回当前的日期和时间 |
| curdate() | 返回当前的日期 |
| curtime() | 返回当前的时间 |
| date(date_expression) | 提取date_expression日期部分 |
| time(date_expression) | 提取date_expression时间部分 |
| day(date_expression) | 返回date_expression中的日期值 |
| month(date_expression) | 返回date_expression中的月份值 |
| year(date_expression) | 返回date_expression中的年份值 |
| date_add(date,interval expr type) | 给日期date添加指定的时间间隔 |
| date_sub(date,interval expr type) | 从日期date减去指定的时间间隔 |
| datediff(date1,date2) | 返回两个日期之间的天数 |
| timediff(date1,date2) | 返回两个日期之间的时间 |
| date_format(date,format) | 将日期转换成字符 |
| str_to_date() | 将字符转换成日期 |
- now(),curdate(),curtime(),date()
mysql> select now(),curdate(),curtime(),date('2019-12-12 16:20:30');
+---------------------+------------+-----------+-----------------------------+
| now() | curdate() | curtime() | date('2019-12-12 16:20:30') |
+---------------------+------------+-----------+-----------------------------+
| 2020-07-09 13:25:24 | 2020-07-09 | 13:25:24 | 2019-12-12 |
+---------------------+------------+-----------+-----------------------------+
1 row in set (0.10 sec)
- date(),time(),day(),month(),year()
mysql> select date('2018-09-07 12:32:32') date,time('2018-09-07 12:32:32') time;
+------------+----------+
| date | time |
+------------+----------+
| 2018-09-07 | 12:32:32 |
+------------+----------+
-
date_add(date,interval expr type) ,date_sub(date,interval expr type)
date参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。type 时间单位，如MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER，YEAR
mysql> select date_add('2020-12-12 00:00:00',interval 2 day) twoday,
-> date_sub('2020-12-12 00:00;00',interval 1 hour) onehour;
+---------------------+---------------------+
| twoday | onehour |
+---------------------+---------------------+
| 2020-12-14 00:00:00 | 2020-12-11 23:00:00 |
+---------------------+---
- datediff(date1,date2),timediff(date1,date2)
mysql> SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate,
-> TIMEDIFF('2008-12-30 00:00:00','2008-12-29 00:00:00') AS DiffTime;
+----------+----------+
| DiffDate | DiffTime |
+----------+----------+
| 1 | 24:00:00 |
+----------+----------+
- str_to_date 将字符转换成日期
mysql> SELECT STR_TO_DATE('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s') AS result;
+---------------------+
| result |
+---------------------+
| 2017-01-06 10:20:30 |
+---------------------+
1 row in set (0.01 sec)
- date_format将日期转换成字符
mysql> SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') from dual;
+----------------------------------------+
| DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') |
+----------------------------------------+
| 2020-07-09 14:14:34 |
+----------------------------------------+
1.10.4 条件判断函数
- if 函数：if else 的效果
IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false
SELECT IF(10<5,'大','小'); #第一个表达式为真则返回第二个参数否则返回第三个
#案例:
SELECT
last_name,
commission_pct,
IF(commission_pct IS NULL,'没奖金','有奖金')
FROM
employees;
- case函数: 类似于switch case 的效果
#案例1:查询员工的工资部门号=30，工资为1.1倍；40，1.2倍;50,1.3倍;其他部门为原工资
SELECT salary AS 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary #相当于switch中的default:
END AS 新工资 #结束
FROM employees;
#案例2:查询员工的工资的情况
SELECT
salary,
CASE
WHEN salary >20000 THEN 'a'
WHEN salary >15000 THEN 'b'
WHEN salary >10000 THEN 'c'
ELSE 'd'
END AS 等级
FROM
employees;
- IFNULL(expression, alt_value)
它有两个参数，并且对第一个参数进行判断。如果第一个参数不是NULL，函数就会向调用者返回第一个参数；如果是NULL,将返回第二个参数。
mysql> select ifnull('lilair','流弊'),ifnull(null,'煞笔');
+---------------------------+-----------------------+
| ifnull('lilair','流弊') | ifnull(null,'煞笔') |
+---------------------------+-----------------------+
| lilair | 煞笔 |
+---------------------------+-----------------------+
1.10.5 聚合函数
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，
它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。
-
COUNT(col) 统计查询结果的行数
-
MIN(col) 查询指定列的最小值
-
MAX(col) 查询指定列的最大值
-
SUM(col) 求和，返回指定列的总和
-
AVG(col) 求平均值，返回指定列数据的平均值
特点：
- 以上五个分组函数都忽略null值，除了count()；
- sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型；
- 都可以搭配distinct使用，用于统计去重后的结果；
- count的参数可以支持：字段、常量值，一般放1 。建议使用 count(*)
说明：
- count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL。
- count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL。
- count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空。
2、数据库表的约束
约束的作用：对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。
约束种类：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GwNYNpkP-1594282103306)(E:\typora\1594116038698.png)]
2.1 主键约束
主键用来唯一标识数据库中的每一条记录。通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。
主键的特点是：非空、唯一。
2.1.1 创建主键
创建主键方式：
- 在创建表的时候给字段添加主键
-- 格式：字段名 字段类型 PRIMARY KEY
-- 创建表学生表 student, 包含字段(id, name, age)将 id 做为主键
CREATE TABLE student (
id INT PRIMARY KEY, -- id 为主键
name VARCHAR(20),
age INT
)
DESC st5;
- 在已有表中添加主键
ALTER TABLE表名ADD PRIMARY KEY(字段名);
ALTER TABLE student ADD PRIMARY KEY(id);
2.1.2 删除主键
-- 删除 student 表的主键
ALTER TABLE student DROP PRIMARY KEY;
-- 个人认为：可能因为一个表的主键只有一个，所以不需要指定主键字段，然而外键则需要指定。
2.1.3 主键自增
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值。格式：AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
/*
CREATE TABLE 表名(
列名 INT PRIMARY KEY AUTO_INCREMENT
)
AUTO_INCREMENT= 起始值;(默认为1)
*/
-- 指定起始值为 1000
create table st4 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
) AUTO_INCREMENT = 1000;
-- 如果想创建表后修改起始值
ALTER TABLE st4 AUTO_INCREMENT = 2000;
DELETE删除所有的记录之后，自增长没有影响。TRUNCATE删除以后，自增长又重新开始。
2.2 唯一约束
唯一约束表示数据表中某一列不能出现重复的值。
格式：字段名 字段类型 UNIQUE
-- 创建学生表 st7, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学生
CREATE TABLE st7 (
id INT,
name VARCHAR(20) UNIQUE
)
2.3 非空约束
非空约束表示数据表的某一列不能为 null。
格式：字段名 字段类型 NOT NULL
-- 创建表学生表 st8, 包含字段(id,name,gender)其中 name 不能为 NULL
CREATE TABLE st8 (
id INT,
name VARCHAR(20) NOT NULL,
gender CHAR(1)
)
或者可以通过设置默认值来防止出现NULL：字段名 字段类型 DEFAULT
-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州
CREATE TABLE st9 (
id INT,
name VARCHAR(20),
address VARCHAR(20) DEFAULT '广州'
)
如果一个字段设置了非空与唯一约束，该字段与主键的区别：主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列。自增长只能用在主键上。
2.4 外键约束
2.4.1 应用场景
问题：当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，
实际应用中不能出现这种情况。employee 的 dep_id 中的数据只能是 department 表中存在的 id
目标: 需要约束 dep_id 只能是 department 表中已经存在 id
解决方式: 使用外键约束
外键约束用于多表中，解决数据冗余的问题。外键是在从表中与主表主键对应的那一列。主表是用来约束别人的表，从表是被别人约束的表。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p2h6nZmd-1594282103307)(E:\typora\1594108151868.png)]
2.4.2 创建外键
- 新建表时增加外键
-- 格式：[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(字段名)(必须唯一)
-- 创建从表 employee 并添加外键约束 emp_depid_fk
-- 多方，从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age INT ,
dep_id INT , -- 外键对应主表的主键
FOREIGN KEY (dep_id) REFERENCES department(id) -- 创建外键约束
)
- 已有表增加外键
-- 格式：ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(字段名);
-- 在 employee 表情存在的情况下添加外键
ALTER TABLE employee ADD CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES department(id);
2.4.3 删除外键
-- ALTER TABLE 从表 DROP FOREIGN KEY 外键名称;
-- 删除 employee 表的 emp_depid_fk 外键
ALTER TABLE employee DROP FOREIGN KEY emp_depid_fk;
2.4.4 外键的级联
在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作。
级联语法描述：
on update cascade：级联更新，创建表的时候创建级联。更新主表中的主键，从表中的外键列也自动同步更新
on delete cascade：级联删除
-- 创建 employee 表，添加级联更新和级联删除
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age INT,
dep_id INT, -- 外键对应主表的主键
-- 创建外键约束
CONSTRAINT emp_depid_fk
FOREIGN KEY (dep_id)
REFERENCES department(id)
ON UPDATE CASCADE
ON DELETE CASCADE
)
3 表连接查询
3.1多表查询
数据准备
# 创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
)
insert into dept (name) values ('云服务部'),('数据部'),('物联技术部')('综合管理部');
# 创建员工表
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('段誉','男',10000,'2016-08-28',1);
insert into emp(name,gender,salary,join_date,dept_id) values('萧峰','男',2100,'2016-12-22',2);
insert into emp(name,gender,salary,join_date,dept_id) values('慕容复','男',6540,'2021-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('黄蓉','女',8000,'2021-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('小龙女','女',9000,'2021-03-14',1);
insert into emp(name,gender,salary,join_date,dept_id) values('尹志平','男',19000,'2022-02-14');
多表查询的作用：如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。
3.1.1 多表查询的分类
多表查询：
- 内连接
- 隐式内连接
- 显示内连接
- 外连接
- 左外连接
- 右外连接
- 全外连接
3.2 笛卡尔积现象
3.2.1笛卡尔积现象
什么是笛卡尔积：
笛卡尔积就是在查询的时候回将两个表中的每一条数据都交叉相乘的结果展示给我们
-- 需求：查询所有的员工和所有的部门
mysql> select * from emp,dept;
+----+-----------+--------+--------+------------+---------+----+-----------------+
| id | name | gender | salary | join_date | dept_id | id | name |
+----+-----------+--------+--------+------------+---------+----+-----------------+
| 1 | 段誉 | 男 | 10000 | 2016-08-28 | 1 | 1 | 云服务部 |
| 1 | 段誉 | 男 | 10000 | 2016-08-28 | 1 | 2 | 数据部 |
| 1 | 段誉 | 男 | 10000 | 2016-08-28 | 1 | 3 | 物联技术部 |
| 1 | 段誉 | 男 | 10000 | 2016-08-28 | 1 | 4 | 综合管理部 |
| 2 | 萧峰 | 男 | 2100 | 2016-12-22 | 2 | 1 | 云服务部 |
| 2 | 萧峰 | 男 | 2100 | 2016-12-22 | 2 | 2 | 数据部 |
| 2 | 萧峰 | 男 | 2100 | 2016-12-22 | 2 | 3 | 物联技术部 |
| 2 | 萧峰 | 男 | 2100 | 2016-12-22 | 2 | 4 | 综合管理部 |
| 3 | 慕容复 | 男 | 6540 | 2021-08-08 | 2 | 1 | 云服务部 |
| 3 | 慕容复 | 男 | 6540 | 2021-08-08 | 2 | 2 | 数据部 |
| 3 | 慕容复 | 男 | 6540 | 2021-08-08 | 2 | 3 | 物联技术部 |
| 3 | 慕容复 | 男 | 6540 | 2021-08-08 | 2 | 4 | 综合管理部 |
| 4 | 黄蓉 | 女 | 8000 | 2021-10-07 | 3 | 1 | 云服务部 |
| 4 | 黄蓉 | 女 | 8000 | 2021-10-07 | 3 | 2 | 数据部 |
| 4 | 黄蓉 | 女 | 8000 | 2021-10-07 | 3 | 3 | 物联技术部 |
| 4 | 黄蓉 | 女 | 8000 | 2021-10-07 | 3 | 4 | 综合管理部 |
| 5 | 小龙女 | 女 | 9000 | 2021-03-14 | 1 | 1 | 云服务部 |
| 5 | 小龙女 | 女 | 9000 | 2021-03-14 | 1 | 2 | 数据部 |
| 5 | 小龙女 | 女 | 9000 | 2021-03-14 | 1 | 3 | 物联技术部 |
| 5 | 小龙女 | 女 | 9000 | 2021-03-14 | 1 | 4 | 综合管理部 |
| 6 | 尹志平 | 男 | 19000 | 2022-02-14 | NULL | 1 | 云服务部 |
| 6 | 尹志平 | 男 | 19000 | 2022-02-14 | NULL | 2 | 数据部 |
| 6 | 尹志平 | 男 | 19000 | 2022-02-14 | NULL | 3 | 物联技术部 |
| 6 | 尹志平 | 男 | 19000 | 2022-02-14 | NULL | 4 | 综合管理部 |
+----+-----------+--------+--------+------------+---------+----+-----------------+
3.2.2 过滤笛卡尔积
我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。
-- 设置过滤条件
select * from emp,dept where emp.`dept_id` = dept.`id`;
mysql> select * from emp,dept where emp.`dept_id` = dept.`id`;
+----+-----------+--------+--------+------------+---------+----+-----------------+
| id | name | gender | salary | join_date | dept_id | id | name |
+----+-----------+--------+--------+------------+---------+----+-----------------+
| 1 | 段誉 | 男 | 10000 | 2016-08-28 | 1 | 1 | 云服务部 |
| 2 | 萧峰 | 男 | 2100 | 2016-12-22 | 2 | 2 | 数据部 |
| 3 | 慕容复 | 男 | 6540 | 2021-08-08 | 2 | 2 | 数据部 |
| 4 | 黄蓉 | 女 | 8000 | 2021-10-07 | 3 | 3 | 物联技术部 |
| 5 | 小龙女 | 女 | 9000 | 2021-03-14 | 1 | 1 | 云服务部 |
+----+-----------+--------+--------+------------+---------+----+-----------------+
-- 查询员工和部门的名字
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;
3.3 内连接
用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键
3.3.1 隐式内连接
隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定
-- SELECT 字段名 FROM 左表, 右表 WHERE 条件
3.3.2 显式内连接
显示内连接：使用 INNER JOIN … ON 语句, 可以省略 INNER
-- SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
查询小龙女的信息，显示员工 id，姓名，性别，工资和所在的部门名称，我们发现需要联合 2 张表同时才能
查询出需要的数据，使用内连接
- 确定查询哪些表 ,如果表很多，为防止字段歧义，给表取别名
select * from emp inner join dept;
- 确定表连接条件，员工表.dept_id = 部门表.id 的数据才是有效的
select * from emp e inner join dept d on e.`dept_id` = d.`id`;
- 确定查询条件，我们查询的是小龙女的信息，员工表.name=‘小龙女’
select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='小龙女';
- 确定查询字段，查询小龙女的信息，显示员工 id，姓名，性别，工资和所在的部门名称
SELECT
e.`id`,
e.`name`,
e.`gender`,
e.`salary`,
d.`name`
FROM
emp e
INNER JOIN dept d ON e.`dept_id` = d.`id`
WHERE
e.`name` = '小龙女';
- 为了显示方便好看，显示的字段名也使用别名
SELECT
e.`id` 编号,
e.`name` 姓名,
e.`gender` 性别,
e.`salary` 工资,
d.`name` 部门名字
FROM
emp e
INNER JOIN dept d ON e.`dept_id` = d.`id`
WHERE
e.`name` = '小龙女';
3.4 外连接
外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。
三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。
3.4.1 左外连接
左外连接：使用 LEFT OUTER JOIN … ON，OUTER 可以省略
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL 。
可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)
– 使用内连接查询
mysql> select * from dept d inner join emp e on d.`id` = e.`dept_id`;
+----+-----------------+----+-----------+--------+--------+------------+---------+
| id | name | id | name | gender | salary | join_date | dept_id |
+----+-----------------+----+-----------+--------+--------+------------+---------+
| 1 | 云服务部 | 1 | 段誉 | 男 | 10000 | 2016-08-28 | 1 |
| 2 | 数据部 | 2 | 萧峰 | 男 | 2100 | 2016-12-22 | 2 |
| 2 | 数据部 | 3 | 慕容复 | 男 | 6540 | 2021-08-08 | 2 |
| 3 | 物联技术部 | 4 | 黄蓉 | 女 | 8000 | 2021-10-07 | 3 |
| 1 | 云服务部 | 5 | 小龙女 | 女 | 9000 | 2021-03-14 | 1 |
+----+-----------------+----+-----------+--------+--------+------------+---------+
– 使用左外连接查询
mysql> select * from dept d left join emp e on d.`id` = e.`dept_id`;
+----+-----------------+------+-----------+--------+--------+------------+---------+
| id | name | id | name | gender | salary | join_date | dept_id |
+----+-----------------+------+-----------+--------+--------+------------+---------+
| 1 | 云服务部 | 1 | 段誉 | 男 | 10000 | 2016-08-28 | 1 |
| 2 | 数据部 | 2 | 萧峰 | 男 | 2100 | 2016-12-22 | 2 |
| 2 | 数据部 | 3 | 慕容复 | 男 | 6540 | 2021-08-08 | 2 |
| 3 | 物联技术部 | 4 | 黄蓉 | 女 | 8000 | 2021-10-07 | 3 |
| 1 | 云服务部 | 5 | 小龙女 | 女 | 9000 | 2021-03-14 | 1 |
| 4 | 综合管理部 | NULL | NULL | NULL | NULL | NULL | NULL |
3.4.2 右外连接
右外连接：使用 RIGHT OUTER JOIN … ON，OUTER 可以省略
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL
可以理解为：在内连接的基础上保证右表的数据全部显示
– 使用右外连接查询
mysql> select * from dept right join emp on dept.`id` = emp.`dept_id`;
+------+-----------------+----+-----------+--------+--------+------------+---------+
| id | name | id | name | gender | salary | join_date | dept_id |
+------+-----------------+----+-----------+--------+--------+------------+---------+
| 1 | 云服务部 | 1 | 段誉 | 男 | 10000 | 2016-08-28 | 1 |
| 2 | 数据部 | 2 | 萧峰 | 男 | 2100 | 2016-12-22 | 2 |
| 2 | 数据部 | 3 | 慕容复 | 男 | 6540 | 2021-08-08 | 2 |
| 3 | 物联技术部 | 4 | 黄蓉 | 女 | 8000 | 2021-10-07 | 3 |
| 1 | 云服务部 | 5 | 小龙女 | 女 | 9000 | 2021-03-14 | 1 |
| NULL | NULL | 6 | 尹志平 | 男 | 19000 | 2022-02-14 | NULL |
3.4.3 全外连接
全外连接：使用 FULL OUTER JOIN … ON，OUTER 可以省略
SELECT 字段名 FROM 左表 FULL [OUTER] JOIN 右表 ON 条件
全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。
-- mysql 不支持 直接写full outer join 或者 full join来表示全外连接
mysql> select * from dept full join emp on dept.`id` = emp.`dept_id`;
ERROR 1054 (42S22): Unknown column 'dept.id' in 'on clause'
-- 使用左外 UNION 右外代替
mysql> SELECT * FROM dept RIGHT JOIN emp ON dept.`id` = emp.`dept_id`
-> UNION
-> SELECT * FROM dept LEFT JOIN emp ON dept.id = emp.dept_id;
+------+-----------------+------+-----------+--------+--------+------------+---------+
| id | name | id | name | gender | salary | join_date | dept_id |
+------+-----------------+------+-----------+--------+--------+------------+---------+
| 1 | 云服务部 | 1 | 段誉 | 男 | 10000 | 2016-08-28 | 1 |
| 2 | 数据部 | 2 | 萧峰 | 男 | 2100 | 2016-12-22 | 2 |
| 2 | 数据部 | 3 | 慕容复 | 男 | 6540 | 2021-08-08 | 2 |
| 3 | 物联技术部 | 4 | 黄蓉 | 女 | 8000 | 2021-10-07 | 3 |
| 1 | 云服务部 | 5 | 小龙女 | 女 | 9000 | 2021-03-14 | 1 |
| NULL | NULL | 6 | 尹志平 | 男 | 19000 | 2022-02-14 | NULL |
| 4 | 综合管理部 | NULL | NULL | NULL | NULL | NULL | NULL |
+------+-----------------+------+-----------+--------+--------+------------+---------+
4. 子查询
4.1子查询的概念
- 一个查询的结果做为另一个查询的条件
- 有查询的嵌套，内部的查询称为子查询
- 子查询要使用括号
4.2 子查询结果分类
4.2.1 单行单列
子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、= 等
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）
【案例1】查询工资最高的员工是谁？
-- 1) 查询最高工资是多少
select max(salary) from emp;
-- 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);
【案例2】查询工资小于平均工资的员工有哪些？
-- 1) 查询平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);
4.2.2 多行单列
子查询结果是单例多行，结果集类似于一个数组，父查询使用 in运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
【案例1】 查询工资大于 5000 的员工，来自于哪些部门的名字
-- 先查询大于 5000 的员工所在的部门 id
select dept_id from emp where salary > 5000;
-- 再查询在这些部门 id 中部门的名字
select name from dept where id in (select dept_id from emp where salary > 5000);
【案例2】查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的 id
select id from dept where name in('开发部','财务部');
-- 再查询在这些部门 id 中有哪些员工
select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));
4.2.3 多行多列
子查询结果只要是多列，肯定在 FROM 后面作为表
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件
子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段
【案例1】 查询出 2011 年以后入职的员工信息，包括部门名称
-- 在员工表中查询 2011-1-1 以后入职的员工
select * from emp where join_date >='2011-1-1';
-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;
-- 此例也可以使用表连接：
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';
5. SQL执行顺序
书写顺序
SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT < limit_number >
执行顺序
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
详细查看：步步深入：MySQL架构总览->查询执行流程->SQL解析顺序
注意其中groupby说法有误，可参考本文1.9.6分组函数
1862
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
