[大数据]SQL-03（数据表的约束和多表查询）

约束

定义：是对数据表的列上的规则，限制添加到表中的数据，保证数据库中数据的正确性，有效性，完整性。

分类：非空约束（NOT NULL），唯一约束（UNIQUE），主键约束（PRIMARY KEY），默认约束（DEFAULT），外键约束（FOREIGN KEY）。其中主键约束是指一行数据的非空并且唯一，外键约束是指让两个表数据之间建立链接。

现在使用一个小案例来使用以上的约束。

CREATE TABLE sus(
id INT PRIMARY KEY auto-increment, --主键且自增
ename VARCHAR(50) NOT NULL UNIQUE,-- 非空且唯一
joindate DATE NOT null,-- 非空
salary DOUBLE(7,2) NOT NULL,-- 非空
bonus DOUBLE(7,2) default 0-- 没有默认是0
);

INSERT INTO sus(id,ename,joindate,salary,bonus) VALUES (1,'第一个','2001-01-12',9000,200); -- 插入一条数据

INSERT INTO sus(ename,joindate,salary,bonus) VALUES ('第一个','2001-01-12',9000,200); -- 填入自增的样例

外键案例

建立一个员工和部门表，员工表有四个字段（id,name,age,dep_id)
部门表（id dep_name,addr)，其中dep_id对应的是部门表中的id.
使用外键将两个表连接起来。部门表是主表，员工表是从表。

-- 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
addr VARCHAR(20)
);
-- 添加部门信息
INSERT INTO dept(dep_name,addr) VALUES
('技术部','上海'),
('设计部','长沙');

-- 创建员工表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,
-- 添加外键，fk_emp_dept是外键的名字，这个意思是指员工表中的dep_id对应的是部门表中的id
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);
-- 添加员工信息
INSERT INTO emp(NAME,age,dep_id) VALUES
('第一个',22,2),
('第二个',23,1),
('第三个',20,2),
('第四个',21,1),
('第五个',24,2);

删除外键

-- 删除emp表中的fk_emp_dept外键
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;

添加外键（创表之后）

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id);

tips:添加外键之前，要先把主表的数据和表创好，不然那会添加不了，记住顺序。先主表在从表。

多表查询

相当于是条件查询。查询两个表之间的

SELECT * FROM emp,dept WHERE emp.dep_id=dept.id;

分类

连接查询

? 内连接：查询两个表之间交集数据

-- 查询emp和dept表交集数据，并且只显示需要的几个字段
SELECT emp.NAME,emp.age,dept.dep_name FROM emp,dept WHERE emp.dep_id=dept.id;

--第二种写法
SELECT * from emp INNER JOIN dept ON emp.dep_id=dept.id;

? 外连接：

? 左外连接：查询1表所有数据和交集部分数据

SELECT * FROM emp LEFT JOIN dept ON emp.dep_id=dept.id;

? 右外连接：查询2表所有数据和交集部分数据

SELECT * FROM emp right JOIN dept ON emp.dep_id=dept.id;

子查询(嵌套查询)

--查询年龄高于第三个的员工信息
SELECT * FROM emp WHERE age > (SELECT age FROM emp WHERE NAME='第三个');

--多行单列查询（查询部门是技术部或者是设计部的员工信息）
SELECT * FROM emp WHERE dep_id IN(SELECT id FROM dept WHERE dep_name='技术部' OR dep_name='设计部');

--多行多列查询（入职日期是‘2001-01-12’之后的员工信息和部门信息）这里使用t1来代替查询emp之后表
SELECT * FROM (SELECT * FROM emp WHERE join_date>'2001-01-12') t1,dept WHERE t1.dep_id=dept.id;