# 下面是一个表的创建语句
create table Name (
    ~~~~~~~~
)engine = InnoDB;
# 在上面的engine = 存储引擎类型 就是存储引擎的设计语句
# 我们默认情况下是InoDB存储引擎

show create table 表名;

# 下述代码会给出该数据库中支持的存储引擎类型
show engines;

# 创建索引
-- UNIQUE表示唯一索引 FULLTEXT表示全文索引
-- (index_col_name,... ) 表示可以形成联合索引，一个索引包括多个表内列
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;
查看索引
SHOW INDEX FROM table_namne;
删除索引
DROP INDEX index_name ON table_name;

# 以下为构造表

-- 创建表

create table tb_user(

id int primary key auto_increment comment '主键',

name varchar(50) not null comment '用户名',

phone varchar(11) not null comment '手机号',

email varchar(100) comment '邮箱',

profession varchar(11) comment '专业',

age tinyint unsigned comment '年龄',

gender char(1) comment '性别 , 1: 男, 2: 女',

status char(1) comment '状态',

createtime datetime comment '创建时间'

) comment '系统用户表';
-- 添加数据

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');
创建索引
-- 为name创造索引，因为name可能重复，这里创造简单索引

create index idx_user_name on tb_user(name);
-- 为phone创造索引，因为phone只有一个，我们创造唯一索引

create unique index idx_user_phone on tb_user(phone);
-- 创造联合索引，为professin，age，status创造联合索引

create index idx_user_pro_age_status on tb_user(profession,age,status);
-- 为email创造索引

create index idx_user_email on tb_user(email);
删除索引
-- 我们删除email的索引

drop index idx_user_email on tb_user;
查看索引
show index from tb_user;

# 显示SQL中各种语句的访问频率

注意_______是七个_
SHOW GLOBAL STATUS LIKE 'Com_______';

# 开启MYSQL慢日志查询开关

slow_query_log = 1;

设置慢查询日志的时间为2s，即SQL的语句执行时间超过2s就被记录到慢查询日志中
long_query_time = 2;

# 查看是否支持profile操作

SELECT @@have_profiling;

# 开启profile操作

SET profiling = 1;

# 查看每条SQL语句的耗时情况

SHOW profile;

查看指定query_id的SQL语句各个阶段的耗时情况（这里的id是SHOW profile的标号id）
SHOW profile for query query_id;
查看指定query_id的SQL语句CPU的使用情况（这里的id是SHOW profile的标号id）
show profile cpu for query query_id;

-- 直接在select语句之前加上关键字 explain / desc

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

# 假设我们的table中有1w条数据，当我们直接查询时可能需要10s

SELECT * FROM table WHERE name = 02932131;

但当我们创建name索引，这时可能耗时15s
CREATE INDEX table_name_index ON table;
然后我们再凭借name而进行数据查询时，耗时将会接近0s
SELECT * FROM table WHERE name = 02932131;

# 例如我们在前面建立了一个idx_user_pro_age_status索引包含了pro，age，status三个列

当我们从左边往右边逐渐使用时不会产生错误：
explain select * from tb_user where profession = '软件工程' and age = 31 and status= '0';

explain select * from tb_user where profession = '软件工程' and age = 31;

explain select * from tb_user where profession = '软件工程';
但当我们中间省略一列，或者缺少最前面的列，后面的索引列将不再被使用
explain select * from tb_user where age = 31 and status = '0';

explain select * from tb_user where status = '0';

# 我们如果使用 <,> 后面的索引将不再生效

explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';

但我们使用 <=,>= 则不受影响
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

# 如果我们采用substring等操作，索引列将失效

explain select * from tb_user where substring(phone,10,2) = '15';

# 这里最后一部分status的0未加引号，数据仍旧可以输出，但不再通过索引查询

explain select * from tb_user where profession = '软件工程' and age = 31 and status= 0;

# 如果头部出现%，索引失效

-- 索引有效

explain select * from tb_user where profession like '软件%';
-- 索引失效

