MySQL基础

发表于 分类于 阅读次数：

SQL分类

SQL语言在功能上主要分为如下3大类：

  • DDL

    DDL（Data Definition Languages, 数据定义语言），这些语句定义了不同的数据库、表、视图、索
    引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。

    主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。

  • DML

    DML（Data Manipulation Language, 数据操作语言），用于添加、删除、更新和查询数据库记
    录，并检查数据完整性。

    主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。

  • DCL

    DCL（Data Control Language, 数据控制语言），用于定义数据库、表、字段、用户的访问权限和
    安全级别。
    主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

通用语法

基本规则

  • SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
  • 每条命令以 ; 或 \g 或 \G 结束
  • 关键字不能被缩写也不能分行
  • 关于标点符号
    • 必须保证所有的()、单引号''、双引号""是成对结束的
    • 必须使用英文状态下的半角输入方式
    • 字符串型和日期时间类型的数据可以使用单引号''表示
    • 列的别名，尽量使用双引号""，而且不建议省略as

SQL大小写规范

  • MySQL 在 Windows 环境下是大小写不敏感的
  • MySQL 在 Linux 环境下是大小写敏感的
    • 数据库名、表名、表的别名、变量名是严格区分大小写的
    • 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
  • 推荐采用统一的书写规范：
    • 数据库名、表名、表别名、字段名、字段别名等都小写
    • SQL 关键字、函数名、绑定变量等都大写

注释

可以使用如下格式的注释结构:

1
2
3
单行注释：#注释文字
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

运算符

算术运算符

算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算

运算符 名称 作用 示例
+ 加法运算符 计算两个值或表达式的和 SELECT a + b
- 减法运算符 计算两个值或表达式的差 SELECT a - b
* 乘法运算符 计算两个值或表达式的乘积 SELECT a * b
/ 或 DIV 除法运算符 计算两个值或表达式的商 SELECT a / b
% 或 MOD 取余(求模)运算符 计算两个值或表达式的余数 SELECT a % b

运算规则

  • 加/减法

    image-20220705102340555

    由此可以得知:

    1
    2
    3
    4
    - 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
    - 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
    - 在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。
    （补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）

  • 乘/除法

    image-20220705102550438

    由此可以得知:

    1
    2
    3
    4
    5
    6
    - 一个数乘以整数1和除以整数1后仍得原数；
    - 一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
    - 一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
    - 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
    - 乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
    - 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL

  • 取余

    image-20220705102749895

比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

运算符 名称 作用 示例
= 等于运算符 判断两个值、字符串或表达式是否相等 SELECT * FROM TABLE WHERE A = B
<=> 安全等于运算符 安全的判断两个值、字符串或表达式是否相等 SELECT * FROM TABLE WHERE A <=> B
<> 或 != 不等于运算符 判断两个值、字符串或表达式是否不相等 SELECT * FROM TABLE WHERE A <> B
< 小于运算符 判断前面的值、字符串或表达式是否大于后面的 SELECT * FROM TABLE WHERE A < B
<= 小于等于运算符 判断前面的值、字符串或表达式是否大于或等于后面的 SELECT * FROM TABLE WHERE A <= B
> 大于运算符 判断前面的值、字符串或表达式是否小于后面的 SELECT * FROM TABLE WHERE A > B
>= 大于等于运算符 判断前面的值、字符串或表达式是否小于或等于后面的 SELECT * FROM TABLE WHERE A >= B

  • 等号运算符

    等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。

    image-20220705103744522

    在使用等号运算符时，遵循如下规则：

    1
    2
    3
    4
    - 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
    - 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
    - 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
    - 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

  • 安全等于运算符

    安全等于运算符（<=>）与等于运算符（=）的作用是相似的， 唯一的区别 是<=>可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。

    image-20220705104054544

  • 不等于运算符

    不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。 SQL语句示例如下：

    image-20220705104218469

非符号类型的运算符

运算符 名称 作用 示例
IS NULL 为空运算符 判断值、字符串或表达式是否为空 SELECT B FROM TABLE WHERE A IS NULL
ISNOTNULL 不为空运算符 判断值、字符串或表达式是否不为空 SELECT B FROM TABLE WHERE A IS NOT NULL
LEAST 最小值运算符 在多个值中返回最小值 SELECT LEAST(A,B,C)
GREATEST 最大值运算符 在多个值中返回最大值 SELECT GREATEST(A,B,C)
BETWEEN AND 两个值之间算符 判断一个值是否在两个值之间 SELECT * FROM TABLE WHERE C BETWEEN A AND B
IN 属于运算符 判断一个值是否在列表中 SELECT * FROM TABLE WHERE C IN(A, B)
NOT IN 不属于运算符 判断一个值是否不在列表中 SELECT * FROM TABLE WHERE C NOT IN(A, B)
LIKE 模糊匹配运算符 判断一个值是否符合模糊匹配规则 SELECT * FROM TABLE WHERE C LIKE '__'
REGEXP 正则表达式运算符 判断一个值是否符合正则表达式的规则 SELECT * FROM TABLE WHERE C REGEXP B
RLIKE 正则表达式运算符 判断一个值是否符合正则表达式的规则 SELECT * FROM TABLE WHERE C RLIKE B

  • 空运算符

    空运算符（IS NULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。

    image-20220705110122730

  • 非空运算符

    非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。

    image-20220705110236772

  • 最小值运算符

    语法格式为：LEAST(值1，值2，...，值n)。其中，“值n”表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。

    image-20220705110338880

    1
    2
    3
    - 当参数是整数或者浮点数时，LEAST将返回其中最小的值
    - 当参数为字符串时，返回字母表中顺序最靠前的字符
    - 当比较值列表中有NULL时，不能判断大小，返回值为NULL

  • 最大值运算符

    语法格式为：GREATEST(值1，值2，...，值n)。其中，n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。

    image-20220705110506031

    1
    2
    3
    - 当参数中是整数或者浮点数时，GREATEST将返回其中最大的值
    - 当参数为字符串时，返回字母表中顺序最靠后的字符
    - 当比较值列表中有NULL时，不能判断大小，返回值为NULL

  • BETWEEN AND运算符

    BETWEEN AND运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B，此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。

    image-20220705110623796

  • IN运算符

    IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，则结果为NULL。

    image-20220705110751507

  • NOT IN运算符

NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0。

