SQL

SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾

2.SQL语句可以使用空格/缩进来增强语句的可读性

3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

4.注释：
单行注释： -- 注释内容 或 # 注释内容（MySQL特有）

多行注释： /* 注释内容 */

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库，表，字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

数据定义语言，用来定义数据库对象（数据库，表，字段）

数据库操作

注：以下操作中[]中的内容属于可以有也可以没有

查询

查询所有数据库：

SHOW DATABASES;

查询当前数据库:

SELECT DATABASE();

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLEATE 排序规则]；

注：字符集中的utf8默认是三个字符，但MySQL中有些符号是四个字符，所以可以使用utf8mb4(没有-)

删除

DROP DATABASE [IF EXISTS] 数据库名;

使用

USE 数据库名;

表操作

创建

CREATE TABLE 表名（

字段1 字段1类型[COMMENT 字段1注释],

字段2 字段2类型[COMMENT 字段2注释],

字段3 字段3类型[COMMENT 字段3注释],

...

字段n 字段n类型[COMMENT 字段n注释]

）[COMMENT 表注释]

注：[...]为可选参数，最后一个字段后面没有逗号

查询

查询当前数据库所有表

SHOW TABLES;

查询表结构

DESC 表名;

查询指定表的建表语句

SHOW CREATE TABLE 表名;

数据类型

数值类型

类型		大小	有符号范围（SIGNED）	无符号范围（UNSIGNED）
整数类型	TINYINT	1byte	（-128，127）	（0，255）
	SMALLINT	2byte
	MEDIUMINT	3byte
	INT或INTGER	4byte
	BIGINT	8byte
浮点类型	FLOAT（单精度浮点数）	4byte
	DOUBLE（双精度浮点数）	8byte
定点数类型	DECTMAL（小数值，精确定点数）	(M+2)byte	依赖于M（精度）和D（标度）的值	依赖于M（精度）和D（标度）的值

注：

1.整数类型无符号的范围是有符号的两倍

浮点类型无符号的范围是有符号的一半（只取正值）

2.(M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。

3.浮点类型可以使用MD，例sorce double(4,1)代表整数位最多有3位，小数位最多有1位

4.UNSIGNED使用时跟在类型后面，例：age TINYINT UNSIGNED

字符串类型

类型	大小	说明
CHAR	0-255byte	定长字符串
VARCHAR	0-65535byte	变长字符串
TINYBLOB	0-255byte
TINYTEXT	0-255byte
BLOB	0-65535byte	二进制形式的文本数据
TEXT	0-65535byte	文本数据
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT

注：

1.CHAR类型和VARCHAR类型定义是需要指定能够存储的最大的字符数量M，例：CHAR(10)，VARCHAR(10)，代表最多能存储10个字符

2.CHAR所占的空间为M个字符所占的空间，未填满的空间会用空格补齐（查询的时候会自动删除这些空格），但CHAR的性能较好

3.VARCHAR所占的空间为所填的字符所占的空间，但VARCHAR的性能较差

4.二进制可以存储视频等其他数据，但性能不好且不好管理，一般使用专门的文件服务器存储，因此BLOB类型的很少使用

日期时间类型

类型	大小	格式	说明
DATA	3byte	YYYY-MM-DD	日期值
TIME	3byte	HH-MM-SS	时间值或持续时间
YEAR	1byte	YYYY	年份值
DATATIME	8byte	YYYY-MM-DD HH-MM-SS	混合日期和时间值
TIMESTAMP	4byte	YYYY-MM-DD HH-MM-SS	混合日期和时间值，时间戳（从1979-1-1到现在的时间）

注：TIMESTAMP时间只到2038-01-19 03：14：07，因此TIMESTAMP和YEAR使用都较少

修改

修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度） [COMMENT 注释] [约束];

删除字段

ALTER TABLE 表名 DROP 字段名;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

删除

删除表

DROP TABLE [IF EXISTS] 表名;

删除指定表，并重新创建该表（相当于清楚表数据，但保留表结构）

TRUNCATE TABLE 表名;

注意：在删除表时，表中的全部数据也会被删除

DML

数据操作语言，用来对数据库表中的数据进行增删改

添加数据

给指定字段添加数据

INSERT INTO 表名 （字段名1,字段名2,...）VALUES （值1,值2,...);

给全部字段添加数据

INSERT INTO 表名 VALUES （值1,值2,...);

批量添加数据

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

INSERT INTO 表名 VALUES（值1,值2,...),（值1,值2,...),(值1,值2,...);

注意事项

1.插入数据时，指定的字段顺序需要与值的顺序一一对应

2.字符串和日期型数据应该包含在引号中

