MySQL練習

1. 数据库操作-DQL

DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库表中的记录。

查询关键字：SELECT

1.2 语法

DQL查询语句，语法结构如下：

SELECT
        字段列表
FROM
        表名列表
WHERE
        条件列表
GROUP BY
        分组字段列表
HAVING
       分组后条件列表
ORDER BY
       排序字段列表
LIMIT
       分页参数

首先準備一些測試數據

create database db02; -- 创建数据库
use db02; -- 切换数据库
-- 员工管理(带约束)
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');

基本查询（不带任何条件）

-- 1. 查询指定字段 name,entrydate 并返回
       select name,entrydate from emp;
       
-- 2. 查询返回所有字段
       select * from emp; 
       
-- 3. 查询所有员工的 name,entrydate, 并起别名(姓名、入职日期)  --- as 关键字可以省略      
  -- 方式1：
       select name AS 姓名, entrydate AS 入职日期 from tb_emp;
  -- 方式2： 别名中有特殊字符时，使用''或""包含
       select name AS '姓 名', entrydate AS '入职日期' from tb_emp;
  -- 方式3：
       select name AS "姓名", entrydate AS "入职日期" from tb_emp;

-- 4. 查询员工有哪几种职位(不要重复) -- distinct 
       select distinct job  from emp 

条件查询（where）

语法：

select  字段列表  from  表名  where  条件列表 ;                     
-- 条件列表：意味着可以有多个条件

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

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

       -- 3. 查询 没有分配职位 的员工信息  -- 判断 null , 用 is null
          select * from emp where job is null 
       

       -- 4. 查询 有职位 的员工信息  -- 判断 不是null , 用 is not null
          select * from emp where job is not null 


       -- 5. 查询 密码不等于 '123456' 的员工信息
          方式一： select * from emp where password <> '123456'
          方式二： select * from emp where password != '123456'
          
       
       -- 6. 查询入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
          方式一：select * from emp where entrydate between '2000-01-01' and  '2010-01-01'

          方式二：select * from emp where entrydate>='2000-01-01' and entrydate <= '2010-01-01';

       
       -- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
            select * from emp where entrydate between '2000-01-01' and  '2010-01-01' and gender ='2'
       
       
       -- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
          方式一： select * from emp  where job in (2,3,4)
          方式二： select * from emp  where job where job=2 or job=3 or job=4;
       

       -- 9. 查询姓名为两个字的员工信息
           select * from emp e where  name like "__";   # 通配符 "_" 代表任意1个字符
       
       
       -- 10. 查询姓 '张' 的员工信息  ---------> 张%
          select * from emp e where name like  '张%';   # 通配符 "%" 代表任意个字符（0个 ~ 多个）
       
         
       -- 11. 查询姓名中包含 '三' 的员工信息
          select * from emp e where name like  '%三%';

常用的比较运算符如下:

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between … and …	在某个范围之内(含最小、最大值)
in(…)	在in之后的列表中的值，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
is null	是null

常用的逻辑运算符如下:

逻辑运算符	功能
and 或 &&	并且 (多个条件同时成立)
or 或 ||	或者 (多个条件任意一个成立)
not 或 !	非 , 不是

聚合函数

语法：

select 聚合函数(字段列表)  from  表名 ;

注意 : 聚合函数会忽略空值，对NULL值不作为统计。

常用聚合函数：

count ：按照列去统计有多少行数据。

- 在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。

sum ：计算指定列的数值和，如果不是数值类型，那么计算结果为0

max ：计算指定列的最大值

min ：计算指定列的最小值

avg ：计算指定列的平均值

案例1：统计该企业员工数量

# count(字段)
select count(id) from emp;   -- 结果：29
select count(job) from emp;  -- 结果：28 （聚合函数对NULL值不做计算）

# count(*)  推荐此写法（MySQL底層進行了優化）
select count(*) from tb_emp;

案例2：统计该企业最早入职的员工

select min(entrydate) from emp;

案例3：统计该企业最迟入职的员工

 select max(entrydate) from emp e; 

案例4：统计该企业员工 ID 的平均值

 select avg(id) from emp; 

案例5：统计该企业员工的 ID 之和

 select sum(id) from emp; 

分组查询

分组： 按照某一列或者某几列，把相同的数据进行合并输出。

分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行合并计算。

分组查询通常会使用聚合函数进行计算。

语法：

select  字段列表  from  表名  [where 条件]  group by 分组字段名  [having 分组后过滤条件];

案例1：根据性别分组 , 统计男性和女性员工的数量

select gender, count(*)
 from emp
 group by gender; 
-- 按照gender字段进行分组（gender字段下相同的数据归为一组）

案例2：查询入职时间在 ‘2015-01-01’ (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位

 select job,count(*)  from emp e where entrydate <= '2015-01-01'
 group by job having count(*)>=2 

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

where与having区别（面试题）

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

排序查询

语法：

select  字段列表  
from   表名   
[where  条件列表] 
[group by  分组字段 ] 
order by  字段1  排序方式1 , 字段2  排序方式2 … ;

排序方式：

• ASC ：升序（默认值）
• DESC：降序

案例1：根据入职时间, 对员工进行升序排序

select * from emp order by entrydate ASC

案例2：根据入职时间，对员工进行降序排序

select * from emp order by entrydate DESC

案例3：根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序

select * from emp order by entrydate asc,update_time DESC 

注意事项：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询

語法：

select  字段列表  from   表名  limit  起始索引, 查询记录数 ;

案例1：从起始索引0开始查询员工数据, 每页展示5条记录

select * from emp limit 0,5 

案例2：查询 第1页 员工数据, 每页展示5条记录

select * from emp limit 0,5 

案例3：查询 第2页 员工数据, 每页展示5条记录

select * from emp limit 5,5

案例4：查询 第3页 员工数据, 每页展示5条记录

select * from emp limit 10,5

注意事项:

• 起始索引从0开始。 计算公式 ： 起始索引 = （查询页码 – 1）* 每页显示记录数
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 条数