image-20220705110841206

  • LIKE运算符

    LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。

    1
    2
    3
    LIKE运算符通常使用如下通配符：
    	%：匹配0个或多个字符。
    	_：只能匹配一个字符。

    image-20220705110958835

  • REGEXP运算符

    REGEXP运算符用来匹配字符串，语法格式为： expr REGEXP 匹配条件 。如果expr满足匹配条件，返回1；如果不满足，则返回0。若expr或匹配条件任意一个为NULL，则结果为NULL。

    1
    2
    3
    4
    5
    6
    REGEXP运算符在进行匹配时，常用的有下面几种通配符：
    （1）^	匹配以该字符后面的字符开头的字符串。
    （2）$	匹配以该字符前面的字符结尾的字符串。
    （3）.	匹配任何一个单字符。
    （4）[...]匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
    （5）*	匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字,而“*”匹配任何数量的任何字符。

    image-20220705111506394

逻辑运算符

MySQL中支持4种逻辑运算符如下：

运算符 作用 示例
AND 或 && 逻辑与 SELECT A && B
OR 或 ` `
NOT 或 ! 逻辑非 SELECT NOT B
XOR 逻辑异或 SELECT A XOR B

  • 逻辑与运算符

    逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。

    image-20220705162417398

  • 逻辑或运算符

    逻辑或（OR或||）运算符是当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。

    image-20220705162525765

    OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合

  • 逻辑非运算符

    逻辑非（NOT或!）运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。

    image-20220705162636209

  • 逻辑异或运算符

    逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。

    image-20220705163817068

位运算符

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。

MySQL支持的位运算符如下:

运算符 作用 示例
& 按位与(位AND) SELECT A & B
` ` 按位或(位OR)
^ 按位异或(位XOR) SELECT A ^ B
~ 按位取反 SELECT ~ A
>> 按位右移 SELECT A >> 2
<< 按位左移 SELECT A << 2

  • 按位与运算符

    按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二进制位的数值都为1时，则该位返回1，否则返回0。

    image-20220705163245044

    1
    2
    - 1的二进制数为0001，10的二进制数为1010，所以1 & 10的结果为0000，对应的十进制数为0。
    - 20的二进制数为10100，30的二进制数为11110，所以20 & 30的结果为10100，对应的十进制数为20。

    image-20220705163924499

  • 按位或运算符

    按位或（|）运算符将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的二进制位的数值有一个或两个为1时，则该位返回1，否则返回0。

    image-20220705163519020

    1
    2
    - 1的二进制数为0001，10的二进制数为1010，所以1 | 10的结果为1011，对应的十进制数为11。
    - 20的二进制数为10100，30的二进制数为11110，所以20 | 30的结果为11110，对应的十进制数为30。

    image-20220705163951001

  • 按位异或运算符

    按位异或（^）运算符将给定的值对应的二进制数逐位进行逻辑异或运算。当给定值对应的二进制位的数值不同时，则该位返回1，否则返回0。

    image-20220705163731301

    1
    2
    - 1的二进制数为0001，10的二进制数为1010，所以1 ^ 10的结果为1011，对应的十进制数为11。
    - 20的二进制数为10100，30的二进制数为11110，所以20 ^ 30的结果为01010，对应的十进制数为10。

    image-20220705164003169

  • 按位取反运算符

    按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将1变为0，将0变为1。

    image-20220705165925877

    1
    由于按位取反（~）运算符的优先级高于按位与（&）运算符的优先级，所以10 & ~1中首先对数字1进行按位取反操作，结果除了最低位为0，其他位都为1，然后与10进行按位与操作，结果为10。

  • 按位右移运算符

    按位右移（>>）运算符将给定的值的二进制数的所有位右移指定的位数。右移指定的位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐。

    image-20220705170032268

    1
    2
    - 1的二进制数为0000 0001，右移2位为0000 0000，对应的十进制数为0。
    - 4的二进制数为0000 0100，右移2位为0000 0001，对应的十进制数为1。

  • 按位左移运算符

    按位左移（<<）运算符将给定的值的二进制数的所有位左移指定的位数。左移指定的位数后，左边高位的数值被移出并丢弃，右边低位空出的位置用0补齐。

    image-20220705170246024

    1
    2
    - 1的二进制数为0000 0001，左移两位为0000 0100，对应的十进制数为4。
    - 4的二进制数为0000 0100，左移两位为0001 0000，对应的十进制数为16。

空值参与运算

所有运算符或列值遇到 null 值，运算的结果都为null

在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。

而且，在 MySQL 里面，空值是占用空间的

运算符的优先级

优先级 运算符
1 :=, =
2 `
3 && , AND
4 NOT
5 BETWEEN AND, CASE, WHEN, THEN, ELSE
6 =(比较) , <=>, >=, <=, <, >, <>, !=, IS, LIKE, REGEXP, IN
7 `
8 &
9 <<, >>
10 -, +
11 *, / , %
12 ^(按位异或)
13 -(负号), ~(按位取反)
14 !
15 ()

数字编号越大，优先级越高，优先级高的运算符先进行计算。

可以看到，赋值运算符的优先级最低，使用()括起来的表达式的优先级最高。

DDL

Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。

数据库操作

  • 查询所有数据库

    1
    show databases;

  • 查询当前数据库

    1
    select database();

  • 创建数据库

    1
    create database [if not exists] 数据库名 [default charset 字符集 ] [collate 排序规则];

  • 删除数据库

    1
    drop database [ if exists ] 数据库名;

  • 切换数据库

    1
    use 数据库名;

使用示例

1
2
3
4
5
-- 创建一个itheima数据库，并且指定字符集
create database if not extists ni9nes_DB default charset utf8mb4;

-- 删除数据库
drop database if exists ni9nes_DB;

表操作

表操作-查询创建

  • 查询当前数据库所有表

    1
    show tables;

  • 查看指定表结构

1
desc 表名;

通过这条指令，我们可以查看到指定表的字段，字段的类型、是否可以为NULL，是否存在默认值等信
息。

  • 查询指定表的建表语句

    1
    show create table 表名;

    这条指令，主要是用来查看建表语句的，而有部分参数我们在创建表的时候，并未指定也会查询
    到，因为这部分是数据库的默认值，如：存储引擎、字符集等。

  • 创建表结构

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE 表名(
    字段1 字段1类型 [ COMMENT 字段1注释 ],
    字段2 字段2类型 [COMMENT 字段2注释 ],
    字段3 字段3类型 [COMMENT 字段3注释 ],
    ......
    字段n 字段n类型 [COMMENT 字段n注释 ]
    ) [ COMMENT 表注释 ] ;

    image-20220707161926712

表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