3.插入数据的大小，应该在字段的规定范围内

修改数据

UPDATA 表名 SET 字段名1 = 值1,字段名2 = 值2,... [WHERE 条件];

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

条件可以使用表中的字段指定，例如表中有一个字段id,则id = 1，即可指定所有id数据为1的数据

删除数据

DELETE FROM 表名 [WHERE 条件];

注：

1.DELETE语句的条件可以有也可以没有，如果没有条件，则会删除整张表的所有数据

2.DELETE语句不能删除某一个字段的值（可以使用UPDATA改为null)

DQL

数据查询语言，用来查询数据库中表的记录

SELECT

字段列表

FROM

表名列表

WHERE

条件列表

GROUP BY

分组字段列表

HAVING

分组后条件列表

ORDER BY

排序字段列表

LIMIT

分页参数

基本查询

查询多个字段

SELECT 字段1，字段2，字段3... FROM 表名；

SELECT * FROM 表名；

使用时尽量不使用*，实际开发时公司有规范，按照规范来

设置别名

SELECT 字段1 [AS 别名1]，[AS 别名2]... FROM 表名 [AS 别名1]，[AS 别名2]...；

注：

1.AS可以省略

2.别名只能在当前的查询指令中使用

3.起了别名后就不能使用原名来指定数据了（相当于改名）

4.用汉字起别名的时候引号可加可不加，但是在后续使用时（如where语句中），使用别名不能加引号，否则指代的就不是前面起别名的数据，而是引号内的内容

select chinese + math '总分' from student where chinese + math > 200 order by 总分 desc;
这一句代表的是，将总分大于200的人降序输出

select chinese + math '总分' from student where chinese + math > 200 order by '总分' desc;
这一句代表的是，将总分大于200的人输出
（'总分'不代表前面起别名的数据，而仅仅是一个字符串，因此为默认排序输出，相当于没有排序）

查询并去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

注：

1.distinct后面如果只有一列，会去除这一列中重复的数据

2.如果后面有多列，会去除这些列的数据都一样的数据

3.如果查询的是*，则代表去除重复的行

条件查询（WHERE）

语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件（运算符）

算术运算符

算术运算符	说明
+	加法运算
-	减法运算
*	乘法运算
/ DIV	除法运算，返回商
% 或 MOD	求余运算，返回余数

算术运算符可以直接在查询时在字段名上使用

例：select price + 10 as newprice from product;

代表将所有商品价格上调10元展示（不改变原来的数据）（为price设置了一个别名newprice）

比较运算符

比较运算符	功能
=	等于
> 和 >=	大于和大于等于
< 和 <=	小于和小于等于
<=>	安全的等于，两个操作码均为null时，其所得值为1；而当一个操作码为null时，其所得值为0
<> 或 !=	不等于（<>要放在数的前面，和!=一模一样）
IS NULL 或 ISNULL	判断一个数是不是NULL
IS NOT NULL	判断一个数是否不为NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN ... AND ...	在某个范围之内（含最小、最大值）（前面为最小值，后面为最大值）
IN(...)	判断一个值是否在in之后的列表中，多选一
NOT IN(...)	判断一个值是否不在in之后的列表中，多选一
LIKE 占位符	模糊匹配（_匹配单个字符,%匹配任意个字符）（这两个字符要放在单引号里）（也可以使用具体的值，比如like '%x'代表最后一个值为x的数据（不区分大小写））
REGEXP	正则表达式匹配

逻辑运算符

逻辑运算符	功能
AND 或 &&	逻辑非，并且（多个条件同时成立）
OR 或 ||	逻辑与，或者（多个条件任意一个成立）
NOT 或!	逻辑或，非，不是（将真变为假，假变为真）
XOR	逻辑异或，不同为真，相同为假

注：当需要同时 多个字符 的值 一一匹配时，可以使用括号将几个字段包进去，并使用逗号隔开

SELET * FROM emp WHERE (id,name) = (1,'张无忌')

位运算符（用的较少）

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

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变二进制数，进行位运算。

然后再将运算结果从二进制数变回十进制数。

聚合函数（count、max、min、avg、sum）

介绍

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

常见聚合函数

函数	功能
count	统计数量
max	最大值（如果指定列是字符串类型，那么使用字符串排序运算）
min	最小值（如果指定列是字符串类型，那么使用字符串排序运算）
avg	平均值（如果指定列类型不是数值类型，那么计算结果为0）
sum	求和（如果指定列类型不是数值类型，那么计算结果为0

注：

1.null值不参与所有聚合函数的计算

2.字段列表可以使用*

语法

SELECT 聚合函数（字段列表） FROM 表名;

