SQL
一、DDL 语句
1.数据库操作
- 查询所有的数据库：
show databases;
- 查询当前数据库是哪个：
select database ();
- 创建：
create database [if not exists] 数据库名 [default charset 字符集]（collate 排序规则)
- 删除：
drop database [if exists] 数据库名;
- 使用某个数据库：
use 数据库名
2.表操作
- 查询当前数据库的所有表：
show tables;
- 查询表结构：
desc 表名;
- 创建：
create table (
字段1 字段1类型 (comment 字段 1 注释),
字段2 字段2类型 (comment 字段 2 注释),
......
) (comment 表注释);
create table 表名 as select *from 另一张表名-------复制另一张表
- 添加字段：
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
- 给指定字段添加数据：
insert into 表名 (字段 1，字段 2....) values (值 1，值 2.......);
- 给全部字段添加数据：
insert into 表名 values (值 1，值 2........);
- 批量添加：
insert into 表名 (字段 1，字段 2.......) values (值 1，值 2....)，(值 1，值 2)，..........；
insert into 表名 values (值 1，值 2....)，(值 1，值 2)，..........；
- 修改数据：
update 表名 set 字段名 1 = 值 1，字段名 2 = 值 2，.....[where 条件];
- 删除数据：
delete from 表名 [where 条件];
三、DQL
1.Select 查询
- 查询多个字段：
select 字段1，字段2，.... from 表名；
select *from 表名;
- 设置别名：
select 字段1 [as 别名1]，字段2[as 别名2],....from 表名;
- 去除重复记录：
select distinct 字段列表 from 表名；
- 条件查询：
select 字段列表 from 表名 where 条件列表；
注意：
like 模糊查询“_”，一个下划线表示一个占位符, 如选择名字为两个字的，where name like '__ __'；
'%'的使用，如查询身份证号最后一位为 X 的，where id like '%X'；这个就是不管前面为多少，只管末位 为 X
- 分组查询：
select 字段列表 from 表名 [where 条件]
group by [having 分组过滤后的条件];
注意：
- where 是分组之前进行过滤，不满足 where 条件的，不参与分组，而 having 是分组之后对结果进行过滤。
- where 不能对聚合函数进行过滤，而 having 可以
- 执行顺序：where > 聚合函数 > having
- 分组后，一般查询的字段为聚合函数和分组的字段，其他字段无意义
- 排序查询：
select 字段列表 from 表名 order by 字段1 排序方式，字段2 排序方式；
注意：
降序：desc，升序：asc ,升序是默认，可以不写
- 分页查询：
select 字段列表 from 表名 limt 起始索引，查询记录数;
注意：
- 起始索引从 0 开始，起始索引=（查询页码-1 * 每页显示记录数
- 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit10
2.聚合函数
count -------统计数量
max------- 最大值
min--------- 最小值
avg--------平均值
Sum----求和
示例：
Select count (id) from 表名
Select count (*) from 表名
这是作用于列的
null 不参与聚合函数运算
四、约束
1.约束条件
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | not null |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | default |
| 检查约束 | 保证字段值满足某一个条件 | check |
| 外键约束 | 用来让两张表的数据之间建立连接欸，保证数据的一致性和完整性 | foreign key |
| 自增 | 自动增长 | auto_increment |
2.外键约束
- 添加外键：
create table 表名 (
字段名 数据类型
..........
[Constraint] [外键名称] foreign key (外键字段名) references 主表 (主表列名)
);
或者
Alter table 表名 add constraint 外键名称 foreign key [外键名称] references 主表[主表列名]
- 删除外键：
alter table 表名 drop foreign key 外键名称;
- 外键更新和删除行为：
| 行为 | 说明 |
|---|---|
No action |
在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则不允许删除/更新 |
Restrict |
与 no action 一致 Cascade |
cascade |
在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录 |
set null |
在父表中删除对应记录时，首先检查记录是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为空） |
set default |
父表有变更时，设置子表中外键值为一个默认值（innodb不支持） |
alter table 表名 add constraint 外键名称 foreign key (外键字段)
references 主表名（主表字段名）
on update cascade on delete cascade;
3.主键约束
- 创建：
create table 表名 (
字段名 数据类型
..........
Constraint [主键名称] primary key (字段1，字段2...)
)；
或者
Alter table 表名 add constraint 主键名称 primary key(字段);
- 删除：
alter table 表名 drop primary key;
4.唯一约束
- 创建：
create table 表名 (
字段名 数据类型
..........
Constraint [名称] unique(字段)
)；
或者
Alter table 表名 add constraint 名称 unique(字段);
- 删除：
alter table 表名 drop index 名称;
5. 检查约束
- 创建：
create table 表名 (
字段名 数据类型
..........
Constraint [名称] check(age>1 and age<20)
)；
或者
Alter table 表名 add constraint 名称 check(age>1 and age<20);
- 删除：
alter table 表名 drop constraint 名称;
五、触发器
- 创建：
create trigger 触发器名字
Before/after insert/update/delete
On 表的名字 for each row
Begin
具体往哪张表插入什么数据
End;
- 查看触发器：
show triggers；
- 删除：
drop trigger 触发器的名字；
六、多表查询
1.连接
- 内连接：
- 隐式内连接：
select 字段列表 from 表1, 表2 where 条件；
- 显示内连接：
select 字段列表 from 表1 [inner] join 表2 on 连接条件；
- 外连接：
- 左外连接：表 1 为全部信息显示
select 字段列表 from 表1 left [outer] join 表2 on 条件;
- 右外连接：表 2 为全部信息显示
select 字段列表 from 表1 right [outer] join 表2 on 条件
- 自连接：
select 字段列表 from 表A 别名A join 表A 别名B on 条件
2.查询
- 联合查询：
select 字段列表 from 表A<br>
Union [all] -----------------加了all全部查询，没加all，会去重
Select 字段列表 from 表B；
注意：联合查询的列数需要保持一致，还有字段类型也要保持一致
- 子查询：
- 标量子查询：
select *from 表名 where 字段名=（select *from 表名 where 条件）；
- 列子查询：
select *from 表名 where 字段名 in (select *from 表名 where 条件)
| 操作符 | 描述 |
|---|---|
in |
在指定的集合范围内选择，多选一 |
not in |
不在指定范围内 |
any |
有任意一个满足即可 |
Some |
与 any 同理 |
all |
所有值都必须满足 |
<> |
不等于 |
- 行子查询：示例：
select *from 表名 where (id，name)=(select id，name from 表名 where 条件)；
- 表子查询：返回的是多行多列，常用 in
select e.*，d.* from (select *from emp where 条件) e left join depet_id on e.dept._id=d.id
七、编程
1.语法
- 常量：
- 字符串常量一般用单引号，也可用双引号
- 日期和时间用单引号
- 布尔常量为
true和false，SQL语句中用 1 表示true，0 表示false - NULL 参与的运算仍为NULL
- 变量：
- 局部变量定义和赋值：
set @局部变量名 = 表达式1 [ , @局部变量名=表达式2，......]；
select 字段名 into @局部变量名 from 表名 - 局部变量显示：select @局部变量名[ , @局部变量名 , .......]；
- 全局变量：
| 全局变量名称 | 说明 |
|---|---|
@@back_log |
返回 MySQL 主要连接请求的数量 |
@@basedir |
返回 MySQL 安装基准目录 |
@@license |
返回服务器的许可类型 |
@@port |
返回服务器侦听 TCP/IP 连接所用的端口 |
@@version |
返回服务器版本号 |
- 重置命令结束标记：delimiter 符号
说明：
- 符号可以是一些特殊符号，如：@@，##，
$$，%%，避免使用/，因为它是MYSQL的转义字符。 - 恢复使用分号作为结束标记：delimiter；
2.函数分类
（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 默认值 |
条件控制函数：
select 字段名 [别名]
case 表达式
when v1 then r1
when v2 then r2
........
[else rn]
end [查询结果别名]
from 表名 where 条件；
（5）系统函数
| 函数 | 功能 |
|---|---|
user() |
返回当前登录的用户名 |
database() |
返回当前所使用的数据库的名字 |
version() |
返回 mysql 服务器版本号 |
3.存储函数
- 存储函数创建：
create function 函数名 ([参数名 参数数据类型 [,...]])
returns 函数返回值的数据类型
begin
函数体；
return 语句；
end
- 调用存储函数：
select 函数名 ([参数值 [，...]])；
- 删除存储函数：
drop function 函数名；
4.存储过程
- 创建存储过程：
create procedure 存储过程名 ( )
begin
过程体
end
- 调用存储过程：
call 存储过程名 ( )；
call 存储过程名 (参数)；
- 存储过程的参数：
create procedure 存储过程名 (
[in | out | inout] 参数1 数据类型，
[in | out | inout] 参数2 数据类型，.....
)
begin
过程体
end
| 参数 | 描述 |
|---|---|
in |
为输入参数，由调用者传入，并且只能被存储过程读取 |
out |
为输出参数，值由存储过程写入 |
inout |
同时具有 in 和 out 的参数特性 |
- 删除存储过程：
drop procedure 存储过程名；
5.条件判断语句
- 声明变量：
declare 局部变量名 [，局部变量名，....] 数据类型 [default 默认值]；
- 为变量赋值：
set 局部变量名 = 表达式1 [，局部变量名 = 表达式2]；
- if 语句：
if 条件 then
sql 语句块1；
[else
sql语句块2；]
end if；
或者
if 条件 then
sql 语句块1；
elseif 条件 then
sql语句块2；
......
else
sql语句块；
end if；
- case 语句：
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;
6.循环语句
- LOOP循环：leave->退出循环，iterate->类似于continue；
标签：LOOP
SQL语句块；
if 条件表达式 then
leave/iterate 标签；
end if；
end LOOP；
- while循环：
while 条件表达式 do
SQL语句块；
end while；
- repeat循环：
repeat
SQL语句块；
until 条件表达式
end repeat；
7.游标
- 声明游标：
declare 游标名 cursor for select 语句；
- 声明游标作用是得到一个select查询的结果集，在该结果集包含了所需要的数据，即select语句查询的结果
- select语句可以带有where语句和order by、group by等，但不能使用into子句
- 打开游标：
open 游标名;
- 打开游标，数据送入游标工作区，以供用户读取
- 提取数据：
fetch 游标名 into 变量名1 [ , 变量名2]；
- 关闭游标：
close 游标名；
8.条件处理程序
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.用户管理
- 创建登录用户：
create user 用户 [identified by'密码'] [, 用户 [identified by '密码']]；
- 用户的格式：用户名@主机名，没有指定主机名，则主机名默认为为 ‘%’ ，表示一组主机；localhost表示本地主机
- identified by 子句指定创建用户时的登录密码
- 修改用户密码：
set password for 用户=‘新密码’；
- 修改用户名：
rename user 旧用户名 to 新用户名 [, 旧用户名 to 新用户名]；
- 删除用户：
drop user 用户名 [, ......]；
2.权限管理
mysql字段级别权限
- 授予 MYSQL 字段级别权限：
grant 权限名称 (列名 [, 列名, ......]) [权限名称 (列名 [, 列名, ......]), .....] on table 数据库名. 表名或视图名 to 用户 [, 用户, ......]；
| 权限名称 | 权限类型 | 说明 |
|---|---|---|
| select | column_priv | 查询数据库表中的记录 |
| insert | column_priv | 向数据库表中插入记录 |
| update | column_priv | 修改数据库表中记录 |
| references | column_priv | 暂未使用 |
| all privileges | 以上所有权限类型的和 | grant_priv权限类型除外 |
| usage | 没有任何权限类型 | 仅仅用于登录 |
mysql表级别权限
- 授予MYSQL表级别权限：
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存储程序级别权限：
grant 权限名称 [, 权限名称名, ........] on function/procedure
数据库名. 函数名或存储过程名 to 用户 [, 用户, ......]；
proc_priv
| 权限名称 | 说明 |
|---|---|
| grant | 将自己的权限分享给其他MYSQL用户 |
| execute | 执行存储过程或函数 |
| alter routine | 修改、删除存储过程和函数 |
| all privileges | 所有的权限的和，grant_priv除外 |
| usage | 无权限，仅用于登录 |
mysql数据库级别权限
- 授予MYSQL数据库级别权限：
grant 权限名称 [, 权限名称, ......] on 数据库.* to 用户 [, 用户, ...]；
- 授予MYSQL数据库级别权限：
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服务器管理员级别权限：
grant 权限名称 [, 权限名称, ......] on *.* to 用户 [, 用户, .....] [with grant option]；
撤销权限
- 撤销所有权限：
revoke all privileges grant option from 用户 [, 用户, ...]；
- 撤销指定权限:
revoke 权限名称 [(列名 [, 列名, ...])] [, 权限名称 [(列名 [, 列名, ...])], ...] on *.*/数据库名.* 数据库名. 表名或视图名 from 用户 [, 用户, ...]；
3.角色管理
定义：是一组相关权限的集合，将不同的权限组合在一起形成角色
- 创建角色：
create role 角色名；
- 角色格式：角色名 @ 主机名。
- 查看是否创建成功：
select User, Host, Account_locked from mysql.user where user='角色名'；
- 授予用户角色：
grant 角色 [, 角色，......] to 用户 [, 用户, ......]；
- 查看是否正确分配：
show grants for 用户 using 角色；
- 撤销用户角色：
revoke 角色 [, 角色, ....] from 用户 [, 用户, ...]；
- 删除角色：
drop role 角色 [, 角色, ...]；
注意：用户在使用角色前必须激活角色；
set global activate_all_roles_on_login=on
八、事务
- 概念：是一组操作的集合，不可分割，事务会把所有的操作作为一个整体一起向系统提交或撤销操作的请求，即这些操作要么同时成功，要么同时失败。
- 四大特性：
| 名 称 | 描 述 |
|---|---|
| 原子性 | 事务是不可分割的最小单元，要么全部成功，要么全部失败 |
| 一致性 | 事务完成时，必须使所有的数据都保持一致的状态 |
| 隔离性 | 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行 |
| 持久性 | 事务一旦提交或回滚，它对数据库中的数据改变就是永久的 |
- 并发事务问题
| 问 题 | 描 述 |
|---|---|
| 脏读 | 一个事务读到另一个事务还没有提交的数据 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次读取得数据不同 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在 |
- 事务的隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
read uncommitted |
√ | √ | √ |
read committed |
× | √ | √ |
repeatable read (默认) |
× | × | √ |
serializable |
× | × | × |
九、数据备份与恢复
1.备份与恢复
使用 mysqldump 命令备份数据
- 备份单个数据库或表：
mysqldump -u 用户名 -h 主机名 -p 密码 数据名 [表名 [表名 .......]] >备份文件名.sql；
- 备份多个数据库：
mysql -u 用户名 -h 主机名 -p 密码 --database 数据库名 数据库名 ..... >备份文件名.sql；
- 备份所有数据库：
mysqldump -u 用户名 -h 主机名 -p 密码 --all - database >备份文件名. sql；
使用mysql命令恢复数据
mysql -u 用户名 -p 数据库名 < 备份文件名. sql；
2.表数据的导出与导入
- 使用
select.....into outfile语句导出表数据
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；
- 使用
mysqldump命令导出数据
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”语句中的各项功能
- 使用load data infile语句导入表数据
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语句无法解析文本文件的内容
- 使用mysqlimport命令导入表数据
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行
3.使用二进制日志文件恢复数据
- 查看二进制日志的开启状态
show global variables like'%log_bin%';
- 查看二进制日志
show binary logs
- 使用二进制日志恢复数据库
mysqlbinlog [option] "日志文件" | mysql -u root -p
说明：
- --start-datetime：指定恢复数据库的起始时间点。
- --stop-datetime：指定恢复数据库的结束时间点。
九、存储引擎
概念：存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
- 创建表时，指定存储引擎：
create table 表名 (
字段1 字段1类型 [comment 字段1注释],
字段1 字段1类型 [comment 字段1注释]
) engine=innodb [comment 表注释]；
- 查看当前数据库支持的存储引擎：
show engines；
十、索引
- 创建索引：
create [unique] index 索引名 on 表名 (列名 [, 列名])；
- 查看索引：
show index from 表名
- 删除索引:
drop index 索引名 on 表名
- 索引的优缺点：
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 | 所以大大提高了查询效率，它是却也降低了更新表的速度 |
- 索引结构：
注意：我们平常所说的索引，如果没有特别指明，都是指B+tree结构组织的索引。
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R+tree索引不支持 | 支持 | 不支持 | |
| Full_text | 5.6版本后支持 | 支持 | 不支持 |
十一、视图
- 创建视图：
create [or replace] view 视图名 [(别名[, 别名])]
as
select 语句
[where check option]；
- 修改视图：
create or replace view 视图名 [(别名[, 别名])]
as
select 语句
[where check option]；
或者
alter view 视图名 [(别名[, 别名])]
as
select 语句
[where check option];
- 删除视图：
drop view 视图名[, 视图名,.......]；
十二、存储过程
- 创建：
cretate procedure 存储过程名称[(参数列表)]
begin
SQL语句；
end；
- 调用：
call 名称[(参数)]；
- 查看：
- 查询指定数据库的存储过程及状态信息
select * from information_schema. routines where routine_schema='xxx'；
- 查询某个存储过程的定义
show create procedure 存储过程名称；