数值类型
类型 大小 有符号(SIGNED)范围 无符号(UNSIGNED)范围 描述
TINYINT 1byte (-128，127) (0，255) 小整数值
SMALLINT 2bytes (-32768，32767) (0，65535) 大整数值
MEDIUMINT 3bytes (-8388608，8388607) (0，16777215) 大整数值
INT/INTEGER 4bytes (-2147483648， 2147483647) (0，4294967295) 大整数值
BIGINT 8bytes (-2^63，2^63-1) (0，2^64-1) 极大整数值
FLOAT 4bytes (-3.402823466 E+38， 3.402823466351 E+38) 0 和 (1.175494351 E- 38，3.402823466 E+38) 单精度浮点数值
DOUBLE 8bytes (-1.7976931348623157 E+308， 1.7976931348623157 E+308) 0 和 (2.2250738585072014 E-308， 1.7976931348623157 E+308) 双精度浮点数值
DECIMAL 依赖于M(精度)和D(标度) 的值 依赖于M(精度)和D(标度)的值 小数 值(精 确定点数) 
1
2
3
4
5
如:
1). 年龄字段 -- 不会出现负数, 而且人的年龄不会太大
age tinyint unsigned
2). 分数 -- 总分100分, 最多出现一位小数
score double(4,1)
字符串类型
类型 大小 描述
CHAR 0-255 bytes 定长字符串(需要指定长度)
VARCHAR 0-65535 bytes 变长字符串(需要指定长度)
TINYBLOB 0-255 bytes 不超过255个字符的二进制数据
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

char 与 varchar 都可以描述字符串
char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关.
而varchar是变长字符串，指定的长度为最大占用长度.
相对来说，char的性能会更高些。

1
2
3
4
5
6
7
如：
1). 用户名 username ------> 长度不定, 最长不会超过50
username varchar(50)
2). 性别 gender ---------> 存储值, 不是男,就是女
gender char(1)
3). 手机号 phone --------> 固定长度为11
phone char(11)
日期时间类型
类型 大小 范围 格式 描述
DATE 3 1000-01-01 至 9999-12-31 YYYY-MM-DD 日期值
TIME 3 -838:59:59 至 838:59:59 HH:MM:SS 时间值或持续时间
YEAR 1 1901 至 2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 至 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:01 至 2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳 
1
2
3
4
5
如:
1). 生日字段 birthday
birthday date
2). 创建时间 createtime
createtime datetime

表操作-案例

1
2
3
4
5
6
7
8
设计一张员工信息表，要求如下：
1. 编号（纯数字）
2. 员工工号 (字符串类型，长度不超过10位)
3. 员工姓名（字符串类型，长度不超过10位）
4. 性别（男/女，存储一个汉字）
5. 年龄（正常人年龄，不可能存储负数）
6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
7. 入职时间（取值年月日即可）

对应的建表语句如下:

1
2
3
4
5
6
7
8
9
create table emp(
    id 			int 		comment '编号',
    workno 		varchar(10) comment '工号',
    name 		varchar(10) comment '姓名',
    gender 		char(1) 	comment '性别',
    age 		tinyint unsigned 	comment '年龄',
    idcard 		char(18) 	comment '身份证号',
    entrydate 	date 		comment '入职时间'
) comment '员工表';

image-20220707163016487

表操作-修改

  • 添加字段

    1
    ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ]; 
    1
    2
    -- 如 , 为emp表增加一个新的字段”昵称”为nickname，类型为varchar(20)
    ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

  • 修改数据类型

    1
    ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

  • 修改字段名和字段类型

    1
    ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
    1
    2
    -- 如, 将emp表的nickname字段修改为username，类型为varchar(30)
    ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

  • 删除字段

    1
    ALTER TABLE 表名 DROP 字段名; 
    1
    2
    -- 如, 将emp表的字段username删除
    ALTER TABLE emp DROP username;

  • 修改表名

    1
    ALTER TABLE 表名 RENAME TO 新表名; 
    1
    2
    -- 如, 将emp表的表名修改为 employee
    ALTER TABLE emp RENAME TO employee;

表操作-删除

  • 删除表

    1
    DROP TABLE [ IF EXISTS ] 表名;

    可选项 IF EXISTS 代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不加该参数项，删除一张不存在的表，执行将会报错)。

    1
    2
    -- 如, 如果tb_user表存在，则删除tb_user表
    DROP TABLE IF EXISTS tb_user;

  • 清空表数据

    1
    TRUNCATE TABLE 表名;

DML

Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

  • 添加数据（INSERT）
  • 修改数据（UPDATE）
  • 删除数据（DELETE）

添加数据

  • 给指定字段添加数据

    1
    INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...); 
    1
    2
    3
    -- 如, 给employee表所有的字段添加数据
    insert into emp(id,workno,name,gender,age,idcard,entrydate)
    values(1,'1','ni9ne','男',10,'123456789012345678','2000-01-01');

  • 给全部字段添加数据

    1
    INSERT INTO 表名 VALUES (值1, 值2, ...); 
    1
    2
    -- 如, 给employee表所有的字段添加数据
    insert into employee values(2,'2','usern','男',18,'123456789012345670','2005-01-01');

  • 批量添加数据

    1
    2
    3
    INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...);

    INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ; 
    1
    2
    3
    4
    -- 如, 批量插入数据到employee表
    insert into emp values
        (3,'3','user1','男',38,'123456789012345670','2005-01-01'),
        (4,'4','user2','女',18,'123456789012345670','2005-01-01');

    注意事项:
    • 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
    • 字符串和日期型数据应该包含在引号中。
    • 插入的数据大小，应该在字段的规定范围内。

修改数据

修改数据的具体语法为:

1
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ; 
1
2
-- 如, 修改id为1的数据，将name修改为 user_new
update emp set name = 'user_new' where id = 1;

注意事项:

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据

删除数据的具体语法为：

1
DELETE FROM 表名 [ WHERE 条件 ] ; 
1
2
-- 如, 删除gender为女的员工
delete from emp where gender = '女';

注意事项:

• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE 语句不能删除某一个字段的值 (可以使用UPDATE，将该字段值置为NULL即可)。

DQL

Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

基本语法

DQL 查询语句，语法结构如下：

1
2
3
4
5
6
7
SELECT 	字段列表
FROM 	表名列表
WHERE 	条件列表
GROUP BY 分组字段列表
HAVING 	分组后条件列表
ORDER BY 排序字段列表
LIMIT 	分页参数

基础查询

在基本查询的DQL语句中，不带任何的查询条件，查询的语法如下：

查询多个字段

1
2
3
-- 语法:
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ; 
SELECT * FROM 表名 ;

注意 : * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。

字段设置别名

1
2
3
-- 语法:
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名; 
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

在列名之后加入关键字AS, 代表给该字段起别名。

别名可以使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。

关键字 AS 可以省略

去除重复记录

默认情况下，查询会返回全部行，包括重复行。在SELECT语句中使用关键字 DISTINCT 去除重复行

1
SELECT DISTINCT 字段列表 FROM 表名;

有两点需要注意：

  • DISTINCT 需要放到所有列名的前面，如果写成 以下方式会报错。

    1
    SELECT salary, DISTINCT department_id FROM employees

  • DISTINCT 其实是对后面所有列名的组合进行去重

