MySql基础汇总-DDL DML DQL DCL，存储过程，事务，触发器，函数等

use testdb;

-- /**********************************************一：数据库操作语言**********************************************/
-- DDL: 数据库定义语言：create drop alter
-- DML: 数据库管理语言：insert update delete
-- DQL: 数据库查询t_usert_student 语言：select 
-- DCL: 数据库控制语言：grant revoke commit

-- /**********************************************二：数据库类型**********************************************/
-- 整数类型：tinyint  int(11) smallint mediumint  bigint 
-- 浮点类型: float double decimal(18,2)
-- 字符类型: char(1) varchar(50) text Blob   
-- *TEXT和BLOB家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，
-- *而TEXT类型有字符集或排序规则。说白了如果要储存中文则选择TEXT。
-- 日期类型: date time datetime timespan

/**********************************************三：数据库DDL语言**********************************************/
-- 【create table】 创建表
CREATE TABLE t_user (
    t_id INT NOT NULL PRIMARY KEY UNIQUE KEY  AUTO_INCREMENT COMMENT '用户id',
    t_name VARCHAR(50) NULL COMMENT '用户姓名',
    t_sex char(1) not null  default '男' comment '性别',
		t_score	decimal(10,2) not NULL DEFAULT '0.00' comment '分数',
    t_memo longtext null comment '备注',
    create_user varchar(50) null comment '创建人',
    create_time datetime null comment '创建时间',
    modify_time datetime null comment '最后一次修改时间',
    modify_user varchar(50) null comment '最后一次修改人',
    delete_time datetime null comment '删除时间',
    delete_user varchar(50) null comment '删除人',
    is_deleted  int not null default 0 comment '是否删除(0:有效 1:已删除)'
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET= utf8;

-- 【alter table add column】  新增一列
alter table t_user add column t_birthday datetime null comment '用户生日';
-- 【alter table modify column】 修改字段的属性
alter table t_user modify column t_birthday datetime not null comment '用户生日';
-- 【alter table change column newcolumn】 修改字段的属性或名字
alter table t_user change  t_birthday  t_birth datetime not null comment '用户生日';
-- 【alter table drop column】  删除字段
alter table t_user drop t_birth;
-- 【show create table】  
show  create table t_user;
-- 【rename table to newrtable】
rename table t_user to my_user;
rename table my_user to t_user;


/**********************************************四：数据库DML语言**********************************************/
create table t_student
(  
   t_sId int not null primary key unique key auto_increment  comment '学生主键id'  ,
	 t_id int not NULL DEFAULT 0 COMMENT '用户主键id',
	 tsg_id int not NULL DEFAULT 0 COMMENT '学生等级主键id',
   t_sName varchar(50) null  comment '学生姓名',
   t_sPhone varchar(20) null comment '学生手机号',
   create_user varchar(50) null comment '创建人',
   create_time datetime null comment '创建时间',
   modify_time datetime null comment '最后一次修改时间',
   modify_user varchar(50) null comment '最后一次修改人',
   delete_time datetime null comment '删除时间',
   delete_user varchar(50) null comment '删除人',
   is_deleted  int not null default 0 comment '是否删除(0:有效 1:已删除)'
   
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET= utf8;

-- 【insert】 插入数据
insert into t_user(t_name,t_sex,t_score,t_memo,create_user,create_time)
values('kobe','男',10,'备注','sys',now()),('james','男',11,'备注','sys',now()),('harden','男',12,'备注','sys',now());

insert into t_student(t_id,tsg_id,t_sName,t_sPhone,create_user,create_time) 
values
(1,1,'学生kobe1','15824130001','sys',now()),(1,1,'学生kobe2','15824130002','sys',DATE_ADD(NOW(),INTERVAL '20' MINUTE)),(1,1,'学生kobe3','15824130003','sys',DATE_ADD(NOW(),INTERVAL '10' MINUTE)),
(2,2,'学生james1','15824130001','sys',now()),(2,2,'学生james2','15824130002','sys',DATE_ADD(NOW(),INTERVAL '20' MINUTE)),(2,2,'学生james3','15824130003','sys',DATE_ADD(NOW(), INTERVAL'10' MINUTE)),
(3,3,'学生harden1','15824130001','sys',now()),(3,3,'学生harden2','15824130002','sys',DATE_ADD(NOW(),INTERVAL '20' MINUTE)),(3,3,'学生harden3','15824130003','sys',DATE_ADD(NOW(), INTERVAL'10' MINUTE));


-- 【update】 更新语句
-- mysql直接执行where后面不是主键条件的会报错，执行前都需要把安全模式改为0
SET SQL_SAFE_UPDATES = 0;
update t_user set t_memo='男性备注' where t_sex='男';
update t_user set t_memo= (select create_user from t_student where t_sId=1);
UPDATE t_user 
SET 
    t_sex = CASE
        WHEN t_name = 'kobe' THEN '男'
        ELSE '女'
    END;
-- 【delete】 删除语句
delete from t_user where t_id>2;


-- /**********************************************五：数据库DQL语言**********************************************/
-- 【"=" "!=" "<>" "between and" "in" "not in" ">=" "<=" "is null" "and" "or" "not"】
select * from t_user where t_id between 1 and 3;
select * from t_user where modify_time is  null;

-- 【"_" "like"】 模糊查询
SELECT * from t_user where t_name like '____';  -- 表示4个字符的名称
SELECT * FROM t_user WHERE t_name like '%o%';

-- 【DISTINCT】 去重
SELECT DISTINCT(t_sex) FROM t_user;

-- 【IFNULL(expr1,expr2)】 如果为空 
SELECT t_name,IFNULL(modify_user,'修改者') from t_user;

-- 【ORDER BY】  排序
SELECT * FROM t_user ORDER BY create_time DESC,t_id ASC;

-- 【GROUP BY】  分组
SELECT t_sex,SUM(t_score) from t_user GROUP BY t_sex;

-- 【GROUP_CONCAT(expr)】 分组后对其他字段进行逗号分隔的字符串连接
SELECT t_sex,GROUP_CONCAT(t_name) as '姓名' FROM t_user GROUP BY t_sex;

-- 【HAVING】  WHERE是分组前的筛选，HAVING是分组后的筛选
SELECT t_sex,SUM(t_score) as sumScore from t_user GROUP BY t_sex HAVING SUM(t_score) > 1;

-- 【LIMIT pageIndex,pageSize】
-- 从0开始第一条,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目
-- 只有一个参数时是返回前几条数据 
-- 选择第一条数据 是LIMIT 1，而不是LIMIT 0
SELECT * FROM t_user WHERE t_sex='男' ORDER BY t_id  LIMIT 0,2;

-- 【UNION】
-- 前提条件： 两表的列的数量和类型必须完全一致，否则报错
-- 返回内容： 合并时【会】对列的【内容】做去重处理(指的是所有列的内容完全一样)，

-- 【UNION ALL】
-- 前提条件： 两表的列的数量和类型必须完全一致，否则报错
-- 返回内容： 合并时【不会】对列的【内容】做去重处理(指的是所有列的内容完全一样)，
-- news
CREATE TABLE news(n_id int,n_title VARCHAR(100),n_content VARCHAR(500));
-- news_history
CREATE TABLE news_history(n_id int,n_title VARCHAR(100),n_content VARCHAR(500));
INSERT INTO news VALUES(1,'标题1','标题1content');
INSERT INTO news VALUES(2,'标题2','标题2content');
INSERT INTO news VALUES(3,'标题3','标题3content');
INSERT INTO news_history VALUES(1,'标题1','标题1content');
INSERT INTO news_history VALUES(2,'标题2','标题2content');
INSERT INTO news_history VALUES(3,'标题3','标题3content');
SELECT * FROM news UNION select * from news_history;
-- 1	标题1	标题1content
-- 2	标题2	标题2content
-- 3	标题3	标题3content
SELECT * FROM news UNION ALL select * from news_history;
-- 1	标题1	标题1content
-- 2	标题2	标题2content
-- 3	标题3	标题3content
-- 1	标题1	标题1content
-- 2	标题2	标题2content
-- 3	标题3	标题3content


-- 【INNER JOIN】 内连接-等值连接
CREATE TABLE t_student_grade
(
   tsg_id INT not null   PRIMARY KEY UNIQUE KEY auto_increment  COMMENT '主键id', 
	 tsg_name VARCHAR(50) NULL COMMENT '等级名称',
	 create_user varchar(50) null comment '创建人',
   create_time datetime null comment '创建时间',
   modify_time datetime null comment '最后一次修改时间',
   modify_user varchar(50) null comment '最后一次修改人',
   delete_time datetime null comment '删除时间',
   delete_user varchar(50) null comment '删除人',
   is_deleted  int not null default 0 comment '是否删除(0:有效 1:已删除)'
) ENGINE=INNODB auto_increment=1 DEFAULT CHARSET =utf8;

INSERT INTO t_student_grade(tsg_name,create_user,create_time) VALUES('三年级','sys',NOW()),('四年级','sys',NOW()),('五年级','sys',NOW());

SELECT t.t_id,t.t_name,b.t_sName,b.t_sPhone,c.tsg_name FROM t_user as a
INNER JOIN t_student as b on t.t_id=b.t_id AND t.t_id<=4 -- 效果与where一样
INNER JOIN t_student_grade as c on b.tsg_id=c.tsg_id
-- WHERE t.t_id<=4

-- 【INNER JOIN】 内连接-非等值连接
SELECT t.t_id as a_tid,t.t_name,b.t_id as t_tid,b.t_sName,b.t_sPhone FROM t_user as a
INNER JOIN t_student as b on t.t_id>b.t_id

-- 【OUTER LEFT JOIN】 外连接-左外连接 
SELECT t.t_id,t.t_name,b.t_sName,b.t_sPhone,c.tsg_name FROM t_user as a
LEFT JOIN t_student as b on t.t_id=b.t_id 
LEFT JOIN t_student_grade as c on b.tsg_id=c.tsg_id
WHERE t.t_id<=4

-- 【OUTER RIGHT JOIN】 外连接-右外连接 
SELECT t.t_id,t.t_name,b.t_sName,b.t_sPhone,c.tsg_name FROM t_user as a
RIGHT JOIN t_student as b on t.t_id=b.t_id 
RIGHT JOIN t_student_grade as c on b.tsg_id=c.tsg_id
WHERE t.t_id<=4;

-- 【NATURAL JOIN】 自然连接 
-- 前提条件： 两表的列的数量和类型必须完全一致，否则报错
-- 返回内容： 合并时【会】对列的【名称】做去重处理
SELECT * from news as a NATURAL JOIN news_history as b;
-- 1	标题1	标题1content
-- 2	标题2	标题2content
-- 3	标题3	标题3content

SELECT * FROM news UNION SELECT * from news_history; 
-- 1	标题1	标题1content
-- 2	标题2	标题2content
-- 3	标题3	标题3content


-- 【CROSS JOIN】 交叉连接
-- 返回内容：把表A和表B的数据进行一个N*M的组合，即笛卡尔积。
SELECT * FROM t_user CROSS JOIN t_student;


-- 【利用Group_CONCAT和SubStringIndex来实现RowNumber(partition by)的功能】
-- 根据创建时间倒序，根据性别分组，取每种性别的第一条用户信息
SELECT a.t_sex, 
SUBSTRING_INDEX(GROUP_CONCAT(a.t_name ORDER BY b.create_time DESC),',',1)  as t_name, 
SUBSTRING_INDEX(GROUP_CONCAT(a.t_score ORDER BY b.create_time DESC),',',1) as t_score,
SUBSTRING_INDEX(GROUP_CONCAT(b.t_sName ORDER BY b.create_time DESC),',',1) as t_sName, 
SUBSTRING_INDEX(GROUP_CONCAT(b.t_sPhone ORDER BY b.create_time DESC),',',1) as t_sPhone,
SUBSTRING_INDEX(GROUP_CONCAT(b.create_time ORDER BY b.create_time DESC),',',1)  as create_time FROM
t_user as a
INNER JOIN t_student as b on a.t_id=b.t_id
GROUP BY  a.t_sex;

-- 【EXISTS】 子查询 
SELECT * FROM t_user as a 
WHERE NOT EXISTS (SELECT 1 FROM t_student as b WHERE a.t_id=b.t_id);

/**********************************************六：视图**********************************************/
CREATE or REPLACE VIEW t_userstu_view
as 
SELECT a.t_id as u_id,a.t_name,a.t_sex,b.t_id as s_id,b.t_sName FROM t_user as a
LEFT JOIN t_student as b on a.t_id=b.t_id;
-- 默认是不可修改

SELECT * FROM  t_userstu_view;

DROP VIEW t_userstu_view;

/**********************************************七：系统函数**********************************************/
-- /**********************************【数学函数】*************************************/
-- 【ABS】 返回绝对值
SELECT ABS(-1);    -- 1

-- 【MOD】/ % 返回两个数的取余
SELECT MOD(7,2);   -- 1
SELECT 7%2;				 -- 1

-- 【FLOOR(X)】 返回比这个数小的最临近的整数
SELECT FLOOR(1);   -- 1
SELECT FLOOR(1.6);   -- 1
SELECT FLOOR(-1.2);   -- -2

-- 【CEILING(X)】返回比这个数大的最临近的整数
SELECT CEILING(1);    -- 1
SELECT CEILING(1.6);   -- 2
SELECT CEILING(-1.2);   -- -1

-- 【ROUND(X)】 返回这个数的四舍五入的整数,与正负无关，只对数字进行四舍五入，然后加上正负
SELECT ROUND(1.49);  -- 1
SELECT ROUND(1.5);   -- 2
SELECT ROUND(-1.49); -- -1
SELECT ROUND(-1.5);   -- -2

-- /**********************************【字符串函数】*************************************/
-- 【ASCII(str)】 返回这个字符串左边第一个字符的ASCII码
SELECT ASCII('ab');  -- 97

-- 【CONCAT(str1,str2,...)】 返回多个字符串的拼接字符串
SELECT CONCAT('h','e','llo');  -- 'hello'
SELECT CONCAT_WS(',','h','e','llo'); -- 'h,e,llo'

-- 【TRIM(str)】 返回去空格的字符串
SELECT TRIM(' he ll o '); -- 'he ll o' 去掉左边和右边的空格(中间的不会去掉)
SELECT LTRIM(' he ll o')；-- 'he ll o' 去掉左边的空格
SELECT RTRIM('he ll o ')；-- 'he ll o' 去掉右边的空格
-- 那么问题来了，如何去掉字符串' he ll o '中所有出现的空格？
SELECT REPLACE(' he ll o ',' ','') -- 'hello';

-- 【LENGTH(str)】 返回字符串的长度(包含空格的数量)
SELECT LENGTH(' he ll o '); -- 9

-- 【REPLACE(str,from_str,to_str)】 返回替换后字符串的内容
SELECT REPLACE('hello','llo','llo world!'); -- hello world!

-- 【LOCATE(str1,str2)】 返回字符存在改字符串中的位置(类似index of ,索引从0开始计算，不存在时返回0而不是0)
SELECT LOCATE('e',' he ll o '); -- 3
SELECT LOCATE('w',' he ll o '); -- 0

-- 【INSERT】 返回插入指定位置,替换指定长度的字符的字符串
SELECT INSERT('hello ',6,1,' world!'); -- 'hello world!'
SELECT INSERT('hello',2,1,'s'); -- 'hsllo'
SELECT INSERT('hello',2,2,'s'); -- 'hslo'
SELECT INSERT('hello',2,1,'ss'); -- 'hssllo'
SELECT INSERT('hello',2,2,'ss'); -- 'hsslo'
SELECT INSERT('hello',2,3,'ss'); -- 'hsso'

-- /**********************************【日期和时间函数】*************************************/
-- 【NOW()/CURRENT_TIMESTAMP】 返回当前时间
SELECT NOW(); -- '2019-09-06 14:48:12'
SELECT CURRENT_TIMESTAMP; -- '2019-09-06 14:48:12'

-- 【YEAR/MONTH/DAY/HOUR/MINUTE/SECOND...】 返回日期的年月日时分秒毫秒
SELECT DATE('2000-01-02 03:04:05.006'); -- 2000-01-02
SELECT TIME('2000-01-02 03:04:05.006'); -- 03:04:05
SELECT YEAR('2000-01-02 03:04:05.006'); -- 2000
SELECT MONTH('2000-01-02 03:04:05.006'); -- 1
SELECT DAY('2000-01-02 03:04:05.006'); -- 2
SELECT HOUR('2000-01-02 03:04:05.006'); -- 3 
SELECT MINUTE('2000-01-02 03:04:05.006'); -- 4
SELECT SECOND('2000-01-02 03:04:05.006'); -- 5
SELECT MICROSECOND('2000-01-02 03:04:05.006'); -- 6000 获取日期的【微秒】
SELECT DAYOFWEEK('2000-01-02 03:04:05.006'); -- 1(1:星期天 2:星期一 3:星期二...)

-- 【DATEDIFF(expr1,expr2)】 返回两个日期之间的天数(第一个时间-第二个参数)
SELECT DATEDIFF('2000-01-02 03:04:05.006','2000-01-01 03:04:05.006'); -- 1

-- 【TIMEDIFF(expr1,expr2)】 返回两个日期之间的时分秒时间戳(第一个时间-第二个参数)
SELECT TIMEDIFF('2000-01-01 05:04:05.006','2000-01-01 04:05:05.006'); -- 00:59:00.000

-- 【TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)】 返回两个日期之间的年/月/日/时/分/秒(第一个时间-第二个参数)
SELECT TIMESTAMPDIFF(YEAR,'2000-01-02 03:04:05.006','2000-01-01 03:04:05.006'); -- 0
SELECT TIMESTAMPDIFF(MONTH,'2000-01-02 03:04:05.006','2000-01-01 03:04:05.006'); -- 0
SELECT TIMESTAMPDIFF(DAY,'2000-01-02 03:04:05.006','2000-01-01 03:04:05.006'); -- 0

-- 【DATE_ADD(date,INTERVAL expr unit)】 返回一个日期增加的年/月/日/时/分/秒
SELECT DATE_ADD('2000-01-01 05:04:05.006',INTERVAL 1 YEAR); -- 2001-01-01 05:04:05.006000
SELECT DATE_ADD('2000-01-01 05:04:05.006',INTERVAL 1 MONTH); -- 2000-02-01 05:04:05.006000
SELECT DATE_ADD('2000-01-01 05:04:05.006',INTERVAL 1 DAY); -- 2000-01-02 05:04:05.006000

-- 【DATE_FORMAT】  日期转字符串
SELECT DATE_FORMAT('2000-01-01 05:04:05','%Y%m%d%H%i%s'); -- 20000101050405

-- 【STR_TO_DATE】  字符串转日期
SELECT STR_TO_DATE('20000101050405','%Y%m%d%H%i%s'); -- 2000-01-01 05:04:05

-- /**********************************【流程控制函数】*************************************/
-- 【CASE WHEN THEN ELSE END】
SELECT CASE WHEN t_sex='男' THEN '1' ELSE '2' END as t_sex FROM t_user;

/**********************************************八：自定义函数**********************************************/
-- 【CREATE FUNCTION】创建
-- 8.0版本MySQL创建函数需要在RETURNS TYPE后面，BEGIN前面加上创建类型。 DETERMINISTIC，注意DETERMINISTIC插入位置。
-- 一般有下面这几种类型：
-- 1）DETERMINISTIC：不确定的
-- 2）NO SQL：没有SQL语句
-- 3）READS SQL DATA：读取数据，不涉及修改数据
-- 4）MODIFIES SQL DATA：涉及到修改数据
-- 5）CONTAINS SQL：包含了SQL语句

-- 创建函数
-- 【INTO】 赋值
CREATE FUNCTION fun_get_user_name(xid INT) RETURNS VARCHAR(50) DETERMINISTIC
BEGIN
   DECLARE xuname VARCHAR(50);
	 SELECT t_name INTO xuname FROM t_user WHERE t_id=xid;
	 RETURN xuname;
END;
SELECT fun_get_user_name(1);

-- 【IF/ELSE IF/SET】IF判断
CREATE FUNCTION fun_get_week(xweek int) RETURNS VARCHAR(50) DETERMINISTIC
BEGIN
-- 使用set赋值时，使用的是“=”，使用select赋值时使用的是“:="。
   DECLARE rweek VARCHAR(50) DEFAULT 'unknown'; -- 局部变量默认值
	 if(xweek = 1) THEN
	 SET rweek='星期天';
	 ELSEIF(xweek=2) THEN
	 SET rweek='星期一';
	 ELSEIF(xweek=3) THEN
	 SET rweek='星期二';
	 ELSEIF(xweek=4) THEN
	 SET rweek='星期三';
	 ELSEIF(xweek=5) THEN
	 SET rweek='星期四';
	 ELSEIF(xweek=6) THEN
	 SET rweek='星期五';
	 ELSEIF(xweek=7) THEN
	 SET rweek='星期六';
	 ELSE
	 SET rweek='unknown';
	 END IF;
	 RETURN rweek;
END;
SELECT fun_get_week(3);

-- 【LOOP/REPEAT/WHILE】循环
CREATE FUNCTION fun_get_sum(max_num int) RETURNS int DETERMINISTIC -- 表示确定的可能有查询，可能有修改
BEGIN
--     需求：求1+2+3....+100
--    【WHILE...DO】 
       DECLARE sum INT DEFAULT 0;
	     DECLARE num INT DEFAULT 1;
	     while_lbl:WHILE(num<=max_num) DO
	     SET sum=sum+num;
	     SET num=num+1;
	     END WHILE while_lbl;
	     RETURN sum;

--    【LOOP...LEAVE】
-- 	   DECLARE sum INT DEFAULT 0;
-- 	   DECLARE num INT DEFAULT 1;
-- 	   loop_lbl: LOOP
-- 	  	  if(num<=max_num) THEN
-- 	  		SET sum=sum+num;
-- 	  		SET num=num+1;
-- 	  	  ITERATE loop_lbl; -- 【ITERATE:结束当前循环，继续下一次循环】
-- 	  		else 
-- 	  		LEAVE loop_lbl;   -- 【LEAVE:退出循环】 注意在循环中使用Leave时，必须声明循环标签，不然会报错
-- 	  		END IF;
-- 	   END LOOP loop_lbl;
-- 	   RETURN sum;
-- 	 
-- 	  【REPEAT...UNTIL】 类似do.. while 
--     DECLARE sum INT DEFAULT 0;
-- 		 DECLARE num INT DEFAULT 1;
--     repeat_lbl:REPEAT
-- 		 SET sum=sum+num;  -- 进来就执行一次
-- 	   SET num=num+1;
-- 		 UNTIL num=max_num+1;  -- 【num】为101时跳出循环
-- 		 END  REPEAT repeat_lbl ;
-- 		 RETURN sum;
END;
SELECT fun_get_sum(50);

/**********************************************九：存储过程**********************************************/
-- 【IN/OUT/INOUT】存储过程
delimiter $$  -- 自定义分隔符
CREATE PROCEDURE proc_validate_score(in xid int,in xscore decimal(10,2),out result int,out msg VARCHAR(2000)) -- 注意参数类型和里面查询的类型一样
BEGIN
	  if((SELECT count(1) FROM t_user WHERE t_id=xid AND t_score >xscore)>0) THEN
		SET result=1;
		SET msg='存在';
		ELSE 
	  SET result=0;
		SET msg='不存在';
		END IF;
END;
$$

-- 【call】执行存储过程
SET @inout_xscore=1;
CALL proc_get_score(2,@out_xname,@inout_xscore);
-- 查询输出参数
SELECT @out_xname,@inout_xscore;

-- 【drop】删除存储过程
DROP PROCEDURE proc_get_score;


/**********************************************十：事务**********************************************/
-- 事务之间的隔离性
-- 1.【READ UNCOMMITTED】(脏读)
-- 例子：事务A:张三给李四转账了1000元，此时事务A还没提交，此时事务B李四去查询自己的余额，发现多了1000元，
-- 当事务A回滚后，李四又发现自己余额少了1000元

-- 2.【READ COMMITTED】(读可以提交, ORACLE默认)
-- 例子：事务A:张三拿着工资卡（余额10000元）去购物,系统检测到卡里有足够的钱，此时事务A还没提交，测试事务B
-- 张三的妻子通过卡号和密码来购买了10000元化妆品并购买成功提交事务B，张三结账时事务A发现卡里余额不足

-- 3.【REPEATABLE COMMITTED】(可重复读取, MySql默认)
-- 例子：事务A:张三拿着工资卡（余额10000元）去购物,系统检测到卡里有足够的钱，此时事务A还没提交，测试事务B
-- 张三的妻子通过卡号和密码来购买了10000元化妆品时，系统提示无法扣卡余额，因为事务A还没有提交或者回滚

-- 4.【SERIALIZABLE】
-- 例子：事务A事务B必须排队来执行，效率低，一般不用

SHOW VARIABLES LIKE '%ISOLATION%';

/*************************************十一：事务+存储过程+输入参数+输出参数*************************************/
-- 创建
delimiter $$
CREATE PROCEDURE proc_update_score
(IN xid int,out result int,out msg VARCHAR(4000))
BEGIN 
	   
	  -- 注意MySql中所有声明的参数必须放在第一行，不然会报错
		-- 声明分数 
	  DECLARE d_score DECIMAL(10,2) DEFAULT 0; 
		-- 声明监视捕捉异常
		DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
		BEGIN
		GET DIAGNOSTICS CONDITION 1 msg= MESSAGE_TEXT;-- 异常消息
		SET result=0;																  -- 异常状态
		END;
	 
	  -- 开始事务
    START TRANSACTION;
		SET result=1;-- 默认成功
 		SET msg='操作成功！';
		
	  SELECT t_score INTO d_score FROM t_user WHERE t_id=xid;
		-- 更新表t_user
		UPDATE t_user SET t_score=t_score+d_score,modify_user='proc_update_score',modify_time=NOW();
		
		-- 测试异常，正常执行时注释
		-- SELECT * FROM t;
		
		-- 更新表t_student
		UPDATE t_student set modify_user='proc_update_score',modify_time=NOW();
		
		-- 提交/回滚
		IF(result=0) THEN
		ROLLBACK;
		ELSE
		COMMIT;
		END IF;
END
$$
-- 调用存储过程
-- CALL proc_update_score(1,@out_result,@out_msg);
SELECT @out_result,@out_msg;
-- DROP PROCEDURE proc_update_score;

/******************************十二：事务+存储过程+输入参数+输出参数+嵌套存储过程+多行数据/多个结果集************************/
delimiter $$
CREATE PROCEDURE proc_get_score(in xid int,in xscore DECIMAL(10,2),out result int,out msg VARCHAR(2000))
pro_label:BEGIN -- 添加开始标签，实现mssql的reurn的效果
	 -- 定义异常
	 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	 BEGIN
	    get diagnostics CONDITION 1 msg=message_text;
			SET result=0;
	 END;
	 
	 START TRANSACTION; 
	 -- 初始化返回参数
	 SET result=1;
	 SET msg='操作成功！';
	 
	 -- 调用子查询过程
   CALL proc_validate_score(xid,xscore,@son_out_result,@son_out_msg);
	 if(@son_out_result=0) THEN
	 SET result=@son_out_result;
	 SET msg=@son_out_msg;
	 ROLLBACK;
	 LEAVE pro_label;
	 END IF;
	 
	 SELECT '通过验证';
	 -- 查询结果集
	 UPDATE t_user SET t_score=t_score+xscore where t_id=xid;
	 SELECT * FROM t_user where t_id=xid;
	 SELECT * FROM t_student WHERE t_id=xid;
	 
	 -- 提交/回滚
	 if(result=0) THEN
	 ROLLBACK;
	 ELSE 
	 COMMIT;
	 END IF;
END $$

-- 调用存储过程
CALL proc_get_score (1,36,@result,@msg);
SELECT @result,@msg;
-- 删除存储过程
-- DROP PROCEDURE proc_get_score;

/***************************************************十三：触发器**********************************************/
-- CREATE TRIGGER 【trigger_name】 【trigger_time】 【trigger_event 】
-- ON 【tb_name】 FOR EACH ROW 【trigger_stmt】
-- 【trigger_name】：触发器名称
-- 【tirgger_time】：触发执行事件
--     BEFORE：事件之前触发
--     AFTER：事件之后触发
-- 【trigger_event】：触发事件
--     INSERT：插入某一行时激活触发器，INSERT,LOAD DATA,REPLACE语句可以触发
--     UPDATE：更改某一行时激活触发器，UPDATE语句可以触发
--     DELETE：删除某一行时激活触发器，DELETE,REPLACE语句可以触发
-- 【tb_name】：触发器要执行的哪张表
-- 【FOR EACH ROW】：触发频率为每一行触发一次
-- 【trigger_stmt】：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

-- 【创建触发器】
CREATE TRIGGER tri_update_user AFTER UPDATE on t_user FOR EACH ROW 
BEGIN
   -- 查询【t_user】更新的信息
   DECLARE old_t_id INT;
	 DECLARE new_update_user VARCHAR(50);
	 SELECT new.t_id,new.create_user INTO old_t_id, new_update_user;
	 
	 -- 更新【t_student】
	 UPDATE t_student SET create_user=new_update_user where t_id=old_t_id;
	 
END;
-- 【触发触发器】
UPDATE t_user set create_user='tri_update' where t_id=1;

-- 【删除触发器】
DROP TRIGGER tri_update_user;

/***************************************************十五：游标**********************************************/
-- 游标只能在存储过程/函数中使用
delimiter $$
CREATE PROCEDURE proc_cursor(out result int,out msg VARCHAR(4000))
BEGIN 
   -- 声明参数
	 DECLARE cursor_result int DEFAULT 1; -- 游标状态
	 DECLARE name_item VARCHAR(50);
	 DECLARE score_item DECIMAL(10,2);
	 DECLARE name_str VARCHAR(1000);
	 -- 声明游标
	 DECLARE my_cursor CURSOR FOR (SELECT t_name,t_score FROM t_user);
   -- 声明全部异常	 
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	 BEGIN 
	    get diagnostics condition 1  msg=MESSAGE_TEXT;
			SET result=0; -- 标记事务失败
	 END;
	 -- 声明游标找不到数据异常
	 DECLARE CONTINUE HANDLER FOR NOT FOUND
	 BEGIN
			SET cursor_result=0; -- 标记游标最后一行
	 END;
	 
	 START TRANSACTION;
	 SET result=1;
	 SET msg='操作成功';
	 -- 打开游标
	 OPEN my_cursor;
	 -- 循环游标
	my_loop_label:LOOP
	-- 取每一条对象
	FETCH my_cursor into name_item,score_item; -- 找不到下一行数据时会报错，触发SQLEXCEPTION 标记result=0
	
	if(cursor_result=0)	THEN  -- 条件判断放在FETCH下面，触发了异常后，标记就发生了改变
	LEAVE my_loop_label;      -- 如果到了最后一行，就跳出循环
	END IF;
	
	if(score_item>65) THEN
	SET name_str=CONCAT_WS(',',name_str,name_item);
-- 	SELECT name_str;
	END IF;
	
	END LOOP my_loop_label;
	-- 关闭游标
  CLOSE my_cursor;
	
	
	if(result=0) THEN
	ROLLBACK;
	ELSE 
	SET msg=name_str;
	COMMIT;
	END IF;
END $$
-- 调用存储过程
CALL proc_cursor(@result,@msg);
SELECT @result,@msg;


/********************************************十六：临时表+循环实现游标效果*******************************************/
-- 前言：游标循环的时候会锁表，所以不建议用，下面是不锁表的处理方式
delimiter $$
CREATE PROCEDURE proc_temp_table(out result int,out msg VARCHAR(4000))
BEGIN
    -- 临时表标记
    DECLARE temp_index int DEFAULT 1; 
		DECLARE name_item VARCHAR(50);
	  DECLARE score_item DECIMAL(10,2);
	  DECLARE name_str VARCHAR(1000);
		
	  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
		BEGIN
		   get diagnostics CONDITION 1 msg=message_text;
			 SET result=0;
		END;
		 
	  START TRANSACTION;
	  SET result=1;
	  SET msg='操作成功';
		 
		-- 如果临时表存在就删除临时表
		DROP TEMPORARY TABLE IF EXISTS temp_user;
		-- 创建临时表
		CREATE TEMPORARY TABLE temp_user
		(  
			id INT  PRIMARY KEY Auto_Increment UNIQUE KEY NOT NULL COMMENT '自增id',
			tu_name VARCHAR(50) NULL COMMENT '姓名',
			tu_score DECIMAL(10,2) NULL COMMENT '分数'
		) ENGINE=INNODB Auto_Increment=1 DEFAULT CHARSET =utf8;
		
		 INSERT temp_user(tu_name,tu_score) SELECT t_name,t_score FROM t_user;
		 
	   while_label:WHILE(TRUE) DO
	   SELECT MIN(id) INTO temp_index FROM temp_user WHERE id=temp_index;
		 if(temp_index is NULL) THEN
		 LEAVE while_label;
		 END IF;
		 SELECT tu_name,tu_score INTO name_item,score_item FROM temp_user WHERE id=temp_index;
		 
		 if(score_item>65) THEN
	   SET name_str=CONCAT_WS(',',name_str,name_item);
	   END IF;
		 
		SET temp_index=temp_index+1;
    END WHILE while_label;
	 
	  if(result=0) THEN
   	ROLLBACK;
	  ELSE 
	  SET msg=name_str;
	  COMMIT;
	  END IF;
	
END $$
-- 调用存储过程
CALL proc_temp_table(@result,@msg);
SELECT @result,@msg;
-- DROP PROCEDURE proc_temp_table

/***************************************************十七：数据库设计**********************************************/
-- 数据库关系
-- 1对1：  1张表
-- 1：1001 
-- 2：1002

-- 1对多：2张表
-- 1    1001:1      
-- 2    1002:1 
-- 3    1003:2
-- 		  1004:2
-- 		  1005:2

-- 多对多：3张表
-- 1    1001    1:1001      
-- 2    1002    1:1002
-- 3    1003		2:1001
-- 							2:1002


/*********************************十八：数据库权限**********************************/
use mysql;

-- 新建用户
CREATE USER test@127.0.0.1 IDENTIFIED by 'admin123';

-- 修改用户名和连接地址
RENAME USER test@127.0.0.1 to n_test@127.0.0.1;

-- 修改用户密码
SET PASSWORD FOR test@127.0.0.1 ='admin';

-- 删除用户
DROP USER n_test@127.0.0.1;

-- 刷新服务，马上生效
FLUSH PRIVILEGES;

-- 展示目前数据库所有的权限种类
SHOW PRIVILEGES;

-- 展示当前用户拥有的权限
-- 方法一
SHOW GRANTS FOR test@127.0.0.1;
-- 方法二
SELECT * FROM USER where USER='test';

-- 赋予权限
GRANT SELECT,INSERT,UPDATE on testdb.t_user to n_test@127.0.0.1;

-- 收回权限
REVOKE INSERT,UPDATE on testdb.t_user FROM n_test@127.0.0.1;


/****************************十九：数据库导入导出*******************************/
-- 导出数据库到本地
-- 1.指定数据库的所有表结构+表数据(不包含存储过程等)
-- mysqldump -u root -p testdb>D:\testdb.sql;
-- mysqldump -u n_test -p -h 127.0.0.1 -P 3306 testdb>d:\testdb.sql

-- 2.指定数据库的指定表结构+表数据(不包含存储过程等)
-- mysqldump -u root -p testdb t_user>D:\testdb.sql;

-- 3.指定数据库的指定表结构【-d】(不包含存储过程等)
-- mysqldump -u root -p -d testdb t_user>D:\testdb.sql;

-- 4.指定数据库的指定表数据【-t】(不包含存储过程等) 
-- mysqldump -u root -p -t testdb t_user>D:\testdb.sql
	 
-- 5.指定存储过程和函数
-- mysqldump -u n_test -p -h 127.0.0.1 -P3306 -ntd -R testdb>D:\t_proc.sql

--     【导出总结】
--     -d 结构(--no-data:不导出任何数据，只导出数据库表结构)
--     -t 数据(--no-create-info:只导出数据，而不添加CREATE TABLE 语句)
--     -n (--no-create-db:只导出数据，而不添加CREATE DATABASE 语句）
--     -R (--routines:导出存储过程以及自定义函数)
--     -E (--events:导出事件)
--     --triggers (默认导出触发器，使用--skip-triggers屏蔽导出)
--     -B (--databases:导出数据库列表，单个库时可省略）
--     --tables 表列表（单个表时可省略）
--     ①同时导出结构以及数据时可同时省略-d和-t
--     ②同时 不 导出结构和数据可使用-ntd
--     ③只导出存储过程和函数可使用-R -ntd
--     ④导出所有(结构&数据&存储过程&函数&事件&触发器)使用-R -E(相当于①，省略了-d -t;触发器默认导出)
--     ⑤只导出结构&函数&事件&触发器使用 -R -E -d


-- 导入本地数据到数据库
-- souce D:\testdb.sql;

/****************************二十：事件（执行计划）*******************************/
-- 创建事件执行日志表
delimiter $$
CREATE TABLE t_event_log
(  
  tel_id int PRIMARY KEY UNIQUE KEY Auto_Increment NOT NULL COMMENT '主键id',
	tel_title varchar(200) NOT NULL COMMENT '主题',
	tel_content VARCHAR(4000) NOT NULL COMMENT '内容',
	tel_begin_time datetime NOT NULL COMMENT '开始时间',
	tel_end_time datetime NOT NULL COMMENT '结束时间'
) $$ delimiter

-- 事件需求:每个月1日的中午12点执行
-- 查询当前事件状态
SHOW variables like '%sche%';

-- 设置数据库启用
SET GLOBAL event_scheduler=1;

-- 创建事件
-- 分隔符
delimiter $$
-- 创建
CREATE EVENT even_send_log
-- 每一个月
-- ON SCHEDULE EVERY 1 MONTH STARTS
ON SCHEDULE EVERY 1 MINUTE STARTS
-- 开始时间
--  DATE_ADD(
--  DATE_ADD(CURRENT_DATE(),INTERVAL -(DAY(CURRENT_DATE())-1) DAY)
-- ,INTERVAL 12 HOUR)
CURRENT_DATE()
-- 事件执行完成后不删除
ON COMPLETION PRESERVE
-- 启用该事件
ENABLE
DO
BEGIN  
  -- 执行内容
	INSERT INTO t_event_log(tel_title,tel_content,tel_begin_time,tel_end_time)
	VALUES('记录日志','记录日志内容',NOW(),NOW());
END
$$ delimiter



SELECT * FROM t_user;
SELECT * FROM t_student;