-- 整型类型tinyint
#有符号
create table demo1 (
c1 tinyint comment '整型有符号'
);
#tinyint范围-128~127
insert into demo1 values(-pow(2,7)), (pow(2,7)-1);
#无符号
create table demo2 (
c1 tinyint unsigned
);
insert into demo2 values (128), (255);
insert into demo2 value (256);
-- 关于int(n)
-- 无论n等于多少int总是占4个字节
-- n表示显示宽度，不足的用0补足，超长的部分无视长度而直接显示整个数字，但需要首先设置unsigned zerofill才有效
create table demo3 (
a int,
b int(5),
c int(5) unsigned,
d int(5) zerofill,
e int(5) unsigned zerofill,
f int zerofill,
g int unsigned zerofill
);
insert into demo3 values (1,1,1,1,1,1,1), (11,11,11,11,11,11,11), (12345,12345,12345,12345,12345,12345,12345);
select * from demo3; #DataGrip对数据做了处理导致1、2条记录d~f字段zerofill未显示0
show create table demo3; #zerofill会将有符号整型转换为无符号整型
-- int(n)中的n省略时，宽度为对应类型无符号最大值的十进制的长度
create table demo4 (
a bigint zerofill
);
insert into demo4 value(1);
select * from demo4;
-- 浮点类型
create table demo5 (
a float(5,2), #type(M,D) M表示总长度D表示小数点后的长度
b double(5,2), #float和double在不指定精度的情况下会按照实际精度显示
c decimal(5,2) #decimal在不指定精度时默认整数为10，小数为0
);
insert into demo5 values (1,1,1), (2.1,2.1,2.1), (3.123,3.123,3.123),
(4.125,4.125,4.125), (5.115,5.115,5.115), (6.126,6.126,6.126),
(7.116,7.116,7.116), (8.1151,8.1151,8.1151), (9.1251,9.1251,9.1251),
(10.11501,10.11501,10.11501), (11.12501,11.12501,11.12501);
select * from demo5; #decimal四舍五入，float、double四舍六入五成双
create table demo6 (
a float,
b double,
c decimal
);
insert into demo6 values (1,1,1),(1.234,1.234,1.4),(1.234,0.01,1.5);
select * from demo6;
#float、double存在精度问题：
select sum(a), sum(b), sum(c) from demo5;
select sum(a), sum(b), sum(c) from demo6;
-- 管理员命令及权限控制
use mysql;
select user, host from user;
-- 创建用户
# create user user_name[@host_name] [identified by 'password'];
create user test1 identified by 'test1'; #主机名默认为%表示可以从任何主机连接MySQL数据库
create user test2@'%' identified by 'test2';
select user, host from user;
-- 修改密码
# 通过管理员修改密码
set password for 'test1'@'%' = 'test1';
# 使用对应的用户登录mysql
set password = '123456';
# 通过修改mysql.user表修改密码 8.0以上版本或已不适用
use mysql;
update user set authentication_string = password('123456') where user = 'test1' and host = '%';
flush privileges;
-- 用户授权
# grant privileges on database.table to 'username'[@'host'] [with grant option]
create user administrator@localhost identified by '123456';
grant all on learnmysql.* to administrator@localhost;#给administrator授予learnmysql表所有权限
grant select, update on learnmysql.* to test2@'%';
#授予account_view@localhost用户查看mysql.user表的user、host字段
create user account_view@localhost identified by '123456';
grant select(user, host) on mysql.user to account_view@localhost;
#查看用户有哪些权限
show grants for test2@'%';
#查看当前用户权限
show grants;
#撤销用户的权限
#revoke privileges on database.tables from 'username'[@'host']
revoke alter on *.* from administrator@localhost;
show grants for administrator@localhost;
-- 删除用户
#drop user username@host;
#delete from user where user = 'username' and host = 'host'; flush privileges;
-- DDL Data Definition Language
#create database [if not exists] database_name;
#drop database [if exists] database_name;
show databases like '%sql%';
drop database if exists testsql;
show databases like '%sql%';
create database if not exists testsql;
-- 表管理
#创建表
/**
create table table_name(
column_name type(length) [restraints] [comment 'remarks'],
column_name type(length)...
)[table options];
*/
#删除表
#drop table [if exists] table_name;
#修改表名
#alter table table_name rename [to] new_table_name;
#表设置备注
#alter table table_name comment 'comment for the table';
#复制表-结构
#create table table_name like source_table;
#复制表-结构+数据
#create table table_name [as] select columns... from source_table_name [where ...];
-- 表中列的管理
#添加列
#alter table table_name add column column_name type [constraints];
#修改列 modify不能修改列明，change可以修改列名
#alter table table_name modify column column_name type [constraints];
#alter table table_name change column column_name new_column_name type [constraints];
#删除列
#alter table table_name drop column column_name;
-- DML Data Manipulation Language
#插入
#insert into table[(column1, column2...)] values (value1, value2...);
#insert into table set column_name = value, column_name = value;
#批量插入
#insert into table_name [(column1, column2)] values (value1, value2), (value3, value4);
#insert into table_name [(column1, column2)] select ...
-- 数据更新
#单表更新
#update table_name [[as] table_alias] set [table_alias.]column_name = value, [table_alias.]column_name = value [where...];
#多表更新
#update table_name [[as] alias1], table_name [[as] alias2] set [alias1.]column1 = value, [alias2.]column2 = value where ...;
-- 删除数据操作
#delete单表删除
#delete [alias] from table_name [[as] alias] [where condition];
#多表删除
#delete [alias1, alias2] from table1 [[as] alias1], table2 [[as] alias2] [where...];
#使用truncate删除
#truncate table_name;
-- DQL Data Query Language
#分组查询
drop table if exists t_order;
create table t_order (
id int not null auto_increment comment '订单编号',
user_id bigint not null comment '下单用户编号',
user_name varchar(16) not null default '' comment '用户名',
price decimal(8,2) not null default 0 comment '订单金额',
order_year smallint not null comment '订单创建年份',
primary key (id)
) comment '订单表';
insert into t_order(user_id, user_name, price, order_year)
values (1001, '路人甲Java', 11.11, '2017'),
(1001, '路人甲Java', 22.22, '2018'),
(1001, '路人甲Java', 88.88, '2018'),
(1002, '刘德华', 33.33, '2018'),
(1002, '刘德华', 12.22, '2018'),
(1002, '刘德华', 16.66, '2018'),
(1002, '刘德华', 44.44, '2019'),
(1003, '张学友', 55.55, '2018'),
(1003, '张学友', 66.66, '2019');
select user_id, count(user_id) from t_order group by user_id, order_year;
select user_id, count(user_id) order_num from t_order where order_year = '2018' group by user_id having order_num > 1;
select user_id, count(user_id) order_num from t_order where order_year = '2018' group by user_id having order_num >= 2 order by order_num desc limit 1;
#group by分组查询中select后只能跟group by分组的列或者聚合函数，可以通过配置取消这一限制
select @@sql_mode;
-- 常用函数
#abs()求绝对值
select abs(5), abs(-2.4), abs(-24), abs(0);
#sqrt()求二次方根 - 负数没有二次方根，返回null
select sqrt(25), sqrt(120), sqrt(-4);
#mode()求余函数 - 对于带有小数部分的数值也起作用，返回除法运算后的余数
select mod(63, 8), mod(120, 10), mod(15.5, 3);
#ceil()、ceiling()向上取整，返回值转化为BIGINT
select ceil(-2.5), ceiling(2.5);
#floor()向下取整
select floor(5), floor(5.66), floor(-4), floor(-4.66);
#rand()生成随机数，传入整数用于产生重复序列
select rand(), rand(), rand();
select rand(2), rand(2), rand(1), rand(-1);
#round()四舍五入 - 返回最接近于参数的整数；round(x, y)对参数x进行四舍五入，返回值保留小数点后y位
#sign返回参数的符号
select sign(-5), sign(0), sign(6);
#pow()、power()次方函数
select pow(5, -2), power(10, 3), pow(100, 0), pow(4, 3), power(6, -3);
#sin()正弦函数
select sin(1), sin(0.5*pi()), pi();
-- 字符串函数
#length()返回字符串的字节长度，utf8中汉字占3字节
select length('你好,bitch'), length('你好'), length('bitch');
#concat() - 若任何一个参数为NULL则返回值为NULL；若含有任意二进制字符串
select concat('hello'), concat('hello', ' world'), concat('hello', null);
#insert(s1, i, len, s2)返回字符串s1，从i到len个字符使用s2替换，若任何一个参数为NULL则返回NULL
select insert('hello sql', 2, 4, '*') as col1, insert('hello world', -1, 4, '**') as col2, insert('hello java', 3, 20, '**') as col3;
select insert('hello python', 4, -2, '*');
#lower()将字母转换为小写
select lower('Hello, World');
#upper()将字母转换为大写
select upper('Hello, 你好');
#left(s, n)返回字符串s左边的n个字符
select left('你好, hello', 2), left('hello', 10), left('你好，MySQL', -2);
#right(s, n)返回字符串s右边n个字符
select right('hello world', 5), right('你好', -1);
#trim()删除字符串左右两侧空格
select trim('【 MySQL 】'), concat('【', trim(' MySQL '), '】');
#replace(s, s1, s2)字符串替换
select replace('hello fucking world', 'fucking ', '');
#substr()和substring()
select substring('hello world', 7);
select substring('chinese', -3);#倒数n个字符
select substring('hello world', 6, 6);
select substring('hello world', -3, 2);#从倒数m个字符截取正数n个字符
select substring('chinese' from 3);#从n截取之后所有字符
select substring('hello world' from -5);
select substring('hello world' from 7 for 5);
select substring('hello world' from -5 for 5);
#reverse()反转字符串
select reverse('爱泡jio的桶') reverse_str;
-- 日期和时间函数
select curdate(), current_date(), curdate()+1;
select curtime(), current_time(), current_time()+1;
select now(), sysdate();
select unix_timestamp(), unix_timestamp(now()), now(), unix_timestamp('2020-10-02 16:22:00');
select from_unixtime(1601630229), from_unixtime(1601630229, '%Y-%m-%d %H:%i:s');
select month('2020-09-30');#month()获取日期的月份
select monthname(now());#monthname()获取指定日期的月份英文名
select dayname(now());#dayname()获取指定日期的星期名称
select dayofweek(now());#dayofweek()获取指定日期对应的周索引，以周日作为每周的第一天
select week('2020-10-02', 4);#week()获取指定日期是一年中的第几周
show variables like 'default_week_format';#week()函数第二个参数mode
select dayofyear(now()), now(), dayofyear('20200-10-02');#dayofyear()获取指定日期在一年中的位置
select now(), dayofmonth(now()), dayofmonth('2020-10-02');#dayofmonth()获取指定日期在一个月中的位置
select year(now()), year('2020-10-02');#year()获取指定日期的年份
select time_to_sec(curtime()), time_to_sec(now()), time_to_sec('12:00:00');#获取当前时间的秒数
select sec_to_time(250);#通过秒数获取时间
select date_add(now(), interval 1 day), adddate(now(), interval 1 week);#向指定日期加上指定的时间间隔
select date_sub(now(), interval 1 day), subdate(now(), interval -1 day);#日期减法运算
select addtime('2020-10-03 12:00:00', '00:00:01'), addtime('12:5:5', '1:1:1');#addtime()时间加法运算
select subtime('2020-10-02 00:00:00', '0:0:1');#subtime()时间减法运算
#获取两个日期的时间间隔，计算只用到日期时间中的日期部分
select datediff('2020-10-01', '2020-10-08'), datediff('2020-10-08', '2020-10-01'), datediff('2020-10-01 12:00:00', '2020-10-02 00:00:00');
select date_format(now(), '%Y/%m/%d %H:%i:%s'), now(), date_format('2020-10-03', '%Y%m%d');#date_format(date, format)格式化指定日期
select weekday(curdate()), dayofweek(now()), weekday('2020-10-03');#获取指定日期在一周的索引位置
-- MySQL聚合函数
#max()、min()、count()、sum()、avg()
-- MySQL流程控制函数
#if(expression, v1, v2)
select if(1<2, '1<2', '1>2'), if(1>5, '√', '×'), if(strcmp('abc', 'ab'), 'yes', 'no');
#ifnull(value, default)
select ifnull(null, 'this is the default value'), ifnull('this is not null', null), ifnull(null, null);
#case
/**
case <expression>
when <value1> then <option1>
when <value2> then <option2>
else <operation>
end case;
*/
/**
case
when <condition1> then <order>
when <condition2> then <order>
else commands
end case;
*/
create table t_stu (
id int auto_increment comment '编号',
name varchar(10) comment '姓名',
sex tinyint comment '性别 0：未知 1：男 2：女',
primary key(id)
) comment '学生表';
insert into t_stu (name, sex) values ('张学友', 1), ('刘德华',1), ('郭富城',1), ('蔡依林',2), ('xxx',0);
#查询所有学生信息，输出：姓名，性别（男、女、未知）
select t.name 姓名, (case t.sex when 1 then '男' when 2 then '女' else '未知' end) 性别 from t_stu t;
select t.name 姓名, (case when t.sex=1 then '男' when t.sex=2 then '女' else '未知' end) 性别 from t_stu t;
-- 其他函数
#version()查询数据库版本号
select version();
#database()查询当前数据库名称
select database();
#user()查询当前登录的用户
select user();
#password()查询字符串密码形式的编码，MySQL8.0已经废弃此函数
select password('123456');
#md5()加密算法
select md5('123456'), sha('123456');
-- 连接查询
drop table if exists t_team;
create table t_team (
id int not null auto_increment primary key comment '组id',
team_name varchar(32) not null default '' comment '组名'
) comment '组表';
insert into t_team values (1,'架构组'),(2,'测试组'),(3,'java组'),(4,'前端组');
drop table if exists t_employee;
create table t_employee (
id int not null auto_increment primary key comment '部门id',
employee_name varchar(32) not null default '' comment '员工名称',
team_id int not null default 0 comment '员工所在组id'
#foreign key(team_id) references t_team(id)
) comment '员工表';
insert into t_employee values (1,'路人甲Java',1),(2,'张三',2),(3,'李四',3),(4,'王 五',0),(5,'赵六',0);
#笛卡尔积
select * from t_team join t_employee;
#内连接 - 内连接相当于在笛卡尔积的基础上加上连接条件
select * from t_team join t_employee te on t_team.id = te.team_id;
select * from t_team, t_employee where t_team.id = t_employee.team_id;
#查询员工及所属部门
select emp.employee_name, team.team_name from t_employee emp join t_team team on emp.team_id = team.id;
select emp.employee_name, team.team_name from t_employee emp, t_team team where emp.team_id = team.id;
#组合条件进行查询
select emp.employee_name, team.team_name from t_employee emp join t_team team on emp.team_id = team.id and team.team_name = '架构组';
select emp.employee_name, team.team_name from t_employee emp join t_team team on emp.team_id = team.id where team.team_name = '测试组';
select emp.employee_name, team.team_name from t_employee emp, t_team team where emp.team_id = team.id and team.team_name = 'java组';
-- 外连接
#左外连接
select emp.employee_name, t.team_name from t_employee emp left join t_team t on emp.team_id = t.id;
select emp.employee_name, t.team_name from t_employee emp left join t_team t on emp.team_id = t.id where t.team_name is not null;
#右外连接
select emp.employee_name, t.team_name from t_team t right join t_employee emp on emp.team_id = t.id;
select emp.employee_name, t.team_name from t_team t right join t_employee emp on emp.team_id = t.id where t.team_name is not null;
-- 表连接原理
drop table if exists test_1;
create table test_1 (
a int
);
drop table if exists test_2;
create table test_2 (
b int
);
insert into test_1 values (1), (2), (3);
insert into test_2 values (3), (4), (5);
#内连接
select * from test_1, test_2;
select * from test_1 t1, test_2 t2 where t1.a = t2.b;
#左连接
select * from test_1 t1 left join test_2 t2 on t1.a = t2.b;
select * from test_1 t1 left join test_2 t2 on t1.a > 10; #*************************************#
select * from test_1 t1 left join test_2 t2 on 1 = 1;
-- 子查询
/**
按结果集的行列数不同分为：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有一行多列）
表子查询（结果集一般为多行多列）
按子查询出现在主查询中的不同位置分：
select后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面：支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行）
exists后面（相关子查询）：表子查询（多行、多列）
*/
drop database if exists `employees`;
create database `employees`;
USE `employees`;
/*部门表*/
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`department_id` int(4) NOT NULL AUTO_INCREMENT comment '部门id',
`department_name` varchar(3) DEFAULT NULL comment '部门名称',
`manager_id` int(6) DEFAULT NULL comment '管理者id',
`location_id` int(4) DEFAULT NULL comment '部门位置id，来源于表locations中的location_id',
PRIMARY KEY (`department_id`),
KEY `loc_id_fk` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 comment '部门表';
insert into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);
/*员工表*/
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`employee_id` int(6) NOT NULL AUTO_INCREMENT comment '员工id',
`first_name` varchar(20) DEFAULT NULL comment '名',
`last_name` varchar(25) DEFAULT NULL comment '姓',
`email` varchar(25) DEFAULT NULL comment '电子邮箱',
`phone_number` varchar(20) DEFAULT NULL comment '手机',
`job_id` varchar(10) DEFAULT NULL comment '职位id，来源于jobs表中的job_id',
`salary` double(10,2) DEFAULT NULL comment '薪水',
`commission_pct` double(4,2) DEFAULT NULL comment '佣金百分比',
`manager_id` int(6) DEFAULT NULL comment '上级id',
`department_id` int(4) DEFAULT NULL comment '所属部门id，来源于departments中的department_id',
`hiredate` datetime DEFAULT NULL comment '入职日期',
PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 comment '员工表';
insert into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');
/*职位信息表*/
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
`job_id` varchar(10) NOT NULL comment '职位id',
`job_title` varchar(35) DEFAULT NULL comment '职位名称',
`min_salary` int(6) DEFAULT NULL comment '薪资范围最小值',
`max_salary` int(6) DEFAULT NULL comment '薪资范围最大值',
PRIMARY KEY (`job_id`)
) ENGINE=InnoDB comment '职位id';
insert into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);
/*位置表*/
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`location_id` int(11) NOT NULL AUTO_INCREMENT comment '位置id',
`street_address` varchar(40) DEFAULT NULL comment '街道地址',
`postal_code` varchar(12) DEFAULT NULL comment '邮编',
`city` varchar(30) DEFAULT NULL comment '城市名称',
`state_province` varchar(25) DEFAULT NULL comment '省',
`country_id` varchar(2) DEFAULT NULL comment '国家编号',
PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 comment '位置表';
insert into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');
/*薪资等级表*/
DROP TABLE IF EXISTS `job_grades`;
CREATE TABLE `job_grades`(
`grade_level` varchar(3) comment '等级',
`lowest_sal` int comment '薪资最低值',
`highest_sal` int comment '薪资最高值',
PRIMARY KEY (`grade_level`)
) comment '薪资等级表';
INSERT INTO job_grades VALUES ('A', 1000, 2999),('B', 3000, 5999),('C', 6000, 9999),('D', 10000, 14999),('E', 15000, 24999),('F', 25000, 40000);
-- select后面的子查询（仅支持标量子查询）
#查询每个部门员工个数
select dep.*, (select count(*) from employees e where e.department_id = dep.department_id) 员工个数 from departments dep;
select dep.department_name from departments dep join employees e on dep.department_id = e.department_id and e.employee_id = 102;
#from后面的子查询
-- 查询每个部门平均工资的工资等级
#查询每个部门平均工资
select e.department_id, avg(e.salary) from employees e group by e.department_id;
#薪资等级表
select * from job_grades;
select em.department_id, j.grade_level, em.avgs from (select e.department_id, avg(e.salary) avgs from employees e group by e.department_id) em, job_grades j where em.avgs >= j.lowest_sal and em.avgs <= j.highest_sal;
select em.department_id, j.grade_level, em.avgs from (select e.department_id, avg(e.salary) avgs from employees e group by e.department_id) em, job_grades j where em.avgs between j.lowest_sal and j.highest_sal;
-- ...
-- NULL导致的神坑
#比较运算符中使用NULL
select 1>null;
select 1<null;
select 1<>null;
select 1>=null;
select 1<=null;
select null=null, null!=null;
select 1 in (null), 1 not in (null), null in (null), null not in (null);
select 1 = any(select null), null = any(select null);
select 1 = all(select null), null = all(select null);
#结论：任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all）比较时，返回值都为NULL，NULL作为布尔值的时候，不为1也不为0。
#当NOT IN后面有NULL值时，不论什么情况下，整个SQL语句的查询结果都为空
-- 事务的几个特性（ACID）
#原子性（Atomicity）
#一致性（Consistency）
#隔离性（Isolation）
#持久性（Durability）
show variables like '%autocommit%';
#开启MySQL显式事务
set autocommit = 0; commit; rollback;
start transaction; commit; rollback;
#设置事务中的存档点，可以回滚事务到指定的存档点
savepoint s1;
rollback to s1;
#只读事务：事务在执行过程中只执行一些只读操作，但是不做insert、update、delete操作，数据库内部会对只读事务进行一些性能优化
start transaction read only;
-- 事务隔离级别：解决多个事务之间数据可见性及数据正确性的问题
/*
隔离级别分类：
读未提交 READ-UNCOMMITTED
读已提交 READ-COMMITTED
可重复读 REPEATABLE-READ
串行 SERIALIZABLE
*/
#查看事务隔离级别
show variables like 'transaction_isolation';
#设置隔离级别
set session transaction isolation level read uncommitted;
drop table if exists trans_test;
create table trans_test (
a int default null
);
#READ UNCOMMITTED隔离级别
set transaction isolation level read uncommitted;
set autocommit = 0;
#session 1
start transaction;
select * from trans_test;
#session 2
start transaction;
insert into trans_test values (1);
select * from trans_test;
#session 1
select * from trans_test;
#session 2
commit;
#session 1
commit;
#READ COMMITTED
set session transaction isolation level read committed;
show variables like 'transaction_isolation';
delete from trans_test where 1=1;
#session 1
start transaction;
select * from trans_test;
#session 2
start transaction;
insert into trans_test values (1);
select * from trans_test;
#session 1
select * from trans_test;
#session 2
commit;
#session 1
select * from trans_test;
commit;
#REPEATABLE READ
set session transaction isolation level repeatable read;
show variables like 'transaction_isolation';
delete from trans_test where 1=1;
#session 1
start transaction read only;
select * from trans_test;
#session 2
start transaction;
insert into trans_test values (1);
select * from trans_test;
#session 1
select * from trans_test;
#session 2
commit;
#session 1
select * from trans_test;
commit;
select * from trans_test;
#幻读演示
drop table if exists t_user;
create table t_user(
id int not null auto_increment primary key comment '用户id',
name varchar(20) not null comment '用户名'
);
#session 1
start transaction;
select * from t_user;
#session 2
start transaction;
insert into t_user values (1, 'java');
select * from t_user;
#session 1
select * from t_user where name = 'java';
#session 2
commit;
#session 1
insert into t_user values (1, 'java');
select * from t_user where id = 1;
commit;
#SERIALIZABLE
set session transaction isolation level serializable; show variables like 'transaction_isolation';
delete from trans_test where 1=1;
#session 1
start transaction;
select * from trans_test;
#session 2
start transaction;
insert into trans_test values (1);
#session 1
commit;
#session 2
commit;
-- 视图
use employees;
#create view view_name as (select...);
create view my_view1 as
select t1.last_name, t2.department_name, t3.job_title
from employees t1, departments t2, jobs t3
where t1.department_id = t2.department_id and t1.job_id = t3.job_id;
select * from my_view1 a where a.last_name like 'a%';
#修改视图
#create or replace view view_name as (select ...);
#alter view view_name as (select ...);
#删除视图
#drop view view_name [, view_name, ...];
#查询视图结构
#desc view_name;
#show create view view_name;
desc my_view1; show create view my_view1;
#更新视图--更改视图中的数据，而不是更改视图结构SQL语句
-- 变量：系统变量、自定义变量
#查看系统变量
show global variables; show session variables; show variables;
#查看指定的系统变量 select @@[global. | session.]variable_name;
select @@global.autocommit;
#赋值
# set [global | session] variable_name = value;
# set @@[global. | session.]variable_name = value;
-- 全局变量
#查看所有全局变量
show global variables;
select @@global.autocommit;
-- 会话变量
show session variables;
select @@session.autocommit;
-- 自定义变量 set语句中冒号可选，select语句中必须有冒号
#set @variable_name = value;
#set @variable_name := value;
#select @variable_name := value;
#select value into @variable_name;
set @user_name = 'newbie';
select 'java' into @programming_language;
select count(*) into @empcount from employees;
select @first_name := 'weiming', @email := 'liu';
insert into employees (first_name, email) values (@first_name, @email);
#局部变量
#declare variable_name type;
#declare variable_name type [default default_value];
use testsql;
drop table if exists test1;
create table test1 (
a int primary key,
b int
);
DELIMITER $$
DROP PROCEDURE IF EXISTS proc1;
CREATE PROCEDURE proc1()
BEGIN
/*声明一个局部变量*/
DECLARE v_a int;
select ifnull(max(a), 0)+1 into v_a from test1;
select @v_b:=v_a*2;
insert into test1(a, b) select v_a, @v_b;
end $$
/*声明脚本的结束符为;*/
DELIMITER ;
/*调用存储过程*/
call proc1();
/*查看结果*/
select * from test1;
#delimiter用于声明SQL的结束符
-- 存储过程和自定义函数
drop database if exists procedure_function_test;
create database procedure_function_test;
use procedure_function_test;
drop table if exists t_user;
create table t_user (
id int not null primary key comment '编号',
age smallint unsigned not null comment '年龄',
name varchar(16) not null comment '姓名'
) comment '用户表';
/*创建存储过程：
create procedure procedure_name([参数模式] 参数名 参数类型)
begin
存储过程体
end
*/
#call procedure_name(参数列表);
#drop procedure [if exists ]存储过程名称;
#存储过程无法进行修改，若要修改可先删除后重建
#show create procedure 存储过程名称;
DELIMITER $
DROP PROCEDURE IF EXISTS proc1;
create procedure proc1()
begin
insert into t_user values (1, 30, '张三');
insert into t_user values (2, 50, '马云');
end $
DELIMITER ;
#调用存储过程
call proc1();
#带in参数的存储过程
DELIMITER $
DROP PROCEDURE IF EXISTS proc2;
CREATE PROCEDURE proc2(id int, age int, in name varchar(16))
begin
insert into t_user values (id, age ,name);
end $
DELIMITER ;
#调用存储过程
select @id:=3, @age:=56, @name='李四';
call proc2(@id, @age, @name);
#带out参数的存储过程
delete a from t_user a where a.id = 4;
DROP PROCEDURE IF EXISTS proc3;
DELIMITER $
CREATE PROCEDURE proc3(id int, age int, in name varchar(16), out user_count int, out max_id int)
begin
insert into t_user values (id, age, name);
/*查询出t_user表的记录，放入user_count中，max_id用来存储t_user中最小的id*/
select count(*), max(id) into user_count, max_id from t_user;
end $
/*将结束符还原为;*/
DELIMITER ;
#调用存储过程
select @id:=4, @age:=40, @name:='王五';
call proc3(@id, @age, @name, @user_count, @max_id);
select @user_count, @max_id;
#带inout参数的存储过程
drop procedure if exists proc4;
DELIMITER $
CREATE PROCEDURE proc4(inout a int, inout b int)
begin
set a = a*2;
select b*2 into b;
end $
DELIMITER ;
set @a = 10, @b := 20;
call proc4(@a, @b);
select @a, @b;
#查看存储过程
show create procedure proc4;
-- 函数
/*
create function 函数名(参数名称 参数类型)
returns 返回值类型
begin
函数体
end
*/
#调用函数
#select 函数名(实参列表);
#删除函数
#drop function [if exists] 函数名;
#查看函数详情
#show create function 函数名;
#无参函数
drop function if exists func1;
delimiter $
create function func1()
returns int
begin
declare max_id int default 0;
select max(id) into max_id from t_user;
return max_id;
end $
delimiter ;
#自定义函数定义报错
set global log_bin_trust_function_creators = 1;
show variables like 'log_bin_trust_function_creators';
select @@global.log_bin_trust_function_creators;
#调用自定义函数
select func1();
#有参函数
drop function if exists get_user_id;
delimiter $
create function get_user_id(v_name varchar(16))
returns int
begin
declare r_id int;
select id into r_id from t_user where name = v_name;
return r_id;
end $
delimiter ;
select get_user_id(name) from t_user;
-- 流程控制语句
use procedure_function_test;
drop table if exists t_user;
create table t_user(
id int primary key comment '编号',
sex tinyint not null default 1 comment '性别d 1男 2女',
name varchar(16) not null default '' comment '姓名'
) comment '用户表';
insert into t_user values (1, 1, '张三'), (2, 1, '李四'), (3, 2, '成慧敏'), (4, 1, '李嘉诚'), (5, 2, '药家鑫');
drop table if exists test1;
create table test1 (
a int not null
);
drop table if exists test2;
create table test2 (
a int not null,
b int not null
);
-- IF函数
#if(条件表达式, 值1, 值2)
select t.id, if(t.sex=1, '男', '女') 性别, t.name from t_user t;
-- CASE结构
/*
case 表达式
when 值1 then 结果1或语句1
when 值2 then 结果2或语句2
else 结果n或语句n
end
*/
#select中使用
select t.id 编号, (case t.sex when 1 then '男' else '女' end) 性别, t.name 姓名 from t_user t;
select t.id 编号, (case t.sex when 1 then '男' when 2 then '女' else '未知' end) 性别, t.name from t_user t;
#begin end中使用
drop procedure if exists proc6;
delete from procedure_function_test.t_user where id = 6;
DELIMITER $
CREATE PROCEDURE proc6(id int, sex_str varchar(8), name varchar(16))
begin
DECLARE v_sex tinyint unsigned;
case sex_str
when '男' then set v_sex = 1;
when '女' then set v_sex = 2;
end case;
insert into procedure_function_test.t_user values (id, v_sex, name);
end $
DELIMITER ;
call proc6(6, '男', '桶桶');
#函数中使用
drop function if exists func5;
DELIMITER $
create function func5(sex tinyint unsigned)
returns varchar(8)
begin
DECLARE v_sex varchar(8);
case sex
when 1 then set v_sex = '男';
when 2 then set v_sex = '女';
end case;
return v_sex;
end $
DELIMITER ;
select t.sex, func5(t.sex) 性别, name from t_user t;
-- IF结构
/*
if 条件语句1 then 语句1;
elseif 条件语句2 then 语句2;
...
else 语句n;
end if;
*/
#写一个存储过程，实现用户数据的插入和新增，如果id存在则修改，不存在则新增，并返回结果
drop procedure if exists add_update;
DELIMITER $
create procedure add_update(v_id int, v_sex varchar(8), v_name varchar(16), out result tinyint)
begin
declare v_count tinyint default 0;/*保存t_user表中记录数量*/
/*根据v_id查询数据放入v_count中*/
select count(id) into v_count from t_user where id = v_id;
/*v_count>0表示数据存在，则进行修改，否则新增*/
if v_count > 0 then
begin
declare l_sex tinyint;
select if(l_sex = '男', 1, 2) into l_sex;
update t_user set sex = l_sex, name = v_name where id = v_id;
/*获取update影响行数*/
select row_count() into result;
end;
else
begin
declare l_sex tinyint;
select if(l_sex = '男', 1, 2) into l_sex;
insert into t_user values (v_id, l_sex, name);
select 0 into result;
end;
end if;
end $
DELIMITER ;
call add_update(7, '男', '黎明', @results);
call add_update(7, '女', '小红', @results);
-- 循环：while、repeat、loop
#循环控制
#结束本次循环
#iterate 循环标签
#退出循环
#leave 循环标签;
-- while
/*
while 循环条件 do
循环体
end while [标签];
*/
#无循环控制语句-根据传入的参数v_count向test1表插入指定数量的数据
delete from test1 where 1=1;
drop procedure if exists insert_record;
DELIMITER $
create procedure insert_record(v_count int)
begin
DECLARE i INT DEFAULT 1;
tag1:
WHILE i<=v_count DO
INSERT INTO test1 values (i);
SET i=i+1;
end while;
end $
DELIMITER ;
call insert_record(5);
select * from test1;
delete from test1 where 1=1;
#添加leave控制语句-根据传入的参数v_count向test1表插入指定数量的数据，当超过10条时结束
drop procedure if exists insert_ten;
DELIMITER $
CREATE PROCEDURE insert_ten(v_count int)
BEGIN
DECLARE i int DEFAULT 1;
tag_insert:
WHILE i<=v_count DO
INSERT INTO test1 VALUES (i);
IF i=10 THEN
LEAVE tag_insert;
END IF;
SET i=i+1;
end while;
end $
DELIMITER ;
call insert_ten(10);
select * from test1;
delete from test1 where 1=1;
#添加iterate控制语句-根据传入的参数v_count向test1表插入指定数量的数据，只插入偶数数据
drop procedure if exists insert_even_number;
DELIMITER $
create procedure insert_even_number(v_count int)
begin
declare i int default 0;
tag_insert:
while i<=v_count do
set i=i+1;
if i%2=0 then
insert into test1 values (i);
else
iterate tag_insert;
end if;
end while;
end $
delimiter ;
call insert_even_number(20);
select * from test1;
delete from test1 where 1=1;
#嵌套循环-test2表2个字段，写一个存储过程（2个参数v_a_count,v_b_count）使用双重循环插入数据，a范围[1, v_a_count]，b范围[1, v_b_count]所有偶数组合
drop procedure if exists insert_two_even;
delimiter $
create procedure insert_two_even(v_a_count int, v_b_count int)
begin
declare v_a int default 0;
declare v_b int default 0;
tag_a:
while v_a<=v_a_count do
set v_a=v_a+1;
if v_a%2=0 then
tag_b:
while v_b<=v_b_count do
set v_b=v_b+1;
if v_b%2=0 then
insert into test2 values (v_a, v_b);
else
iterate tag_b;
end if;
end while;
else
set v_b = 0;
iterate tag_a;
end if;
end while;
end $
delimiter ;
call insert_two_even(10, 10);
drop procedure if exists insert_two_even2;
delimiter $
create procedure insert_two_even2(v_a_count int, v_b_count int)
begin
declare v_a int default 0;
declare v_b int default 0;
tag_a:
while v_a<=v_a_count do
set v_a=v_a+1;
set v_b=0;
tag_b:
while v_b<=v_b_count do
set v_b=v_b+1;
if v_b%2!=0 then
iterate tag_b;
end if;
if v_a%2!=0 then
iterate tag_a;
end if;
insert into test2 values (v_a, v_b);
end while ;
end while ;
end $
delimiter ;
call insert_two_even2(10, 10);
select * from test2;
-- repeat循环
/*
[标签:]repeat
循环体
until 循环结束条件 end repeat [标签];
*/
select * from test1;
drop procedure if exists insert_into_test1;
delimiter $
create procedure insert_into_test1(v_count int)
begin
declare i int default 1;
repeat
insert into test1 values (i);
set i=i+1;
until i>v_count end repeat ;
end $
delimiter ;
call insert_into_test1(5);
-- loop循环
/*
[标签]:loop
循环体;
end loop [标签];
*/
drop procedure if exists loop_insert;
delimiter $
create procedure loop_insert(v_count int)
begin
declare i int default 1;
loop_tag:
loop
if i>v_count then leave loop_tag;
else
insert into test1 values (i);
set i=i+1;
end if;
end loop ;
end $
delimiter ;
call loop_insert(10);
select * from test1;
-- 游标
drop database if exists cursor_db;
create database cursor_db;
use cursor_db;
create table test1(a int, b int);
insert into test1 values (1, 2), (3, 4), (5, 6);
create table test2(a int);
insert into test2 values (100), (200), (300);
create table test3(b int);
insert into test3 values (400), (500), (600);
#[声明游标]: declare 游标名称 cursor for 查询语句;
#[打开游标]: open 游标名称;
#[遍历游标]: fetch 游标名称 into 变量列表;
#[关闭游标]: close 游标名称;
#单游标示例
drop function if exists single_cursor_test;
delimiter $
create function single_cursor_test(v_max_a int)
returns int
begin
#用于保存结果
declare v_total int default 0;
#用于保存当前行中的a
declare v_a int default 0;
#用于保存当前行中的b
declare v_b int default 0;
#创建游标结束标志变量
declare v_done int default false;
#创建游标
declare sum_cursor cursor for select a, b from test1 where a<v_max_a;
#设置游标结束时v_done的值为true
declare continue handler for not found set v_done = true;
#设置v_total初始值
set v_total = 0;
#打开游标
open sum_cursor;
#使用loop循环遍历游标
cursor_loop:
loop
#首先获取当前行的数据并放入v_a,v_b中，如果当前行无数据，v_done将被置为true
fetch sum_cursor into v_a, v_b;
#通过v_done判断游标是否遍历结束
if v_done then
leave cursor_loop;
end if;
#对v_total值累加处理
set v_total = v_total + v_a + v_b;
end loop ;
#关闭游标
close sum_cursor;
#返回结果
return v_total;
end $
delimiter ;
set @@global.log_bin_trust_function_creators = 1;
select single_cursor_test(1);
#嵌套游标
drop procedure if exists nested_cursor;
delimiter $
create procedure nested_cursor()
begin
declare v_a int default 0;
declare v_b int default 0;
declare v_c_a int default false;
declare cursor_a cursor for select * from test2;
declare continue handler for not found set v_c_a = true;
open cursor_a;
loop_a:
loop
fetch cursor_a into v_a;
if v_c_a then leave loop_a;
end if;
#使用begin...end...将两个游标隔绝开
begin
declare v_c_b int default false;
declare cursor_b cursor for select * from test3;
declare continue handler for not found set v_c_b = true;
open cursor_b;
loop_b:
loop
fetch cursor_b into v_b;
if v_c_b then leave loop_b;
end if;
insert into test1 values (v_a, v_b);
end loop loop_b;
end;
end loop loop_a;
end $
delimiter ;
call nested_cursor();
select * from test1;
-- 异常捕获及处理
drop table if exists t1_exception;
create table t1_exception (
a int primary key
);
drop procedure if exists exception_not_catch;
delimiter $
#MySQL内部异常
#未进行异常捕获时第二条数据违反了唯一键约束导致只有一条数据插入成功
create procedure exception_not_catch(v_a int, v_b int)
begin
declare v_auto tinyint default 0;
select @@session.autocommit into v_auto;
set session autocommit = false;
start transaction;
insert into t1_exception values (v_a);
insert into t1_exception values (v_b);
commit;
set session autocommit = v_auto;
end $
delimiter ;
call exception_not_catch(1, 1);
#捕获唯一键约束异常并进行事务回滚
drop procedure if exists exception_catch;
delimiter $
create procedure exception_catch(v_a int, v_b int)
begin
declare exception_flag tinyint default 0;#异常标志
declare auto_flag tinyint default 0;#会话变量是否自动提交缓存
declare continue handler for sqlexception set exception_flag = true;
select @@session.autocommit into auto_flag;
start transaction;
insert into t1_exception values (v_a);
insert into t1_exception values (v_b);
if exception_flag then rollback;
else commit;
end if;
end $
delimiter ;
call exception_catch(1, 1);
#外部异常 - 由于SQL执行结果和期望不一致需要做处理的情况
drop table if exists t_funds;
create table t_funds (
user_id int primary key comment '用户编号',
available decimal(10, 2) not null default 0 comment '可用余额'
) comment '用户账户表';
drop table if exists t_order;
create table t_order (
id int primary key auto_increment comment '订单编号',
price decimal(10, 2) not null default 0 comment '订单金额'
) comment '订单表';
insert into t_funds values (1001, 1000);
#存储过程模拟实现下单扣款操作
drop procedure if exists order_procedure;
delimiter $
create procedure order_procedure(v_user_id int, v_price decimal(10, 2), out v_msg varchar(64))
order_tag:
begin
declare v_available decimal(10, 2);
#查询余额判断余额是否足够
select f.available into v_available from t_funds f where f.user_id = v_user_id;
if v_available < v_price then
set v_msg = '账户余额不足，无法下单！';
leave order_tag;
end if ;
#模拟耗时5秒
select sleep(5);
#计算余额
set v_available = v_available - v_price;
#更新余额
start transaction;
update t_funds set available = v_available where user_id = v_user_id;
#插入订单明细
insert into t_order (price) values (v_price);
#提交
commit;
set v_msg = '下单成功';
end $
delimiter ;
#在两个窗口同时下单
call order_procedure(1001, 100, @v_msg);
select @v_msg;
#两次下单均成功但是扣款只有一次100
select * from t_funds;
select * from t_order;
#对下单存储过程进行优化
drop table if exists t_funds;
create table t_funds (
user_id int primary key comment '用户编号',
available decimal(10, 2) not null default 0 comment '可用余额',
version int default 0 comment '版本号，每次修改+1'
) comment '用户账户表';
insert into t_funds values (1001, 1000, 0);
drop procedure if exists order_procedure_optimize;
delimiter $
create procedure order_procedure_optimize(v_user_id int, v_price decimal(10, 2), out v_msg varchar(64))
order_tag:
begin
declare v_available decimal(10, 2);
declare v_version int;
declare v_update_count int default 0;#修改的数据行数
select f.available, f.version into v_available, v_version from t_funds f where f.user_id = v_user_id;
if v_available < v_price then
set v_msg = '余额不足，无法下单';
leave order_tag;
end if ;
#模拟耗时5秒
select sleep(5);
set v_available = v_available - v_price;
start transaction;
update t_funds set available = v_available, version = v_version+1 where user_id = v_user_id and version = v_version;
#获取更新影响的行数
select row_count() into v_update_count;
if v_update_count = 1 then
insert into t_order (price) values (v_price);
set v_msg = 'order success';
commit;
else
set v_msg = 'order fail';
rollback;
end if ;
end $
delimiter ;
call order_procedure_optimize(1001, 100, @v_msg);
select @v_msg;
-- 索引
#创建索引
#create [unique] index 索引名称 on 表明(列名[(length)]);
#alter 表明 add [unique] index 索引名称 on (列名[(length)]);
#删除索引
#drop index 索引名称 on 表名;
#查看索引
#show index from 表名;
drop table if exists t_student;
create table t_student(
id int not null comment '编号',
name varchar(20) not null comment '姓名',
sex tinyint not null comment '性别',
email varchar(40) comment '邮箱'
);
drop procedure if exists create_million_records;
delimiter $
create procedure create_million_records()
begin
declare i int default 1;
declare v_name varchar(10) default '桶桶';
start transaction;
while i<=2000000 do
insert into t_student values (i, concat(v_name, i), if(mod(i, 2), 1, 2), concat(v_name, i, '@foxmail.com'));
if i%10000=0 then
commit;
start transaction;
end if ;
set i = i+1;
end while ;
commit;
end $
delimiter ;
call create_million_records();
select * from t_student where id = 100000;
create index idx1 on t_student_idx(id);
create unique index u_idx on t_student_idx (name);
select * from t_student_idx where id = 100000;
#由于邮箱@foxmail.com部分相同，前面部分已经可以唯一确定一个Email，指定最小长度可以节省空间保存更多索引
create index idx_fixed_length on t_student_idx (email(13));
show index from t_student_idx;
#t_student_primary包含主键id的表
alter table t_student_primary modify id int primary key comment '主键编号';
select * from t_student_primary where id = 10000;
show index in t_student_primary;
#范围查询
select * from t_student_primary where id between 100 and 500;
#数值和字符串进行比较时字符串会被强制转换为数值型
select 'hello' > 123;
select 'hello' < 123;
-- SQL中的where条件在数据库中提取与应用
use learnmysql;
drop table if exists where_extract_tbl;
create table where_extract_tbl (
a int primary key,
b int,
c int,
d int,
e varchar(20)
);
insert into where_extract_tbl values
(4, 3, 1, 1, 'd'),
(1, 1, 1, 1, 'a'),
(8, 8, 8, 8, 'h'),
(2, 2, 2, 2, 'b'),
(5, 2, 3, 5, 'e'),
(3, 3, 2, 2, 'c'),
(7, 4, 5, 5, 'g'),
(6, 6, 4, 4, 'f');
#Index Key - Index First Key、 Index Last Key
#Index Filter
#Table Filter
-- MySQL分布式锁
#乐观锁：通过增加版本号控制数据的更新只有第一条更新会成功其余更新可以通过获取会话变量row_count判断是否执行成功，失败则进行回滚
drop table if exists t_lock_distributed;
create table t_lock_distributed (
lock_key varchar(32) primary key not null comment '锁唯一标志',
request_id varchar(64) not null default '' comment '用来标识请求对象',
lock_count int not null default 0 comment '当前上锁次数',
timeout bigint not null default 0 comment '锁超时时间',
version int not null default 0 comment '版本号，每次+1'
);
select database();
select * from t_lock_distributed;
-- 案例：电商中资金账户高频变动解决方案
drop database if exists e_commerce;
create database e_commerce;
use e_commerce;
drop table if exists t_account;
create table t_account (
acct_id int primary key not null comment '账户ID',
balance decimal(12, 2) not null comment '账户余额',
version int not null default 0 comment '版本号，每次更新+1'
) comment '账户表';
drop table if exists t_acct_data;
create table t_acct_data (
id int auto_increment primary key comment '编号',
acct_id int not null comment '账户ID',
price decimal(12, 2) not null comment '交易额',
open_balance decimal(12, 2) not null comment '初期余额',
end_balance decimal(12, 2) not null comment '末期余额'
) comment '账户流水表';
insert into t_account (acct_id, balance, version) values (1, 10000, 0);
-- 【ORIGINAL VERSION】
#在多线程请求的场景下会造成同时查询出相同的balance覆盖更新的问题
drop procedure if exists update_account;
delimiter $
create procedure update_account(v_price decimal(12, 2), v_acct_id int)
begin
declare v_balance decimal(12, 2);
start transaction;
select balance into v_balance from t_account where acct_id = v_acct_id;
update t_account set balance = balance + v_price, version = version+1 where acct_id = v_acct_id;
insert into t_acct_data (acct_id, price, open_balance, end_balance) values (v_acct_id, v_price, v_balance, v_balance+price);
commit;
end $
delimiter ;
call update_account(100, 1);
-- 【优化】根据t_account表的数据更新结果进行流水表数据的处理，每次版本号+1实现乐观锁
#在多线程并发的情况下可能会出现大量失败的操作
drop procedure if exists update_account_optimistic_lock;
delimiter $
create procedure update_account_optimistic_lock(v_price decimal(12, 2), v_acct_id int)
begin
declare v_balance decimal(12, 2);
declare v_version int default 0;
declare v_update_count tinyint default 0;
declare v_update_exception tinyint;
declare continue handler for sqlexception set v_update_exception = true;
start transaction;
select balance, version into v_balance, v_version from t_account where acct_id = v_acct_id;
update t_account set balance = v_balance + v_price, version = version + 1 where acct_id = v_acct_id and version = v_version;
select row_count() into v_update_count;
if v_update_exception = true then
rollback;
elseif v_update_count = 1 then
#向流水表写入数据
insert into t_acct_data (acct_id, price, open_balance, end_balance) values (v_acct_id, v_price, v_balance, v_balance+v_price);
commit;
else
rollback;
end if ;
end $
delimiter ;
call update_account_optimistic_lock(100, 1);
-- 采用MySQL同步redo log和bin log日志的机制优化转账操作
#新增账户操作日志表
drop table if exists t_acct_log;
create table t_acct_log (
id int auto_increment primary key comment '编号',
acct_id int not null comment '账户ID',
open_balance decimal (12, 2) comment '账户扣款前余额',
price decimal(12, 2) not null comment '交易额',
status smallint not null default 0 comment '状态，0：待处理，1：处理成功'
) comment '账户操作日志表';
#修改账户表
drop table if exists t_account;
create table t_account (
acct_id int primary key not null comment '账户ID',
balance decimal (12, 2) not null comment '账户金额',
old_balance decimal (12, 2) not null comment '交易额',
version int not null default 0 comment '版本号，每次更新+1'
) comment '账户表';
insert into t_account values (1, 10000, 10000, 0);
drop procedure if exists update_account_async;
delimiter $
create procedure update_account_async(v_price decimal(12, 2), v_acct_id int)
begin
declare v_update_count int default 0;
start transaction;
insert into t_acct_log (acct_id, price, status) values (v_acct_id, v_price, 0);
update t_account set balance = balance + v_price, version = version + 1 where acct_id = v_acct_id and balance + v_price > 0;
select row_count() into v_update_count;
if v_update_count = 1 then
commit;
else
rollback;
end if;
end $
delimiter ;
#异步定时任务将转账日志处理存入转账流水表：查询t_acct_log表中状态为0的记录，进行遍历处理
drop procedure if exists update_account_async_job;
delimiter $
create procedure update_account_async_job()
begin
declare v_price decimal (12, 2);
declare v_acct_id int;
declare v_end int default false;
declare v_open_balance decimal (12, 2);
declare v_update_count tinyint default 0;
declare cursor_log cursor for select acct_id, open_balance, price from t_acct_log where status = 0;
declare continue handler for not found set v_end = true;
open cursor_log;
loop_tag: loop
if v_end != true then
fetch cursor_log into v_acct_id, v_open_balance, v_price;
start transaction;
insert into t_acct_data (acct_id, price, open_balance, end_balance) values (v_acct_id, v_price, v_open_balance, v_open_balance + v_price);
if v_update_count = 1 then
update t_acct_log set status = 1 where acct_id = v_acct_id;
if row_count() = 1 then
commit;
else
rollback ;
end if ;
end if ;
else
leave loop_tag;
end if ;
end loop ;
select price, acct_id into v_price, v_acct_id from t_acct_log where status = 0;
end $
delimiter ;
-- 跨库转账问题
#A库转账到B库，C库记录转账订单
drop database if exists cross_db_transfer_a;
create database cross_db_transfer_a;
use cross_db_transfer_a;
drop table if exists t_account;
create table t_account(
acct_id int primary key not null comment '账户ID',
balance decimal(12, 2) not null comment '账户金额',
version int not null default 0 comment '版本号，每次更新+1'
) comment '账户表';
drop table if exists t_order;
create table t_order(
transfer_order_id int primary key not null comment '转账订单ID',
price decimal(12, 2) not null comment '转账金额',
status smallint not null default 0 comment '状态：1，转账成功；2，转账失败',
version int not null default 0 comment '版本号，每次更新+1'
) comment '转账订单';
drop table if exists t_transfer_step_log;
create table t_transfer_step_log(
id int primary key not null comment '账户ID',
transfer_order_id int not null comment '转账订单ID',
step smallint not null comment '转账步骤：0，正向操作；1，回滚操作',
unique key (transfer_order_id, step)
) comment '转账步骤日志表';
drop database if exists cross_db_transfer_b;
create database cross_db_transfer_b;
use cross_db_transfer_b;
drop table if exists t_account;
create table t_account(
acct_id int primary key not null comment '账户ID',
balance decimal(12, 2) not null comment '账户金额',
version int not null default 0 comment '版本号，每次更新+1'
) comment '账户表';
drop table if exists t_order;
create table t_order(
transfer_order_id int primary key not null comment '转账订单ID',
price decimal(12, 2) not null comment '转账金额',
status smallint not null default 0 comment '状态：1，转账成功；2，转账失败',
version int not null default 0 comment '版本号，每次更新+1'
) comment '转账订单';
drop table if exists t_transfer_step_log;
create table t_transfer_step_log(
id int primary key not null comment '账户ID',
transfer_order_id int not null comment '转账订单ID',
step smallint not null comment '转账步骤：0，正向操作；1，回滚操作',
unique key (transfer_order_id, step)
) comment '转账步骤日志表';
drop database if exists cross_db_transfer_c;
create database cross_db_transfer_c;
use cross_db_transfer_c;
drop table if exists t_transfer_order;
create table t_transfer_order (
id int not null auto_increment primary key comment '账户ID',
from_acct_id int not null comment '转出方账户',
to_acct_id int not null comment '转入方账户',
price decimal(12, 2) not null comment '转账金额',
addtime int comment '入库时间（秒）',
status smallint not null default 0 comment '状态，0：待处理；1，转账成功；2：转账失败',
version int not null default 0 comment '版本号，每次更新+1'
) comment '转账订单表';
-- 转账步骤如下
#STEP 1:创建转账订单，订单状态为0表示处理中
drop procedure if exists step_one;
delimiter $
create procedure step_one(v_from_acct_id int, v_to_acct_id int, v_price decimal(12, 2), out t_transfer_order_id int)
begin
declare v_addtime int;
start transaction ;
select unix_timestamp() into v_addtime;
insert into cross_db_transfer_c.t_transfer_order (from_acct_id, to_acct_id, price, addtime, status, version)
values (v_from_acct_id, v_to_acct_id, v_price, v_addtime, 0, 0);
select id into t_transfer_order_id from cross_db_transfer_c.t_transfer_order tto where tto.from_acct_id = v_from_acct_id and tto.to_acct_id = v_to_acct_id and tto.price = v_price and tto.addtime = v_addtime and status = 0;
commit;
end $
delimiter ;
#STEP 2:A库操作(需要使用STEP 1的t_transfer_order_id),对A库账户进行转出操作并记录日志表，如果转出失败则向订单表插入转出失败的记录
drop procedure if exists step_two;
delimiter $
create procedure step_two(v_transfer_order_id int)
begin
end $
#STEP 3:分支，如果A库转出成功则执行STEP 4，如果A库转出失败则执行STEP 6
#STEP 4:首先检查订单表是否有转入成功或失败的记录，如果有则返回成功/失败；如果没有则执行转入操作成功则记录转账日志，并记录订单记录
#STEP 5:判断第4步操作是否成功，如果成功则执行STEP 7
#STEP 6:对C库进行操作更新转账订单为失败
#STEP 7:对C库进行操作更新转账订单为成功
#增加job补偿机制处理C库中状态为0或者超过10分钟的转账订单
以上内容源自itsoku的《MySQL笔记》
浙公网安备 33010602011771号