条件查询

1
2
-- 语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表 ; 
1
2
3
4
5
6
-- 如, 查询年龄等于 88 的员工
select * from emp where age = 88; 
-- 查询有身份证号的员工信息
select * from emp where idcard is not null; 
-- 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from emp where age between 15 and 20;

聚合函数

将一列数据作为一个整体，进行纵向计算 。

常见的聚合函数:

函数 功能
count 统计数量
max 大值
min 小值
avg 平均值
sum 求和 
1
2
-- 语法:
SELECT 聚合函数(字段列表) FROM 表名 ;

注意 : NULL值是不参与所有聚合函数运算的。

1
2
3
4
5
6
7
-- 如, 统计该企业员工数量
select count(*) from emp; -- 统计的是总记录数
select count(idcard) from emp; -- 统计的是idcard字段不为null的记录数
-- 统计该企业员工的平均年龄
select avg(age) from emp; 
-- 统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = '西安';

分组查询

1
2
-- 语法
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
1
2
3
4
5
6
7
8
9
-- 如, 根据性别分组, 统计男性员工和女性员工的数量
select gender, count(*) from emp group by gender ; 
-- 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp 
where age < 45 
group by workaddress 
having address_count >= 3;
-- 统计各个工作地址上班的男性及女性员工的数量
select workaddress, gender, count(*) '数量' from emp group by gender, workaddress;

注意事项:

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
• 执行顺序: where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB

where与having区别

  • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
  • 判断条件不同：where不能对聚合函数进行判断，而having可以

WITH ROLLUP 使用

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所
有记录的总和，即统计记录数量。

1
2
-- 根据性别分组 , 统计男性员工、女性员工的平均年龄, 及全部员工的平均年龄
select gender, avg(age) from emp group by gender with rollup;

image-20220707171949006

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的

排序查询

1
2
-- 语法:
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

排序方式

  • ASC : 升序(默认值)
  • DESC: 降序
1
2
3
4
-- 如, 根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
-- 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from emp order by age asc , entrydate desc;

注意:

可以使用不在SELECT列表中的列排序。

在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

分页查询

MySQL中使用 LIMIT 实现分页, 格式:

1
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

注意事项:

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

1
2
3
4
-- 如, 查询第1页员工数据, 每页展示10条记录
select * from emp limit 0,10;
-- 查询第2页员工数据, 每页展示10条记录    (页码-1)*页展示记录数
select * from emp limit 10,10;

执行顺序

image-20220707173247097

比如一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的:

1
2
3
4
5
6
7
SELECT DISTINCT player_id, player_name, count(*) as num 	# 顺序 5
FROM player JOIN team ON player.team_id = team.team_id 		# 顺序 1
WHERE height > 1.80 			# 顺序 2
GROUP BY player.team_id 		# 顺序 3
HAVING num > 2 					# 顺序 4
ORDER BY num DESC 				# 顺序 6
LIMIT 2 						# 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步
骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

执行原理

  1. SELECT语句是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

    1
    2
    3
    4
    5
    1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
    2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
    3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

    如果操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是原始数据。

  2. 在虚拟表 vt1基础上再进行 WHERE 阶段 。在这个阶段中，会根据 vt1表的结果进行筛选过滤，得到虚拟表 vt2 。

  3. 然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。

  4. 进入到 SELECT 和 DISTINCT阶段 。首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1 和 vt5-2 。

  5. 当提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到虚拟表 vt6。

  6. 最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段 ，得到最终的结果，对应的是虚拟表vt7 。

在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略

DCL

Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

管理用户

  • 查询用户

    1
    select * from mysql.user;

    image-20220707174342109

    其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的。

    User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。

  • 创建用户

    1
    CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

  • 修改用户密码

    1
    ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

  • 删除用户

    1
    DROP USER '用户名'@'主机名' ;

注意事项:

• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。

• 主机名可以使用 % 通配，代表所有。

权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

权限 说明
ALL, ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表

上述只是简单罗列了常见的几种权限描述，其他权限描述及含义，可以直接参考官方文档。

1
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

  • 查询权限

    1
    SHOW GRANTS FOR '用户名'@'主机名' ;

  • 授予权限

    1
    GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

  • 撤销权限

    1
    REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项：

• 多个权限之间，使用逗号分隔

• 授权时， 数据库名和表名可以使用 * 进行通配，代表所有。

函数

MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。

MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。

数值函数

基本函数

函数 用法
ABS(x) 返回x的绝对值
SIGN(X) 返回X的符号。正数返回1，负数返回-1，0返回0
PI() 返回圆周率的值
CEIL(x)，CEILING(x) 返回大于或等于某个值的最小整数
FLOOR(x) 返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…) 返回列表中的最小值
GREATEST(e1,e2,e3…) 返回列表中的最大值
MOD(x,y) 返回X除以Y后的余数
RAND() 返回0~1的随机值
RAND(x) 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随数
ROUND(x) 返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y) 返回一个对x的值进行四舍五入后 接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y) 返回数字x截断为y位小数的结果
SQRT(x) 返回x的平方根。当X的值为负数时，返回NULL
RADIANS(x) 将角度转化为弧度，其中，参数x为角度值
DEGREES(x) 将弧度转化为角度，其中，参数x为弧度值

使用范例 

1
2
SELECT 
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),FLOOR(-43.23),MOD(12,5);

image-20220706195815666

1
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1);

image-20220706195909263

随机数rand()函数, 获取0~1之间的随机数值.

支持传入参数, 代表随机数因子, 当传入相同因子时, 同一次获取到的随机数是相同的

1
SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)

image-20220706200555076

1
SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90));

image-20220706201057191

三角函数

函数 用法
SIN(x) 返回x的正弦值，其中，参数x为弧度值
ASIN(x) 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(x) 返回x的余弦值，其中，参数x为弧度值
ACOS(x) 返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x) 返回x的正切值，其中，参数x为弧度值
ATAN(x) 返回x的反正切值，即返回正切值为x的值
ATAN2(m,n) 返回两个参数的反正切值
COT(x) 返回x的余切值，其中，X为弧度值 
1
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1));

image-20220707123537722

指数对数函数

函数 用法
POW(x,y)，POWER(X,Y) 返回x的y次方
EXP(X) 返回e的X次方，其中e是一个常数，2.718281828459045
LN(X)，LOG(X) 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG10(X) 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG2(X) 返回以2为底的X的对数，当X <= 0 时，返回NULL 
1
SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4);

image-20220707123757518

进制转换函数

函数 用法
BIN(x) 返回x的二进制编码
HEX(x) 返回x的十六进制编码
OCT(x) 返回x的八进制编码
CONV(x,f1,f2) 返回f1进制数变成f2进制数 
1
SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)

