MySQL学习笔记-部分实例datagrip源码-10-21

show tables;
create table user(id int primary key auto_increment,name varchar(10) not null unique,age int check(age>0 and age<=120),status char(1) default '1',gender char(1)
) comment '用户表';
-- 添加数据
insert into user(name, age, status, gender) values('张三', 20, '1', '1'), ('李四', 20, '0', '1');
insert into user(name, age, status, gender) values('王五', 20, '1', '0');insert into user(name, age, status, gender) values(null, 20, '1', '0');
insert into user(name, age, status, gender) values('王五', 20, '1', '0');
insert into user(name, age, status, gender) values('小七', 80, '1', '0');
insert into user(name, age, status, gender) values('小九', 121, '1', '0');
insert into user(name, age, gender) values('小九', 120,'0');create table dept(
id int auto_increment comment 'Io' primary key,
name varchar(50)not null comment'部门名称'
)comment'部门表';
INSERT INTo dept (id, name) VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');
create table emp(id        int auto_increment comment 'ID' primary key,name      varchar(50) not null comment '姓名',age       int comment '年龄',job       varchar(20) comment '职位',salary    int comment '薪资',entrydate date comment '入职时间',managerid int comment '直属领导ID',dept_id   int comment '部门ID'
)comment'员工表';INSERT INTo emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
(1,'金庸',66,'总裁',20000,'2080-01-01',null,5),
(2,'张无忌',20,'项日经理',12500,'2005-12-05',1,1),
(3,'杨道',33,'开发',8400,'2000-11-03',2,1),
(4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
(5,'常遇春',43,'开发',10500,'2004-09-07',3,1),
(6,'小昭',18,'程序员鼓励师',6600,'2004-10-12',2,1);
-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
-- 删除外键
alter table emp drop foreign key fk_emp_dept_id;create table account(
id int auto_increment primary key comment'主键ID',name varchar(10)comment'姓名',money int comment'余额comment"账户表');
insert into account(id, name, money)VALUES (nuLl,'张三',2000),(null,'李四',2000);
select @@autocommit;
set @@autocommit = 1 ;
update account set money = 2000 where name = '张三'or name = '李四';
-- 转账操作(张三给李四转账1000)
-- 1.查询张三账户余颜
select * from account where name ='张三';
-- 2.将张三账户余额-1000
update account set money=money-1000 where name ='张三';
程序执行报错 ......
-- 3.将李四账户余额+1000
update account set money = money + 1000 where name ='李四';
-- 事务提交
commit;
-- 事务回滚
rollback;
-- 方式二
-- 转账操作（张三给李四转账1000）
start transaction;
-- 1.查询张三账户余颜
select * from account where name ='张三';
-- 2.将张三账户余额-1000
update account set money=money-1000 where name ='张三';
程序执行报错 ......
-- 3.将李四账户余额+1000
update account set money = money + 1000 where name ='李四';
-- 事务提交
commit;
-- 事务回滚
rollback;
-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置事务隔离级别
set session transaction isolation level read uncommitted;
-- 设置事务默认级别
set session transaction isolation level repeatable read ;
-- 1.事务简介
-- 事务是一组操作的集合，这组操作，要么全部执行成功，要么全部执行失败。
-- 2.事务操作
-- START TRANSACTION;--开启事务
-- COMMIT/ROLLBACK;--提交/回滚事务
-- 3.事务四大特性
-- 原子性( Atomicity )、一致性( Consistency)、隔离性( Isolation)、持久性( Durability)
-- 4.并发事务问题
-- 赃读、不可重复读、幻读
-- 5.事务隔离级别
-- READ UNCOMMITTED 、READ COMMITTED、 REPEATABLE READ、SERIALIZABLE