explain select * from tb_user where profession like '%工程';
-- 索引失效

explain select * from tb_user where profession like '%工%';

# 如果phone被设置有索引 但age未设置索引 ，则采用普通查询方法不采用索引

explain select * from tb_user where phone = '17799990017' or age = 23;

# 假设我们所查询的数据占用该表的大多数数据，可能不采用索引而直接采用全表查询

-- 假设我们希望查询 phone 不为 NULL的行，但全表大部分都不为NULL，则会直接采用全表查询

SELECT * FROM table WHERE phone is not NULL;

# 因为我们的SQL系统自动判定时可能不会采用最佳的运行方法

-- 比如 我们有 profession索引 和 profession，age，status联合索引

-- 当我们希望查询含有profession，age，status的数据时，系统却自动选择profession索引导致速度降低

-- 因而我们需要手动设置SQL提示来提高整体运行速度
推荐使用索引 use index
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
拒绝使用索引 ignore index
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
强迫使用索引 force index
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

# 当我们可以一次性获得所有数据时就不再需要回表查询操作，可以大大提高查询速度

-- 例如：我们的主键为id，索引有name
-- 则下述我们在查询name时，可以获得id，这样就获得了所有数据，就不再进行回表查询

SELECT * FROM table WHERE name = 'xxx';
-- 但如果包含其他元素，我们就会进行回表查询，导致速度降低

SELECT * FROM table WHERE name = 'xxx' and status = '0';

# 选择前缀长度来创建前缀索引

create index idx_xxxx on table_name(column(n)) ;

# 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
我们可以通过下述代码找到 不同元素 和 全部元素 的比例从而获得最佳前缀长度
select count(distinct email) / count() from tb_user ;

select count(distinct substring(email,1,5)) / count() from tb_user ;

# 在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

# 我们在插入数据时，可以一次插入多条数据以进行SQL优化（可以一次插入500~1000条数据）

insert into tb_test values (1,'TOM'),(2,'JERRY')...;

# SQL在每条语句后都进行提交会影响整体性能，我们可以手动提交以减轻电脑负担

start transaction;
insert into tb_test values (1,'TOM'),(2,'JERRY')...;

insert into tb_test values (3,'TaM'),(4,'JyRRY')...;

insert into tb_test values (5,'TeM'),(6,'JiRRY')...;
commit;

# 主键的顺序插入会减轻SQL排序操作直接插入加快速度

主键插入：1，2，3，6，9，12，40，60...

# 如果想要更详细了解，可以移步其他大佬的文章介绍~

-- 客户端连接服务端时，加上参数 -–local-infile

mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关

set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中

load data local infile '/root/sql1.log' into table tb_user fieldsterminated by ',' lines terminated by '\n' ;

# 我们通常直接排序（在不使用主键或者索引时）使用的是Using filesort
explain select id，age，phone from tb_user order by name;
但当我们通过主键排序或者使用索引后，采用Using index，速度提高
create index idx_user_age_phone_aa on tb_user(age,phone);
explain select id,age,phone from tb_user order by age，phone;
explain select id,age,phone from tb_user order by age desc，phone desc;
但是请注意：我们的索引排序也有具有ASC和DESC排序，当我们默认时均为ASC
当我们采用ASC，ASC或DESC，DESC时可以采用Using index，但若以ASC，DESC或DESC，ASC的形式时使用Using filesort
因而我们如果需要ASC，DESC或DESC，ASC的形式时需要再次创建index：create index idx_user_age_phone_aa on tb_user(age ASC,phone DESC);
create index idx_user_age_phone_aa on tb_user(age ASC,phone DESC);
explain select id,age,phone from tb_user order by age ASC，phone DESC;

# 当我们正常使用时，效率较低

explain select profession , count(*) from tb_user group by profession ;

但当我们建立索引后，效率会有明显提升（注意同样满足索引的使用规范）
create index idx_user_pro_age_sta on tb_user(profession , age , status);
explain select profession , count(*) from tb_user group by profession ;

