• MySQL ｜ 全内容

    MySQL

    相关历史

    最初是瑞典的N有SQK AB 公司，1995年开发

    2008年被SUN公司收购

    2009年 Oracle收购SUN公司，进而MySQL进入Oracle

    2016每年，MySQL 8.0问世

    相关特点

    • 开源的、关系型数据库

    • 支持千万级别的数据量存储，大型的数据库

    DB\DBMS\SQL

    DB:database，看做是数据库文件（类似：.doc、.txt……）

    DBMS：数据库管理系统。（类似于word\wps等工具）

    MySQL数据库中安装了MySQL DBMS，使用MySQL DBMS来管理和操作DB，使用的是SQL语言

    非关系型数据库

    • 键值对类型的：redis
    • 文档型：MangoDB
    • 搜索引擎数据库：ES、Solr
    • 列式数据库：HBase
    • 图形数据库：InfoGrid

    表与表之间的关系

    • ORM 思想
    • 表与表之间的记录关系：一对一、一对多、多对多、自关联

    启动相关命令


    MySQL｜相关命令

    net start MySQL#服务名
net stop  MySQL#服务名

    • 1
    • 2

    MySQL｜登陆

    mysql -uroot  -ppassword
mysql -u root -p
mysql -uroot -p

    • 1
    • 2
    • 3

    -u #用户
-p #密码
-P #端口
-h #host IP

    • 1
    • 2
    • 3
    • 4

    ## 查看版本信息
mysql -v
mysql --version

    • 1
    • 2
    • 3

    注意事项

    -p 与密码之间不可以加空格，别的都行












    基础指令进阶版

    查看数据库支持的编码特性

    show variables like ‘character_%’;_

    show variables like ‘collation_%’;

    数据库级别

    # 查看所有的数据库
show databases;
# 创建新的数据库
create database database_name;
# 使用某个数据库
use database_name;
# 查看所有的表
show tables;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    表级别

    MySQL | 表操作

    show databases; ## 查看所有数据库表
## 使用某个数据库（切换到该数据库下）
use database_name; 
## 查看该数据库下所有的表
show tables;

    • 1
    • 2
    • 3
    • 4
    • 5

    MySQL｜增删改查

    # 插入数据
insert into TABLE_NAME values(1001,'JJ');

# 查看数据
select * from TABLE_NAME;

# 删除数据
delete from TABLE_NAME where id=1001;

