Zhong__MySQL笔记

时间：2022.01.02

环境：Windows、Linux

目的：MySQL笔记

说明：

作者：Zhong QQ交流群：121160124 欢迎加入！

安装

卸载

Windows系统

停止服务

删除

Linux系统

查看版本

连接登录

数据库

查看所有数据库

创建数据库

删除数据库

修改数据库

数据库结构信息

查看编码

表

查看所有表

创建表

删除表

修改表

查看表结构信息

CREATE增

DELETE删

UPDATE改

SELECT查

as别名

DISTINCT去重

空值null

``反引号

常数

WHERE过滤

运算符

比较运算符

=

<=>

<>

!=

<

<=

>

>=

like(模糊查询)

ISNULL

IS NULL

IS NOT NULL

LEAST最小的

GREATEST最大的

BETWEEN AND(在之间)

in(在)/not in

and/or

运算符

算术运算符

+(加)

-(减)

*(乘)

/(DIV)(除)

%(MOD)(取模)

比较运算符

=(等于)

<=>(安全等于)

<>(!=)(不等于)

<(小于)

<=(小于等于)

>(大于)

>=(大于等于)

逻辑运算符

NOT(!)(非)

AND(&&)(与)

OR(||)(或)

XOR(异或)

位运算符

&(按位与/位AND)

|(按位或/位OR)

^(按位异或/位XOR)

~(按位取反)

>>(按位右移)

<<(按位左移)

运算符优先级

正则表达式查询

^(匹配文本的开始字符)

$(匹配文本的结束字符)

.(匹配任何单个字符)

*(匹配零个或多个在它前面的字符)

+(匹配前面的字符1次或多次)

<字符串>(匹配包含指定的字符串的文本)

[字符集合](匹配字符集合中的任何一个字符)

[^](匹配不在括号中的任何字符)

字符串{n,}(匹配前面的字符串至少n次)

字符串{n,m}(匹配前面的字符串至少n次 至多m次 如果n为0 此参数为可选参数)

排序与分页

排序(ORDER BY)

ASC(升序)

DESC(降序)

多列排序(多级排序)

首先根据age升序 如果age相同的根据name升序

分页(LIMIT)

note

表关系

一对一

一对多

多对多

多表查询(关联查询)

等值连接/非等值连接

自连接/非自连接

内连接(INNER JOIN)/外连接(OUTER JOIN)

note

UNION(合并查询结果)

UNION

UNION ALL

7种SQL JOINS

中图：内连接 A∩B

左上图：左外连接

右上图：右外连接

左中图：A - A∩B

右中图：B-A∩B

右下图

左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）

语法格式

函数

内置函数

单行函数

数值函数

字符串函数

日期/时间函数

流程控制函数

加密与解密函数

信息函数

其他函数

聚合函数

AVG(平均值)

SUM(和值)

MAX(最大值)

MIN(最小值)

COUNT(计数)

GROUP BY

note

其它

DUAL(伪表)

笛卡尔积(交叉连接)

数据导入与导出

规则与规范

常见问题

安装

官网下载最新的版本 mysql community server 选择对应的版本

windows建议下载msi格式安装包 Linux下载tar.gz包

卸载

Windows系统

停止服务

通过管理服务停止mysql服务

删除

• 通过控制面板卸载
• 通过360等工具卸载
• 通过mysql提供的工具卸载

如果想彻底删除可以删除mysql数据文件(配置文件、数据库和表等文件)、环境变量、注册表(可选，用于早期的版本)

完成上述步骤后如果即时要重装MySQL时最好重启下电脑

Linux系统

查看版本

mysql -V

连接登录

mysql -uroot -p mysql -uroot -p123456 -hlocalhost -P3306 # 指定ip地址和端口

数据库

查看所有数据库

show databases;

选择数据库

use db1;

创建数据库

create database db1;

删除数据库

drop database db1;

修改数据库

数据库结构信息

show create database db1;

查看编码

show variables like 'character_%'; show variables like 'collation_%';

表

查看所有表

show tables;

创建表

create table tb1(id int, name varchar(20));

删除表

drop table tb1;

修改表

修改表字符编码

alter table tb1 charset utf8mb4;

查看表结构信息

查看创建语句、表信息

show create table tb1;

查看表结构(字段)信息

DESC tb1;

CREATE增

