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