今天學習計算機知識的時間大概有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*每頁記錄數
。。。