本文的SQL语法以MySQL为例

1. 简介

1.1. SQL语言

SQL，Structured Query Language，结构化查询语言，是一种用于管理和操作关系型数据库的标准化编程语言

1.2. SQL语法概览

1.3. SQL语法

MySQL 语句不区分大小写。但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置。例如：SELECT与 select 、Select 是相同的

多条 SQL 语句必须以分号;分隔

处理 SQL 语句时，所有空格都被忽略

SQL 语句可以写成一行，也可以分写为多行

-- 一行
UPDATE user SET username='robot', password='robot' WHERE username = 'root';
-- 多行 SQL 语句
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';

MySQL 支持三种注释

• #注释1
• -- 注释2，--后面是一个空格
• /*注释3*/

MySQL中反引号、单引号、双引号的区别

反引号：用于引用标识符，如表名、列名等。它的作用是将标识符包裹起来，以便在标识符中包含特殊字符或保留字。使用反引号可以确保MySQL将标识符视为字面值，而不是关键字

单引号：用于引用字符串值。字符串值是由单引号括起来的文本

双引号：双引号也可以用于引用字符串值，但它不是MySQL的默认字符串引号，需要通过启用ANSI_QUOTES SQL模式来启用。在ANSI_QUOTES模式下，双引号用于引用字符串值，而单引号用于引用字符值

1.4. SQL分类

数据操作语言DML

主要功能是访问数据，因此其语法都是以读写数据库为主

/* 这四个指令合称 CRUD,即增删改查 */
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据
SELECT - 从数据库表中获取数据

数据定义语言DDL

主要功能是定义数据库对象

CREATE DATABASE - 创建新数据库

ALTER DATABASE - 修改数据库

CREATE TABLE - 创建新表

ALTER TABLE - 变更（改变）数据库表

DROP TABLE - 删除表

CREATE INDEX - 创建索引（搜索键）

DROP INDEX - 删除索引

数据控制语言DCL

以控制用户的访问权限为主

grant 语句给用户增加权限

revoke 语句收回用户权限

事务控制语言TCL

用于管理数据库中的事务

commit 语句提交事务

rollback 语句回滚事务

2. 数据库操作

2.1. 操作数据库

选择一个数据库

use 数据库名;

创建一个数据库

create database if not exists `数据库名`;

删除一个数据库

drop database if exists `数据库名`;

查询所有数据库名

show databases;

2.2. 数据库的类型

数据表在物理空间的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

MySQL引擎在物理文件上的区别

InnoDB在数据库表中只有一个 *.frm文件，以及上级目录下的ibdata1文件

MYISAM对应文件 *.frm表结构的定义文件 、*.MYD数据文件(data)
*.MYI索引文件(index)

设置数据库表的字符集编码

不设置的话，会是mysql默认的字符集编码 （不支持中文！）

MySQL 字符编码集中有两套 UTF-8 编码实现：utf8 和 utf8mb4（UTF-8 的完整实现，最多支持使用 4 个字节表示字符，因此可以用来存储 emoji 符号）

在建表时设置（建议）
CHARSET=utf8mb4
或
在my.ini中配置默认的编码
character-set-server=utf8mb4

设置数据库表的排序规则

ci（case-insensitive）表示不区分大小写，cs（case-sensitive）表示区分大小写，bin（binary）表示二进制比较

一般使用 ci即可

在建表时设置（建议）
COLLATE utf8mb4_unicode_ci
或
在my.ini中配置
collation-server=utf8mb4_unicode_ci

3. 数据表操作

3.1. 数据表的列类型

数值

int 标准整数 4字节，在设计数据表时，所设置的长度，表示该字段可以容纳的最大数字位数
bigint 较大的数据
float 浮点数 4字节
double 浮点数
decimal 字符串形式的浮点数（金融计算一般用这个）

字符串

char 固定占用10个字符的存储空间
varchar 可变长字符串，在设计数据表时，所设置的长度，是指该字段可以容纳的最大字符数
tinytext 微型文本 
text 文本串

时间日期

date YYYY-MM-DD
time HH:mm:ss
datetime YYYY-MM-DD HH:mm:ss （最常用的时间格式）
timestamp 时间戳 1970.1.1到现在的毫秒数（也常用）
year 年份

null

没有值、未知
不要用null进行运算，结果为null

3.2. 数据表的约束