image-20220707123940222

字符串函数

函数 用法
ASCII(S) 返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s) 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s) 返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,......,sn) 连接s1,s2,……,sn为一个字符串
CONCAT_WS(x, s1,s2,......,sn) 同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x
INSERT(str, idx, len,replacestr) 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b) 用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s) 将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s) 将字符串s的所有字母转成小写字母
LEFT(str,n) 返回字符串str左边的n个字符
RIGHT(str,n) 返回字符串str右边的n个字符
LPAD(str, len, pad) 用字符串pad对str左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad) 用字符串pad对str右边进行填充，直到str的长度为len个字符
LTRIM(s) 去掉字符串s左侧的空格
RTRIM(s) 去掉字符串s右侧的空格
TRIM(s) 去掉字符串s开始与结尾的空格
TRIM(s1 FROM s) 去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s) 去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s) 去掉字符串s结尾处的s1
REPEAT(str, n) 返回str重复n次的结果
SPACE(n) 返回n个空格
STRCMP(s1,s2) 比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len) 返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同
LOCATE(substr,str) 返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn) 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn) 返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2) 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s) 返回s反转后的字符串
NULLIF(value1,value2) 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回 value1

注意：MySQL中，字符串的位置是从1开始的。

日期和时间函数

获取日期、时间

函数 用法
CURDATE() ，CURRENT_DATE() 返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME() 返回当前时间，只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() /
LOCALTIME() / LOCALTIMESTAMP() 		返回当前系统日期和时间
UTC_DATE() 返回UTC（世界标准时间）日期
UTC_TIME() 返回UTC（世界标准时间）时间 
1
SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME();

image-20220707125419937

日期与时间戳的转换

函数 用法
UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间。
UNIX_TIMESTAMP(date) 将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp) 将UNIX时间戳的时间转换为普通格式的时间 
1
SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP('2011-11-11 11:11:11'), FROM_UNIXTIME(1576380910);

image-20220707130551116

获取月份、星期、星期数、天数等函数

函数 用法
YEAR(date) / MONTH(date) / DAY(date) 返回具体的日期值
HOUR(time)/MINUTE(time)/ SECOND(time) 返回具体的时间值
MONTHNAME(date) 返回月份：January，…
DAYNAME(date) 返回星期几：MONDAY，TUESDAY…..SUNDAY
WEEKDAY(date) 返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date) 返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date) 返回一年中的第几周
DAYOFYEAR(date) 返回日期是一年中的第几天
DAYOFMONTH(date) 返回日期位于所在月份的第几天
DAYOFWEEK(date) 返回周几，注意：周日是1，周一是2，。。。周六是 7 
1
2
3
SELECT 
YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE());

image-20220707130851404

1
2
3
SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW());

image-20220707131104475

日期的操作函数

函数 用法
EXTRACT(type FROM date) 返回指定日期中特定的部分，type指定返回的值

函数中type的取值与含义：

image-20220707131225431

image-20220707131231606

1
2
SELECT NOW(),EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW());

image-20220707131353893

时间和秒钟转换的函数

函数 用法
TIME_TO_SEC(time) 将 time 转化为秒并返回结果值。转化的公式为：小时x3600+分钟x60+秒
SEC_TO_TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间 
1
SELECT TIME_TO_SEC('05:16:42'), SEC_TO_TIME('19002');

image-20220707131824120

计算日期和时间的函数

  • date类型

    函数 用法
    DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) 返回与给定日期时间相差INTERVAL时间段的日期时间
    DATE_SUB(date,INTERVAL expr type)， SUBDATE(date,INTERVAL expr type) 返回与date相差INTERVAL时间间隔的日期

    type的取值：

    image-20220707133114760

    1
    2
    3
    4
    5
    6
    7
    SELECT 
    DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,
    DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
    ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
    DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
    DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
    DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号

    image-20220707133309139

    1
    2
    3
    4
    SELECT 
    DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
    SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
    DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3

    image-20220707133349081

  • time类型

    函数 用法
    ADDTIME(time1,time2) 返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
    SUBTIME(time1,time2) 返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
    DATEDIFF(date1,date2) 返回date1 - date2的日期间隔天数
    TIMEDIFF(time1, time2) 返回time1 - time2的时间间隔
    FROM_DAYS(N) 返回从0000年1月1日起，N天以后的日期
    TO_DAYS(date) 返回日期date距离0000年1月1日的天数
    LAST_DAY(date) 返回date所在月份的最后一天的日期
    MAKEDATE(year,n) 针对给定年份与所在年份中的天数返回一个日期
    MAKETIME(hour,minute,second) 将给定的小时、分钟和秒组合成时间并返回
    PERIOD_ADD(time,n) 返回time加上n后的时间 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT
    NOW(),					# 2022-07-07 05:41:32
    ADDTIME(NOW(),20),		# 2022-07-07 05:41:52
    SUBTIME(NOW(),30),		# 2022-07-07 05:41:02
    SUBTIME(NOW(),'1:1:3'),	# 2022-07-07 04:40:29
    DATEDIFF(NOW(),'2022-5-01'),	# 67
    TIMEDIFF(NOW(),'2022-5-25 22:10:10'),	# 838:59:59
    FROM_DAYS(366),			# 0001-01-01
    TO_DAYS('0000-12-25'),	# 359
    LAST_DAY(NOW()),		# 2022-07-31
    MAKEDATE(YEAR(NOW()),12),		# 2022-01-12
    MAKETIME(10,21,23),		# 10:21:23
    PERIOD_ADD(20200101010101, 10)	# 13754718911

日期的格式化与解析

函数 用法
DATE_FORMAT(date,fmt) 按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt) 按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type) 返回日期字符串的显示格式
STR_TO_DATE(str, fmt) 按照字符串fmt对str进行解析，解析为一个日期

  • fmt参数常用的格式符：

    格式符 说明 格式符 说明
    %Y 4位数字表示年份 %y 表示两位数字表示年份
    %M 月名表示月份（January,….） %m 两位数字表示月份 （01,02,03。。。）
    %b 缩写的月名（Jan.，Feb.，….） %c 数字表示月份（1,2,3,…）
    %D 英文后缀表示月中的天数 （1st,2nd,3rd,…） %d 两位数字表示月中的天数(01,02…)
    %e 数字形式表示月中的天数 （1,2,3,4,5…..）
    %H 两位数字表示小数，24小时制 （01,02..） %h 和%I 两位数字表示小时，12小时制 （01,02..）
    %k 数字形式的小时，24小时制(1,2,3) %l 数字形式表示小时，12小时制 （1,2,3,4….）
    %i 两位数字表示分钟（00,01,02） %S 和%s 两位数字表示秒(00,01,02…)
    %W 一周中的星期名称（Sunday…） %a 一周中的星期缩写（Sun.， Mon.,Tues.，..）
    %w 以数字表示周中的天数 (0=Sunday,1=Monday….)
    %j 以3位数字表示年中的天数(001,002…) %U 以数字表示年中的第几周， （1,2,3。。）其中Sunday为周中第一天
    %u 以数字表示年中的第几周， （1,2,3。。）其中Monday为周中第一天
    %T 24小时制 %r 12小时制
    %p AM或PM %% 表示%

  • GET_FORMAT函数中date_type和format_type参数取值如下：

    image-20220707135101922

