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 '用戶表';
-- 添加數(shù)據(jù)
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,'研發(fā)部'),(2,'市場部'),(3,'財(cái)務(wù)部'),(4,'銷售部'),(5,'總經(jīng)辦');
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 '直屬領(lǐng)導(dǎo)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,'項(xiàng)日經(jīng)理',12500,'2005-12-05',1,1),
(3,'楊道',33,'開發(fā)',8400,'2000-11-03',2,1),
(4,'韋一笑',48,'開發(fā)',11000,'2002-02-05',2,1),
(5,'常遇春',43,'開發(fā)',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 = '李四';
-- 轉(zhuǎn)賬操作(張三給李四轉(zhuǎn)賬1000)
-- 1.查詢張三賬戶余顏
select * from account where name ='張三';
-- 2.將張三賬戶余額-1000
update account set money=money-1000 where name ='張三';
程序執(zhí)行報(bào)錯 ......
-- 3.將李四賬戶余額+1000
update account set money = money + 1000 where name ='李四';
-- 事務(wù)提交
commit;
-- 事務(wù)回滾
rollback;
-- 方式二
-- 轉(zhuǎn)賬操作（張三給李四轉(zhuǎn)賬1000）
start transaction;
-- 1.查詢張三賬戶余顏
select * from account where name ='張三';
-- 2.將張三賬戶余額-1000
update account set money=money-1000 where name ='張三';
程序執(zhí)行報(bào)錯 ......
-- 3.將李四賬戶余額+1000
update account set money = money + 1000 where name ='李四';
-- 事務(wù)提交
commit;
-- 事務(wù)回滾
rollback;
-- 查看事務(wù)隔離級別
select @@transaction_isolation;
-- 設(shè)置事務(wù)隔離級別
set session transaction isolation level read uncommitted;
-- 設(shè)置事務(wù)默認(rèn)級別
set session transaction isolation level repeatable read ;
-- 1.事務(wù)簡介
-- 事務(wù)是一組操作的集合，這組操作，要么全部執(zhí)行成功，要么全部執(zhí)行失敗。
-- 2.事務(wù)操作
-- START TRANSACTION;--開啟事務(wù)
-- COMMIT/ROLLBACK;--提交/回滾事務(wù)
-- 3.事務(wù)四大特性
-- 原子性( Atomicity )、一致性( Consistency)、隔離性( Isolation)、持久性( Durability)
-- 4.并發(fā)事務(wù)問題
-- 贓讀、不可重復(fù)讀、幻讀
-- 5.事務(wù)隔離級別
-- READ UNCOMMITTED 、READ COMMITTED、 REPEATABLE READ、SERIALIZABLE