# 更新数据
update from TABLE_NAME 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    基础的数据库语句

    • 1946年，世界上第一台电脑诞生，如今，借由这台电脑发展起来的互联网已经自成江湖。在这几十年里，无数的技术、产业在这片江湖里沉浮，有的方兴未艾，有的已经几幕兴衰。但在这片浩荡的波动里，有一门技术从未消失，甚至“老当益壮”，那就是SQL。
    • 45年前，也就是 1974 年，1BM 研究员发布了一篇揭开数据库技术的论文 《SEQUEL：一门结构化的英语查询语言》，直到今天这门结构化的查询语言并没有太大的变化，相比于其他语言，SQL 的半衰期可以说足非常长了。
    • 不论是前端工程师，还是后端算法工程师，都一定会和数据打交道，都需要了解如何又快又准确地提取自己想要的数据。更别提数据分析师了，他们的工作就是和数据打交道，整理不同的报告，以便指导业务决策。
    • SQL (Structured Query Language，结构化查询语言） 是使用关系模型的数据库应用语言，与数据直接打交道，由IBM 上世纪70年代开发出来。后由美国国家标准局 (ANSI） 开始着手制定SQL标准，先后有 SQL-86，SQL-89， SQL-92,SQL-99 等标准。
    • SQL 有两个重要的标准，分别是SQL92和SQL99，它们分别代表了92年和99年颁布的SQL标准，我们今天使用的 SQL语言依然遵循这些标准。
    • 不同的数据库生产厂商都支持SQL语句，但都有特有内容。

    SQL分类

    • DDL (Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
    CREATE # 创建 databases / table / index
DROP	 # 删除表
ALTER	 # 修改表
RENAME # 重命名
TRUNCATE # 清空表

    • 1
    • 2
    • 3
    • 4
    • 5
    • DML (Data Manipulafion Language、数据操作语言)，用于添加、删除、更新和查询数据库记录，并检查数据完整性。

    SELECT也被单独称作是DQL

    INSERT # 插入
DELETE # 删除
UPDATE # 更新、修改
SELECT # 查询

    • 1
    • 2
    • 3
    • 4
    • DCL (Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。

    COMMIT、ROLLBACK又被称作TCL 事务控制语言

    COMMIT 		# 提交
ROLLBACK 	# 回滚
SAVEPOINT # 设置保存点
GRANT			# 赋予权限
REVOKE		# 回收权限

    • 1
    • 2
    • 3
    • 4
    • 5

    SQL 基本规则

    SQL可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进

    • 每条命令以;或/g或/G结束
    • 关键字不能被缩写也不能分行
    • 关于标点符号
      • 必须保证所有的0、单引1号、双引1号是成对结束的
      • 必须使用英文状态下的半角输入方式
      • 字符串型和日期时间类型的数据可以使用单1号（表示列的别名，尽量使用双引号（“”)，而且不建议省略as

    SQL大小写规范

    在Windows上大小写不敏感，在Linux上大小写敏感

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

    SQL 注释信息

    # 单行注释

/*
多行注释
*/

-- 单行注释，--后面必须要加一个空格🈳️

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    命名规则

    • 数据库、表名不得超过30个字符，变量名限制为29个
    • 必须只能包含 A-Z,a-z,0-9，_共63个字符
    • 数据库名、表名、字段名等对象名中间不要包含空格
    • 同个MysQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
    • 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语向中使用 ` （着重号）引起来
    • 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

    Select

    SELECT FROM

    # 在 mysql 命令行中执行
SELECT 1+1,2*3;
# 伪表中查询
SELECT 1+1,2*3 FROM DUAL; 

# 查询全部内容
SELECT * FROM employees; 
# 查询部分内容
SELECT employee_id,last_name,salary FROM employees;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    列的别名

    1. 可以用空格
    2. 可以用AS
    3. 可以用一对""双引号引起来（）
    SELECT employee_id emp_id,salary sary from employees;
SELECT employee_id AS emp_id,salary AS sary from employees;
SELECT employee_id "emp_id",salary "sary" from employees;

    • 1
    • 2
    • 3

    去除重复行 | DISTINCT

    # 加上 DISTINCT 去重
SELECT DISTINCT id AS "ID" FROM employees;

    • 1
    • 2

    空值参与运算

    空值参与运算无论加减乘除都是nul

    SELECT id*1000 "ID",name FROM employees;

    • 1

    是null的地方参与运算之后还是null

    着重号

    用于区分关键字与表名

    SELECT * FROM `ORDER`;

    • 1

    查询常数

    SELECT '尚硅谷',id,name FROM employees;
SELECT '尚硅谷' AS "公司",id,name FROM employees;

    • 1
    • 2

    显示表结构

    DESC employees;
DESCRIBE employees;

    • 1
    • 2

    使用WHERE 过滤数据

    SELECT * FROM t_admin WHERE id=5;
SELECT * FROM employees WHERE name="Mask";

    • 1
    • 2

    常用运算符

    +、-、*、/

    加减 ｜+、-｜隐式运算

    SELECT 100+'1' FROM DUAL;
SELECT 100+'a' FROM DUAL;
SELECT 100+null FROM DUAL;

    • 1
    • 2
    • 3

    结果分别为：101、100、null

    当数字放在单引号之内做加减乘除的时候会出发隐式运算，所以结果是101，但是'a'不是一个数字，无法做转换

    乘除 ｜*、/｜ 除法默认带小数点

    SELECT 100,100*1,100*1.0,100/1.0,100/2,100+2*5/2,100/3,100 DIV 0 FROM DUAL;

    • 1

    SQL做除法默认带小数位

    取模运算 ｜ 结果仅与被模数有关

    SELECT 12%3,12%5,12 MOD -5,-12%5,-12%-5 FROM DUAL;

    • 1

    若是a % b，则结果是正还是负数与a有关，与b无关

    运算符

    比较运算符

    字符串与字符之间做比较，那么也会触发隐式转换。

    运算符内容运算符内容
    =等于<=>安全等于
    !=不等于<>不等于
    <小于<=小于等于
    >大于>=大于等于
    SELECT 1=2,1!=2,1<=>2,1<>2,1='1',1='a',0='a' FROM DUAL;

    • 1

    结果：0 1 0 1 1 0 1

    SELECT 'a'='a','ab'='ab','a'='b' FROM DUAL;

    • 1

    结果：1 1 0

    select 1=NULL,NULL=NULL from DUAL;

    • 1

    结果：null null

    SELECT * FROM employees WHERE id=null;

    • 1

    ❌ 错误的写法 ❌

    SELECT * FROM employees WHERE id<=>null;

    • 1

    ✅ 正确的写法 ✅ 使用安全等于<=> ✅

    关键字

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

    IS NULL

    SELECT * FROM employees WHERE ISNULL(id) AND name IS NOT NULL;
SELECT * FROM employees WHERE id IS NULL AND name IS NOT NULL;
SELECT * FROM employees WHERE id <=> NULL AND name IS NOT NULL;

    • 1
    • 2
    • 3

    LEAST / GREATEST

    SELECT LEAST('A','B','C','D','E'),GREATEST('A','B','C','D','E');
# ↑ A E
SELECT LEAST(id,name) FROM employees;

    • 1
    • 2
    • 3

    BETWEEN AND

    BETWEEN 0 AND 5的范围是[0,5] 而非 [0,5)或者其他。

    SELECT id  FROM employees WHERE id BETWEEN 1002 AND 1003;
SELECT id FROM employees WHERE id >= 1002 AND ID <=1003;

    • 1
    • 2

    IN

    SELECT id  FROM employees WHERE id IN(1002,1003);
SELECT id  FROM employees WHERE id = 1002 OR id = 1003;

    • 1
    • 2

    NOT IN

    SELECT id  FROM employees WHERE id NOT IN(1002,1003);
SELECT id  FROM employees WHERE id != 1002 OR id != 1003;
SELECT id  FROM employees WHERE NOT id = 1002 OR NOT id = 1003;

    • 1
    • 2
    • 3

    LIKE

    # Jack Mask
SELECT * FROM employees WHERE name LIKE '%a%';

# 以 a 开头
SELECT * FROM employees WHERE name LIKE 'a%';
# 以 a 结尾
SELECT * FROM employees WHERE name LIKE '%a';

### 包含 a 或者包含 e
SELECT * FROM employees WHERE name LIKE '%a%' AND name LIKE '%c%';
### a 必须在 e 前面
SELECT * FROM employees WHERE name LIKE '%a%e%';

### 查询第二个字符是 a 的名字
SELECT * FROM employees WHERE name LIKE '_a%';

### 查询第二个字符是下划线且第三个字符是 a 的信息 使用转义字符
SELECT * FROM employees WHERE name LIKE '_\_a%';
SELECT * FROM employees WHERE name LIKE '_$_a%' ESCAPE '$';
# ESCAPE 意思是将`$`作为转义字符

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    一些字符

    占位符

    %：0 个或多个字符

    _：一个字符

    转义字符

    \：普通转义字符

    关键字ESCAPE定义转义字符

    正则表达式 REGEXP、RLIKE

    正则表达式跳转连接

    逻辑运算符

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

    XOR逻辑异或：只要 A 与 B 一真一假，那么结果就为真，一样则为假

    AND可以与OR一起参与运算，但是AND的优先级要高于OR;

    ## 先运算 A AND B 与 C AND D，然后再 OR
A AND B OR C AND D;

    • 1
    • 2

    位运算符

    运算符作 用示 例
    &按位与SELECT A & B
    |按位或SELECT A | B
    ^按位异或SELECT A ^ B
    ~按位取反SELECT ~ B
    >>按位右移SELECT A >> 2
    <<按位左移SELECT A << 2

    排序与分页

    排序 ORDER BY | ASC | DESC

    默认升序 （ASC） | DESC 是降序

    ## DESC 降序排列
SELECT * FROM employees ORDER BY id;
SELECT * FROM employees ORDER BY id DESC;

    • 1
    • 2
    • 3

    分页 | LIMIT

    可以用在 MySQL | PGSQL | MariaDB | SQLite

    SELECT * FROM employees LIMIT 0,3;
SELECT * FROM employees ORDER BY id LIMIT 4,3;

    • 1
    • 2

    多表查询

    笛卡尔积错误，每个数据都跟另一个表里的每一个数据做了组合。

    SELECT empid,depname FROM employees,dep WHERE dep.depid = employees.empid;
# 两个表的链接条件
SELECT id,name FROM employees,department WHERE employees.id = department.id;
# 报错 解决方案
SELECT employees.id,department.name FROM employees,department WHERE employees.id = department.id;

    • 1
    • 2
    • 3
    • 4
    • 5

    从SQL优化的角度出发，建议每个字段前都指明他所在的表

    可以在SELECT和WHERE中给表去别名。

    SELECT emp.id,dep.name 
FROM employees emp,department dep
WHERE emp.id = dep.id;

    • 1
    • 2
    • 3

    注意，取别名之后要全部都用别名

    连接方式

    等值连接 vs 非等值连接 BETWEEN AND

    自连接 vs 非自连接

    • 自己连接自己SELECT * FROM employees;
    • 自己连接别人

    内连接 vs 外连接

    • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
    • 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右） 表中不满足条件的行，这种连接称为左（或右） 外连接。没有匹配的行时，结果表中相应的列为空(NULL)。
    • 如果是左外连接，则连接条件中左边的表也称为 主表，右边的表称为 从表。
      如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为 从表。

    右外连接

    # 左外连接
SELECT empid,depname 
FROM employees e,dep d
WHERE e.`empid`=d.depid;

# MySQL 不支持的做法 ()
SELECT empid,depname 
FROM employees e,dep d
WHERE e.`empid`=d.depid(+);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    左外连接

    ## SQL99 内连接
SELECT last_name,department_name,city 
FROM employees e JOIN department d
ON e.`employee_id` = d.`department_id`
JOIN locations l 
ON d.`location_id` = l.`location_id`;

SELECT last_name,department_name,city 
FROM employees e LEFT OUTER  JOIN department d
ON e.`employee_id` = d.`department_id`
JOIN locations l 
ON d.`location_id` = l.`location_id`;

SELECT last_name,department_name,city 
FROM employees e RIGHT OUTER  JOIN department d
ON e.`employee_id` = d.`department_id`
JOIN locations l 
ON d.`location_id` = l.`location_id`;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    如何实现满外连接？ UNION

    合并查询结果

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

    语法格式：

    SELECT COLUMN ... FROM table1 
UNION(ALL)
SELECT COLUMN ... FROM table12;

    • 1
    • 2
    • 3

    中间图 | 内连接

    SELECT empid,depname 
FROM employees e JOIN dep d
WHERE e.`empid`=d.`depid`;

    • 1
    • 2
    • 3

    左上图 | 左外连接

    SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;

    • 1
    • 2
    • 3

    右上图 | 右外连接

    SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;

    • 1
    • 2
    • 3

    左中图

    SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE d.`depid` IS NULL;

    • 1
    • 2
    • 3
    • 4

    右中图

    SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE e.`depid` IS NULL;

    • 1
    • 2
    • 3
    • 4

    左下图 | 满外连接

    方式一 | 左上图 UNION ALL 右中图
    SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`

UNION ALL

SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE e.`depid` IS NULL;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    方式二 | 左中图 UNION ALL 右上图
    SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE d.`depid` IS NULL
UNION ALL
SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    右下图

    左中图 UNION ALL 右中图

    SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE d.`depid` IS NULL
UNION ALL 
SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE e.`depid` IS NULL;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    SQL99 语法新特性

    自然连接

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

    SELECT empid,depname 
FROM employees e NATURAL JOIN dep d;

    • 1
    • 2

    USING

    当两个表中字段名字一样的时候，可以直接用 USING( )

    SELECT e.id,d.name 
FROM employees e JOIN dep d
USING(id);

    • 1
    • 2
    • 3

    函数

    单行函数

    函数作用备注
    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

    一些函数

    SELECT ABS(-5),ABS(4),SIGN(10),SIGN(-10),SIGN(0),FLOOR(32.32),CEIL(32.32),CEILING(-43.32),CEILING(-43.32),CEILING(32.32),FLOOR(-43.32),MOD(12,5)  FROM DUAL;
## 5	4	1	-1	0	32	33	-43	-43	33	-44	2

    • 1
    • 2

    RAND | 随机数

    相同的 x 会导致结果相同

    SELECT RAND(),RAND(),RAND(0),RAND(0),RAND(10),RAND(10),RAND(-1),RAND(-1);
# 0.11622921760774145	0.1567088152810082	0.15522042769493574	0.15522042769493574	0.6570515219653505	0.6570515219653505	0.9050373219931845	0.9050373219931845

    • 1
    • 2

    ROUND | 四舍五入

    SELECT ROUND(125.555),ROUND(125.49),ROUND(-12.56),ROUND(-12.49),ROUND(123.456,1),ROUND(123.456,2),ROUND(123.456,-1),ROUND(123.456,-2);
# 126	125	-13	-12	123.5	123.46	120	100

    • 1
    • 2

    TRUNCATE | 截断操作

    SELECT TRUNCATE(123.456,0),TRUNCATE(123.456,1),TRUNCATE(123.456,-1) FROM DUAL;
# 123	123.4	120

    • 1
    • 2

    单行函数的嵌套

    SELECT TRUNCATE(ROUND(123.456,2),0) FROM DUAL;
# 123 

    • 1
    • 2

    三角函数

    函数作用备注
    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 为弧度值
    SELECT SIN(5),ACOS(0.6) FROM DUAL;
# -0.9589242746631385	0.9272952180016123

    • 1
    • 2

    指数 与 对数

    函数用法备注
    POW(x,y) POWER(x,y)返回 x 的 y 次方
    EXP(x)返回 e 的 x 次方ex
    LN(x),LOG(x)返回以 e 为底的 x 的对数，当 x≤0 时，返回结果是NULL
    LOG10(x)返回结果以 10 为底的 X 的对数，当 X≤0 是，返回的结果为NULL
    LOG2(x)返回以 2 为底的 x 的对数，当 x≤0 时，返回NULL
    SELECT POW(2,3),POWER(2,4),EXP(2) FROM  DUAL;
# 8	16	7.38905609893065

    • 1
    • 2

    EXP(x) 是ex

    mysql> SELECT EXP(2);
+------------------+
| EXP(2)           |
+------------------+
| 7.38905609893065 |
+------------------+
1 row in set (0.01 sec)

mysql> SELECT LN(EXP(2));
+------------+
| LN(EXP(2)) |
+------------+
|          2 |
+------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    进制转换函数

    函数作用备注
    BIN(x)返回 x 的二进制
    OCT(x)返回 x 的八进制
    HEX(x)返回 x 的十六进制
    CONV(x,f1,f2)返回 f1 进制变成 f2 进制
    SELECT BIN(123089471290387402) FROM DUAL;
# 110110101010011010011100011010011110110101010101111001010
SELECT CONV(15,16,8) FROM DUAL;
# 25

    • 1
    • 2
    • 3
    • 4

    字符串函数

    函数作用备注
    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)同上，但是每个字符串之间要加上 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)字符串总长度为 10，不满的在左侧插入 pad（右对齐）
    RPAD(str,len,pad)字符串总长度为 10，不满的在右侧插入 pad（左对齐）
    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)比较两个字符串，若是相等则返回 NULL，否则返回value1
    SELECT ASCII('abc'),CHAR_LENGTH('hello'),LENGTH('hello'),CHAR_LENGTH('你好'),LENGTH('你好') FROM DUAL;
