MySQL分析与整理 — 表的基本操作（select查询）

电玩女神 2023-02-24 08:14 8阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]

> 文章中所有操作均是在 MySQL 5.7 版本下进行的

### 1 示例表创建及示例数据创建 ###

-- 员工表
    drop table if exists tbl_emp;
    create table tbl_emp(
        empno	 int primary key comment '员工号',
        ename	 varchar(50) not null comment '员工姓名',
        job		 varchar(50) not null comment '工作职位',
        mgr		 int comment '上司编号',
        hiredate datetime not null comment '入职日期',
        sal		 decimal(7,2) not null comment '工资',
        comm	 decimal(7,2) comment '奖金',
        deptno	 int not null comment '部门编号'
    ) comment '员工表';
    -- 插入示例数据
    insert into tbl_emp values (7369, '张三', '普通职员', 7902, '2010-12-17', 800.00, null, 20);
    insert into tbl_emp values (7499, '李四', '销售', 7698, '2011-02-20', 1600.00, 300.00, 30);
    insert into tbl_emp values (7521, '王五', '销售', 7698, '2011-02-22', 1250.00, 500.00, 30);
    insert into tbl_emp values (7566, '赵六', '经理', 7839, '2011-04-02', 2975.00, null, 20);
    insert into tbl_emp values (7654, '周一', '销售', 7698, '2011-09-28', 1250.00, 1300.45, 30);
    insert into tbl_emp values (7698, '周二', '经理', 7839, '2011-05-01', 2850.00, null, 30);
    insert into tbl_emp values (7782, '周三', '经理', 7839, '2011-06-09', 2450.00, null, 10);
    insert into tbl_emp values (7788, '周四', '分析师', 7566, '2017-04-19', 3000.00, null, 20);
    insert into tbl_emp values (7839, '周五', '总裁', null, '2011-11-17', 5000.00, null, 10);
    insert into tbl_emp values (7844, '周六', '销售', 7698, '2011-09-08', 1500.00, 630.42, 30);
    insert into tbl_emp values (7876, '周日', '普通职员', 7788, '2017-05-23', 1100.00, null, 20);
    insert into tbl_emp values (7900, 'SMITH', '普通职员', 7698, '2011-12-03', 950.00, null, 30);
    insert into tbl_emp values (7902, 'ALLEN', '销售', 7566, '2011-12-03', 3000.00, null, 20);
    insert into tbl_emp values (7934, 'worklee', '普通职员', 7782, '2012-01-23', 1300.00, null, 10);
    insert into tbl_emp values (1001, 'lee', '普通职员', 7782, '2012-01-24', 1300.00, null, 50);

### 2 基本查询 ###

#### 2.1 基本查询 ####

-- 查询所有列，*表示查询所有列
    select * from tbl_emp;
    -- 指定列查询（列要在表里存在）
    select empno, ename, sal from tbl_emp;

#### 2.2 去重复查询 ####

/*
    去重复查询distinct，distinct在查询列之前
    distinct是和它后面所有的列一起去重复，不是第一个列
    */
    -- 去重复(job列)查询
    select job from tbl_emp;
    select distinct job from tbl_emp;
    -- 查询两个列数据有(job和mgr去重复)
    select job, mgr from tbl_emp;
    select distinct job, mgr from tbl_emp;

#### 2.3 条件查询 ####

/*
    根据条件过滤表的数据，查询到想要的数据
    语法：
    select 
    	要查询的字段|表达式|常量值|函数|等
    from 
    	表名
    where 
    	条件;
    条件运算符：大于> 小于< 大于等于>= 小于等于<= 等于= 不等于!=或<>，注：!=和<>均为不等于
    多条件同时成立用and连接
    多条件一个成立的用or连接
    查询null数据应该用is null，而不是=''
    */
    -- 查询“工作职位是普通职员”的数据
    select * from tbl_emp where job = '普通职员';
    -- 查询“员工姓名是SMITH”的数据
    select * from tbl_emp where ename = 'SMITH';
    -- 查询“工资大于2000”的数据
    select * from tbl_emp where sal > 2000;
    -- 查询“工资大于2000并且职位是经理”的数据
    select * from tbl_emp where sal > 2000 and job = '经理';
    -- 查询“职位是经理或者职位是分析师”的数据
    select * from tbl_emp where job = '分析师' or job = '经理';
    -- and和or一起使用
    -- 查询“工资大于2500并且职位是经理或者职位是分析师”的数据
    select * from tbl_emp where sal > 2500 and job = '分析师' or job = '经理';
    select * from tbl_emp where (sal > 2500 and job = '分析师') or job = '经理';
    -- 查询“工资大于2500并且职位是经理或者工资大于2500职位是分析师”的数据
    select * from tbl_emp where sal > 2500 and (job = '分析师' or job = '经理');
    -- 查询“没有上司“的数据
    select * from tbl_emp where mgr is null;

