MySQL8从入门到精通学习笔记

MySQL8从入门到精通

MySQL是一个小型关系型数据库管理系统相比Oralce、DB2、SQL Server等，规模小、功能有限，但是它体积小、速度快、成本低，并且提供的功能对稍微复杂的应用来说已经够用。

1.数据库操作

1.1 创建数据库

-- CREATE DATABASE 数据库名
CREATE DATABASE IF NOT EXISTS test_ddpl /*让数据库能够插入中文数据，在创建时指定字符集和排序规则使用UTF-8字符集*/
DEFAULT CHARACTER 
	SET utf8 /* 使用UTF-8字符集 */
	COLLATE utf8_general_ci;
/* 使用utf-8_general_ci排序规则*/
/*CREATE DATABASE IF NOT EXISTS test_ddpl
-- 使用GBK字符集
DEFAULT CHARACTER SET gbk
COLLATE gbk_chinese_ci;*/

1.2 查看数据库

SHOW DATABASES;

1.3 删除数据库

-- DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS test_ddpl;

1.4 选择数据库

USE test_ddpl;

2.数据表操作

2.1 创建数据表

/*
CREATE [TEMPORARY(临时表)] TABLE [IF NOT EXISTS] 数据表名
[(col_name(字段名) type(字段类型) [NOT NULL | NULL] [DEFAULT deafault_value] [AUTO_INCREMENT(自动编号)] [PRIMARY KEY(主键)] [UNIQUE(唯一性约束)] [reference_definition(注释)],...
[表级别约束条件])]
[table_options(表的特性参数)] [select_statement(select语句描述部分，使用它可以快速创建表)];
*/
CREATE TABLE
IF
	NOT EXISTS `ddpl_admin` (
		`id` INT auto_increment PRIMARY KEY COMMENT '主键ID',
		`user` VARCHAR ( 30 ) NOT NULL COMMENT '用户名',
		`password` VARCHAR ( 30 ) NOT NULL COMMENT '密码',
		`createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期' 
	) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '管理员表';-- 设置存储引擎和编码格式，数据库初始化时如定义了可以不写
CREATE TABLE
IF
	NOT EXISTS ddpl_dept ( 
	`dept_id` INT ( 11 ) COMMENT '部门编号',
	`dept_name` VARCHAR ( 100 ) NOT NULL COMMENT '部门名称',
	PRIMARY KEY(`dept_id`), -- 主键可以最后定义，可以定义多个联合主键
	UNIQUE(dept_name) -- 唯一约束可最后定义，[CONSISTENT <约束名> UNIQUE(<字段名>)]，可设置多个，可为空值
	)COMMENT '部门表';
INSERT INTO ddpl_dept
VALUES
	( 100, '综合部' ),
	( 200, '开发部' ),
	( 300, '销售部' ),
	( 400, '财务部' ),
	( 500, '营销部' );
/*
使用外键约束
外键用来在两个表的数据之间建立连接，可以是一列或多列。一个表可以有一个或多个外键。
主表(父表)：相关联字段中主键所在的那个表即是主表。
从表(子表)：相关联字段中外键所在的那个表即是从表。
[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [ ,字段名2,...] REFERENCES <主表名> 主键列1 [ ,主键列2,...]
*/
CREATE TABLE
IF
	NOT EXISTS ddpl_emp (
		`emp_id` INT ( 11 ) AUTO_INCREMENT PRIMARY KEY COMMENT '人员编号',
		`emp_name` VARCHAR ( 100 ) NOT NULL COMMENT '姓名',
		`emp_age` INT ( 11 ) NOT NULL COMMENT '年龄',
		`emp_sex` ENUM ( '男', '女' ) DEFAULT '男' NOT NULL COMMENT '性别',
		`emp_salary` FLOAT COMMENT '薪资',
		`dept_id` INT ( 11 ) COMMENT '所属部门编号',
		CONSTRAINT fk_emp_dept1 FOREIGN KEY ( dept_id ) REFERENCES ddpl_dept ( dept_id ) 
	)COMMENT '人员表';
INSERT INTO ddpl_emp
VALUES
	( 1, '张三', 28, '男', 5048.59, 200 ),
	( 2, '大强', 32, '男', 7858.42, 100 ),
	( 3, '小王', 19, '男', 2000.00, 300 ),
	( 4, '小胖', 24, '男', 3012.66, 100 ),
	( 5, '小赵', 25, '男', 4869.75, 300 ),
	( 6, '小丽', 27, '女', 6875.41, 100 ),
	( 7, '小文', 25, '女', 4862.53, 200 ),
	( 8, '大黄', 30, '男', NULL, NULL );
/*根据已有的表创建表结构*/
CREATE TABLE
IF
	NOT EXISTS `ddpl_tb1` LIKE `ddpl_admin`;
/*根据已有的表创建表结构和数据*/
CREATE TABLE
IF
	NOT EXISTS ddpl_tb2 AS SELECT
	* 
FROM
	ddpl_admin;

2.2 查看表

/*查看所有表*/
SHOW TABLES;
/*查看指定数据库的表*/
SHOW TABLES 
FROM
	test_ddpl;
/*查看表结构
1.SHOW COLUMNS语句*/-- SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];
-- SHOW [FULL] COLUMNS FROM 数据库名.数据表名;
SHOW COLUMNS 
FROM
	test_ddpl.ddpl_admin;

/*2.DESCRIBE语句*/
-- DESCRIBE 数据表名;
-- DESCRIBE 数据表名 列名;
-- DESCRIBE可简写为DESC
DESC ddpl_admin `user_name`;

/*
3.查看表详细结构
SHOW CREATE TABLE <表名>
*/
SHOW CREATE TABLE ddpl_emp;

2.3 修改表

#修改表结构：
ALTER TABLE 数据表名
{alter_spec[,alter_spec]...}
[FIRST(移动到首列)|
AFTER 另一个字段(移动到指定字段后面)|]

alter_spec内容：

修改语句	作用
ADD COLUMN <列名> <类型>	新增列
ADD PRIMARY KEY <列名>	添加主键
ADD UNIQUE [索引名] <列名>	添加唯一键
ADD INDEX [索引名] <列名>	添加索引
CHANGE COLUMN <旧列名> <新列名> <新列类型>	修改字段名
MODIFY COLUMN <列名> <类型>	修改字段类型
DROP COLUMN <列名>	删除列
DROP INDEX 索引名	删除索引
DROP PRIMARY KEY	删除主键
DROP FOREIGN KEY 外键	删除外键
RENAME TO <新表名>	重命名表
CHARACTER SET <字符集名>	更改字符集
COLLATE <校对规则名>	更改校对规则名
ENGINE=<更改后的存储引擎名>	更改存储引擎名

ALTER TABLE ddpl_admin ADD `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
CHANGE `user` `user_name` VARCHAR ( 30 ),
MODIFY `password` VARCHAR ( 40 );
/*重命名表
RENAME TABLE 数据表名1 TO 数据表名2；*/
RENAME TABLE ddpl_admin TO ddpl_user;

2.4 删除数据表

-- 删除表 DROP TABLE 数据表名;
DROP TABLE IF EXISTS `ddpl_tb1`;
-- 清空表
TRUNCATE test_user;

2.5 导入脚本文件

source .sql完整文件名，不能含中文

3.MySQL数据类型

3.1 数值类型

3.1.1 整数类型

类型名称	说明	存储需求	取值范围(有符号)	取值范围(无符号)
TINYINT	很小的整数	1字节	-128~127	0~255
SMALLINT	小的整数	2字节	-32768~32767	0~65535
MEDIUMINT	中等大小的整数	3字节	-8388608~8388607	0~16777215
INT(INTEGER)	普通大小的整数	4字节	-2147483648~2147483647	0~4294967295
BIGINT	大整数	8字节	-9223372036854775808~9223372036854775807	0~18446744073709551615

显示宽度只用于显示，并不能限制取值范围和占用空间。例如：INT(3)会占用4字节的存储空间，并且允许的最大值不会是999，而是INT整型所允许的最大值。

CREATE TABLE tmp1 ( x TINYINT ( 4 ), y SMALLINT ( 6 ), z MEDIUMINT ( 9 ), m INT ( 11 ), n BIGINT ( 20 ) );
DESC tmp1;

3.1.2 浮点数类型和定点数类型

浮点数类型和定点数类型都可以用(M,N)来表示。M为精度，表示总共的位数；N为标度，表示小数的位数。

类型名称	说明	存储需求	取值范围(有符号)	取值范围(无符号)
FLOAT	单精度浮点数	4字节	-3.402823466E+38~-1.175494351E-38	0和1.175494351E-38~3.402823466E+38
DOUBLE	双精度浮点数	8字节	-1.7976931348623157E+308~-2.2250738585072014E-308	0和2.2250738585072014E-308~1.7976931348623157E+308
DECIMAL(M,D),DEC	压缩的“严格”定点数		M+2字节	M+2字节

在MYsql中，定点数以字符串形式存储，在对精度要求比较高的时候(如货币、科学数据等) 使用DECIMAL的类型比较好，另外两个浮点数进行减法和比较运算时容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

CREATE TABLE tmp2 (
	x FLOAT ( 5, 1 ),
	y DOUBLE ( 5, 1 ),
    z DECIMAL ( 5, 1 ));
INSERT INTO tmp2 VALUES	( 5.12, 5.15, 5.123 );

3.2 日期/时间类型

类型名称	日期格式	日期范围	存储需求
YEAR	YYYYY	1901~2155	1字节
TIME	HH:MM:SS	-838:59:59~838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01~9999-12-3	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC	4字节

CREATE TABLE tmp3 (
	y YEAR,
	t TIME,
	d DATE,
	dt DATETIME,
	`createtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
	`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
);
INSERT INTO tmp3 ( y, t, d, dt )
VALUES
	( 2010, '10:05:05', '1998-08-08', '1998-08-08 08:08:08' ),
	( '0', '23:23', '19980808', '19980808080808' ),
	( '00', '2 10:10', '99-09-09', '99-09-09 09:09:09' ),
	( '77', '302', CURRENT_DATE (), 101010101010 ),
	( '10',	'101112', NOW(), NOW());

3.3 字符串类型

3.3.1 文本字符串类型

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此L<=M和1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此L<2^8
TEXT	小的非二进制字符串	L+2字节，在此L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此L<2^32
ENUM	单选，枚举类型，只能有一个枚举字符串值	1或2字节，取决于枚举值的数目，最大值为65535
SET	多选，字符串对象可以有0个或多个SET成员	1、2、3、4或8字节，取决于聚合成员的数量(最多64个成员)

CREATE TABLE tmp4 (
	ch CHAR ( 4 ),
	vch VARCHAR ( 4 ),
	enm ENUM ( 'FIRST', 'SECOND', 'THIRD' ),
	s SET ( 'a', 'b', 'c', 'd' ) 
);
INSERT INTO tmp4 VALUES ( 'ab ', 'ab ', 1, 'a,b,c' );

3.3.2 二进制字符串类型

类型名称	说明	存储需求
BIT(M)	位字段类型	大约(M+7)/8字节
BINARY(M)	固定长度二进制字符串	M字节
VARBINARY(M)	可变长度二进制字符串	M+1字节
TINYBLOB(M)	非常小的BLOB	L+1字节，在此L<2^8
BLOB(M)	小BLOB	L+2字节，在此L<2^16
MEDIUMBLOB(M)	中等大小的BLOB	L+3字节，在此L<2^32
LONGBLOB(M)	非常大的BLOB	L+4字节，在此L<2^32

BLOB主要存储图片、音频信息。

CREATE TABLE tmp5 ( b BIT ( 4 ), bin BINARY ( 4 ), vb VARBINARY ( 3 ) );
INSERT INTO tmp5 VALUES	( 2, 5, 5 );

4.MySQL函数

4.1 数学函数

4.1.1 绝对值函数ABS(x)和返回圆周率的函数PI()

SELECT ABS(- 3.3 ), PI();

4.1.2 平方根函数SQRT(x)和求余函数MOD(x,y)

SELECT SQRT( 9 ), MOD ( 22, 3 );

4.1.3 获取整数的函数CEIL(x）、CEILING(x)和FLOOR(x)

SELECT CEIL( 3.35 ), CEILING( 3.35 ), FLOOR( 3.35 );

4.1.4 获取随机数的函数RAND()和RAND(x)

SELECT RAND(), RAND(), RAND( 10 ), RAND( 10 ), RAND( 11 );

4.1.5 四舍五入函数ROUND(x)、ROUND(x,y)和截取值TRUNCATE(x,y)

SELECT ROUND( 1.15 ), ROUND( 1.38, 1 ), ROUND( 1.38, 0 ), 	ROUND( 232.28,- 1 ), ROUND( 232.28,- 2 ), TRUNCATE ( 1.58, 1 ), TRUNCATE ( 1.68, 0 ), TRUNCATE ( 239.28,- 1 ), TRUNCATE ( 282.28,- 2 );

4.1.6 符号函数SIGN(X),x的值为负、0或正时返回的结果依次为-1、0或1

SELECT SIGN(- 21 ), SIGN( 0 ), SIGN( 33 );

4.1.7 幂运算函数POW(x,y)、POWER(x,y)和EXP(x)

POW(x,y)、POWER(x,y)函数返回x的y次乘方的结果值。
EXP(x)返回e的x乘方后的值。

SELECT POW( 2, 2 ), POWER( 2, 3 ), EXP( 3 ), EXP( 0 );

4.1.8 对数运算函数LOG(x)和LOG10(x)

LOG(x)返回x的自然对数，x相对于基数e的对数。
LOG10(x)返回x的基数为10的对数。

SELECT LOG( 3 ), LOG(- 3 ), LOG10( 2 ), LOG10( 100 ), LOG10(- 100 );

4.1.9 角度与弧度相互转换的函数RADIANS(x)和DEGREES(s)

RADIANS(x)将x由角度转化为弧度。
DEGREES(x)将x由弧度转换为角度。

SELECT RADIANS( 90 ), RADIANS( 180 ), DEGREES(PI()), DEGREES( PI()/ 2 );

4.1.10 正弦函数SIN(x)和反正弦函数ASIN(x)

SIN(x)返回弧度x正弦。
ASIN(x)返回弧度x的反正弦，若x不在-1~1范围内，则返回NULL。

SELECT SIN( RADIANS( 30 ) ), SIN(PI()), ASIN( 0.5 ), ASIN( 3 );

4.1.11 余弦函数COS(x)和反余弦函数ACOS(x)

COS(x)返回弧度x的余弦。
ACOS(x)返回弧度x的反余弦，若x不在-1~1范围内，则返回NULL。

SELECT COS( 0 ), COS(RADIANS( 60 )), ACOS( 1 ), ACOS( 0 );

4.1.12 正切函数、反正切函数和余切函数

TAN(x)返回弧度x的正切。
ATAN(x)返回弧度x的反正切。
COT(x)返回弧度x的余切。

SELECT TAN( 0.3 ), ROUND(TAN( PI()/ 4 )), ATAN( 1 ), COT( 0.3 );

4.2字符串函数

4.2.1 计算字符串符数的函数和字符串长度的函数

CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。
LENGTH(str)返回值为字符串的字节长度，使用utf8(UNICODE的一种变长字符编码，又称万国码)编码字符集时，一个汉字是3字节，一个数字或字母算1字节。

SELECT CHAR_LENGTH( 'apple' ), CHAR_LENGTH( 'egg' ), LENGTH( '苹果' ), LENGTH( 'egg' );

4.2.2 合并字符串函数CONCAT(s1,s2,…)、CONCAT_WS(separator,str1,str2,…)

CONCAT(s1,s2,…)返回结果为连接参数产生的字符串，或许有一个或多个参数。如有任何一个参数为NULL，则返回值为NULL。
CONCAT_WS(separator,str1,str2,…)函数连接带分隔符的字符串

SELECT
	CONCAT( 'My SQL', '8.0' ),
	CONCAT( 'My SQL', NULL, '8.0' ),
	CONCAT_WS( '-', '1st', '2nd', '3rd' ),
	CONCAT_WS( '*', '1st', NULL, '3rd' );

4.2.3 替换字符串的函数INSERT(s1,x,len,s2)

INSERT(s1,x,len,s2)返回字符串s1，其子字符串起始于x位置和被字符串s2取代的len字符。
如果x超过字符串长度，则返回值为原始字符串。
假如len的长度大于其他字符串的长度，则从位置x开始替换。
若任何一个参数为NULL，则返回值为NULL。

SELECT 
    INSERT ( 'Quest', 2, 4, 'What' ),
	INSERT ( 'Quest', - 1, 4, 'What' ),
	INSERT ( 'Quest', 3, 100, 'What' );

4.2.4 字母大小写转换函数

LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换成小写字母。
UPPER(str)或者UCASE(str)可以将字符串str中的字母字符全部转换成大写字母。

SELECT LOWER( 'BEAUTIFUL' ), LCASE( 'Well' ), UPPER( 'black' ), UCASE( 'Pillow' );

4.2.5 获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

LEFT(s,n)返回字符串s开始的最左边n个字符。
RIGHT(s,n)返回字符串中右边n个字符。

SELECT LEFT	( 'football', 4 ),	RIGHT ( 'football', 4 );

4.2.6 填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)

LPAD(s1,len,s2)返回字符串s1,其左边由字符串s2填补到len字符长度。
假如s1的长度大于len，则返回值被缩短至len字符。
RPAD(s1,len,s2)返回字符串s1,其右边由字符串s2填补到len字符长度。
假如s1的长度大于len，则返回值被缩短至len字符。

SELECT
	LPAD( 'world', 4, 'hello' ),
	LPAD( 'world', 10, 'hello' ),
	RPAD( 'hello', 4, 'world' ),
	RPAD( 'hello', 10, 'world' );

4.2.7 删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)

