use testdb;
-- /**********************************************一：数据库操作语言**********************************************/
-- DDL: 数据库定义语言：create drop alter
-- DML: 数据库管理语言：insert update delete
-- DQL: 数据库查询语言：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;
