8.13 JavaWeb（MySQL P89-P103）

DML（數據操作語言）

Data Manipulation Language，用來對數據庫表中的數據記錄進行增、刪、改操作

添加數據

-- DML ： 數據操作語言
-- DML ： 插入數據 - insert
-- 1.為tb_emp表的username，name，gender字段插入值
insert into tb_emp(username, name, gender,create_time, update_time)values ('wuji', '張無忌', 1, now(), now());-- 2.為tb_emp表的所有字段插入值
insert into tb_emp values (null, 'zhiruo', '123', '周芷若', 2, '1.jpg', 1, '2010-01-01', now(), now());-- 3.批量為tb_emp表的username, name, gender字段插入數據
insert into tb_emp(username, name, gender,create_time, update_time)values ('weifuwang', '韋一笑', 1, now(), now()),('xieshiwang', '謝遜', 1, now(), now());

注意：

1.插入數據時，指定的字段順序需要與值的順序是一一對應的

2.字符串和日期型數據應該包含在引號中

3.插入的數據大小，應該在字段的規定范圍內

修改數據

-- DML : 更新數據 - update
-- 1.將tb_emp表的ID為1的員工姓名name字段更新為‘張三’
update tb_emp set name = '張三', update_time = now() where id = 1;-- 2.將tb_emp表的所有員工的入職日期更新為‘2010-01-01’
update tb_emp set entrydate = '2010-01-01', update_time = now();

刪除數據

-- DML : 刪除數據 - delete
-- 1.刪除tb_emp表中ID為1的員工
delete from tb_emp where id = 1;-- 2.刪除tb_emp表中的所有員工
delete from tb_emp;

注意：delete不能刪除某一個字段的值，如果要操作應該使用update將該字段值設為null

DQL（數據查詢語言）

Data Query Language，用來查詢數據庫表中的記錄

基本查詢

-- DQL : 基本查詢
-- 1.查詢指定字段 name，entrydate 并返回
select name, entrydate
from tb_emp;-- 2.查詢返回所有字段
-- 推薦
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp;-- 不推薦（不直觀、性能低）
select *
from tb_emp;-- 3.查詢所有員工的name， entrydate，并起別名（姓名，入職日期）
select name as 姓名, entrydate  入職日期
from tb_emp;-- 4.查詢已有的員工關聯了哪幾種職位（不要重復）
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 between '2000-01-01' and '2010-01-01';-- 7.查詢入職時間在‘2000-01-01’（包含）到‘2010-01-01’（包含）之間且性別為女的員工信息
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 in (2,3,4);-- 9.查詢姓名為兩個字的員工信息
select *
from tb_emp
where name like '__';-- 10.查詢姓‘張’的員工信息
select *
from tb_emp
where name like '張%';

分組查詢

-- DQL : 分組查詢
-- 聚合函數 : 不對null值進行計算-- 1.統計該企業員工數量
select count(id)
from tb_emp;select count(1)
from tb_emp;-- 推薦，因為底層對count(*)作了優化
select count(*)
from tb_emp;-- 2.統計該企業最早入職的員工
select min(entrydate)
from tb_emp;-- 3.統計該企業最遲入職的員工
select max(entrydate)
from tb_emp;-- 4.統計該企業員工ID的平均值
select avg(id)
from tb_emp;-- 5.統計該企業員工的ID之和
select sum(id)
from tb_emp;

-- 分組
-- 1.根據性別分組，統計男性和女性員工的數量
select gender, count(*) -- 注意要返回分組字段
from tb_emp
group by gender;-- 2.先查詢入職時間在‘2015-01-01’（包含）以前的員工，并對結果根據職位分組，獲取員工數量大于等于2的職位
select job
from tb_emp
where entrydate<='2015-01-01'-- where不能使用聚合函數
group by job
having count(*)>=2;

where和having的區別：

1.執行時機不同：where是分組之前進行過濾，不滿足where條件不參與分組；而having是分組后對結果進行過濾

2.判斷條件不同：where不能對聚合函數進行判斷，而having可以

排序查詢

-- 排序查詢
-- 1.根據入職時間，對員工進行升序排序
select *
from tb_emp
order by entrydate;-- 2.根據入職時間，對員工進行降序排序
select *
from tb_emp
order by entrydate desc;-- 3.根據入職時間對公司的員工進行升序排序，入職時間相同，再按照更新時間進行降序排序
select *
from tb_emp
order by entrydate, update_time desc;

分頁查詢

-- 分頁查詢
-- 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;-- 起始索引 = （頁碼-1）*每頁展示記錄數

注意：

1.起始索引從0開始，起始索引 = （查詢頁碼-1）*每頁顯示的記錄數

2.分頁查詢是數據庫的方言，不同的數據庫有不同的實現，MySQL中是limit

3.如果查詢的是第一頁數據，起始索引可以省略，直接簡寫為 limit 10

案例

1.根據需求完成員工管理的條件分頁查詢

-- 案例1 ： 按需求完成員工管理的條件分頁查詢 - 根據輸入條件，查詢第一頁數據，每頁展示10條記錄
-- 輸入條件：-- 姓名：張-- 性別：男-- 入職時間：2000-01-01   2015-12-31select *
from tb_emp
where name like '張%'and gender = 1and entrydate between '2000-01-01' and '2015-12-31'
order by update_time desc
limit 0,10;

2.根據需求，完成員工信息的統計

-- 案例2-1 ： 根據需求，完成員工性別信息的統計
-- if(條件表達式，true取值，false取值)
select if(gender=1,'男性員工', '女性員工') 性別, count(*)
from tb_emp
group by gender;-- 案例2-2 ： 根據需求，完成員工職位信息的統計
-- case 表達式 when 值1 then 結果1 when 值2 then 結果2 ...end
select (case jobwhen 1 then '班主任'when 2 then '講師'when 3 then '學工主管'when 4 then '教研主管'else '未分配職位'end) 職位,count(*) 數量
from tb_emp
group by job;

多表設計

項目開發中，在進行數據庫表結構設計時，會根據業務需求及業務模塊之間的關系，分析并設計表結構，由于業務之間相互關聯，所以各個表結構之間也存在著各種聯系，基本上分為三種：一對多、多對多、一對一

一對多

需求：根據頁面原型及需求文檔，完成部門及員工模塊的表結構設計

首先創建一個員工表和一個部門表，這里員工表新增一個字段dept_id表示歸屬部門的id

-- 員工
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 '入職時間',dept_id int unsigned comment '部門ID',create_time datetime not null comment '創建時間',update_time datetime not null comment '修改時間'
) comment '員工表';-- 部門
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 '部門表'

出現的問題：部門數據可以直接刪除，例如在部門表中刪除某各部門，然而還有部分員工歸屬于該部門下，此時就出現了數據的不完整、不一致問題