# 当我们希望获得第900000个数据后的十个数据，就需要完全获得前9000000个数据才可以，这会损耗许多时间

优化思路：
我们通过select只获得第9000000个后的十个数据的id
然后通过id对比来获得整行数据：
explain select * from tb_sku t , (select id from tb_sku order by id limit 9000000,10) a where t.id = a.id;

# 我们所需要注意的就是Update的操作尽量采用索引来进行改变，这样锁就会变成行锁，只控制这一行数据

如果我们采用的Update的操作没有使用索引，那么就会采用表锁，导致整个表的数据都无法改变，影响其他人同步修改该表
-- 这个就是采用行锁，你可以在另一个服务器同步修改该表中其他行

update course set name = 'javaEE' where id = 1 ;
-- 这个采用表锁，你无法在另一个服务器同步修改该表

update course set name = 'SpringBoot' where name = 'PHP' ;

# 创建视图

[WITH [CASCADED | LOCAL] CHECK OPTION] 表示限制条件，我们在后续会讲到
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

# 查询

-- 查看创建视图语句

SHOW CREATE VIEW 视图名称;
-- 查看视图数据

SELECT * FROM 视图名称;

# 修改

-- 方法1：

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
-- 方法2：

ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

DROP VIEW [IF EXISTS] 视图名称；

# 我们在对视图进行INSERT操作时，会直接对原表进行操作

但倘若我们对原表操作成功，但是对该视图要求不符合，该操作结构是不会产生在视图中的，导致原表改变但并未达到我们希望的效果
另一方面，我们对视图的创建常常建立于另一视图的操作
倘若我们不对此设置检查，可能导致视图创建失败或对之前视图操作失败

# 首先我们来介绍CHECK操作
-- CHECK操作会对视图要求进行检测并加以约束
-- 假设我们有一个 原表table含有age属性
-- 下述视图没有设置CHECK

CREATE VIEW table_view1 AS SELECT id,age FROM table WHERE age > 20;

-- 当我们对view1操作时,如果添加的数据没有大于20，仍旧会执行成功

INSERT INTO table_view1 values (1,18)；
-- 下述视图设置CHECK

CREATE VIEW table_view2 AS SELECT id,age FROM table WHERE age > 20 WITH CASCADED CHECK OPTION;

-- 当我们对view1操作时,如果添加的数据没有大于20，不会执行成功

INSERT INTO table_view2 values (1,18)；

# 下面我们介绍CASCADED操作

-- CASCADED：不仅为当前视图检查条件，而且为当前视图的之前视图检查条件
-- 假设我们有一个 原表table含有age属性
-- 下述视图没有设置CHECK

CREATE VIEW table_view1 AS SELECT id,age FROM table WHERE age > 20;
-- 下述视图view2以view1为模板设置检查条件

CREATE VIEW table_view2 AS SELECT id,age FROM table WHERE age < 25 WITH CASCADED CHECK OPTION;

-- 这时，我们所添加的数据不仅需要满足当前条件age<25,并且需要满足上一视图条件age>20

INSERT INTO table_view2 values (1,23)；

# 下面我们介绍LOCAL操作

-- LOCAL：只为当前视图检查条件，不为之前视图设置条件
-- 假设我们有一个 原表table含有age属性
-- 下述视图没有设置CHECK

CREATE VIEW table_view1 AS SELECT id,age FROM table WHERE age > 20;
-- 下述视图view2以view1为模板设置检查条件

CREATE VIEW table_view2 AS SELECT id,age FROM table WHERE age < 25 WITH LOCAL CHECK OPTION;

-- 这时，我们所添加的数据只需要满足当前条件age<25即可

INSERT INTO table_view2 values (1,10)；

# 创建存储过程

CREATE PROCEDURE 存储过程名称([参数列表])

BEGIN

--SQL语句

END;

# 调用存储过程

CALL 名称（[参数]）

# 查看存储过程

-- 查询指定数据库的存储过程及状态信息

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'XXX';
-- 查询某个存储过程的定义