SQL 约束用于规定表中的数据规则

NOT NULL - 指示某列不能存储 NULL 值

UNIQUE - 保证某列的每行必须有唯一的值

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性

CHECK - 保证列中的值符合指定的条件

DEFAULT - 规定没有给列赋值时的默认值

Unsigned - 无符号整数

zerofill - 不足的位则用0填充

自增

自动在上一条记录的基础上＋1
通常用来设计唯一的主键，必须是整数类型
可自定义设计主键自增的起始值和步长

3.3. 操作数据表

直接使用数据库.表名来指定某数据库中的某个表

select * from mysql.user;

查所有数据表

show tables;

查表中字段

show columns from 表名
或
show create table 表名;
或
describe 表名;

创建数据表（建议为每个表定义一个主键）

-- 每张表中都要有一个主键，通常为id
CREATE TABLE [IF NOT EXISTS] `表名` (
`字段名` 列类型 [属性] [索引] [注释]，
`字段名` 列类型 [属性] [索引] [注释]，
`字段名` 列类型 [属性] [索引] [注释],
primary key(`id`)
) [表类型] [字符集设置] [注释];

如：
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR (20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

修改表名

alter table 旧表名 rename as 新表名;

修改表的字段名

alter table 表名 change  旧字段名 新字段名 列属性;

修改表的字段的数据类型

alter table 表名 modify 字段名 数据类型;

添加表的字段

alter table 表名 add 新字段名 列属性;

删除表的字段

alter table 表名 drop 字段名;

删除数据表

drop table if exists 表名;

4. CRUD操作

所有的创建和删除操作尽量加上判断，以免报错，如 if exists的判断

4.1. 插入

插入查询出来的数据

INSERT INTO user(username)
SELECT name
FROM account;

插入行的一部分

INSERT INTO user(username, password, email)
VALUES ('admin', 'admin', 'xxxx@163.com');
-- 可以同时插入多条数据，VALUES后面的值使用逗号隔开即可values(),().....

4.2. 更新

UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';

4.3. 删除

delete和truncate都是用于删除数据库表中数据的SQL语句，但是它们有以下几个区别： 1 . 对表结构的影响:

• DELETE 语句只删除表中的数据行，而不会删除表的结构（表名、列等）。
• TRUNCATE 语句会删除表中的所有数据行，并重置表的自增长列的计数器。同时也会释放表占用的存储空间。

2 . 执行速度:

• TRUNCATE 语句通常比 DELETE 语句执行得更快，因为它只需要重置表的数据页，而不需要遍历整个表来删除每一行数据。

3 . 事务日志记录:

• DELETE 语句会将每个被删除的行记录到事务日志中，因此可以进行回滚操作。
• TRUNCATE 语句会直接从磁盘中删除数据页，不会记录到事务日志中，因此无法进行回滚。

4 . WHERE 子句:

• DELETE 语句可以使用 WHERE 子句来有选择地删除满足条件的行。
• TRUNCATE 语句无法使用 WHERE 子句，它会删除整个表的所有数据行。

总的来说，如果需要删除表中的全部数据，并且不需要保留删除记录，那么使用 TRUNCATE 语句更加高效。如果需要有选择地删除部分数据行，并且需要保留删除记录以便于回滚，那么使用 DELETE 语句更合适。

delete删除的问题，重启数据库，现象

InnoDB 自增列会重1开始 (存在内存当中的，断电即失)
MyISAM继续从上一个自增量开始(存在文件中的，不会丢失)

4.4. 查询

SELECT：用于从数据库中查询数据。

DISTINCT：用于去重查询，作用于所有列。在 SELECT 语句中使用 DISTINCT，它会确保返回的结果集中没有重复的行。它作用于所有列，也就是说所有列的值都相同才算相同

SELECT DISTINCT column1, column2, ...
FROM table_name;

LIMIT：用于限制返回的行数，可以有一个或两个参数。

• 一个参数：表示返回的行数。
• 两个参数：第一个参数表示起始行（从 0 开始），第二个参数表示返回的总行数

ORDER BY：用于对结果集进行排序，默认升序，可以指定降序。

• ASC ：升序（默认）
• DESC ：降序

  5. 外键（不建议使用）

外键是一种用于建立表之间关系的约束。它指定了一个表中的列，该列的值必须与另一个表中的列的值匹配。这种匹配通常是基于主键和外键之间的关系进行的

在SQL中，可以使用FOREIGN KEY关键字来定义外键约束

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

以上示例中，创建了一个名为 orders 的表，其中包含一个名为 order_id 的列和一个名为 customer_id 的列。还定义了一个外键约束，该约束指定 customer_id 列必须与 customers 表中的 customer_id 列匹配

当向orders 表中插入数据时，如果customer_id 列的值不在customers 表中，则会出现外键约束错误。这可以确保数据的完整性和一致性

以上操作是物理外键，数据库级别的外键，不建议使用（数据库插入和删除都会麻烦、异常）

推荐方式：想使用外键通过程序去实现

6. 查询数据

通配符

*  匹配任意一个或多个字符
_  匹配任意一个字符
%  匹配任意一个字符串

注：若要匹配的字符串中有 %、_ 则需要转义

在下面的代码中，我们使用了LIKE关键字进行模糊匹配，并使用了%和_通配符。同时，我们使用了ESCAPE关键字指定了转义符为\。因此，在LIKE子句中，我们需要使用\%和\_对%和_进行转义，以避免它们被解释为通配符

String sql = "SELECT * FROM student WHERE name LIKE '%\\_%' ESCAPE '\\'";

逻辑运算符

6.1. 单表查询

查询所有字段

select * from 表名

查询指定字段

select 字段名1,字段名2 from 表名

where 等值查询，where过滤指定的行，后面不能加聚合函数（分组函数）

select 字段名1,字段名2 from 表名 where id=1;

distinct 去重查询

select distinct 字段名 from 表名;

起别名

AS 给结果起一个名字、可以给字段起别名、也可以给表起别名
例
select 字段名1 as newname from 表名;

select 表达式 from 表; 其中表达式的内容可以是

文本值，列，Null,函数，计算表达式，系统变量...
例
查询当前数据库版本
select version();

查看数据库文件存放的目录
select @@datadir;

查询MySQL服务的安装路径
select @@basedir;

全部学生成绩+1
select studentResult + 1 AS '成绩+1' from result;

6.2. 模糊查询

例 select studentResult from result where name in (a,b,c);

6.3. 联表查询（常用）

在SQL中使用多个表的数据来生成结果集的查询

两张表中的字段名冲突时，用表名.字段名的方法解决

join关键字左边的是左表，右边的是右表

内联接（INNER JOIN）

返回两个表中满足连接条件的记录

左联接（LEFT JOIN）

返回左表中的所有记录，以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则结果集中的此部分将为 NULL

右联接（RIGHT JOIN）

返回右表中的所有记录，以及左表中满足连接条件的记录。如果左表中没有匹配的记录，则结果集中的此部分将为 NULL

自联接

自己的表和自己连接，核心：通过别名来将一张表视为两张表

SELECT a.columns, b.columns
FROM table a
INNER JOIN table b
ON a.column = b.column;

注意：只写JOIN会由MySQL自动决定是左联接还是右联接

例

/*从学生表和成绩表中查询成绩大于60分的学生的姓名和成绩*/
select score.score,student.name 
from score 
inner join student 
on score.student_id=student.student_id 
where score >60;

6.4. 分页查询

物理分页

物理分页是指在数据库层面上进行分页，即只返回指定页的数据，而不是返回整个结果集。这种分页方式通常用于处理大型数据集，因为它可以避免在内存中加载整个结果集

逻辑分页

逻辑分页是指在应用程序层面上进行分页，即返回整个结果集，然后在应用程序中对结果集进行分页。这种分页方式通常用于处理较小的数据集，因为它可以提供更好的用户体验

两种分页方式对比

物理分页每次都访问数据库，逻辑分页只访问一次数据库，物理分页对数据库造成的负担大

逻辑分页一次性将数据读取到内存，占用了较大的内容空间，物理分页每次只读取一部分数据，占用内存空间较小

逻辑分页一次性将数据读取到内存，数据发生改变，数据库的最新状态不能实时反映到操作中，实时性差。物理分页每次需要数据时都访问数据库，能够获取数据库的最新状态，实时性强。

逻辑分页主要用于数据量不大、数据稳定的场合，物理分页主要用于数据量较大、更新频繁的场合

LIMIT属于物理分页

LIMIT子句有一个参数时，limit 3，表示查询前3条记录

LIMIT子句有两个参数时，limit N,M 相当于 limit M offset N，从第 N 条记录开始，返回 M 条记录

LIMIT子句指定要返回的行数，OFFSET子句指定要跳过的行数（默认为0），对于 LIMIT 子句中的值，必须是常量，不得使用变量

/*从table_name表中返回10行，跳过前20行*/
SELECT * FROM table_name LIMIT 10 OFFSET 20;

常用的分页模板

/*常用的分页模板*/
limit(查询起始下标,pageSize)  
即 limit((n-1)*pageSize,pageSize)
/*n是当前页数，pageSize是页面大小*/

6.5. 排序

order by

order by result asc 升序
order by result desc 降序

6.6. 子查询（常用）

子查询就是指将一个 select查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件

select studentNo from student 
where Name = 
(select studentID from result where grade=80) 

6.7. 分组查询

GROUP BY子句用于对具有相同值的行进行分组。它通常与聚合函数（如COUNT、SUM、AVG、MAX和MIN）一起使用，以对每个行组执行计算

having：过滤分组，一般都是和 group by 连用，不能单独使用

以下是GROUP BY子句的一些常见用途：

聚合数据：使用GROUP BY子句计算行组的汇总统计信息。例如，使用GROUP BY在销售表中为每个产品类别计算总销售额

过滤数据：使用GROUP BY子句过滤掉不符合某些条件的行。例如，使用GROUP BY在销售表中查找下订单最多的客户

排序数据：使用GROUP BY子句按一个或多个列对查询结果进行排序。例如，使用GROUP BY按产品类别排序销售表，然后按每个类别内的总销售额排序

 /*按班级分组，并且统计每个班级的学生数，最后过滤出学生数大于3的班级*/
select class_id,count(name) from student group by class_id having count(name) > 3;

6.8. 联合查询

UNION 操作符用于合并两个或多个SELECT 语句的结果集

UNION操作的基本规则：

• 所有查询的列数和列顺序必须相同
• 每个查询中涉及表的列的数据类型必须相同或兼容
• 通常返回的列名取自第一个查询

注意：UNION 内部的SELECT 语句必须拥有相同数量的列。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。列也必须拥有相似的数据类型。同时，每条SELECT 语句中的列的顺序必须相同。

/*version()的查询结果是int型，且只有一列，所以 union 之后的那个select查询结果也必须只有一列，而且数据类型也需要类似 int*/
select version() union select id from users;

6.9. select完整语法（重点）

select [all | distinct]
from tablename1 [as newname]
[left | right | inner join tablename2] -- 联表查询
[where] -- 条件查询
[group by] -- 指定结果按照哪几个字段来分组
[having] -- 过滤分组的记录必须满足的次要条件
[order by] -- 指定查询记录按一个或多个条件排序
[limit] -- 指定查询的记录从哪条至哪条

以上先后顺序不能改变
[]括号代表可选的, { }括号代表必选得

7. MySQL常见函数

7.1. 数学运算

SELECT ABS (-8) --绝对值
SELECT CEILING(9.4) --向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(10) --判断一个数的符号，0-0，负数返回-1，正数返回1

7.2. 字符串函数

字符串拼接函数

concat(str1,str2 … … ) 没有分隔符的连接字符串

select concat(id,'-',name) from users;
/*结果为 id-name 的拼接*/

如果连接串中存在NULL，则返回结果为NULL

select concat(id,NULL,name) from users;
/*结果为 NULL*/

concat_ws(separator,str1,str2… … )该函数的第一个参数是其他参数的分隔符，分隔符在连接字符串之间加入。分隔符可以是一个字符串，也可以是其它参数。分隔符为NULL，则返回结果为NULL。如果参数中存在NULL，则会被忽略

select concat_ws('-',id,name,password) from users;
/*结果为 id - name - password 的拼接*/

group_concat(str1,str2… … )将多行查询的结果以逗号为分隔符连接成一行结果

select group_concat(id,name,password) from users;
/*所有结果显示在一行*/

字符串截取函数

left()

从左开始截取字符串 left(str,length) ，str要截取的字符串， length要截取的长度

right()

从右开始截取字符

substring(str,pos,length)

str被截取字段， pos从第几位开始截取， length截取长度

substring_index(str,delim,count)

按关键字截取字符串

str被截取字段， delim关键字 ，count关键字出现的次数

7.3. 时间和日期

SELECT CURRENT DATE() --获取当前日期
SELECT CURDATE() --获取当前日期
SELECT NOW()--获取当前的时间
SELECT LOCALTIME() --本地时间
SELECT SYSDATE() --系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

7.4. 系统

SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

7.5. 聚合函数

SELECT COUNT(字段名) FROM 表名
SELECT AVG(字段名) FROM 表名;
SELECT SUM(字段名) FROM 表名;

7.6. MD5

md5()

如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值

8. 事务

事务是一组SQL语句，它们被视为单个逻辑单元并且必须全部执行或全部回滚。事务可以确保数据库的一致性和完整性，即使在出现故障或其他问题的情况下也是如此

在SQL中，可以使用BEGIN TRANSACTION、COMMIT和ROLLBACK语句来管理事务

BEGIN TRANSACTION;

UPDATE table_name SET column1 = value1 WHERE condition;
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
DELETE FROM table_name WHERE condition;

COMMIT;

以上例子中，使用BEGIN TRANSACTION 语句开始一个新的事务。然后执行一些SQL语句，包括UPDATE、INSERT和DELETE语句。如果所有语句都成功执行，则我们使用COMMIT语句提交事务。如果出现错误或其他问题，则可以使用ROLLBACK语句回滚事务，以便撤消所有更改

8.1. 事务的四个特性（ACID）

原子性（Atomicity）

事务被视为单个逻辑单元，必须全部执行或全部回滚。如果事务中的任何一部分失败，则整个事务都将回滚到原始状态

一致性（Consistency）

事务必须确保数据库的一致性和完整性。这意味着在事务开始和结束时，数据库必须处于一致的状态（数据总量不变）。如果事务失败，则必须回滚所有更改，以便数据库保持一致

隔离性（Isolation）

针对多个用户同时操作。事务必须在隔离的环境中执行，以便它们不会相互干扰。这意味着在事务执行期间，其他事务不能访问正在使用的数据

持久性（Durability）

一旦事务提交，其结果必须永久保存在数据库中。即使在出现故障或其他问题的情况下，也必须保持数据的完整性和一致性

隔离所导致的一些问题

脏读

指一个事务读取了另外一个事务未提交的数据

不可重复读

在一个事务内读取表中的某一行数据， 多次读取结果不同

虚读

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

8.2. 事务处理

MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 BEGIN TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交

/*autocommit 标记是针对每个连接而不是针对服务器的*/
set autocommit = 0 关闭自动提交
set autocommit = 1 开启

手动处理事务

-- 关闭自动提交
SET autocommit = 0  

-- 开始事务
BEGIN TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 创建保留点 updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滚到保留点 updateA
ROLLBACK TO updateA;

-- 提交事务，只有操作 A 生效
COMMIT;

-- 开启自动提交
SET autocommit = 1 

9. 索引

索引是一种数据结构，用于加速数据库中的数据访问。索引可以将数据按照特定的顺序组织起来，以便更快地查找和检索数据

在SQL中，可以使用CREATE INDEX 语句来创建索引

CREATE INDEX index_name ON table_name (column1, column2, ...);

以上示例中，使用CREATE INDEX 语句创建了一个名为 index_name 的索引，该索引基于名为 table_name 的表中的 column1、column2 等列。一旦创建了索引，就可以使用SELECT语句等来查询数据，而无需扫描整个表。这可以大大提高查询性能，特别是在大型表中

但是，索引也有一些缺点。首先，索引需要占用额外的存储空间。其次，索引需要维护，这可能会导致插入、更新和删除操作的性能下降。因此，应该仔细考虑何时使用索引，并确保只为最常用的查询创建索引

9.1. 索引的使用

在创建表的时候给字段增加索引或创建完毕后再增加索引

-- DESC命令可以查看表中所有字段的信息，包括该字段是否为主键。在输出的结果中，主键字段会有一个“PRI”的标识
DESC `table_name`;

-- 显示所有的索引信息
SHOW INDEX FROM `table_name`

-- 添加一个索引 列名(索引名) 
ALTER TABLE `student` ADD UNIQUE `classes`(`classes`);

-- 创建索引 CREATE INDEX 索引名on表(字段)
CREATE INDEX id_ app_ user_ name ON app_ user(`name`) ;

-- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名;

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student;

10. 视图

视图是一种虚拟表，它是基于SQL查询的结果集构建的。视图不包含任何数据，而是根据查询定义的规则动态生成数据。视图可以像表一样使用，可以查询、插入、更新和删除数据。视图的主要优点是它们提供了一种简单的方法来组织和管理复杂的查询。视图还可以用于隐藏底层表的复杂性，从而简化应用程序的开发和维护

在SQL中，可以使用CREATE VIEW语句来创建视图。以下语句将创建一个名为view_name 的视图，该视图基于名为 table_name 的表：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

在以上语句中，使用SELECT语句定义了视图的查询规则。还使用WHERE子句来指定要包含在视图中的行。一旦创建了视图，就可以像使用表一样使用它例如，以下查询将从view_name 视图中检索数据：

SELECT * FROM view_name;

视图可以像表一样使用，但是它们不包含任何数据。每次查询视图时，都会根据定义的规则动态生成数据。如果底层表发生更改，则视图也会相应地更新

11. 数据库用户管理

11.1. MySQL权限级别

权限存储在 mysql库 的user、db、tables_priv、columns_priv、procs_priv这几个系统表中，待MySQL实例启动后就加载到内存中

11.2. 操作用户

要授予用户帐户权限，可以用GRANT命令。有撤销用户的权限，可以用REVOKE命令

GRANT 和 REVOKE 可在几个层次上控制访问权限：

• 整个服务器，使用 GRANT ALL 和 REVOKE ALL
• 整个数据库，使用 ON database.*
• 特定的表，使用 ON database.table
• 特定的列
• 特定的存储过程

用户表

mysql.user

数据库用户名的含义

root@127.0.0.1
用户root的访问权限为127.0.0.1，表示root用户只支持本地访问

开启数据库外连：使某个数据库用户支持除本地IP外的外部IP连接

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

/*password是root用户的密码；代码中的 % 表示任何IP地址，如果你的IP地址固定，可以把 % 换成你的IP地址，不换也行*/

刷新权限：每次更新权限后记得刷新权限

FLUSH PRIVILEGES;

查询当前数据库用户

select user();

查看当前用户权限

show grants for '用户名'@'主机地址';

查看所有用户（用户名、给谁授权）

select user,host from mysql.user; 
或 
select * from mysql.user;

添加用户

create user 用户名@'主机地址' identified by '密码';

创建用户同时授权

grant all privileges on mq.* to 用户名@主机名 identified by '密码';

设置与更改用户密码

SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

删除用户

drop user 用户名@'主机地址';

给用户授权

grant 权限 on 数据库名.表名 to 用户名@'主机名' （在这里加上 WITH GRANT OPTION，可以使他有赋权能力即root用户的权限）

撤销权限

revoke 权限 on 数据库名.表名 from '用户名'@'主机名';

切换用户

注意：不同用户的主机得是一样的，否则无法切换

使用命令 -u 用户名 -p 

12. MySQL备份

导出

mysqldump -u 用户名 -p 数据库名 表1 表2 表3 > 导出的文件.sql

将.sql文件导入数据库

source  D:\ceshi.sql

13. 预编译

13.1. 即时 SQL

一条 SQL 在 DB 接收到最终执行完毕返回，大致的过程如下

词法和语义解析

优化 SQL 语句，制定执行计划

执行并返回结果

如上，一条 SQL 直接是走流程处理，一次编译，单次运行，此类普通语句被称作 Immediate Statements （即时 SQL）

13.2. 预处理 SQL

介绍

所谓预编译语句就是将 SQL 语句中的值用占位符替代，可以视为将 SQL 语句模板化或者说参数化，一般称这类语句叫Prepared Statements。

预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程

此外预编译语句能防止 SQL 注入

语法

prepare、execute、deallocate 统称为 PREPARE STATEMENT

-- 定义预处理语句
PREPARE stmt_name FROM preparable_stmt;

-- 执行预处理语句
EXECUTE stmt_name [USING @var_name , @var_name ...];

-- 删除(释放)定义
{DEALLOCATE | DROP} PREPARE stmt_name;

#计算一个数加2的值
PREPARE stmt1 FROM 'SELECT (?+2) AS name';
SET @a = 3;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;

解决limit无法传参问题

对于 LIMIT 子句中的值，必须是常量，不得使用变量，也就是说不能使用：SELECT * FROM TABLE LIMIT @skip, @numrows; 可以用 PREPARE 语句解决此问题

mysql> SET @skip = 100; SET @numrows = 3;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t1 LIMIT @skip, @numrows;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@skip, @numrows' at line 1

mysql> PREPARE stmt3 FROM "SELECT * FROM t1 LIMIT ?, ?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt3 USING @skip, @numrows;
+-----+--------+
| a   | filler |
+-----+--------+
| 100 | filler |
| 101 | filler |
| 102 | filler |
+-----+--------+
3 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt3;
Query OK, 0 rows affected (0.00 sec)

预处理 SQL 使用注意点

• stmt_name 作为 preparable_stmt 的接收者，唯一标识，不区分大小写
• preparable_stmt 语句中的 ? 是个占位符，所代表的是一个字符串，不需要将 ? 用引号包含起来
• 定义一个已存在的 stmt_name ，原有的将被立即释放，类似于变量的重新赋值
• PREPARE stmt_name 的作用域是session级
• 预处理编译 SQL 是占用资源的，所以在使用后注意及时使用 DEALLOCATE PREPARE 释放资源，这是一个好习惯

14. 存储过程（不建议使用）

存储过程是一组为了完成特定功能的SQL语句集合，它们被预编译并存储在数据库中，可以被多次调用。存储过程可以提高程序的运行效率，减少网络传输的数据量，同时也可以提高程序的安全性

在MySQL中，可以使用CREATE PROCEDURE 语句来创建存储过程，使用CALL语句来调用存储过程

-- 创建存储过程
CREATE PROCEDURE GetCustomerOrders(IN CustomerID INT)
BEGIN
    SELECT * FROM Orders WHERE CustomerID = CustomerID;
END 

-- 执行 
CALL GetCustomerOrders(1);

在上面的示例中，创建了一个名为GetCustomerOrders的存储过程，它接受一个名为CustomerID的输入参数，并返回与该客户相关的所有订单

15. 游标

游标是一种用于遍历结果集的数据库对象。它可以让我们逐行处理结果集，而不是一次性将整个结果集加载到内存中

在MySQL中，可以使用DECLARE CURSOR 语句声明游标，使用FETCH 语句获取结果集中的行，使用CLOSE 语句关闭游标，使用DEALLOCATE 语句释放游标所占用的资源

    DECLARE total INT;
    -- 创建接收游标数据的变量
    DECLARE sid INT;
    DECLARE sname VARCHAR(10);
    -- 创建总数变量
    DECLARE sage INT;
    -- 创建结束标志变量
    DECLARE done INT DEFAULT false;
    -- 创建游标
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
    -- 指定游标循环结束时的返回值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    SET total = 0;
    OPEN cur;
    FETCH cur INTO sid, sname, sage;
    WHILE(NOT done)
    DO
        SET total = total + 1;
        FETCH cur INTO sid, sname, sage;
    END WHILE;

    CLOSE cur;
    SELECT total;
END $
DELIMITER ;

16. 触发器

触发器是一种特殊的存储过程，它与表操作有关，当触发器所在表上进行插入、更新或删除操作时将自动执行某些操作

在MySQL中使用 CREATE TRIGGER 语句创建触发器，使用 SHOW TRIGGERS 语句查看触发器，使用 DROP TRIGGER trigger_name 语句删除触发器

-- 创建触发器
CREATE TRIGGER tr_Customer_Insert
AFTER INSERT ON Customers
FOR EACH ROW  -- 表示任何一条记录上的操作满足触发事件都会触发该触发器
BEGIN
    INSERT INTO CustomerAudit (CustomerID, Action) VALUES (NEW.CustomerID, 'INSERT');
END;

在上面的示例中，创建了一个名为tr_Customer_Insert的触发器，它在Customers表上定义。该触发器在每次插入新行时自动执行，并将新行的CustomerID插入到CustomerAudit表中

MySQL 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程

在 MySQL 5.7.2 版之前，可以为每个表定义最多六个触发器

• BEFORE INSERT - 在将数据插入表格之前激活
• AFTER INSERT - 将数据插入表格后激活
• BEFORE UPDATE - 在更新表中的数据之前激活
• AFTER UPDATE - 更新表中的数据后激活
• BEFORE DELETE - 在从表中删除数据之前激活
• AFTER DELETE - 从表中删除数据后激活

• Previous
  虚拟机ping的问题
• Next
  Python之旅上