多表-DDL以及DQL

多表DDL

个表之间也可能存在关系
存在在一对多和多对多和一对多的关系

一对多（外键）

在子表建一哥字段（列）和对应父表关联

父表是一，对应子表的多（一个部门对应多个员工，但一个员工只能归属一个部门）
正常建表，但是这里没加外键约束！！！

# 新建个数据库（db03）来储存
create  database if not exists db03 ;
use db03;
-- 部门表
create table tb_dept (
                         id int unsigned primary key auto_increment comment 'ID',
                         name varchar(10) not null unique comment '部门名称',
                         create_time datetime not null comment '创建时间',
                         update_time datetime not null comment '修改时间'
) comment '部门表';
-- 员工表
create table tb_emp (
                        id int unsigned primary key auto_increment comment 'ID',
                        username varchar(20) not null unique comment '用户名',
                        password varchar(32) default '123456' comment '密码',
                        name varchar(10) not null comment '姓名',
                        gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
                        image varchar(300) comment '图像',
                        job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
                        entrydate date comment '入职时间',
                        dept_id int unsigned comment '归属的部门ID',
                        create_time datetime not null comment '创建时间',
                        update_time datetime not null comment '修改时间'
) comment '员工表';

不加外键约束的话其实相当于两个表的数据没有连接起来
就算你把部门表其中一个部门删了，员工表属于那个部门的还是存在

外键定义

注意：子表 添加外键到 父表
那个外键名称其实可以不写

更建议用图形化

这时删除1号部门删不掉（因为有属于1的员工）
可以删掉没有员工关联的五号部门

白雪外键

现在已经不用这个低能技术了
都在业务层逻辑中实现一套逻辑，代替外键的作用

注意后面的外键是用物理外键定义的，但是实际你要知道用逻辑外键就行

一对一

一对一场景
对用户信息查询的多，而对身份信息查询的少就分成两张表
可以提高查重效率

在任意一方加入外键==（unique属性-保证一对多）==
保证一对一的关系，关联另一个表的主键（该对应的字段）

多对多

比如一个学生可以选多门课，一门课可以被多个学生选择
这样就是多对多的关系
但是此时我们不能在一张表里自己添加字段进行外键关联，因为，无论在哪个表，都要存储多个数据
一对多，和一对一（一对多是在，多的表里面创建字段外键到一的表，一对一则在哪个表创建都可以）

所以要创建一个中间表，两个外键字段分别关联到两个表的主键
比如是你要查1号学生学了什么课
你可以直接去找studentid=1
查询，查询到对应的有courseid的数据
然后去对应的课程表找id对应的课程的名就行

案例

看一下大概的界面

设计

分类管理

分析一下，一个菜品肯定对应多个菜品
一个菜品对应一个分类
一对多

菜品管理
含菜品信息

套餐管理
也是一个套餐归属一个分类，一个分类可以对应多个套餐，也是一对多

在分析菜品和套餐间的关系
一个菜品可以归属多个套餐，一个套餐可以关联多个菜品
多对多（新建关系表）

根据上面的表
设计一下
这种表之间的关系设计一下

实现

-- 案例
-- 1. 分类表category

-- 2. 菜品表 dish

-- 3. 套餐表 setmeal

-- 4. 套餐菜品关系表 setmeal_dish

-- 分类表
create table category(
     id int unsigned primary key auto_increment comment '主键ID',
     name varchar(20) not null unique comment '分类名称',
     type tinyint unsigned not null comment '类型 1 菜品分类 2 套餐分类',
     sort tinyint unsigned not null comment '顺序',
     status tinyint unsigned not null default 0 comment '状态 0 禁用，1 启用',
     create_time datetime not null comment '创建时间',
     update_time datetime not null comment '更新时间'
) comment '分类表' ;

-- 菜品表
create table dish(
     id int unsigned primary key auto_increment comment '主键ID',
     name varchar(20) not null unique comment '菜品名称',
     category_id int unsigned not null comment '菜品分类ID',
     price decimal(8, 2) not null comment '菜品价格',
     image varchar(300) not null comment '菜品图片',
     description varchar(200) comment '描述信息',
     status tinyint unsigned not null default 0 comment '状态, 0 停售 1 起售',
     create_time datetime not null comment '创建时间',
     update_time datetime not null comment '更新时间'
) comment '菜品表';


-- 套餐表
create table setmeal(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(20) not null unique comment '套餐名称',
    category_id int unsigned not null comment '分类id',
    price decimal(8, 2) not null comment '套餐价格',
    image varchar(300) not null comment '图片',
    description varchar(200) comment '描述信息',
    status tinyint unsigned not null default 0 comment '状态 0 停售 1 起售',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '更新时间'
)comment '套餐' ;


-- 套餐菜品关联表
create table setmeal_dish(
     id int unsigned primary key auto_increment comment '主键ID',
     setmeal_id int unsigned not null comment '套餐id ',
     dish_id int unsigned not null comment '菜品id',
     copies tinyint unsigned not null comment '份数'
)comment '套餐菜品关系';

多表DQL

概述

也就是所谓的多表查询
最简单的多表查询

select * from tb_dept,tb_emp;

这样就会展示两个表的笛卡尔积
一共AxB条数据

展示出来的数据如图
其实无效数据的，所以需要筛选

然后我们用where筛选一下

select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