insert into tb1 values(1, "hongzhenying");

DELETE删

UPDATE改

SELECT查

as别名

可以使用空格/as定义

select username name,userage as age,usersex "user sex" from users;

DISTINCT去重

单字段去重

SELECT DISTINCT `name` FROM tb1;

多字段去重 默认为多字段联合唯一

SELECT DISTINCT `sex`,`age` FROM tb1;

空值null

null不等于0/""/"/'null' 代表为空 参与运算时结果也为null

``反引号

``可用于强调内容不是关键字如order是一张表

select * from `order`;

常数

常数会作为一列填充

SELECT sex,age,"歌手" FROM tb1;

WHERE过滤

SELECT * FROM tb1 WHERE age = 18;

运算符

+、-、*、/(DIV)、%(MOD)、>、<、=

可在sql中使用加减乘除等运算符

SELECT `sex`, age * 0.5 age FROM tb1;

比较运算符

=

等于

<=>

安全的等于 针对Null使用

SELECT * FROM tb1 WHERE age = NULL; # age为Null的不会查询到 SELECT * FROM tb1 WHERE age <=> NULL; # 等于 SELECT * FROM tb1 WHERE age is NULL; 都可以查询为Null的数据

<>

!=

<

<=

>

>=

like(模糊查询)

直接使用like效果等于=

SELECT * FROM `users` WHERE name LIKE "周杰伦";

%like% 包含'周'字的

SELECT * FROM `users` WHERE name LIKE "%周%";

%like 以'周'字结尾的

SELECT * FROM `users` WHERE name LIKE "%周";

like% 以'周'字开始的

SELECT * FROM `users` WHERE name LIKE "周%";

_ 占位符 查询第二个字符为'杰'的 多个字符可用多个_占位

SELECT * FROM `users` WHERE name LIKE "_杰%";

\转义_ 查询第二个字符为'杰'的且第三个字符为'_'

SELECT * FROM `users` WHERE name LIKE "_杰\_%";

ISNULL

ISNULL是一个函数

IS NULL

查询age为Null的

SELECT * FROM tb1 WHERE age is NULL;

IS NOT NULL

查询age不为Null的

SELECT * FROM tb1 WHERE age IS NOT NULL;

LEAST最小的

查询最小的

SELECT LEAST('a','b','c');

GREATEST最大的

查询最大的

SELECT GREATEST('a',1,'c');

BETWEEN AND(在之间)

select * from tb1 where age between 8 and 18;

in(在)/not in

select * from tb1 where age in (18);

and/or

运算符

算术运算符

+(加)

-(减)

*(乘)

/(DIV)(除)

%(MOD)(取模)

比较运算符

=(等于)

<=>(安全等于)

<>(!=)(不等于)

<(小于)

<=(小于等于)

>(大于)

>=(大于等于)

逻辑运算符

NOT(!)(非)

AND(&&)(与)

OR(||)(或)

XOR(异或)

位运算符

&(按位与/位AND)

|(按位或/位OR)

^(按位异或/位XOR)

~(按位取反)

>>(按位右移)

<<(按位左移)

运算符优先级

正则表达式查询

^(匹配文本的开始字符)

$(匹配文本的结束字符)

.(匹配任何单个字符)

*(匹配零个或多个在它前面的字符)

+(匹配前面的字符1次或多次)

<字符串>(匹配包含指定的字符串的文本)

[字符集合](匹配字符集合中的任何一个字符)

[^](匹配不在括号中的任何字符)

字符串{n,}(匹配前面的字符串至少n次)

字符串{n,m}(匹配前面的字符串至少n次 至多m次 如果n为0 此参数为可选参数)

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合

要求的特殊字符串。例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户

输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常

复杂的查询。

MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配

列表

排序与分页

排序(ORDER BY)

使用ORDER BY启用排序 ASC/DESC来指定规则 默认ASC

ASC(升序)

从小到大排序

SELECT * FROM `users` ORDER BY age ASC;

DESC(降序)

从大到小排序

SELECT * FROM `users` ORDER BY age DESC;

单列排序(一级排序)

根据age升序

SELECT * FROM `users` ORDER BY age ASC;

多列排序(多级排序)

首先根据age升序 如果age相同的根据name降序

SELECT * FROM `users` ORDER BY age ASC, name DESC;

