mysql 查询练习题
mysql 查询练习题
1.单表查询练习
1.1、准备数据
-- 创建数据库
crate database kdftest;
-- 选择数据库
user kdftest;
-- ----------------------------
-- 创建商品表
-- ----------------------------
drop table if exists goods;
create table goods (
id int(11) not null primary key auto_increment comment'商品编号',
type varchar(30) not null comment '商品类型',
name varchar(20) unique comment '商品名称',
price decimal(7,2) comment '商品价格',
num int(11) default 0 comment'商品库存',
add_time datetime comment '添加时间'
);
-- 添加商品表数据
INSERT INTO goods(id,type,name,price,num,add_time)
VALUES(1,'书籍','西游记',50.4,20,'2018-01-01 13:40:40'),
(2,'糖类','牛奶糖',7.5,200,'2018-02-02 13:40:40'),
(3,'糖类','水果糖',2.5,100,null),
(4,'服饰','休闲西服',800,null,'2018-04-04 13:40:40'),
(5,'饮品','果汁',3,70,'2018-05-05 13:40:40'),
(6,'书籍','东游记',50.4,200,'2018-09-01 14:40:40')，
(7,'书籍','水浒传',50.4,200,'2018-09-01 14:40:40');
1.2、查询的语法
-- 查询的语法 （*叫做通配符）
select *|字段 from 表名
[where 查询条件
group by 分组字段
having 过滤（筛选）条件
order by 排序字段 asc|desc
limit 分页条件]
1.3、聚合函数
聚合函数通常和group by一起使用
| 函数 | 作用 |
|---|---|
| sum() | 求多个数值和 |
| avg() | 请平均数 |
| count() | 统计记录数 |
| max() | 求最大值 |
| min() | 求最小值 |
1.4、单表查询练习题
-- 1、将name字段修改为 bookname
alter table goods change name bookname varchar(20) not null;
-- 2、添加一个创建人 creat_at字段
alter table goods add creat_at varchar(20) not null;
-- 3、查询所有数据，按价格由高到低显示。
select * from goods order by price desc;
-- 4、查询 商品类型，名称，价格，创建人
select type,bookname,price,creat_at from goods;
-- 5、统计商品中糖类有集中
select type,count(*) from goods group by type;
-- 6、输出糖类的最高价和最低价
select type,max(price),min(price) from goods where type="糖类" ;
-- 7、求所有商品价格的总和
select sum（price) from goods;
-- 8、求商品的平均价格
select avg（price) from goods;
-- 9、求饮品比服饰的价格低多少(自连接查询)
select sum(t2.price)-sum(t1.price) from (select price from goods where type="饮品") t1
inner join (select price from goods where type="服饰") t2 ;
-- 10、统计商品信息表中有几类商品（去重统计）
select count(distinct type) from goods ;
-- 11、查询 id 为 1,2,3 的数据
-- 方法1
select * from goods where id in(1,2,3);
-- 方法2
select * from goods where id in =1 or id=2 or id=3;
-- 方法3
select * from goods where id between 1 and 3;
-- 12、查询 日期 在2018-01-01 13:40:40 和 2018-05-05 13:40:40 范围内的数据 （between and ）
select * from goods where add_time between "2018-01-01 13:40:40" and "2018-05-05 13:40:40"
-- 13、显示价格较高的前5条数据
select * from goods order by price desc limit 0,5;
-- 14、查询商品表的记录，按库存升序排列按价格降序排列
select * from goods order by num asc, price desc;
-- 15、 查询 添加事件为 null 的记录
select * from goods where add_time is null;
-- 16、查询商品数量超过2个的商品的类别,按照数量由多到少降序排列
select type,count(type) from goods group by type having count(type)>2 order by count(type) desc;
-- 17、查询商品名称 以“记”结尾的商品信息
select * from goods where name like "%记";
-- 18、查询商品名称中 包含“游”字的商品信息
select * from goods where name like "%游%";
2.多表查询练习
2.1、准备数据
-- ---------------------------多表练习准备数据---------------------------------------
-- ----------------------------
-- 创建部门表
-- ----------------------------
DROP TABLE IF EXISTS `tb_dept`;
CREATE TABLE `tb_dept` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '部门编号',
`dept_name` varchar(20) NOT NULL COMMENT '部门名称',
`dept_desc` varchar(200) DEFAULT NULL COMMENT '部门简介',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- 创建员工表
-- ----------------------------
DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '员工编号',
`emp_name` varchar(20) NOT NULL COMMENT '员工姓名',
`emp_card` varchar(18) DEFAULT NULL COMMENT '身份证号',
`emp_sex` varchar(2) DEFAULT NULL COMMENT '员工性别',
`emp_dept` int NOT NULL COMMENT '员工部门',
`emp_birthday` date NOT NULL COMMENT '员工生日',
`emp_email` varchar(20) DEFAULT NULL COMMENT '员工邮箱',
`emp_phone` varchar(11) DEFAULT NULL COMMENT '员工电话',
`emp_status` enum('在职','离职','试用期') DEFAULT NULL COMMENT '员工状态',
PRIMARY KEY (`id`),
KEY `fk_emp_dept` (`emp_dept`),
CONSTRAINT `fk_emp_dept` FOREIGN KEY (`emp_dept`) REFERENCES `tb_emp` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- 创建请假表
-- ----------------------------
DROP TABLE IF EXISTS `tb_leave`;
CREATE TABLE `tb_leave` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '请假编号',
`emp_id` int NOT NULL COMMENT '员工编号',
`type` enum('事假','病假') NOT NULL,
`start_date` datetime DEFAULT NULL COMMENT '开始时间',
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
`detail` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '请假事由',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 添加部门表数据
INSERT INTO `tb_dept` VALUES ('1', '研发部', '负责公司的产品研发工作');
INSERT INTO `tb_dept` VALUES ('2', '市场部', '负责市场推广和客户关系管理');
INSERT INTO `tb_dept` VALUES ('3', '销售部', '负责销售和客户关系维护');
INSERT INTO `tb_dept` VALUES ('4', '人力资源部', '负责招聘、培训和员工关系管理');
INSERT INTO `tb_dept` VALUES ('5', '财务部', '负责公司的财务管理和报表制作');
-- 添加员工表数据
-- ----------------------------
INSERT INTO `tb_emp` VALUES ('1', '张三', '440102200001010001', '男', '1', '2000-01-01', 'zhangsan@a.com', '1234567890', '在职');
INSERT INTO `tb_emp` VALUES ('2', '李四', '440102200001010002', '男', '2', '1990-02-02', 'lisi@a.com', '9876543210', '离职');
INSERT INTO `tb_emp` VALUES ('3', '王五', '440102200001010003', '男', '3', '1995-03-03', 'wangwu@a.com', '5555555555', '在职');
INSERT INTO `tb_emp` VALUES ('4', '赵六', '440102200001010004', '女', '4', '2005-04-04', 'zhaoliu@a.com', '9999999999', '试用期');
INSERT INTO `tb_emp` VALUES ('5', '孙七', '440102200001010005', '女', '5', '1985-05-05', 'sunqi@a.com', '7777777777', '在职');
INSERT INTO `tb_emp` VALUES ('6', '周八', '440102200001010006', '女', '3', '2015-06-06', 'zhouba@a.com', '3333333333', '离职');
INSERT INTO `tb_emp` VALUES ('7', '吴九', '440102200001010007', '女', '5', '2025-07-07', 'wujiu@a.com', '6666666666', '在职');
INSERT INTO `tb_emp` VALUES ('8', '小明', '440102200001010007', '女', '5', '2025-07-07', 'xiaomin@a.com', '6666666666', '在职');
INSERT INTO `tb_emp` VALUES ('9', '杰克', '440102200001010007', '男', '5', '2025-07-07', 'jieke@a.com', '6666666666', '在职');
INSERT INTO `tb_emp` VALUES ('10', '张明', '440102200001010007', null, '5', '2025-07-07', 'zhangmi@a.com', '6666666666', '在职');
-- 添加请假表数据
INSERT INTO `tb_leave` VALUES ('1', '1', '病假', '2023-05-01 09:00:00', '2023-05-03 17:00:00', '因病请假两天');
INSERT INTO `tb_leave` VALUES ('2', '2', '事假', '2023-05-15 14:00:00', '2023-05-16 09:00:00', '因家庭原因请假一天');
INSERT INTO `tb_leave` VALUES ('3', '3', '事假', '2023-05-22 18:00:00', '2023-05-24 12:00:00', '因个人原因请假两天');
INSERT INTO `tb_leave` VALUES ('4', '1', '病假', '2023-06-05 11:00:00', '2023-06-07 16:00:00', '因病请假两天');
INSERT INTO `tb_leave` VALUES ('5', '2', '事假', '2023-06-12 19:00:30', '2023-06-16 14:30:38', '因家庭原因请假两天');
INSERT INTO `tb_leave` VALUES ('6', '3', '事假', '2023-06-19 17:45:15', '2023-06-21 11:59:59', '因个人原因请假两天半');
INSERT INTO `tb_leave` VALUES ('7', '1', '病假', '2023-07-01 14:35:55', '2023-07-03 18:45:15', '因病请假两天');
INSERT INTO `tb_leave` VALUES ('8', '7', '事假', '2023-07-15 16:45:35', '2023-07-19 19:34:45', '因家庭原因请假两天');
INSERT INTO `tb_leave` VALUES ('9', '8', '事假', '2023-07-28 18:45:45', '2023-07-31 16:45:45', '因个人原因请假三天');
INSERT INTO `tb_leave` VALUES ('10', '9', '事假', '2023-08-19 19:45:45', '2023-08-21 19:45:45', '因病请假两天');
-- 多表连接查询
-- 内连接
-- 显示内连接
-- select t1.字段，t2.字段 from 表1 t1 inner join 表2 t2 on 连接条件
-- 隐示内连接
-- select t1.字段，t2.字段 from 表1 t1 , 表2 t2 where 连接条件
-- 外连接 left | right join
-- select t1.字段，t2.字段 from 表1 t1 left|reight join 表2 on 连接条件
-- ------------------------------单表查询复习-------------------------------------------------------
-- 1、查询 员工表的所有数据
select * from tb_emp;
-- 2、查询 指定列的数据，查询员工表中的员工姓名、和邮箱两列。
select emp_name,emp_email from tb_emp;
-- 3、查询 张三的邮箱地址
select emp_name,emp_email from tb_emp where emp_name="张三";
-- 4、查询 员工编号1到5 的数据
-- 方法1
select * from tb_emp where id in(1,2,3,4,5) ;
-- 方法2
select * from tb_emp where id between 1 and 5;
-- 4、查询 员工编号 不是 1到5 的数据
select * from tb_emp where id not between 1 and 5;
-- 5、查询 姓张的员工的信息
select * from tb_emp where emp_name like "张%";
-- 6、查询 姓名包含“三”字的员工的信息
select * from tb_emp where emp_name like "%三%";
-- 7、使用聚合函数查询员工总人数
select count(*) from tb_emp;
-- 8、使用聚合函数查询在职的员工人数
select count(*) from tb_emp where emp_status="在职";
-- --------------------------------多表连接查询-----------------------------------------------
-- 9、使用聚合函数查询张三的请假次数
-- 查询 张三对应的id
select id from tb_emp where emp_name="张三";
-- 通过 id查询次数
select count(*) from tb_leave where emp_id = (select id from tb_emp where emp_name="张三");s
-- 连接查询
select count(*) from tb_leave,tb_emp e where emp_id = e.id and e.emp_name="张三";
-- 10、使用聚合函数查询财务部男女员工的人数
select emp_sex, count(*) from tb_dept d,tb_emp e where emp_dept = d.id and d.dept_name="财务部" group by emp_sex;
-- 11、查询 销售部 员工的姓名、电话、身份证号和邮箱地址
-- ----------------------------使用子查询的方式-------------------------------
-- 查询销售部的部门id
select id from tb_dept where dept_name = "销售部";
-- 将部门id带入到员工表中
select emp_name,emp_phone,emp_card,emp_email from tb_emp where emp_dept=(select id from tb_dept where dept_name = "销售部");
-- ----------------------------使用连接查询的方式-------------------------------
select emp_name,emp_phone,emp_card,emp_email from tb_emp,tb_dept d where emp_dept = d.id and dept_name = "销售部"
-- 12、查询 在研发部在职员工的姓名和电话
select emp_name,emp_phone from tb_emp,tb_dept d where emp_dept = d.id and dept_name = "研发部" and emp_status="在职";
-- 13、查询张三请假的次数
select emp_name, count(*) from tb_leave l,tb_emp e where l.emp_id=e.id and emp_name="张三"
-- 14、查询张三请病假的次数
select emp_name ,count(*) from tb_emp e,tb_leave l where e.id = l.emp_id and emp_name="张三" and type="病假" ;
-- 15、查询研发部员工请假的情况，输出请假的员工的部门，姓名，电话，请假类型
select dept_name,emp_name,emp_phone,type from tb_dept d INNER JOIN tb_emp e on d.id = e.emp_dept
inner join tb_leave l on e.id = l.emp_id and dept_name="研发部"
-- 16、使用左外连接查询每个部门对应的员工的姓名，电话，邮箱地址
select dept_name,emp_name,emp_phone,emp_email from tb_dept d left join tb_emp e on d.id = e.emp_dept
-- 17、查询请假超过三次的员工的姓名和对应的部门
select emp_name,dept_name,count(*) from tb_leave l inner join tb_emp e on l.emp_id = e.id inner join tb_dept d
on d.id = e.emp_dept group by emp_name,dept_name HAVING count(*) >3
-- 18、查询请假天数超过3天的员工姓名，请假天数
-- DATEDIFF(expr1,expr2)函数 计算两个日期之间的时间差
select emp_name ,DATEDIFF(end_time,start_date) from tb_leave l inner join tb_emp e on l.emp_id = e.id and DATEDIFF(end_time,start_date)>2;
-- 19、查询没有请过假的员工的姓名
select emp_name from tb_emp where id not in (select DISTINCT emp_id from tb_leave)
-- 21、查询请过假的员工的姓名
-- 1.在请假表中查询请过假的员工的id
select DISTINCT emp_id from tb_leave;
-- 2.通过id 查询到请假的员工的姓名
select emp_name from tb_emp where id in (select DISTINCT emp_id from tb_leave);
-- 23、查询没有请过假的员工所在的部门
select distinct dept_name from tb_emp e ,tb_dept d where e.id in (select DISTINCT emp_id from tb_leave) AND e.emp_dept = d.id;
-- 24、查询哪个部门下的全部员工从来没有请过假
-- 1.查询请过假的员工的id
select DISTINCT emp_id from tb_leave;
-- 2.查询请过假的员工所在的部门
select emp_dept from tb_emp where id in(select DISTINCT emp_id from tb_leave);
-- 3.组合条件
select dept_name from tb_dept where id not in (select emp_dept from tb_emp where id in(select DISTINCT emp_id from tb_leave));
-- 25、使用聚合函数统计在职的每个员工姓名和请假的次数，显示前5条数据，按照请假次数倒序排列
select emp_name ,count(*) from tb_leave l,tb_emp e where l.emp_id = e.id and emp_status = "在职"
group by emp_id order by count(*) desc limit 0,5;
-- 26、查询每个部门的请假情况，要求输出部门名称， 请假人，请假次数
select dept_name,emp_name,count(*) from tb_dept d left join tb_emp e on d.id = e.emp_dept left join tb_leave l on l.emp_id=e.id group by dept_name,emp_name;
3、mysql知识链接
-- 建表语法
create table 表名(
字段1 数据类型(长度) 约束条件 comment '注释',
字段2 数据类型(长度) 约束条件 comment '注释'
);
-- 常用数据类型
整型 int
字符 varchar 、char、text
浮点型 float、double、decimal(m,n)
日期 date
日期时间 datetime
枚举 enum
-- 约束条件
主键约束 primary key 唯一标识一条记录
自增约束 auto_increment
唯一约束 unique 设置该字段的值不能重复
非空约束 not null 设置该字段的值必须要添加
默认约束 default '默认值' 设置默认值
外键约束 foreign key 保证数据的完整性
-- 修改表的字段
alter table 表名 change 旧字段 新字段 类型
-- 添加字段
alter table 表名 add 字段 类型 约束条件
浙公网安备 33010602011771号