数据库进阶（多表查询/约束/关系）【数据库笔记五】

目录

一、多表查询

（1） union 组合查询

（2）连接查询

（3）子查询（嵌套sql）

二、约束

primary key 主键约束

not null 非空约束

unique 唯一约束

外键约束

三、关系

概念模型

一对多

一对一

多对多

---

一、多表查询

格式

select 字段列表|* 
from 表名 
[where 搜索条件] 
[group by 分组字段 [having 分组条件]] 
[order by 排序字段 排序规则] 
[limit 分页参数];

分类：（1）union 组合查询；（2）连接查询；（3）子查询

 

（1） union 组合查询

MySQL允许执⾏多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。所以也称 union 查询为合并结果集。

使用规则：

①UNION必须由两条或两条以上的SELECT语句组成，语句之间⽤关键字UNION分隔；

②UNION中的每个查询必须包含相同的列、表达式或聚集函数；

③union：去除重复行；  union all 不去除重复行

--使⽤union将两个sql⼀并执⾏
select vend_id,prod_price from products where prod_price <= 5
union
select vend_id,prod_price from products where vend_id in(1001,1002);

（2）连接查询

分类：① 内连接；② 外连接（左外连接、右外连接、全外连接【MySQL不支持】）③自然连接（属于一种简化方式）④自连接

①内连接：查询结果两表都满足才显示（不满足或者 null 不显示）

-- 格式
select * from 表1 别名1 (inner) join 表2 别名2 on 别名1.xx = 别名2.xx;

-- eg.
select emp.ename,emp.sal,dept.dname
from employee emp
join department dep
on emp.deptno = dep.deptno;
-- 查询所有员工的姓名、工资以及部门名称
-- inner 可以不写，默认内连接

②外连接：外连接两表有主从关系，左外连接左表为主表，右外连接右表为主。主表无论是否满足条件都会显示（例如学生表和成绩表连接，学生表为主表，查询成绩的时候如果学生没有成绩，学生表内容依然会显示，成绩表部分对应显示为空【和内连接的区别是：内连接查不到成绩的学生，信息不显示】）

-- 格式
select * from 表1 别名1 left join 表名2 别名2 on 别名1.xx = 别名2.xx;

-- eg.查询所有员工姓名、工资、部门信息
-- 若员工无部门信息，内连接会忽略，外连接会显示 null 
select e.ename,e.sal,d.dname
from employee e 
left join department d
on e.deptno = d.deptno;

（3）子查询（嵌套sql）

查询中有查询 (注意：子查询效率最低，有替代方案就一般不用子查询)

select cust_name,cust_contact
from customers
where cust_id in (select cust_id
 				  from orders
 				  where order_num in (select order_num
 									  from orderitems
 									  where prod_id = 'TNT2'));
--为了执⾏上述SELECT语句，MySQL实际上必须执⾏3条SELECT语句。
--最⾥边的⼦查询返回订单号列表，此列表⽤于其外⾯的⼦查询的WHERE⼦句。
--外⾯的⼦查询返回客户ID列表，此客户ID列表⽤于最外层查询的WHERE⼦句。
--最外层查询确实返回所需的数据。

二、约束

primary key 主键约束

主键特性：非空、唯一、被引用（作为外键）

指定主键

-- 第一种在需要添加的字段后面加上 primary key 关键字
create table student(
	sid char(6) primary key,
	sname varchar(20),
	age int
);

-- 第二种在最后添加 primary key(指定列)字段
create table student(
	sid char(6),
	sname varchar(20),
	age int,
	primary key(sid)
);

-- 修改表的时候指定主键
alter table student add primary key(sid);

-- 删除主键
alter table student drop primary key;
-- 如果主键设置了自增长，需要先删除自增长

主键自增长

因为主键列的特性是：必须唯一、不能为空，所以我们通常会指定主键类型为整型。然后设置其自增长，这样就可以保证在插入的数据的时候主键列的唯一性和非空性。

-- 创建表的时候指定主键自增长
create table student(
	sid int primary key auto_increment,
	sname varchar(20),
	age int
);

-- 修改表的时候设置主键自增长
alter table student change sid sid int auto_increment;
-- 修改表时删除自增长
alter table student change sid sid int;

not null 非空约束

对某些不能设置为 null 值的列，可以对列添加非空约束 

create table student(
	sid int primary key auto_increment,
	sname varchar(20) not null,
	age int
);

unique 唯一约束

对某些不能设重复值的列，可以对列添加唯一约束

create table student(
	sid int primary key auto_increment,
	sname varchar(20) not null unique,
	age int
);

外键约束

如果员工表中有部门编号，为了保证这个部门编号在部门表中是存在的，需要对部门编号作出外键约束，来保证员工表的部门编号一点存在。外键必须是另一个表的主键

-- 格式
constraint 约束名称 foreign key(外键列名) reference 关联表(关联表的主键);

-- 创建表的时候指定外键约束
create table employee(
	empno int primary key,
	ename varchar(20),
	deptno int,
	constraint fk_emp_deptno foreign key(deptno) reference department(deptno);
);

-- 修改表的时候添加外键约束
alter table employee add constraint fk_emp_deptno foreign key(deptno) reference department(deptno);
-- 修改表的时候删除外键约束
alter table employee drop constraint fk_emp_deptno foreign key(deptno) reference department(deptno);

 

三、关系

概念模型

当我们要完成一个软件的时候，需要把系统中的实体抽取出来，形成概念模型。

例如部门、员工都是系统中的实体，概念模型中的实体最终会成为 Java 中的类（对象模型）、数据库中的表（关系模型）

对象模型：可以双向关联，而且引用的是对象，而不是一个主键

关系模型：只能多方引用一方，而且引用的只是主键，而不是一整行记录

 

实体之中三种关系：

一对多：每个员工从属一个部门，一个部门可有多个员工

一对一：一个老公对应一个老婆

多对多：一个老师可有多个学生，一个学生可有多个老师

 

一对多

指定一的主键为多的外键

-- 员工表
create table employee(
	empno int primary key,
	ename varchar(20),
	deptno int,
	constraint fk_emp_deptno foreign key(deptno) reference department(deptno);
);
-- 部门表
create table department(
	deptno int primary key,
	ename varchar(20)
);

一对一

建立一对一关系比较特殊，需要让其中一张表的主键，既是主键又是外键

-- 丈夫表
create table husband(
	hid int primary key,
	hname varchar(20)
);
-- 妻子表
create table wife(
	wid int primary key,
	wname varchar(20)
	constraint fk_wife_husband foreign key(wid) reference husband(hid);
);
-- husband.hid是主键，不能重复;wife.wid是主键，不能重复，又是外键，必须来自husband.hid

多对多

建立多对多的关系，需要使用中间表，即需要三张表，在中间表中使用两个外键，分别引用其他两个表的主键

-- 学生表
create table student(
	sid int primary key,
	sname varchar(20)
);
-- 老师表
create table teacher(
	tid int primary key,
	tname varchar(20)
);
-- 关联表
create table stu_tea(
	sid int,
	tid int,
	constraint fk_stu_tea_sid foreign key(sid) reference student(sid);
	constraint fk_stu_tea_tid foreign key(tid) reference teacher(tid);
);