2024/03/07（2024春季）
今天学习计算机知识的时间大概有2个小时。
代码主要以数据库MySQL的操作为主，大概有200多行。
今天博客发表的篇数为一篇。
今天学习的知识点主要是数据库的查询操作，包括单表的基本查询、条件查询、聚合函数、分组查询、排序查询和分页查询
还有多表的设计和查询包括内连接、外连接和子查询
-- 员工管理(带约束) 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'); use db01; select * from tb_emp; select name,entrydate from tb_emp; select name 姓名,entrydate 入职日期 from tb_emp; select distinct job from tb_emp;-- distinct用于去除重复记录 select * from tb_emp where id<=5; select * from tb_emp where job is null; select * from tb_emp where job is not null; select * from tb_emp where password != '123456'; -- != 和 <>效果一样 select * from tb_emp where password <> '123456'; 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'; select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01'; select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender=2; select * from tb_emp where job=2 or job=3 or job=4; select * from tb_emp where job in (2,3,4); -- 模糊匹配 select * from tb_emp where name like '__'; select * from tb_emp where name like '张%'; -- count不对null进行运算 -- A:字段 select count(id) from tb_emp; select count(username) from tb_emp; select count(job) from tb_emp; -- B:常量 select count('A') from tb_emp; -- C：* select count(*) from tb_emp; -- min max select min(entrydate) from tb_emp; select max(entrydate) from tb_emp; -- avg求平均 select avg(job) from tb_emp; -- 分组查询返回的字段分为分组字段和聚合函数 select gender,count(*) from tb_emp group by gender ; -- 2015-01-01之前入职的员工数大于2的职位 聚合函数的结果作为条件时要再后面最后加having作为分组后过滤的条件 select job,count(*) from tb_emp where entrydate<'2015-01-01' group by job having count(*)>=2; -- 排序查询 order by 默认升序asc select * from tb_emp order by entrydate asc ; -- 降序desc select * from tb_emp order by entrydate desc ; select * from tb_emp order by entrydate ,update_time desc ;-- 第一个字段排序相同第二个才生效 -- 分页查询 select * from tb_emp limit 0,5;-- 第一页的索引是0，查询5条信息 -- 起始索引=页面-1*每页记录数
。。。