# 97	5	5	2	6

    • 1
    • 2

    注意：

    • 中文的 你好 的LENGTH为5，你好的 CHAR_LENGTH 为2
    • 字符串的索引是从1开始的
    SELECT TRIM('    hel      lo    ');
# `hel      lo`
SELECT REVERSE('ajksfghdfjuyagesufgkavdcsdekuy');
# yukedscdvakgfusegayujfdhgfskja

    • 1
    • 2
    • 3
    • 4

    ⌚️日期和时间函数

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

    日期与时间戳转换

    函数作用备注
    UNIX_TIMESTAMP()以Unix时间戳的形式返回房钱时间。SELECT UNIX_TIMESTAMP();–>1661173047
    UNIX_YIMESTAMP(date)将时间date以Unix时间戳的形式返回
    FROM_UNIXTIME(timestamp)将UNIX时间戳的时间转换为普通格式的时间

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

    函数用法备注
    YEAR(date) / MONTH(date) / DAY(date)返回具体的日期值
    HOUR(time) / MINUTE(time) / SECOND(time)返回具体的时间值
    MONTHNAME(date)返回月份：January，…
    DAYNAME(date)返回星期几，注意：周一是0，周二是1…周日是6
    QUARTER(date)返回日期对应的季节，范围是1～4
    WEEK(date),WEEKOFYEAR(date)返回一年中的第几周
    DAYOFYEAR(date)返回日期是一年中的第几天
    DAYOFMONTH(date)返回日期位于所在月份的第几天
    DAYOFWEEK(date)返回洲际，注意：周日是1，周一是2…周六是7

    日期的操作函数

    函数
    EXTRACT(type FROM date)返回指定日期的特定部分，type值返回值的类型
    Type 类型作用Type 类型作用
    MICROSECOND毫秒数SECOND秒
    MINUTE分钟HOUR小时
    DAY天WEEK日期在一年中的第几个星期
    MONTH日期在一年中的第几个月QUARTER日期在一年中的第几个季度
    YEAR日期的年份SECOND_MICROSECOND返回秒和毫秒值
    MINUTE_MICROSECOND返回分钟和毫秒值MINUTE_SECOND返回分钟和秒值
    HOUR_MICROSECOND返回小时和毫秒值HOUR_SECOND返回小时和秒值
    HOUR_MINUTE返回小时和分钟值DAY_MICROSECOND返回日期和毫秒值
    DAY_SECOND返回日期和秒值DAY_MINUTE返回日期和分钟值
    DAY_HOUR返回日期和小时值YEAR_MONTH返回年和月

    时间和秒钟转换的函数

    函数作用备注
    TIME_TO_SEC(time)将time转化为秒并返回结果值。转化的公式为：小时*3600+分钟*60+秒
    SEC_TO_TIME(time)将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时间间隔的日斯

    日期的格式化与解析

    函数作用备注
    DATE_FORMATE(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(vaine,value1,value2)如果value的值为TRUE，返回value1，否则
    返回value2
    IFNULL(value1, value2)如果value1不为NULL，返回value1，否则返
    回value2
    CASE WHEN 条件1THEN结果1WHEN 条件2THEN 结果2…ELSE
    resultn] END    		相当于Java的i.else if…else…
    CASE expr WHEN 常量值1THEN 值1 WHEN 常量值1THEN 值1…
    ELSE (En] END    		相当于Java的switch…case.

    IF

    SELECT IF(1,2,3),IFNULL(123,456),IFNULL(NULL,456);
