-- 创建员工管理表(带约束) 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 from tb_emp;系统可以自动显示表的所有字段 -- 推荐 select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp; -- 方法二。不推荐,不直观性能低 select *from tb_emp; -- 3. 查询所有员工的 name,entrydate, 并起别名(姓名、入职日期),起别名:字段名+as+单引号或者双引号,其中as可以省略 select name as "姓名",entrydate '入职日期' from tb_emp; -- 4. 查询已有的员工关联了哪几种职位(不要重复),distinct+字段名 select distinct job from tb_emp; -- =================== DQL: 条件查询 ====================== -- 1. 查询 姓名 为 杨逍 的员工 select *from tb_emp where name='杨逍'; -- 2. 查询 id小于等于5 的员工信息 select *from tb_emp where id<=5; -- 3. 查询 没有分配职位 的员工信息 select *from tb_emp where job is null ; -- 4. 查询 有职位 的员工信息 select *from tb_emp where job is not null ; -- 5. 查询 密码不等于 '123456' 的员工信息, 不等于用 != 可以用 <> -- 方法一 select *from tb_emp where password !='123456' ; -- 方法二 select *from tb_emp where password <>'123456' ; -- 6. 查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息 -- 方法一 select *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'(包含) 之间 且 性别为女 的员工信息 -- 并且可以用and ,也可以用&& select *from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender=2; -- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息 -- 方法一 select *from tb_emp where job =2 or job = 3 or job =4; -- 方法二 select *from tb_emp where job in (2,3,4); -- 9. 查询 姓名 为两个字的员工信息,用模糊查询, 用两个下划线代表两个字符 select *from tb_emp where name like '__'; -- 10. 查询 姓 '张' 的员工信息,模糊查询用like %代表多个字符 select *from tb_emp where name like '张%'; -- =================== DQL: 分组查询 ====================== -- 聚合函数 -- 1. 统计该企业员工数量 --count 统计的是非空字段个数 -- count(字段) select count(id) from tb_emp; -- 统计id 字段数据的总个数为29,表中一共有29条数据 -- count(常量) select count(1) from tb_emp; -- count(*),推荐 select count(*) from tb_emp; -- 统计表的总数据量为29 -- 2. 统计该企业员工 ID 的平均值 - avg select avg(id) from tb_emp; -- 15 -- 3. 统计该企业最早入职的员工 -min select min(entrydate) from tb_emp; -- 4. 统计该企业最迟入职的员工 -max select max(entrydate) from tb_emp; -- 5. 统计该企业员工的 ID 之和 -sum select sum(id) from tb_emp; -- 435 -- 分组 -- 1. 根据性别分组 , 统计男性和女性员工的数量 select gender,count(*) from tb_emp group by gender; -- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位 -- 查询入职时间在 '2015-01-01' (包含) 以前的员工 select * from tb_emp where entrydate <= '2015-01-01'; -- 查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 select job from tb_emp where entrydate <= '2015-01-01' group by job; -- 最后再获取员工数量大于等于2的职位,用到count(*) select job, count(*) -- 获取员工数量 from tb_emp where entrydate <= '2015-01-01' -- 分组前条件 group by job -- 按照job字段分组 having count(*) >= 2; -- 分组后条件,having是分组之后对结果进行过滤 -- where与having区别(面试题) -- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。 -- 判断条件不同:where不能对聚合函数count()进行判断,而having可以。 -- 注意事项: -- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义 -- 执行顺序:where > 聚合函数 > having -- =================== 排序查询 ====================== -- 1. 根据入职时间, 对员工进行升序排序 -asc, 默认是升序,所以asc可以省略 select * from tb_emp order by entrydate asc; -- 2. 根据入职时间, 对员工进行降序排序 -desc select * from tb_emp order by entrydate desc; -- 3. 根据 入职时间 对公司的员工进行 升序排序 , 入职时间相同 , 再按照 更新时间 进行降序排序 -- 多字段,只有当第一个字段相同时,才会根据第二个字段排序。entrydate相同 ,那就执行 update_time desc -- 有三个字段时,只有当前两个字段相同时,才会根据第三个字段排序 select * from tb_emp order by entrydate ,update_time desc; -- =================== 分页查询 ====================== -- select 字段列表 from 表名 limit 起始索引, 查询记录数 ; -- 1. 从起始索引0开始查询员工数据, 每页展示5条记录 -就是查询前5条数据 select *from tb_emp limit 0,5; -- 查询的是第一页可以省略起始索引0 select *from tb_emp limit 5; -- 2. 查询 第1页 员工数据, 每页展示5条记录 -第一页的起始索引就是0, select *from tb_emp limit 0,5; -- 3. 查询 第2页 员工数据, 每页展示5条记录 -第2页的起始索引为5 select *from tb_emp limit 5,5; -- 4. 查询 第3页 员工数据, 每页展示5条记录 -第2页的起始索引为10 select *from tb_emp limit 10,5; -- 起始索引计算公式 = (页码-1)* 每页展示的记录数,前端传递过来的是页码数不是起始索引 -- 查询的是第一页可以省略起始索引0 -- 案例一 -- 根据输入条件查询第1页数据(每页展示10条记录),如果要查第2页改成limit 0 , 10; -- 输入条件: -- 姓名:张 (模糊查询) -- 性别:男 -- 入职时间:2000-01-01 ~ 2015-12-31 -- 分页: 0 , 10 -- 排序: 修改时间 DESC -- 格式化 ctrl + alt + L select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp where name like '张%' and gender = 1 and entrydate between '2000-01-01' and '2015-12-31' order by update_time desc limit 0 , 10; -- 案例二 -- 2.1 统计员工性别数量 -- 查的是性别就要用到 select gender , 统计数量就要用到count(*) , 按性别分组就要用到 group by gender -- select gender ,count(*) from tb_emp group by gender; -- 添加注释 -- if(条件表达式, true取值 , false取值) -- if(表达式, tvalue, fvalue) :当表达式为true时,取值tvalue;当表达式为false时,取值fvalue select if(gender=1,'男性员工','女性员工') AS 性别, count(*) AS 人数 from tb_emp group by gender; -- 2.2 统计不同职位的人数 -- 没有添加注释 select gender ,count(*) from tb_emp group by gender; -- case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else result end select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end) AS 职位 , count(*) AS 人数 from tb_emp group by job;