LTRIM(s)返回字符串s，字符串左侧空格字符被删除。
RTRIM(s)返回字符串s，字符串右侧空格字符被删除。
TRIM(s)返回字符串s，字符串两侧空格字符被删除。

SELECT LTRIM( '  book  ' ), RTRIM( '  book  ' ), TRIM( '  book  ' );

4.2.8 删除指定字符串的函数TRIM(s1 FROM s)

TRIM(s1 FROM s)删除字符串s中两端所有的字符串s1,s1默认为空格。

SELECT TRIM( 'xy' FROM 'xyxobxyonakxyxy' );

4.2.9 重复生成字符串的函数REPEAT(s,n)

REPEAT(s,n)返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。
若n<=0，则返回一个空字符串。若s或n为NULL，则返回NULL。

SELECT REPEAT ( 'mysql', 3 );

4.2.10 空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)

SPACE(n)返回一个由n个空格组成的字符串。
REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。

	SELECT SPACE( 6 ), REPLACE ( 'xxx.mysql.com', 'x', 'w' );

4.2.11 比较字符串大小的函数STRCMP(s1,s2)

STRCMP(s1,s2)：若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1,；其他情况返回1。

	SELECT STRCMP( 'txt', 'txt2' ), STRCMP( 'txt1', 'txt' ), STRCMP( 'txt', 'txt2' );

