【MySQL】详细版SQL用法 (含编程、用户管理、备份等)

编程

语法

  1. 常量：
  • 字符串常量一般用单引号，也可用双引号
  • 日期和时间用单引号
  • 布尔常量为 true 和 false，SQL语句中用 1 表示 true，0 表示 false
  • NULL 参与的运算仍为 NULL
  1. 变量：
  • 局部变量定义和赋值：
1
2
set @局部变量名 = 表达式1 [ , @局部变量名 = 表达式2，...];
select 字段名 into @局部变量名 from 表名;
  • 局部变量显示：
1
select @局部变量名[ , @局部变量名 , ...];
  • 全局变量：
全局变量名称 说明
@@back_log 返回 MySQL 主要连接请求的数量
@@basedir 返回 MySQL 安装基准目录
@@license 返回服务器的许可类型
@@port 返回服务器侦听 TCP/IP 连接所用的端口
@@version 返回服务器版本号
  1. 重置命令结束标记：delimiter 符号
  • 符号可以是一些特殊符号，如：@@，##，$$，%%，避免使用 /，因为它是 MYSQL 的转义字符。
  • 恢复使用分号作为结束标记：delimiter;

函数分类

(1) 字符串函数

函数 功能
concat(s1, s2) 字符串拼接，将字符串 s1 和 s2 连接在一起
lower(str) 将字符串 str 中的所有字符转换为小写
lupper(str) 将字符串 str 中的所有字符转换为大写
lpad(str, n, pad) 左填充，使用字符 pad 对字符串 str 的左侧进行填充，直到达到长度 n
rpad(str, n, pad) 右填充，使用字符 pad 对字符串 str 的右侧进行填充，直到达到长度 n
trim(str) 去除字符串 str 头部和尾部的空格
substring(str, start, len) 返回从字符串 str 的 start 位置开始的长度为 len 的子字符串
char_length(str) 返回字符串 str 所包含的字符个数
length(str) 返回字符串的字节长度，一个汉字时3字节，一个数字或字母时1字节
ltrim(str) 返回删除前导空格的字符串 str
rtrim(str) 返回删除尾部空格的字符串 str
trim(str) 返回删除两侧空格的字符串 str

(2) 数学函数

函数 功能
ceil(x) 向上取整
floor(x) 向下取整
mod(x, y) 返回 x/y 的模
rand() 返回0~1内的随机数
round(x, y) 求参数 x 的四舍五入的值，保留 y 位小数
abs(x) 返回 x 的绝对值
PI() 返回圆周率的值
sqrt() 返回非负数的二次方根

(3) 日期函数

函数 功能
curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前日期和时间
year(date) 获取指定 date 的年份
month(date) 获取指定 date 的月份
day(date) 获取指定 date 的日期
data_add(date, interval expr type) 返回一个日期/时间值加上一个时间间隔 expr 后的时间值
datediff(date1, date2) 返回起始时间 date1 和结束时间 date2 之间的天数

(4) 流程函数

函数 功能
if(value, t, f) 如果 value 为 true，则返回 t，否则返回 f
ifnull(value1, value2) 如果 value1 不为空, 返回 value1, 否则返回 value2
case when [val1] then [res1]..... else [default] end 如果 val1 为 true，则返回 res1，….. 否则返回 default 默认值
case [expr] when [val1] then [res1]....... else [default] end 如果 expr 的值等于 val1，则返回 res1，…. 否则返回 default 默认值

条件控制函数：

1
2
3
4
5
6
7
8
select 字段名 [别名],
 case 
   when 条件1 then 结果1
   when 条件2 then 结果2
   ...
   [else 默认结果]
 end [查询结果别名]
from 表名 where 条件;

(5) 系统函数

函数 功能
user() 返回当前登录的用户名
database() 返回当前所使用的数据库的名字
version() 返回 mysql 服务器版本号

存储函数

  1. 存储函数创建：
1
2
3
4
5
6
create function 函数名 ([参数名 参数数据类型 [, ...]])
returns 函数返回值的数据类型
begin
    函数体;
    return 语句；
end
  1. 调用存储函数：
1
select 函数名 ([参数值 [, ...]]);
  1. 删除存储函数：
1
drop function 函数名;

存储过程

  1. 创建存储过程：
1
2
3
4
create procedure 存储过程名 ()
begin 
   过程体
end;
  1. 调用存储过程：
1
2
call 存储过程名 ()；
call 存储过程名 (参数)；
  1. 存储过程的参数：
1
2
3
4
5
6
7
create procedure 存储过程名 ( 
   [in | out | inout] 参数1  数据类型，
   [in | out | inout] 参数2  数据类型，.....
)
begin
   过程体
end;
参数 描述
in 为输入参数，由调用者传入，并且只能被存储过程读取
out 为输出参数，值由存储过程写入
inout 同时具有 in 和 out 的参数特性
  1. 删除存储过程：