这样就可以筛选到全部的有效信息了。

内连接(交集)

# 查询员工姓名，以及所属部门名称（隐式内连接）
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_dept.id=tb_emp.dept_id;
# 起别名
select e.name,d.name from tb_emp (as) e, tb_dept (as) d where e.dept_id =d.id;
as可写可不写，但是起了别名后一定要把对应的表明改为别名

# 查询员工姓名，以及所属部门名称（显式内连接）
select tb_emp.name,tb_dept.name from tb_emp (inner) join tb_dept on dept_id=tb_dept.id;
inner加不加效果都一样

这样就行，两行代码的运行数据一样

外连接（交集兼选中集合的不符项）

分为==左外连接（一般都用这个，因为可以互相替换）==和右外连接
图中所示
表1（A）是左表，表2（B）为右表
左外连接不仅会包含条件后的选项，还会包含A中的所有项（比如A中有不满足条件，也会显示出来）
右外连接同理

比如这个做外连接

# 1.查询员工表 所有 员工姓名和对应部门名称(左外连接)
select * from tb_emp e left join tb_dept d on e.dept_id=d.id ;

之前插入17条员工，但内连接只能查到16
因为一个员工没有部门为null
但是用左外连接就能查到该数据

# 2查询部门表 所有 部门 以及对应员工
select * from tb_emp e right join tb_dept d on e.dept_id=d.id;

可以看到就算无员工的部门也包含了

什么时候用外连接，什么时候用内连接

看需求
如果需求是
查询全部员工、部门什么的就用外连接
正常没强调就内连接

子查询

根据子查询返回的不同
分为四种

标量子查询和列子查询

对应的标量子查询
就是a步骤

# 子查询
# 标量子查询
# A.查询教研部的所有员工
# a.查询到教研部部门的id
select id from tb_dept where name='教研部';
# b.查询该部门ID下员工的信息
select * from tb_emp where dept_id=(select id from tb_dept where name='教研部');

查询到一个标量就是2
然后我们可以直接通过dept_id=（a语句（2））来完成查询对应部门编号为2的操作

练习

查询在方东白入职之后入职的员工信息
1.查询方东白的入职日期（标量）
2.再查员工表比较
select * from tb_emp where entrydate>(select entrydate from tb_emp where name='方东白');

# 列子查询
# A.查询"教研部"和"咨询部"的所有员工信息
# a.查询'教研部'和'咨询部'对应的id
select id from tb_dept where name='教研部' or name='咨询部';
# b.查询对应dept_id=查询结果的
select * from tb_emp where dept_id in (select id from tb_dept where name='教研部' or name='咨询部');

这里返回的是一列多行
常用操作符为in 和 not in

行子查询和表子查询

# 行子查询
# A.查询与“韦一笑”的入职日期 及 职位的相同的员工信息
# a.查询“韦一笑”入职日期和职位
select entrydate,job from tb_emp where name='韦一笑';
# b.作为条件比较
select * from tb_emp where (entrydate,job)=('2007-01-01',2);
select * from tb_emp where (entrydate,job)=(select entrydate,job from tb_emp where name='韦一笑');

表子查询和对应的多表查询相似！！！！！
把临时表作为一个表进行多表查询
而不是像上面把查询那样作为判断条件了

# 表子查询
# A.查询入职日期是“2006-01-01”之后的员工信息，及其部门名称
# a.查询"2006-01-01"后入职的员工信息
select * from tb_emp where entrydate>'2006-01-01';
# b.根据对应的信息查询对应的部门名称
select * from (select * from tb_emp where entrydate>'2006-01-01') e,tb_dept d where e.dept_id=d.id;

案例

# 能加条件查询的尽量不要用子表查询，会多查询一次浪费时间
-- 需求:
-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
-- 表: dish , category
-- SQL:
select d.name,d.price,c.name
from dish d
   ,category c
where d.category_id=c.id and d.price<10;

-- 2. 查询 !所有! 价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品, 展示出菜品的名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
-- 表: dish , category
-- SQL:
select d.name,d.price,c.name from dish d
left join category c
    on  d.category_id=c.id and d.price between 10 and 50
and d.status=1;


-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
-- 表: dish , category
-- SQL:
select c.name,max(d.price)
from dish d,category c
where d.category_id=c.id
group by c.name;

-- 4. 查询各个分类下 菜品状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
-- 表: dish , category
-- SQL:
select c.name,count(*)
from dish d,category c
where d.status=1 and d.category_id=c.id
group by c.name
having count(*)>=3;

-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）.
-- 表: setmeal , setmeal_dish , dish
-- SQL:
select s.name,s.price,d.name,d.price,sd.copies
from dish d,setmeal s,setmeal_dish sd
    where s.id=sd.setmeal_id
    and d.id=sd.dish_id
    and s.name='商务套餐A';


-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
-- 表: dish
-- SQL:
-- a. 计算 菜品平均价格
select avg(price) from dish;

-- b. 查询出低于菜品平均价格的菜品信息
select price from dish where price<(select avg(price) from dish);

多表DQL要点

1.能不用子表查询就不要用，节省一次查询的时间
2.差用group by 来进行分组查询，且可以用having 来进行分组后的过滤查询（这里可以用聚合函数，where不能用）
3.聚合函数用的多
4.注意需求，所有这种一般是外连接的
5.感觉这块要多练，做起来挺难的