#### 2.4 模糊查询 ####

/*
    根据模糊条件l过滤表的数据 like使用
    '_'代表匹配任意一个字符，'%'代表匹配0～n个任意字符
    */
    -- 查询“两个字且六结尾名字”的数据
    select * from tbl_emp where ename like '_六';
    -- 查询“两个字且王开头名字”的数据
    select * from tbl_emp where ename like '王_';
    -- 查询“三个字且李在中间名字”的数据
    select * from tbl_emp where ename like '_李_';
    -- 查询“含有以w开头名字”的数据
    select * from tbl_emp where ename like 'w%';
    -- 查询“含有以一结尾名字”的数据
    select * from tbl_emp where ename like '%一';
    -- 查询“含有赵字名字”的数据
    select * from tbl_emp where ename like '%赵%';
    -- 查询“含有赵字名字并且工资大于1000”的数据
    select * from tbl_emp where ename like '%赵%' and sal > 1000;

#### 2.5 列运算 ####

/*
    数值的列可以进行加、减、乘、除运算
    null的任何运算值都等于null
    将字符串做加减乘除运算，会把字符串当作0
    */
    -- 查询“7369员工的姓名，工资，工资+1000”的数据
    select ename, sal, sal+1000 from tbl_emp where empno = 7369;
    -- 查询“7369员工的年薪”的数据（7369员工的奖金是null所以无论怎么运算最终都是null）
    select ename, sal*12+comm*12 from tbl_emp where empno = 7369;
    -- 上述情况可以用ifnull()函数判断null值，ifnull(comm, 0)表示如果comm是null返回0
    select ename, sal*12 + ifnull(comm, 0)*12 from tbl_emp where empno = 7369;

#### 2.6 排序 ####

/*
    默认排序是升序（正序）
    默认情况下并不是按照主键排序的，是和相关查询列有关
    排序通过order by 列名 升序asc/降序desc
    无论多么复杂的查询语句，order by总是在所有查询语句的最后
    order by的概念就是所有的查询结束，针对查询之后的数据进行排序
    */
    -- 按照“员工工资”升序排序，asc升序关键字可以省略
    select * from tbl_emp order by sal;
    select * from tbl_emp order by sal asc;
    -- 按照“员工入职日期”降序排序，desc降序关键字不能省略
    select * from tbl_emp order by hiredate desc;
    -- 查询“工资大于2000的员工并按入职日期降序排序”的数据
    select * from tbl_emp where sal > 2000 order by hiredate desc;

#### 2.7 聚合函数 ####

/*
    聚合函数
    count(汇总记录行数) 可以为count(列名)，count(*)，count(1)，count(0)
    max(最大值)
    min(最小值)
    sum(求和)
    avg(平均值)
    */
    -- 查询记录数
    select count(empno) from tbl_emp;
    select count(*) from tbl_emp;
    select count(1) from tbl_emp;
    select count(0) from tbl_emp;
    select count(*) from tbl_emp where sal > 2000;
    -- 工资最大值
    select max(sal) from tbl_emp;
    select max(sal) from tbl_emp where deptno = 20;
    -- 工资最小值
    select min(sal) from tbl_emp;
    select min(sal) from tbl_emp where deptno = 20;
    -- 工资总和
    select sum(sal) from tbl_emp;
    select sum(sal) from tbl_emp where deptno = 20;
    -- 工资平均值
    select avg(sal) from tbl_emp;
    select avg(sal) from tbl_emp where deptno = 20;

#### 2.8 分组查询 ####

分组查询如果遇到“only\_full\_group\_by”的问题，请查看作者另外一篇文章《MySQL5.7.x版本only\_full\_group\_by解决办法》进行解决。