1
2
3
4
5
SELECT 
NOW(),
DATE_FORMAT(NOW(), '%H:%i:%s'), 
STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s'),
GET_FORMAT(DATE, 'USA');

image-20220707135301222

流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。

函数 用法
IF(value,value1,value2) 如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2) 如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END 相当于if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END 相当于switch…case…

加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。

函数 用法
PASSWORD(str) 返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密
MD5(str) 返回字符串str的md5加密后的值，也是一种加密方式。若参数为 NULL，则会返回NULL
SHA(str) 从原明文密码str计算并返回加密后的密码字符串，当参数为 NULL时，返回NULL。SHA加密算法比MD5更加安全。
ENCODE(value,password_seed) 返回使用password_seed作为加密密码加密value
DECODE(value,password_seed) 返回使用password_seed作为加密密码解密value 
1
2
3
4
SELECT 
PASSWORD('mysql'), PASSWORD(NULL), 
md5('123'), SHA('123'), 
ENCODE('mysql', 'mysql'), DECODE(ENCODE('mysql','mysql'),'mysql')

image-20220707135922748

MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

函数 用法
VERSION() 返回当前MySQL的版本号
CONNECTION_ID() 返回当前MySQL服务器的连接数
DATABASE()，SCHEMA() 返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、
SYSTEM_USER()，SESSION_USER()		 返回当前连接MySQL的用户名，返回结果格式为 “主机名@用户名”
CHARSET(value) 返回字符串value自变量的字符集
COLLATION(value) 返回字符串value的比较规则 
1
SELECT DATABASE(),SCHEMA(),VERSION(),CONNECTION_ID(), USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER(),CHARSET('ABC'),COLLATION('ABC')

image-20220707140254594

其他函数

MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。

函数 用法
FORMAT(value,n) 返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位
CONV(value,from,to) 将value的值进行不同进制之间的转换
INET_ATON(ipvalue) 将以点分隔的IP地址转化为一个数字
INET_NTOA(value) 将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr) 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code) 将value所使用的字符编码修改为char_code 
1
SELECT INET_ATON('192.168.1.100'), INET_NTOA(3232235876), BENCHMARK(1000000, MD5('mysql'))

image-20220707140632895

约束

概述

约束是表级的强制规定。可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

分类:

约束 描述 关键字
非空约束 限制该字段的数据不能为null NOT NULL
唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE
主键约束 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY
默认约束 保存数据时，如果未指定该字段的值，则采用默认值 DEFAULT
检查约束(8.0.16版本后) 保证字段值满足某一个条件 CHECK
外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 FOREIGN KEY

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

非空约束

NOT NULL , 限定某个字段/某列的值不允许为空

1
2
3
4
5
特点
- 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
- 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
- 一个表可以有很多列都分别限定了非空
- 空字符串''不等于NULL，0也不等于NULL
1
2
3
4
5
6
7
8
9
10
11
-- 建表添加非空约束
CREATE TABLE 表名称(
 ...
 字段名 数据类型 NOT NULL, 
 ...
);
-- 添加非空约束
alter table 表名称 modify 字段名 数据类型 not null;
-- 删除非空约束
alter table 表名称 modify 字段名 数据类型 NULL;#去掉not null，相当于修改字段，该字段允许为空
alter table 表名称 modify 字段名 数据类型;#去掉not null，相当于修改某个非注解字段，该字段允许为空

唯一性约束

UNIQUE , 用来限制某个字段/某列的值不能重复

1
2
3
4
5
6
特点
- 同一个表可以有多个唯一约束。
- 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
- 唯一性约束允许列值为空。
- 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
- MySQL会给唯一约束的列上默认创建一个唯一索引。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 建表时添加唯一约束
create table 表名称(
 ...
 字段名 数据类型  unique, 
 字段名 数据类型  unique key,
 ...
);
create table 表名称(
 ...
 字段名 数据类型, 
 ... 
 [constraint 约束名] unique(字段名)
);

-- 添加唯一约束
-- 字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
alter table 表名称 add unique key(字段列表);	# 表级约束
alter table 表名称 modify 字段名 字段类型 unique;  # 行级约束

-- 删除唯一约束
-- 删除唯一约束只能通过删除唯一索引的方式删除。
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; #查看都有哪些约束
ALTER TABLE 表名称 DROP INDEX 唯一索引名;

注意：可以通过 show index from 表名称; 查看表的索引

主键约束

PRIMARY KEY, 用来唯一标识表中的一行记录。

1
2
3
4
5
6
7
8
特点:
- 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。
- 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
- 主键约束对应着表中的一列或者多列（复合主键）
- 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
- MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
- 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
- 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 建表时指定主键约束
create table 表名称(
 字段名 数据类型  primary key, #列级模式
 字段名 数据类型, 
 字段名 数据类型 
);
create table 表名称(
 字段名 数据类型,
 字段名 数据类型, 
 字段名 数据类型,
 [constraint 约束名] primary key(字段名) #表级模式
);

-- 建表后增加主键约束
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

-- 删除主键约束
-- 说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。
alter table 表名称 drop primary key;

自增列

AUTO_INCREMENT , 某个字段的值自增

1
2
3
4
5
6
特点:
- 一个表最多只能有一个自增长列
- 当需要产生唯一标识符或顺序值时，可设置自增长
- 自增长列约束的列必须是键列（主键列，唯一键列）
- 自增约束的列的数据类型必须是整数类型
- 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 建表时指定自增约束
create table 表名称(
 字段名 数据类型  primary key auto_increment,
 字段名 数据类型,
 字段名 数据类型,
);
create table 表名称(
 字段名 数据类型,
 字段名 数据类型 unique key auto_increment, 
 字段名 数据类型,
);

-- 建表后增加自增约束
alter table 表名称 modify 字段名 数据类型 auto_increment;

-- 删除自增约束
alter table 表名称 modify 字段名 数据类型;  	#去掉auto_increment相当于删除

外键约束

FOREIGN KEY, 用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

主表和从表/父表和子表:

  • 主表（父表）：被引用的表，被参考的表
  • 从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。
例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是
主表，选课表是从表。