首先根据age升序 如果age相同的根据name升序

SELECT * FROM `users` ORDER BY age,name ASC;

分页(LIMIT)

公式：LIMIT (pageNum-1) * pageSize, pageSize

LIMIT m, n LIMIT 从第m条开始, 取n条数据

每页显示10条数据 获取第一页数据

SELECT * FROM `users` LIMIT 0,10;

每页显示10条数据 获取第二页数据

SELECT * FROM `users` LIMIT 10,10;

note

顺序

FROM ... ORDER BY ... LIMIT

MySQL8.x新特性OFFSET

LIMIT 2,10 等于 LIMIT 10 OFFSET 2

表关系

一对一

一表对一表

自关联

一对多

多对多

多表查询(关联查询)

建议对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。

等值连接/非等值连接

等值连接

不同的表使用值是否一致(=)比较连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。

SELECT * FROM users u,dep d WHERE u.name = d.name; # SELECT * FROM users AS u,dep AS d WHERE u.name = d.name;

非等值连接

不是(=)的条件 过滤查询

查询users表中age范围在students表中的min_age与max_age范围内数据

SELECT * FROM users AS u,students AS s WHERE u.age BETWEEN s.min_age AND s.max_age;

自连接/非自连接

内连接(INNER JOIN)/外连接(OUTER JOIN)

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

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

行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。

如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

SELECT CONCAT(u.name, "--", u1.name) FROM users AS u,users AS u1 WHERE u.id = u1.id;

note

表连接的约束条件可以有三种方式：WHERE, ON, USING

WHERE：适用于所有关联查询

ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起

写，但分开写可读性更好。

USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字

段值相等

UNION(合并查询结果)

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并

时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION

ALL关键字分隔。

UNION

UNION会去重

SELECT * FROM users UNION SELECT * FROM users;

UNION ALL

UNION ALL不去重

SELECT * FROM users UNION ALL SELECT * FROM users;

7种SQL JOINS

中图：内连接 A∩B

SELECT employee_id,last_name,department_name

FROM employees e JOIN departments d

ON e.`department_id` = d.`department_id`;

左上图：左外连接

SELECT employee_id,last_name,department_name

FROM employees e LEFT JOIN departments d

ON e.`department_id` = d.`department_id`;

右上图：右外连接

SELECT employee_id,last_name,department_name

FROM employees e RIGHT JOIN departments d

ON e.`department_id` = d.`department_id`;

左中图：A - A∩B

SELECT employee_id,last_name,department_name

FROM employees e LEFT JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE d.`department_id` IS NULL

右中图：B-A∩B

SELECT employee_id,last_name,department_name

FROM employees e RIGHT JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE e.`department_id` IS NULL

SELECT employee_id,last_name,department_name

FROM employees e LEFT JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE d.`department_id` IS NULL

UNION ALL #没有去重操作，效率高

SELECT employee_id,last_name,department_name

FROM employees e RIGHT JOIN departments d

ON e.`department_id` = d.`department_id`;

右下图

左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）

SELECT employee_id,last_name,department_name

FROM employees e LEFT JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE d.`department_id` IS NULL

UNION ALL

SELECT employee_id,last_name,department_name

FROM employees e RIGHT JOIN departments d

ON e.`department_id` = d.`department_id`

WHERE e.`department_id` IS NULL

语法格式

函数

SQL提供了内置函数 另外可以自定义函数

内置函数

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

e.g.

SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),SIGN(0),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),FLOOR(-43.23),FLOOR(-43.91),MOD(12,5) FROM DUAL;

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

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

函数	用法
RADIANS(x)	将角度转化为弧度，其中，参数x为角度值
DEGREES(x)	将弧度转化为角度，其中，参数x为弧度值

e.g.

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

三角函数

函数	用法
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为弧度值

e.g.

SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1)) FROM DUAL;

科学函数

函数	用法
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

e.g.

SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4) FROM DUAL;

进制转换

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

e.g.

SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8) FROM DUAL;

字符串函数

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

函数	用法
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

e.g.

SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma') FROM DUAL;

SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '') FROM DUAL;

日期/时间函数

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

日期与时间戳的转换

函数	用法
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

e.g.

SELECT UNIX_TIMESTAMP(now()); SELECT UNIX_TIMESTAMP(CURDATE()); SELECT UNIX_TIMESTAMP(CURTIME()); SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11');

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