HOW CREATE PROCEDURE 存储过程名称 ;

# 删除存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名称;

# 查看系统变量

-- 查看所有系统变量

SHOW [SESSION | GLOBAL] VARIABLES;
-- 可以通过LIKE模糊匹配查找变量

SHOW [SESSION | GLOBAL] VARIABLES LIKE '...';
-- 直接查看指定变量

SELECT @@[SESSION | GLOBAL].系统变量名;
设置系统变量
SET [SESSION | GLOBAL] 系统变量名 = 值;

SET @@[SESSION | GLOBAL].系统变量名 = 值;

# 赋值

-- 直接赋值

SET @var_name = expr [, @var_name = expr] ... ;

SET @var_name := expr [, @var_name := expr] ... ;

SELECT @var_name := expr [, @var_name := expr] ... ;
-- 从表中抽取数据进行赋值

SELECT 字段名 INTO @var_name FROM 表名;
使用
SELECT @var_name ;

# 声明

-- [DEFAULT ...]表示设置初始化值

DECLARE 变量名 变量类型[DEFAULT ...]
赋值
SET 变量名 = 值 ;

SET 变量名 := 值 ;

SELECT 字段名 INTO 变量名 FROM 表名 ... ;

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])

BEGIN

-- SQL语句

END ;

IF 条件1 THEN

.....

ELSEIF 条件2 THEN -- 可选

.....

ELSE -- 可选

.....

END IF;

# case结构1：

-- 当case_value的值为 when_value1时，执行statement_list1，

-- 当值为 when_value2时，执行statement_list2，

-- 否则就执行 statement_list
CASE case_value

WHEN when_value1 THEN statement_list1

[ WHEN when_value2 THEN statement_list2] ...

[ ELSE statement_list ]

END CASE;
case结构2：
-- 当条件search_condition1成立时，执行statement_list1，

-- 当条件search_condition2成立时，执行statement_list2，

-- 否则就执行 statement_list
CASE

WHEN search_condition1 THEN statement_list1

[WHEN search_condition2 THEN statement_list2] ...

[ELSE statement_list]

END CASE;

# while循环结构

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑

WHILE 条件 DO

SQL逻辑...

END WHILE;

# repeat循环结构

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环

REPEAT

SQL逻辑...

UNTIL 条件

END REPEAT;

# loop循环结构

-- begin_label可以自己设置

-- 退出指定标记的循环体：LEAVE label;

-- 直接进入下一次循环： ITERATE label;
[begin_label:] LOOP

SQL逻辑...

END LOOP [end_label];

# 要求：计算从1到n之间的偶数累加的值，n为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值;

-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx

-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx
-- 创建存储过程

create procedure p10(in n int)

begin

-- 设置返回值

declare total int default 0;
-- 进入loop循环，sum是label标记
sum:loop

-- 整体结束判断：如果n减到0, 则退出循环
if n<=0 then
leave sum;
end if;

-- 单个判断：如果当次累加的数据是奇数, 则直接进入下一次循环.
if n%2 = 1 then
set n := n - 1;
iterate sum;
end if;

-- 如果没有问题，最后执行语句
set total := total + n;
set n := n - 1;

-- 结束loop循环
end loop sum;

-- 输出结果
select total;

end;
-- 执行存储过程

call p10(100);

# 声明游标

DECLARE 游标名称 CURSOR FOR 查询语句 ;

打开游标
OPEN 游标名称;
获得游标记录
FETCH 游标名称 INTO 变量[,变量];
关闭游标
CLOSE 游标名称;

# 要求：根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

-- 逻辑:

-- A. 声明游标, 存储查询结果集

-- B. 准备: 创建表结构

-- C. 开启游标

-- D. 获取游标中的记录

-- E. 插入数据到新表中

-- F. 关闭游标
-- 创建存储过程

create procedure p11(in uage int)

begin

-- 定义变量：用户姓名，用户专业

declare uname varchar(100);