1
drop procedure 存储过程名;

条件判断语句

  1. 声明变量：
1
declare 局部变量名 [, 局部变量名，....] 数据类型 [default 默认值];
  1. 为变量赋值：
1
set 局部变量名 = 表达式1 [, 局部变量名 = 表达式2];
  1. if 语句：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
if 条件 then 
    sql 语句块1;
[else 
    sql语句块2;]
end if;
或者
if 条件 then 
    sql 语句块1;
elseif 条件 then 
    sql语句块2;
    ......
else
   sql语句块；
end if;
  1. case 语句：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
case  [表达式]
 when [表达式值1] then SQL 语句块1;
 when [表达式值2] then SQL 语句块2;
 ........
 when [表达式值n] then SQL 语句块n;
 [else SQL 语句块 n+1;]
end;
或者
case
 when 条件1 then SQL 语句块1;
 when 条件2 then SQL 语句块2;
 .......
 when 条件n then SQL 语句块n;
 else SQL 语句块n+1;
end;

循环语句

  1. LOOP 循环：leave —> 退出循环，iterate —> 类似于 continue;
1
2
3
4
5
6
标签：LOOP
  SQL语句块;
  if 条件表达式 then 
    leave/iterate 标签；
  end if;
end LOOP;
  1. while 循环：
1
2
3
while 条件表达式 do
  SQL语句块;
end while;
  1. repeat 循环：
1
2
3
4
repeat
  SQL语句块;
  until 条件表达式
end repeat;

游标

  1. 声明游标：
1
declare 游标名 cursor for select 语句;
  • 声明游标作用是得到一个 select 查询的结果集，在该结果集包含了所需要的数据，即 select 语句查询的结果
  • select 语句可以带有 where 语句和 order by、group by 等，但不能使用 into 子句
  1. 打开游标：
1
open 游标名;
  • 打开游标，数据送入游标工作区，以供用户读取
  1. 提取数据：
1
fetch 游标名 into 变量名1 [ , 变量名2];
  1. 关闭游标：
1
close 游标名;

条件处理程序

1
2
3
4
5
6
7
8
9
10
11
delcare handler_action handler for condition _value 
[, condition_value].... statement；

handler_action
    continue：继续执行当前程序
    exit：终止当前程序
condition:
    SQLSTATE sqlstate_value：状态码：如 02000
    sqlwarning：所有以 01 开头的 SQLSTATE 代码的简写
    not found：所有以 02 开头的 SQLSTATE 代码的简写
    sqlexception：所有没有被 sqlwaning或not found 捕获的 SQLSTATE 代码的简写

数据库运行维护

用户管理

  1. 创建登录用户：
1
create user 用户 [identified by'密码'] [, 用户 [identified by '密码']];
  • 用户的格式：用户名@主机名，没有指定主机名，则主机名默认为为 % ，表示一组主机；localhost 表示本地主机
  • identified by 子句指定创建用户时的登录密码
  1. 修改用户密码：
1
set password for 用户='新密码';
  1. 修改用户名：
1
rename user 旧用户名 to 新用户名 [, 旧用户名 to 新用户名];
  1. 删除用户：
1
drop user 用户名 [, ......];

权限管理

mysql 字段级别权限

  1. 授予 MYSQL 字段级别权限：
1
grant 权限名称 (列名 [, 列名, ......]) [权限名称 (列名 [, 列名, ......]), .....] on table 数据库名. 表名或视图名 to 用户 [, 用户, ......];
权限名称 权限类型 说明
select column_priv 查询数据库表中的记录
insert column_priv 向数据库表中插入记录
update column_priv 修改数据库表中记录
references column_priv 暂未使用
all privileges 以上所有权限类型的和 grant_priv 权限类型除外
usage 没有任何权限类型 仅仅用于登录

mysql表级别权限

  1. 授予MYSQL表级别权限：
1
grant 权限名称 (列名 [, 列名, ......]) [权限名称 (列名 [, 列名, ......]), .....] on table 数据库名. 表名或视图名 to 用户 [, 用户, ......];

table_priv

权限名称 说明
select 查询数据库表中的记录
insert 向数据库表中插入记录
update 修改数据库表中的记录
delete 删除数据库表中的记录
create 创建数据库表，但不允许创建索引和视图
drop 删除数据库表以及视图的定义，但不能删除索引
grant 将自己的权限分享给其他 MYSQL 用户
index 创建和删除索引
alter 执行 alter table 修改表结构
create view 执行 create view 创建视图，在创建视图时，还需要持有基表的 select 权限
show view 执行 show create view 查看视图的定义
all privileg es 以上所有权限的和，grant_priv 除外
usage 无权限，仅用于登录

mysql 存储程序级别权限

  • 授予 MYSQL 存储程序级别权限：
