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