分组查询(GROUP BY)

语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]

where和having的区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组后对结果进行过滤

判断条件不同：where不能对聚合函数进行判断，而having可以

注意事项

1.WHERE和HAVING都是过滤

2.执行顺序：where -> 聚合函数 ->having

3.分组之后,查询的字段为聚合函数和分组字段，查询其他字段无任何意义

4.分组字段名代表的是该字段下数据相同的会分到一组，相当于一张临时表

5.分组字段名可以是多个字段，则需要这些字段数据都相同的才能分到同一组

排序查询（ORDER BY)

语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2；

排序方式

ASC：升序（默认）

DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

特点

1.order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名

2.order by子句，放在查询语句的最后面，LIMIT子句除外

分页查询（LIMIT）

语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数；

注意事项

1.起始索引从0开始，起始索引=（查询页码 - 1）*每页显示记录数

2.分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

3.如果查询的是第一页的数据，起始索引可以忽略，直接简写为limit n

limit n,m中n可以理解为跳过n条数据（因为初始索引为0），m表示展示多少条数据

n也可以理解为从第n个索引位置处开始

执行顺序

1.FROM 表名列表（先确定获取的是哪张表的数据）

2.WHERE 条件列表（进行第一次过滤）

3.GROUP BY 分组字段列表（进行分组）

4.count（聚合函数）

5.HAVING 分组后条件列表（进行第二次过滤）

6.SELECT 字段列表（检索数据）

7.DISTINCT（去重）

8.ORDER BY 排序字段列表（对得到的数据进行排序）

9.LIMIT 分页参数（最后通过分页展示数据）

注:SELECT在WHERE阶段后面，而给字段取别名这一操作在SELECT中，因此在SELECT中取的别名，在WHERE中不能使用，给表取别名是在FROM阶段，因此给表取得别名在任意阶段都可以使用

INSERT INTO SELECT语句

作用：将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句

格式：
insert into 表2 （字段1，字段2...） select 字段1，字段2... from 表1

或：
insert into 表2 select * from 表1

注：

1.select前面的字段是表2中的字段，后面的字段是表1中的字段

2.该语句是将从表1中查询到的结果导入到表2中（因此可以在表1中通过分组和count来查询每个组的数据的数量，然后导入到表2中，则表2就存储了表1中每个组的数据的数量）

3.导入的数据的类型和表2的数据类型应该相同

4.要求目标表2必须存在

正则表达式

概念

正则表达式（regular expression）描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常用来检索、替换那些符合某个规则的文本。

MySQL通过REGEXP关键字支持正则表达式进行字符串匹配

格式

模式	描述
^	匹配输入字符串的开始位置。例：'^a'代表字符串是否以a开头
$	匹配输入字符串的结束位置。例：'^a'代表字符串是否以a结尾
.	匹配除"\n"(换行符)之外的任何单个字符
[...]	字符集合，匹配所包含的任意一个字符。例："[abc]"可以匹配"plain"中的'a'。
[^...]	赋值字符集合。匹配未包含的任意字符。例："[^abc]"可以匹配"plain"中的'p'。 注：只有[]中的^才代表取反，其他的代表以什么开头
p1|p2|p3	匹配p1或p2或p3。例：'z|food'能匹配"z"或"food"。'(z|f)ood能'匹配"zood"或"food"
()	代表里面的内容作为一个整体，正常情况是否使用()相同，但配合匹配次数（下表）时使用与不使用不同

模式	描述
*	匹配前面的子表达式零次或多次。例：zo*能匹配"z"以及"zo"。等价于{0,}。
+	匹配前面的子表达式一次或多次。例：'zo+'能匹配"zo"以及"zoo"，但不能匹配"z"。等价于{1，}。
?	匹配前面的子表达式零次或一次。例：'a?'能匹配""以及"a"，但不能匹配"aa"。
{n}	n是一个非负整数。匹配确定的n次。例：'o{2}'不能匹配"Bob"中的'o',但能匹配"food"中的两个'o'。
{n,m}	m和n均为非负整数，其中n<=m。最少匹配n次且最多匹配m次。

DCL

数据控制语言，用来创建数据库用户、控制数据库的访问权限

管理用户

查询用户

USE mysql;(用户数据都存储在musql数据库中)

SELECT * FROM user；

创建用户

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

修改用户密码

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

删除用户

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

注意事项

1.主机名可以使用%通配（即可以在任意主机上访问数据库）

2.这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用

权限控制

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

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

查询权限

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

授予权限

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

撤销权限

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

注意事项

1.授权时，数据库名和表名都可以使用*进行通配，代表所有，如 (*.*)

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