SQL基础(约束 多表查询 事务)

 內键约束

CREATE TABLE user(
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age int check( age > 0 && age <= 120 ) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别'
) comment '用户表';
#插入数据
insert into user(name,age,status,gender) values ('tom1',15,'1','男'),('tom2',19,'1','男'),
('tom3',29,'1','女');

外键约束

#制表
CREATE TABLE dept (
    id INT AUTO_INCREMENT COMMENT 'ID' 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 '员工表';

添加外键

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;

删除更新行为
如果在 dept 表中更新了 id，那么在 emp 表中所有相关的记录的 dept_id 也会被更新，而如果在 dept 表中删除了某个 id，那么在 emp 表中相关的记录也会被删除。

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE;

在更新或删除关联记录时，如果在 dept 表中找不到匹配的记录，将会把 emp 表中的 dept_id 设置为 NULL。

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id) ON UPDATE SET NULL ON DELETE SET NULL;

多表查询

内连接

select emp.name , dept.name from emp inner join dept on emp.dept_id = dept.id;

左外连接

select emp.* , dept.name from emp left outer join dept on emp.dept_id = dept.id; 

右外连接

select dept.* , emp.* from emp right outer join dept on emp.dept_id = dept.id;

自连接查询
可以是内连接

select a.name , b.name from emp a , emp b where a.managerid = b.id;

也可以是外连接

select a.name '员工' , b.name '领导' from emp a left join emp b on a.managerid = b.id;

联合查询

select * from emp where id < 5
union
select * from dept where id >3;

子查询
标量子查询

select * from emp where dept_id = (select id from dept where name = '经理部');

列子表查询

select * from emp where dept_id in (select id from dept where name = '经理部');

行子查询

select * from emp where (name,age) = (select name, age from emp where id = 1);

表子查询

select e.*,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

事务

事务操作
1.查看/设置事务提交方式

select @@autocommit;
set @@autocommit = ();

查看张三账户余额

select* from account where name = '张三';

将张三账户余额-1000

update account set money = money - 1000 where name = '张三';

将李四账户余额+1000

update account set money = money - 1000 where name = '李四';

提交事务

commit;

事务回滚

rollback;

2.开启事务

start transaction 或 begin

#查看张三账户余额
select* from account where name = '张三';
#将张三账户余额-1000
update account set money = money - 1000 where name = '张三';
#将李四账户余额+1000
update account set money = money - 1000 where name = '李四';

#提交事务
commit;
#事务回滚
rollback;

事务四大特性 ACID并发事务

事务隔离
查看事务隔离级别

select @@transaction_isolation;

修改事务隔离级别( READ UNCOMMITTED I READ COMMITTED I REPEATABLE READ I SERIALIZABLE  )

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;