逻辑外键建表 + 案例联系
-- =====================================多表设计================================
-- -------------------一对多 职位 与 员工
-- 员工 子表
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 '员工表';
-- 部门 父表
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 '部门表';
-- 插入测试数据
insert into tb_dept values
(1,'学业部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()),
(4,'就业部',now(),now()),(5,'人事部',now(),now()) ;
insert into tb_emp values
(null,'11','123456','一',1,'1.jpg',4,'2000-01-01',2,now(),now()),
(null,'22','123456','二',1,'1.jpg',4,'2001-01-01',2,now(),now()),
(null,'33','123456','三',1,'1.jpg',4,'2004-01-01',2,now(),now()),
(null,'44','123456','四',2,'1.jpg',4,'2007-01-01',2,now(),now()),
(null,'55','123456','五',2,'1.jpg',4,'2009-01-01',2,now(),now()),
(null,'66','123456','六',3,'1.jpg',4,'2010-01-01',2,now(),now()),
(null,'77','123456','七',3,'1.jpg',4,'2010-01-01',2,now(),now()),
(null,'88','123456','八',4,'1.jpg',4,'2011-01-01',2,now(),now()),
(null,'99','123456','九',4,'1.jpg',4,'2014-01-01',2,now(),now()),
(null,'1010','123456','十',5,'1.jpg',4,'2016-01-01',2,now(),now()),
(null,'1111','123456','十一',5,'1.jpg',4,'2018-01-01',2,now(),now()),
(null,'1212','123456','十二',5,'1.jpg',null,'2021-01-01',2,now(),now()) ;
-- -------------------一对一 用户 与 身份证
create table tb_user(
id int unsigned primary key auto_increment comment 'id',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别，1男，2女',
phone char(11) comment '手机号',
degree varchar(10) comment '学历'
) comment '用户信息表';
insert into tb_user values (null,'ttt',1,'12237334444','初中'),
(null,'nnn',1,'12283336444','大专'),
(null,'nnn',2,'12233834944','jn'),
(null,'nnn',2,'12233734444','xn');
create table tb_user_card(
id int unsigned primary key auto_increment comment 'id',
nationality varchar(10) not null comment '民族',
birthday date not null comment '生日',
idcard char(18) not null comment '身份证号',
issued varchar(20) not null comment '签发机关',
expier_begin date not null comment '有效期限-开始',
expier_end date comment '有效期限-结束',
user_id int unsigned not null unique comment '用户id', -- unique,一对一关键点，外键唯一
constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment '用户信息表';
insert into tb_user_card values
(null,'汉','1960-01-01','100000100000001000','昭阳区公安局','2000-01-01',null,1),
(null,'回','1970-01-01','100000100000001000','静安区公安局','2005-01-01','2025-01-01',2),
(null,'汉','1960-01-01','100000100000001000','昌平区公安局','2000-01-01',null,3),
(null,'汉','1966-01-01','100000100000001000','xxx区公安局','2000-01-01','2020-01-01',4);
-- -------------------多对多 学生 与 课程
-- 需要建立第三张中间表
create table tb_student(
id int auto_increment primary key comment '主键id',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';
insert into tb_student (name, no) VALUES
('nnn','10000000'),('lll','10000001'),('kkk','10000002'),('mmm','10000003');
create table tb_course(
id int auto_increment primary key comment '主键id',
name varchar(10) comment '课程名称'
) comment '课程表';
insert into tb_course (name) values ('java'),('php'),('c#'),('python');
create table tb_student_course(
id int auto_increment primary key comment '主键',
student_id int not null comment '学生id',
course_id int not null comment '课程id',
constraint fk_course_id foreign key (course_id) references tb_course(id),
constraint fk_student_id foreign key (student_id) references tb_student(id)
) comment '学生课程中间表';
insert into tb_student_course (student_id, course_id) VALUES (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);
-- ——————————多表查询
-- 用的是多表设计中一对多的表与数据
-- 单表
select * from tb_dept;
select * from tb_emp;
-- 多表
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id ;
-- ==============内链接 没有联系的查询不出来
-- a.查询员工的姓名和所属的部门名称 隐式内连接实现
select tb_emp.name , tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;
-- 别名 起别名之后后边只能用别名
select e.name , d.name from tb_emp e ,tb_dept d where e.dept_id = d.id;
-- b.查询员工的姓名和所属的部门名称 显式内连接实现 inner可以省略
select tb_emp.name , tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;
-- ==============外链接 显示所有数据 outer可以省略
-- 以下员工表为左表，部门表为右表
-- a.查询员工表所有员工姓名和对应的部门名称 （左外链接）
select e.name , d.name from tb_emp e left outer join tb_dept d on e.dept_id = d.id;
-- b.查询部门表所有部门信息和对应的员工名称 （有外链接）
select e.name , d.name from tb_emp e right outer join tb_dept d on e.dept_id = d.id;
-- 内连接（显式、隐式）只是方法不同，结果是一样的，外连接（左外链接、右外连接）结果不同（左外包含左表所有内容，右外同理）
-- ==============子查询
-- -----------------标量子查询 子查询的返回结果是单行单列的值
-- a.查询教研部所有员工信息
-- 步骤1：查询教研部的部门id - demp
select id from tb_dept where name = '教研部';
-- 步骤2：再查询该部门id下在员工信息 - emp
select * from tb_emp where dept_id = 2;
-- 最终：
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
-- b.查询在六入职之后的员工信息
-- 步骤1：查询六的入职时间
select entrydate from tb_emp where name = '六';
-- 步骤2：查询六入职之后的员工信息
select * from tb_emp where entrydate > '2010-01-01';
-- 最终：
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '六');
-- -----------------列子查询 子查询的返回结果是一列（可多行
-- a.查询教研部和咨询部的所有员工信息
-- 步骤1：查询教研部和咨询部的id
select id from tb_dept where name = '教研部' or name = '咨询部';
-- 步骤2：根据部门id查询该部门下的员工信息
select * from tb_emp where dept_id in (2,3);
-- 最终：
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
-- -----------------行子查询 子查询的返回结果是一行（可多列
-- a.查询和六入职日期和职位都相同的员工
-- 步骤1：查询六入职日期和职位
select entrydate , job from tb_emp where name = '六';
-- 步骤2：与其入职日期和职位都相同的员工
select * from tb_emp where entrydate = '2010-01-01' and job = 3;
-- 最终?
select * from tb_emp where entrydate = (select entrydate from tb_emp where name = '六') and job = (select job from tb_emp where name = '六');
-- 优化1：
select * from tb_emp where (entrydate , job) = ('2010-01-01' , 3);
-- 优化2：
select * from tb_emp where (entrydate , job) = (select entrydate , job from tb_emp where name = '六');
-- -----------------表子查询 多行多列
-- a.查询入职日期是 2010-01-01 之后的员工信息及其部门名称
-- 步骤1：查询查询入职日期是 2010-01-01 之后的员工信息
select * from tb_emp where entrydate > '2010-01-01';
-- 步骤2：查询这部分的员工信息及其部门名称 将上一个查询结果作为一张临时表来查询
select e.* , d.name from (select * from tb_emp where entrydate > '2010-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 outer join category c on c.id = d.category_id
where d.price between 10 and 50
and d.status = 1;
-- 3.查询每个分类下最贵的菜品、展示出分类的名称、最贵的菜品的价格 -- 分组查询不熟练
-- 表：dish , category
-- sql：
select c.name, max(d.price), d.name
from dish d,
category c
where d.category_id = c.id
group by c.name;
-- 4.查询各个分类下菜品状态为起售，并且该分类下菜品总数量大于等于3 的分类名称
-- 表：dish , category , setmeal_dish
-- sql：
select c.name, count(*)
from dish d,
category c,
setmeal_dish sd
where d.category_id = c.id
and d.status = 1
group by c.name
having count(*) > 3;
-- 5.查询商务套餐A中包含了哪些菜品（展示出套餐名称、价格、包含的菜品名称、价格、份数）
-- 表：dish , setmeal_dish , setmeal
-- sql：
select s.name, s.price, d.name, d.price, sd.copies
from dish d,
setmeal s,
setmeal_dish sd
where d.id = sd.dish_id
and s.id = sd.setmeal_id
and s.name = '商务套餐A';
-- 6.查询出低于菜品平均价格的菜品信息（展示出菜品名称、菜品价格） -- 子查询严重失误
-- 表：dish
-- sql：
-- having只能与group by 一起使用，这么用是不对的
-- select * from dish having price < avg(dish.price);
-- a.计算菜品平均价格
select avg(price) from dish;
-- b.查询低于平均价格的菜品信息
select * from dish where price < (select avg(price) from dish);
标签：comment,01,多表,name,--,sql,tb,id From： https://www.cnblogs.com/yansans/p/18313565