MySQL ｜ 全内容

已于 2023-04-19 14:28:32 修改
阅读量301 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库
于 2022-08-23 21:47:54 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Hsk_03/article/details/126493559
版权

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#服务名

MySQL｜登陆

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


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


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

注意事项

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












基础指令进阶版

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

show variables like ‘character_%’;_

show variables like ‘collation_%’;

数据库级别

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

表级别

MySQL | 表操作

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

MySQL｜增删改查

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

# 查看数据
select * from TABLE_NAME;

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

# 更新数据
update from TABLE_NAME

基础的数据库语句

  • 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 # 清空表
  • DML (Data Manipulafion Language、数据操作语言)，用于添加、删除、更新和查询数据库记录，并检查数据完整性。

SELECT也被单独称作是DQL

INSERT # 插入
DELETE # 删除
UPDATE # 更新、修改
SELECT # 查询
  • DCL (Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。

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

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

SQL 基本规则

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

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

SQL大小写规范

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

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

SQL 注释信息

# 单行注释

/*
多行注释
*/

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

命名规则

  • 数据库、表名不得超过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. 可以用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;

去除重复行 | DISTINCT

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

空值参与运算

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

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

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

着重号

用于区分关键字与表名

SELECT * FROM `ORDER`;

查询常数

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

显示表结构

DESC employees;
DESCRIBE employees;

使用WHERE 过滤数据

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

常用运算符

+、-、*、/

加减 ｜+、-｜隐式运算

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

结果分别为：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;

SQL做除法默认带小数位

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

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

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

运算符

比较运算符

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

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

结果：0 1 0 1 1 0 1

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

结果：1 1 0

select 1=NULL,NULL=NULL from DUAL;

结果：null null

SELECT * FROM employees WHERE id=null;

❌ 错误的写法 ❌

SELECT * FROM employees WHERE id<=>null;

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

关键字

运 算 符名 称作 用示 例
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;

LEAST / GREATEST

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

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;

IN

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

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;

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 意思是将`$`作为转义字符

一些字符

占位符

%：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;

位运算符

运算符作 用示 例
&按位与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;

分页 | LIMIT

可以用在 MySQL | PGSQL | MariaDB | SQLite

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

多表查询

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

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;

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

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

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

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

连接方式

等值连接 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(+);

左外连接

## 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`;

如何实现满外连接？ UNION

合并查询结果

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

语法格式：

SELECT COLUMN ... FROM table1 
UNION(ALL)
SELECT COLUMN ... FROM table12;
中间图 | 内连接
SELECT empid,depname 
FROM employees e JOIN dep d
WHERE e.`empid`=d.`depid`;
左上图 | 左外连接
SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
右上图 | 右外连接
SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
左中图
SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE d.`depid` IS NULL;
右中图
SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE e.`depid` IS NULL;
左下图 | 满外连接
方式一 | 左上图 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;
方式二 | 左中图 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`;
右下图

左中图 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;

SQL99 语法新特性

自然连接

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

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

USING

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

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

函数

单行函数

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

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

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

TRUNCATE | 截断操作

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

单行函数的嵌套

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

三角函数

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

指数 与 对数

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

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 |
+------------+

进制转换函数

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

字符串函数

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

注意：

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

⌚️日期和时间函数

函数作用备注
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;
CASE WHEN
SELECT salary,CASE WHEN salary>=15000 THEN '白骨精'
									 WHEN	salary>=10000 THEN '潜力股'
									 WHEN	salary>=8000 THEN '还不错'
									 ELSE '小屌丝' END
FROM employees;
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;

加密

PASSWORD

在 MySQL8.0中已经弃用

 SELECT PASSWORD('MySQL') FROM DUAL;

MD5 | SHA

不可逆的加密方式

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

| 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;

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;
SQL 92 语法
SELECT ... , ... , ...
FROM ... , ... , ...
WHERE 夺标的链接条件 AND 不包含聚合函数的过滤条件
GROUP BY ... , ...
HAVING 包含聚合函数的过滤条件
ORDER BY ... , ...(ASC / DESC)
LIMIT ... , ...

SQL 99 语法

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

🎨 执行顺序

🎨 执行顺序

9. 子查询

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

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

SELECT salary
FROM employees
WHERE last_name = 'Jim'

SELECT last_name,salary
FROM employees 
WHERE salary > 1100;

方式二：自连接

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

方式三：子查询

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

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

## 可读性比较好
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:内查询是否被执行多次

  • 相关自查询（举例：查询工资大于本部门平均工资的 人）
  • 非相关子查询
Jim.KK
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL ----索引
520_LH
08-28 294
1：索引的定义： 索引是帮助MySql高效获取数据的有序数据结构，在数据库系统维护数据之外，还维护着满足特定算法的数据结构，这种数据结构以某种的特定的方式指向数据，这样就可以在这些数据上实现高级算法，这就是索引。 2：索引的优缺点 2.1 索引类似于书籍的目录，提高数据的查询速度，降低数据库的IO 2.2 通过索引对数据进行排序，降低数据库排序成本，降低cpu消耗 2.3 索引实际上可以算作一张保存了主键和指向实体类的一些记录，需要占用物理空间 2.4 索引同时会降低操作数...
关系数据库(六)：MySQL查询数据以及where、like子句(正则)
Hanscal
04-13 2591
查询数据 语法 # 以下为在MySQL数据库中查询数据通用的 SELECT 语法： SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M] 查询语句中可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。 SELECT 命令可以读取一条或者多条记录。 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据 你可以使用 WHERE 语
MySQL_查询
星航夜空的帆舟
10-21 2746
一、单表查询 1.1 带 in / not in 关键字的查询  语法规则为： select 字段名 from 表名 where 字段名 in / not in (n1,n2,n3,…);  示例: 查询当Id不等于1时，字段Name和Salary的内容。 select Name,Salary from tb_emp where Id not in(1); 1.2 带 (not)between and 的范围查询  语法规则为：select 字段名 from 表名 where 字段名 between
MySQL，一些认识
Crazy_Cw的博客
11-04 458
MySQL 的逻辑架构 MySQL逻辑架构图主要分三层： （1）第一层负责连接处理，授权认证，安全等等 （2）第二层负责编译并优化SQL （3）第三层是存储引擎。 一条SQL查询语句执行过程 mysql分为server层与存储引擎层，server层包含连接器、分析器、优化器、执行器。 首先由连接器进行身份验证，权限管理 若开启了缓存，会检查缓存是否有该sql对应结果（缓存存储形式key-vlaue，key是执行的sql，value是对应的值）若开启缓存又有该sql的映射，将结果直接返回； 分析
DBHelper完善查询
weixin_51051290的博客
10-14 193
DBHelper中有关查询的，并且完善 params可变化的 数组params SqlParameter[] sqlParameters public static DataTable ExeQuery(string sql,params SqlParameter[] sqlParameters) { using (SqlDataAdapter sda = new SqlDataAdapter(sql, connter)) {
Mysql | binlog|data-569541.pdf
12-14
4. 查看binlog内容：`mysqlbinlog`是MySQL提供的一个工具，用于查看和解析binlog文件的内容。通过`mysqlbinlog mysql-bin.000117`命令可以查看指定binlog文件的具体事件，但请注意，如果文件不存在或权限不足，会...
在mysql数据库原有字段后增加新内容
10-29
### 在MySQL数据库原有字段后增加新内容 在MySQL数据库中，有时我们可能需要在已有的字段数据基础上添加新的内容。这种需求通常出现在需要扩展现有记录信息的场景中，例如为用户账号追加权限标识、更新产品描述等。...
mysql 替换字段部分内容及mysql 替换函数replace()
09-08
在实际数据库操作中，`REPLACE()`函数常用于更新表中的字段内容。假设你有一个名为`users`的表，其中有一个`email`字段，你想将所有包含`'example.com'`的电子邮件地址的`'example'`部分替换为`'test'`，你可以执行...
mysql修改字段内容语句
09-06
mysql修改数据库内对应表格对应字段的SQL语句
mysql教学内容
04-23
mysql教学内容mysql教学内容mysql教学内容mysql教学内容
mysql一些经典sql_MySQL经典SQL语句
weixin_39620278的博客
02-02 83
经典Mysql语句SQL分类：DDL—数据定义语言(Create，Alter，Drop，DECLARE)DML—数据操纵语言(Select，Delete，Update，Insert)DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)首先,简要介绍基础语句：1、创建数据库Create DATABASE database-name2、删除数据库drop database ...
mysql在数据库中查询语句,MySQL 数据库基本查询语句汇总
weixin_39542043的博客
03-23 275
1)INBETWEEN AND>SELECT IDD FROM table1WHERE (AGE BETWEEN 19 AND 21) AND (SEX IN ('Male')); # 结果如下图22)LIKE>SELECT IDD FROM table1WHERE (NAME LIKE 'Z%') OR (NAME LIKE 'ZH_...
2021-06-05
qq_41902369的博客
06-05 81
模糊查询是否走索引 -- 走索引 select * from tableA where a like 'aa%'； select * from tableA where a like 'aa_'； -- 不走索引 select * from tableA where a like '%aa'； select * from tableA where a like '%aa%'； select * from tableA where a like '_aa%'； 查看索引的方式 -- MySql explai
mysql select from table where_mysql – “SELECT * FROM view_table WHERE cause”=>...
weixin_42164534的博客
02-01 114
在SELECT * FROM view_table中结果始终为-----------------------|| id || author ||-----------------------|| 1 || a || || 1 || c || || 1 || b || || 2 || d |||| 3...
Linux（九）MySQL中的where和having，表连接，连表联查，试图
L缶神的博客
03-11 1575
where 和 having where和having都是用做筛选 where:处理元数据(from读取的数据) having:对from读取数据的处理结果进行再次筛选 where->group by ->having select *,age-18 as c from student where c>2; # 报错 select *,age-18 as c from student having c>2; # 正确 select sex,count(*) as c fr
mysql语句之select查询
weixin_42214393的博客
05-28 6156
一、select基础查询语句。1、select简单条件查询：select  * from table where 条件where后可以接的运算符有：2、当我们无法确定查询的准确内容时，用模糊查询是一种很好的查询方式。select * from table1 where field1 like ’value1%’3、select也可对字段进行排序。（单字段、多字段都可以，以多字段为例）select ...
【MySQL数据库】常见知识点总结
m0_46628950的博客
07-18 629
【MySQL数据库】常见知识点总结
数据库-面试题（持续更新）
热门推荐
04-21 1万+
来自牛客网的汇总1. MySQL查询时，只有满足联接条件的记录才包含在查询结果，这种联接是（内联接）。内联接：典型的联接运算，使用像 = 或 <> 之类的比较运算符。包括相等联接和自然联接。内联接使用比较运算符根据每一表共有的列的值匹配两个表中的行。例如，检索 students 和 courses 表中学生标识号相同的所有行。外联接：外联接可以是左向外联接、右向外联接或完整外部联接。 在FROM子
mysql使用like分组_（5）MySQL的查询：模糊查询（通配符查询like）、限制符查询（limit）、排序查询（order by）、分组查询（group by）、（子查询）...
weixin_35348630的博客
01-19 1064
注意事项指令语法的优先级： where > group by >order by > limit例：select count(id) as cnt,age from tablename where id > 6 group by age having cnt < 2 order by age desc limit 2,5PS:查找tablename表中age列从id列...
information_schema | | mysql | | performance_schema
最新发布
01-10
如果你想查看information_schema中的mysql表和performance_schema表的内容，可以使用以下SQL语句： ```sql SELECT * FROM information_schema.tables WHERE table_schema = 'information_schema'; SELECT * FROM ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值