4.2.12 获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)

SUBSTRING(s,n,len)带有len参数的格式，从字符串s返回一个长度与len字符相同的子字符串，起始于位置n。也可能对n使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。
MID(s,n,len)与SUBSTRING(s,n,len)的作用相同。

	SELECT
		SUBSTRING( 'breakfast', 5 ),
		SUBSTRING( 'breakfast', 5, 3 ),
		SUBSTRING( 'breakfast', - 3 ),
		SUBSTRING( 'breakfast', - 5, 3 ),
		MID( 'lunch', 3 );

4.2.13 匹配子串开始位置的函数

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str, str1)3个函数的作用相同，返回子字符串str1在字符串str中的开始位置。

	SELECT LOCATE( 'ball', 'football' ), POSITION( 'ball' IN 'football' ), INSTR( 'football', 'ball' );

4.2.14 字符串逆序的函数REVETSE(s)

REVETSE(s)将字符串s反转，返回的字符串的顺序和s字符串顺序相反。

	SELECT REVERSE( 'ABC' );

4.2.15 返回指定位置的字符串的函数

ELT(N,str1,str2,str3,…)：若N=1，则返回值为str1；若N=2，则返回值为str2；以此类推；若N小于1或大于参数的数目，则返回值为NULL。

	SELECT
		ELT( 3, '1st', '2nd', '3rd' ),
		ELT( 3, '1st', '2nd' );

4.2.16 返回指定字符串位置的函数FIELD(s,s1,s2,…,sn)

FIELD(s,s1,s2,…,sn)返回字符串s在列表s1,s2,…sn中第一次出现的位置，在找不到s的情况下，返回值为0。
如果s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。

	SELECT
		FIELD( 'Hi', 'hihi', 'Hi', 'HHi' ),
		FIELD( 'Hi', 'hihi', 'HIi', 'HHi' );

4.2.17 返回子串位置的函数FIND_IN_SET(s1,s2)

FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置。
如果s1不在s2或s2为空字符串，则返回值为0。
如果任意一个参数为NULL，则返回值为NULL。

	SELECT FIND_IN_SET( 'Hi', 'hihi,Hey,Hi,bas' );

4.2.18 选取字符串的函数MAKE_SET(x,s1,s2,…sn)

MAKE_SET(x,s1,s2,…sn)函数按二进制数从s1,s2,…sn中选取字符串。
例如5的二进制是0101，从右往左选取第1位和第3位,NULL不会被添加到结果中。

	SELECT
		MAKE_SET( 5, 'a', 'b', 'c' ),
		MAKE_SET( 1 | 4, 'a', 'b', 'c' ),
		MAKE_SET( 1 | 4, 'a', 'b', NULL, 'c' );

4.3 日期和时间函数

4.3.1 获取当前日期的函数和获取当前时间的函数

CURDATE()和CURRENT_DATE()函数的作用相同，将当前日期按照’YYYY-MM-DD’或YYYYMMDD格式的值返回，具体格式根据函数在字符串或是数字语境中而定。“CURDATE() + 0”将当前日期值转换为数值型。
CURTIME()和CURRENT_TIME()函数的作用相同，将当前日期按照’HH:MM:SS’或HHMMSS格式的值返回，具体格式根据函数在字符串或是数字语境中而定。“CURTIME() + 0”将当前日期值转换为数值型。

	SELECT
		CURDATE(),
		CURRENT_DATE (),
		CURDATE() + 0,
		CURTIME(),
	    CURRENT_TIME (),
	    CURTIME() + 0;

4.3.2 获取当前日期和时间的函数

CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE()4个函数的作用相同，均返回当前日期和时间值，格式为’YYYY-MM-DD HH:MM:SS’或’YYYYMMDDHHMMSS’，具体格式根据函数在字符串或数字语境中而定。

	SELECT CURRENT_TIMESTAMP(),	LOCALTIME (), NOW(), SYSDATE();

4.3.3 UNIX时间戳函数

UNIX_TIMESTAMP(date)若无参数调用，则返回一个UNIX时间戳(‘1970-01-01 00:00:00’ GMT’格林尼治时间’之后的秒数)作为无符号整数。
FROM_UNIXTIME()将UNIX时间戳转换为普通格式时间。

	SELECT
		UNIX_TIMESTAMP(),
		UNIX_TIMESTAMP(
		NOW()),
		NOW(),
		FROM_UNIXTIME( '1675211727' );

4.3.4 返回UTC日期的函数和返回UTC时间的函数

UTC_DATE()函数返回当前UTC(世界标准时间)日期值，格式为’YYYY-MM-DD’或’YYYYMMDD’，具体格式根据函数在字符串或是数字语境中而定。

	SELECT UTC_DATE	(), UTC_DATE ()+ 0,	UTC_TIME (), UTC_TIME ()+ 0;

4.3.5 获取月份的函数MONTH(date)和MONTHNAME(date)