/*
    分组查询group by一般是和聚合函数一起使用
    分组查询不能查到表中列的信息，是用来查询分组之后的统计信息
    如果筛选分组查询的结果应该用having连接聚合函数进行筛选
    分组查询的列必须是分组的列
    where是用来表中的字段进行条件删选的
    having是用来对分组查询后的统计数据进行筛选的
    where和having如果一起使用，应该是先where进行筛选然后分组统计之后再having
    如果有排序，order by必须在所有的查询最后
    */
    -- 通过“deptno部门号分组查询各个部门人数”
    select deptno, count(empno) from tbl_emp group by deptno;
    -- 通过“deptno部门号分组查询各个部门工资最高”
    select deptno, max(sal) from tbl_emp group by deptno;
    -- 通过“deptno部门号分组查询各个部门人数，且部门人数高于3人”
    select deptno, count(empno) from tbl_emp group by deptno having count(empno) > 3;
    -- 查询“工资大于1000并按照deptno部门号分组查询各个部门人数，且部门人数高于3人”
    select deptno, count(empno) from tbl_emp where sal > 1000 group by deptno having count(empno) > 3;
    -- 查询“工资大于1000并按照deptno部门号分组查询各个部门人数，且部门人数高于3人，并按部门号降序排列”
    select deptno, count(empno) from tbl_emp where sal > 1000 group by deptno having count(empno) > 3 order by deptno desc;
    -- 查询“工资大于1000并按照deptno部门号分组查询各个部门人数，且部门人数高于3人，并按人数降序排列”
    select deptno, count(empno) from tbl_emp where sal > 1000 group by deptno having count(empno) > 3 order by count(empno) desc;

#### 2.9 别名 ####

/*
    别名分字段的别名和表的别名
    可以通过“as别名”方式进行别名定义
    通常情况下字段别名和表的别名在一起使用时，会只省略表的as关键字
    别名大部分在多表查询中方便区分不同的表
    */
    -- 表的别名（可以省略as关键字）
    select e.ename, e.sal from tbl_emp as e;
    select e.ename, e.sal from tbl_emp e;
    -- 列的别名（可以省略as关键字）
    select deptno as 部门, count(empno) as 人数 from tbl_emp group by deptno;
    select deptno 部门, count(empno) 人数 from tbl_emp group by deptno;
    -- 表的别名和列的别名共用（可以省略as关键字）
    select e.deptno as 部门, count(e.empno) as 人数 from tbl_emp as e group by e.deptno;
    select e.deptno 部门, count(e.empno) 人数 from tbl_emp e group by e.deptno;
    -- 利用别名进行操作
    select e.deptno as dept, count(e.empno) as counts from tbl_emp e where e.sal > 1000 group by e.deptno having counts > 3 order by e.deptno desc;

#### 2.10 分页查询 ####

/*
    分页查询是我们经常用到的查询方式
    分页查询的关键字是limit
    格式为"limit m, n"
    其中从m开始，不包括m，m可以省略表示从0开始，n为查询个数
    */
    -- 查询“前10条记录”
    select * from tbl_emp limit 10;
    select * from tbl_emp limit 0, 10;
    -- 查询“从第2条记录前10条记录（不包括第2条）”
    select * from tbl_emp limit 2, 10;
    -- 带条件的
    select * from tbl_emp where sal > 1000 limit 2, 5;

### 3 多表查询 ###

增加一个示例数据表。

-- 部门表
    drop table if exists tbl_dept;
    create table tbl_dept(
        deptno	 int primary key comment '部门号',
        dname	 varchar(50) not null comment '部门名称',
        addr	 varchar(50) not null comment '地址'
    ) comment '员工表';
    -- 插入示例数据
    insert into tbl_dept values (10, '财务部', '一层');
    insert into tbl_dept values (20, '研发部', '二层');
    insert into tbl_dept values (30, '销售部', '三层');
    insert into tbl_dept values (40, '分公司', '解放路');

普通多变连接
    自连接
    内连接，外连接
    子查询
    联合查询
    select 字段，... from 表1
    [inner|left outer|right outer|cross] join 表2 on  连接条件
    [inner|left outer|right outer|cross] join 表3 on  连接条件
    [where 筛选条件]
    [group by 分组字段]
    [having 分组后的筛选条件]
    [order by 排序的字段或表达式]
    如果无连接条件将出现笛卡尔乘积现象
    n个表连接，至少需要n-1个必要连接条件
    多个表之间不分主次，没有顺序要求
    通常情况下会为各个表起别名，提高阅读性和性能

#### 3.1 传统多表查询 ####

/*
    传统多表连接，如果数据多了，性能会受到影响
    n个表连接，至少需要n-1个必要连接条件
    */
    -- 查询“员工姓名，工资，部门名称”
    select e.ename, e.sal, d.dname from tbl_emp e, tbl_dept d where e.deptno = d.deptno;

#### 3.2 自连接 ####

