MySQL 基础学习
1. 数据库概述
1. 为什么要使用数据库
为了持久化数据, 把数据保存到可掉电式存储设备中以供之后使用。持久化的主要作用是将内存中的数据存储在关系型数据库中, 当然也可以存储在磁盘文件、XML 数据文件中。但是数据量大的时候MySQL效率更高.
2. 数据库与数据库管理系统
2.1 数据库的相关概念
- DB: 数据库(Database): 本质是一个文件系统
- DBMS: 数据库管理系统(DB Management System): 一种操作数据库的软件, 用域建立、使用和维护数据库
- SQL: 结构化查询语言
2.2 数据库与数据库管理系统的关系
MySQL其实是数据库管理系统
2.3 常见的数据库管理系统
MySQL, Oracle, Redis, MongDB, PostgreSQL, SQLite 等等
3. MySQL 介绍
Sun 收购 MySQL, 然后 Oracle 收购 SUN.
MySQL 6.x 之后分为社区版和商业版
MySQL 在32最大表文件最大4GB, 64位支持最大8TB表文件
存储引擎一开始用的 MyISAM, 后面转为默认 InnoDB 引擎.
Oracle 适合大公司, 对于费用不敏感的.
4. RDMBS 和 非RDMBS
RDBMS 是现在的一个主流, 使用最多的是 Oracle, MySQL, SQL server, 都是关系型数据库.
4.1 关系型数据库
关系型数据库模型十八复杂的数据结构归结位简单的二元关系, 即二维表
优势:
- 复杂查询: 可以用SQL语句方便地在一个表和多个表之间做复杂的数据查询
- 事务支持, 是的对于安全性能很高的数据访问要求得以实现
4.2 非关系型数据库
可以看成传统关系型数据库的功能阉割版, 轻量版, 基于对键值对存储数据, 不需要经过SQL层的解析, 性能非常高。同时减少不常用的功能进一步提高性能。
- 键值型的数据库 Redis K:V
- 文档型的数据库 MongoDB, 可以存放并获取文档, 可以是XML, JSON等格式.
- 搜索引擎数据库: Solr, ES
- 列式数据库： 相对于MySQL这种行式存储, 适合用于分布式文件系统, HBase
- 图数据库: Neo4J
NoSQL 对于关系型数据库是一个很好的补充, 用于一些适合非关系数据库的场景, 比如日志收集, 排行榜, 定时器等
4.3 小结
NoSQL 分类很多, 除去SQL的都算NoSQL, 但是还是SQL的阵营很强大.
5. 关系型数据库设计规则
- 关系型数据库的典型数据结构就是数据表, 数据表都是结构化的
- 将数据放到表中, 表放到数据库中
- 一个数据库中可以有多个表, 每个表都有一个名字, 具有唯一性
5.1 表、记录、字段
- E-R（Entity-relationship, 实体-联系）模型中有个三个主要概念是：实体集、属性、联系集
- 一个实体集（class）对应于数据库中的一个表（table）, 一个实体（instance）则对应数据库表中的一行（row）, 也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column）, 也成为一个字段（filed）。
ORM 思想（Object Relational Mapping）体现：
数据库一个表 对应 Java中一个类
表中一条数据 对应 一个对象, 实体
表中一个列 对应 类中的一个字段或属性
5.2 表的关联关系
- 表与表之间的数据记录有关系（relationship）。现实世界中的各种实体以及实体之间的各种联系均用关系模型表示
- 四种：一对一关联、一对多关联, 多对多关联, 自我引用
5.2.1 一对一关联（one-to-one）
- 实际的开发中应用不对, 因为效率不高
- 例如设计学生表：学号、姓名、手机号、班级、身份证号、籍贯。。。。。
- 可以拆分成两个表
- 基础信息表（常用信息表）：学号、姓名、手机号、班级
- 档案信息表（不常用信息表）：学号、籍贯、家庭住址
- 两种建表原则
- 外键唯一：主表的逐渐和从表的外键（唯一）, 形成主外键关系, 外键唯一
- 外键是逐渐：主表的主键和从表的主键, 形成主外键关系。
5.2.2 一对多关系（one-to-many）
- 常见示例场景：客户表和订单表, 分类表和商品表, 部门表和员工表
一个部门编号, 在别的表里一个部门编号会对应多个员工
5.2.3 多对多关系（many-to-many）
要表示多对多关系, 必须创建第三个表, 该表通常称为联接表, 他将多对多关系划分为两个一对多关系, 将这两个表的主键都插入到第三个表中
- 例如：学生和课程
- 例如: 用户和角色
5.2.4 自我引用（self refence）
比如员工表, 有一个字段是该员工的主管编号, 这个字段来自于员工表里的员工编号. 因此是自我引用.
2. MySQL 环境搭建
1. MySQL的卸载
Windows
mysql --version
-
软件位置：你看你自己安装的时候设置的
-
数据文件一般在 C:\ProgramData\MySQL\xxxx\Data
例如我的：C:\ProgramData\MySQL\MySQL Server 8.2\Data -
服务：我的电脑->管理->服务->MySQL80-> 设置停止
-
环境变量里也有MySQL, 也需要删除
通过控制面板卸载 MySQL, 或者用安装的时候的工具去卸载, 数据文件此时还会在
之前5.7的版本卸载需要手动删除注册表（regedit中删除）：
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet{xxx}\Services\Eventlog\Application\MySQL服务
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet{xxx}\Services\MySQL服务
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL服务
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL服务
最后重启电脑
2. MySQL 的下载
我们装社区版就行了.
还有一个集群版 MySQL Cluster 集群版(免费)
还有一个 MySQL Workbench, 一款专门为 MySQL 设计的图形管理工具
安装 MySQL 社区版, 然后配置环境变量, 把你的安装位置的 bin 目录放到环境变量里
然后还装一个 5.7, 这个要招 msi文件去 MySQL for windows 里面找
这时候同时有两个MySQL
# 登录 mysql8
mysql -uroot -h localhost -P 3306 -p
# 登录 mysql5.7
mysql -uroot -h localhost -P 13306 -p
# 这是因为我把 8的端口设置为 3306
# 把5.7的端口设置成了 13306
# 退出
quit # 或 exit
MySQL 5.7 默认的字符编码是 latin1, 我们可以用
-- 查看默认使用的字符集
show variables like 'character_%';
show variables like 'collation_%';
在 my.ini 下面修改
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
我的这个文件在 C:\ProgramData\MySQL\MySQL Server 5.7 下面
重启服务, 然后就会发现生效了.
抽象问题
密码重置
- 先关闭 mysqld 服务
- 通过特殊参数开启mysqld
mysqld --defaults-file="xxxx\my.ini"
- 此时 mysqld 已打开
- mysql -uroot 直接登录服务器
- 修改权限表
use mysql;
update user set authentication_string=password('123456') where user='root;
flush privileges;
3. 基本的SELECT语句
1. SQL 概述
SQL的分类
- DDL: 数据定义语言
- CREATE
- ALTER
- DROP
- RENAME
- TRUNCATE
- DML: 数据操作语言
- INSERT
- DELETE
- UPDATE
- SELECT
- DCL: 数据控制语言
- COMMIT
- ROLLBACK
- SAVEPOINT
- GRANT
- REVOKE
2. SQL语言的规则和规范
2.1 基本规则
- SQL 可以写在一行或者多行, 为了提高可读性, 各句子分行写, 必要时使用缩进.
- 每条命令以
;或\g或\G结束 - 关键字不能被缩写也不能分行
- 关于标点符号
- 必须保证所有的
()、单引号、双引号是成对出现的 - 必须使用英文状态下的半角输入方式
- 字符串型和日期时间类型的数据可以使用单引号
' '表示 - 列的别名, 尽量使用双引号
"", 并且不建议省略as
- 必须保证所有的
2.2 SQL 大小写规范
- MySQL 在 Windows 环境下是大小写不敏感的
- MySQL 在 Linux 环境下是大小写敏感的
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列名、列的别名是不区分大小写的
- 推荐采用统一的书写规范
- 数据库名、表明、表别名、字段名、字段别名等都是小写
- SQL 关键字、函数名、绑定变量等都是大写
2.3 注释
可以使用如下格式的注释结构
-- 单行注释 -- 要加上空格
# 单行注释(MySQL特有)
/* 多行注释 */
2.4 命名规则
2.5 数据导入导出
导入现有的数据表、表的数据
方式1: source 文件的全路径名
mysql -uroot -p
# 登入后
source xxx.sql;
方式2: 基于具体的图形化界面的工具可以导入数据
3. 基本的 SELECT 语句
3.0 SELECT
最基本的 SELECT 语句
SELECT 1;
-- mysql> select 1;
-- +---+
-- | 1 |
-- +---+
-- | 1 |
-- +---+
-- 1 row in set (0.00 sec)
SELECT 1+1;
-- mysql> select 1+1;
-- +-----+
-- | 1+1 |
-- +-----+
-- | 2 |
-- +-----+
-- 1 row in set (0.00 sec)
--
SELECT 1+1, 3*2;
-- mysql> SELECT 1+1, 3*2;
-- +-----+-----+
-- | 1+1 | 3*2 |
-- +-----+-----+
-- | 2 | 6 |
-- +-----+-----+
-- 1 row in set (0.00 sec)
最基本的 SELECT 语句: SELECT 字段1, 字段2, … FROM 表名;
*：表中的所有字段
SELECT * FROM employees;
mysql> SELECT * FROM employees;
-- +-------------+------------+-----------+------+---------------+------------+---------+--------+----------------+------------+---------------+
-- | employee_id | first_name | last_name | email| phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id |
-- +-------------+------------+-----------+------+---------------+------------+--------+--------+----------------+------------+---------------+
SELECT employee_id, last_name, salary FROM employees;
-- +-------------+-------------+----------+
-- | employee_id | last_name | salary |
-- +-------------+-------------+----------+
-- | 100 | King | 24000.00 |
-- | 101 | Kochhar | 17000.00 |
列的别名
SELECT employee_id, last_name, department_id FROM employees;
-- +-------------+-------------+---------------+
-- | employee_id | last_name | department_id |
-- +-------------+-------------+---------------+
-- | 100 | King | 90 |
-- | 101 | Kochhar | 90 |
-- as 的全程是 alias, 可以省略
SELECT employee_id, last_name as lname, department_id as dep_id FROM employees;
-- +-------------+-------------+--------+
-- | employee_id | lname | dep_id |
-- +-------------+-------------+--------+
-- | 100 | King | 90 |
-- | 101 | Kochhar | 90 |
-- 别名可以用双引号引起来, 不要使用单引号
SELECT employee_id, last_name as "lname", department_id as "dep id" FROM employees;
-- +-------------+-------------+--------+
-- | employee_id | lname | dep id |
-- +-------------+-------------+--------+
-- | 100 | King | 90 |
-- | 101 | Kochhar | 90 |
-- 别名可以用反引号引起来
SELECT employee_id, last_name as `lname`, department_id as `dep id` FROM employees;
-- +-------------+-------------+--------+
-- | employee_id | lname | dep id |
-- +-------------+-------------+--------+
-- | 100 | King | 90 |
-- | 101 | Kochhar | 90 |
去除重复行
SELECT DISTINCT department_id FROM employees;
-- +---------------+
-- | department_id |
-- +---------------+
-- | NULL |
-- | 10 |
-- | 20 |
-- DISTINCT 需要放到所有列名的前面
SELECT salary, DISTINCT department_id FROM employees;
-- 上面这样会报错
空值参与运算
- 空值: null
- null 不等同于 0, 也不等同于空字符串
- 加减乘除等运算中, 只要有一个操作数为 null, 结果都是 null
- 实际的解决方案: 使用 IFNULL 函数, IFNULL(列名, 替代值)
SELECT 1+NULL;
-- +--------+
-- | 1+NULL |
-- +--------+
-- | NULL |
-- +--------+
-- 1 row in set (0.00 sec)
SELECT employee_id, salary as "月工资", salary * (1+commission_pct) * 12 as "年工资"
FROM employees;
-- +-------------+----------+-----------+
-- | employee_id | 月工资 | 年工资 |
-- +-------------+----------+-----------+
-- | 100 | 24000.00 | NULL |
-- | 101 | 17000.00 | NULL |
-- 因为有一些 commission_pct 为 NULL
着重号 ``
-- ORDER 是一个关键字;
-- 下面的 ORDER 是一个表名, 下面这样写就有问题
SELECT * FROM ORDER;
-- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER' at line 1
-- 应该要用着重号(反引号)包起来
SELECT * FROM `ORDER`;
-- +----------+------------+
-- | order_id | order_name |
-- +----------+------------+
-- | 1 | shkstart |
-- | 2 | tomcat |
-- | 3 | dubbo |
-- +----------+------------+
查询常数
SELECT "你好", order_name FROM `ORDER`;
-- +------+------------+
-- | 你好 | order_name |
-- +------+------------+
-- | 你好 | shkstart |
-- | 你好 | tomcat |
-- | 你好 | dubbo |
-- +------+------------+
4. 显示表格结构
DESC employees;
-- +----------------+-------------+------+-----+---------+-------+
-- | Field | Type | Null | Key | Default | Extra |
-- +----------------+-------------+------+-----+---------+-------+
-- | employee_id | int | NO | PRI | 0 | |
-- | first_name | varchar(20) | YES | | NULL | |
-- | last_name | varchar(25) | NO | | NULL | |
-- | email | varchar(25) | NO | UNI | NULL | |
-- | phone_number | varchar(20) | YES | | NULL | |
-- | hire_date | date | NO | | NULL | |
-- | job_id | varchar(10) | NO | MUL | NULL | |
-- | salary | double(8,2) | YES | | NULL | |
-- | commission_pct | double(2,2) | YES | | NULL | |
-- | manager_id | int | YES | MUL | NULL | |
-- | department_id | int | YES | MUL | NULL | |
-- +----------------+-------------+------+-----+---------+-------+
DESCRIBE employees; -- 这个也是一样的, DESC 是 DESCRIBE 的缩写
5. 过滤数据(条件查询)
SELECT * FROM employees;
-- 查出来了 107 条记录
SELECT * FROM employees WHERE department_id = 90;
-- 查出来了 3 条记录
-- 查询字段
SELECT *
-- 查询的表
FROM employees
-- 过滤条件
WHERE department_id = 90 AND salary > 10000;
Windows 下的 MySQL 默认是不区分大小写的(MySQL甚至字符串的值有时候都能不分大小写), Linux 下是区分大小写的.
作业:
- 查询员工12个月的工资总和, 起别名为 annual_salary
select employee_id, last_name, salary *12 as annual_salary from employees;
-- 基本工资+奖金
select employee_id, last_name, salary *12 * (1+IFNULL(commission_pct, 0)) as annual_salary from employees;
- 查询 employees 表中去除重复的 job_id 以后的数据
SELECT job_id FROM employees;
-- 107 rows in set (0.00 sec)
SELECT DISTINCT job_id FROM employees;
-- 19 rows in set (0.00 sec)
- 查询工资大于 12000 的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE salary > 12000;
- 查询员工号为176的员工的姓名和部门号
SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;
- 显示表 departments 的结构, 并查询其中的全部数据
DESC departments;
SELECT * FROM departments;
4. 运算符
1. 算数运算符
- 加法: +
- 减法: -
- 乘法: *
- 除法: / 或 DIV
- 取模: % 或 MOD
SELECT 1+1, 1-1, 2*3, 4/2, 5%2;
-- +-----+-----+-----+--------+------+
-- | 1+1 | 1-1 | 2*3 | 4/2 | 5%2 |
-- +-----+-----+-----+--------+------+
-- | 2 | 0 | 6 | 2.0000 | 1 |
-- +-----+-----+-----+--------+------+
-- 1 row in set (0.00 sec)
-- SQL 中 + 号没有连接的作用, 就是表示加法的运算结果. 因此下面会将字符串转换为数值
SELECT 100 + 'a', 100 + '10', 100 + '01';
-- +-----------+------------+------------+
-- | 100 + 'a' | 100 + '10' | 100 + '01' |
-- +-----------+------------+------------+
-- | 100 | 110 | 101 |
-- +-----------+------------+------------+
SELECT 100 + '10a';
-- +-------------+
-- | 100 + '10a' |
-- +-------------+
-- | 110 |
-- +-------------+
-- 如果有 null 值参与运算, 那么结果为 null
-- 除法中分母如果为0, 那么结果为 null
SELECT 12 %5.4;
-- +---------+
-- | 12 %5.4 |
-- +---------+
-- | 1.2 |
-- +---------+
-- 12 = 5.4*2 +1.2 = 10.8+1.2
SELECT 12 % -5;
-- +---------+
-- | 12 % -5 |
-- +---------+
-- | 2 |
-- +---------+
2. 比较运算符
2.1 符号类
- 等于: =
- 安全等于: <=>
- 不等于: != 或 <>
- 小于: <
- 小于等于: <=
- 大于: >
- 大于等于: >=
SELECT 1=2, 1!=2;
-- +-----+------+
-- | 1=2 | 1!=2 |
-- +-----+------+
-- | 0 | 1 |
SELECT 1='a';
-- +-------+
-- | 1='a' |
-- +-------+
-- | 0 |
-- +-------+
-- 如果字符串存在隐式转换, 如果转换数值不成功, 那么转换成0, 因此会有下面这种情况.
SELECT 0='a';
-- +-------+
-- | 0='a' |
-- +-------+
-- | 1 |
-- +-------+
SELECT 'a' = 'b';
-- +-----------+
-- | 'a' = 'b' |
-- +-----------+
-- | 0 |
-- +-----------+
-- 此时两边都是字符串, 那么就是字符串的比较
SELECT '你' = '你';
-- +-------------+
-- | '你' = '你' |
-- +-------------+
-- | 1 |
-- +-------------+
SELECT 1 = NULL, NULL=NULL;
-- +----------+-----------+
-- | 1 = NULL | NULL=NULL |
-- +----------+-----------+
-- | NULL | NULL |
-- +----------+-----------+
-- 只要有 NULL 参与比较, 那么就是NULL
-- 对是1, 错是0
SELECT last_name, salary
FROM employees
WHERE salary > 12000;
-- +-----------+----------+
-- | last_name | salary |
-- +-----------+----------+
-- | King | 24000.00 |
-- | Kochhar | 17000.00 |
-- 抽象的
SELECT last_name, salary, department_id
FROM employees
WHERE department_id = NULL;
-- Empty set (0.00 sec)
-- NULL 不能用 = 来判断, 要用 is null
SELECT last_name, salary, department_id
FROM employees
WHERE department_id is NULL;
-- +-----------+---------+---------------+
-- | last_name | salary | department_id |
-- +-----------+---------+---------------+
-- | Grant | 7000.00 | NULL |
-- +-----------+---------+---------------+
-- 安全等于也可以用于比较 NULL
SELECT 1 <=> NULL, NULL <=> NULL;
-- +------------+---------------+
-- | 1 <=> NULL | NULL <=> NULL |
-- +------------+---------------+
-- | 0 | 1 |
-- +------------+---------------+
SELECT last_name, salary, department_id
FROM employees
WHERE department_id <=> NULL;
-- IS NOT NULL 也可以写为 WHERE NOT DEPARTMENT_ID <=> NULL
-- +-----------+---------+---------------+
-- | last_name | salary | department_id |
-- +-----------+---------+---------------+
-- | Grant | 7000.00 | NULL |
-- +-----------+---------+---------------+
-- 安全等于在没有 NULL 的时候和等于是一样的
-- 安全等于相当于是专门增加来比较 NULL 的
SELECT 1>NULL, NULL != NULL, 0!=NULL, 0=NULL;
-- +--------+--------------+---------+--------+
-- | 1>NULL | NULL != NULL | 0!=NULL | 0=NULL |
-- +--------+--------------+---------+--------+
-- | NULL | NULL | NULL | NULL |
-- +--------+--------------+---------+--------+
-- 全是 NULL
2.2 非符号类
- IS NULL
- IS NOT NULL
- ISNULL(): 判断是否为 NULL
- LEAST(): 返回参数列表中的最小值
- GREATEST(): 返回参数列表中的最大值
- BETWEEN … AND …: 在某个范围内
- IN: 在某个集合内
- NOT IN: 不在某个集合内
- LIKE: 模糊查询
- REGEXP: 正则表达式
- RLIKE: 正则表达式
SELECT last_name, salary, department_id
FROM employees
-- WHERE department_id IS NULL;
WHERE ISNULL(department_id) ;
-- 最小
SELECT LEAST('g', 'b', 't', 'm') ;
-- +---------------------------+
-- | LEAST('g', 'b', 't', 'm') |
-- +---------------------------+
-- | b |
-- +---------------------------+
SELECT GREATEST('g', 'b', 't', 'm') ;
-- +------------------------------+
-- | GREATEST('g', 'b', 't', 'm') |
-- +------------------------------+
-- | t |
-- +------------------------------+
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE salary BETWEEN 10000 AND 20000;
-- 等价于 WHERE salary >= 10000 AND salary <= 20000
-- +-------------+-----------+----------+---------------+
-- | employee_id | last_name | salary | department_id |
-- +-------------+-----------+----------+---------------+
-- | 101 | Kochhar | 17000.00 | 90 |
-- | 102 | De Haan | 17000.00 | 90 |
-- | 108 | Greenberg | 12000.00 | 100 |
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE salary BETWEEN 20000 AND 10000;
-- Empty set (0.00 sec)
-- 第一个数是下界, 第二个数是上界, 因此是空集
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE NOT salary BETWEEN 10000 AND 20000;
-- 不在 10000 和 20000 之间的
-- +-------------+-------------+----------+---------------+
-- | employee_id | last_name | salary | department_id |
-- +-------------+-------------+----------+---------------+
-- | 100 | King | 24000.00 | 90 |
-- | 103 | Hunold | 9000.00 | 60 |
-- 查询部门为 10, 20, 30 的员工信息
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id IN (10, 20, 30);
-- +-------------+------------+---------------+
-- | employee_id | last_name | department_id |
-- +-------------+------------+---------------+
-- | 200 | Whalen | 10 |
-- | 201 | Hartstein | 20 |
-- | 114 | Raphaely | 30 |
-- 或者写抽象一点
SELECT employee_id, last_name, department_id
FROM employees
-- 这里要写完整, 不能写成
-- WHERE department_id IN 10 OR 20 OR 30
WHERE department_id = 10 OR department_id = 20 OR department_id = 30;
-- +-------------+------------+---------------+
-- | employee_id | last_name | department_id |
-- +-------------+------------+---------------+
-- | 200 | Whalen | 10 |
-- | 201 | Hartstein | 20 |
-- | 114 | Raphaely | 30 |
-- 查询部门不为 10, 20, 30 的员工信息
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id NOT IN (10, 20, 30);
-- +-------------+-------------+---------------+
-- | employee_id | last_name | department_id |
-- +-------------+-------------+---------------+
-- | 100 | King | 90 |
-- | 101 | Kochhar | 90 |
-- 模糊查询
-- % 代表不确定个数的字符, 也包括 0 个
SELECT last_name
FROM employees
WHERE last_name LIKE 'K%';
-- +-----------+
-- | last_name |
-- +-----------+
-- | King |
-- | Kochhar |
-- | Khoo |
SELECT last_name
FROM employees
WHERE last_name LIKE '%k%';
+-------------+
| last_name |
+-------------+
| Kaufling |
| Mikkilineni |
| Markle |
| Atkinson |
-- 这里是因为 MySQL 在 Windows 下是不区分大小写的, 所以抽象
-- 找包含字符 a 并且包含字符 k 的
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%k%';
-- 这种是不行的, 这种是 a一定在k前面的, 这个只能查出3个
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%k%';
-- 这个能查出 6个
-- 如果要查第二个字符为a的
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';
-- +-----------+
-- | last_name |
-- +-----------+
-- | Pataballa |
-- | Faviet |
-- | Raphaely |
-- 这里的 _ 代表一个字符
-- 查询第二个字符为 _ , 且第三个字符为 a 的
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';
-- 这里用转义字符
-- 或者
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';
-- ESCAPE '$' 是指定转义字符
-- 一般不用的
-- REGEXP 正则表达式
-- ^ 代表开头
-- $ 代表结尾
-- . 代表任意一个字符
-- * 代表前面的字符出现 0 次或多次
-- + 代表前面的字符出现 1 次或多次
-- ? 代表前面的字符出现 0 次或 1 次
-- [] 代表字符集合
-- [^] 代表非字符集合
-- | 代表或
-- 以 s 开头的 -> ^s
SELECT last_name
FROM employees
WHERE last_name REGEXP '^s';
-- +-----------+
-- | last_name |
-- +-----------+
-- | Sciarra |
-- | Stiles |
-- 以 t 结尾的 -> t$
SELECT last_name
FROM employees
WHERE last_name REGEXP 't$';
-- +-----------+
-- | last_name |
-- +-----------+
-- | Ernst |
-- | Faviet |
-- 以 e 开头并且以 t 结尾的
SELECT last_name
FROM employees
WHERE last_name REGEXP '^e.*t$';
-- +-----------+
-- | last_name |
-- +-----------+
-- | Ernst |
-- | Everett |
-- +-----------+
SELECT last_name
FROM employees
WHERE last_name LIKE 'e%t';
-- +-----------+
-- | last_name |
-- +-----------+
-- | Ernst |
-- | Everett |
-- +-----------+
3. 逻辑运算符
- NOT: 非(!)
- AND：与(&&)
- OR：或(||)
- XOR：异或
-- 异或可以认为是两个同时只满足一个. 也就是说两个都不满足或者两个都满足都不行
-- AND 的优先级大于别的.
4. 位运算符
会先将数组操作变成二进制数, 然后进行位运算, 最后将计算结果转换成十进制数
一般用不到.
- &: 按位与
- |: 按位或
- ^: 按位异或
- ~: 按位取反
- «: 左移
-
: 右移
5. 运算符的优先级
一般不用记, 用括号就好了.
6. 练习
-- 1. 选择工资不在5000到12000的员工的姓名和工资
SELECT last_name, salary
FROM employees
WHERE NOT salary BETWEEN 5000 AND 12000;
-- 这里也可以放在 NOT BETWEEN 5000 AND 12000
-- +-------------+----------+
-- | last_name | salary |
-- +-------------+----------+
-- | King | 24000.00 |
-- | Kochhar | 17000.00 |
-- | De Haan | 17000.00 |
-- 2. 选择在20或者50号部门工作的员工姓名或部门号
SELECT last_name, department_id
FROM employees
WHERE department_id IN (20, 50);
-- +-------------+---------------+
-- | last_name | department_id |
-- +-------------+---------------+
-- | Weiss | 50 |
-- | Fripp | 50 |
-- 3. 选择公司中没有管理者的员工姓名及job_id
SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;
-- +-----------+---------+
-- | last_name | job_id |
-- +-----------+---------+
-- | King | AD_PRES |
-- +-----------+---------+
-- 4. 选择公司中有奖金的员工姓名, 工资和奖金级别
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
-- +------------+----------+----------------+
-- | last_name | salary | commission_pct |
-- +------------+----------+----------------+
-- | Russell | 14000.00 | 0.40 |
-- | Partners | 13500.00 | 0.30 |
-- 5. 选择员工姓名的第三个字母是a的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
-- +-----------+
-- | last_name |
-- +-----------+
-- | Grant |
-- | Grant |
-- | Whalen |
-- +-----------+
-- 6. 选择员工中有字母 a 和 k 的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%k%';
-- +------------+
-- | last_name |
-- +------------+
-- | Kochhar |
-- | Kaufling |
-- | Markle |
-- 7. 显示出表 employee 表中 first_name 以 e 结尾的员工信息
SELECT first_name
FROM employees
WHERE first_name LIKE '%e';
-- +------------+
-- | first_name |
-- +------------+
-- | Bruce |
-- | Irene |
-- 8. 显示出表 employees 部门编号在 80 - 100 之间的姓名、工种
SELECT last_name, job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;
-- +------------+------------+
-- | last_name | job_id |
-- +------------+------------+
-- | King | AD_PRES |
-- | Kochhar | AD_VP |
-- | De Haan | AD_VP |
-- 9. 显示出表 employees 的manager_id 是 100, 101, 110 的员工姓名、工资、管理者 ID
SELECT last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 110);
-- +-----------+----------+------------+
-- | last_name | salary | manager_id |
-- +-----------+----------+------------+
-- | Kochhar | 17000.00 | 100 |
-- | De Haan | 17000.00 | 100 |
-- | Raphaely | 11000.00 | 100 |
5. 排序与分页
1. 排序数据
1.1 排序规则
-- 如果没有使用排序操作 ,默认情况下查询返回的数据是按照添加数据的顺序返回的
SELECT * FROM employees;
-- 我们希望按照 salary 从高到低的顺序显示员工信息
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC;
-- +-------------+-------------+----------+
-- | employee_id | last_name | salary |
-- +-------------+-------------+----------+
-- | 100 | King | 24000.00 |
-- | 101 | Kochhar | 17000.00 |
-- | 102 | De Haan | 17000.00 |
-- 升序: ASC （默认）
-- 降序: DESC
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary ASC;
-- +-------------+-------------+----------+
-- | employee_id | last_name | salary |
-- +-------------+-------------+----------+
-- | 132 | Olson | 2100.00 |
-- | 128 | Markle | 2200.00 |
-- | 136 | Philtanker | 2200.00 |
-- 列的别名只能在 ORDER BY 子句中使用, 不能在 WHERE 子句中使用
SELECT employee_id, last_name, salary as "月工资"
FROM employees
ORDER BY "月工资" DESC;
-- +-------------+-------------+----------+
-- | employee_id | last_name | 月工资 |
-- +-------------+-------------+----------+
-- | 100 | King | 24000.00 |
-- | 101 | Kochhar | 17000.00 |
-- | 102 | De Haan | 17000.00 |
SELECT employee_id, last_name, salary as "月工资"
FROM employees
WHERE "月工资" > 10000;
-- Empty set, 1 warning (0.00 sec)
-- 因为是先执行 FROM 子句, 然后才执行 SELECT 子句, 所以在 WHERE 子句中不能使用别名
-- 但是可以使用别名
SELECT employee_id, last_name, salary as "月工资"
FROM employees
WHERE salary > 10000;
-- +-------------+-----------+----------+
-- | employee_id | last_name | 月工资 |
-- +-------------+-----------+----------+
-- | 100 | King | 24000.00 |
-- | 101 | Kochhar | 17000.00 |
-- | 102 | De Haan | 17000.00 |
-- WHERE 是要在 ORDER BY 之前执行的, FROM 之后执行的
-- 因此可以
SELECT employee_id, last_name, salary as "月工资"
FROM employees
WHERE salary > 10000
ORDER BY "月工资" DESC;
+-------------+-----------+----------+
| employee_id | last_name | 月工资 |
+-------------+-----------+----------+
| 100 | King | 24000.00 |
| 101 | Kochhar | 17000.00 |
| 102 | De Haan | 17000.00 |
所以执行顺序是: FROM -> WHERE -> SELECT -> ORDER BY
1.2 单列排序
上面已经涵盖了.
1.3 多列排序
二级排序
按照 Department_id 降序, 然后按照 salary 升序排列
SELECT employee_id, last_name, salary, department_id
FROM employees
ORDER BY department_id DESC, salary ASC;
+-------------+-------------+----------+---------------+
| employee_id | last_name | salary | department_id |
+-------------+-------------+----------+---------------+
| 206 | Gietz | 8300.00 | 110 |
| 205 | Higgins | 12000.00 | 110 |
| 113 | Popp | 6900.00 | 100 |
| 111 | Sciarra | 7700.00 | 100 |
2. 分页
2.1 背景
- 一次性查询大量数据, 会导致网络传输压力大, 数据传输时间长
- 一次性查询大量数据, 会导致客户端内存压力大, 数据处理时间长
- 一次性查询大量数据, 会导致数据库服务器压力大, 数据处理时间长
- 一次性查询大量数据, 会导致客户端显示压力大, 数据显示时间长
2.2 实现规则
使用 LIMIT 实现数据的分页显示
每页显示20条记录
SELECT employee_id, last_name, salary, department_id
FROM employees
ORDER BY department_id DESC, salary ASC
LIMIT 0, 20;
+-------------+-----------+----------+---------------+
| employee_id | last_name | salary | department_id |
+-------------+-----------+----------+---------------+
| 206 | Gietz | 8300.00 | 110 |
| 205 | Higgins | 12000.00 | 110 |
| 113 | Popp | 6900.00 | 100 |
| 111 | Sciarra | 7700.00 | 100 |
| 112 | Urman | 7800.00 | 100 |
| 110 | Chen | 8200.00 | 100 |
| 109 | Faviet | 9000.00 | 100 |
| 108 | Greenberg | 12000.00 | 100 |
| 101 | Kochhar | 17000.00 | 90 |
| 102 | De Haan | 17000.00 | 90 |
| 100 | King | 24000.00 | 90 |
| 173 | Kumar | 6100.00 | 80 |
| 179 | Johnson | 6200.00 | 80 |
| 167 | Banda | 6200.00 | 80 |
| 166 | Ande | 6400.00 | 80 |
| 165 | Lee | 6800.00 | 80 |
| 155 | Tuvault | 7000.00 | 80 |
| 161 | Sewall | 7000.00 | 80 |
| 164 | Marvins | 7200.00 | 80 |
| 172 | Bates | 7300.00 | 80 |
+-------------+-----------+----------+---------------+
这里的 0 是偏移量, 20 是每页显示的记录数
如果要显示下一页
SELECT employee_id, last_name, salary, department_id
FROM employees
ORDER BY department_id DESC, salary ASC
LIMIT 20, 20;
+-------------+------------+----------+---------------+
| employee_id | last_name | salary | department_id |
+-------------+------------+----------+---------------+
| 171 | Smith | 7400.00 | 80 |
| 154 | Cambrault | 7500.00 | 80 |
| 160 | Doran | 7500.00 | 80 |
| 153 | Olsen | 8000.00 | 80 |
| 159 | Smith | 8000.00 | 80 |
| 177 | Livingston | 8400.00 | 80 |
| 176 | Taylor | 8600.00 | 80 |
| 175 | Hutton | 8800.00 | 80 |
| 152 | Hall | 9000.00 | 80 |
| 158 | McEwen | 9000.00 | 80 |
| 163 | Greene | 9500.00 | 80 |
| 157 | Sully | 9500.00 | 80 |
| 151 | Bernstein | 9500.00 | 80 |
| 170 | Fox | 9600.00 | 80 |
| 156 | King | 10000.00 | 80 |
| 169 | Bloom | 10000.00 | 80 |
| 150 | Tucker | 10000.00 | 80 |
| 162 | Vishney | 10500.00 | 80 |
| 149 | Zlotkey | 10500.00 | 80 |
| 148 | Cambrault | 11000.00 | 80 |
+-------------+------------+----------+---------------+
因此对应第 pageNo 页的话, 条数为 pageSize, 就是
SELECT employee_id, last_name, salary, department_id
FROM employees
ORDER BY department_id DESC, salary ASC
LIMIT (pageNo-1)*pageSize, pageSize;
声明顺序
SELECT xxx
FROM xxx
WHERE xxx
ORDER BY xxx
LIMIT xxx, xxx;
LIMIT 偏移量, 条目数;
2.3 拓展
MySQL 8.0 新特性
SELECT employee_id, last_name, salary, department_id
FROM employees
LIMIT 10 OFFSET 2;
LIMIT pageSize OFFSET 偏移量
查询员工表中工资最高的员工信息
SELECT employee_id, last_name, salary, department_id
FROM employees
ORDER BY salary DESC
-- LIMIT 1;
-- LIMIT 0,1;
-- 新特性
LIMIT 1 OFFSET 0;
-- 以上三个等价.
+-------------+-----------+----------+---------------+
| employee_id | last_name | salary | department_id |
+-------------+-----------+----------+---------------+
| 100 | King | 24000.00 | 90 |
+-------------+-----------+----------+---------------+
LIMIT 可以使用在 MySQL, PGSQL, MariaDB, SQLite 等数据库中使用, 表示分页, 不能在 SQL Server 和 Oracle 中使用.
2.4 练习
-- 1. 查询员工的姓名和部门号和年薪, 按年薪降序, 按姓名升序显示
SELECT last_name, department_id, salary*12 as annual_salary
FROM employees
ORDER BY annual_salary DESC, last_name ASC;
+-------------+---------------+---------------+
| last_name | department_id | annual_salary |
+-------------+---------------+---------------+
| King | 90 | 288000.00 |
| De Haan | 90 | 204000.00 |
| Kochhar | 90 | 204000.00 |
-- 2. 选择公司不在 8000 到 17000 的员工的姓名和工资, 按工资降序, 显示第21到40位置的数据
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20, 20;
+-----------+---------+
| last_name | salary |
+-----------+---------+
| Ernst | 6000.00 |
| Fay | 6000.00 |
| Mourgos | 5800.00 |
| Austin | 4800.00 |
| Pataballa | 4800.00 |
| Whalen | 4400.00 |
| Lorentz | 4200.00 |
| Sarchand | 4200.00 |
| Bull | 4100.00 |
| Bell | 4000.00 |
| Everett | 3900.00 |
| Chung | 3800.00 |
| Dilly | 3600.00 |
| Ladwig | 3600.00 |
| Rajs | 3500.00 |
| Dellinger | 3400.00 |
| Bissot | 3300.00 |
| Mallin | 3300.00 |
| Stiles | 3200.00 |
| Nayer | 3200.00 |
+-----------+---------+
-- 3. 查询邮箱中包含 e 的员工信息, 并先按照邮箱的字节数降序, 再按照部门号升序.
SELECT employee_id, last_name, email,department_id
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id ASC;
+-------------+------------+----------+---------------+
| employee_id | last_name | email | department_id |
+-------------+------------+----------+---------------+
| 201 | Hartstein | MHARTSTE | 20 |
| 114 | Raphaely | DRAPHEAL | 30 |
| 119 | Colmenares | KCOLMENA | 30 |
| 186 | Dellinger | JDELLING | 50 |
| 191 | Perkins | RPERKINS | 50 |
6. 多表查询
学习中遇到的几个表
DESC employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int | YES | MUL | NULL | |
| department_id | int | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
DESC departments;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| department_id | int | NO | PRI | 0 | |
| department_name | varchar(30) | NO | | NULL | |
| manager_id | int | YES | MUL | NULL | |
| location_id | int | YES | MUL | NULL | |
+-----------------+-------------+------+-----+---------+-------+
DESC locations;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| location_id | int | NO | PRI | 0 | |
| street_address | varchar(40) | YES | | NULL | |
| postal_code | varchar(12) | YES | | NULL | |
| city | varchar(30) | NO | | NULL | |
| state_province | varchar(25) | YES | | NULL | |
| country_id | char(2) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
1. 一个案例引发的多表查询
查询员工名为 Abel 的员工在哪个城市
SELECT *
FROM employees
WHERE last_name = 'Abel';
+-------------+------------+-----------+-------+--------------------+------------+--------+----------+----------------+------------+---------------+
| employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id |
+-------------+------------+-----------+-------+--------------------+------------+--------+----------+----------------+------------+---------------+
| 174 | Ellen | Abel | EABEL | 011.44.1644.429267 | 1996-05-11 | SA_REP | 11000.00 | 0.30 | 149 | 80 |
+-------------+------------+-----------+-------+--------------------+------------+--------+----------+----------------+------------+---------------+
知道了部门 id
SELECT *
FROM departments
WHERE department_id = 80;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 80 | Sales | 145 | 2500 |
+---------------+-----------------+------------+-------------+
知道了 location_id
SELECT *
FROM locations
WHERE location_id = 2500;
+-------------+------------------------------------------+-------------+--------+----------------+------------+
| location_id | street_address | postal_code | city | state_province | country_id |
+-------------+------------------------------------------+-------------+--------+----------------+------------+
| 2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB | Oxford | Oxford | UK |
+-------------+------------------------------------------+-------------+--------+----------------+------------+
因此就知道了这个人在这个城市工作.
这里需要我们查询三次才能得到结果, 这里就是多表查询的一个案例.
笛卡尔积
SELECT employee_id
FROM employees, departments;
-- 这里就是笛卡尔积, 也就是两个表的乘积
-- 这里是 107 * 27 = 2889 条记录
-- 这里是 107 是 employees 表的记录数, 27 是 departments 表的记录数
-- employees 表里的每条记录都和 departments 表里的每条记录进行了匹配, 这样是没有意义的
笛卡尔积又叫交叉连接 CROSS JOIN, 这里出现这种问题是因为没有给定多表连接的条件
-- 加上连接条件
SELECT employee_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
-- 这样就是正常的连接了
这样就剩下 106 条记录, 可能是因为有department_id 为NULL的
SELECT employee_id, last_name, department_id
FROM employees
WHERE isnull(department_id);
+-------------+-----------+---------------+
| employee_id | last_name | department_id |
+-------------+-----------+---------------+
| 178 | Grant | NULL |
+-------------+-----------+---------------+
果然如此
问题分析
SELECT employee_id, department_name, employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
-- 这样会报错
-- ERROR 1052 (23000): Column 'department_id' in field list is ambiguous
-- 因为两个表都有 department_id 字段, 所以会报错
-- 指定表名就好了
建议多表查询时, 每个字段前都指明其所在的表名, 这样可以避免字段名冲突
SELECT employees.employee_id, departments.department_name, employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
会发现问题, SQL 有些内容太长了, 因此可以给表起别名
SELECT e.employee_id, e.last_name, e.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;
设置了别名就都得使用别名, 不然会报错.
并上 locations 表
SELECT e.employee_id, e.last_name, e.department_id, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;
如果有 n 个表实现多表查询, 则至少有 n-1 个连接条件
2. 多表查询分类讲解
2.1 分类1: 等值连接 vs 非等值连接
这是针对连接条件来说的
等值连接
之前的例子是等值连接
非等值连接
select * from job_grades;
+-------------+------------+-------------+
| grade_level | lowest_sal | highest_sal |
+-------------+------------+-------------+
| A | 1000 | 2999 |
| B | 3000 | 5999 |
| C | 6000 | 9999 |
| D | 10000 | 14999 |
| E | 15000 | 24999 |
| F | 25000 | 40000 |
+-------------+------------+-------------+
这种就是非等值连接的例子
查询员工的等级
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
LIMIT 0,10;
+-----------+----------+-------------+
| last_name | salary | grade_level |
+-----------+----------+-------------+
| King | 24000.00 | E |
| Kochhar | 17000.00 | E |
| De Haan | 17000.00 | E |
| Hunold | 9000.00 | C |
| Ernst | 6000.00 | C |
| Austin | 4800.00 | B |
| Pataballa | 4800.00 | B |
| Lorentz | 4200.00 | B |
| Greenberg | 12000.00 | D |
| Faviet | 9000.00 | C |
+-----------+----------+-------------+
2.2 分类2: 自连接 vs 非自连接
这是根据这个表是不是自己和自己连接分类的
-- 自连接
-- 查询员工的经理信息
SELECT e1.last_name "员工", e1.employee_id, e2.last_name "经理", e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id
LIMIT 0,10;
+-----------+-------------+-----------+-------------+
| 员工 | employee_id | 经理 | employee_id |
+-----------+-------------+-----------+-------------+
| Kochhar | 101 | King | 100 |
| De Haan | 102 | King | 100 |
| Hunold | 103 | De Haan | 102 |
| Ernst | 104 | Hunold | 103 |
| Austin | 105 | Hunold | 103 |
| Pataballa | 106 | Hunold | 103 |
| Lorentz | 107 | Hunold | 103 |
| Greenberg | 108 | Kochhar | 101 |
| Faviet | 109 | Greenberg | 108 |
| Chen | 110 | Greenberg | 108 |
+-----------+-------------+-----------+-------------+
也可以搞多份, 比如三级经理
SELECT e1.last_name "员工", e1.employee_id, e2.last_name "经理", e2.employee_id, e3.last_name "总经理", e3.employee_id
FROM employees e1, employees e2, employees e3
WHERE e1.manager_id = e2.employee_id AND e2.manager_id = e3.employee_id
LIMIT 0,10;
+-----------+-------------+-----------+-------------+---------+-------------+
| 员工 | employee_id | 经理 | employee_id | 总经理 | employee_id |
+-----------+-------------+-----------+-------------+---------+-------------+
| Hunold | 103 | De Haan | 102 | King | 100 |
| Ernst | 104 | Hunold | 103 | De Haan | 102 |
| Austin | 105 | Hunold | 103 | De Haan | 102 |
| Pataballa | 106 | Hunold | 103 | De Haan | 102 |
| Lorentz | 107 | Hunold | 103 | De Haan | 102 |
| Greenberg | 108 | Kochhar | 101 | King | 100 |
| Faviet | 109 | Greenberg | 108 | Kochhar | 101 |
| Chen | 110 | Greenberg | 108 | Kochhar | 101 |
| Sciarra | 111 | Greenberg | 108 | Kochhar | 101 |
| Urman | 112 | Greenberg | 108 | Kochhar | 101 |
+-----------+-------------+-----------+-------------+---------+-------------+
当然这里没处理没有三级经理的情况
-- 非自连接
不同的表之间的连接就是非自连接
2.3 分类3: 内连接 vs 外连接
内连接
上面的都是内连接
左表和右表中满足连接条件的查出来了, 不满足的就没有查出来
因此上面有些员工没有经理的就没有查出来
外连接
合并具有同一列的两个以上的表的行, 结果集中除了包含一个表与另一个表匹配的行之外, 还查询到了左表 或 右表中不匹配的行.
外连接:
- 左外连接
- 右外连接
- 全(满)外连接
在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行, 这些种连接称为左外连接.
右外连接类似.
练习: 查询所有的员工信息和部门信息, 如果员工没有部门, 也要显示出来
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
-- +-------------+---------------+------------------+
-- | last_name | department_id | department_name |
-- +-------------+---------------+------------------+
-- | King | 90 | Executive |
-- | Kochhar | 90 | Executive |
-- | De Haan | 90 | Executive |
返回了 107 条, 把没有部门的员工也查出来了
SQL92 语法实现外连接
左外连接: 使用 +
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
-- 但是 MySQL 中不支持 SQL92 的语法.
-- 但是 Oracle 中支持 SQL92 的语法
右外连接就:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
-- SQL 99语法中使用JOIN ... ON 的方式实现多表的查询.
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
SELECT e1.last_name, e1.department_id, e1.manager_id, e2.last_name, e2.employee_id
FROM employees e1 LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;
-- +-------------+---------------+------------+-----------+-------------+
-- | last_name | department_id | manager_id | last_name | employee_id |
-- +-------------+---------------+------------+-----------+-------------+
-- | King | 90 | NULL | NULL | NULL |
-- | Kochhar | 90 | 100 | King | 100 |
-- | De Haan | 90 | 100 | King | 100 |
返回 107 条
-- SQL 99 语法实现内连接
SELECT e.last_name, e.department_id, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
返回 106 条
-- Left OUTER JOIN 和 LEFT JOIN 是等价的
-- 右外连接 RIGHT JOIN
满外连接
-- MySQL 不支持 FULL JOIN
SELECT e.last_name, e.department_id, d.department_name
FROM employees e FULL JOIN departments d
ON e.department_id = d.department_id;
MySQL中要:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION
SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;
3. SQL99语法实现多表查询
4. UNION的使用
合并查询结果, 利用 UNION 关键字, 可以给出多条 SELECT 语句, 并根据他们的结果组成单个结果集. 合并时, 两个表对应的列数和数据类型必须相同, 并且相互对应. 各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字分割.
- UNION: 去重
- UNION ALL: 不去重
UNION ALL 语句所需要的资源比 UNION 语句少, 如果明确知道合并数据后的结果数据不存在重复的行, 可以使用 UNION ALL 语句. 尽量使用 UNION ALL 语句, 以提高查询效率.
5. 7种SQL JOINS的使用
1. A ∩ B: 内连接
SELECT e.last_name, e.department_id, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
或者
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
2. 左外连接
SELECT employee_id, last_name, department_id
FROM employees LEFT JOIN departments
ON employees.department_id = departments.department_id;
3. 右外连接
SELECT employee_id, last_name, department_id
FROM employees RIGHT JOIN departments
ON employees.department_id = departments.department_id;
4. A - B = A - (A ∩ B): 左外连接, 右表为空
SELECT employee_id, last_name, departments.department_id
FROM employees LEFT JOIN departments
ON employees.department_id = departments.department_id
WHERE departments.department_id IS NULL;
5. B - A = B - (A ∩ B): 右外连接, 左表为空
SELECT employee_id, last_name, departments.department_id
FROM employees RIGHT JOIN departments
ON employees.department_id = departments.department_id
WHERE employees.department_id IS NULL;
6. A ∪ B: 全外连接
SELECT employee_id, last_name, departments.department_id
FROM employees LEFT JOIN departments
ON employees.department_id = departments.department_id
UNION
SELECT employee_id, last_name, departments.department_id
FROM employees RIGHT JOIN departments
ON employees.department_id = departments.department_id;
也可以变成 左外连接 ∪ B - A = B - (A ∩ B)
SELECT employee_id, last_name, departments.department_id
FROM employees LEFT JOIN departments
ON employees.department_id = departments.department_id
UNION
SELECT employee_id, last_name, departments.department_id
FROM employees RIGHT JOIN departments
ON employees.department_id = departments.department_id
WHERE employees.department_id IS NULL;
7. A - B ∪ B - A: 全外连接 - 内连接
其实是 A - B = (A - (A ∩ B)) ∪ (B - A) = B - (A ∩ B)
SELECT employee_id, last_name, departments.department_id
FROM employees LEFT JOIN departments
ON employees.department_id = departments.department_id
WHERE departments.department_id IS NULL
UNION
SELECT employee_id, last_name, departments.department_id
FROM employees RIGHT JOIN departments
ON employees.department_id = departments.department_id
WHERE employees.department_id IS NULL;
6. SQL99语法新特性
6.1 Natural Join
NATURAL JOIN 会帮你自动查询两张连接表中所有相同的字段, 然后进行等值连接.
SELECT employee_id, last_name, salary, department_name
FROM employees NATURAL JOIN departments;
6.2 Using Join
-- 原本是
SELECT employee_id, last_name, salary, department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
-- 使用 USING
SELECT employee_id, last_name, salary, department_name
FROM employees JOIN departments
USING (department_id);
-- 括号里填写同名的字段名
7. 多表查询练习
1. 显示所有员工的姓名, 部门号和部门名称
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
-- 107 rows
2. 查询90号部门的员工的job_id 和90号部门的location_id
SELECT e.job_id, d.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 90;
-- 3 rows
3. 选择所有有奖金的员工的 last_name, department_name, location_id, city
这里是所有, 所以两个都要是 LEFT JOIN
SELECT e.last_name, d.department_name, l.location_id, l.city
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL OR e.commission_pct > 0;
-- 35 rows
4. 选择 city 在 Toronto工作的员工的 last_name, job_id, department_id, department_name
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE l.city = 'Toronto';
-- 2 rows
5. 查询员工所在的部门名称，部门地址，姓名，工作，工资，其中员工所在的部门名称为 'Executive'
SELECT e.last_name, e.job_id, e.salary, d.department_name, l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_name = 'Executive';
-- 3 rows
6. 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果以以下的格式
employees EMP# manager Mgr#
xxx xx xxx xx
SELECT e.last_name "employees", e.employee_id "EMP#", m.last_name "manager", m.employee_id "Mgr#"
FROM employees e LEFT JOIN employees m
ON e.manager_id = m.employee_id;
+-------------+------+-----------+------+
| employees | EMP# | manager | Mgr# |
+-------------+------+-----------+------+
| King | 100 | NULL | NULL |
| Kochhar | 101 | King | 100 |
| De Haan | 102 | King | 100 |
-- 107 rows
7. 查询哪些部门没有员工
SELECT d.department_id, d.department_name
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.last_name IS NULL;
-- 16 rows
8. 查询哪个城市没有部门
SELECT l.city, d.department_id
FROM locations l LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE d.department_id IS NULL;
-- 16 rows
9. 查询部门名为 'Sales' 或 'IT' 的员工信息
SELECT e.last_name, e.department_id, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name IN ('Sales', 'IT');
-- 39 rows
7. 单行函数
1. 函数的理解
1.1 什么是函数
1.2 不同DBMS函数的差异
不同管理系统的里面差别特别大
1.3 MySQL的内置函数及分类
从实现相关的角度分类
- 数值函数
- 字符串函数
- 日期和时间函数
- 流程控制函数
- 加密与解密函数
- 获取MySQL信息函数
- 聚合函数等
又根据操作数据的行数可以分为
- 单行函数
- 聚合函数
单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
2. 数值函数
2.1 基本函数
- ABS(x): 返回 x 的绝对值
- SIGN(x): 返回 x 的符号, 正数返回 1, 负数返回 -1, 0 返回 0
- PI(): 返回圆周率的值
- CEIL(x), CEILING(x): 返回大于或等于 x 的最小整数
- FLOOR(x): 返回小于或等于 x 的最大整数
- LEAST(x, y, …): 返回列表中的最小值
- GREATEST(x, y, …): 返回列表中最大值
- MOD(x, y): 返回 x 除以 y 的余数
- RAND(): 返回 0 到 1 之间的随机数
- RAND(x): 返回 x 为种子的随机数
- ROUND(x): 四舍五入
- ROUND(x, y): 四舍五入到小数点后 y 位
- TRUNCATE(x, y): 返回 x 截断到小数点后 y 位
- SQRT(x): 返回 x 的平方根
SELECT ROUND(123.456, 0), ROUND(123.456, 1), ROUND(123.456, 2), ROUND(123.456, -1);
+-------------------+-------------------+-------------------+--------------------+
| ROUND(123.456, 0) | ROUND(123.456, 1) | ROUND(123.456, 2) | ROUND(123.456, -1) |
+-------------------+-------------------+-------------------+--------------------+
| 123 | 123.5 | 123.46 | 120 |
+-------------------+-------------------+-------------------+--------------------+
2.2 角度与弧度互换函数
- RADIANS(x): 将角度 x 转换为弧度
- DEGREES(x): 将弧度 x 转换为角度
SELECT RADIANS(180), DEGREES(3.1415926);
+-------------------+--------------------+
| RADIANS(180) | DEGREES(3.1415926) |
+-------------------+--------------------+
| 3.141592653589793 | 179.99999692953102 |
+-------------------+--------------------+
2.3 三角函数
这里的 x 都是弧度值, 不是角度值
- SIN(x): 返回 x 的正弦
- ASIN(x): 返回 x 的反正弦, 如果x不在-1到1之间, 返回NULL
- COS(x): 返回 x 的余弦
- ACOS(x): 返回 x 的反余弦, 如果x不在-1到1之间, 返回NULL
- TAN(x): 返回 x 的正切
- ATAN(x): 返回 x 的反正切
- ATAN2(y, x): 返回 y/x 的反正切, 返回值在 -PI 到 PI 之间
- COT(x): 返回 x 的余切
2.4 指数与对数
- POW(x, y), POWER(x,y): 返回 x 的 y 次幂
- EXP(x): 返回 e 的 x 次幂
- LN(x), LOG(x): 返回 x 的自然对数, 当 x 为负数或 0 时, 返回 NULL
- LOG10(x): 返回 x 的以 10 为底的对数, 当 x 为负数或 0 时, 返回 NULL
- LOG2(x): 返回 x 的以 2 为底的对数, 当 x 为负数或 0 时, 返回 NULL
2.5 进制间的转换
- BIN(x): 返回 x 的二进制值
- HEX(x): 返回 x 的十六进制值
- OCT(x): 返回 x 的八进制值
- CONV(x, a, b): 返回 x 的 a 进制值转换为 b 进制值
3. 字符串函数
- ASCII(S): 返回 S 的第一个字符的 ASCII 值
- CHAR_LENGTH(S), LENGTH(S): 返回 S 的字符数
- LENGTHB(S): 返回 S 的字节数, 与字符集相关
- CONCAT(S1, S2, …): 返回连接 S1, S2, … 的结果
- CONCAT_WS(separator, S1, S2, …): 返回连接 S1, S2, … 的结果, 并用 separator 分隔开
- INSERT(str, start, length, new_str): 返回 str 的子串, 从 start 开始, 长度为 length, 替换为 new_str
- REPLACE(str, from_str, to_str): 返回 str 的子串, 将 from_str 替换为 to_str
- UPPER(S) 或 UCASE(S): 返回 S 的大写形式
- LOWER(S) 或 LCASE(S): 返回 S 的小写形式
- LEFT(S, N): 返回 S 的前 N 个字符
- RIGHT(S, N): 返回 S 的后 N 个字符
- LPAD(S, N, pad_str): 返回 S 的左填充 pad_str 字符, 直到长度为 N
- RPAD(S, N, pad_str): 返回 S 的右填充 pad_str 字符, 直到长度为 N
- LTRIM(S): 返回去掉 S 左边空格的结果
- RTRIM(S): 返回去掉 S 右边空格的结果
- TRIM(S): 返回去掉 S 两边空格的结果
- TRIM(s1 FROM s2): 返回去掉 s2 两边 s1 的结果
- TRIM(LEADING s1 FROM s2): 返回去掉 s2 左边 s1 的结果
- TRIM(TRAILING s1 FROM s2): 返回去掉 s2 右边 s1 的结果
- REPEAT(S, N): 返回 S 重复 N 次的结果
- SPACE(N): 返回 N 个空格
- STRCMP(S1, S2): 返回 S1 和 S2 的比较结果, 相等返回 0, S1 大于 S2 返回 1, S1 小于 S2 返回 -1
- SUBSTR(s, start, length), SUBSTRING(s, start, length), MID(s, start, length): 返回 s 的子串, 从 start 开始, 长度为 length
- LOCATE(s1, s2), POSITION(s1 IN s2), INSTR(s1, s2): 返回 s1 在 s2 中的位置, 从 1 开始
- ELT(N, S1, S2, …): 返回 S1, S2, … 中的第 N 个字符串
- FIELD(S, S1, S2, …): 返回 S 在 S1, S2, … 中的位置, 从 1 开始
- FIND_IN_SET(S, S1): 返回 S 在 S1 中的位置, 从 1 开始
- REVERSE(S): 返回 S 的反转结果
- NULLIF(S1, S2): 如果 S1 = S2, 返回 NULL, 否则返回 S1
-- 字符数
SELECT CHAR_LENGTH("hello"), CHAR_LENGTH("你好");
+----------------------+---------------------+
| CHAR_LENGTH("hello") | CHAR_LENGTH("你好") |
+----------------------+---------------------+
| 5 | 2 |
+----------------------+---------------------+
-- 字节数
SELECT LENGTH("hello"), LENGTH("你好");
+-----------------+----------------+
| LENGTH("hello") | LENGTH("你好") |
+-----------------+----------------+
| 5 | 4 |
+-----------------+----------------+
-- CONCAT_WS
SELECT CONCAT_WS(',', 'a', 'b', 'c');
+-------------------------------+
| CONCAT_WS(',', 'a', 'b', 'c') |
+-------------------------------+
| a,b,c |
+-------------------------------+
-- INSERT
SELECT INSERT('hello world', 7, 4, 'MySQL');
+--------------------------------------+
| INSERT('hello world', 7, 4, 'MySQL') |
+--------------------------------------+
| hello MySQLd |
+--------------------------------------+
SELECT INSERT('hello world', 7, 5, 'MySQL');
+--------------------------------------+
| INSERT('hello world', 7, 5, 'MySQL') |
+--------------------------------------+
| hello MySQL |
+--------------------------------------+
SELECT INSERT('hello world', 8, 7, 'MySQL');
+--------------------------------------+
| INSERT('hello world', 8, 7, 'MySQL') |
+--------------------------------------+
| hello wMySQL |
+--------------------------------------+
-- 看来是超出就超出了, 他不管你
SELECT INSERT('hello world', 1, 3, 'MySQL');
+--------------------------------------+
| INSERT('hello world', 1, 3, 'MySQL') |
+--------------------------------------+
| MySQLlo world |
+--------------------------------------+
-- MySQL 里字符串的索引是从 1 开始的
-- LPAD
SELECT LPAD('hello', 10, 'ax');
+-------------------------+
| LPAD('hello', 10, 'ax') |
+-------------------------+
| axaxahello |
+-------------------------+
-- 看起来是一直重复填充字符串，然后取前 10-char_length(S) 个字符
SELECT LOWER('HELLO你好'), UPPER('hello你好');
+--------------------+--------------------+
| LOWER('HELLO你好') | UPPER('hello你好') |
+--------------------+--------------------+
| hello你好 | HELLO你好 |
+--------------------+--------------------+
TRIM(s1 FROM s2)
SELECT TRIM('a' FROM 'aabbaccaa');
+----------------------------+
| TRIM('a' FROM 'aabbaccaa') |
+----------------------------+
| bbacc |
+----------------------------+
就是去除首尾的 a
SELECT TRIM('ab' FROM 'ababbaccaab');
+-------------------------------+
| TRIM('ab' FROM 'ababbaccaab') |
+-------------------------------+
| bacca |
+-------------------------------+
去除首尾的 ab
TRIM(LEADING s1 FROM s2): 返回去掉 s2 左边 s1 的结果
SELECT TRIM(LEADING 'a' FROM 'aabbaccaa');
+------------------------------------+
| TRIM(LEADING 'a' FROM 'aabbaccaa') |
+------------------------------------+
| bbaccaa |
+------------------------------------+
就是类似于LTRIM的功能
TRIM(TRAILING s1 FROM s2): 返回去掉 s2 右边 s1 的结果
SELECT TRIM(TRAILING 'a' FROM 'aabbaccaa');
+-------------------------------------+
| TRIM(TRAILING 'a' FROM 'aabbaccaa') |
+-------------------------------------+
| aabbacc |
+-------------------------------------+
就是类似于RTRIM的功能
FIND_IN_SET(S, S1): 返回 S 在 S1 中的位置, 从 1 开始
SELECT FIND_IN_SET('b', 'a,b,c,d');
+-----------------------------+
| FIND_IN_SET('b', 'a,b,c,d') |
+-----------------------------+
| 2 |
+-----------------------------+
感觉就是分割后用了 FIELD 函数
MySQL 中字符串的索引是从 1 开始的!
4. 日期和时间函数
4.1 获取日期、时间
- CURDATE(), CURRENT_DATE(): 返回当前日期, 只包含年月日
- CURTIME(), CURRENT_TIME(): 返回当前时间, 只包含时分秒
- NOW(), SYSDATE(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP(): 返回当前日期和时间
- UTC_DATE(): 返回当前日期, 以 UTC 为准
- UTC_TIME(): 返回当前时间, 以 UTC 为准
- UNIX_TIMESTAMP(): 返回当前日期和时间的时间戳
SELECT CURDATE(), CURRENT_DATE(), CURTIME(), CURRENT_TIME(), NOW(), SYSDATE(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP(), UTC_DATE(), UTC_TIME(), UNIX_TIMESTAMP();
+------------+----------------+-----------+----------------+---------------------+---------------------+---------------------+---------------------+---------------------+------------+------------+------------------+
| CURDATE() | CURRENT_DATE() | CURTIME() | CURRENT_TIME() | NOW() | SYSDATE() | CURRENT_TIMESTAMP() | LOCALTIME() | LOCALTIMESTAMP() | UTC_DATE() | UTC_TIME() | UNIX_TIMESTAMP() |
+------------+----------------+-----------+----------------+---------------------+---------------------+---------------------+---------------------+---------------------+------------+------------+------------------+
| 2024-03-13 | 2024-03-13 | 10:57:47 | 10:57:47 | 2024-03-13 10:57:47 | 2024-03-13 10:57:47 | 2024-03-13 10:57:47 | 2024-03-13 10:57:47 | 2024-03-13 10:57:47 | 2024-03-13 | 02:57:47 | 1710298667 |
+------------+----------------+-----------+----------------+---------------------+---------------------+---------------------+---------------------+---------------------+------------+------------+------------------+
4.2 日期与时间戳的转换
- UNIX_TIMESTAMP(): 以 UNIX 时间戳的形式返回当前时间
- UNIX_TIMESTAMP(date): 以 UNIX 时间戳的形式返回 date 的时间
- FROM_UNIXTIME(unix_timestamp): 返回 UNIX 时间戳 unix_timestamp 的日期和时间
SELECT from_unixtime(1710298667);
mysql> SELECT from_unixtime(1710298667);
+---------------------------+
| from_unixtime(1710298667) |
+---------------------------+
| 2024-03-13 10:57:47 |
+---------------------------+
4.3 获取月份、星期、星期数、天数等函数
- YEAR(date): 返回 date 的年份
- MONTH(date): 返回 date 的月份
- DAY(date): 返回 date 的天
- HOUR(time): 返回 time 的小时
- MINUTE(time): 返回 time 的分钟
- SECOND(time): 返回 time 的秒
- MONTHNAME(date): 返回 date 的月份名, 英文
- DAYNAME(date): 返回 date 的星期名, 英文
- WEEKDAY(date): 返回 date 是周几, 0 表示星期一, 6 表示星期日
- QUARTER(date): 返回 date 的季度, 范围是 1-4
- WEEK(data): 返回是 data 的年份中的第几周
- WEEKOFYEAR(date): 返回是 data 的年份中的第几周
- DAYOFYEAR(date): 返回 date 是 data 的年份中的第几天
- DAYOFMONTH(date): 返回 date 是 data 的月份中的第几天
- DAYOFWEEK(date): 返回 date 是 data 的星期中的第几天, 周一是1
4.4 日期的操作函数
- EXTRACT(unit FROM date): 返回 date 的 unit 部分, unit 可以是 YEAR, MONTH, DAY, HOUR, MINUTE, SECONDs
4.5 时间和秒钟转换的函数
- TIME_TO_SEC(time): 返回 time 的秒数
- SEC_TO_TIME(seconds): 返回 seconds 秒数的时间
SELECT TIME_TO_SEC(CURTIME()), SEC_TO_TIME(TIME_TO_SEC(CURTIME()));
+------------------------+-------------------------------------+
| TIME_TO_SEC(CURTIME()) | SEC_TO_TIME(TIME_TO_SEC(CURTIME())) |
+------------------------+-------------------------------------+
| 48507 | 13:28:27 |
+------------------------+-------------------------------------+
就是今天的时间转换成秒数, 然后再转换回来
4.6 计算日期和时间的函数
-
DATE_ADD(date, INTERVAL expr type): 返回 date 加上 expr 的结果
-
DATE_SUB(date, INTERVAL expr type): 返回 date 减去 expr 的结果
-
ADDTIME(time1, time2): 返回 time1 加上 time2 的结果
-
SUBTIME(time1, time2): 返回 time1 减去 time2 的结果
-
DATEDIFF(date1, date2): 返回 date1 和 date2 之间的天数
-
TIMEDIFF(time1, time2): 返回 time1 和 time2 之间的时间差
-
FROM_DAYS(N): 返回 0 年 1 月 1 日 N 天之后的日期
-
TO_DAYS(date): 返回 date 距离 0 年 1 月 1 日的天数
-
LAST_DAY(date): 返回 date 所在月的最后一天
-
MAKEDATE(year, dayofyear): 返回 year 年的第 dayofyear 天
-
MAKETIME(hour, minute, second): 返回 hour 小时 minute 分 second 秒的时间
-
PERIOD_ADD(P, N): 返回 P 加上 N 个月的结果
4.7 日期的格式化与解析
- DATE_FORMAT(date, format): 返回 date 的格式化结果
- TIME_FORMAT(time, format): 返回 time 的格式化结果
- GET_FORMAT([DATE|TIME], [EUR|USA|JIS|ISO|INTERNAL|DB]): 返回日期或时间的格式, 返回当地的日期或时间格式
- STR_TO_DATE(str, format): 返回 str 根据 format 格式化的日期
5. 流程控制函数
- IF(value, value1,value2): 如果 value 为真, 返回 value1, 否则返回 value2
- IFNULL(value1, value2): 如果 value1 不为 NULL, 返回 value1, 否则返回 value2
- CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result END: 返回满足条件的结果
- CASE expr WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE result END: 返回满足条件的结果
SELECT IF(1 > 2, 'yes', 'no'), IFNULL(NULL, 'yes'), IFNULL('no', 'yes');
+------------------------+---------------------+---------------------+
| IF(1 > 2, 'yes', 'no') | IFNULL(NULL, 'yes') | IFNULL('no', 'yes') |
+------------------------+---------------------+---------------------+
| no | yes | no |
+------------------------+---------------------+---------------------+
SELECT last_name, salary, IF(salary >=6000, "高", "低") "工资等级"
FROM employees;
+-------------+----------+----------+
| last_name | salary | 工资等级 |
+-------------+----------+----------+
| King | 24000.00 | 高 |
| Kochhar | 17000.00 | 高 |
| De Haan | 17000.00 | 高 |
SELECT last_name, commission_pct, IFNULL(commission_pct, 0) "奖金"
FROM employees;
+-------------+----------------+------+
| last_name | commission_pct | 奖金 |
+-------------+----------------+------+
| King | NULL | 0.00 |
| Kochhar | NULL | 0.00 |
IFNULL(value1, value2) 可以看成是 IF(value1 IS NULL, value2, value1) 的这种情况
CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
SELECT last_name, salary, CASE WHEN salary >= 15000 THEN "大于15000"
WHEN salary >= 10000 THEN "大于10000"
ELSE "小于10000" END "工资等级"
FROM employees;
+-------------+----------+-----------+
| last_name | salary | 工资等级 |
+-------------+----------+-----------+
| King | 24000.00 | 大于15000 |
| Kochhar | 17000.00 | 大于15000 |
| De Haan | 17000.00 | 大于15000 |
类似于 if else if else 的情况, 也可以没有最后的else
SELECT last_name, salary, CASE salary WHEN 24000 THEN "最高"
WHEN 17000 THEN "次高"
END "工资等级"
FROM employees;
+-------------+----------+----------+
| last_name | salary | 工资等级 |
+-------------+----------+----------+
| King | 24000.00 | 最高 |
| Kochhar | 17000.00 | 次高 |
| De Haan | 17000.00 | 次高 |
| Hunold | 9000.00 | NULL |
CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
查询部门号为 10, 20, 30 的员工信息
若部门号为 10, 则打印其工资的 1.1 倍
若部门号为 20, 则打印其工资的 1.2 倍
若部门号为 30, 则打印其工资的 1.3 倍
其他部门打印其工资的 1.5 倍
SELECT employee_id, last_name, department_id, salary,
CASE department_id WHEN 10 THEN salary * 1.1
WHEN 20 THEN salary * 1.2
WHEN 30 THEN salary * 1.3
ELSE salary * 1.5 END "工资"
FROM employees;
+-------------+-------------+---------------+----------+----------+
| employee_id | last_name | department_id | salary | 工资 |
+-------------+-------------+---------------+----------+----------+
| 100 | King | 90 | 24000.00 | 36000.00 |
| 101 | Kochhar | 90 | 17000.00 | 25500.00 |
| 102 | De Haan | 90 | 17000.00 | 25500.00 |
也可以没有 ELSE 的情况
6. 加密与解密函数
- PASSWORD(str): 返回 str 的加密结果, 41 位长度, 不可逆
- MD5(str): 返回 str 的 MD5 加密结果, 若输入为 NULL, 返回 NULL
- SHA(str): 返回 str 的 SHA 加密结果, 若输入为 NULL, 返回 NULL, 比 MD5 安全
- ENCODE(value, password_seed): 返回 value 的加密结果, 使用 password_seed 作为种子
- DECODE(value, password_seed): 返回 value 的解密结果, 使用 password_seed 作为种子
# 8.0 之后就不让用 PASSWORD 了
# 8.0 也不支持 ENCODE 和 DECODE 了
SELECT PASSWORD('aaaa');
SELECT MD5('aaaa'), SHA('aaaa');
+----------------------------------+------------------------------------------+
| MD5('aaaa') | SHA('aaaa') |
+----------------------------------+------------------------------------------+
| 74b87337454200d4d33f80c4663dc5e5 | 70c881d4a26984ddce795f6f71817c9cf4480e79 |
+----------------------------------+------------------------------------------+
7. MySQL 信息函数
- VERSION(): 返回 MySQL 服务器的版本
- CONNECTION_ID(): 返回连接数
- DATABASE(): 返回当前数据库名
- SCHEMA(): 返回当前数据库名
- USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER(): 返回当前用户
- CHARSET(value): 返回 value 的字符集
- COLLATION(value): 返回 value 的比较规则
8. 其他函数
- FORMAT(value, n): 返回对数字 value 进行格式化后的结果数据, n 为小数点后的位数
- CONV(value, from, to): 将 value 的值进行不同进制之间的转换
- INET_ATON(str): 将以点分割的IP地址转化为一个数字
- INET_NTOA(value): 将数字形式的 IP 地址转化为以点分隔的 IP 地址
- BENCHMARK(n, expr): 将表达式重复执行 n 次, 用域测试 MySQL 处理 expr 表达式所耗费的时间
- CONVERT(value USING charset): 返回 value 的字符集为 charset 的结果
9. 课后练习
看过了
8. 聚合函数
1. 聚合函数介绍
多进一出的函数, 下面介绍几个常见的聚合函数
1.1 AVG和SUM 函数
SELECT AVG(salary), SUM(salary)
FROM employees;
+-------------+-------------+
| AVG(salary) | SUM(salary) |
+-------------+-------------+
| 6461.682243 | 691400.00 |
+-------------+-------------+
SELECT AVG(last_name)
FROM employees;
+----------------+
| AVG(last_name) |
+----------------+
| 0 |
+----------------+
这个其实是没有意义的
1.2 MIN和MAX 函数
SELECT MAX(salary), MIN(salary)
FROM employees;
+-------------+-------------+
| MAX(salary) | MIN(salary) |
+-------------+-------------+
| 24000.00 | 2100.00 |
+-------------+-------------+
SELECT MAX(last_name), MIN(last_name)
FROM employees;
+----------------+----------------+
| MAX(last_name) | MIN(last_name) |
+----------------+----------------+
| Zlotkey | Abel |
+----------------+----------------+
根据字母表排序的
1.3 COUNT 函数
作用: 计算指定字段中在查询结果中出现的个数
SELECT COUNT(employee_id)
FROM employees;
+--------------------+
| COUNT(employee_id) |
+--------------------+
| 107 |
+--------------------+
SELECT COUNT(salary)
FROM employees;
+---------------+
| COUNT(salary) |
+---------------+
| 107 |
+---------------+
计算表中有多少条记录
COUNT(*)
COUNT(1)
COUNT(具体字段): 但是这个不一定对
SELECT COUNT(commission_pct)
FROM employees;
+-----------------------+
| COUNT(commission_pct) |
+-----------------------+
| 35 |
+-----------------------+
但是实际上有 107 条
SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
-- 35 rows
因此COUNT(具体字段)就是找的非NULL的值的个数
SELECT AVG(commission_pct), SUM(commission_pct), SUM(commission_pct)/COUNT(commission_pct), SUM(commission_pct)/COUNT(*)
FROM employees;
+---------------------+---------------------+-------------------------------------------+------------------------------+
| AVG(commission_pct) | SUM(commission_pct) | SUM(commission_pct)/COUNT(commission_pct) | SUM(commission_pct)/COUNT(*) |
+---------------------+---------------------+-------------------------------------------+------------------------------+
| 0.222857 | 7.80 | 0.222857 | 0.072897 |
+---------------------+---------------------+-------------------------------------------+------------------------------+
SUM 和 AVG 都是自动跳过了 NULL
AVG = SUM/COUNT
可以这样写, 平均奖金
SELECT AVG(IFNULL(commission_pct, 0))
FROM employees;
+--------------------------------+
| AVG(IFNULL(commission_pct, 0)) |
+--------------------------------+
| 0.072897 |
+--------------------------------+
COUNT(*), COUNT(1), COUNT(具体字段) 哪个效率更高?
- 如果使用的是 MyISAM 存储引擎, 那么 三者 的效率是一样的, 都是O(1), 都是直接返回表的总行数
- 如果使用的是 InnoDB 存储引擎, 那么 COUNT(*) = COUNT(1) > COUNT(具体字段), 因为 COUNT(*) 和 COUNT(1) 是直接返回表的总行数, 而 COUNT(具体字段) 是返回非空值的行数, 需要扫描具体字段的值
2. GROUP BY 子句
需求: 想要求出每个部门的平均工资
-- 整个公司的平均工资
SELECT AVG(salary)
FROM employees;
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
+---------------+--------------+
| department_id | AVG(salary) |
+---------------+--------------+
| NULL | 7000.000000 |
| 10 | 4400.000000 |
| 20 | 9500.000000 |
相当于把员工表分成了很多组, 每一组的平均工资
查询各个 job_id 的平均工资
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC;
+------------+--------------+
| job_id | AVG(salary) |
+------------+--------------+
| AD_PRES | 24000.000000 |
| AD_VP | 17000.000000 |
| MK_MAN | 13000.000000 |
需求: 查询各个部门, 工种的平均工资
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY AVG(salary) DESC;
+---------------+------------+--------------+
| department_id | job_id | AVG(salary) |
+---------------+------------+--------------+
| 90 | AD_PRES | 24000.000000 |
| 90 | AD_VP | 17000.000000 |
| 20 | MK_MAN | 13000.000000 |
-- 20 rows
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY job_id, department_id
ORDER BY AVG(salary) DESC;
-- 20 rows
这两个是一样的, 因为属于同一组的记录都是通过两个元素定位的, 元素的顺序已经和名字绑定了, 因此是一样的
错误代码
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id;
非聚合函数和列名必须声明在 group by 中.
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY job_id;
也是错的
GROUP BY 声明在 SELECT 语句后面, WHERE 语句的后面, ORDER BY 语句的前面.
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
+---------------+--------------+
| department_id | AVG(salary) |
+---------------+--------------+
| NULL | 7000.000000 |
| 10 | 4400.000000 |
| 20 | 9500.000000 |
| 30 | 4150.000000 |
| 40 | 6500.000000 |
| 50 | 3475.555556 |
| 60 | 5760.000000 |
| 70 | 10000.000000 |
| 80 | 8955.882353 |
| 90 | 19333.333333 |
| 100 | 8600.000000 |
| 110 | 10150.000000 |
| NULL | 6461.682243 |
+---------------+--------------+
最后这个是总的平均工资, 即
SELECT AVG(salary)
FROM employees;
+-------------+
| AVG(salary) |
+-------------+
| 6461.682243 |
+-------------+
就是说, 有了 WITH ROLLUP, 就会多出一行, 这一行是总的.
2.1 基本使用
2.2 使用多个列分组
2.3 GROUP BY 中使用 WITH ROLLUP
3. HAVING
3.1 基本使用
查询各个部门中最高工资比 10000 高的部门信息
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
| 20 | 13000.00 |
| 30 | 11000.00 |
| 80 | 14000.00 |
| 90 | 24000.00 |
| 100 | 12000.00 |
| 110 | 12000.00 |
+---------------+-------------+
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
WHERE MAX(salary) > 10000;
用 WHERE 会报错
- 如果用了聚合函数, 就必须用
HAVING, 不能用WHERE - 如果出现了
HAVING, 那么必须放在GROUP BY后面声明. HAVING后面的条件是对分组后的结果进行过滤, 而WHERE后面的条件是对原始数据进行过滤.- 开发中, 使用
HAVING的前提是使用了 GROUP BY
查询部门 id 10,20,30,40 这4个部门中最高工资比10000高的部门信息
SELECT department_id, MAX(salary)
FROM employees
WHERE department_id IN (10, 20, 30, 40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
| 20 | 13000.00 |
| 30 | 11000.00 |
+---------------+-------------+
另一种方式
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
AND department_id IN (10, 20, 30, 40);
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
| 20 | 13000.00 |
| 30 | 11000.00 |
+---------------+-------------+
这里会发现 HAVING 里也能写普通的过滤条件, 推荐使用第一个方法, 因为第一个方法执行效率更高.
- 当过滤条件中有聚合函数时, 此过滤条件必须声明在
HAVING中 - 当过滤条件中没有聚合函数时, 则此过滤条件声明在
WHERE或HAVING中都是可以的, 但是建议声明在 WHERE 里.
3.2 WHERE 和 HAVING 的区别
- 适用范围上来说, HAVING 的使用范围更广, 有聚合函数的时候, 只能使用
HAVING - 如果过滤条件中没有聚合函数: 这种情况下使用
WHERE, 因为WHERE的执行效率比HAVING高.
4. SELECT 的执行过程
4.1 查询的结构
-- sql92 的语法
SELECT ..., ..., ...(假设存在聚合函数)
FROM ..., ..., ...
WHERE ... AND ... (不包含聚合函数的过滤条件 和 多表的连接条件)
GROUP BY ...
HAVING ... (包含聚合函数的过滤条件)
ORDER BY ...
LIMIT ...
-- sql99 的语法
SELECT ..., ..., ...(假设存在聚合函数)
FROM ... (LEFT/RIGHT) JOIN ...
ON ...
WHERE ... (不包含聚合函数的过滤条件)
GROUP BY ...
HAVING ... (包含聚合函数的过滤条件)
ORDER BY ...
LIMIT ...
4.2 SELECT 的执行顺序
第一步:
FROM ... (LEFT/RIGHT) JOIN ...
ON ...
WHERE ... (不包含聚合函数的过滤条件)
GROUP BY ...
HAVING ... (包含聚合函数的过滤条件)
第二步:
SELECT ..., ..., ...(假设存在聚合函数)
第三步:
ORDER BY ...
LIMIT ...
每一步里是按顺序执行的
中间的过程中会存在很多的虚拟表
FROM
-> ON
-> (LEFT/RIGHT) JOIN
-> WHERE
-> GROUP BY
-> HAVING
-> SELECT
-> DISTINCT
-> ORDER BY
-> LIMIT
4.3 SQL 的执行原理
5. 练习
1. WHERE 子句是否可以使用组函数进行过滤?
不可以
2. 查询公司员工工资的最大值, 最小值, 平均值, 总和
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees;
+-------------+-------------+-------------+-------------+
| MAX(salary) | MIN(salary) | AVG(salary) | SUM(salary) |
+-------------+-------------+-------------+-------------+
| 24000.00 | 2100.00 | 6461.682243 | 691400.00 |
+-------------+-------------+-------------+-------------+
3. 查询各 job_id 的员工工资的最大值最小值, 平均值, 总和
SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id;
+------------+-------------+-------------+--------------+-------------+
| job_id | MAX(salary) | MIN(salary) | AVG(salary) | SUM(salary) |
+------------+-------------+-------------+--------------+-------------+
| AC_ACCOUNT | 8300.00 | 8300.00 | 8300.000000 | 8300.00 |
| AC_MGR | 12000.00 | 12000.00 | 12000.000000 | 12000.00 |
| AD_ASST | 4400.00 | 4400.00 | 4400.000000 | 4400.00 |
| AD_PRES | 24000.00 | 24000.00 | 24000.000000 | 24000.00 |
4. 选择具有各个job_id 的员工的人数
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
+------------+----------+
| job_id | COUNT(*) |
+------------+----------+
| AC_ACCOUNT | 1 |
| AC_MGR | 1 |
| AD_ASST | 1 |
5. 查询员工最高工资和最低工资的差距 DIFFERENCE
SELECT MAX(salary) - MIN(salary) "DIFFERENCE"
FROM employees;
+------------+
| DIFFERENCE |
+------------+
| 21900.00 |
+------------+
6. 查询各个管理者手下员工的最低工资, 其中最低工资不能低于6000, 没有管理者的员工不计算在内
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;
+------------+-------------+
| manager_id | MIN(salary) |
+------------+-------------+
| 102 | 9000.00 |
| 108 | 6900.00 |
| 145 | 7000.00 |
7. 查询所有部门的名称, location_id, 员工的数量和平均工资, 并按平均工资降序排序
SELECT department_name, location_id, COUNT(e.employee_id), IFNULL(AVG(salary), 0) "AVG(salary)"
FROM departments d LEFT JOIN employees e
ON e.department_id = d.department_id
GROUP BY department_name, location_id
ORDER BY AVG(salary) DESC;
+----------------------+-------------+----------+--------------+
| department_name | location_id | COUNT(*) | AVG(salary) |
+----------------------+-------------+----------+--------------+
| Executive | 1700 | 3 | 19333.333333 |
| Accounting | 1700 | 2 | 10150.000000 |
| Public Relations | 2700 | 1 | 10000.000000 |
| Marketing | 1800 | 2 | 9500.000000 |
| Sales | 2500 | 34 | 8955.882353 |
8. 查询每个工种, 每个部门的部门名, 工种名和最低工资
SELECT d.department_name, j.job_title, MIN(e.salary)
FROM departments d JOIN employees e
ON d.department_id = e.department_id
JOIN jobs j
ON e.job_id = j.job_id
GROUP BY d.department_name, j.job_title;
+------------------+---------------------------------+---------------+
| department_name | job_title | MIN(e.salary) |
+------------------+---------------------------------+---------------+
| Accounting | Public Accountant | 8300.00 |
| Accounting | Accounting Manager | 12000.00 |
| Administration | Administration Assistant | 4400.00 |
这题总觉得有些问题, 可能是我对题意理解有问题
9. 子查询
子查询就是将查询语句嵌套在另一个查询语句内部的查询, MySQL 4.1 开始引入.
1. 需求分析与问题解决
1.1 实际问题
问题: 谁比Abel 的工资高?
诞生子问题: Abel 的工资是多少?
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE last_name = 'Abel');
+-----------+----------+
| last_name | salary |
+-----------+----------+
| King | 24000.00 |
| Kochhar | 17000.00 |
| De Haan | 17000.00 |
| Greenberg | 12000.00 |
| Russell | 14000.00 |
| Partners | 13500.00 |
| Errazuriz | 12000.00 |
| Ozer | 11500.00 |
| Hartstein | 13000.00 |
| Higgins | 12000.00 |
+-----------+----------+
不用子查询的方式
SELECT e1.last_name, e1.salary
FROM employees e1, employees e2
WHERE e1.salary > e2.salary
AND e2.last_name = 'Abel';
算是不等值连接
+-----------+----------+
| last_name | salary |
+-----------+----------+
| King | 24000.00 |
| Kochhar | 17000.00 |
| De Haan | 17000.00 |
| Greenberg | 12000.00 |
| Russell | 14000.00 |
| Partners | 13500.00 |
| Errazuriz | 12000.00 |
| Ozer | 11500.00 |
| Hartstein | 13000.00 |
| Higgins | 12000.00 |
+-----------+----------+
会发现结果是相同的.
称呼: 外查询, 内查询(都是相对的)
1.2 子查询的基本使用
- 子查询(内查询)在主查询之前一次执行完成
- 子查询的结果被主(外)查询使用
- 注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询, 多行操作符对应多行子查询
1.3 子查询的分类
内查询返回的结果的条目数:
- 单行子查询: 返回的结果是一行一列
- 多行子查询: 返回的结果是多行多列
内查询是否被执行多次:
- 相关子查询
- 非相关子查询
相关子查询的需求, 子查询的返回每次返回的可能不一样, 需要根据外部的条件来确定
相关子查询:
查询工资大于本部门平均工资的员工信息(子查询查出来的值要根据外部的部门id来确定, 因此是相关的)
非相关子查询:
查询工资大于公司平均工资的员工信息(子查询查出来的都是一样的, 都是公司平均工资, 这个只用执行一次.)
2. 单行子查询
2.1 单行比较操作符
- 等于: =
- 不等于: !=, <>
- 大于: >
- 小于: <
- 大于等于: >=
- 小于等于: <=
2.2 代码示例
查询工资大于 149号员工工资的员工的信息、
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE employee_id = 149);
可以从外往里写, 也可以从里往外写.
返回 job_id 与 141 号员工相同, salary 比 143 号员工多的员工信息
SELECT employee_id, last_name, job_id, salary, job_id
FROM employees
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary > (SELECT salary
FROM employees
WHERE employee_id = 143);
返回公司工资最少的员工的 last_name, job_id 和 salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees);
查询与 141 号员工的 manager_id 和 department_id 相同的其他员工
用了成对比较的方法
SELECT employee_id, last_name, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) = (SELECT manager_id, department_id
FROM employees
WHERE employee_id = 141)
AND employee_id NOT IN (141);
2.3 HAVING 中的子查询
查询最低工资大于50号部门最低工资的部门id 和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
2.4 CASE 中的子查询
显示员工的employee_id, last_name, job_id 和 location, 其中若员工的 department_id 与 location_id为1800的department_id相同, 则 location 为 'Canada', 其余为 'USA'
SELECT e.employee_id, e.last_name, e.job_id,
CASE WHEN e.department_id = (SELECT department_id
FROM departments
WHERE location_id = 1800)
THEN 'Canada'
ELSE 'USA' END "location"
FROM employees e;
2.5 子查询中的空值问题
有时候子查询不返回任何结果, 这时候就会返回 NULL
SELECT last_name, job_id
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE last_name = 'hans');
其实也还好, 就是空的
2.6 非法使用子查询
有时候回返回多行, 但是等号又是单行操作符, 这时候就会报错
3. 多行子查询
3.1 多行比较操作符
- 内查询返回的结果是多行的
- 使用多行比较操作符
IN: 等于列表中的任意一个值
ANY: 需要和单行比较操作符一起使用, 和子查询返回的某一个值进行比较
ALL: 需要和单行比较操作符一起使用, 和子查询返回的所有值比较
SOME: 实际上是 ANY 的别名, 作用相同
3.2 代码示例
查询各个部门的最低工资的员工是哪一位
SELECT last_name, department_id, salary
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id);
ANY: 意思是存在一个就可以
返回其他 job_id 比 job_id 为 'IT_PROG' 部门任一工资低的员工的员工号、姓名、job_id 以及 salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id != 'IT_PROG'
AND salary < ANY (SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');
查询平均工资最低的部门id
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1);
MySQL 中聚合函数不能嵌套, 不然直接 MIN(AVG(salary)), 排序不用也行, 改成<=ALL, 那么自己就是最小的.
或者
SELECT MIN(t.avg_sal)
FROM (
SELECT AVG(salary) avg_sal, department_id
FROM employees
GROUP BY department_id
) t;
3.3 空值问题
SELECT "1" IN ("a", "b", "c", NULL, "1");
+-----------------------------------+
| "1" IN ("a", "b", "c", NULL, "1") |
+-----------------------------------+
| 1 |
+-----------------------------------+
SELECT "1" IN ("a", "b", "c", "1");
+-----------------------------+
| "1" IN ("a", "b", "c", "1") |
+-----------------------------+
| 1 |
+-----------------------------+
SELECT NULL IN ("a", "b", "c", NULL, "1");
+------------------------------------+
| NULL IN ("a", "b", "c", NULL, "1") |
+------------------------------------+
| NULL |
+------------------------------------+
SELECT NULL IN ("a", "b", "c" , "1");
+-------------------------------+
| NULL IN ("a", "b", "c" , "1") |
+-------------------------------+
| NULL |
+-------------------------------+
4. 相关子查询
4.1 相关子查询执行流程
相关子查询
SELECT col1, col2, ...
FROM table1 outer
WHERE col1 op (SELECT col1
FROM table2
WHERE expr1 = outer.expr2);
这里体现相关性的就是子查询的条件是根据外部表的信息的 outer.expr2
4.2 代码示例
查询员工中工资大于本部门平均工资的员工的 last_name, salary 和 department_id
SELECT last_name, salary, department_id
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id);
-- 38 rows
另一种写法: 在 FROM 里写子查询
SELECT last_name, salary, e1.department_id
FROM employees e1, (SELECT AVG(salary) avg_sal, department_id
FROM employees
GROUP BY department_id) t
WHERE e1.department_id = t.department_id
AND e1.salary > t.avg_sal;
-- 38 rows
相当于是先得到每个部门的平均工资表, 然后再和员工表进行连接, 这样就不用每次都计算一次平均工资了.
avg_sal 别名是必要的, 因为要和外部的 salary 进行比较
如果是大于本公司的平均工资
SELECT last_name, salary, department_id
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees);
查询员工的 id, salary 按照 department_name 排序
SELECT employee_id, salary
FROM employees e1
ORDER BY (SELECT department_name
FROM departments d
WHERE e1.department_id = d.department_id);
- SELECT 查询中, 除了 GROUP BY 和 LIMIT 都可以写子查询
4.3 EXISTS 和 NOT EXISTS 关键字
查询公司管理者的 employee_id, last_name, job_id, department_id 信息
SELECT mgr.employee_id, mgr.last_name, mgr.job_id, mgr.department_id
FROM employees e JOIN employees mgr
ON e.manager_id = mgr.employee_id;
这样会查出来106条, 会有很多重复的信息
因此要加上 DISTINCT 关键字去重
SELECT DISTINCT mgr.employee_id, mgr.last_name, mgr.job_id, mgr.department_id
FROM employees e JOIN employees mgr
ON e.manager_id = mgr.employee_id;
这样剩下 18 条, 去重了
使用子查询的方式
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees);
这样直接就是18 条了, 因为外查询只会遍历一遍 eployees 表, 因此不会产生重复的情况.
使用 EXISTS 的方式
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS (SELECT 1
FROM employees e2
WHERE e1.employee_id = e2.manager_id);
这里 1 是因为查到的内容不重要, 重要的是是否存在, 因此 1 就可以了. 或者 *
查询 departments 表中, 不存在于 employees 表中的部门的 department_id 和 department_name
SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (SELECT 1
FROM employees
WHERE d.department_id = employees.department_id);
16 rows
相当于查出来这几个部门是没有人的
NOT EXISTS 其实不算一个关键字, 而是 NOT 和 EXISTS 的组合
4.4 相关更新和删除
在 employees 表中增加一个 department_name 字段, 并将每个员工的部门名称更新到该字段中
删除 employees 表中所有于 emp_history 表中相同的记录
5. 思考
我们一开始的问题是: 谁比Abel 的工资高?
我们可用的方法有两种: 自连接 和 子查询
那么有一个问题, 哪一种方法更好呢?
自连接的方法更好, 因为大部分 DBMS 优化器会将自连接转换为内连接, 而内连接的效率要高于子查询.
6. 子查询练习
1. 查询和 Zlotkey 相同部门的员工的员工姓名和工资（会不会有重名的）
SELECT last_name, salary
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey');
如果有重名的就用 IN 而不用等号.
2. 查询工资比公司平均工资高的员工的员工号、姓名和工资
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees);
3. 选择工资大于所有 JOB_ID="SA_MAN" 的员工的工资的员工的last_name, job_id 和 salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary > ALL (SELECT salary
FROM employees
WHERE job_id = "SA_MAN");
-- 3 rows
或者
SELECT last_name, job_id, salary
FROM employees
WHERE salary > (SELECT MAX(salary)
FROM employees
WHERE job_id = "SA_MAN");
-- 这种也可以
4. 查询姓名中包含字母u 的员工在相同部门的员工的员工号和姓名
SELECT e1.employee_id, e1.last_name
FROM employees e1
WHERE e1.department_id IN (SELECT department_id
FROM employees e2
WHERE e2.last_name LIKE "%u%");
5. 查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1700
);
6. 查询管理者是 KING 的员工姓名和工资
SELECT e1.last_name, e1.salary
FROM employees e1
WHERE manager_id IN (SELECT employee_id
FROM employees e2
WHERE e2.last_name = 'KING');
有重名捏, 用 IN
7. 查询工资最低的员工信息: last_name, salary
SELECT last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees);
8. 查询平均工资最低的部门的信息
SELECT d.*
FROM departments d
WHERE department_id = (SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1);
或者
SELECT d.*, (SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d, (
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 1
) t_department
WHERE d.department_id = t_department.department_id;
感觉没我的好, 下面这个是老师写的
9. 查询平均工资最低的部门信息和该部门的平均工资
SELECT d.*, (SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d
WHERE department_id = (SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1);
10. 查询平均工资对高的 job 信息
SELECT j.*
FROM jobs j
WHERE j.job_id = (SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1);
11. 查询平均工资高于公司平均工资的部门有哪些?
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary)
FROM employees);
12. 查询公司所有 manager 的详细信息
SELECT mgr.*
FROM employees mgr
WHERE mgr.employee_id IN (SELECT manager_id
FROM employees);
能用 IN 的时候通常都能改写成 EXISTS
13. 各个部门中, 最高工资中最低的那个部门的最低工资是多少？
-- 这样子查询找到了最高工资中最低的那个部门
SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id = (SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1);
14. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
-- 平均工资最高的部门
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;
SELECT manager_id
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1);
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1));
+-----------+---------------+-------+----------+
| last_name | department_id | email | salary |
+-----------+---------------+-------+----------+
| King | 90 | SKING | 24000.00 |
+-----------+---------------+-------+----------+
15. 查询部门的部门号, 其中不包括job_id 是 ST_CLERK 的员工的部门
SELECT DISTINCT department_id
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE job_id = 'ST_CLERK');
16. 选择所有没有管理者的员工的last_name
SELECT last_name
FROM employees
WHERE manager_id IS NULL;
这个可以不用子查询
17. 查询员工号、姓名、雇佣时间、工资，其中员工的管理者为 "De Haan"
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE last_name = 'De Haan');
感觉子查询的不是指定主键的值作为条件或者 LIMIT 1 了以外最好都用 EXISTS
18. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT employee_id, last_name, salary
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
GROUP BY department_id
HAVING e1.department_id = e2.department_id);
19. 查询每个部门下的部门人数大于5的部门名称
SELECT department_name
FROM departments
WHERE department_id IN (SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5);
20. 查询每个国家下的部门个数大于 2 的国家编号
SELECT DISTINCT l.country_id
FROM locations l
WHERE 2 < (
SELECT COUNT(*)
FROM departments d
WHERE l.location_id = d.location_id
);
10. 创建和管理表
DDL
1. 基础知识
1.1 一条数据存储的过程
- 创建数据库
- 确认字段
- 创建数据表
- 插入数据
1.2 标识符命名规则
- 数据库名、表名不得超过30个字符, 变量名限制为29个
- 必须只能包含A-Z、a-z、0-9、_、共63个字符
- 数据库名、表明、字段名等对象名中间不包含空格
- 同一个MySQL 软件中, 数据库不能重名, 同一个库中, 表不能重名, 同一表中, 字段不能重名.
- 必须保证你的字段没有和保留字、数据库系统或常用方法冲突. 如果坚持使用, 请在SQL语句中使用 ` 符号包裹
- 保持字段名和类型的一致性: 在明明字段并为其指定数据类型的时候一定要保证一致性, 加入数据类型在一个表里是整数, 在另一个表里也最好是整数
1.3 MySQL 中的数据类型
| 类型 | 类型举例 |
|---|---|
| 整数 | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT |
| 浮点类型 | FLOAT, DOUBLE |
| 定点数类型 | DECIMAL |
| 位类型 | BIT |
| 日期时间类型 | YEAR, DATE, TIME, 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 |
2. 创建和管理数据库
2.1 创建数据库
1. 方式1
CREATE DATABASE db_name;
SHOW CREATE DATABASE db_name;
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| db_name | CREATE DATABASE `db_name` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
得到默认信息
2. 方式2: 显式指定字符集
CREATE DATABASE db_name2 CHARACTER SET utf8mb4;
3. 方式3:
CREATE DATABASE IF NOT EXISTS db_name3;
-- 保证了不报错
2.2 使用数据库
1. 当前连接中的数据库都有哪些
SHOW DATABASES;
2. 切换(使用)数据库
USE db_name;
3. 查看当前使用的数据库中的数据表
SHOW TABLES;
4. 查看当前使用的数据库
SELECT DATABASE();
5. 查看指定数据库下保存的数据表
SHOW TABLES FROM db_name;
2.3 修改数据库
1. 修改数据库的字符集
ALTER DATABASE db_name CHARACTER SET 字符集;
2.4 删除数据库
方式1:
DROP DATABASE db_name;
方式2:
DROP DATABASE IF EXISTS db_name;
3. 创建表
3.1 创建方式1
1. 创建一个表
-- 需要用户具备创建表的权限
CREATE TABLE IF NOT EXISTS mytable (
id INT,
emp_name VARCHAR(20),
hire_date DATE
);
查看表结构
DESC mytable;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| emp_name | varchar(20) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
或者
SHOW CREATE TABLE mytable;
+---------+---------------------------------
| Table | Create Table |
+---------+---------------------------------
| mytable | CREATE TABLE `mytable` (
`id` int DEFAULT NULL,
`emp_name` varchar(20) DEFAULT NULL,
`hire_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+---------+--------------------------------
如果创建表时没有指明使用的字符集, 则默认使用表所在的数据库的字符集
3.2 创建方式2
基于现有的表创建新的表
CREATE TABLE mytable2
AS
SELECT employee_id, last_name, salary
FROM employees;
DESC mytable2;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int | NO | | 0 | |
| last_name | varchar(25) | NO | | NULL | |
| salary | double(8,2) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
SELECT * FROM mytable2;
这种方式创建的表是有数据的. 就是得到通过查询语句得到的
3.3 查看数据表结构
DESC mytable2;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int | NO | | 0 | |
| last_name | varchar(25) | NO | | NULL | |
| salary | double(8,2) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
4. 修改表
4.1 追加一个列
增加一个字段
ALTER TABLE mytable
ADD COLUMN
phone VARCHAR(20);
ALTER TABLE mytable
ADD salary DOUBLE(8, 2) AFTER id;
AFTER 的意思是在哪个字段后面, 不然就添加到表的最后面了.
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| salary | double(8,2) | YES | | NULL | |
| emp_name | varchar(20) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
还有一个是 FIRST, 就是在第一个字段的前面
4.2 修改一个列
修改列的数据类型之类的, 一般不改
ALTER TABLE mytable
MODIFY emp_name VARCHAR(45) DEFAULT 'unknown';
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| salary | double(8,2) | YES | | NULL | |
| emp_name | varchar(45) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
修改了一下长度. 增加了默认值
4.3 重命名一个列
ALTER TABLE mytable
CHANGE COLUMN phone phone_number VARCHAR(20);
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| salary | double(8,2) | YES | | NULL | |
| emp_name | varchar(45) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
4.4 删除一个列
ALTER TABLE mytable
DROP COLUMN phone_number;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| salary | double(8,2) | YES | | NULL | |
| emp_name | varchar(45) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5. 重命名表
方式1:
RENAME TABLE mytable TO mytable_new;
我又改回来啦
方式2:
ALTER TABLE mytable_new
RENAME TO mytable;
6. 删除表
无法恢复, 慎用
DROP TABLE IF EXISTS mytable2;
7. 清空表
只是清空数据, 表结构还在
TRUNCATE TABLE mytable;
DCL 中的 COMMIT 和 ROLLBACK
- COMMIT: 提交数据, 一旦执行 COMMIT, 则数据就被永久的保存在了数据库, 意味着数据不可以回滚
- ROLLBACK: 回滚数据, 一旦执行 ROLLBACK, 则数据就被回滚到上一次 COMMIT 之后的状态
对比 TRUNCATE TABLE 和 DELETE FROM
-
相同点: 都可以实现对表中的所有数据的删除, 同时保留表结构
-
不同点:
- TRUNCATE TABLE 是清空表数据. 同时数据无法回滚
- DELETE FROM 是删除表中的数据, 同时数据可以回滚, 可以只删除部分数据
-
DDL 的操作一旦执行, 就无法回滚, 因为 DDL 操作结束后一定会自动执行一次 COMMIT, 而此COMMIT 操作不受
SET autocommit = false影响 -
DML 的操作在默认情况下是无法回滚的, 但是可以通过设置事务的方式来实现回滚, 设置
SET autocommit = false, 则执行的 DML 操作就可以实现回滚, 想要回滚的时候执行 ROLLBACK
8. 内容拓展
-
阿里巴巴规范: TRUNCATE TABLE 比 DELETE 速度快, 且使用的系统和事务日志资源少, 但 TRUNCATE 无事务且不触发 TRIGGER, 有可能造成事故, 因此不建议在开发代码中使用这个语句
-
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同.
-
[强制] 表明、字段名必须使用小写字母或数字, 禁止出现数字开头, 禁止两个下划线中间只出现数字, 数据库字段名的修改代价大, 因为无法进行预发布, 所以字段名需要慎重考虑
- 正例: aliyun_admin, rdc_config
- 反例: AliyunAdmin
-
[强制] 禁用保留字, 如 desc 等
-
[强制] 表必备三字段, id, gmt_create, gmt_modified
- id 必为主键, 类型为 BIGINT UNSIGNED、单表时自增, 步长为1
- gmt_create 表示主动式创建
- gmt_modified 表示被动式更新
-
[推荐] 表的命名最好是遵循"业务名称_表的作用"
- alipay_task, force_project
MySQL 新特性-DDL 的原子化
DDL 操作要么成功要么回滚
如下显示
当前只有一个表 book21, 没有 book2
DROP TABLE book1, book2;
这时候会报错, book1 会依然在.
11. 数据处理之增删改
1. 插入数据
1.1 插入数据实际问题
use atguigudb;
CREATE TABLE IF NOT EXISTS emp1(
id INT,
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(8, 2)
);
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(15) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
| salary | double(8,2) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
此时里面是没有数据的
1.2 方式1: VALUES 的方式添加
一条一条添加数据
1. 没有指明添加字段, 因此要严格按照声明的顺序添加
INSERT INTO emp1 VALUES (1, "luo", "2010-3-3", 20000);
2. 指明了添加的字段
INSERT INTO emp1 (id, salary, `name`) VALUES (2, 30000, "ting");
没有写的字段就按默认的
3. 一次插入多条
INSERT INTO emp1 (id, `name`, salary)
VALUES
(3, "zhao", 4000),
(4, "wang", 5000);
1.2 方式2: 将查询结果插入到表内
INSERT INTO emp1 (id, `name`, salary)
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id IN (60,70);
要提前看看被添加的表的字段的长度不能低于被查询的表的长度
2. 更新数据
UPDATE ... SET ... WHERE ...
UPDATE emp1
SET salary=25000
WHERE `name` = 'luo';
同时修改一条数据的多个字段
UPDATE emp1
SET salary=salary+1000, hire_date=CURDATE()
WHERE `name` = 'luo';
修改数据是有可能修改不成功的, 有可能因为约束的影响造成的
3. 删除数据
DELETE FROM ...
WHERE ...
DELETE FROM emp1
WHERE `name` = 'luo';
DELETE 也可能删除失败, 有可能因为约束的影响造成的
DML 操作默认情况下, 执行完都会自动提交数据
如果下网执行完不自动提交数据, 则需要使用SET autocommit = FALSE;
4. MySQL 8 新特性: 计算列
某一列的值是通过别的列计算得来的, 这个列就是计算列
CREATE TABLE IF NOT EXISTS emp2(
id INT,
salary DOUBLE(8, 2),
bonus DOUBLE(8, 2) GENERATED ALWAYS AS (salary * 0.1) VIRTUAL
);
5. 综合案例
1. 空着了
12. MySQL 数据类型精讲
1. MySQL 中的数据类型
| 类型 | 类型举例 |
|---|---|
| 整数 | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT |
| 浮点类型 | FLOAT, DOUBLE |
| 定点数类型 | DECIMAL |
| 位类型 | BIT |
| 日期时间类型 | YEAR, DATE, TIME, 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 |
创建表时常遇到的数据类型的属性, 如下:
| MySQL关键字 | 含义 |
|---|---|
| NULL | 数据列可包含NULL值 |
| NOT NULL | 数据列不允许包含NULL值 |
| DEFAULT | 默认值 |
| PRIMARY KEY | 主键 |
| AUTO_INCREMENT | 自增, 适用于整数类型 |
| UNSIGNED | 无符号, 适用于整数类型 |
| CHARACTER SET 字符集 | 指定字符集 |
2. 整数类型
2.1 类型介绍
整数类型一共有5种, 包括: TINYINT, SMALLINT, MEDIUMINT, INT(INTEGER), BIGINT
| 整数类型 | 字节 | 有符号取值范围 | 无符号取值范围 |
|---|---|---|---|
| TINYINT | 1 | (-128, 127) | (0, 255) |
| SMALLINT | 2 | (-32768, 32767) | (0, 65535) |
| MEDIUMINT | 3 | (-8388608, 8388607) | (0, 16777215) |
| INT | 4 | (-2147483648, 2147483647) | (0, 4294967295) |
| BIGINT | 8 | (-9223372036854775808, 9223372036854775807) | (0, 18446744073709551615) |
2.2 可选属性
2.2.1 M
M: 表示显示宽度, M 的取值范围: [0,255]
MySQL 5.7 会在类型后面有一个括号, 括号里面的数字代表可能的最大的长度, 比如 TINYINT(4) 是显示宽度, 但是不会影响存储和取值范围, 直接用没有用, 但是加上 ZEROFILL 就会有用了. 显示宽度不足4位时会用0填充. 用了 ZEROFILL 之后, 会自动加上 UNSIGNED.
从 MySQL 8 开始, 整数数据类型不推荐使用显示宽度属性.
2.2.2 UNSIGNED
UNSIGNED 是无符号类型, 所有的整数类型都有一个可选的属性 UNSIGNED
2.2.3 ZEROFILL
0 填充, 与 M 属性配合, 会自动加上 UNSIGNED
2.3 适用场景
- TINYINT: 一般用域枚举数据, 比如系统设定取值范围很小且固定的场景
- SMALLINT: 可以用于较小范围的统计数据, 比如统计工厂的固定资产库存数量
- MEDIUMINT: 用于较大范围的统计数据, 比如车站每日的客流量
- INT: 用于较大范围的统计数据, 比如公司每日的销售额
- BIGINT: 用于较大范围的统计数据, 比如国家的人口数量，或者双十一的交易量
2.4 如何选择?
根据实际情况选择, 一般来说, 一个表中的整数类型的字段越小, 则占用的存储空间越小, 读取的速度也越快, 但是如果数据量很大, 则需要选择大一点的整数类型. 考虑存储空间和可靠性. 一般用 INT, 系统故障的成本比存储空间的成本要高得多.
3. 浮点类型
3.1 类型介绍
浮点数和定点数都可以处理小数, 可以把整数看成小数的一个特例, 因此浮点数和定点数的使用场景更大. MySQL 支持的浮点数类型: FLOAT, DOUBLE, REAL.
REAL 默认就是 DOUBLE, 如果你把 SQL 模式设定为启动 “REAL_AS_FLOAT” 选项, 那么 REAL 就是 FLOAT, 如果要启动 “REAL_AS_FLOAT” 选项, 可以 SET sql-mode=REAL_AS_FLOAT
3.2 数据精度说明
MySQL 中单精度值使用 4 个字节, 双精度使用 8 个字节
3.3 精度误差说明
因为是2进制存储的, 所以有些小数是无法精确存储的. 因此在浮点数的时候, 要避免用 “=” 来判断两个数是否相等
4. 定点数类
4.1 类型介绍
使用 DECIMAL(M,D) 来表示高精度小数, 其中 M 被称为精度, D被称为标度.
DECIMAL 的最大取值范围与 DOUBLE 类型一样. 默认是 DECIMAL(10,0), 也就是说默认是整数.
- 浮点数相对于定点数的有点是在长度一定的情况下, 浮点类型取值范围大, 但是不精确, 适用于需要取值范围大, 又可以容忍微小误差的科学计算场景
- 定点数类型取值范围相对小, 但是精准, 没有误差, 适合用域对精度要求极高的场景, 比如涉及钱的计算的场景.
- DECIMAL 会存在四舍五入
4.2 开发中经验
由于 DECIMAL 数据类型的精准性, 在项目中, 除了极少数(如商品编号)用到整数类型外, 其他的数值用的都是 DECIMAL 类型, 以保证数据的精准性, 对于钱的全部用 DECIMAL 类型
5. 位类型
BIT(M): 长度 M, M 的取值范围是 1-64, 占用约 (M+7)/8 个字节, 用 值+0 就可以显示10进制的值
6. 日期与时间类型
| 类型 | 名称 | 字节 | 日期格式 | 最小值 | 最大值 |
|---|---|---|---|---|---|
| YEAR | 年 | 1 | YYYY | 1901 | 2155 |
| TIME | 时间 | 3 | HH:MM:SS | ‘-838:59:59’ | ‘838:59:59’(还能表示时间间隔) |
| DATE | 日期 | 3 | YYYY-MM-DD | ‘1000-01-01’ | ‘9999-12-31’ |
| DATETIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | ‘1000-01-01 00:00:00’ | ‘9999-12-31 23:59:59’ |
| TIMESTAMP | 时间戳 | 4 | YYYY-MM-DD HH:MM:SS | ‘1970-01-01 00:00:00’ UTC | ‘2038-01-19 03:14:07’ UTC |
6.1 YEAR类型
几种存储格式:
- 以 4 位字符串或数字格式表示 YEAR 类型, 其格式为 YYYY, 可以存储的范围是 1901-2155
- 以 2 位字符串格式表示 YEAR 类型, 最小值为 00, 最大值为 99
- 当取值范围为 01-69 时, 表示的年份为 2001-2069
- 当取值范围为 70-99 时, 表示的年份为 1970-1999
- 00 比较复杂, 一种是 0000 年, 一种是 2000 年
- ‘00’/‘0’ 表示 2000 年
- 00/0 表示 0000 年 2 位不推荐使用了, 默认现在是 4 位
6.2 DATE类型
- 以 YYYY-MM-DD 格式存储日期, 取值范围是 1000-01-01 到 9999-12-31
- 以 YY-MM-DD 格式或者 YYYYMMDD 格式存储日期, 取值范围是 1970-01-01 到 2069-12-31
- 使用 CURRENT_DATE() 或 NOW() 可以获取当前日期
6.3 TIME类型
需要占 3 个字节,
- 可以使用带有冒号的字符串, 比如 “D HH:MM:SS”, “HH:MM:SS”, “HH:MM”, “D HH:MM”, “D HH” 或 “SS” 格式, D 表示天, 最小值是0, 最大值是 34, 如果使用带有 D 格式的字符串插入 TIME 类型的字段时, D 会被转化为小时, 计算格式为 “D*24+HH”, 当使用带有冒号且不带 D 的字符串表示时间时, 表示当天的时间, 比如 12:10 表示 12:10:00 而不是 00:12:10
- 可以使用不带有冒号的字符串或数字, 格式为 “HHMMSS” 或者 HHMMSS。 如果插入一个不合法的字符串或者数字, MySQL 在存储数据时, 会将其自动转化为 00:00:00 进行存储, 比如 1210, MySQL 会将最右边的两位解析成秒, 表示 00:12:10 而不是 12:10:00
- 使用 CURRENT_DATE() 或 NOW() 可以获取当前日期
6.4 DATETIME类型
需要占用 8 个字节
- 可以表示为 “YYYY-MM-DD HH:MM:SS” 格式, 取值范围是 1000-01-01 00:00:00 到 9999-12-31 23:59:59
- 以 YY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式存储日期, 取值范围是 1970-01-01 00:00:00 到 2069-12-31 23:59:59
6.5 TIMESTAMP类型
需要占用 4 个字节
- 存储数据的时候需要对当前时间所在的时区进行转换, 查询数据的时候再将时间转换回当前的失去, 因此, 使用 TIMESTAMP 存储同一个时间值, 在不同的时区查询会显示不同的时间
- 时间范围是 1970-01-01 00:00:01 到 2038-01-19 03:14:07
6.6 开发中经验
一般用时间戳
7. 文本字符串类型
| 文本字符串类型 | 值的长度 | 长度范围 | 占用的存储空间 |
|---|---|---|---|
| CHAR(M) | M | 0-255 | M 字节 |
| VARCHAR(M) | M | 0-65535 | 实际长度+1 字节 |
| TINYTEXT | L | 0-255 | L+2 字节 |
| TEXT | L | 0-65535 | L+2 字节 |
| MEDIUMTEXT | L | 0-16777215 | L+3 字节 |
| LONGTEXT | L | 0-4294967295 | L+4 字节 |
| ENUM | L | 0-65535 | 1或2 字节 |
| SET | L | 0-64 | 1, 2, 3, 4 或 8 字节 |
7.1 CHAR与VARCHAR类型
- CHAR: 固定长度字符串, 如果存储的字符串长度小于 M, 则会在字符串后面用空格填充, 如果存储的字符串长度大于 M, 则会被截断或者报错
- 如果 CHAR 插入的字符串后面有空格, 检索出来的时候也会被去掉
- VARCHAR: 可变长度字符串, 如果存储的字符串长度小于 M, 则会按照实际长度存储, 如果存储的字符串长度大于 M, 则会被截断
CHAR
- 一般需要预先定义字符串长度, 如果不指定M, 则默认为1个字符
- 如果保存时, 数据的实际长度比 CHAR 类型声明的长度小, 则会在右侧填充空格以达到指定的长度. 当 MySQL 检索 CHAR 类型的数据时, CHAR 类型的字段会去除尾部的空格.
- 定义 CHAR 类型字段时, 声明的字段长度即为 CHAR 类型字段所占的存储空间的字符数
VARCHAR
- VARCHAT(M) 定义时, 必须指定长度 M, 否则报错
- MySQL4.0以下的版本, VARCHAR(20): 指的是20个字节, 如果存放 UTF8汉字时, 只能存放 6 个汉字, 但是 MySQL5.0 以上的版本, VARCHAR(20): 指的是20个字符, 如果存放 UTF8汉字时, 可以存放 20 个汉字
- 检索 VARCHAR 类型的字段数据时, 会保留数据尾部的空格. VARCHAR 类型的字段所占用的存储空间为字符串的实际长度加1个字节.
- VARCHAR 后面最大应该是 21845, 如果超过了, 就会报错, 因为65535/3=21845, 但是还要减去一些空间, 所以最大是 21845
根据存储引擎
- MyISAML 最好使用固定长度(CHAR)的数据代替可变长度(VARCHAR)的数据列, 这样是的整个表静态化, 从而使数据检索更快, 用空间换时间.
- MEMORY: MEMORY 数据表目前都使用固定长度的数据行存储, 因此无论使用 CHAR 或 VARCHAR 列都没有关系, 两者都是作为 CHAR 类型处理的
- InnoDB: 建议使用 VARCHAR 类型, 因为对于InnoDB数据表, 内部的行存储格式并没有区分固定长度和可变长度列, 而且主要影响性能的因素时数据行使用的存储总量, 由于 CHAR 平均占用的空间多余 VARCHAR, 所以除了简短并固定长度的, 其他考虑 VARCHAR, 这样节省空间, 对磁盘 I/O 和数据存储总量都比较好.
7.2 TEXT类型
在 MySQL 中, TEXT 用来保存文本类型的字符串, 总共包含四种类型, 分别为 TINYTEXT, TEXT, MEDIUMTEXT 和 LONGTEXT
在向 TEXT 类型的字段保存和查询数据时, 系统自动按照实际长度存储, 不需要预先定义长度, 这一点和 VARCHAR 类型相同.
每种 TEXT 类型保存的数据长度和所占用的存储空间不同, 如下:
| 文本字符串类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 |
|---|---|---|---|---|
| TINYTEXT | 小文本, 可变长 | L | 0-255 | L+2 字节 |
| TEXT | 文本, 可变长 | L | 0-65535 | L+2 字节 |
| MEDIUMTEXT | 中等文本, 可变长 | L | 0-16777215 | L+3 字节 |
| LONGTEXT | 大文本, 可变长 | L | 0-4294967295 | L+4 字节 |
由于实际存储的长度不确定, MySQL 不允许 TEXT 类型的字段做主键. 遇到这种情况, 只能采用 CHAR(M), 或者 VARCHAR(M) 来代替 TEXT 类型的字段.
- 保存的时候不会自动删除尾部的空格.
- TEXT 文本类型, 可以存储比较大的文本段, 搜索速度比较慢, 因此如果不是特别大的内容, 建议使用 CHAR, VARCHAR 来代替.
8. ENUM类型
ENUM 类型也叫枚举类型, ENUM 类型的取值范围需要在定义字段时进行指定, 设置字段值时, ENUM 类型只允许从成员中选取单个值, 不能一次选取多个值
其所需要的存储空间由定义 ENUM 类型时指定的成员个数决定.
| 文本字符串类型 | 长度 | 长度范围 | 占用的存储空间 |
|---|---|---|---|
| ENUM | L | 0-65535 | 1或2 字节 |
- 当 ENUM 类型包含 1-255 个成员时, 占用 1 个字节
- 当 ENUM 类型包含 256-65535 个成员时, 占用 2 个字节
- ENUM 类型的成员个数的上限为 65535 个, 如果超过这个上限, 则会报错
9. SET类型
SET 表示一个字符串对象, 可以表示0个或者多个成员, 但是成员个数的上限为 64, 设置字段值时, 可以取取值范围内的0个或者多个值
当 SET 类型包含的成员个数不同时, 其所占用的存储空间也是不同的, 具体如下:
| 成员个数范围 | 占用的存储空间 |
|---|---|
| 1-8 | 1 字节 |
| 9-16 | 2 字节 |
| 17-24 | 3 字节 |
| 25-32 | 4 字节 |
| 33-64 | 8 字节 |
SET 类型在存储数据时, 成员个数越多, 其占用的存储空间越大, 注意: SET 类型在选取成员时, 可以一次选择多个成员, 这与 ENUM 类型不同.
CREATE TABLE IF NOT EXISTS test_set(
s SET ('a','b', 'c')
);
INSERT INTO test_set VALUES ('a,b');
+------+
| s |
+------+
| a,b |
+------+
SELECT * FROM test_set;
INSERT INTO test_set VALUES ('a,b,c,a');
+-------+
| s |
+-------+
| a,b |
| a,b,c |
+-------+
10. 二进制字符串类型
MySQL 中的二进制字符串类型主要存储一些二进制数据, 比如可以存储图片, 音频和视频等二进制数据
MySQL 中支持的二进制字符串主要包括 BINAY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB 和 LONGBLOB 类型
10.1 BINARY与VARBINARY类型
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR, 只是他们存储的是二进制字符串
10.2 BLOB类型
BLOB 是一个二进制大对象, 可以容纳可变数量的数据
一般用于存储: 图片, 音频, 视频等
实际工作中一般不会在 MySQL 数据库中使用 BLOB 存储图片, 音频, 视频等二进制数据, 一般会将这些数据存储在文件系统中, 然后在数据库中存储文件的路径.
TEXT 和 BLOB 的使用注意事项
- BLOB 和 TEXT 的值也会引起一些问题, 特别是执行了大量地删除或更新操作的时候, 删除这种值会在数据表中留下很大的空洞, 以后填入这些空洞的记录可能长度不同, 为了提高性能, 建议定期使用 OPTIMIZE TABLE 命令来进行碎片处理
- 如果需要对大文本字段进行模糊查询, MySQL 提供了前缀索引. 但是仍然要在不必要的时候避免检索大型的 BLOB 或 TEXT 的值, 例如, SELECT * 查询就不是一个好的操作, 除非你能够确定作为约束条件的 WHERE 子句只会找到所需要的数据行.
- 把 BLOB 或 TEXT 列, 分离到单独的表中. 在某些环境中, 如果把这些数据列移动到第二张数据表中, 可以让你把原数据表中的数据列转换为固定长度的数据行格式, 那么他就是有意义的. 这回减少主表中的碎片, 使得能够发挥固定长度数据行的性能优势.
11. JSON类型
CREATE TABLE IF NOT EXISTS test_json(
info JSON
);
INSERT INTO test_json VALUES ('{"name": "luo", "age": 18}');
SELECT * FROM test_json;
+----------------------------+
| info |
+----------------------------+
| {"age": 18, "name": "luo"} |
+----------------------------+
12. 空间类型
用的比较少
13. 小结及选择建议
- 整数用 INT
- 小数用 DECIMAL
- 如果用日期和时间用 DATETIME
- TEXT 方便但是效率不如 CHAR 和 VARCHAR
建议:
- VARCHAR 不超过5000, 如果超过了就用 TEXT
13. 约束
1. 约束(contraint)的概念
1.1 为什么需要约束
数据完整性(Data Integrity) 是指数据的精确性(Accuracy) 和可靠性(Reliability). 他是方式数据库中存在不符合语义规定的数据和放置因错误信息的输入输出造成无效操作或错误信息而提出的
为了保证数据的完整性, SQL 规范以约束的方式对 表数据进行额外的条件限制, 从以下四个方面考虑:
- 实体完整性(Entity Integrity): 例如: 同一个表中, 不能存在两条完全相同无法区分的记录
- 域完整性(Domain Integrity): 例如: 年龄范围0-255, 性别范围: 男/女
- 引用完整性(Referential Integrity): 例如: 一个表中的外键必须是另一个表中的主键, 员工属于一个部门, 部门表中要能够找到这个部门
- 用户自定义完整性(User-defined Integrity): 例如: 用户名唯一、密码不能为空
1.2 什么是约束
约束时表级的强制规定.
1.3 约束的分类
- 根据约束的字段个数: 单列约束, 多列约束
- 根据约束的作用范围: 列级约束, 表级约束
- 根据约束的作用: 非空约束(NOT NULL), 唯一性约束(UNIQUE), 主键约束(PRIMARY KEY), 外键约束(FOREIGN KEY), 默认约束(DEFAULT), 检查约束(CHECK)
一般在创建表的时候就创建好了约束
查看表中的约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'employees';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | atguigudb | emp_email_uk | atguigudb | employees | UNIQUE | YES |
| def | atguigudb | emp_emp_id_pk | atguigudb | employees | UNIQUE | YES |
| def | atguigudb | PRIMARY | atguigudb | employees | PRIMARY KEY | YES |
| def | atguigudb | emp_dept_fk | atguigudb | employees | FOREIGN KEY | YES |
| def | atguigudb | emp_job_fk | atguigudb | employees | FOREIGN KEY | YES |
| def | atguigudb | emp_manager_fk | atguigudb | employees | FOREIGN KEY | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
2. 非空约束
2.1 作用
限定某个字段的值不允许为空
2.2 关键字
NOT NULL
2.3 特点
- 默认, 所有的类型的值都可以是 NULL, 包括 INT, FLOAT等数据类型
- 非空约束只能出现在表对象的列上, 只能某个列单独限定非空, 不能组合非空
- 一个表可以由很多列都分别限定了非空
- 空字符串和 NULL 是不一样的
2.4 添加非空约束
CREATE TABLE IF NOT EXISTS table1(
id INT NOT NULL,
`name` VARCHAR(15) NOT NULL
);
-- 如果原来有 NULL, 想要改成 NOT NULL, 那么就会报错
ALTER TABLE table1
MODIFY COLUMN email VARCHAR(25) NOT NULL;
2.5 删除非空约束
-- 删除约束
ALTER TABLE table1
MODIFY COLUMN email VARCHAR(25) NULL;
3. 唯一性约束
3.1 作用
用来限制某个字段/某个列的值不能重复
3.2 关键字
UNIQUE
3.3 特点
- 同一个表可以有多个唯一约束
- 唯一约束可以是某一个列的值唯一, 也可以是多个列组合的值唯一
- 唯一性约束允许列值为空
- 在创建唯一性约束时, 如果不给唯一约束命名, 就默认和列名相同
- MySQL 会给唯一性约束的列上默认创建一个唯一索引
3.4 添加唯一性约束
CREATE TABLE IF NOT EXISTS table2(
id INT UNIQUE,
`name` VARCHAR(15),
email VARCHAR(25) UNIQUE,
-- 表级约束
CONSTRAINT table2_email_uk UNIQUE(email)
);
DESC table2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | varchar(15) | YES | | NULL | |
| email | varchar(25) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'table2';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | atguigudb | email | atguigudb | table2 | UNIQUE | YES |
| def | atguigudb | id | atguigudb | table2 | UNIQUE | YES |
| def | atguigudb | table2_email_uk | atguigudb | table2 | UNIQUE | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
这个约束名是用在删除约束的时候用的. 对于有了唯一性约束的列, 可以有多个NULL.
-- 添加约束
ALTER TABLE table2
ADD UNIQUE(email);
-- 如果要加名字
ALTER TABLE table2
ADD CONSTRAINT table2_email_uk UNIQUE(email);
或者
ALTER TABLE table2
MODIFY COLUMN email VARCHAR(25) UNIQUE;
关于复合唯一性约束
CREATE TABLE IF NOT EXISTS table3(
id INT,
`name` VARCHAR(15),
email VARCHAR(25),
-- 复合唯一性约束
UNIQUE(`name`, email) -- 组合的话, 默认的名字是第一个
-- 如果要加名字的话
-- CONSTRAINT table3_name_email_uk UNIQUE(`name`, email)
);
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'table3';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | atguigudb | name | atguigudb | table3 | UNIQUE | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
3.5 删除唯一性约束
ALTER TABLE table2
DROP INDEX email;
4. PRIMARY KEY 约束
4.1 作用
用来唯一标识表中的一行记录
4.2 关键字
PRIMARY KEY
4.3 特点
- 主键约束相当于 唯一约束+非空约束的组合, 主键约束列不允许重复, 也不允许出现空值
- 一个表最多只能有一个主键约束, 建立主键约束可以在列级别创建, 也可以在表级别上创建
- 主键约束对应着表中的一列或者多列(复合主键)
- 如果是多列组合的复合主键约束, 那么这些列都不允许为空值, 并且组合的值不允许重复
- MySQL 的主键名总是 PRIMARY, 就算自己命名了主键约束名也没用
- 当创建主键约束时, 系统默认会在所在的列或者列组合上建立对应的主键索引. 如果删除主键约束了, 主键约束对印的索引就自动删除了.
- 需要注意的一点是, 不要修改主键字段的值, 因为主键是数据记录的唯一标识, 如果修改了主键的值, 就有可能会破坏数据的完整性
4.4 添加主键约束
-- 主键的特征 非空且唯一
在 CREATE TABLE 时, 添加主键约束
CREATE TABLE IF NOT EXISTS table4(
id INT PRIMARY KEY, -- 列级约束
`name` VARCHAR(15),
email VARCHAR(25)
);
4.5 关于复合主键
复合主键两个都不能为 NULL
ALTER TABLE table4
ADD PRIMARY KEY(id, `name`);
4.6 删除主键约束
-- 实际开发中不会删除主键约束
ALTER TABLE table4
DROP PRIMARY KEY;
5. 自增列: AUTO_INCREMENT
5.1 作用
某个字段的值自增
5.2 关键字
AUTO_INCREMENT
5.3 特点和要求
- 一个表最多只能有一个自增长列
- 当需要产生唯一标识符或顺序值时, 可设置自增长
- 自增长列约束的列必须是键列(主键列, 唯一键列)
- 自增约束的列的数据类型必须是整数类型
- 如果自增列指定了 0 和 NULL, 会在当前最大值的基础上自增, 如果自增列手动指定了具体值, 直接赋值为具体值
5.4 如何指定自增约束
ALTER TABLE table4
MODIFY COLUMN id INT AUTO_INCREMENT;
5.5 如何删除自增约束
ALTER TABLE table4
MODIFY COLUMN id INT;
5.6 MySQL 8.0 新特性-自增变量的持久化
这里是 MySQL 5.7
CREATE DATABASE dbtest03;
USE dbtest03;
CREATE TABLE IF NOT EXISTS t1(
id INT AUTO_INCREMENT,
`name` VARCHAR(15),
PRIMARY KEY(id)
);
插入4条数据
INSERT INTO t1(`name`) VALUES('zhangsan');
INSERT INTO t1(`name`) VALUES('lisi');
INSERT INTO t1(`name`) VALUES('wangwu');
INSERT INTO t1(`name`) VALUES('zhaoliu');
DELETE FROM t1 WHERE id=4;
再插入一条数据
INSERT INTO t1(`name`) VALUES('niuqi');
这时候 id 是 5
再删掉
DELETE FROM t1 WHERE id=5;
重启服务器
再插入一条数据
INSERT INTO t1(`name`) VALUES('niuqi');
这时候 id 是 4
因为 MySQL 5.7 这个是存在内存中的, 重启服务器就会丢失
下面是 MySQL 8.0 的区别
CREATE DATABASE dbtest04;
USE dbtest04;
CREATE TABLE IF NOT EXISTS t1(
id INT AUTO_INCREMENT,
`name` VARCHAR(15),
PRIMARY KEY(id)
);
插入4条数据
INSERT INTO t1(`name`) VALUES('zhangsan');
INSERT INTO t1(`name`) VALUES('lisi');
INSERT INTO t1(`name`) VALUES('wangwu');
INSERT INTO t1(`name`) VALUES('zhaoliu');
DELETE FROM t1 WHERE id=4;
再插入一条数据
INSERT INTO t1(`name`) VALUES('niuqi');
这时候 id 是 5
再删掉
DELETE FROM t1 WHERE id=5;
重启服务器
再插入一条数据
INSERT INTO t1(`name`) VALUES('niuqi');
这时候 id 是 6
这个是持久化的, 重启服务器也不会丢失
6. FOREIGN KEY 约束
6.1 作用
限定某个表的某个字段的引用完整性. 也就是说, 限定某个表的某个字段的值必须在另一个表的某个字段的值中存在
6.2 关键字
FOREIGN KEY
6.3 主表和从表/父表和子表
- 主表: 被引用的表, 被参考的表
- 从表: 引用别人的表, 参考别人的表
- 例如: 员工表的员工所在部门的这个字段的值要参考部门表, 部门表示主表, 员工表是从表
- 例如: 学生表, 课程表, 选课表: 选课表的学生和课程要分别参考学生表和课程表, 学生表和课程表是主表, 选课表是从表.
6.4 外键约束的特点
- 从表的外键列, 必须引用/参考主表的键主键或唯一约束的列
- 在创建外键约束时, 如果不给外键约束命名, 默认名不是列名, 而是自动产生一个外键名, 也可以指定外键约束名
- 创建表时就指定外键约束的话, 先创建主表, 再创建从表
- 删表时, 先删从表, 再删主表
- 当主表的记录被从表参照时, 主表的记录将不允许删除, 如果要删除数据, 需要先删除从表中依赖该记录的数据, 然后才可以删除主表的数据
- 在“从表”中指定外键约束, 并且一个表可以建立多个外键约束
- 从表的外键列于主表被参照的列名字可以不相同, 但是数据类型必须一样, 逻辑意义一致. 如果类型不一样, 创建子表时, 就会出现错误
- 当创建外键约束时, 系统默认会在所有的列上建立对应的普通索引. 但索引名是列名, 不是外键的约束名.(根据外键查询效率很高)
- 删除外键约束后, 必须手动删除对应的索引
6.5 添加外键约束
外键约束 foreign key
在 CREATE TABLE 时添加外键约束
主表和从表: 父表和子表
CREATE DATABASE dbtest05;
# 先创建主表
CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
-- 添加外键约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
);
# 这时候会报错, 因为这里dept1 里的 dept_id 不是主键，也没有唯一性约束
# 因此这里增加一个主键
ALTER TABLE dept1
ADD PRIMARY KEY (dept_id);
# 重新创建
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,
-- 添加外键约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
);
DESC emp1;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| emp_id | int | NO | PRI | NULL | auto_increment |
| emp_name | varchar(15) | YES | | NULL | |
| department_id | int | YES | MUL | NULL | |
+---------------+-------------+------+-----+---------+----------------+
或者查看约束信息
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'emp1';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | dbtest05 | PRIMARY | dbtest05 | emp1 | PRIMARY KEY | YES |
| def | dbtest05 | fk_emp1_dept_id | dbtest05 | emp1 | FOREIGN KEY | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
6.6 演示问题
# 演示外键的效果
INSERT INTO emp1
VALUES (1000, "luo", 10);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbtest05`.`emp1`, CONSTRAINT `fk_emp1_dept_id` FOREIGN KEY (`department_id`) REFERENCES `dept1` (`dept_id`))
# 这时候会报错，是因为主表中没有 dept_id 为 10 的记录
INSERT INTO dept1
VALUES (10, "IT");
INSERT INTO emp1
VALUES (1000, "luo", 10);
# 这时候就能成功插入
# 删除失败
DELETE FROM dept1
WHERE dept_id =10;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dbtest05`.`emp1`, CONSTRAINT `fk_emp1_dept_id` FOREIGN KEY (`department_id`) REFERENCES `dept1` (`dept_id`))
# 删除失败, 这是因为 emp1 中用到了dept_id 为 10, 因此从表还关联着, 主表没办法删
# 更新失败
UPDATE dept1
SET dept_id = 20
WHERE dept_id = 10;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dbtest05`.`emp1`, CONSTRAINT `fk_emp1_dept_id` FOREIGN KEY (`department_id`) REFERENCES `dept1` (`dept_id`))
# 与上面的道理是相同的
约束关系时针对双方的:
- 添加了外键约束后, 主表的修改和删除数据受约束
- 添加了外键约束后，从表的添加和修改数据受约束
- 在从表上建立外键，要求主表必须存在
- 删除主表时, 要求从表先删除，或将从表中外键引用该主表的关系先删除
6.7 约束等级
- Cascade 方式: 在父表上 update/delete 记录时, 同步 update/delete 掉子表的匹配记录, 更新跟着更新, 删除跟着删除
- Set null 方式: 在父表上 update/delete 记录时, 同步将子表上匹配记录的列设为null, 但是要注意子表的外键列不能设置为 null, 更新和删除都是设为 null
- No action 方式: 如果子表中有匹配的记录, 则不允许对父表相关记录进行 update/delete 操作
- Restrict 方式: 同 No action, 都是立即检查外键约束
- Set Default 方式: 父表有变更时, 子表将外键列设置成一个默认的值, 但是 innodb 不能识别
如果没有指定约束等级, 就默认使用 Restrict 方式.
一般实际操作时采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式
CREATE TABLE emp3(
eid INT PRIMARY KEY,
ename VARCHAR(15),
deptid INT,
FOREIGN KEY (deptid) REFERENCES dept(dept) ON UPDATE CASCADE ON DELETE SET NULL
);
6.8 删除外键约束
一个表中可以有多个外键约束
# 建表结束后进行添加外键约束
CREATE TABLE dept2 (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);
CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT
);
SELECT * FROM information_schema.table_constraints
WHERE table_name = "emp2";
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | dbtest05 | PRIMARY | dbtest05 | emp2 | PRIMARY KEY | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
# 这时候只有一个主键约束
ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY (department_id) REFERENCES dept2(dept_id);
# 然后看看
SELECT * FROM information_schema.table_constraints
WHERE table_name = "emp2";
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | dbtest05 | PRIMARY | dbtest05 | emp2 | PRIMARY KEY | YES |
| def | dbtest05 | fk_emp2_dept_id | dbtest05 | emp2 | FOREIGN KEY | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
# 删除外键约束
ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | dbtest05 | PRIMARY | dbtest05 | emp1 | PRIMARY KEY | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
对应的外键就被删除了
# 再手动删除外键约束对应的普通索引
SHOW INDEX FROM emp1;
+-------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp1 | 0 | PRIMARY | 1 | emp_id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| emp1 | 1 | fk_emp1_dept_id | 1 | department_id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;
# 根据外键约束名进行删除索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp1 | 0 | PRIMARY | 1 | emp_id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6.9 开发场景
- 问题1: 如果两个表之间有关系(一对一、一对多)，比如: 员工表和部门表(一对多), 他们之间是否一定要建外键约束
答: 不一定需要
- 问题2: 建和不建外键约束有什么区别
答: 建外键约束, 操作(创建表, 删除表, 添加, 修改, 删除) 会受到限制, 从语法层面受到限制. 例如: 在员工表中不能添加一个部门不存在的员工
不建外键约束, 操作(创建表, 删除表, 添加, 修改, 删除) 不受到限制, 保证数据的引用完整性只能依靠程序员的自觉, 或者是在 Java 程序中进行限制. 例如: 在员工表中可以添加一个员工信息, 他的部门指定为一个完全不存在的部门.
- 问题3: 那么建和不建外键约束和查询有没有关系
答: 没有
在 MySQL 里, 外键约束是有成本的, 需要消耗系统资源, 对于大并发的 SQL 操作, 有可能会不合适. 比如大型网站的中央数据库, 可能会因为外键约束导致系统开销而变得很慢. 所以, MySQL 允许不适用系统自带的外键约束, 在应用层面完成检查数据一致性的逻辑, 因此, 即使不使用外键约束, 也要想办法通过系统应用层面的附加逻辑, 来实现外键约束的功能, 确保数据的一致性
6.10 阿里开发规范
- [强制] 不得使用外键与级联, 一切外键概念必须在应用层面解决.
说明: 学生表中的student_id 是主键, 那么成绩表中 student_id 则为外键, 如果更新学生表中的 student_id, 同时出发成绩表中的 student_id 更新, 即为级联更新. 外键与级联更新适用于单机低并发, 不适合使用于 分布式、高并发集群, 级联更新是强阻塞, 存在数据库更新风暴的风险, 外键影响数据库的插入速度.
7. CHECK 约束
7.1 作用
检查某个字段的值是否符合xx要求, 一般指的是值的范围.
7.2 关键字
CHECK
7.3 说明
MySQL 5.7 不支持 check 约束, 就是你加了不会报错, 但是不起作用.
MySQL 8.0 中开始支持 check 约束了.
CREATE TABLE emp4(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK(salary > 2500)
);
INSERT INTO emp4
VALUES (10,"luo", 2000);
-- ERROR 3819 (HY000): Check constraint 'emp4_chk_1' is violated.
INSERT INTO emp4
VALUES (10,"luo", 2500);
-- ERROR 3819 (HY000): Check constraint 'emp4_chk_1' is violated.
INSERT INTO emp4
VALUES (10,"luo", 2501);
# 添加成功
8. DEFAULT 约束
8.1 作用
给某个字段指定默认值, 一旦设置默认值, 在插入数据时, 如果没有显示赋值, 则赋值为默认值.
8.2 关键字
DEFAULT
8.3 如何给字段加默认值
CREATE TABLE emp5(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) DEFAULT 2000
);
INSERT INTO emp5
VALUE(1, "luo", 3000);
SELECT * FROM emp5;
+------+-----------+---------+
| id | last_name | salary |
+------+-----------+---------+
| 1 | luo | 3000.00 |
+------+-----------+---------+
INSERT INTO emp5 (id, last_name)
VALUES (2,"ting");
+------+-----------+---------+
| id | last_name | salary |
+------+-----------+---------+
| 1 | luo | 3000.00 |
| 2 | ting | 2000.00 |
+------+-----------+---------+
CREATE TABLE emp6(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2)
);
# 增加默认值
ALTER TABLE emp6
MODIFY salary DECIMAL(10,2) DEFAULT 2000;
8.4 如何删除默认值约束
ALTER TABLE emp6
MODIFY salary DECIMAL(10,2);
9. 面试
- 问题1: 为什么建表时, 加 not null default "" 或 default 0
答: 因为不想让表中出现 null 值
- 问题2: 为什么不想要 null 值
答: 1. 不好比较, null 是一种特殊值, 比较时只能用专门的 is null 和 is not null 来比较, 碰到运算符通常都返回 null
- 问题3: 带 AUTO_INCREMENT 约束的字段值是从1开始的吗?
在 MySQL 中, 默认AUTO_INCREMENT 的初始值是1, 每新增一条记录, 字段值自动加1. 设置自增属性的时候m 还可以指定第一条插入记录的自增字段的值, 这样插入的记录的自增字段值从初始值开始递增, 如在表中插入第一条记录, 同时指定 id 值为5, 则以后插入的记录的 id 值就会从 6 开始外伤增加, 添加主键约束时, 往往需要设置字段自动增加属性.
- 问题4: 是不是每个表都可以任意选择存储引擎?
外键约束(FOREIGN KEY) 不能跨引擎使用
MySQL 支持多种存储引擎. 每个表都可以指定一个不同的存储引擎, 需要注意的是: 外键约束是用来保证数据的参照完整性的, 如果表之间需要关联外键, 却指定了不同的存储引擎, 那么这些表之间是不能创建外键约束的, 所以说, 存储引擎的选择也不是完全随意的.
14. 视图
1. 常见的数据库对象
| 对象 | 描述 |
|---|---|
| 表(table) | 表是存储数据的逻辑单元, 由行和列组成, 列就是字段, 行就是记录 |
| 数据字典 | 就是系统表, 存放数据库相关信息的表. 系统表的数据通常由数据库系统维护, 程序员通常不应该修改, 只可查看 |
| 约束(constraint) | 执行数据校验的规则, 用于保证数据完整性的规则 |
| 视图(VIEW) | 一个或者多个数据表里的数据的逻辑显示, 视图并不存储数据 |
| 索引(INDEX) | 用于提高查询性能, 相当于书的目录 |
| 存储过程(PROCEDURE) | 用于完成一次完整的业务处理, 没有返回值, 但可通过传出参数将多个值传给调用环境 |
| 存储函数(FUNCTION) | 用于完成一次特定的计算, 具有一个返回值 |
| 触发器(TRIGGER) | 相当于一个事件监听器, 当数据库发生特定事件后, 触发器被处罚, 完成相应的处理 |
2. 视图概念
2.1 为什么使用视图
有时候我们想要给别人看表里部分字段的数据, 但是又不想通过维护一个子表的形式来实现, 因此我们可以使用视图, 因为视图的数据都还在原表里.
视图一方面可以帮我们使用表的一部分而不是所有的表, 另一方面也可以针对不同的用户制定不同的查询视图. 比图, 针对一个公司的销售人员, 我们只想给他看部分数据, 而某些特殊的数据,比如采购的价格, 则不会提供给他. 再比如, 人员薪酬是个敏感的字段, 那么只给某个级别以上的人员开放, 其他人的查询视图中则不提供这个字段.
2.2 视图的理解
- 视图是一种虚拟表, 本身是不具有数据的, 占用很少的内存空间, 他是 SQL 中的一个重要概念.
- 视图建立再已有表的基础上, 视图依赖建立的这些表称为基表.
- 视图的创建和删除只影响视图本身, 不影响对应的基表. 但是当对视图中的数据进行增加、删除和修改操作时, 数据表中的数据会相应地发生变化, 反之亦然
- 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
- 在数据库中, 视图不会保存数据, 数据真正保存在数据表中. 当对视图中的数据进行增加、删除和修改操作时, 数据表中的数据会相应地发生变化, 反之亦然.
- 视图, 是向用户提供基表数据的另一种表现形式. 通常情况下, 小型项目的数据库可以不适用视图, 但是在大型项目中, 以及数据表比较复杂的情况下, 视图的价值就凸显出来了, 他可以帮助我们把经常查询的结果集放到虚拟表中, 提升使用效率. 理解和使用起来都非常方便.
3. 创建视图
CREATE VIEW 视图名
AS SELECT 语句;
3.1 创建单表视图
# 准备工作
CREATE DATABASE dbtest14;
USE dbtest14;
CREATE TABLE emps
AS
SELECT *
FROM atguigudb.employees;
CREATE TABLE depts
AS
SELECT *
FROM atguigudb.departments;
# 针对于单表
CREATE VIEW vu_emp1
AS
SELECT employee_id, last_name, salary
FROM emps;
SELECT * FROM vu_emp1;
CREATE VIEW vu_emp2
AS
SELECT employee_id emp_id, last_name lname, salary #查询语句中字段的别名会作为视图中字段的名字出现
FROM emps
WHERE salary > 10000;
# 小括号内的字段别名要与 SELECT 的字段一一对应
CREATE VIEW vu_emp2(emp_id, name, money_sal)
AS
SELECT employee_id emp_id, last_name, salary
FROM emps
WHERE salary > 10000;
# 查询各个部门的平均工资的一个视图
# 上面的情况是视图中的数据是原表中就有的
# 这里是视图中的字段是基表中没有对应的字段
CREATE VIEW vu_emp_sal
AS
SELECT department_id, AVG(salary)
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;
3.2 创建多表联合视图
CREATE VIEW vu_emp_dept1
AS
SELECT e.employee_id, e.department_id, d.department_name
FROM emps e LEFT JOIN depts d
ON e.department_id = d.department_id;
SELECT * FROM vu_emp_dept;
# 利用视图对数据进行格式化
CREATE VIEW vu_emp_dept2
AS
SELECT CONCAT(e.employee_id, "号员工") emp_id, e.last_name, e.salary, d.department_name
FROM emps e JOIN depts d
ON e.department_id = d.department_id;
SELECT * FROM vu_emp_dept2;
3.3 基于视图创建视图
CREATE VIEW emp_dept_ysalary
AS
SELECT emp_dept.ename, dname, year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;
4. 查看视图
# 1. 查看数据库的表对象, 视图对象
SHOW TABLES;
+--------------------+
| Tables_in_dbtest14 |
+--------------------+
| depts |
| emps |
| vu_emp_dept |
| vu_emp_dept1 |
| vu_emp_dept2 |
+--------------------+
# 2. 查看视图的结构
DESC vu_emp_dept2;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| emp_id | varchar(14) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| salary | double(8,2) | YES | | NULL | |
| department_name | varchar(30) | NO | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
# 3. 查看视图的属性信息
SHOW TABLE STATUS LIKE 'vu_emp_dept2';
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| vu_emp_dept2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2024-03-24 17:21:33 | NULL | NULL | NULL | NULL | NULL | VIEW |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
SHOW TABLE STATUS LIKE 'vu_emp_dept2'\G
*************************** 1. row ***************************
Name: vu_emp_dept2
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: 2024-03-24 17:21:33
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
# 4. 查看视图的详细定义信息
SHOW CREATE VIEW vu_emp_dept2;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| vu_emp_dept2 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vu_emp_dept2` AS select concat(`e`.`employee_id`,'号员工') AS `emp_id`,`e`.`last_name` AS `last_name`,`e`.`salary` AS `salary`,`d`.`department_name` AS `department_name` from (`emps` `e` join `depts` `d` on((`e`.`department_id` = `d`.`department_id`))) | gbk | gbk_chinese_ci |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
5. 更新视图的数据
针对视图中的数据进行DML操作, 会影响到基表中的数据
5.1 一般情况
SELECT employee_id, last_name, salary
FROM emps
LIMIT 0,10;
UPDATE vu_emp1
SET salary = 10000
WHERE last_name = 'Kochhar';
SELECT employee_id, last_name, salary
FROM emps
LIMIT 0,10;
-- 发现修改成功
UPDATE emps
SET salary = 20000
WHERE last_name = 'Kochhar';
SELECT employee_id, last_name, salary
FROM vu_emp1
LIMIT 0,10;
-- 也修改成功
5.2 不可更新的视图
# 有些视图的字段不是来自于基表的, 就有可能不能更新
CREATE VIEW vu_emp_sal
AS
SELECT department_id, AVG(salary) avg_sal
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;
# 比如这个
UPDATE vu_emp_sal
SET AVG(salary)=10000
WHERE department_id=90;
要使得视图可更新, 视图中的行和底层基本表中的行之间必须存在一对一的关系. 另外当视图定义出现如下情况时, 视图不支持更新操作:
- 在定义视图的时候制定了 “ALGORITHM = TEMPTABLE”, 视图将不支持 INSERT 和 DELETE 操作
- 视图中不包含基表中所有被定义为非空又未指定默认值的列, 视图将不支持 INSERT 操作
- 在定义视图的 SELECT 语句中使用了
JOIN 联合查询, 视图将不支持 INSERT 和 DELETE 操作 - 在定义视图的 SELECT 语句后的字段列表中使用了
数学表达式或子查询, 视图将不支持 INSERT, 也不支持 UPDATE 使用了数学表达式、子查询的字段值. - 在定义视图的 SELECT 语句后的字段列表中使用 DISTINCT 关键字, 聚合函数, GROUP BY, HAVING, UNION 等, 视图将不支持 INSERT, UPDATE, DELETE 操作
- 视图定义基于一个
不可更新视图 - 常量视图
6. 修改、删除视图
6.1 修改视图
# 方式1: 使用 CREATE OR REPLACE VIEW 子句修改视图
DESC vu_emp1;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int | NO | | 0 | |
| last_name | varchar(25) | NO | | NULL | |
| salary | double(8,2) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
CREATE OR REPLACE VIEW vu_emp1
AS
SELECT employee_id, last_name, salary, email
FROM emps;
DESC vu_emp1;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int | NO | | 0 | |
| last_name | varchar(25) | NO | | NULL | |
| salary | double(8,2) | YES | | NULL | |
| email | varchar(25) | NO | | NULL | |
+-------------+-------------+------+-----+---------+-------+
# 方式2:
ALTER VIEW vu_emp1
AS
SELECT employee_id, last_name, salary, email, phone_number
FROM emps;
DESC vu_emp1;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| employee_id | int | NO | | 0 | |
| last_name | varchar(25) | NO | | NULL | |
| salary | double(8,2) | YES | | NULL | |
| email | varchar(25) | NO | | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6.2 删除视图
SHOW TABLES;
+--------------------+
| Tables_in_dbtest14 |
+--------------------+
| depts |
| emps |
| vu_emp1 |
| vu_emp_dept |
| vu_emp_dept1 |
| vu_emp_dept2 |
| vu_emp_sal |
+--------------------+
DROP VIEW vu_emp_dept2;
SHOW TABLES;
+--------------------+
| Tables_in_dbtest14 |
+--------------------+
| depts |
| emps |
| vu_emp1 |
| vu_emp_dept |
| vu_emp_dept1 |
| vu_emp_sal |
+--------------------+
DROP VIEW IF EXISTS vu_emp_dept2;
说明: 基于视图a,b 创建了新的视图c, 如果将视图 a 或者视图 b 删除, 会导致视图 c 的查询失败, 这样的视图 c 需要手动删除或修改, 否则影响使用
7. 总结
7.1 视图优点
- 操作简单
将经常使用的查询操作定义为视图, 可以使开发人员不需要关心视图对应的数据表的结构, 表与表之间的关联关系, 也不需要关心数据表之间的业务逻辑和查询条件, 而只需要简单地操作视图即可, 极大简化了开发人员对数据的操作
- 减少数据冗余
视图与实际的数据表不一样, 它存储的式查询语句, 所以在使用的时候, 我们要通过定义视图的查询语句来获取结果集, 而是视图本身不存储数据, 不占用数据存储的资源, 减少了数据冗余
- 数据安全
MySQL 将用户对数据的访问限制在某些数据的结果集上, 而这些数据的结果集可以使用试图来实现, 用户不必直接查询或操作数据表, 这也可以理解视图具有隔离性, 视图相当于在用户和实际的数据表之间加了一层虚拟表.
同时, MySQL 可以根据权限将用户对数据的访问限制在某些视图上, 用户不需要查询数据表, 可以直接通过视图获取数据表中的信息. 这在一定程度上保障了数据表中数据的安全性.
- 适应灵活多变的需求
当业务系统的需求发生变化后, 如果需要修改数据表的结构, 则工作量较大, 因此可以使用视图来减少改动的工作量, 这种方式在实际工作中使用的比较多.
- 能够分解复杂的查询
数据库中如果存在复杂的查询逻辑, 则可以将问题进行分解, 创建多个视图获取数据, 再将创建的多个视图结合起来, 完成复杂的查询逻辑
7.2 视图不足
如果我们在实际数据表的接触上创建了视图, 那么, 如果实际数据表的结果变了, 我们就需要及时对相关的视图进行相应的维护. 特别是嵌套的视图, 维护会变的很复杂.
15. 存储过程与函数
MySQL 5.0 开始支持存储过程和函数. 存储过程和函数能够将复杂的 SQL 逻辑封装在一起, 应用程序无需关注存储过程的函数内部复杂的 SQL 逻辑, 而只需要简单地调用存储过程和函数即可.
就是用户自定义的函数.
1. 存储过程概述
1.1 理解
Stored Procedure, 存储过程, 是一组为了完成特定功能的 SQL 语句集, 存储在数据库中, 经过编译后存储在数据库中, 用户可以通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来调用存储过程.
好处:
- 简化操作, 提高了 SQL 语句的重用性, 减少了开发程序员的压力
- 减少操作过程中的失误, 提高效率
- 减少网络传输量(客户端不需要把所有的SQL语句通过网络发送给服务器)
- 减少了SQL语句暴露在网上的风险, 提高了数据查询的安全性.
和视图、函数的对比
他和视图有相同的优点, 清晰, 安全, 还可以减少网络传输量. 不过视图是虚拟表, 通常不对底层数据表进行直接操作, 而存储过程时程序化的 SQL, 可以直接操作底层数据表, 相比于面向集合的操作方式, 能够实现一些更复杂的数据处理
一旦存储过程被创建出来, 使用起来就像使用函数一样简单, 直接通过调用存储过程名即可, 相较于函数, 存储过程是没有返回值的.
1.2 分类
存储过程的参数类型可以是 IN, OUT, INOUT, 因此可以分为:
- 没有参数(无参数无返回)
- 仅有 IN (有参数无返回)
- 仅有 OUT (无参数有返回)
- 既有 IN 又有 OUT (有参数有返回)
- 有 INOUT (有参数有返回)
2. 创建存储过程
跳过, 不使用这玩意
2.1 语法分析
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 数据类型)
BEGIN
SQL 语句;
END
2.2 代码举例
3. 调用存储过程
3.1 调用格式
3.2 代码举例
3.3 如何调试
4. 存储函数的使用
4.1 语法分析
4.2 调用存储函数
4.3 代码举例
4.4 对比存储函数和存储过程
5. 存储过程和函数的查看、修改、删除
5.1 查看
5.2 修改
5.3 删除
6. 关于存储过程使用的争议
6.1 优点
6.2 缺点
16. 变量、流程控制与游标
和存储过程相关, 因此也跳过先
17. 触发器
在实际开发中, 我们经常会遇到这样的情况: 有 2 个或这多个相互关联的表, 如 商品信息 和 库存信息, 分别存放在 2 个不同的数据表中, 我们在添加一条新商品记录的时候, 为了保证数据的完整性, 必须同时在库存表中添加一条库存记录
这样一来, 我们就必须把这两个关联的操作步骤写道程序里面, 而且要用事务抱起来, 确保这两个操作成为一个原子操作, 要么全部执行, 要么全部不执行, 要是遇到特殊情况, 可能还要对数据进行手动维护, 这样就很容易忘记其中一步, 导致数据缺失
这个时候就可以使用触发器, 让商品信息数据的插入操作自动触发库存数据的插入操作, 这样一来, 就不用因为忘记添加库存数据而导致的数据缺失了.
1. 触发器概述
MySQL 5.0 开始支持, 嵌入到 MySQL 服务器中的一段程序
触发器是由某个事件来触发的某个操作, 这些事件包括 INSERT, UPDATE, DELETE 事件, 所谓事件就是指用户的动作或者触发某项行为, 如果定义了某个出发程序, 当数据库执行这些语句的时候, 就相当于事件发生了, 就会自动激发触发器执行相应的操作.
当对数据表中的数据执行插入、更新和删除操作, 需要自动执行一些数据库逻辑时, 就可以使用触发器来实现.
2. 触发器的创建
2.1 创建触发器语法
CREATE TRIGGER 触发器名
{BEFORE|AFTER} # 触发时机
{INSERT|UPDATE|DELETE} # 触发事件
ON 表名
FOR EACH ROW
触发器执行的语句块
- 表名: 表示触发器监控的对象
- BEFORE|AFTER: 表示触发器执行的时机, BEFORE 表示在触发事件之前执行, AFTER 表示在触发事件之后执行
- INSERT|UPDATE|DELETE: 表示触发器监控的事件, INSERT 表示在插入数据之前或之后执行, UPDATE 表示在更新数据之前或之后执行, DELETE 表示在删除数据之前或之后执行
- 触发器执行的语句块: 触发器执行的具体操作, 可以是一条 SQL 语句, 也可以是一个代码块, 代码块使用 BEGIN 和 END 包裹
2.2 触发器代码举例
CREATE DATABASE dbtest17;
USE dbtest17;
# 1. 创建数据表
CREATE TABLE IF NOT EXISTS test_trigger(
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS test_trigger_log(
id INT PRIMARY KEY AUTO_INCREMENT,
log VARCHAR(30)
);
# 2. 创建触发器
# 在MySQL中，DELIMITER是一个用于改变语句分隔符的命令。
# 默认情况下，MySQL使用分号（;）作为语句的分隔符，表示每个语句的结束。
# 而使用DELIMITER命令，可以将分隔符改变为其他字符，以避免在语句中使用的分号与语句的分隔符冲突。
DELIMITER //
CREATE TRIGGER before_insert_trigger
BEFORE INSERT
ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log(log)
VALUES('插入数据之前触发');
END //
DELIMITER ;
INSERT INTO test_trigger(t_note)
VALUES('test1');
SELECT * FROM test_trigger_log;
+----+------------------+
| id | log |
+----+------------------+
| 1 | 插入数据之前触发 |
+----+------------------+
# 2. 例子2
DELIMITER $
CREATE TRIGGER after_insert_trigger
AFTER INSERT
ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log(log)
VALUES('插入数据之后触发');
END $
DELIMITER ;
INSERT INTO test_trigger(t_note)
VALUES('test2');
SELECT * FROM test_trigger_log;
+----+------------------+
| id | log |
+----+------------------+
| 1 | 插入数据之前触发 |
| 2 | 插入数据之前触发 |
| 3 | 插入数据之后触发 |
+----+------------------+
# 3. 例子3
# 定义触发器 salary_check_trigger, 基于员工表 employees 的 INSERT 事件, 在插入数据之前检查员工的工资是否大于他的领导的薪资, 如果大于领导薪资,
# 则报 sqlstate_value 为 HY000 的错误, 从而使得添加失败
CREATE TABLE employees
AS
SELECT *
FROM atguigudb.employees;
CREATE TABLE departments
AS
SELECT *
FROM atguigudb.departments;
# 创建触发器
DELIMITER $
CREATE TRIGGER salary_check_trigger
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
DECLARE leader_salary DECIMAL(10,2);
SELECT salary INTO leader_salary
FROM employees
WHERE employee_id = NEW.manager_id;
IF NEW.salary > leader_salary THEN
SIGNAL SQLSTATE 'HY000'
SET MESSAGE_TEXT = '员工工资不能大于领导工资';
END IF;
END$
DELIMITER ;
# 测试
INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary, manager_id)
VALUES(207, 'luo', 'xxx@qq.com', '2020-01-01', 'AD_VP', 10000, 103);
# ERROR 1644 (HY000): 员工工资不能大于领导工资
INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary, manager_id)
VALUES(207, 'luo', 'xxx@qq.com', '2020-01-01', 'AD_VP', 8000, 103);
# 成功
3. 查看、删除触发器
3.1 查看触发器
SHOW TRIGGERS;
*************************** 1. row ***************************
Trigger: salary_check_trigger
Event: INSERT
Table: employees
Statement: BEGIN
DECLARE leader_salary DECIMAL(10,2);
SELECT salary INTO leader_salary
FROM employees
WHERE employee_id = NEW.manager_id;
IF NEW.salary > leader_salary THEN
SIGNAL SQLSTATE 'HY000'
SET MESSAGE_TEXT = '员工工资不能大于领导工资';
END IF;
END
Timing: BEFORE
Created: 2024-03-24 19:44:05.86
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
Trigger: before_insert_trigger
Event: INSERT
Table: test_trigger
Statement: BEGIN
INSERT INTO test_trigger_log(log)
VALUES('插入数据之前触发');
END
Timing: BEFORE
Created: 2024-03-24 19:34:33.40
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: utf8mb4_0900_ai_ci
*************************** 3. row ***************************
Trigger: after_insert_trigger
Event: INSERT
Table: test_trigger
Statement: BEGIN
INSERT INTO test_trigger_log(log)
VALUES('插入数据之后触发');
END
Timing: AFTER
Created: 2024-03-24 19:37:10.83
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: utf8mb4_0900_ai_ci
3 rows in set (0.00 sec)
# 2.
SHOW CREATE TRIGGER salary_check_trigger\G
*************************** 1. row ***************************
Trigger: salary_check_trigger
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `salary_check_trigger` BEFORE INSERT ON `employees` FOR EACH ROW BEGIN
DECLARE leader_salary DECIMAL(10,2);
SELECT salary INTO leader_salary
FROM employees
WHERE employee_id = NEW.manager_id;
IF NEW.salary > leader_salary THEN
SIGNAL SQLSTATE 'HY000'
SET MESSAGE_TEXT = '员工工资不能大于领导工资';
END IF;
END
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: utf8mb4_0900_ai_ci
Created: 2024-03-24 19:44:05.86
1 row in set (0.00 sec)
# 3.
SELECT * FROM information_schema.triggers\G
# 太长了, 就不再复制了, 还有别的数据库的触发器
3.2 删除触发器
DROP TRIGGER salary_check_trigger;
SHOW CREATE TRIGGER salary_check_trigger\G
ERROR 1360 (HY000): Trigger does not exist
4. 触发器的优缺点
4.1 优点
- 可以保证数据的完整性
- 触发器可以帮助我们记录操作日志
- 触发器还可以用在操作数据前, 对数据进行合法性检查
4.2 缺点
- 触发器会导致可读性差, 容易找不到问题
- 相关数据的变更, 可能会导致触发器出错
4.3 注意点
如果再子表中定义了外键约束, 并且外键制定了 ON UPDATE/DELETE CASCADE/SET NULL 子句, 此时修改父表被引用的键值或者删除父表被引用的记录行时, 也会引起子表的修改和删除操作, 此时基于子表的UPDATE 和DELETE语句定义的出发其并不会被激活. 这是因为外键约束的级别高于触发器的级别.
18. MySQL8其他新特性
1. MySQL 8 新特性概述
1.1 MySQL 8.0 新增特性
- 更简便的 NoSQL 支持
- 更好的索引
- 更好的 JSON 支持
- 安全和账户管理
- InnoDB的变化
- 数据字典
- 原子数据定义语句
- 资源管理
- 字符集支持
- 优化器增强
- 共用表表达式
- 窗口函数
- 正则表达式支持
- 内部临时表
- 日志记录
- 备份锁
- 增强的MySQL复制
1.2 MySQL 8.0 移除的旧特性
- 查询缓存
- 语句
- 系统变量
- 状态变量
- 线程状态
- 加密相关的函数
- 空间函数相关
- \N和NULL
- mysql_install_db
- 通用分区处理程序
- 系统和状态变量信息
- mysql_plugin 工具
2. 新特性1: 窗口函数
2.1 使用窗口函数前后对比
2.2 窗口函数分类
- 静态窗口函数
- 动态窗口函数
2.3 语法结构
2.4 分类讲解
- 序号函数
- 分布函数
- 前后函数
- 首位函数
- 其他函数