MONTH(date)函数返回date对应的月份，范围值为1~12。
MONTHNAME(date)函数返回日期date对应月份的英文全名。

	SELECT MONTH( '2023-02-01' ), MONTHNAME( '2023-02-01' );

4.3.6 获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDEY(d)

DAYNAME(d)函数返回d对应的工作日的英文名称。
DAYOFWEEK(d)函数返回d对应的一周中的索引。
WEEKDAY(d)函数返回日期对应的工作日索引。

	SELECT
		DAYNAME( '2023-02-01' ),
		DAYOFWEEK( '2023-02-01' ),
		WEEKDAY( '2023-02-01' );

4.3.7 获取星期数的函数WEEK(d)和WEEKOFYEAR(d)

WEEK(d)计算日期d是一年中的第几周。
WEEK(date[,mode]) mode指定改星期是否起始于周日或周一。
WEEKOFYEAR(d)技术某天位于一年中的第几周，范围时1~53，相当于WEEK(d,3)
WEEK函数中Mode参数取值：

MODE	一周的第一天	范围	Week 1为第一周…
0	周日	0~53	本年度中有一个周日
1	周一	0~53	本年度中有3天以上
2	周日	1~53	本年度中有一个周日
3	周一	1~53	本年度中有3天以上
4	周日	0~53	本年度中有3天以上
5	周一	0~53	本年度中有一个周一
6	周日	1~53	本年度中有3天以上
7	周一	1~53	本年度中有一个周一

	SELECT 
	    WEEK ( '2023-02-01' ),
		WEEK ( '2023-02-01', 0 ),
		WEEK ( '2023-02-01', 1 ),
		WEEK ( '2023-02-01', 3 ),
		WEEKOFYEAR( '2023-02-01' );

4.3.8 获取天数的函数DAYOFYEAR(d)和DAYOFMONTH(d)

DAYOFYEAR(d)函数返回d是一年中的第几天，范围是1~366。
DAYOFMONTH(d)函数返回d是一个月中的第几天，范围是1~31。

	SELECT
		DAYOFYEAR( '2023-02-01' ),
		DAYOFMONTH( '2023-02-01' );

4.3.9 获取年份、季度、小时、分钟和秒钟的函数

YEAR(date)返回date对应的年份，范围是1970~2069。
QUARTER(date)返回date对应的一年中的季度值，范围是1~4。
MINUTE(time)返回time对应的分钟数，范围是0~59。
SECOND(time)返回time对应的秒数，范围是0~59。

	SELECT 
	    YEAR ( '18-02-03' ),
		YEAR ( '96-02-03' ),
		QUARTER ( '18-04-01' ),
		MINUTE ( '2023-02-01 09:10:25"' );

4.3.10 获取日期的指定值的函数EXTRACT(type FROM date)

EXTRACT(unit FROM date)函数所使用的时间间隔类型说明符与DATE_ADD()或DATE_SUB()的相同，但它从日期中提取一部分，而不是执行日期运算。
type值为YEAR时，只返回年值；type值为YEAR_MONTH时返回年与月份；type值为DAY_MINUTE时，返回日、小时和分钟值。

	SELECT
		EXTRACT( YEAR FROM '2023-02-01' ),
		EXTRACT( YEAR_MONTH FROM '2023-02-01' ),
		EXTRACT( DAY_MINUTE FROM '2023-02-01' );

4.3.11 时间和秒钟转换的函数

TIME_TO_SEC(time)返回已转换为秒的time参数。转换公式为：小时3600+分钟60+秒。
SEC_TO_TIME(seconds)返回被转化为小时、分钟和秒数的seconds参数值，其格式为’HH:MM:SS’或’HHMMSS’，具体格式根据函数在字符串或是数字语境中而定。

	SELECT
		TIME_TO_SEC( '09:20:50' ),
		SEC_TO_TIME( 33650 ),
		SEC_TO_TIME( 33650 )+ 0;

4.3.12 计算日期和时间的函数

计算日期和时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()、SUBTIME()和DATEDIFF()
DATE_ADD(date,INTERVAL expr unit)和ADDDATE(date,INTERVAL expr unit)两个函数的作用相同，执行日期的加运算。
DATE_SUB(date,INTERVAL expr unit)和SUBDATE(date,INTERVAL expr unit)两个函数的作用相同，执行日期的减运算。
ADDTIME(expr1,expr2)函数将expr2值添加到expr1，并返回修改后的值，expr1是一个日期或日期时间表达式，而expr2是一个时间表达式。
SUBTIME(expr1,expr2)函数将expr1减去expr2值，并返回修改后的值，expr1是一个日期或日期时间表达式，而expr2是一个时间表达式。
DATEDIFF(expr1,expr2)返回起始时间expr1和结束时间epr2之间的天数。expr1和expr2为日期或日期时间表达式。

	SELECT
		DATE_ADD( '2023-02-01 09:43:20', INTERVAL 1 SECOND ) AS col1,
		ADDDATE( '2023-02-01 09:43:20', INTERVAL 1 SECOND ) AS col2,
		DATE_ADD( '2023-02-01 09:43:20', INTERVAL '1:1' MINUTE_SECOND ) AS col3,
		DATE_SUB( '2023-02-01', INTERVAL 31 DAY ) AS col4,
		SUBDATE( '2023-02-01', INTERVAL 31 DAY ) AS col5,
		DATE_SUB( '2023-02-01 09:43:20', INTERVAL '0 0:1:1' DAY_SECOND ) AS col6,
		ADDTIME( '2023-02-01 09:43:20', '1:1:1' ) AS col7,
		ADDTIME( '02:02:02', '02:00:00' ) AS col8,
		SUBTIME( '2023-02-01 09:43:20', '1:1:1' ) AS col9,
		SUBTIME( '02:02:02', '00:02:02' ) AS col10,
		DATEDIFF( '2023-02-01 09:43:20', '2022-02-22' ) AS col11;

4.3.13 将日期和时间格式化的函数

DATE_FORMAT(date,format)根据format指定的格式显示date值。
TIME_FORMAT(time,format)根据表达式format的要求显示时间time。
时间日期格式：

说明符	说明
%a	工作日的缩写名称(Sum…Sat)
%b	月份的缩写名称(Jan…Dec)
%c	月份，数字形式(0…12)
%D	以英文后缀标识月中的几号(1st,2nd…)
%d	该月日期，数字形式(00…31)
%e	该月日期，数字格式(0…31)
%f	微秒(000000…999999)
%H	以2位数表示24小时(00…23)
%h,%I	以2位数表示12小时(01…12)
%i	分钟，数字形式(00…59)
%j	一年中的天数(001…366)
%k	以24(0…23)小时表示时间
%l	以12(1…12)小时表示时间
%M	月份名称(January…December)
%m	月份，数字形式(00…12)
%p	上午(AM)或下午(PM)
%r	时间，12小时制(小时hh:分钟mm:秒数ss后加AM或PM)
%S,%s	以2位数形式表示秒(00…59)
%T	时间，24小时制(小时hh:分钟mm:秒数ss)
%U	周(00…53)，其中周日为每周的第一天
%u	周(00…53)，其中周一为每周的第一天
%V	周(01…53)，其中周日为每周的第一天，和%X同时使用
%v	周(01…53)，其中周日为每周的第一天，和%x同时使用
%W	工作日名称(周日…周六)
%w	一周中的每日(0=周日…6=周六)
%X	该周的年份，其中周日为每周的第一天；数字形式，4位数；和%V同时使用
%x	该周的年份，其中周一为每周的第一天；数字形式，4位数；和%v同时使用
%Y	4位数形式表示年份
%y	2位数形式表示年份
%%	标识符%

	SELECT
		DATE_FORMAT( '2023-02-01 10:15:00', '%W %M %Y' ) AS col1,
		DATE_FORMAT( '2023-02-01 10:15:00', '%D %y %a %d %m %b %j' ) AS col2,
		TIME_FORMAT( '16:00:00', '%H %k %h %I %l' ) AS col3;

GET_FORMAT({DATE|TIME|DATETIME}, {‘EUR’|‘USA’|‘JIS’|‘ISO’|‘INTERNAL’})返回日期时间字符串的显示格式。
GET_FORMAT返回的格式字符串：