declare upro varchar(100);
-- 定义游标，并设置得到之前表中所需数据
declare u_cursor cursor for select name,profession from tb_user where age <=uage;

-- 创建表
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
    id int primary key auto_increment,
    name varchar(100),
    profession varchar(100)
);

-- 打开游标
open u_cursor;

-- 获得数据
while true do
	-- 将游标数据赋值给变量
    fetch u_cursor into uname,upro;
    -- 将变量数据上传至新表中
    insert into tb_user_pro values (null, uname, upro);
end while;

-- 关闭游标
close u_cursor;

end;
-- 调用存储过程

call p11(30);

# handler结构

DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement ;
handler_action 的取值：

CONTINUE: 继续执行当前程序

EXIT: 终止执行当前程序
condition_value 的取值：

SQLSTATE sqlstate_value: 状态码，如 02000

SQLWARNING: 所有以01开头的SQLSTATE代码的简写

NOT FOUND: 所有以02开头的SQLSTATE代码的简写

SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

# 在上述的游标案例中，我们的while循环无法关闭，因为无法判断游标中的值何时结束

所以我们需要采用HANDLER结构来关闭WHILE循环
-- 创建存储过程

create procedure p11(in uage int)

begin
declare uname varchar(100);
declare upro varchar(100);


declare u_cursor cursor for select name,profession from tb_user where age <=uage;

-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
-- 这里也可以采用NOT FOUND 状况码进行判断并退出
declare exit handler for SQLSTATE '02000' close u_cursor;

drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
    id int primary key auto_increment,
    name varchar(100),
    profession varchar(100)
);

open u_cursor;

while true do
    fetch u_cursor into uname,upro;
    insert into tb_user_pro values (null, uname, upro);
end while;

close u_cursor;

end;
-- 调用存储过程

call p11(30);

# 存储函数创建结构

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])

RETURNS type [characteristic ...]

BEGIN

-- SQL语句

RETURN ...;

END ;
characteristic 可以有以下三种情况：
-- DETERMINISTIC：相同的输入参数总是产生相同的结果

-- NO SQL ：不包含 SQL 语句。

-- READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

# 要求：计算从1累加到n的值，n为传入的参数值。

-- 创建存储函数

create function fun1(n int)

returns int deterministic

begin

declare total int default 0;
while n>0 do
    set total := total + n;
    set n := n - 1;
end while;

return total;

end;
-- 执行存储函数

select fun1(50);

# 创建触发器

CREATE TRIGGER trigger_name

BEFORE/AFTER INSERT/UPDATE/DELETE

ON tbl_name FOR EACH ROW

BEGIN

trigger_stmt ;

END;
解释
-- BEFORE/AFTER 表示是在操作进行前/操作进行后触发

-- INSERT/UPDATE/DELETE 表示操作类型

-- FOR EACH ROW 表示行级触发器

# 查看触发器

SHOW TRIGGERS ;

# 删除触发器

DROP TRIGGER [schema_name.]trigger_name ;

-- 如果没有指定 schema_name，默认为当前数据库 。

create table user_logs(

id int(11) not null auto_increment,

operation varchar(20) not null comment '操作类型, insert/update/delete',

operate_time datetime not null comment '操作时间',

operate_id int(11) not null comment '操作的ID',

operate_params varchar(500) comment '操作参数',

primary key(id)

)engine=innodb default charset=utf8;

create trigger tb_user_insert_trigger

after insert on tb_user for each row

begin

insert into user_logs(id, operation, operate_time, operate_id, operate_params)

VALUES

(null, 'insert', now(), new.id, concat('插入的数据内容为:id=',new.id,',name=',new.name, ', phone=', 		NEW.phone, ', email=', NEW.email, ',profession=', NEW.profession));

end;

create trigger tb_user_update_trigger

after update on tb_user for each row

begin

insert into user_logs(id, operation, operate_time, operate_id, operate_params)

VALUES