/*
    自连接其实就利用表的别名进行当前表和当前表联合查询
    */
    -- 查询“7369员工的姓名和他上司的姓名”
    select e1.empno, e1.ename, e2.ename from tbl_emp e1, tbl_emp e2 where e1.mgr = e2.empno and e1.empno = 7369;

#### 3.3 内连接 ####

/*
    内连接 inner join ... on（inner和on都可省略）
    内连接可以粗浅的理解为传统多表连接的优化写法，性能更优
    */
    -- 查询“员工姓名，工资，部门名称”
    select e.ename, e.sal, d.dname from tbl_emp e inner join tbl_dept d on e.deptno = d.deptno;
    select e.ename, e.sal, d.dname from tbl_emp e join tbl_dept d on e.deptno = d.deptno;
    select e.ename, e.sal, d.dname from tbl_emp e join tbl_dept d where e.deptno = d.deptno;
    -- 查询“7369员工姓名，工资，部门名称”
    select e.ename, e.sal, d.dname from tbl_emp e inner join tbl_dept d on e.deptno = d.deptno and e.empno = 7369;
    select e.ename, e.sal, d.dname from tbl_emp e inner join tbl_dept d on e.deptno = d.deptno where e.empno = 7369;
    */

#### 3.4 外连接 ####

/*
    外连接分：左外连接，右外连接
    左外连接，可以粗浅的理解为用join的表匹配前表，如果join的表有不匹配的数据用null表示
    右外连接，可以粗浅的理解是以join的表为主匹配前表，如果前表有不匹配的数据用null表示
    MySQL并不支持全外连接，可以通过union连接左外连接和右外连接，得到全外连接
    */
    -- 查询“员工姓名，部门号，部门名称”，左外连接，如果tbl_dept没有匹配数据用null代替
    select e.ename, d.deptno, d.dname from tbl_emp e left outer join tbl_dept d on e.deptno = d.deptno;
    -- 左外连接outer可省略
    select e.ename, d.deptno, d.dname from tbl_emp e left join tbl_dept d on e.deptno = d.deptno;
    -- 查询“员工姓名，部门号，部门名称”，右外连接，如果tbl_emp没有匹配数据用null代替
    select e.ename, d.deptno, d.dname from tbl_emp e right outer join tbl_dept d on e.deptno = d.deptno;
    -- 右外连接outer可省略
    select e.ename, d.deptno, d.dname from tbl_emp e right join tbl_dept d on e.deptno = d.deptno;

#### 3.5 联合查询 ####

/*
    union联合、合并查询
    select 字段|常量|表达式|函数 from 表 where 条件 
    union [all]
    select 字段|常量|表达式|函数 from 表 where 条件 
    union [all]
    .....
    select 字段|常量|表达式|函数 from 表 where 条件;
    多条查询语句的查询的列数必须是一致的
    多条查询语句的查询的列的类型几乎相同
    union是去重复数据，union all不去重
    */
    -- 比如刚才的左外和右外
    select e.ename, d.deptno, d.dname from tbl_emp e left outer join tbl_dept d on e.deptno = d.deptno 
    union 
    select e.ename, d.deptno, d.dname from tbl_emp e right outer join tbl_dept d on e.deptno = d.deptno;

#### 3.6 子查询 ####

/*
    子查询在多表或单表均可使用
    where型子查询
    	如果子查询sql语句返回一行数据用 = 匹配，返回的数据是多行用 in 匹配
    相关子查询
    	exists 相对于where型子查询性能更优
    所有的子查询这里全都以单列为准的，不涉及多列
    */
    select d.deptno, d.dname from tbl_dept d where d.deptno in 
    (
    	select e.deptno from tbl_emp e where e.sal > 2000
    );
    select d.deptno, d.dname from tbl_dept d 
    where exists(select 1 from tbl_emp e where d.deptno = e.deptno and e.sal > 2000);

### 4 其他查询 ###

在 MySQL 中还有一种查询是 cross join 查询，cross join 查询在没有条件的筛选下其实就获得的笛卡尔积。说实话不止是 MySQL 中，作者认为别的数据库中也不推荐使用表与表之间的笛卡尔积。即使我们用 cross join 加上 on 筛选多表查询的必要条件时，那它其实就是最基本的多表查询。这里不过多解释了，有兴趣的朋友可以去搜一搜 cross join 的相关介绍。

### 结语 ###

数据库表的数据查询不止只有这些知识点，单表和多表，尤其多表中的查询比较复杂，写出更好更优的 SQL 查询语句是一点一点积累出来的。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]: /images/20230209/bce312ef55f643369bba63ce89d1863f.png