值类型	格式化类型	显示格式字符串
DATE	EUR	%d.%m.%Y
DATE	INTERVAL	%Y%m%d
DATE	ISO	%Y-%m-%d
DATE	JIS	%Y-%m-%d
DATE	USA	%m.%d.%Y
TIME	EUR	%H.%i.%s
TIME	INTERVAL	%H%i%s
TIME	ISO	%H.%i.%s
TIME	JIS	%H.%i.%s
TIME	USA	%h:%i:%s%p
DATETIME	EUR	%Y-%m-%d %H.%i.%s
DATETIME	INTERVAL	%Y-%m-%d%H.%i
DATETIME	ISO	%Y-%m-%d %H.%i.%s
DATETIME	JIS	%Y-%m-%d %H.%i.%s
DATETIME	USA	%Y-%m-%d %H.%i.%s

	SELECT
		GET_FORMAT( DATE, 'EUR' ),
		GET_FORMAT( DATE, 'USA' ),
		DATE_FORMAT( '2023-02-01 10:32:00', GET_FORMAT( DATE, 'USA' ));

4.4 条件判断函数

4.4.1 IF(expr,v1,v2)函数

IF(expr,v1,v2)：如果表达式expr是TRUE，则返回值为v1，否则返回值为v2。
如果v1或v2中只有一个明确是NULL，则IF()函数的结果类型为非NULL表达式的结果类型。

SELECT 
    IF(1>2,2,3),
	IF(1<2,'yes','no'),
	IF(STRCMP('test','test1'),'no','yes');

4.4.2 IFNULL(v1,v2)函数

IFNULL(v1,v2)：假如v1不为NULL，则IFNULL()的返回追为v1；否则其返回值为v2。

SELECT 
    IFNULL(1,2),
	IFNULL(NULL,10),
	IFNULL(1/0,'wrong');

4.4.3 CASE函数

CASE case_value
WHEN when_value THEN
statement_list
ELSE
statement_list
END;
如果case_value等于when_value，则返回THEN后的结果，否则返回ELSE后的结果。

SELECT 
  CASE 2
	WHEN 1 THEN
		'one'
	WHEN 2 THEN
	  'two'
	ELSE
	  'more'
END;

4.5 系统信息函数

4.5.1 获取MySQL版本号、连接数和数据库名的函数

VERSION()返回指示MySQL服务器版本的字符串。
CONNECTION_ID()返回MySQL服务器当前连接的次数，每个连接都有各自唯一的ID。
SHOW PROCESSLIST;SHOW FULL PROCESSLIST查询运行的线程。
DATEBASE()和SCHEMA()函数返回使用utf8字符集的默认数据库名。

SELECT 
    VERSION() AS 'MySQL版本号',
	CONNECTION_ID() AS 'MySQL连接数',
	DATABASE() AS '数据库名',
	SCHEMA() AS '数据库名';

SHOW PROCESSLIST;

SHOW FULL PROCESSLIST;

4.5.2 获取用户名的函数

USER()、CURRENT_USER、CURRENT_USER()、SYSTEM_USER()和SESSION_USER()这几个函数返回当前被MySQL服务器验证的用户名和主机名组合。这个值符合确定当前登录用户存取权限的MySQL账户。

SELECT 
  USER(),
	CURRENT_USER(),
	SYSTEM_USER();

4.5.3 获取字符串的字符集和排序方式的函数

CHARSET(str)返回字符串str自变量的字符集。
COLLATION(str)返回字符串str的字符排列方式。

	SELECT
		CHARSET( 'abc' ),
		CHARSET( CONVERT ( 'abc' USING latin1 )),
		CHARSET( VERSION()),
		COLLATION ( 'abc' ),
		COLLATION ( CONVERT ( 'abc' USING latin1 ));

4.5.4 获取最后一个自动生成的ID值的函数

LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值。
一次插入一条记录。

	INSERT INTO ddpl_user ( `user_name`, `password` );
	VALUES
		( 'peter', 'faudsaf' );
	SELECT
		* 
	FROM
		ddpl_user;
	SELECT
		LAST_INSERT_ID();

一次插入多条记录
LAST_INSERT_ID()只返回插入的第一行数据时产生的值。

	INSERT INTO ddpl_user ( `user_name`, `password` )
	VALUES
		( 'mary', 'scv34d' ),
		( 'faosj', 'aosjfoai' ),
		( 'tom', 'fsoaijf9a0sf8' );
	SELECT
		* 
	FROM
		ddpl_user;
	SELECT
		LAST_INSERT_ID();

4.6 其他函数

4.6.1 格式化函数FORMAT(x,n)

FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n点，结果以字符串的形式返回。

	SELECT
		FORMAT( 168.646161, 3 ),
		FORMAT( 861.21, 5 ),
		FORMAT( 16161.6464, 0 );

4.6.2 不同进制的数字进行转换的函数

CONV(N,from_base,to_base)函数进行不同进制数间的转换。
返回值为数值N的字符串表示，由from_base进制转化为to_base进制。如有任意一个参数为NULL，则返回值为NULL。
自变量N被理解为一个整数，但是可以被指定为一个整数或字符串。最小基数为2，最大基数为36。

	SELECT
		CONV( 'a', 16, 2 ),
		CONV( 15, 10, 2 ),
		CONV( 15, 10, 8 );

4.6.3 IP地址与数字相互转换的函数

INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。
INET_NTOA(expr)给定一个数字网络地址，放回作为字符串的该地址的点地址表示。

	SELECT
		INET_ATON( '209.207.224.40' ),
		INET_NTOA( 3520061480 );

4.6.4 加密函数哈解锁函数

GET_LOCK(str,timeout)设法使用字符串str给定的名字得到一个锁，超时timeout秒。若成功得到锁，当执行RELEASE_LOCK()或连接断开(正常或非正常)时，这个锁就会解除。
RELEASE_LOCK(str)解开被GET_LOCK()获取的、用字符串str所命名的锁。若锁被解开，则返回1；若该线程尚未创建锁，则返回0(此时锁没有被解开)；若命名的锁不存在，则返回NULL。若该锁从未被GET_LOCK()的调用获取，或锁已经被提前解开，则该锁不存在。
IS_FREE_LOCK(str)检查名为str的锁是否可以使用(换言之，没有被封锁)。若锁可以使用，则返回1(没有人在用这个锁)；若这个锁正在被使用，则返回0；出现错误，则返回NULL(诸如不正确的参数)。
IS_USED_LOCK(str)检查名为str的锁是否正在被使用(换言之，被封锁)。若被封锁，则返回使用该锁的客户端的连接标识符(CONNECTION ID)；否则，返回NULL。

	SELECT
		GET_LOCK( 'lock1', 10 ) AS GetLock,
		IS_USED_LOCK( 'lock1' ) AS ISUsedLock,
	    IS_FREE_LOCK( 'lock1' ) AS ISFreeLock,
	    RELEASE_LOCK( 'lock1' ) AS ReleaseLock;

4.6.5 重复执行指定操作的函数

BENCHMARK(count,expr)函数重复count次执行表达式expr。它可以用于计算MySQL处理表达式的速度。
结果值通常为0，只是代表处理过程很快，并不是没有花费时间。
BENCHMARK(count,expr)报告的时间是客户端经过的时间，而不是在服务器端的CPU时间，每次执行后报告的时间并不一定是相同的。

SELECT BENCHMARK(500000,SHA('DEADPL'));

4.6.6 改变字符集的函数

CONVERT(expr USING transcoding_name)：带有USING的CONVERT()函数被用来不同的字符集之间转化数据。

SELECT CHARSET('string'),CHARSET(CONVERT('string' USING latin1));

4.6.7 改变数据类型的函数

CAST(expr AS type)和CONVERT(x,type)函数将一个类型的值转换为另一个类型的值，可转换的type有BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED。

	SELECT
		CAST( 100 AS CHAR ( 2 )),
		CONVERT ( '2023-02-02 08:09:05', TIME );

4.7 MySQL8的新特性1————加密函数