1
2
3
4
5
6
7
8
9
10
11
12
特点:
- 从表的外键列，必须引用/参考主表的主键或唯一约束的列
	为什么？因为被依赖/被参考的值必须是唯一的
- 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1），也可以指定外键约束名。
- 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
- 删表时，先删从表（或先删除外键约束），再删除主表
- 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
- 在“从表”中指定外键约束，并且一个表可以建立多个外键约束
- 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。
	例如：都是表示部门编号，都是int类型。
- 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）
- 删除外键约束后，必须手动删除对应的索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 建表时指定外键约束
create table 主表名称(
 字段1 数据类型  primary key,
 字段2 数据类型
);
create table 从表名称(
 字段1 数据类型,
 字段2 数据类型,
 [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);

-- 建表后增加外键约束
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

-- 删除外键约束(必须手动删除对应的索引)
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;	# 删除外键约束
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;	# 删除外键约束对应索引

总结：约束关系是针对双方的

  • 添加了外键约束后，主表的修改和删除数据受约束
  • 添加了外键约束后，从表的添加和修改数据受约束
  • 在从表上建立外键，要求主表必须存在
  • 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

外键约束等级

添加外键约束时, 提供可选参数 [on update 约束等级][on delete 约束等级];, 约束等级有以下几种:

  • Cascade方式 ：

    在父表上update/delete记录时，同步update/delete掉子表的匹配记录

  • Set null方式 ：

    在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null

  • No action方式 ：

    如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作

  • Restrict方式 ：

    同no action， 都是立即检查外键约束

  • Set default方式 :

    父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式。

1
2
3
4
5
6
7
8
9
10
11
create table dept(
 did int primary key, 	#部门编号
 dname varchar(50) 		#部门名称
);
create table emp(
 eid int primary key,  	#员工编号
 ename varchar(5),   	#员工姓名
 deptid int, 			#员工所在的部门
 foreign key (deptid) references dept(did)  on update cascade on delete set null
 #把修改操作设置为级联修改等级，把删除操作设置为set null等级
);

默认值约束

DEFAULT , 给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

1
2
3
4
5
6
7
8
9
10
11
12
-- 建表时给字段加默认值
create table 表名称(
 字段名 数据类型  not null default 默认值,
);
# 说明：默认值约束一般不在唯一键和主键列上加

-- 建表后给字段加默认值
alter table 表名称 modify 字段名 数据类型 default 默认值;

-- 删除默认值约束
alter table 表名称 modify 字段名 数据类型 ;			#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型  not null; 	#删除默认值约束，保留非空约束

约束使用

1
2
3
4
5
6
7
案例需求： 根据需求，完成表结构的创建。需求如下
字段名		字段含义	字段类型			约束条件			约束关键字
id		ID唯一标识		int			主键，并且自动增长		  PRIMARY KEY,AUTO_INCREMENT
name	姓名			varchar(10)	  不为空，并且唯一			NOT NULL , UNIQUE
age		年龄			int	
status	状态			char(1)		  如果没有指定该值，默认为1	 DEFAULT
gender	性别			char(1)		        
1
2
3
4
5
6
7
8
-- 建表语句
CREATE TABLE tb_user(
	id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
	name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
	age int  COMMENT '年龄' ,
	status char(1) default '1' COMMENT '状态',
	gender char(1) COMMENT '性别'
);

查看某个表已有的约束:

1
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

image-20220707180448064

多表查询

等值连接/非等值连接

  • 等值连接

    关联条件为相等关系的多表查询

    1
    2
    3
    4
    -- 查询员工信息及部门信息
    SELECT emp.employee_id,dept.department_name,emp.department_id
    FROM employees emp,departments dept
    WHERE emp.`department_id` = dept.department_id;

  • 非等值连接

    关联条件为其他关系的多表查询

    1
    2
    3
    4
    -- 查询员工的工资等级
    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`;

自连接

关联条件为同一个表的不同字段的多表查询

1
2
3
4
-- 查询员工id,员工姓名及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

内连接/外连接

  • 内连接

    多表查询结果集中不包含一个表与另一个表不匹配的行

    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- 查询员工信息及部门信息(隐式内连接)
    SELECT employee_id,department_name
    FROM employees e,departments d
    WHERE e.`department_id` = d.department_id;

    -- 或使用INNER JOIN 实现同样的需求(显示内连接)
    SELECT employee_id,department_name
    FROM employees e INNER JOIN departments d
    ON e.`department_id` = d.department_id;

  • 外连接

    多表查询结果集中除了包含一个表与另一个表匹配的行之外的行数据, 包含左外连接和右外连接

    • 左外连接

      两个表在连接过程中除了返回满足连接条件的行以外, 还返回左表中不满足条件的行

      1
      2
      3
      4
      -- 查询 所有 的员工的last_name,department_name信息 
      SELECT employee_id,department_name
      FROM employees e LEFT JOIN departments d
      ON e.`department_id` = d.department_id;

      image-20220706175720567

      左表与右表不匹配, 默认赋值为Null

    • 右外连接

      两个表在连接过程中除了返回满足连接条件的行以外, 还返回右表中不满足条件的行

      1
      2
      3
      4
      -- 查询 所有 的部门的last_name,department_name信息 
      SELECT last_name,department_name
      FROM employees e RIGHT OUTER JOIN departments d
      ON e.`department_id` = d.`department_id`;

      image-20220706175904303

      某些部门没有员工, 即右表与左表不匹配, 默认赋值为Null

    • 满外连接

      满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
      SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
      需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

      1
      2
      3
      4
      5
      6
      7
      8
      SELECT employee_id,department_name
      FROM employees e LEFT JOIN departments d
      ON e.`department_id` = d.`department_id`
      UNION ALL
      SELECT employee_id,department_name
      FROM employees e RIGHT JOIN departments d
      ON e.`department_id` = d.`department_id`
      WHERE e.`department_id` IS NULL;

UNION使用

UNION 用于合并查询结果.

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

1
2
3
4
-- 语法格式
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

  • UNION

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

    image-20220706180626678

  • UNION ALL

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

    image-20220706180704144

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

1
2
3
4
-- 查询中国用户中男性的信息以及美国用户中年男性的用户信息
SELECT id,cname FROM t_cnmale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

7种 JOINS的实现

image-20220706181002857

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。

1
2
3
4
5
6
7
8
-- 下面两句SQL执行结果是一样的
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

USING

当多表进行连接的时候，还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。比如：

1
2
3
4
5
6
7
8
-- 下面两句SQL执行结果是一样的
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

多表连接注意

要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

1
2
3
【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保
证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。

多表查询示例

image-20220706190420600

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- 1.显示所有员工的姓名，部门号和部门名称。
SELECT CONCAT(e.first_name, '.', e.last_name) ename, d.department_id, d.department_name
FROM employees e 
LEFT JOIN departments d USING(department_id)

-- 2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.job_id,d.location_id
FROM employees e 
RIGHT JOIN departments d USING(department_id) 
WHERE d.department_id = 90;

-- 3.查询所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name,d.department_name,l.location_id,l.city
FROM employees e 
LEFT JOIN departments d USING(department_id) 
LEFT JOIN locations l USING (location_id)
WHERE e.commission_pct IS NOT NULL;

-- 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT e.last_name,e.job_id,d.department_name,d.department_name
FROM employees e LEFT JOIN departments d USING(department_id) 
LEFT JOIN locations l USING (location_id)
WHERE l.city = 'Toronto';

-- 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
-- employees 	Emp# 		manager 		Mgr#
-- kochhar 		101 		king 				100
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;

-- 7.查询哪些部门没有员工
SELECT e.employee_id,d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d USING(department_id) 
WHERE e.employee_id IS NULL;

-- 8. 查询哪个城市没有部门
SELECT l.city, d.department_id
FROM locations l
LEFT JOIN departments d USING(location_id)
WHERE d.department_id IS NULL;

子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

1
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

子查询分类

根据子查询结果不同，分为：

  • 标量子查询 (子查询结果为单个值）

    子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

    常用的操作符：= <> > >= < <=

    1
    2
    3
    4
    5
    -- 查询 "销售部" 的所有员工信息
    select * from emp where dept_id = (select id from dept where name = '销售部');

    -- 查询在 "Hunold" 入职之后的员工信息
    select * from emp where entrydate > (select entrydate from emp where name = 'Hunold');

  • 列子查询 (子查询结果为一列)

    子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

    常用操作符:

    操作符 描述
    IN 在指定的集合范围之内，多选一
    NOT IN 不在指定的集合范围之内
    ANY 子查询返回列表中，有任意一个满足即可
    SOME 与ANY等同，使用SOME的地方都可以使用ANY
    ALL 子查询返回列表的所有值都必须满足 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- 查询 "销售部" 和 "市场部" 的所有员工信息
    select * from emp where dept_id 
    in (select id from dept where name = '销售部' or name = '市场部');

    -- 查询比 "财务部" 所有人工资都高的员工信息
    select * from emp where salary 
    > all ( 
        select salary from emp where dept_id =(select id from dept where name = '财务部') 
    );

  • 行子查询 (子查询结果为一行)

    子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

    常用的操作符：= 、<> 、IN 、NOT IN

    1
    2
    -- 查询与 "Hunold" 的薪资及直属领导相同的员工信息 ;
    select * from emp where (salary,managerid) = (select salary, managerid from emp where name = 'Hunold') and name != 'Hunold';

  • 表子查询 (子查询结果为多行多列)

    子查询返回的结果是多行多列，这种子查询称为表子查询。

    常用的操作符：IN 

    1
    2
    3
    4
    5
    -- 查询与 "Lex" , "David" 的职位和薪资相同的员工信息
    select * from emp where (job,salary) in ( select job, salary from emp where name ='Lex' or name = 'David' );

    -- 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
    select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

按内查询是否被执行多次，将子查询划分为 相关(或关联)子查询 和 不相关(或非关联)子查询 。

  • 关联子查询

    如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

  • 非关联子查询

    子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

-- 方式一：相关子查询
SELECT e.last_name,e.salary,e.department_id FROM employees e 
WHERE e.salary > 
	(SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

-- 方式二：在 FROM 中使用子查询
SELECT e1.last_name,e1.salary,e1.department_id 
FROM employees e1,
	(SELECT department_id,AVG(salary) dept_avg_sal 
    FROM employees 
    GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id AND e2.dept_avg_sal < e1.`salary`;

EXISTS 与 NOT EXISTS关键字

  • 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
  • 如果在子查询中不存在满足条件的行：
    • 条件返回 FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行：
    • 不在子查询中继续查找
    • 条件返回 TRUE
  • NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。
1
2
3
4
5
6
7
8
9
-- 查询公司管理者的employee_id，last_name，job_id，department_id信息
SELECT e1employee_id, e1last_name, e1job_id, e1department_id
FROM  employees e1
WHERE EXISTS ( SELECT * FROM  employees e2 WHERE e2.manager_id = e1.employee_id);

-- 查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X' FROM  employees WHERE department_id = d.department_id);

相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据。

1
2
UPDATE table1 alias1
SET column = (SELECT expression FROM  table2 alias2 WHERE alias1.column = alias2.column);
1
2
3
4
-- 在employees中增加一个department_name字段，数据为员工对应的部门名称
UPDATE employees e
SET department_name = 
(SELECT department_name FROM departments d WHERE e.department_id = d.department_id);

相关删除

使用相关子查询依据一个表中的数据删除另一个表的数据。

1
2
3
DELETE FROM table1 alias1
WHERE column operator 
(SELECT expression FROM  table2 alias2 WHERE alias1.column = alias2.column);
1
2
3
-- 删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE e.employee_id in (SELECT employee_id FROM emp_history);

事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务操作

  • 控制事务一

    查看/设置事务提交方式

    1
    2
    3
    4
    -- 查询当前事务提交方式
    SELECT @@autocommit;
    -- 设置为手动提交
    SET @@autocommit = 0;

    提交事务

    1
    COMMIT;

    回滚事务

    1
    ROLLBACK;

    注意：上述的这种方式是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时执行的DML语句都不会提交, 需要手动的执行commit进行提交。

  • 控制事务二

    开启事务

    1
    2
    3
    START TRANSACTION;
    -- 或 
    BEGIN;

    提交事务

    1
    COMMIT;

    回滚事务

    1
    ROLLBACK;

    示例: 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    -- 开启事务
    start transaction

    -- 1. 查询张三余额
    select * from account where name = '张三';

    -- 2. 张三的余额减少1000
    update account set money = money - 1000 where name = '张三';

    -- 3. 李四的余额增加1000
    update account set money = money + 1000 where name = '李四';

    -- 如果正常执行完毕, 则提交事务
    commit;

    -- 如果执行过程中报错, 则回滚事务
    -- rollback;

事务四大特性

事务的四大特性，简称ACID。

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

image-20220714142522324

脏读

一个事务读到另外一个事务还没有提交的数据。

image-20220714143052165

不可重复读

一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

幻读

一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

image-20220714143140146

事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

隔离级别 脏读 不可重复读 幻读
未提交度 Read Uncommitted √ √ √
已提交读 Read Committed × √ √
可重复读 Repeatable Read(默认) × × √
序列化 Serializable × × ×

查看事务隔离级别

1
SELECT @@transaction_isolation;

设置事务隔离级别

1
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事务隔离级别越高，数据越安全，但是性能越低。