JavaWeb_Mysql_DML&DQL

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(id, username, password, name, gender, image, job, entrydate, create_time, update_time)
            values(null, 'zhiruo', '123', '周芷若', 2, '1.jpg', 1, '2010-01-01', now(), now());

insert into tb_emp values(null, 'zhiruo2', '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());

更新数据 - 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();

删除数据 - delete

-- 1. 删除 tb_emp 表中 ID为1的员工
delete from tb_emp where id=1;
-- 2. 删除 tb_emp 表中的所有员工
delete from tb_emp;

DQL

语法

select
      字段列表
from
      表名列表
where
      条件列表
group by
      分组字段列表
having
      分组后条件列表
order by
      排序字段列表
limit
      分页参数

基本查询

-- 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, 并起别名(姓名、入职日期)
-- 可以不加as和单引号, 但如果有特殊字符(如空格), 则需要添加单引号
select name as '姓名', entrydate as '入职日期' from tb_emp;

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

条件查询

-- 1. 查询 姓名 为 杨逍 的员工
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where name='杨逍';

-- 2. 查询 id小于等于5 的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where id <= 5;

-- 3. 查询 没有分配职位 的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where job is null;

-- 4. 查询 有职位 的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
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' && 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'(包含) 之间 且 性别为女 的员工信息
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 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. 查询 姓 '张' 的员工信息
select * from tb_emp where name like '张%';

聚合函数和分组查询

1. 分组之后, 查询的字段一般为聚合函数和分组字段, 查询其他字段无任何意义.
2. 执行顺序: where > 聚合函数 > having.

聚合函数

-- 聚合函数: 不对null值进行运算
-- 1. 统计该企业员工数量
-- A. count(字段)
select count(id) from tb_emp; # 29
select count(job) from tb_emp; # 28
-- B. count(常量)
select count(1) from tb_emp;
-- C. count(*) -- 推荐
select count(*) from tb_emp;

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

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

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

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

分组查询

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

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

排序查询

-- 1. 根据入职时间, 对员工进行升序排序
-- 默认升序排序, 可以不加asc
select * from tb_emp order by entrydate asc;

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

-- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 更新时间 进行降序排序
select * from tb_emp order by entrydate, update_time desc;

分页查询

1. 起始索引从0开始, 起始索引=(查询页码-1)*每页显示记录数
2. 分页查询是数据库的方言, mysql是limit
3. 如果查询的是第一页数据, 起始索引可以省略, 简写为limit 10

-- 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: 条件分页查询

-- 根据输入条件查询第1页数据（每页展示10条记录）
-- 输入条件：
    -- 姓名：张 （模糊查询）
    -- 性别：男
    -- 入职时间：2000-01-01 ~ 2015-12-31
-- 分页： 0 , 10
-- 排序： 修改时间  DESC
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: 分组聚合查询

员工性别统计

-- if(条件表达式, true取值, false取值)
select if(gender = 1, '男性员工', '女性员工') '性别', count(*) '人数'
from tb_emp
group by gender;

员工职位统计

-- case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else ... end
select (case job
            when 1 then '班主任'
            when 2 then '讲师'
            when 3 then '学工主管'
            when 4 then '教研主管'
            else '无职' end) '职位',
       count(*)              '人数'
from tb_emp
group by job;

来源

黑马程序员. JavaWeb开发教程