4.7.1 加密函数MD5(str)

MD5(str)为字符串算出一个MD5 128比特校验码。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则会返回NULL。

	SELECT
		MD5( 'mypwd' );

4.7.2 加密函数SHA(str)

SHA(str)从原明文密码str计算并返回加密后的密码字符串，若参数为NULL，则会返回NULL，比MD5更加安全。

	SELECT
		SHA( 'mypwd' );

4.7.3 加密函数SHA2(str,hash_length)

SHA2(str,hash_length)使用hash_length作为长度，加密str。hash_length支持的值为224、256、384、512和0。0等于256。

	SELECT
		SHA2 ( 'mypwd', 0 );

链接: 解密网站

4.8 MySQL8的新特性2

在MySQL8中，新增了一个窗口函数，用它可以实现很多新的查询方式。
窗口函数类似于SUM()、COUNT()那样的集合函数，但它不会将多行查询结果合并为一行，而是将结果放回多行当中。也就是说，窗口函数是不需要GROUP BY的。

   SELECT
	   *,
	   RANK() over w1 AS 'rank' 
   FROM
	   ddpl_emp window w1 AS ( ORDER BY `emp_salary` DESC);

	SELECT
	  *,
		SUM(`emp_salary`) over() as total_count 
	FROM
	  ddpl_emp;

	SELECT
	  *,
	  `emp_salary` / SUM(`emp_salary`) over() as rate 
	FROM
	  ddpl_emp;

5.查询数据

5.1 基本查询语句

SELECT selection_list       // 要查询的内容，选择哪些列
FROM 数据表名               // 指定数据表
WHERE primary_constraint    // 查询时需要满足的条件，行必须满足的条件
GROUP BY grouping_columns   // 如何对结果进行分组
ORDER BY sorting_columns    // 如何对结果进行排序
HAVING secondary_constraint // 查询时满足的
LIMIT count;                // 限定输出的查询结果

5.1.1 查询表中的所有数据

SELECT * FROM ddpl_emp;

5.1.2 查询表中的一列或多列

从单个表中获取数据。

SELECT
	`emp_name`,
	`emp_age`,
	`emp_sex` 
FROM
	ddpl_emp;

从多个表中获取数据，可以使用WHERE条件来确定表之间的联系。

SELECT
	ddpl_emp.emp_id,
	ddpl_emp.emp_name,
	ddpl_dept.dept_id,
	ddpl_dept.dept_name 
FROM
	ddpl_emp,
	ddpl_dept 
WHERE
	ddpl_emp.dept_id = ddpl_dept.dept_id;

5.2 单表查询

5.2.1查询所有字段

SELECT * FROM 数据表名;

5.2.2 查询指定字段

SELECT 字段名 FROM 表名;

5.2.3查询指定数据

SELECT 字段名1m字段名2,…字段名n
FROM 表名
WHERE 查询条件

比较运算符	说明
id=5	等于
NULL<=>NULL	安全等于运算符，两个数据均为NULL时，返回1，而不是NULL
id>5	大于
id<5	小于
id=>5	大于等于
id<=5	小于等于
id!=5	不等于!=(<>)
id is null	是否为空值
id is not null	是否为非空值
id between 1 and 6	在…与…之间
LEAST(2,0,NULL)	返回多个参数中的最小值，有NULL时返回NULL
GREATEST(2,0,NULL)	返回多个参数中的最大值，有NULL时返回NULL
id in(3,4,5)	在指定范围内
name not in(‘李四’)	不在指定范围内
name like(‘王%’)	模式匹配
name not like（‘王%’)	模式匹配
name REGEXP 正则表达式	常规表达式

逻辑运算符	说明
NOT(!)	逻辑非
AND(&&)	逻辑与
OR(||)	逻辑或
XOR	逻辑异或

位运算符	说明
|	位或
&	位与
^	位异或
<<	位左移
>>	位右移
~	位取反，反转所有比特

SELECT * FROM ddpl_emp WHERE `emp_age` > 30;

5.2.4 带IN关键字的查询

IN关键字可以判断某个字段的值是否在指定的集合中
SELECT * FROM 表名 WHERE 条件 [NOT] IN (元素1，元素2，…，元素n);

SELECT * FROM ddpl_emp WHERE `emp_id` IN ( 1, 5, 7 );

SELECT * FROM ddpl_dept WHERE `dept_name` NOT IN ( '开发部', '销售部' );

5.2.5 带BETWEEN AND的范围查询

BETWEEN AND关键字可以判断某个字段的值是否在指定的范围内
SELECT * FROM 表名 WHERE 条件 [NOT] BETWEEN 取值1 AND 取值2；

SELECT * FROM ddpl_emp WHERE `emp_age` BETWEEN 25 AND 30;

5.2.6 带LIKE的字符匹配查询

LIKE属于较常用的比较运算符，通过它可以实现模糊查询。它有两种通配符：“%”和“_”。
“%”可以匹配一个或多个字符，可以代表任意长度的字符串，长度可以为0。
“_”只匹配一个字符。

SELECT * FROM ddpl_emp WHERE `emp_name` LIKE '张%';

5.2.7 用IS NULL关键字查询空值

IS [NOT] NULL

SELECT * FROM ddpl_emp WHERE `dept_id` IS NULL;

5.2.8 带AND的多条件查询

AND关键字可以联合多个条件进行查询，只有同时满足所有条件的记录才会被查询出来。
SELECT * FROM 数据表名 WHERE 条件1 AND 条件2 […AND 条件表达式n];

SELECT * FROM ddpl_emp WHERE `emp_age` > 30 AND `emp_sex` = '男';

5.2.9 带OR的多条件查询

OR关键字也可以用来联合多个条件进行查询，只要满足查询条件中的一个，此记录就会被查询出来。
SELECT * FROM 数据表名 WHERE 条件1 OR 条件2 […OR 条件表达式n];

SELECT * FROM ddpl_emp WHERE `emp_id` = 3 OR `emp_name` = '小赵';

5.2.10 用DISTINCT关键字去除查询结果中的重复记录

SELECT DISTINCT 字段名 FROM 表名;

SELECT DISTINCT
	d.`dept_name` 
FROM
	ddpl_emp e,
	ddpl_dept d 
WHERE
	e.`dept_id` = d.`dept_id`;

5.2.11 用ORDER BY关键字对查询结果排序

ORDER BY 字段名 [ASC(升序，默认)|DESC(降序)];

5.2.11.1 单列排序

SELECT * FROM ddpl_emp ORDER BY `emp_age`;

5.2.11.2 多列排序

ORDER BY 字段名 [ASC(升序，默认)|DESC(降序)] ,字段名 [ASC(升序，默认)|DESC(降序)]] […];

SELECT * FROM ddpl_emp ORDER BY	`emp_age` DESC,	`emp_salary` DESC;

5.2.12 用GROUP BY关键字分组查询

通过GROUP BY语句可以将数据划分到不同的组中，实现对记录进行分组查询。
在查询时，所查询的列必须包含在分组的列中，使查询到的数据没有矛盾。

5.2.12.1 使用GROUP BY关键字来分组

单独使用GROUP BY关键字，查询结果只显示每组的一条记录。

如果报1055-ONLY_FULL_GROUP_BY的设定，将不允许查询字段包括非聚集列错误。
先查询数据库配置

SELECT	@@GLOBAL.sql_mode;

查询结果：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
把ONLY_FULL_GROUP_BY 去掉 修改mysql 配置

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

SELECT
	`emp_id`,
	`emp_name`,
	`dept_id` 
FROM
	ddpl_emp 
GROUP BY
	`dept_id`;

5.2.12.2 GROUP BY关键字与GROUP_CONCAT()函数一起使用

使用GROUP BY和GROUP_CONCAT()，可以将每个组中的所有字段值都显示出来。

SELECT
	`emp_id`,
	`emp_name`,
	GROUP_CONCAT( `dept_id` ) 
FROM
	ddpl_emp 
GROUP BY
	`dept_id`;

5.2.12.3 在GROUP_BY子句中使用WITH ROLLUP

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

SELECT
	`emp_sex`,
	COUNT(*) AS Total 
