Day07-JavaWeb开发-MySQL-DQL(基本&条件查询,聚合,分组,排序,分页)&多表设计(一对多-外键&多对多,案例分析)

 一. 数据库-DQL(单表操作)

1. 基本查询

--  =========================== 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 as 入职日期 from tb_emp;
-- as 可以省略, 如下:
select name "姓 名",entrydate 入职日期 from tb_emp;


-- 4. 查询已有的员工关联了哪几种职位(不要重复)
select distinct job from tb_emp;

2. 条件查询

-- 1. 查询 姓名 为杨逍 的员工
select * from tb_emp where name = '杨逍';

-- 2. 查询 id小于等于5 的员工信息
select * from tb_emp where id <= 5;

-- 3. 查询 没有分配职位 的员工     // 注: 不能写成job = null, 否则查询结果为空.
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';

-- 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' (包含) 到 '2012-01-01'(包含) 之间 且 性别为女 的员工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' && gender = 2;

-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息   // 以下两句均可
select * from tb_emp where job = 2 || job = 3 or job = 4;

select * from tb_emp where job in (2,3,4);

-- 9. 查询 姓名 为两个字的员工信息     // 模糊查询, _匹配单个字符, %匹配任意个字符.
select * from tb_emp where name like '__';

select * from tb_emp where name like '___';

-- 10. 查询 姓 '张' 的员工信息
select * from tb_emp where name like '张%';

3. 分组查询--聚合函数

--  =========================== DQL:  分组查询 ==============================
-- 聚合函数 : 不对null值进行运算的.
-- 1. 统计该企业员工数量 -- count    // 以下为其三种方式:
-- A. count(字段)     注意: 一定要count一个非空字段
select count(id) from tb_emp;
select count(username) from tb_emp;
select count(job) from tb_emp;

-- B. count(常量)
select count('A') from tb_emp;

-- C. count(*)  // 推荐使用
select count(*) from tb_emp;

-- 2. 统计该企业最早入职的员工 - min
select min(entrydate) from tb_emp;

-- 3. 统计该企业最迟入职的员工 - max
select max(entrydate) from tb_emp;

-- 4. 统计该企业员工 ID 的平均值 avg
select avg(id) from tb_emp;

-- 5. 统计该企业员工的 ID 之和 - sum
select sum(id) from tb_emp;

4. DQL-分组查询

-- 分组
-- 1. 根据性别分组, 统计男性和女性员工的数量 -- count(*)
select gender,count(*) from tb_emp group by gender;


-- 2. 先查询入职时间在'2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组, 获取员工大于等于2的职位
select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;

5. DQL-排序查询

--  =========================== DQL:  排序查询 ==============================
-- 1. 根据入职时间, 对员工进行升序排序 - ASC(默认值)
select * from tb_emp order by entrydate asc;
select * from tb_emp order by entrydate;

-- 2. 根据入职时间, 对员工进行降序排序 - desc
select * from tb_emp order by entrydate desc;

-- 3. 根据 入职时间 对公司的员工进行 升序排序, 入职时间相同, 再按照 更新时间 进行降序排序
select * from tb_emp order by entrydate, update_time desc;
-- //只有第一个字段相同时, 第二个字段才会生效.

6. DQL-分页查询

--  =========================== DQL:  排序查询 ==============================
-- 1. 从 起始索引0 开始查询员工数据, 每页展示5条记录
select * from tb_emp limit 0,5;

-- 2. 查询 第1页 员工数据, 每页展示5条记录
select * from tb_emp limit 5;

-- 3. 查询 第2页 员工数据, 每页展示5条记录
select * from tb_emp limit 5,5;

-- 4. 查询 第3页 员工数据, 每页展示5条记录
select * from tb_emp limit 10,5;


--  起始索引 = (页码 - 1) * 每页展示记录数

7. MySQL-DQL-案例

案例1

select *
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

-- 案例2-1 : 根据需求, 完成员工性别信息的统计 - count(*)
-- if(条件表达式, true取值, false取值)
select if(gender=1, '男性员工', '女性员工'),count(*) from tb_emp group by gender;