(null, 'update', now(), new.id,concat('更新之前的数据: id=',old.id,',name=',old.name, ', 					phone=',old.phone, ', email=', old.email, ', profession=', old.profession,' | 更新之后的数据: 				id=',new.id,',name=',new.name, ', phone=',NEW.phone, ', email=', NEW.email, ', profession=', 				NEW.profession));

end;

create trigger tb_user_delete_trigger

after delete on tb_user for each row

begin

insert into user_logs(id, operation, operate_time, operate_id, operate_params)

VALUES

(null, 'delete', now(), old.id,concat('删除之前的数据: id=',old.id,',name=',old.name, ', 					phone=',old.phone, ', email=', old.email, ', profession=', old.profession));

end;

# 这里对 全库的逻辑备份 做出解释

-- 当我们进行全库逻辑备份时，在实际开发不止一台客户端操作该数据库

-- 整个数据库的保存备份需要一定时间，如果我们在保存过程中，有其他客户端用户进行操作，就会导致备份与原稿不同

# 设置全局锁

flush tables with read lock;

进行备份
mysqldump -uroot -p123456 itcast > itcaset.sql

-- -u后加账号 -p后加密码 itcast为数据库名称 itcast.sql为本地地址
关闭全局锁
unlock tables;

# 在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

# 加锁操作：

lock tables 表名... read/write;

释放锁：
unlock tables / 断开客户端连接

# 注意：下述客户端1，2号是同时进行，同时开始事务！

这里是 客户端1号
-- 首先我们需要开启事务，才能体现锁的作用

begin;
-- 这里我们使用select 、select ... lock in share mode 开启了SHARED_READ锁

select * from table;
-- 然后我们提交事务

commit;
这里是 客户端2号
-- 我们同样采用select 、select ... lock in share mode ，这里反馈的是SHARED_READ锁，与前面的SHARED_READ锁兼容，可以运行

select * from table1;
-- 这里我们采用insert方法，反馈为SHARED_WRITE锁，与前面的SHARED_READ锁兼容，可以运行

update table1 set name = '叶' where id = '1';
-- 然后我们提交事务

commit;

# 注意：下述客户端1，2号是同时进行，同时开始事务！

这里是 客户端1号
-- 首先我们需要开启事务，才能体现锁的作用

begin;
-- 这里我们使用select 、select ... lock in share mode 开启了SHARED_READ锁

select * from table;
-- 然后我们提交事务

commit;
这里是 客户端2号
-- 这里我们采用alter方法，这里触发的是EXCLUSIVE锁，与之前的SHARED_READ锁不兼容

-- 则一直等待，直至客户端1号提交后，才会进行操作

alter table table1 add java int;
-- 然后我们提交事务

commit;

# 查看所有锁

select object_type,object_schema,object_name,lock_type,lock_duration fromperformance_schema.metadata_locks ;

# 这里是没有意见锁的情况下：

-- 我们在客户端1中使用了行级锁，这时就不能使用表级锁

-- 表级锁在使用前，需要一行一行检查该行是否具有行级锁，在确认所有行均没有行级锁后才可以进行表级锁

-- 这种操作效率低下
这里是有意见锁的情况下：
-- 我们在客户端1中使用了行级锁并且设置了意见锁，这时就不能使用表级锁

-- 表级锁在使用前，只需要检查是否含有意见锁，若含有则等待至意见锁消失（客户端1事务结束）才可以进行表级锁

-- 这种操作效率较高

# 这里是客户端1

-- 开启事务

begin；
-- 首先我们设置意见锁：

select * from table1 lock in share mode;
-- 提交事务

commit;
#这里是客户端2
-- 开启事务

begin;
-- 在IS中，我们只能进行read表锁操作

lock tables table1 read;-- 运行成功
-- 在IS中，我们无法进行write操作

lock tables table1 write;-- 运行失败
-- 关闭锁

unlock tables;
-- 关闭事务

commit;

# 这里是客户端1

-- 开启事务

begin；
-- 首先我们设置意见锁：

select * from table1 lock in share mode;
-- 提交事务

commit;
#这里是客户端2
-- 开启事务