FROM
	ddpl_emp 
GROUP BY
	`emp_sex` WITH ROLLUP;

5.2.12.4 按多个字段进行分组

使用GROUP BY关键字也可以按多个字段进行分组

SELECT
	`emp_id`,
	`emp_name`,
	`emp_sex`,
	`dept_id` 
FROM
	ddpl_emp 
GROUP BY
	`dept_id`,
	`emp_sex`;

5.2.12.5 分组求和

SELECT
	d.dept_name 部门名称,
	ROUND( sum( e.emp_salary ), 2 ) 部门总薪资 
FROM
	ddpl_emp e,
	ddpl_dept d 
WHERE
	e.dept_id = d.dept_id 
GROUP BY
	d.dept_name;

5.2.12.6 GROUP BY组合使用HAVING进行过滤分组

HAVING与WHERE其实差别不大。WHERE通常当做标准的过滤条件；HAVING用作分组过滤条件。

SELECT
	emp_name,
	emp_salary 
FROM
	ddpl_emp 
GROUP BY
	emp_id 
HAVING
	emp_salary > 5000;

5.2.12.7 分组排序

GROUP BY与ORDER BY一起使用。
ROLLUP和ORDER BY是互相排斥的。

SELECT
	count( e.emp_id ) 人员数目,
	d.dept_name 部门名称
FROM
	ddpl_emp e,
	ddpl_dept d
WHERE
	e.dept_id = d.dept_id 
GROUP BY
	e.dept_id 
ORDER BY
	人员数目;

5.2.13用LIMIT限制查询结果的数量

LIMIT语句可以对查询结果的记录条数进行限定，控制它输出的行数。

SELECT
	* 
FROM
	ddpl_emp 
	LIMIT 3;

LIMIT还可以从查询结果的中间部分取值，LIMIT 偏移量,行数。
OFFSET偏移配合LIMIT使用, LIMIT 行数 OFFSET 偏移量。
两种偏移查询等同。

SELECT
	* 
FROM
	ddpl_emp 
	LIMIT 3, 2;

SELECT
	* 
FROM
	ddpl_emp 
	LIMIT 2 OFFSET 3;

5.3 聚合函数查询

聚合函数的特点是它们根据一组数据求出一个值。
聚合函数的结果值只根据选定行中非NULL的值进行计算，NULL值被忽略。

5.3.1 COUNT()函数

对于“*”，返回选择集合中所有行数。
对于除“*”以外的任何参数，返回所选集合中非NULL值的行数。

SELECT COUNT(*) FROM ddpl_emp;

5.3.2 SUM()函数

SUM()函数可以求出表中某个字段取值的总和，忽略值为NULL的行。

SELECT
	ROUND( SUM( `emp_salary` ), 2 ) 
FROM
	ddpl_emp;

5.3.3 AVG()函数

AVG()函数可以求出表中某个字段取值的平均值。

SELECT
	ROUND( avg( `emp_salary` ), 2 ) 
FROM
	ddpl_emp;

5.3.4 MAX()函数

MAX()函数可以求出表中某个字段取值的最大值。

SELECT max( `emp_age` ) FROM ddpl_emp;

5.3.5 MIN()函数

MIN()函数可以求出表中某个字段取值的最小值。

SELECT min( `emp_age` ) FROM ddpl_emp;

5.4 连接查询

连接是把不同表的记录连到一起的最普遍的方法。这种连接语句可以以多种高级方法来组合表记录。

5.4.1 简单连接

查询员工姓名和员工对应的部门名称。

SELECT
	e.`emp_name`,
	d.`dept_name` 
FROM
	ddpl_emp AS e,
	ddpl_dept AS d 
WHERE
	e.`dept_id` = d.`dept_id`;

5.4.2 内连接查询

内连接(INNER JOIN)是最普遍的连接类型，而且是最匀称的，因为它们要求构成连接的每一部分的每个表相匹配，不匹配的行将被排除。
内连接最常见的例子是相等连接，也就是连接后的表中的某个字段与每个表中的都相同。
查询员工姓名和员工对应的部门名称

SELECT
	e.`emp_name`,
	d.`dept_name` 
FROM
	ddpl_emp AS e
	INNER JOIN ddpl_dept AS d ON ( e.`dept_id` = d.`dept_id` );

5.4.3 外连接查询

使用OUTER JOIN关键字将两个表连接起来。
外连接生成的结果集不仅包含符合连接条件的行数据，而且还包含左表、右表或两边连接表中所有的数据行。
SELECT 字段名称 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.字段名1 = 表名2.属性名2;
外连接分为左外连接、右外连接和全外连接。

5.4.3.1 左外连接

左外连接是指将左表中的所有数据分别与右表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括左表中不符合条件的数据，并在右表的相应列中添加NULL值。
查询ddpl_emp和ddpl_dept表，使用左外连接列出ddpl_emp表中的所有记录

SELECT
	* 
FROM
	ddpl_emp
	LEFT JOIN ddpl_dept ON ddpl_emp.dept_id = ddpl_dept.dept_id;

5.4.3.2 右外连接

右外连接是指将右表中的所有数据分别与左表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括右表中不符合条件的数据，并在左表的相应列中添加NULL值。
查询ddpl_emp和ddpl_dept表，使用右外连接列出ddpl_emp表中的所有记录

SELECT
	* 
FROM
	ddpl_emp
	RIGHT JOIN ddpl_dept ON ddpl_emp.dept_id = ddpl_dept.dept_id;

5.4.3.3 复合条件连接查询

在连接查询时，也可以增加其他的限制条件，通过多个条件的复合查询，可以使查询结果更加准确。

SELECT
	* 
FROM
	ddpl_emp,
	ddpl_dept 
WHERE
	ddpl_emp.dept_id = ddpl_dept.dept_id 
	AND ddpl_dept.dept_name = '开发部' 
	AND ddpl_emp.emp_sex = '女';

5.5 子查询

子查询就是SELECT查询是另一个查询的附属。MYSQL可以嵌套多个查询，在外面一层的查询中使用里面一层查询产生的结果集。
多层查询时，MYSQL从最内层的查询开始，然后向外、向上移动到外层(主)查询，在这个过程中每个查询产生的结果集都被赋给包围它的父查询，接着这个父查询被执行。

5.5.1 带IN关键字的子查询

只有当子查询返回的结果列包含一个值时，比较运算符才适用。
如果一个字查询返回的结果集是值的列表，这时比较运算符就必须用IN运算符代替。

SELECT
	* 
FROM
	ddpl_dept 
WHERE
	`dept_id` IN ( SELECT `dept_id` FROM ddpl_emp WHERE `dept_id` IS NOT NULL );

SELECT
	* 
FROM
	ddpl_dept 
WHERE
	`dept_id` NOT IN ( SELECT `dept_id` FROM ddpl_emp WHERE `dept_id` IS NOT NULL );

5.5.2 带比较运算符的子查询

子查询时可以使用比较运算符，=、!=、>、>=、<、<=等
查询薪资大于平均薪资的员工

SELECT
	* 
FROM
	ddpl_emp 
WHERE
	`emp_salary` > ( SELECT avg( `emp_salary` ) FROM ddpl_emp );

5.5.3 带EXISTS关键字的子查询

在使用EXISTS关键字时，内层查询语句不返回查询的记录，而返回一个真假值。
当返回的值为true时，外层查询语句将进行查询，反之不查询或没有结果。

SELECT
	* 
FROM
	ddpl_dept 
WHERE
	EXISTS ( SELECT * FROM ddpl_emp WHERE `emp_age` < 30 AND `emp_sex` = '女' AND ddpl_emp.dept_id = ddpl_dept.dept_id );
	/*当EXISTS关键字与其他查询条件一起使用时，需要使用AND或OR关键字来连接表达式与EXISTS关键字*/

SELECT
	* 
FROM
	ddpl_dept 
WHERE
	`dept_id` <= 200 
	AND NOT EXISTS ( SELECT * FROM ddpl_emp WHERE `emp_salary` < 2000 AND ddpl_emp.dept_id = ddpl_dept.dept_id );