1
grant 权限名称 [, 权限名称名, ...] on function/procedure 数据库名. 函数名或存储过程名 to 用户 [, 用户, ...];

proc_priv

权限名称 说明
grant 将自己的权限分享给其他 MYSQL 用户
execute 执行存储过程或函数
alter routine 修改、删除存储过程和函数
all privileges 所有的权限的和，grant_priv 除外
usage 无权限，仅用于登录

mysql数据库级别权限

  • 授予MYSQL数据库级别权限：
1
grant 权限名称 [, 权限名称, ......] on 数据库.*  to 用户 [, 用户, ...];
  • 授予MYSQL数据库级别权限：
1
grant 权限名称 [, 权限名称, ....] on *.* to 用户 [, 用户, ......];
  • 权限的转移：with grant option

如果使用了 with grant option 子句，则表示 TO 子句中的所有用户都具有把自己所拥有的权限授予给其他用户的权利

db

权限名称 权限类型 说明
select Select_priv 查询数据库表中的记录
insert Insert_priv 向数据库表中插入记录
update Update_priv 修改数据库表中的记录
delete Delete_priv 删除数据库表中的记录
create Create_priv 创建数据库或者数据库表，但不允许创建索引和视图
drop Drop_priv 删除数据库、数据库表以及视图的定义，但不能删除索引
with grant option Grant_priv 将自己的权限分享给其他 MYSQL 用户
index Index_priv 创建或者删除索引
alter Alter_priv 执行 alter table 修改表结构，在修改表名时，还需要持有旧表的 drop 权限以及新表的 create、insert 权限
create temporary tables Create_tmp_table_priv 执行 create temporary tables 命令创建临时表
lock tables Lock_tables_priv 执行 lock tables 命令显示地加锁，执行 uplock tables 命令显示地解锁
execute Execute_priv 执行存储过程或者函数
create view Create_view_priv 执行 Create view 创建视图，在创建视图时，还需要持有基表的 select 权限
show view Show_view_priv 执行 Show view 查看视图定义
create routine Create_routine_priv 创建存储过程或者函数
alter routine Alter_routine_priv 修改、删除存储过程或者函数
event Event_priv 创建、修改、删除以及查看事件
trigger Trigger_priv 创建、执行以及删除触发器
all privileges 以上所有权限类型的和 Grant_priv 权限类型除外
usage 没有任何权限 仅用于登录
  • 对于多种级别的权限，易混淆的 select，其实对于 column_priv 是设置的特定表某些字段的权限，而对于 table_priv 而言，则是整张表的所有字段都赋予特定的权限，而对于 db，则是当前数据库的所有表都具有该权限

mysql服务器管理员级别权限

  • 授予MYSQL服务器管理员级别权限：
1
grant 权限名称 [, 权限名称, ...] on *.* to 用户 [, 用户, ...] [with grant option];

撤销权限

  • 撤销所有权限：
1
revoke all privileges grant option from 用户 [, 用户, ...];
  • 撤销指定权限:
1
revoke 权限名称 [(列名 [, 列名, ...])] [, 权限名称 [(列名 [, 列名, ...])], ...] on *.*/数据库名.*  数据库名. 表名或视图名 from 用户 [, 用户, ...];

角色管理

定义：是一组相关权限的集合，将不同的权限组合在一起形成角色

  1. 创建角色：
1
create role 角色名;
  • 角色格式：角色名 @ 主机名。
  • 查看是否创建成功：
1
select User, Host, Account_locked from mysql.user where user='角色名';
  1. 授予用户角色：
