约束条件 ,表与表之间的关系 和多表联合查询***
一.约束条件
为了确保数据的完整性 可以给列加上约束条件
完整性的分类:
1.实体完整性
2.域完整性
3.引用完整性
1.实体完整性
实体:即表中的一行(一条)记录 代表一个实体 entity
实体完整性的作用:标识每一行数据不重复
约束类型:
1.1 主键约束(primary key)
1.2 唯一约束(unique)
1.3 自动增长列(auto_increment)
1.1 主键约束
每一个表中要有一个主键(可以是一列 也可以是多列组成)
特点: 数据唯一 且不能为null
第一种添加方式 在创建表的同时 直接添加主键约束
create table 表名(
列名1 列值1 primary key,
列名2 列值2,
....
列名n 列值n
)
第二种添加方式 后期补充主键约束
create table 表名(
列名1 列值1,
列名2 列值2,
....
列名n 列值n
)
alter table 表名 add primary key(列名1);
联合主键 (由多个列组成的主键)
create table 表名(
列名1 列值1,
列名2 列值2,
....
列名n 列值n,
primary key(列名1,列名2);
)
删除主键约束
alter table 表名 drop primary key;
1.2 唯一约束
特点:数据不能重复 值可以为null
第一种添加方式 在建表的时候添加
create table 表名(
列名1 列值1 约束条件,
列名2 列值2 unique,
....
)
第二种创建方式 后期追加
alter table 表名 add unique(列名);
1.3 自动增长列
mysql --> auto_increment
sqlserver --> idenitiy
oracle --> sequence
特点:
1.设置自动增长的时候 列只能是整型
2.当有行被删除 自增会继续自增 在删除行的基础上+1
2.域完整性
域完整性的作用:限制此单元格的数据正确 不对照此列的其他单元格
域代表当前单元格
域完整性约束:
数据类型 非空约束(not null) 默认值约束(default)
2.1 数据类型约束
2.2 非空约束
not null
alter table 表名 modify 字段名 字段类型 not null;
2.3 默认值约束
default
第一种添加方式 创建表时添加
create table 表名(
列名1 列值1 约束,
列名2 列值2 约束 default 默认值,
....
列名n 列值n
)
第二种添加方式 后期追加
alter table 表名 alter 列名 set default 默认值;
3.引用完整性
外键约束 foreign key
用来约束 表与表之间的关系
建立外键关系 首先 明确 主从关系
外键一定是在从表中创建 从而找到与主表之间的关系
例：
主表 : 部门表 学生表
从表 : 员工表 分数表
外键必须要满足的条件:
1.至少涉及到两张表
2.建立外键关系 对应列具有相似的数据类型
3.必须建立索引 主键 和 外键
注意:外键是可以有重复的 也可以有空值
创建外键的两种方式
第一种 在创建表的时候 添加外键
constraint 外键关系名 foreign key(从表的字段) references 主表名(主表的字段)
constraint 外键关系名 可以不写 就是一个外键约束的名字 如果不写 系统会自动给
foreign key(从表的字段) 将 从表中的字段作为外键
references 主表名(主表的字段) 映射到主表的字段
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,//默认自动增加
sname VARCHAR(10) NOT NULL,//值不能为空
gender VARCHAR(10) DEFAULT "男"
)
CREATE TABLE score(
id INT PRIMARY KEY,
score INT,
fsid INT,
CONSTRAINT fk_student_score FOREIGN KEY(fsid) REFERENCES student(sid)//这里不加分号也不加逗号
)
第二种 表已经创建 后期追加
alter table 主表 add primary key(主表列名)
alter table 从表 add foreign key(从表列名) references 主表(主表列名)
注意:
1.在从表中添加数据时 外键关联列中存储的数据 必须是主表中存在的数据
2.想要删除主表中 正在被从表使用的行 必须先删除从表 再删除主表
删除外键关联:
alter table 表名 drop foreign key 外键关系名//就是你起得名字fk_student_score
二.表与表之间的关系
三种关系
1.一对一 1 vs 1
person 表 card 表 人表 和 身份证表
人是主表 身份证表是从表
2.一对多 1 vs N
3.多对多 N vs N
三.多表联合查询***
在关系型数据库中 一个查询往往会涉及到多张表 多张表如何进行联合查询 就成为了学习sql查询的关键
如果单纯的只将数据放在一张表中 那么这个表存在大量的数据冗余 这种数据库的设计是不合理的(一般不用)
1.合并结果集
union
union all
作用 合并结果集 把两个select语句的查询结果合并到一起
CREATE TABLE g(
gname VARCHAR(10),
gage INT
)
CREATE TABLE h(
hname VARCHAR(10),
hage INT
)
INSERT INTO g VALUES("a",1),("b",2),("c",3);
INSERT INTO h VALUES("d",1),("e",2),("c",3);
/*union 去除重复数据 合并*/
SELECT * FROM g UNION SELECT * FROM h;
/*union all 不去除重复数据 合并*/
SELECT * FROM g UNION ALL SELECT * FROM h;
要求:被合并的两个结果集 列数/列类型 必须相同
2.连接查询
连接查询 就是求多个表的乘积
例如 a表 连接 b表 那么查询结果就是 a表*b表
连接查询 产生 笛卡尔积(在sql中 实现方式 交叉连接 cross join)
所有的连接方式 都会先产生笛卡尔积 然后 加上限制条件 才会有实际查询意义
select * from emp,dept;
join 就是把多个表连接成一个表的手段
多表查询 分为 内/外连接
SELECT emp.ename,dept.dname,emp.job FROM emp,dept WHERE emp.deptno = dept.deptno;
SELECT e.ename,d.dname,e.job FROM emp AS e,dept AS d WHERE e.deptno = d.deptno;
AS关键字 可以给表 起别名 as可省略
内连接 [INNER]join...on INNER可省略
[隐式]
SELECT e.ename,d.dname,e.job FROM emp AS e,dept AS d WHERE e.deptno = d.deptno;
[显示]
SELECT e.ename,d.dname,e.job FROM emp e INNER join dept d ON e.deptno = d.deptno;
特点: 查询结果必须是满足条件的
外连接 [OUTER]join...on OUTER可省略
左外连接 Left [OUTER]join
右外连接 right [OUTER]join
全外连接 full join (Mysql不支持)
左外连接
以左表为主
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;
左连接 是先查询出左表(以左表为主)
然后查询右表 右表中满足条件的显示 不满足条件的显示为null
右外连接
以右表为主
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;
右连接 是先查询出右表(以右表为主)
然后查询左表 做表中满足条件的显示 不满足条件的显示为null
/*练习 查询出 员工姓名 工作 部门 部门所在地 当部门为 RESEARCH 的 不显示 */
SELECT * FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno WHERE dept.dname!="RESEARCH";
SELECT * FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno AND dept.dname!="RESEARCH" WHERE emp.ename !="SMITH";
/* 步骤
1.生成笛卡尔积 select * from emp cross join dept
2.应用 on 筛选器 emp.deptno = dept.deptno
3.看外连接 是 Left join 还是 right join
如果是 left join 会以左表为主 作为保留表
4.where过滤器 过滤的是 left join 之后的数据
*/
总结:
1.如果sql 用的是Left join
on 后面条件 对left表没作用 只是对right表起过滤作用
where语句可以对生成临时表后起到过滤 相当于过滤了 left表
2.如果sql 用的是right join
on 后面条件 对right表没作用 只是对left表起过滤作用
where语句可以对生成临时表后起到过滤 相当于过滤了 right表
on 和 where 的区别:
在使用left join时 on 和 where 区别如下:
1.on条件是在 生成临时表 时 使用的条件
不管on的条件是否为真 都会返回左边表的数据
2.where条件在 生成临时表 后 再对临时表进行过滤
如果条件不为真 就会全部过滤
自然连接 Natural join
自然连接 无需你去给出外键关联等式 它会自动找到这一等式
两张连接表中 名称 和 类型 完全一致的列 作为条件
/*自然连接*/
SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;
总结:
连接查询时 sql查询的核心 按照实际需求来选择对应的类型
如果选择不当 非但不能提高效率 反而会带来一系列的逻辑错误 或者性能下降
选择依据:
1.查两张表关联列相等的数据 用内连接
2.right表是left表的子集 用左外连接
3.left表是right表的子集 用右外连接
4.right 和 left 彼此之间有交集 但是互不为子集 使用全外连接
3.子查询
一个select语句中 包含另一个完整的select语句
子查询 就是 嵌套查询
子查询出现的位置:
where 后: 作为查询条件的一部分
from 后:作为表出现
当子查询出现在where后 作为条件时 还可以使用如下关键字
any 任意一个 ---> min
all 所有 ---> max
子查询结果集的形式:
1.单行单列 (用于条件)
2.单行多列 (用于条件)
3.多行单列 (用于条件)
4.多行多列 (表)
1.单行单列 (用于条件)
例如 :员工表中 工资高于CLARK的员工都有谁
分析 :
1.查询CLARK的工资
select sal from emp where ename="CLARK"
2.查询高于CLARK的员工
select * from emp where sal > 第一步的结果
3.结论
select * from emp where sal > (select sal from emp where ename="CLARK")
2.单行多列 (用于条件)
例如 :查询员工表中 工作 和 工资 与 MARTIN完全相同的员工信息
分析 :
1.查询MARTIN的工作和工资
select job,sal from emp where ename="MARTIN";
2.查询与MARTIN工作和工资相同的人
select * from emp where (job,sal) in 第一步的结果
3.结论
select * from emp where (job,sal) in (select job,sal from emp where ename="MARTIN")
3.多行单列 (用于条件)
多行子查询 返回多行数据
在where 使用多行子查询时 要用 all any in
例如 :查找工资高于30号部门所有人的 员工
分析 :
1.查找30号部门所有人的工资
select sal from emp where deptno=30;
2.查找高于30号部门所有人工资的员工信息
select * from emp where sal > all 第一步的结果
3.结论
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30);
SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno=30);
/*练习 部门编号为20的员工工资 并且 大于部门编号为30的所有人工资的 员工列表*/
SELECT * FROM emp WHERE deptno=20 AND sal> ALL(SELECT sal FROM emp WHERE deptno=30)
4.多行多列 (表)
例如 :查询员工编号为7788的员工 姓名 工资 部门名称 部门地址
分析 :涉及到 emp dept两张表
1.查询 姓名 和 工资
select ename,sal from emp where empno=7788
2.查询 部门名称 和 部门地址
select e.ename,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno and e.empno=7788
改版:
select e.ename,e.sal,d.dname,d.loc
from emp e,(select dname,loc,deptno from dept) d
where e.deptno = d.deptno and e.empno=7788
/*练习 求7369员工 姓名 经理姓名 经理编号 自己连接自己*/
SELECT e1.ename,e2.ename,e2.empno
FROM emp e1,(SELECT empno,ename FROM emp) e2
WHERE e1.mgr = e2.empno AND e1.empno = 7369