begin;
-- 在IX中，我们无法进行read表锁操作

lock tables table1 read;-- 运行失败，进行堵塞，当客户端提交事务后才可进行
-- 在IX中，我们无法进行write操作

lock tables table1 write;-- 运行失败，进行堵塞，当客户端提交事务后才可进行
-- 关闭锁

unlock tables;
-- 关闭事务

commit;

# 客户端1
-- 开启事务

begin；
-- 正常select语句，不加锁，对另一个客户端无影响

select * from table1;
-- 提交事务

commit；
客户端2

# 客户端1
-- 开启事务

begin；
-- select...lock in share mode，加共享锁，共享锁与共享锁之间兼容。

select * from table1 in share mode;
-- 提交事务

commit；
客户端2
-- 开启事务

begin；
-- 客户端1采用共享锁，这里同样采用共享锁，可以同时触发

select * from table1 in share mode;
-- 提交事务

commit；

# 客户端1

-- 开启事务

begin；
-- select...lock in share mode，加共享锁，

select * from table1 in share mode;
-- 提交事务

commit；
客户端2
-- 开启事务

begin；
-- 客户端1采用共享锁，但这里使用update为排他锁，不能同时使用，该事务等待，直至客户端1提交后才可以运行

update table1 set name = 'xxx' where id = '1';
-- 提交事务

commit；

# 客户端1

-- 开启事务

begin；
-- 这里设置为排他锁

update table1 set name = 'xxx' where id = '1';
-- 提交事务

commit；
客户端2
-- 开启事务

begin；
-- 客户端1采用排他锁，这里也采用排他锁，两者互斥，需要等待客户端1提交事务后才可以运行

update table1 set name = 'xxx' where id = '1';
-- 提交事务

commit；

# 当我们依据索引进行的查找 属于 行锁

-- 例如下述我们采用id进行查找，属于正常行锁
客户端1
-- 开启事务

begin；
-- 这里设置为排他锁

update table1 set name = 'xxx' where id = '1';
-- 提交事务

commit；
客户端2
-- 开启事务

begin；
-- 因为上述属于行锁，只锁住id为1的行信息，我们下述修改id为2的内容不受影响

update table1 set name = 'xxx' where id = '2';
-- 提交事务

commit；
当我们依据非索引进行的查找 属于 表锁
-- 例如下述我们采用name进行查找，这里就属于表锁
客户端1
-- 开启事务

begin；
-- 因为这里的查找并未采用索引查找，所以行锁自动变成表锁，整个表无法进行修改（假设为id为2的行信息）

update table1 set age = 18 where name = 'xxx';
-- 提交事务

commit；
客户端2
-- 开启事务

begin；
-- 因为客户端1启动了表锁，所以我们下述的修改无法成功

update table1 set name = 'xxx' where id = '1'; -- 运行失败
-- 提交事务

commit；

# 我们给出表结构以及信息

-- id  name age

-- 1   xxx	15

-- 3   xxx	23

-- 7   xxx	32

-- 15  xxx	16

-- 25  xxx	27
客户端1
-- 开启事务

begin；
-- 当我们进行查询，并且查询的信息是不存在的时

-- 我们会在不存在的信息的前后之间加上间隙锁，例如下述代码会导致：3~7之间存在间隙锁，不能对此进行修改

update table set name = 'xxx' where id = 5;
-- 提交事务

commit；
客户端2
-- 开启事务

begin；
-- 因为上述我们对3~7之间设置了间隙锁，我们不能在此之间添加信息

insert into table values (6,'xxx',28) -- 运行错误
-- 提交事务

commit；

# 介绍分析一下：

-- 我们知道InnoDB的B+树索引，叶子节点是有序的双向链表。

-- 假如，我们要根据这个二级索引查询值为x的数据，并加上共享锁，我们是只锁定x这一行就可以了吗？

-- 并不是，因为是非唯一索引，这个结构中可能有多个x的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值。

-- 此时会对x加临键锁，并对x到一个不为x的数之前的间隙加锁。