1
grant 角色 [, 角色，... to 用户 [, 用户, ...];
  • 查看是否正确分配：
1
show grants for 用户 using 角色;
  1. 撤销用户角色：
1
revoke 角色 [, 角色, ...] from 用户 [, 用户, ...];
  1. 删除角色：
1
drop role 角色 [, 角色, ...];

注意：用户在使用角色前必须激活角色； set global activate_all_roles_on_login=on

数据备份与恢复

备份与恢复

使用 mysqldump 命令备份数据

  1. 备份单个数据库或表：
1
mysqldump -u 用户名 -h 主机名 -p 密码 数据名 [表名 [表名 .......]] >备份文件名.sql;
  1. 备份多个数据库：
1
mysql -u 用户名 -h 主机名 -p 密码 --database 数据库名 数据库名 ..... >备份文件名.sql;
  1. 备份所有数据库：
1
mysqldump -u 用户名 -h 主机名 -p 密码 --all-database >备份文件名.sql;

使用 mysql 命令恢复数据

1
mysql -u 用户名 -p 数据库名 < 备份文件名.sql;

表数据的导出与导入

  1. 使用 select...into outfile 语句导出表数据
1
2
3
4
5
6
7
select 语句 into outfile '文本文件'
[fields [terminated by '字符']
    [optionally] enclosed by '字符']
    [escaped by '字符']
[lines [starting by '字符串']
    [terminated by '字符串']
];

说明：

  • terminated by '字符'：字段分隔符，默认是制表符 \t；
  • [optionally] enclosed by '字符'：向字段值两边加上字段包尾符，如果使用 optionally 选项，则只在 char、vachar 和 text 字符串类型的字段值两边添加字段包围符。
  • escaped by '字符'：设置转义字符，默认值为 '\';
  • starting by '字符串'：设置每行开头的字符，默认情况下无任何字符。
  • terminated by '字符串'：设置每行的结束符，默认值是 '\n'。

注意：使用 select...... into outfile 语句时，目标文件的路径只能是 MySQL 的 secure_file_priv 参数指定的位置，获取方式 select @@secure_file_priv；

  1. 使用 mysqldump 命令导出数据
1
2
3
4
5
6
mysqldump -u root -T "目标路径" 数据库名 表名
[ --fields-terminated-by=字符]
[--fields-enclose-by=字符]
[--fields-optionally-enclosed-by=字符]
[--fields-escaped-by=字符]
[--lines-terminated-by=字符串]

说明：

  • 只有指定 -T 参数，才能导出纯文本文件
  • 导出生成的文件有两个，一个是包含创建表的 create table 语句的 表名.sql 文件，一个是包含其数据的 表名.txt 文件
  • 目标路径必须是 MySQL 的 secure_file_priv 参数所指定的位置
  • 各选项功能对应 “select... into outfile” 语句中的各项功能
  1. 使用 load data infile 语句导入表数据
1
2
3
4
5
6
7
8
9
load data infile '文本文件' into table 表名
[fields [terminated by '字符']
        [[optionally] enclosed by '字符']
        [escaped by '字符']
]
[lines [staring by '字符串']
       [terminated by '字符串']
]
[ignore n lines];

说明：

  • fileds 和 lines 选项的功能与 select... into outfile 语句中选项的功能相同
  • ignore n lines：忽略文本文件中的前 n 条记录
  • 使用 select... into outfile 语句将数据从一个数据库表导出到一个文本文件，再使用 load data infile 语句从文本文件中将数据导入数据库表时，两个命令的选项参数必须匹配，否则 load data infile 语句无法解析文本文件的内容
  1. 使用 mysqlimport 命令导入表数据
1
2
3
4
5
6
7
mysqlimport -u root -p 数据库名 文本文件名.txt
[--fileds-terminated-by=字符]
[--fileds-enclose-by=字符]
[--fileds-optionally-enclosed-by=字符]
[--fileds-escaped-by=字符]
[--lines-terminated-by=字符串]
[--ignore-lines=n]

说明：

  • --ignore-lines=n 表示忽略文本文件的前 n 行

使用二进制日志文件恢复数据

  1. 查看二进制日志的开启状态
1
show global variables like'%log_bin%';
  1. 查看二进制日志
1
show binary logs;
  1. 使用二进制日志恢复数据库
1
mysqlbinlog [option] "日志文件" | mysql -u root -p;

说明：

  • --start-datetime：指定恢复数据库的起始时间点
  • --stop-datetime：指定恢复数据库的结束时间点

存储引擎

概念：存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

  1. 创建表时，指定存储引擎：
1
2
3
4
create table 表名 (
   字段1  字段1类型 [comment 字段1注释],
   字段1  字段1类型 [comment 字段1注释]
) engine=innodb [comment 表注释];
  1. 查看当前数据库支持的存储引擎：
1
show engines;

索引

  1. 创建索引：
1
create [unique] index 索引名 on 表名 (列名 [, 列名]);
  1. 查看索引：
1
show index from 表名;
  1. 删除索引:
1
drop index 索引名 on 表名;
  1. 索引的优缺点：
优势 劣势
提高数据检索的效率，降低数据库的IO成本 索引列也是要占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 所以大大提高了查询效率，它是却也降低了更新表的速度
  1. 索引结构：
  • 我们平常所说的索引，如果没有特别指明，都是指 B+tree 结构组织的索引
索引 InnoDB MyISAM Memory
B+tree索引 支持 支持 支持
Hash索引 不支持 不支持 支持
R+tree索引不支持 支持 不支持
Full_text 5.6版本后支持 支持 不支持

视图

  1. 创建视图：
1
2
3
4
create [or replace] view 视图名 [(别名[, 别名])]
as
select 语句
[where check option];
  1. 修改视图：
1
2
3
4
5
6
7
8
9
create or replace view 视图名 [(别名[, 别名])]
as
select 语句
[where check option]；
或者
alter view 视图名 [(别名[, 别名])]
as
select 语句
[where check option];
  1. 删除视图：
1
drop view 视图名[, 视图名,.......];