赞
踩
- 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;
-
-
-
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。