MySQL（一）

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 数据库定义语言

DDL-数据库操作

• 查询
  • 查询所有数据库

SHOW DATABASES;

• 查询当前数据库

SELECT DATABASE();

• 创建

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

• 删除

DROP DATABASE[IF EXISTS] 数据库名

• 使用

USE 数据库名

DDL-表操作-查询

• 查询当前数据库的所有表

SHOW TABLES;

• 查询表结构

DESC 表名;

• 查询指定表的建表语句

SHOW CREATE TABLE 表名

DDL-表操作-创建

CREATE TABLE 表名(
    字段1 字段1类型[COMMENT 字段1注释],
    字段2 字段2类型[COMMENT 字段2注释],
    字段3 字段3类型[COMMENT 字段2注释],
    字段n 字段n类型[COMMENT 字段2注释]
)[COMMENT 表注释];

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

DDL-表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

数值类型

类型	大小	描述
TINYINT	1 byte	微整数值
SMALLINT	2 byte	小整数值
MEDIUMINT	3 byte	中整数值
INT或INTEGER	4 byte	大整数值
BIGINT	8 byte	极大整数值
FLOAT	4 byte	单精度浮点数值
DOUBLE	8 byte	双精度浮点数值
DECIMAL		小数值（精确定点数）

无符号（不存在负数）时的范围有符号时范围的两倍左右，若想使用该类型的无符号形式，则需要在类型后面声明unsigned如下所示：

age TINYINT UNSIGNED

使用浮点型时可以在类型后面添加括号，向括号中添加两个参数指定浮点型显示格式，第一个指定小数点前的位数，第二个指定小数点后的位数

字符类型

类型	大小	描述
CHAR	0-255 bytes	定长字符串，性能好占用高
VARCHAR	0-65535 bytes	变长字符串，性能低占用低
TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的长文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

日期类型

类型	大小	范围	格式	描述
DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	4	1901 至2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970:-01-01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

DDL-表操作-修改

• 添加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束]

• 修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

• 修改字段名和字段类型

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

• 删除字段

ALTER TABLE 表名 DROP 字段名;

• 修改表名

ALTER TABLE 表名 RENAME TO 新表名;

• 删除表

DROP TABLE{IF EXISTS} 表名;

• 删除指定表，并重新创建该表

TRUNCATE TABLE 表名;

在删除表时，表中的全部数据也会被删除。

DML 数据操作语言

DML-介绍

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作。

• 添加数据（INSERT）
• 修改数据（UPDATE）
• 删除数据（DELETE）

DML-添加数据

• 给指定字段添加数据

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

• 给全部字段添加数据

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

• 批量添加数据

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

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

插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的指定范围内。

DML-修改数据

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

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

DML-删除数据

DELETE FROM 表名 [WHERE 条件]

DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
DELETE语句不能删除某一个字段的值（可以使用UPDATE）。

DQL 数据查询语言

DQL-介绍

DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库中表的记录。
查询关键字：SELECT

DQL语法

SELECT
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

• 基本查询
• 条件查询（WHERE）
• 聚合查询（count、max、min、avg、sum）
• 分组查询（GROUP BY）
• 排序查询（ORDER BY）
• 分页查询（LIMIT）

DQL-基本查询

• 查询多个字段

SELECT 字段1,字段2,字段3... FROM 表名;

• 设置别名

SELECT 字段1[AS 别名1],字段2[AS 别名2]... FROM 表名;

• 去重复记录

SELECT DISTINCT 字段列表 FROM 表名;

DQL-条件查询

• 语法

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

• 条件
  条件可以是比较和逻辑运算，常用的运算符如下：

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN...AND...	在某个范围之内(含最大、最小值)
IN(...)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL	是NULL

逻辑运算符	功能
AND 或 &&	并且（多个条件同时成立）
OR 或||	或者（多个条件任意一个成立）
NOT 或 !	非，不是

DQL-聚合函数

• 介绍
  将一些列数据作为一个整体，进行纵向计算。
• 常见聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

• 语法

SELECT 聚合函数(字段列表) FROM 表名;

null值不参与所有的聚合函数运算

DQL-分组查询

• 语法

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

• WHERE与HAVING 区别
  • 执行时机不同：WHERE是分组之前进行过滤，不满足WHER条件，不参与分组；而HAVING是分组之后对比结果进行过滤。
  • 判断条件不同：WHERE不能对聚合函数进行判断，而HAVING可以。

执行顺序：WHERE > 聚合函数 > having。
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

• 语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 ,字段2 排序方式2;

• 排序方式
  • ASC：升序（默认值）
  • DESC：降序

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

DQL-分页查询

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

起始索引从0开始，起始索引=（查询页码-1）*每页显示记录。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为LIMIT 10。

DQL 执行顺序

• 编写顺序

SELECT
        字段列表
FROM
        表名列表
WHERE
        条件列表
GROUP BY
        分组字段列表
HAVING
        分组后条件列表
ORDER BY
        排序字段列表
LIMIT
        分页参数

• 执行顺序

FROM
        表名列表
WHERE
        条件列表
GROUP BY
        分组字段列表
HAVING
        分组后条件列表
SELECT
        字段列表
ORDER BY
        排序字段列表
LIMIT
        分页参数

总结

• DQl语句
  
  
  

DCL-介绍

DCL英文全称是Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问 权限。

DCL 管理用户

• 查询用户

USE mysql;
SELECT * FROM user;

• 创建用户

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

• 修改用户密码

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

• 删除用户

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

主机名可以使用%通配。
这类SQL开发人员操作的比较少，主要是DBA(Database Administrator 数据库管理员)使用

DCL-权限控制

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

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

• 查询权限

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

• 授予权限

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

• 撤销权限

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