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