函数	用法
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

日期的操作函数

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

EXTRACT(type FROM date)函数中type的取值与含义

e.g.

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

时间和秒转换的函数

函数	用法
TIME_TO_SEC(time)	将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟 *60+秒
SEC_TO_TIME(seconds)	将 seconds 描述转化为包含小时、分钟和秒的时间

计算日期和时间的函数

函数	用法
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时间间隔的 日期

函数	用法
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后的时间

e.g.

SELECT ADDTIME( NOW( ), 20 ), SUBTIME( NOW( ), 30 ), SUBTIME( NOW( ), '1:1:3' ), DATEDIFF( NOW( ), '2021-10- 01' ), TIMEDIFF( NOW( ), '2021-10-25 22:10:10' ), FROM_DAYS( 366 ), TO_DAYS( '0000-12-25' ), LAST_DAY( NOW( ) ), MAKEDATE( YEAR ( NOW( ) ), 12 ), MAKETIME( 10, 21, 23 ), PERIOD_ADD( 20200101010101, 10 ) FROM DUAL;

日期的格式化与解析

函数	用法
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进行解析，解析为一个日期

流程控制函数

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

MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

函数	用法
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	相当于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END	相当于Java的switch...case...

e.g.

SELECT IF(1 > 0,'正确','错误'); # 如果条件成立那么a否则b SELECT IFNULL(null,'Hello Word'); # 如果条件为null那么a SELECT CASE WHEN 1 > 0 THEN '1 > 0' ELSE '3 > 0' END; SELECT CASE 1 WHEN 1 THEN '我是1' WHEN 2 THEN '我是2' ELSE "你是谁" END; SELECT name,score, CASE WHEN score >= 92 THEN "good" WHEN score < 92 AND score > 82 THEN "generiac" ELSE "Come On" END "评级" FROM users;

加密与解密函数

函数	用法
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

e.g.

SELECT md5('123'); SELECT SHA('Tom123');

信息函数

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

对数据库进行维护工作。

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

其他函数

函数	用法
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

聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

AVG(平均值)

可以对数值型数据使用AVG函数

SUM(和值)

可以对数值型数据使用SUM 函数

MAX(最大值)

可以对任意数据类型的数据使用MAX函数

MIN(最小值)

可以对任意数据类型的数据使用MIN函数

COUNT(计数)

COUNT(*)/COUNT(1)/COUNT(列名)

用哪个呢？对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。

count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行

COUNT(*)返回表中记录总数，适用于任意数据类型

GROUP BY

SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中 在GROUP BY子句中的列不必包含在SELECT列表中

HAVING

过滤分组

1. 行已经被分组。

2. 使用了聚合函数。

3. 满足HAVING 子句中条件的分组将被显示。

4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

note

WHERE和HAVING的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。

区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

|        | 优点                         | 缺点                                   |

| ------ | ---------------------------- | -------------------------------------- |

| WHERE  | 先筛选数据再关联，执行效率高 | 不能使用分组中的计算函数进行筛选       |

| HAVING | 可以使用分组中的计算函数     | 在最后的结果集中进行筛选，执行效率较低 |

其它

DUAL(伪表)

select "age",1+1 from DUAL;

笛卡尔积(交叉连接)

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能

组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素

个数的乘积数。

users表有多条数据 dep表只有一条数据

SELECT * FROM users,dep;

为了避免笛卡尔积 可以加上WHERE连接条件

SELECT * FROM users,dep WHERE users.name = dep.name;

数据导入与导出

source <sql_path>

使用Navicat

规则与规范

字符串类型和日期时间类型的数据使用单引号表示

列的别名尽量使用双引号表示，不建议省略as

数据库名、表名、表的别名、变量名是严格区分大小写的

关键字、函数名、列名/字段名、列的别名/字段的别名是忽略大小写的

数据库名、表名、表的别名、字段名、字段的别名等建议都小写

SQL关键字、函数名、绑定变量等建议都大写

列的别名只能在ORDER BY排序使用 不能在WHERE使用

常见问题

MySQL8.x采用新的密码加密方式，如果图形化工具连接报错可以升级工具版本或者还原数据库兼容5.7方式的加密方式，如果是程序的话就要看情况了例如Java有提供对应的依赖插件，Django没有！