-- 整体目的就是为了防止出现新的 等值信息 导致结果发生变化
我们给出表结构以及信息
-- id  name age

-- 1   xxx	15

-- 3   xxx	23

-- 7   xxx	32

-- 15  xxx	16

-- 25  xxx	27
我们只使用一个客户端
-- 开启事务

begin；
-- 我们设置普通索引，并通过普通索引进行操作

create index ind_table_age on table(age);
-- 我们通过age查找id为3的行信息并加锁

select * from table where age = 23 lock in share mode;
-- 提交事务

commit；
-- 然后我们进行锁查询

-- 查看所有锁

select object_type,object_schema,object_name,lock_type,lock_duration fromperformance_schema.metadata_locks ;
接下来我们可以看到的是
-- 在 33 之间存在间隙锁

-- 在 37 之间存在间隙锁

-- 在 3 上存在行锁

# 稍微先提示一下：

-- 当我们进行范围查询时，我们所需要锁住的信息包括这个范围中的所有信息

我们给出表结构以及信息
-- id  name age

-- 1   xxx	15

-- 3   xxx	23

-- 7   xxx	32

-- 19  xxx	16

-- 25  xxx	27
我们只使用一个客户端
-- 开启事务

begin；
-- 我们希望查找id>=15的值

select * from table where id >=15 lock in share mode;
-- 提交事务

commit；
-- 然后我们进行锁查询

-- 查看所有锁

select object_type,object_schema,object_name,lock_type,lock_duration fromperformance_schema.metadata_locks ;
接下来我们可以看到的是
-- 针对单个 id=19 的行锁

-- 在 （19，25] 之间的临键锁

-- 在 （25，正无穷] 之间的临键锁

语法 ：
	mysql [options] [database]
选项 ：
    -u, --user=name #指定用户名
    -p, --password[=name] #指定密码
    -h, --host=name #指定服务器IP或域名
    -P, --port=port #指定连接端口
    -e, --execute=name #执行SQL语句并退出

语法:
    mysqladmin [options] command ...
选项:
    -u, --user=name #指定用户名
    -p, --password[=name] #指定密码
    -h, --host=name #指定服务器IP或域名
    -P, --port=port #指定连接端口
通过帮助文档查看选项：
    mysqladmin --help

语法 ：
    mysqlbinlog [options] log-files1 log-files2 ...
选项 ：
    -d, --database=name 指定数据库名称，只列出指定的数据库相关操作。
    -o, --offset=# 忽略掉日志中的前n行命令。
    -r,--result-file=name 将输出的文本格式日志输出到指定文件。
    -s, --short-form 显示简单格式， 省略掉一些信息。
    --start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。
    --start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。

语法 ：
    mysqlshow [options] [db_name [table_name [col_name]]]
选项 ：
    --count 显示数据库及表的统计信息（数据库，表 均可以不指定）
    -i 显示指定数据库或者指定表的状态信息
示例：
    #查询test库中每个表中的字段书，及行数
    mysqlshow -uroot -p2143 test --count
    #查询test库中book表的详细情况
    mysqlshow -uroot -p2143 test book --count

语法 ：
    mysqldump [options] db_name [tables]
    mysqldump [options] --database/-B db1 [db2 db3...]
    mysqldump [options] --all-databases/-A
连接选项 ：
    -u, --user=name 指定用户名
    -p, --password[=name] 指定密码
    -h, --host=name 指定服务器ip或域名
    -P, --port=# 指定连接端口
输出选项：
    --add-drop-database 在每个数据库创建语句前加上 drop database 语句
    --add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
    -n, --no-create-db 不包含数据库的创建语句
    -t, --no-create-info 不包含数据表的创建语句
    -d --no-data 不包含数据
    -T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

语法 ：
    mysqlimport [options] db_name textfile1 [textfile2...]
示例 ：
    mysqlimport -uroot -p2143 test /tmp/city.txt

语法 ：
    source /root/xxxxx.sql