5.5.4 带ANY、SOME关键字的子查询

ANY、SOME关键字表示满足其中任意一个条件。

SELECT
	* 
FROM
	ddpl_dept 
WHERE
	`dept_id` > ANY ( SELECT `dept_id` FROM ddpl_emp );

SELECT
	* 
FROM
	ddpl_dept 
WHERE
	`dept_id` > SOME ( SELECT `dept_id` FROM ddpl_emp );

5.5.5 带ALL关键字的子查询

ALL关键字表示满足所有条件。

SELECT
	* 
FROM
	ddpl_dept 
WHERE
	`dept_id` > ALL ( SELECT `dept_id` FROM ddpl_emp WHERE `dept_id` IS NOT NULL );

5.6 合并查询结果

合并查询结果是将多个SELECT语句的查询结果合并到一起显示，使用UNION和UNION ALL关键字。

5.6.1 UNION语句

UNION是将所有的查询结果合并到一起，然后去除相同记录。

SELECT
	`dept_id` 
FROM
	ddpl_dept UNION
SELECT
	`dept_id` 
FROM
	ddpl_emp;

5.6.2 UNION ALL语句

UNION ALL只是简单地将结果合并到一起

SELECT
	`dept_id` 
FROM
	ddpl_dept UNION ALL
SELECT
	`dept_id` 
FROM
	ddpl_emp;

5.7 定义表和字段的别名

5.7.1 为表起别名

表名 [AS] 别名

SELECT
	e.`emp_id`,
	e.`emp_name`,
	d.`dept_id`,
	d.`dept_name` 
FROM
	ddpl_emp e,
	ddpl_dept d 
WHERE
	e.dept_id = d.dept_id;

5.7.2 为字段起别名

字段名 [AS] 别名

SELECT
	`emp_id` 编号,
	`emp_name` 姓名,
	`emp_age` 年龄,
	`emp_salary` 薪资,
	`dept_id` 部门编号 
FROM
	ddpl_emp;

5.8 使用正则表达式

正则表达式是用某种模式去匹配一类字符串的一种方式。
字段名 REGEXP ‘匹配方式’：

模式匹配字符	含义	应用举例
^	匹配以特定字符或字符串开头的记录	查询以php开头的记录：SELECT books FROM tb_book WHERE REGEXP ‘^php’;
$	匹配以特定字符或字符串结尾的记录	查询以’模块’结尾的记录：SELECT books FROM tb_book WHERE REGEXP ‘模块$’;
.	匹配字符串的任意一个字符，包括回车和换行	查询包含P字符的记录：SELECT books FROM tb_book WHERE REGEXP ‘P.’;
[字符集合]	匹配“字符集合”中的任意一个字符	程序包含PCA字符的记录：SELECT books FROM tb_book WHERE REGEXP ‘[PCA]’;
[^字符集合]	匹配除“字符集合”以外的任意一个字符	窗查询包含c~z以外的记录：SELECT books FROM tb_book WHERE REGEXP ‘[^c-z]’;
S1|S2|S3	匹配S1、S2和S3中的任意一个字符串	查询包含pht、c或java字符串中任意一个的记录：SELECT books FROM tb_book WHERE REGEXP 'php
*	匹配多个该符号之前的字符，包括0和1个	查询A字符前出现过J字符的记录：SELECT books FROM tb_book WHERE REGEXP ‘J*A’;
+	匹配多个该符号之前的字符，包括1个	窗口A字符前至少出现过1个J字符的记录：SELECT books FROM tb_book WHERE REGEXP ‘J+A’;
字符串{N}	匹配字符串出现N此	查询连续出现3次a字符的记录：SELECT books FROM tb_book WHERE REGEXP ‘a{3}’;
字符串{M,N}	匹配字符串出现至少M次，最多N次	查询最少出现2次，最多出现4次a字符的记录：SELECT books FROM tb_book WHERE REGEXP ‘a{2,4}’;

SELECT * FROM ddpl_emp WHERE `emp_name` REGEXP '[小张]';

5.9 字段基本操作

5.9.1 字段拼接

CONCAT(str1,str2,…)合并(拼接)多个

SELECT
	CONCAT( '你好啊 ', `emp_name`, ' 今天天气怎么样？' ) 
FROM
	ddpl_emp 
WHERE
	`emp_id` = 1;

5.9.2 去除空白字符串

RTRIM(str) 去掉右边的空白字符
LTRIM(str) 去掉右边的空白字符
TRIM(str) 去掉右边的空白字符

SELECT
	RTRIM( '    哥，今天管饱     ' ) 
FROM
	student 
WHERE
	id = 1;

SELECT
	LTRIM( '    哥，今天管饱     ' ) 
FROM
	student 
WHERE
	id = 1;

SELECT
	TRIM( '    哥，今天管饱     ' ) 
FROM
	student 
WHERE
	id = 1;

5.10 计算

SELECT	2+3-4 * 7 / 1.5;

5.11 MySQL8的新特性

5.11.1 GROUP BY不再隐式排序

如果确实需要排序，必须加上ORDER BY子句。

5.11.2 通用表表达式

通用表表达式CTE(Common Table Expressions)。CTE是命名的临时结果集，作用范围是当前语句。
CTE可以理解成一个可以复用的子查询，CTE可以引用其他CTE，但子查询不能引用其他子查询。

6.插入、更新与删除数据

6.1 插入数据

INSERT INTO 数据表名[(column_name1,column_name2,…)] values (value1,value2,…);

6.1.1 为表的所有字段插入数据

INSERT INTO ddpl_user VALUES ( 'Jerry', '86816841' );

6.1.2 为表的指定字段插入数据

INSERT INTO ddpl_user ( `user_name`, `password` ) VALUES ( 'MR', '123' );

6.1.3 同时插入多条数据

INSERT INTO ddpl_user ( `user_name`, `password` )
VALUES
	( 'MR', '123' ),
	( 'Mr', '2681');

6.1.4 将查询结果插入到表中

INSERT INTO table_name1 (COLUMN_list1)
SELECT (COLUMN_list2) FROM TABLE_name2 WHERE (CONDITION);
为员工添加账号：账号名(姓名)，初始密码(姓名@Szy)

INSERT INTO `ddpl_user` (`user_name`, `password`)
SELECT `emp_name` , SHA(CONCAT(`emp_name`,'@Szy')) FROM `ddpl_emp`;

6.2 更新数据

UPDATE 数据表名
SET 列名1 = new_value1, 列名2 = new_value2,…
WHERE 查询条件;

UPDATE ddpl_user 
SET `password` = '7890' 
WHERE
	`user_name` = 'MR';

6.3 删除数据

DELETE
FROM 数据表名
WHERE 查询条件;

DELETE 
FROM
	ddpl_user 
WHERE
	`user_name` = 'HB';

6.4 为表增加计算列

计算列：某一列的值是通过别的列计算得来。
ALTER TABLE 表名 ADD 列名 值类型 [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment] [NOT NULL | NULL] [PRIMARY KEY];

ALTER  TABLE `ddpl_emp` ADD `emp_rate` DOUBLE GENERATED ALWAYS AS (`emp_salary` / `emp_age`) VIRTUAL COMMENT'薪资/年龄';

6.5.MySQL8的新特性——DDL的原子化

在MySQL8,0版本中，INNODB表的DDL支持事务完整性，即DDL操作要么成功要么回滚。
DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log(该表是隐藏的表，通过show tables无法看到)中，用户回滚操作。
通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

7.索引

8.存储过程和函数

9.视图

10.MySQL触发器

11.MySQL权限与安全管理

12.数据备份与恢复

13.MySQL日志

14.性能优化

15.MySQL Replication

16.MySQL Workbench

17.MySQL管理利器——MySQL Urilities

18.读写分离的利器——MySQL Proxy

19.精通MySQL存储引擎

20.Java操作MySQL数据库

21.Python操作MySQL数据库

22.C操作MySQL数据库

23.C#操作MySQL数据库

24.PDO数据库抽象类库

持续更新中……

参考文献

1. 王英英，《MySQL8 从入门到精通：(视频教学版)》,2019