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

╰+攻爆jí腚メ 2024-04-18 18:07 39阅读 0赞

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;