通过 if(...) [字段名] 的形式, 更改字段名后如下:

-- 案例2-1 : 根据需求, 完成员工性别信息的统计 - count(*)
-- 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 ... else ... end   // MySQL当中的另一个流程控制函数
select
       (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end) 职位,
       count(*)
from tb_emp group by job;

8. 单表操作总结

二. 多表设计

1. 一对多

-- 一对多
-- 员工
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 '归属的部门',
    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 '部门表';


-- 插入测试数据
insert into tb_dept (id, name, create_time, update_time)
values (1, '学工部', now(), now()),
       (2, '教研部', now(), now()),
       (3, '咨询部', now(), now()),
       (4, '就业部', now(), now()),
       (5, '人事部', now(), now());

INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 2, now(), now()),
       (2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', 2, now(), now()),
       (3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', 2, now(), now()),
       (4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', 2, now(), now()),
       (5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', 2, now(), now()),
       (6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1, now(), now()),
       (7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', 1, now(), now()),
       (8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', 1, now(), now()),
       (9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 1, now(), now()),
       (10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', 1, now(), now()),
       (11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 1, '2007-02-01', 1, now(), now()),
       (12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 1, '2008-08-18', 1, now(), now()),
       (13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 2, '2012-11-01', 2, now(), now()),
       (14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', 2, now(), now()),
       (15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', 2, now(), now()),
       (16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', 2, now(), now()),
       (17, 'chenyouliang', '123456', '陈友谅', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());

2. 一对一

-- 一对一: 用户 与 身份证
create table tb_user
(
    id     int unsigned primary key auto_increment comment 'ID',
    name   varchar(10)      not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1 男  2 女',
    phone  char(11) comment '手机号',
    degree varchar(10) comment '学历'
) comment '用户信息表';

insert into tb_user
values (1, '白眉鹰王', 1, '18812340001', '初中'),
       (2, '青翼蝠王', 1, '18812340002', '大专'),
       (3, '金毛狮王', 1, '18812340003', '初中'),
       (4, '紫衫龙王', 2, '18812340004', '硕士');


create table tb_user_card
(
    id           int unsigned primary key auto_increment comment 'ID',
    nationality  varchar(10)  not null comment '民族',
    birthday     date         not null comment '生日',
    idcard       char(18)     not null comment '身份证号',
    issued       varchar(20)  not null comment '签发机关',
    expire_begin date         not null comment '有效期限-开始',
    expire_end   date comment '有效期限-结束',
    user_id      int unsigned not null unique comment '用户ID',
    constraint fk_user_id foreign key (user_id) references tb_user (id)
) comment '用户信息表';
insert into tb_user_card values (1,'汉','1960-11-06','100000100000100001','朝阳区公安局','2000-06-10',null,1),
                                (2,'汉','1971-11-06','100000100000100002','静安区公安局','2005-06-10','2025-06-10',2),
                                (3,'汉','1963-11-06','100000100000100003','昌平区公安局','2006-06-10',null,3),
                                (4,'回','1980-11-06','100000100000100004','海淀区公安局','2008-06-10','2028-06-10',4);

3. 多对多

--  多对多: 学生 与 课程
create table tb_student
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no   varchar(10) comment '学号'
) comment '学生表';
insert into tb_student(name, no)
values ('黛绮丝', '2000100101'),
       ('谢逊', '2000100102'),
       ('殷天正', '2000100103'),
       ('韦一笑', '2000100104');


create table tb_course
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';
insert into tb_course (name)
values ('Java'),
       ('PHP'),
       ('MySQL'),
       ('Hadoop');


create table tb_student_course
(
    id         int auto_increment comment '主键' primary key,
    student_id int not null comment '学生ID',
    course_id  int not null comment '课程ID',
    constraint fk_courseid foreign key (course_id) references tb_course (id),
    constraint fk_studentid foreign key (student_id) references tb_student (id)
) comment '学生课程中间表';

insert into tb_student_course(student_id, course_id)
values (1, 1),
       (1, 2),
       (1, 3),
       (2, 2),
       (2, 3),
       (3, 4);

4. 案例分析