分组之后的目的聚合函数 最大 最小 平均数 求和
用分组之后的条件进行过滤 使用having
分组之后的聚合函数只能是分组字段
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
------------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
-----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
面试题 复制表格
----------------------------------------------------------------
use db02;
-- 员工管理(带约束)
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 '入职时间',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
-- 准备测试数据
insert into tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time)
values (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),
(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),
(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),
(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),
(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),
(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),
(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),
(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),
(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),
(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),
(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33',
'2022-10-27 16:36:01'),
(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),
(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33',
'2022-10-27 16:36:05'),
(17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33',
'2022-10-27 16:36:07'),
(18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
(19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
(20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
(21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),
(22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),
(23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
(24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
(25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
(26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
(27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
(28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
(29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');
-- =================== DQL: 基本查询 ======================
-- 1. 查询指定字段 name,entrydate 并返回
--
select name, entrydate
from tb_emp;
-- 2. 查询返回所有字段 查询多个字段 自己添加 或者全部选中 取消不必要的字段
select *
from tb_emp;
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp;
-- 3. 查询所有员工的 name,entrydate, 并起别名(姓名、入职日期) as可以省略 并且建议使用单引号
select name as '姓名', entrydate as '入职日期'
from tb_emp;
select name '姓名', entrydate '入职日期'
from tb_emp;
-- 4. 查询已有的员工关联了哪几种职位(不要重复) distinct不重复
select distinct job
from tb_emp;
-- =================== DQL: 条件查询 ====================== where
-- 1. 查询 姓名 为 杨逍 的员工
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where name = '杨逍';
-- 2. 查询 id小于等于5 的员工信息
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where id <= 5;
-- 3. 查询 没有分配职位 的员工信息
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where job is null;
-- 4. 查询 有职位 的员工信息
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where job is not null;
-- 5. 查询 密码不等于 '123456' 的员工信息
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where password != 123456;
-- 6. 查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where entrydate >= '2000-01-01'
and entrydate <= '2010-01-01';
-- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where entrydate >= '2000-01-01'
and entrydate <= '2010-01-01'
and gender = 2;
-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where job in (2, 3, 4);
-- 9. 查询 姓名 为两个字的员工信息
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where name like '__';
-- 10. 查询 姓 '张' 的员工信息
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where name like '张%';
-- =================== DQL: 分组查询 ======================
-- 聚合函数
group by
-- 1. 统计该企业员工数量 null无法使用聚合函数 语法：select 聚合函数(字段列表) from 表名 ;
select count(*)
from tb_emp; -- 推荐使用 *
select count(gender)
from tb_emp; -- 指定字段 不用引号
select count(1)
from tb_emp;
-- 常量
-- 2. 统计该企业员工 ID 的平均值 字段不用引号 语法：select 聚合函数(字段列表) from 表名 ;
select avg(id)
from tb_emp;
-- 3. 统计该企业最早入职的员工 语法：select 聚合函数(字段列表) from 表名 ;
select min(entrydate)
from tb_emp;
-- 4. 统计该企业最迟入职的员工
select max(entrydate)
from tb_emp;
-- 5. 统计该企业员工的 ID 之和
select sum(id)
from tb_emp;
-- 分组
-- 1. 根据性别分组 , 统计男性和女性员工的数量 group by 查询的必须是分组之后的字段后者聚合函数
select gender, count(*)
from tb_emp
group by gender;
-- 3. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位 having后加分组后的过滤条件
select job, count(*)
from tb_emp
where entrydate <= '2015-01-01'
group by job /* having count(*)>=2*/;
select job, count(*)
from tb_emp
where entrydate <= '2015-01-01'
group by job
having count(*) >= 2;
select job, count(*) 员工数量
from tb_emp
where entrydate <= '2015-01-01'
group by job
having count(*) >= 2;
select job, count(*) 员工数量
from tb_emp
where entrydate <= '2015-01-01'
group by job
having 员工数量 >= 2;
-- 案例1 ： 按需求完成员工管理的条件分页查询 - 根据输入条件，查询第一页数据，每页展示5条记录
-- 输入条件： 姓名：张XX 性别：男 入职时间：'2000-01-01' '2015-12-31'
select *
from tb_emp
where name like ('张%')
and gender = 1
and entrydate between '2000-01-01' and '2015-12-31'
limit 0,5;
-- 案例2 ： 男性与女性员工的人数统计 (1 : 男性员工 , 2 : 女性员工)
-- 函数: if(条件表达式 , t , f)
select if(gender = 1, '男性员工', '女性员工') '性别', count(*) 数量
from tb_emp
group by gender;
-- 展示每个职位信息对应的员工人数 -- count
-- 函数: case when ... then ... when ... then ... else ... end
select (case
when job = 1 then '班主任'
when job = 2 then '讲师'
when job = 3 then '学工主管'
when job = 4 then '教研主管'
else '未分配职业' end) as '职业' ,count(*) 数量
from tb_emp
group by job;
-- 听课练习
-- =================== DQL: 基本查询 ======================(每次查询时需要习惯性的先输入需要查询的表格 ,如果没有填写查询的表格,查询的字段会直接报错)
-- 1. 查询指定字段 name,entrydate 并返回 查询多个字段：select 字段1, 字段2, 字段3 from 表名;
select name, entrydate
from tb_emp;
-- 2. 查询返回所有字段查询所有字段（通配符）：select * from 表名;(不建议)
-- 第一种查询所有员工 select 之后直接输入需要查询的表格;在进行第一字段出现之后选中所有字段 可以取消主键
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp;
-- 第二种使用*通配符代替 也可以直接先写需要查询的表格
select *
from tb_emp;
-- 3. 查询所有员工的 name,entrydate, 并起别名(姓名、入职日期) 只展示修改的字段 设置别名：select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;
-- 引号 可以使用空格 建议使用单引号进行修改别名
select name as '姓名', entrydate as '入职日期'
from tb_emp;
-- 4.1 查询已有的员工关联了哪几种职位 可重复不需要添加distinct select 字段列表 from 表名; 与1相同查询特定的字段
select job
from tb_emp;
-- 4.2 查询已有的员工关联了哪几种职位(不要重复) 去除重复记录：select distinct 字段列表 from 表名; 使用去重来修饰字段
select distinct job
from tb_emp;
-- =================== DQL: 条件查询 从所有字段查询 *,或者全部字段 ====================== 条件查询：select 字段列表 from 表名 where 条件列表 ;
-- 1. 查询 姓名 为 杨逍 的员工 条件查询：select 字段列表 from 表名 where 条件列表 ;
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where name = '杨逍';
select *
from tb_emp
where name = '杨逍';
-- 2. 查询 id小于等于5 的员工信息 条件查询：select 字段列表 from 表名 where 条件列表 ;
select *
from tb_emp
where id <= 5;
-- 3. 查询 没有分配职位 的员工信息 条件查询：select 字段列表 from 表名 where 条件列表 ;
select *
from tb_emp
where job is null;
-- 4. 查询 有职位 的员工信息 条件查询：select 字段列表 from 表名 where 条件列表 ;
select *
from tb_emp
where job is not null;
-- 5. 查询 密码不等于 '123456' 的员工信息 条件查询：select 字段列表 from 表名 where 条件列表 ;
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where password != '123456';
select *
from tb_emp
where password <> '123456';
-- 6. 查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息 条件查询：select 字段列表 from 表名 where 条件列表 ;
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where entrydate >= '2000-01-01'
and entrydate <= '2010-01-01';
select *
from tb_emp
where entrydate between '2000-01-01' and '2010-01-01';
-- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息 条件查询：select 字段列表 from 表名 where 条件列表 ;
select *
from tb_emp
where entrydate between '2000-01-01' and '2010-01-01'
and gender = 2;
-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息 条件查询：select 字段列表 from 表名 where 条件列表 ; in(...) 在in之后的列表中的值，多选一 in(2,3,4,)//查询多个字段中不通类型的信息.
select *
from tb_emp
where job = 2 || job = 3
or job = 4;
select *
from tb_emp
where job in (2, 3, 4);
-- 9. 查询 姓名 为两个字的员工信息 匹配个数 条件查询：select 字段列表 from 表名 where 条件列表 ; like 占位符 模糊匹配(_匹配单个字符适用于查询为字符几个的信息就是几个_; %匹配任意个字符)
select *
from tb_emp
where name like ('__');
-- 10. 查询 姓 '张' 的员工信息 条件查询：select 字段列表 from 表名 where 条件列表 ;
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
where name like ('张%');
-- =================== DQL: 分组查询 ====================== group by
-- 1. 统计该企业员工数量 null无法使用聚合函数 语法：select 聚合函数(字段列表) from 表名 ;
select count(*)
from tb_emp; -- 推荐使用 *
select count(gender)
from tb_emp; -- 指定字段 不用引号
select count(1)
from tb_emp;
-- 常量
-- 2. 统计该企业员工 ID 的平均值 字段不用引号 语法：select 聚合函数(字段列表) from 表名 ;
select avg(id)
from tb_emp;
-- 3. 统计该企业最早入职的员工 语法：select 聚合函数(字段列表) from 表名 ;
select min(entrydate)
from tb_emp;
-- 4. 统计该企业最迟入职的员工
select max(entrydate)
from tb_emp;
-- 5. 统计该企业员工的 ID 之和
select sum(id)
from tb_emp;
-- 分组 group by 可以根据某几个字段进行分组统计
-- 1. 根据性别分组 , 统计男性和女性员工的数量 select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];
select gender, count(*)
from tb_emp
group by gender;
-- select后必须是分组后的字段 或者聚合函数 重在理解
-- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位 select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];
select job, count(*)
from tb_emp
where entrydate < '2015-01-01'
group by job
having count(*) >= 2;
-- =================== 排序查询 ====================== ASC：升序（默认值） DESC：降序
-- 1. 根据入职时间, 对员工进行升序排序 select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 … ;
select *
from tb_emp
order by entrydate asc; -- asc只要填写了排序规则,系统默认的是升序;
select *
from tb_emp
order by entrydate;
-- 2. 根据入职时间, 对员工进行降序排序 条件查询：select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 … ;
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
create_time,
update_time
from tb_emp
order by entrydate desc;
-- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同, 再按照 更新时间 进行降序排序 条件查询：select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 … ;
select *
from tb_emp
order by entrydate, update_time desc;
-- =================== 分页查询 ====================== 分页查询（limit 起始索引,查询记录数 ） 页码-1 * 每页展示的数量
-- 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 5。 分页查询：select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
-- 1. 从起始索引0开始查询员工数据, 每页展示5条记录
select *
from tb_emp
limit 0,5;
-- 2. 查询 第1页 员工数据, 每页展示5条记录
select *
from tb_emp
limit 0,5;
-- 3. 查询 第2页 员工数据, 每页展示5条记录
select *
from tb_emp
limit 5,5;
-- 4. 查询 第3页 员工数据, 每页展示5条记录
select *
from tb_emp
limit 10,5;
-- 案例一 按照需求完成员工管理的条件分页查询 根据输入条件 查询第一页的数据 每页展示10条记录
-- 输入条件 :
-- 姓名:张 模糊查询
-- 性别:男 1
-- 入职时间:2000-01-01 2015-12-31 分页10
select *
from tb_emp
where name like '%张%' && gender = 1 && entrydate between '2000-01-01' and '2015-12-31'
limit 0,10;
-- 案例二 员工性别统计 -count(*)
-- 条件表达式 true 取值 false取值 将数据1 2修改为男女性别
# select count(*)
# from tb_emp where gender=1;
select gender, count(*)
from tb_emp
group by gender;
select if(gender = 1, '男性', '女性') as '性别', count(*) as '人数'
from tb_emp
group by gender;
-- 员工职位统计 班主任 讲师 教研主管 学工主管
-- case 表达式 when 值1 then 结果1 when 值2 then 结果2 ...else...end
select job as '职业', count(*) as '人数'
from tb_emp
group by job;
select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end) '职位',
count(*) as '人数'
from tb_emp
group by job;
use db05_hw;
-- 员工管理(带约束)
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 '入职时间',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
-- 准备测试数据
INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time) VALUES
(1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),
(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),
(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),
(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),
(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),
(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),
(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),
(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),
(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),
(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),
(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33', '2022-10-27 16:36:01'),
(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),
(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:05'),
(17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33', '2022-10-27 16:36:07'),
(18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
(19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
(20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
(21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),
(22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),
(23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
(24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
(25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
(26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
(27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
(28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
(29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');
-- 1. 查询指定字段 name,entrydate 并返回
select name,entrydate from tb_emp;
-- 2. 查询返回所有字段 （两种写法哦）
select * from tb_emp;
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp;
-- 3. 查询所有员工的 name,entrydate, 并起别名 (姓名、入职日期)
select name '姓名', entrydate '入职日期' from tb_emp;
-- 4. 查询已有的员工关联了 哪几种 职位(不要重复)
select distinct job from tb_emp;
-- 5. 查询 姓名 为 '杨逍' 的员工
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where name = '杨逍' ;
-- 6. 查询在 id小于等于5 的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where id <=5;
-- 7. 查询 没有分配职位 的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where job is null;
-- 8. 查询 有职位 的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where job is not null;
-- 9. 查询 密码不等于 '123456' 的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where password!=123456;
-- 10. 查询入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where entrydate>='2000-01-01' and entrydate <= '2010-01-01' ;
-- 11. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where entrydate>='2000-01-01' and entrydate <= '2010-01-01' and gender =2 ;
-- 12. 查询 职位是 2 (讲师) 或 3 (学工主管) 或 4 (教研主管) 的员工信息（两种写法实现）
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp where job in(2,3,4);
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp where job = 2 or job = 3 or job = 4;
-- 13. 查询姓名为两个字的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where name like '__';
-- 14. 查询姓 '张' 的员工信息 且 入职时间在 '2008-01-01' 之后入职的员工
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where name like '张%' and entrydate >='2008-01-01';
-- 15. 查询出 性别为 男 , 或 入职时间在 2010-01-01 之后入职的员工
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp where gender = 1 and entrydate >='2010-01-01';
-- 16. 根据性别分组 , 统计男性和女性员工的数量 (结果展示时, 性别不能展示 1 或 2 , 要转化为 男 或 女 )
select if(gender=1,'男','女')'性别', count(*) 数量
from tb_emp group by gender;
-- 17. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job, count(*)
from tb_emp where entrydate <= '2015-01-01' group by job having count(*)>=2;
-- 18. 查询所有的性别为男(gender 为 1)的 讲师 (job 为 2) , 并根据入职时间, 对员工进行升序排序
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where gender = 1 and job = 2 order by entrydate ;
-- 19. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 ID 进行降序排序
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp order by entrydate asc ,id desc ;
-- 10. 查询性别为男(gender 为 1)的员工, 并根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 ID 进行降序排序
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where gender = 1 order by entrydate asc ,id desc ;
-- 21. 查询性别为男(gender 为 1)的员工 且 在 '2018-10-01' 之前入职的员工, 并根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 ID 进行降序排序
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where gender = 1 and entrydate <= '2018-10-01' order by entrydate asc ,id desc ;
-- 22. 查询姓 '张' 且 在 '2018-10-01' 之前入职的员工, 并根据入职时间进行升序排序, 并对结果分页操作, 展示第1页员工数据, 每页展示5条记录
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where name like '张%' and entrydate<= '2018-10-01' order by entrydate asc limit 0,5;
-- 23. 查询所有性别为 '男' 且 职位 为 '讲师' 的员工数据, 并根据入职时间进行升序排序, 入职时间相同 根据id倒序排序, 并对结果分页操作, 并进行分页操作, 展示第2页员工数据, 每页展示5条记录
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where gender = 1 and job = 2 order by entrydate asc ,id desc limit 5,5;
-- 24. 查询所有员工的 姓名、性别、职位。 并满足如下两点要求: (提示: if , case)
-- 1). 要求查询出来的结果中，性别要展示出 男/女，不要展示1/2
-- 2). 要求查询出来的结果中，职位要展示出 班主任/讲师/学工主管/教研主管，而不要展示 1/2/3/4 不需要统计数量
select name, if(gender = 1 ,'男','女') as '性别',(case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管'when 4 then '教研主管' else ' 无分派工作' end ) as ' 职业' from tb_emp group by name ,gender ,job ;
use db05_hw01;
-- 员工管理
create table employee (
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 教研主管, 5 咨询师',
entrydate date comment '入职时间',
salary int unsigned comment '月工资',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
INSERT INTO employee
(id, username, password, name, gender, image, job, entrydate,salary, create_time, update_time) VALUES
(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',20000,now(),now()),
(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',18000,now(),now()),
(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',16800,now(),now()),
(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',12000,now(),now()),
(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',9000,now(),now()),
(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',6000,now(),now()),
(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',6500,now(),now()),
(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',7200,now(),now()),
(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',5300,now(),now()),
(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',12000,now(),now()),
(11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',8900,now(),now()),
(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',7800,now(),now()),
(13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',6800,now(),now()),
(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',15800,now(),now()),
(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',11500,now(),now()),
(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',8300,now(),now()),
(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',4500,now(),now());
-- 1. 将 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工，工资统一涨 1000
-- 提示: 如果更新某一个字段, 将其在原有基础上增加多少 , 可以使用 : .... set a = a + 10 这样的操作.
update employee set salary = salary+1000 where entrydate >='2000-01-01' and entrydate <='2010-01-01';
-- 2. 将 工资在 5000 至 7000 或 性别为女 的员工，工资统一涨 500 , 提示: set salary = salary+500
update employee set salary = salary+500 where salary >=5000 and salary<=7000 or gender = 2;
-- 3. 将 职位是 2 (讲师) 或 3 (学工主管) 或 4 (教研主管) 的员工的入职时间, 统一设置为 '2010-09-01'
update employee set entrydate = '2010-09-01' where job in (2,3,4);
-- 4. 给没有分配职位的员工, 分配职位为 班主任
update employee set job = 1 where job is null;
-- 5. 删除 性别 为 女 , 且 岗位 为 咨询师 的员工信息
-- 没有修改状态字段 使用物理删除
delete from employee where gender = 2 and job = 5;
use db06_demo01;
-- 部门管理
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 (id, name, create_time, update_time)
values(1,'学工部',now(),now()),
(2,'教研部',now(),now()),
(3,'咨询部',now(),now()),
(4,'就业部',now(),now()),
(5,'人事部',now(),now());
-- 员工管理
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 教研主管, 5 咨询师',
entrydate date comment '入职时间',
dept_id int unsigned comment '部门ID',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
(11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()),
(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()),
(13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()),
(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,now(),now()),
(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());
-- ============================= 内连接 =================== =======
-- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现) 隐式内连接：select 字段列表 from 表1 , 表2 where 条件 ... ;
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_dept.id=tb_emp.dept_id;
-- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现) 显式内连接：select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;
select tb_dept.name , tb_emp.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;
-- inner
-- =============================== 外连接 ============================
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接) 左外连接：select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;
select tb_emp.name,tb_dept.name from tb_emp left join tb_dept on tb_dept.id=tb_emp.dept_id;
-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接) 右外连接：select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;
select tb_emp.name,tb_dept.name from tb_emp right join tb_dept on tb_dept.id=tb_emp.dept_id;
-- ========================= 子查询 ================================
-- 标量子查询
-- A. 查询 "教研部" 的所有员工信息
# 1. 查询 "教研部" 部门ID
# 2. 根据 "教研部" 部门ID，查询员工信息
-- 1.查询"教研部"部门ID
select id from tb_dept where name = '教研部'; #查询结果：2
-- 2.根据"教研部"部门ID, 查询员工信息
select * from tb_emp where dept_id = 2;
-- 合并出上两条SQL语句
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
-- B. 查询在 "方东白" 入职之后的员工信息
# 可以将需求分解为两步：
# 1. 查询 方东白 的入职日期
# 2. 查询 指定入职日期之后入职的员工信息
-- 1.查询"方东白"的入职日期
select entrydate from tb_emp where name = '方东白'; #查询结果：2012-11-01
-- 2.查询指定入职日期之后入职的员工信息
select * from tb_emp where entrydate > '2012-11-01';
-- 合并以上两条SQL语句
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');
-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
# 分解为以下两步：
# 1. 查询 "销售部" 和 "市场部" 的部门ID
# 2. 根据部门ID, 查询员工信息
-- 1.查询"销售部"和"市场部"的部门ID
select id from tb_dept where name = '教研部' or name = '咨询部'; #查询结果：3,2
-- 2.根据部门ID, 查询员工信息
select * from tb_emp where dept_id in (3,2);
-- 合并以上两条SQL语句
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
-- 行子查询
-- A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
# 可以拆解为两步进行：
#
# 1. 查询 "韦一笑" 的入职日期 及 职位
# 2. 查询与"韦一笑"的入职日期及职位相同的员工信息
-- 查询"韦一笑"的入职日期 及 职位
select entrydate , job from tb_emp where name = '韦一笑'; #查询结果： 2007-01-01 , 2
-- 查询与"韦一笑"的入职日期及职位相同的员工信息
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);
-- 合并以上两条SQL语句
select * from tb_emp where (entrydate,job) = (select entrydate , job from tb_emp where name = '韦一笑');
-- 表子查询
-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
# 分解为两步执行：
#
# 1. 查询入职日期是 "2006-01-01" 之后的员工信息
# 2. 基于查询到的员工信息，在查询对应的部门信息
select * from tb_emp where entrydate > '2006-01-01';
select e.*, d.* from (select * from tb_emp where entrydate > '2006-01-01') e left join tb_dept d on e.dept_id = d.id ;
use db06_demo02;
-- 分类表
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 '套餐菜品中间表';
-- ================================== 导入测试数据 ====================================
-- category
insert into category (id, type, name, sort, status, create_time, update_time) values (1, 1, '酒水饮料', 10, 1, '2022-08-09 22:09:18', '2022-08-09 22:09:18');
insert into category (id, type, name, sort, status, create_time, update_time) values (2, 1, '传统主食', 9, 1, '2022-08-09 22:09:32', '2022-08-09 22:18:53');
insert into category (id, type, name, sort, status, create_time, update_time) values (3, 2, '人气套餐', 12, 1, '2022-08-09 22:11:38', '2022-08-10 11:04:40');
insert into category (id, type, name, sort, status, create_time, update_time) values (4, 2, '商务套餐', 13, 1, '2022-08-09 22:14:10', '2022-08-10 11:04:48');
insert into category (id, type, name, sort, status, create_time, update_time) values (5, 1, '经典川菜', 6, 1, '2022-08-09 22:17:42', '2022-08-09 22:17:42');
insert into category (id, type, name, sort, status, create_time, update_time) values (6, 1, '新鲜时蔬', 7, 1, '2022-08-09 22:18:12', '2022-08-09 22:18:28');
insert into category (id, type, name, sort, status, create_time, update_time) values (7, 1, '汤类', 11, 1, '2022-08-10 10:51:47', '2022-08-10 10:51:47');
-- dish
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (1,'王老吉', 1, 6.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/41bfcacf-7ad4-4927-8b26-df366553a94c.png', '', 1, '2022-06-09 22:40:47', '2022-06-09 22:40:47');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (2,'北冰洋', 1, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4451d4be-89a2-4939-9c69-3a87151cb979.png', '还是小时候的味道', 1, '2022-06-10 09:18:49', '2022-06-10 09:18:49');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (3,'雪花啤酒', 1, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/bf8cbfc1-04d2-40e8-9826-061ee41ab87c.png', '', 1, '2022-06-10 09:22:54', '2022-06-10 09:22:54');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (4,'米饭', 2, 2.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/76752350-2121-44d2-b477-10791c23a8ec.png', '精选五常大米', 1, '2022-06-10 09:30:17', '2022-06-10 09:30:17');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (5,'馒头', 2, 1.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/475cc599-8661-4899-8f9e-121dd8ef7d02.png', '优质面粉', 1, '2022-06-10 09:34:28', '2022-06-10 09:34:28');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (6,'老坛酸菜鱼', 5, 56.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4a9cefba-6a74-467e-9fde-6e687ea725d7.png', '原料：汤，草鱼，酸菜', 1, '2022-06-10 09:40:51', '2022-06-10 09:40:51');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (7,'经典酸菜鮰鱼', 5, 66.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/5260ff39-986c-4a97-8850-2ec8c7583efc.png', '原料：酸菜，江团，鮰鱼', 1, '2022-06-10 09:46:02', '2022-06-10 09:46:02');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (8,'蜀味水煮草鱼', 5, 38.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a6953d5a-4c18-4b30-9319-4926ee77261f.png', '原料：草鱼，汤', 1, '2022-06-10 09:48:37', '2022-06-10 09:48:37');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (9,'清炒小油菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/3613d38e-5614-41c2-90ed-ff175bf50716.png', '原料：小油菜', 1, '2022-06-10 09:51:46', '2022-06-10 09:51:46');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (10,'蒜蓉娃娃菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4879ed66-3860-4b28-ba14-306ac025fdec.png', '原料：蒜，娃娃菜', 1, '2022-06-10 09:53:37', '2022-06-10 09:53:37');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (11,'清炒西兰花', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/e9ec4ba4-4b22-4fc8-9be0-4946e6aeb937.png', '原料：西兰花', 1, '2022-06-10 09:55:44', '2022-06-10 09:55:44');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (12,'炝炒圆白菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/22f59feb-0d44-430e-a6cd-6a49f27453ca.png', '原料：圆白菜', 1, '2022-06-10 09:58:35', '2022-06-10 09:58:35');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (13,'清蒸鲈鱼', 5, 98.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c18b5c67-3b71-466c-a75a-e63c6449f21c.png', '原料：鲈鱼', 1, '2022-06-10 10:12:28', '2022-06-10 10:12:28');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (14,'东坡肘子', 5, 138.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a80a4b8c-c93e-4f43-ac8a-856b0d5cc451.png', '原料：猪肘棒', 1, '2022-06-10 10:24:03', '2022-06-10 10:24:03');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (15,'梅菜扣肉', 5, 58.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/6080b118-e30a-4577-aab4-45042e3f88be.png', '原料：猪肉，梅菜', 1, '2022-06-10 10:26:03', '2022-06-10 10:26:03');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (16,'剁椒鱼头', 5, 66.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/13da832f-ef2c-484d-8370-5934a1045a06.png', '原料：鲢鱼，剁椒', 1, '2022-06-10 10:28:54', '2022-06-10 10:28:54');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (17,'馋嘴牛蛙', 5, 98.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/7a55b845-1f2b-41fa-9486-76d187ee9ee1.png', '配料：鲜活牛蛙，丝瓜，黄豆芽', 1, '2022-06-10 10:37:52', '2022-06-10 10:37:52');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (18,'鸡蛋汤', 7, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c09a0ee8-9d19-428d-81b9-746221824113.png', '配料：鸡蛋，紫菜', 1, '2022-06-10 10:54:25', '2022-06-10 10:54:25');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (19,'平菇豆腐汤', 7, 6.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/16d0a3d6-2253-4cfc-9b49-bf7bd9eb2ad2.png', '配料：豆腐，平菇', 1, '2022-06-10 10:55:02', '2022-06-10 10:55:02');
-- setmeal
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (1, 4, '商务套餐A', 20.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/21a5ed3a-97f6-447a-af9d-53deabfb5661.png', '2022-06-10 10:58:09', '2022-06-10 10:58:09');
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (2, 4, '商务套餐B', 22.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8d0075f8-9008-4390-94ca-2ca631440304.png', '2022-06-10 11:00:13', '2022-06-10 11:11:37');
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (3, 3, '人气套餐A', 49.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8979566b-0e17-462b-81d8-8dbace4138f4.png', '2022-06-10 11:11:23', '2022-06-10 11:11:23');
-- setmeal_dish
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (1, 1, 1, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (2, 1, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (3, 1, 11, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (4, 2, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (5, 2, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (6, 2, 9, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (7, 3, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (8, 3, 6, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (9, 3, 5, 1);
-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
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元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
select d.name,d.price,c.name,d.status '状态'
from dish d left join category c on d.category_id = c.id where d.price>=10 and d.price<=50 and c.status =1 ;
-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
select c.name,max(d.price)
from dish d ,category c where d.category_id = c.id group by d.category_id;
-- 4. 查询各个分类下 状态为 '起售' , 并且 该分类(进行分组)下菜品总数量大于等于3 的 分类名称 .
select count(*),c.name from dish d, category c where d.category_id = c.id and c.status = 1 group by d.category_id having count(*) >= 3;
-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）.
select id
from setmeal where name = '商务套餐A';
select d.name, d.price, s.name, s.price, sd.copies
from dish d,
setmeal_dish sd,
setmeal s
where d.id = sd.dish_id
and s.id = sd.setmeal_id
and s.name = ' 商务套餐A';
-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
select d.name,d.price
from dish d where d.price<(select avg(dd.price) from dish dd);
use db08_homework;
-- 部门管理
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 (id, name, create_time, update_time)
values(1,'学工部',now(),now()),
(2,'教研部',now(),now()),
(3,'咨询部',now(),now()),
(4,'就业部',now(),now()),
(5,'人事部',now(),now());
-- 员工管理
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 教研主管, 5 咨询师',
entrydate date comment '入职时间',
dept_id int unsigned comment '部门ID',
salary int unsigned comment '月工资',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate,dept_id,salary, create_time, update_time) VALUES
(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,20000,now(),now()),
(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,18000,now(),now()),
(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,16800,now(),now()),
(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,12000,now(),now()),
(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,9000,now(),now()),
(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,6000,now(),now()),
(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,6500,now(),now()),
(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,7200,now(),now()),
(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,5300,now(),now()),
(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,12000,now(),now()),
(11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,8900,now(),now()),
(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,7800,now(),now()),
(13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,6800,now(),now()),
(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,15800,now(),now()),
(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,11500,now(),now()),
(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,8300,now(),now()),
(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,4500,now(),now());
-- 薪资等级表
create table salgrade(
grade int comment '等级',
losal int comment '该等级最低薪资',
hisal int comment '该等级最高薪资'
) comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);
-- 1. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
select tb_dept.name,tb_emp.name
from tb_emp,tb_dept where tb_dept.id = tb_emp.dept_id;
-- 2. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
select tb_dept.name,tb_emp.name
from tb_emp join tb_dept on tb_dept.id = tb_emp.dept_id;
-- 3. 查询员工的 姓名、性别、职位、部门名称 （隐式内连接）
select tb_emp.name,
if(tb_emp.gender = 1, '男', '女') '性别',
(case tb_emp.job when 1 then '班主任' when 2 then '讲师' when 1 then '学工主管' when 1 then '教研主管' else 'null' end) '职业',
tb_dept.name
from tb_dept,
tb_emp
where tb_dept.id = tb_emp.dept_id;
-- 4. 查询 薪资 高于 10000 的员工的姓名、性别、职位、部门名称（显式内连接）
select tb_emp.name,
if(tb_emp.gender = 1, '男', '女') '性别',
(case tb_emp.job when 1 then '班主任' when 2 then '讲师' when 1 then '学工主管' when 1 then '教研主管' else 'null' end) '职业',
tb_dept.name,tb_emp.salary
from tb_dept
join tb_emp on tb_dept.id = tb_emp.dept_id and salary >= 10000;
-- 5. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
select tb_emp.name ,td.name
from tb_emp left join tb_dept td on tb_emp.dept_id = td.id;
-- 6. 查询员工表 所有 员工的姓名, 和对应的部门名称 (右外连接)
select tb_emp.name ,td.name
from tb_dept td right join tb_emp on tb_emp.dept_id = td.id;
-- 7. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
select tb_emp.name,tb_dept.name from tb_dept right join tb_emp on tb_dept.id = tb_emp.dept_id;
-- 8. 查询 "教研部" 的所有员工信息 （标量子查询）
-- 查询教研部对应的id
select id from tb_dept where name = '教研部';
-- job为2 的部门信息
select * from tb_emp where dept_id = 2;
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
-- 9. 查询在 "方东白" 入职之后的员工信息 （标量子查询）
-- 先查询 '方东白' 的入职日期;
select entrydate from tb_emp where name = '方东白';
-- 查询上边入职日期之后的员工信息
select * from tb_emp where entrydate >='2012-11-01';
select * from tb_emp where entrydate >=(select entrydate from tb_emp where name = '方东白');
-- 10. 查询 "教研部" 和 "咨询部" 的所有员工信息 （列子查询）
-- 先查询 '教研部' '咨询部' 的 id
select id from tb_dept where name = '教研部' or name = '咨询部';
-- 查询 23的所有员工信息
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 = '咨询部');
-- 11. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 （行子查询）
-- 查询'韦一笑' 的入职日期 和 职位
select entrydate,job from tb_emp where name = '韦一笑';
-- 查询 2007-01-01 job为 2 的员工信息
select * from tb_emp where entrydate = '2007-01-01' and job = 2;
select * from tb_emp where (entrydate,job ) = (select entrydate,job from tb_emp where name = '韦一笑');
-- 12. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息 （表子查询）
select * from tb_emp ,tb_dept where tb_emp.dept_id=tb_dept.id and entrydate>='2006-01-01';
-- 13. 查询 拥有员工的 部门ID、部门名称 (没有员工的部门无需展示)
select tb_emp.name, tb_dept.id,tb_dept.name from tb_emp ,tb_dept where tb_dept.id = tb_emp.dept_id ;
-- 14. 查询所有 在 2010-01-01 之后入职的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
-- 左外连接
select tb_emp.*, td.name
from tb_emp
left join tb_dept td on tb_emp.dept_id = td.id
having tb_emp.entrydate >= '2010-01-01';
-- 15. 查询 "教研部" 员工的平均工资
-- 查出教研部的id
select id from tb_dept where name = '教研部';
-- 查询 员工所有 职业的 的平均工资.
select dept_id, avg(salary) from tb_emp group by dept_id ;
-- 查询ID = 2 的平均工资
select dept_id, avg(salary) from tb_emp where dept_id = 2 group by dept_id ;
-- 16. 查询工资比 "俞莲舟" 高的员工信息。
-- 先查 俞莲舟的工资
select salary from tb_emp where name = '俞莲舟';
--
select id, username, password, name, gender, image, job, entrydate, dept_id, salary, create_time, update_time
from tb_emp where salary >=(select salary from tb_emp where name = '俞莲舟');
-- 17. 查询 工资 比该企业员工的平均薪资 还要高的员工信息
-- 先求出员工的平均工资
select avg(salary) from tb_emp ;
select * from tb_emp where salary >= 10376.4706;
select * from tb_emp where salary >= (select avg(salary) from tb_emp);
-- 18. 查询所有的部门信息, 并统计部门的员工人数
select (case tb_emp.dept_id when 1 then '学工部' when 2 then '教研部'
when 3 then '咨询部' when 4 then '就业部' when 5 then '人事部' else 'null' end ) '部门' , count(tb_emp.id) from tb_dept left join tb_emp on
tb_emp.dept_id = tb_dept.id group by tb_emp.dept_id;
-- 19. 查询所有员工的 姓名, 工资 , 及 工资等级 (有难度的哦)
select tb_emp.name, tb_emp.salary, salgrade.grade
from tb_emp,
salgrade
where tb_emp.salary >= salgrade.losal
and tb_emp.salary <= salgrade.hisal;
-- 20. 查询 "教研部" 所有员工的信息 及 工资等级 (有难度的哦)
-- 查询教研部的id
select id from tb_dept where name = '教研部';
select tb_emp.*, salgrade.grade
from tb_emp,
salgrade,
tb_dept
where tb_emp.salary >= salgrade.losal
and tb_emp.salary <= salgrade.hisal
and tb_emp.dept_id = tb_dept.id
and tb_emp.dept_id = (select id from tb_dept where name = '教研部');
-- 21. 查询 工资 低于 本部门平均工资的员工信息 (有难度的哦)
-- 先查询每个部门的平均工资
select tb_dept.name, avg(tb_emp.salary) from tb_dept, tb_emp where tb_dept.id = tb_emp.dept_id group by tb_dept.name;
select id from tb_dept where name in ('学工部','教研部','咨询部','就业部','人事部');
select tb_emp.* from tb_dept,tb_emp where tb_emp.dept_id = tb_dept.id and tb_emp.salary <=(select avg(salary) from
tb_emp where dept_id in (1,2,3,4,5));
select tb_emp.*
from tb_emp
where salary < (select avg(salary)
from tb_emp,
tb_dept
where tb_emp.dept_id = tb_dept.id
and
tb_emp.dept_id);
select id,
username,
password,
name,
gender,
image,
job,
entrydate,
dept_id,
salary,
create_time,
update_time
from tb_emp
where tb_emp.salary <= (select avg(e2.salary)
from tb_emp e2
where e2.dept_id = tb_emp.dept_id)
;
-- 分类表
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 '套餐菜品中间表';
-- ================================== 导入测试数据 ====================================
-- category
insert into category (id, type, name, sort, status, create_time, update_time) values (1, 1, '酒水饮料', 10, 1, '2022-08-09 22:09:18', '2022-08-09 22:09:18');
insert into category (id, type, name, sort, status, create_time, update_time) values (2, 1, '传统主食', 9, 1, '2022-08-09 22:09:32', '2022-08-09 22:18:53');
insert into category (id, type, name, sort, status, create_time, update_time) values (3, 2, '人气套餐', 12, 1, '2022-08-09 22:11:38', '2022-08-10 11:04:40');
insert into category (id, type, name, sort, status, create_time, update_time) values (4, 2, '商务套餐', 13, 1, '2022-08-09 22:14:10', '2022-08-10 11:04:48');
insert into category (id, type, name, sort, status, create_time, update_time) values (5, 1, '经典川菜', 6, 1, '2022-08-09 22:17:42', '2022-08-09 22:17:42');
insert into category (id, type, name, sort, status, create_time, update_time) values (6, 1, '新鲜时蔬', 7, 1, '2022-08-09 22:18:12', '2022-08-09 22:18:28');
insert into category (id, type, name, sort, status, create_time, update_time) values (7, 1, '汤类', 11, 1, '2022-08-10 10:51:47', '2022-08-10 10:51:47');
-- dish
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (1,'王老吉', 1, 6.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/41bfcacf-7ad4-4927-8b26-df366553a94c.png', '', 1, '2022-06-09 22:40:47', '2022-06-09 22:40:47');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (2,'北冰洋', 1, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4451d4be-89a2-4939-9c69-3a87151cb979.png', '还是小时候的味道', 1, '2022-06-10 09:18:49', '2022-06-10 09:18:49');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (3,'雪花啤酒', 1, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/bf8cbfc1-04d2-40e8-9826-061ee41ab87c.png', '', 1, '2022-06-10 09:22:54', '2022-06-10 09:22:54');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (4,'米饭', 2, 2.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/76752350-2121-44d2-b477-10791c23a8ec.png', '精选五常大米', 1, '2022-06-10 09:30:17', '2022-06-10 09:30:17');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (5,'馒头', 2, 1.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/475cc599-8661-4899-8f9e-121dd8ef7d02.png', '优质面粉', 1, '2022-06-10 09:34:28', '2022-06-10 09:34:28');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (6,'老坛酸菜鱼', 5, 56.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4a9cefba-6a74-467e-9fde-6e687ea725d7.png', '原料：汤，草鱼，酸菜', 1, '2022-06-10 09:40:51', '2022-06-10 09:40:51');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (7,'经典酸菜鮰鱼', 5, 66.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/5260ff39-986c-4a97-8850-2ec8c7583efc.png', '原料：酸菜，江团，鮰鱼', 1, '2022-06-10 09:46:02', '2022-06-10 09:46:02');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (8,'蜀味水煮草鱼', 5, 38.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a6953d5a-4c18-4b30-9319-4926ee77261f.png', '原料：草鱼，汤', 1, '2022-06-10 09:48:37', '2022-06-10 09:48:37');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (9,'清炒小油菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/3613d38e-5614-41c2-90ed-ff175bf50716.png', '原料：小油菜', 1, '2022-06-10 09:51:46', '2022-06-10 09:51:46');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (10,'蒜蓉娃娃菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4879ed66-3860-4b28-ba14-306ac025fdec.png', '原料：蒜，娃娃菜', 1, '2022-06-10 09:53:37', '2022-06-10 09:53:37');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (11,'清炒西兰花', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/e9ec4ba4-4b22-4fc8-9be0-4946e6aeb937.png', '原料：西兰花', 1, '2022-06-10 09:55:44', '2022-06-10 09:55:44');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (12,'炝炒圆白菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/22f59feb-0d44-430e-a6cd-6a49f27453ca.png', '原料：圆白菜', 1, '2022-06-10 09:58:35', '2022-06-10 09:58:35');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (13,'清蒸鲈鱼', 5, 98.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c18b5c67-3b71-466c-a75a-e63c6449f21c.png', '原料：鲈鱼', 1, '2022-06-10 10:12:28', '2022-06-10 10:12:28');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (14,'东坡肘子', 5, 138.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a80a4b8c-c93e-4f43-ac8a-856b0d5cc451.png', '原料：猪肘棒', 1, '2022-06-10 10:24:03', '2022-06-10 10:24:03');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (15,'梅菜扣肉', 5, 58.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/6080b118-e30a-4577-aab4-45042e3f88be.png', '原料：猪肉，梅菜', 1, '2022-06-10 10:26:03', '2022-06-10 10:26:03');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (16,'剁椒鱼头', 5, 66.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/13da832f-ef2c-484d-8370-5934a1045a06.png', '原料：鲢鱼，剁椒', 1, '2022-06-10 10:28:54', '2022-06-10 10:28:54');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (17,'馋嘴牛蛙', 5, 98.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/7a55b845-1f2b-41fa-9486-76d187ee9ee1.png', '配料：鲜活牛蛙，丝瓜，黄豆芽', 1, '2022-06-10 10:37:52', '2022-06-10 10:37:52');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (18,'鸡蛋汤', 7, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c09a0ee8-9d19-428d-81b9-746221824113.png', '配料：鸡蛋，紫菜', 1, '2022-06-10 10:54:25', '2022-06-10 10:54:25');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (19,'平菇豆腐汤', 7, 6.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/16d0a3d6-2253-4cfc-9b49-bf7bd9eb2ad2.png', '配料：豆腐，平菇', 1, '2022-06-10 10:55:02', '2022-06-10 10:55:02');
-- setmeal
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (1, 4, '商务套餐A', 20.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/21a5ed3a-97f6-447a-af9d-53deabfb5661.png', '2022-06-10 10:58:09', '2022-06-10 10:58:09');
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (2, 4, '商务套餐B', 22.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8d0075f8-9008-4390-94ca-2ca631440304.png', '2022-06-10 11:00:13', '2022-06-10 11:11:37');
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (3, 3, '人气套餐A', 49.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8979566b-0e17-462b-81d8-8dbace4138f4.png', '2022-06-10 11:11:23', '2022-06-10 11:11:23');
-- setmeal_dish
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (1, 1, 1, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (2, 1, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (3, 1, 11, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (4, 2, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (5, 2, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (6, 2, 9, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (7, 3, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (8, 3, 6, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (9, 3, 5, 1);
-- 1. 查询价格低于 20元 且 状态为 '起售' 的菜品的名称 、价格 及其 菜品的分类名称 .
select dish.name, dish.price, category.name
from dish,
category
where dish.category_id = category.id
and dish.status = 1
and dish.price <= 20;
-- 2. 查询所有价格在 40元(含)到100元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称
-- (即使菜品没有分类 , 也需要将菜品查询出来).
select dish.name, dish.price, dish.status, category.name
from dish left join category
on dish.category_id = category.id
and dish.status = 1
where dish.price >= 40 and dish.price <= 100;
-- 3. 查询每个分类下最便宜的菜品, 展示出分类的名称、最便宜的菜品的价格 .
select c.name, min(d.price)
from dish d ,category c where d.category_id = c.id group by d.category_id;
-- 4. 查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于2 的 分类名称 .
select c.name , count(*)
from category c,
dish d
where d.category_id = c.id
and d.status = 1
group by d.category_id having count(*)>=2 ;
-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 套餐包含的菜品名称、价格、份数）.
-- 查询 '商务套餐A'的编号
select id
from setmeal where name = '商务套餐A';
-- 查询 setmeal ID为1 的菜品 id
select dish_id from setmeal_dish where setmeal_id = 1;
-- 查询编号为1 4 11 的菜品信息
select dish.name, dish.price, setmeal.name,setmeal.price, setmeal_dish.copies
from dish,
setmeal,
setmeal_dish
where dish.id = setmeal_dish.dish_id
and setmeal.id
and setmeal.name = '商务套餐A';
select d.name, d.price, s.name, s.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. 查询哪些套餐中包含的有 "米饭" (展示出套餐名称、套餐价格、套餐分类名称).
-- 先查出 米饭对应的id
select id
from dish where name = '米饭';
-- sd表中根据有dish_id 查出对应的 setmeal_id
select setmeal_id from setmeal_dish where dish_id = 4;
-- 在套餐表中 根据id 1 2 查出 category_id对应的套餐
select category_id from setmeal where id = 1 or id = 2;
-- id知道4 在套餐表中查分类
select category.name, setmeal.name, setmeal.price
from category,
setmeal
where category.id = setmeal.category_id
and category.id = 4;
select category.name, setmeal.name, setmeal.price
from category,
setmeal
where category.id = setmeal.category_id
and category.id in (select category_id
from setmeal
where id in (select setmeal_id
from setmeal_dish
where dish_id = (select id from dish where name = '米饭')));
-- 7. 查询出 低于 菜品平均价格的菜品信息 (展示出菜品名称、菜品价格、菜品分类名称).
select dish.name,dish.price
from dish where dish.price<(select avg(dish.price) from dish);
-- 8. 查询出与 "剁椒鱼头" 的分类 及 价格都相同的菜品数据 .
select price
from dish
where name = '剁椒鱼头';
select dish.category_id
from dish where dish.name = '剁椒鱼头';
select dish.*
from dish,
category
where dish.id = category.id
and dish.category_id = (select dish.category_id
from dish
where dish.name = '剁椒鱼头')
and dish.price = (select price
from dish
where name = '剁椒鱼头');
-- 部门管理
create table 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 dept (id, name, create_time, update_time) values(1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()), (4,'就业部',now(),now()),(5,'人事部',now(),now());
-- 员工管理
create table 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 教研主管, 5 咨询师',
entrydate date comment '入职时间',
dept_id int unsigned comment '部门ID',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
INSERT INTO emp
(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
(11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()),
(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()),
(13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()),
(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',2,now(),now()),
(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());
delete from emp where id = 17 ;
insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)
values ('zhangsansan','张三三',1,'www.baidu.com',2,now(),4,now(),now());
delete from emp where id = 19 ;
# 修改员工
update emp set username = 'lisi',name = '李四' ,gender = 1,image = 'www.baidu.com',job = 1,entrydate =now(),dept_id = 1 , update_time = now() where id = 20;
update emp set username = 'rrrrr',name = 'wewe' ,gender = 1,image = 'wwwwwwww',job = 1,entrydate =now(),dept_id =4 , update_time = now() where id =17;
select * from emp where id = 21 ;
# 根据多个条件查询
select * from emp where name like '%张%' and gender = 1 and entrydate >='2000-01-01' and entrydate<= '2024-01-01';
select * from emp where name like concat ('%','张','%') and gender = 1 and entrydate >='2000-01-01' and entrydate<= '2024-01-01';
use db10_hw;
-- 学员表
create table student(
id int unsigned primary key auto_increment comment 'ID,主键',
name varchar(10) not null comment '姓名',
no char(10) not null unique comment '学号',
gender tinyint unsigned not null comment '性别, 1: 男, 2: 女',
phone varchar(11) not null unique comment '手机号',
degree tinyint unsigned comment '最高学历, 1:初中, 2:高中, 3:大专, 4:本科, 5:硕士, 6:博士',
violation_count tinyint unsigned not null default 0 comment '违纪次数',
violation_score tinyint unsigned not null default 0 comment '违纪扣分',
class_id int unsigned not null comment '班级ID, 关联班级表ID',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '学员表';
insert into student(name, no, gender, phone, degree, class_id, create_time, update_time)VALUES
('段誉','2022000001',1,'18800000001',1,1,now(),now()),
('萧峰','2022000002',1,'18800210003',2,1,now(),now()),
('虚竹','2022000003',1,'18800013001',2,1,now(),now()),
('萧远山','2022000004',1,'18800003211',3,1,now(),now()),
('阿朱','2022000005',2,'18800160002',4,1,now(),now()),
('阿紫','2022000006',2,'18800000034',4,2,now(),now()),
('游坦之','2022000007',1,'18800000067',4,2,now(),now()),
('康敏','2022000008',2,'18800000077',5,2,now(),now()),
('徐长老','2022000009',1,'18800000341',3,2,now(),now()),
('云中鹤','2022000010',1,'18800006571',2,2,now(),now()),
('钟万仇','2022000011',1,'18800000391',4,3,now(),now()),
('崔百泉','2022000012',1,'18800000781',4,3,now(),now()),
('耶律洪基','2022000013',1,'18800008901',4,3,now(),now()),
('天山童姥','2022000014',2,'18800009201',4,3,now(),now()),
('刘竹庄','2022000015',1,'18800009401',3,4,now(),now()),
('李春来','2022000016',1,'18800008501',4,4,now(),now()),
('王语嫣','2022000017',2,'18800007601',2,4,now(),now());
# 根据分页查询
select * from student limit 0,5;
# 根据id查询
select * from student where id = 1;
/*新增*/
insert into student (name, no, gender, phone, degree, class_id, create_time, update_time) values ('段王','2023000011',1,'18009091214',4,2,now(),now());
# 根据id 修改信息 修改学生信息 需要根据id修改
update student set name= '段王爷',no = '2023000013',gender = 1,phone = '18009121214',degree = 4,class_id =2 ,violation_count =1 ,violation_score = 10 where id =22;
扫一扫
915
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