# 2	123	456
SELECT name,salary,IF(salary>6000,'高工资','低工资') "DETAILS" FROM employees;

    • 1
    • 2
    • 3

    CASE WHEN

    SELECT salary,CASE WHEN salary>=15000 THEN '白骨精'
									 WHEN	salary>=10000 THEN '潜力股'
									 WHEN	salary>=8000 THEN '还不错'
									 ELSE '小屌丝' END
FROM employees;								

    • 1
    • 2
    • 3
    • 4
    • 5

    CASE expr WHEN

    SELECT salary,CASE department WHEN 10 THEN salary*1.1
									 WHEN	20 THEN salary*1.2
									 WHEN	30 THEN salary*1.3
									 ELSE salary*1.4 END "Salary"
FROM employees;		

    • 1
    • 2
    • 3
    • 4
    • 5

    加密

    PASSWORD

    在 MySQL8.0中已经弃用

     SELECT PASSWORD('MySQL') FROM DUAL;

    • 1

    MD5 | SHA

    不可逆的加密方式

    SELECT MD5('MySQL'),SHA('MySQL') FROM DUAL;

    • 1

    | 62a004b95946bb97541afa471dcca73a | deaa0c393a6613972aaccbf1fecfdad67aa21e88 |

    ENCODE ｜ DECODE 加密解密

    在 MySQL8.0中已经弃用

    函数作用函数
    ENCODE(value,password)使用password作为加密密码加密value
    DECODE(value,password)使用password作为解密密码解密value

    MySQL 信息函数

    函数作用备注
    VERSION()返回当前MySQL版本号
    CONNECTION_ID()返回当前MySQL服务器连接数
    DATABASE() / SCHEMA()返回MySQL命令行当前所在的数据库
    USER() / CURRENT_USER() / SYSTEM_USER() / SESSION_USER()返回链接MySQL的用户名，返回结果为用户@IP
    CHARSET(value)返回字符串value自变量的字符集
    COLLATION(value)返回字符串value的比较规则

    其他函数

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

    8.0 聚合函数

    我们上一章讲到了 SQL 单行函数。实际上 SQL 函数还有一类，叫做聚合（或緊集、分组） 函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

    8.1 常用聚合函数

    函数作用备注
    AVG( )求平均值AVG = SUM / COUNT
    SUM( )求和
    MAX( )求最大值数字、字符串、日期时间类型
    MIN( )求最小值数字、字符串、日期时间类型
    COUNT( )统计个数不统计NULL

    COUNT: 常数 * 字段

    如果要用COUNT(字段)，该选择什么方式

    问：在MysQL 中统计数据表的行数，可以使用三种方式： SELECT COUNT（)、SELECT COUNT(1) 和 SELECT COUNT(具体宇段），使用这三者之间的查询效率是怎样的？
    答：
    在 MysQL InnoDB 存储号1擎中，COUNT(    ） 和COUNT(1)都是对所有结果进行 COUNT。如果有 WHERE 子句，则是对所有符合筛选条件的数据行进行统计；如果没有 WHERE 子句，则是对数据表的数据行数进行统计。
    因此 COUNT（*）和 COUNT(1） 本质上并没有区别，执行的复杂度都是。(N)，也就是采用全表扫描，进行循环＋计数的方式进行统计。
    如果是 MysQL MyISAM 存储引擎，统计数据表的行数只需要 o(1）的复杂度，这是因为每张 MyISAM 的数据表都有一个meta 信息存储了row-count 值，而一致性则由表级锁来保证。因为 InnoDB 支持事务，采用行级锁和 MVCC机制，所以无法像 MyISAM 一样，只维护一个row_count 变量，因此需要采用扫描全表，进行循环＋计数的方式来完成统计。
    需要注意的是，在实际执行中，COUNT(*）和 COUNT(1） 的执行时间可能略有差别，不过你还是可以把它俩的执
    行效率看成是相等的。
    另外在 InnoDB 引擎中，如果采用 COUNT(*） 和COUNT(1） 来统计数据行数，要尽量采用二级索引。因为主键采的索引是聚族索引，聚筷索引1包含的信息多，明显会大于二级索引（非聚族索引)。对于 COUNT（*）和
    COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引1来进行统计。

    8.2 GROUP BY 的使用

    SELECT中出现的内容一定要出现在GROUP BY中

    声明在 FROM、 WHERE后面，ORDER BY 与 LIMIT前面

    ## 查询各个部门的平均工资
SELECT AVG(salary)
FROM dep
GROUP BY depid;

## 查询
SELECT depid,jobid,AVG(salary)
FROM departments
GROUP BY depid,jobid;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    8.3 HAVING 的使用

    HAVING必须声明在GROUP BY后面

    WHERE 与 HAVING 的对比

    • HAVING的范围更大，可以用WHERE的地方一定可以用HAVING
    • 有 GROUP BY或聚合函数的地方一定要用HAVING
      • WHERE的执行效率要高于HAVING
    ### 错误的写法
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary>10000)
GROUP BY department_id;

## 如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE
SELECT department-id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000；

# 查询部门id为10，20，30，40 这四个部门中最高工资比10000高的部门信息
SELECT department_id,MAX(salary)
FROM employees
WHERE depattment_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    SQL 92 语法

    SELECT ... , ... , ...
FROM ... , ... , ...
WHERE 夺标的链接条件 AND 不包含聚合函数的过滤条件
GROUP BY ... , ...
HAVING 包含聚合函数的过滤条件
ORDER BY ... , ...(ASC / DESC)
LIMIT ... , ...

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    SQL 99 语法

    SELECT ... , ... , ...(存在聚合函数)
FROM ... (LEFT / RIGHT) JOIN ... ON 多表的链接条件
(LEFT / RIGHT) JOIN ... ON 
WHERE 不包含聚合函数的过滤条件
GROUP BY ... , ...
HAVING 包含聚合函数的过滤条件
GROUP BY ... , ...(ASC / DESC)
LIMIT ... , ... 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    🎨 执行顺序

    🎨 执行顺序

    9. 子查询

    举例： 查询工资比 Jim 高的所有的员工；

    方式一 先查一下Jim的工资，再带进去

    SELECT salary
FROM employees
WHERE last_name = 'Jim'

SELECT last_name,salary
FROM employees 
WHERE salary > 1100;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    方式二：自连接

    SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.`salary` > e1.`salary`
AND e1.last_name == 'Jim'

    • 1
    • 2
    • 3
    • 4

    方式三：子查询

    SELECT last_name,salary
FROM employees 
WHERE salary > (
  SELECT salary
  FROM employees
  WHERE last_name = 'Jim'
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    称谓规范：外查询（主查询）、内查询（子查询）、

    ## 可读性比较好
SELECT last_name,salary
FROM employees 
WHERE salary > (
  SELECT salary
  FROM employees
  WHERE last_name = 'Jim'
);

## 可读性较差
SELECT last_name,salary
FROM employees 
WHERE (
  SELECT salary
  FROM employees
  WHERE last_name = 'Jim'
) < salary;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    自查询的分类

    角度1:从内查询返回结果的条目数

    • 单行自查询
    • 多行自查询

    角度2:内查询是否被执行多次

    • 相关自查询（举例：查询工资大于本部门平均工资的 人）
    • 非相关子查询
  • 相关阅读:
    客户收入企稳、亏损收窄，有赞在盛夏“回春”？
    【封装--限定符private--包】
    YOLOv5改进原创 HFAMPAN 结构，信息高阶特征对齐融合和注入，全局融合多级特征，将全局信息注入更高级别
    用AI原生向量数据库Milvus Cloud 搭建一个 AI 聊天机器人
    conda环境安装opencv带cuda版本
    uniapp easycom
    MySQL---多表联合查询（下）（内连接查询、外连接查询、子查询（ALL/ANY/SOME/IN/EXISTS关键字）、自关联查询）
    最小堆提升每次排序的效率
    教培行业迎来重大变局，三大方向或成新机遇
    计算机底层原理
  • 原文地址：https://blog.csdn.net/Hsk_03/article/details/126493559