MySQL学习笔记（二）

2021-10-25 15:40:45

B站老杜——MySQL入门

DQL-Data Query Language（数据查询语言）

简单查询（查询单个字段）

  • 格式：select 字段名 from 表名; 
//字段名和表名都是标识符，select和from都是关键字
//强调：对于sql语句来说是通用的，sql不区分大小写，以‘;’结尾
//		中文别名需要用引号引起来

查询多个字段

//使用英文下逗号隔开
select 字段名,字段名 from 表名;

查询所有字段

  1. 可以把每个字段都写上

    select a,b,c,d,e,f... from 表名;

  2. 可以使用*

    select * from 表名;

    缺点：（会先把'*'转化为字段名，然后再按照第一种方式查询）

    1. 效率低
    2. 可读性差

    在实际开发中不建议。

  3. 查询年薪

    select ename, sal*12 from emp;

    结论：字段可以参与加减乘除运算

  4. 查询时起别名（使用‘as'）

select deptno, dname as deptname from dept;
//'as'可以省略，注意：别名之间不能有空格

select deptno, dname deptname from dept;
//如果非要在别名中加' '空格,可以使用引号（''或""都可以）

select deptno,dname 'dept name' from dept;
//注意：在所有的数据库中，字符串统一使用单引号括起来，单引号是标准，双引号在oracle数据库中用不了。但是在mysql中可以使用

注意：

  • 只是将显示的查询结果列名显示为deptname，原列名不改变还是叫：dname

  • 在所有的数据库中，字符串统一使用单引号括起来，单引号是标准，双引号在oracle数据库中用不了。但是在mysql中可以使用

记住：

  • select语句是永远都不会进行修改操作的。（因为只负责查询）

条件查询

  1. 什么是条件查询

    • 不是将表中所有数据都查出来。是查询出符合条件的。

  2. 语法格式

    select 
	字段1,字段2,字段3...
from
	表名
where
	条件;

  3. 都有哪些条件

    // = 等于
// 查询薪资等于800的员工
select empno,ename from emp where sal = 800;

// 查询叫smith的员工
select empno,ename,sal from emp where ename = 'smith';

// <> 或 != 不等于
// 查询薪资不等于800的员工
select empno,ename from emp where sal != 800;
select empno,ename from emp where sal <> 800;

// < 小于
// 查询薪资小于2000的员工
select empno,ename from emp where sal < 2000;

// <= 小于等于
// 查询薪资小于等于2000的员工
select empno,ename from emp where sal <= 2000;

// > 大于
// 查询薪资大于2000的员工
select empno,ename from emp where sal > 2000;

// >= 大于等于
// 查询薪资大于等于2000的员工
select empno,ename from emp where sal >= 2000;
// between ... and ... 两个值之间，等同于 >= and <=

// 查询薪资在2450和3000之间的员工信息? 包括2450和3000
select empno,ename,sal from emp where sal between 2450 and 3000;
select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;

// is null 为空
// 查询津贴为空的员工信息
select empno,ename,sal,comm from emp where comm is null;

// and 并且
//查询工作岗位是manager，并且工资大于2500的员工信息
select empno,ename,job,sal from emp where job = 'manager' and sal > 2500;

// or 或者
//查询工作岗位是manager 和salesman的员工
select empno,ename,job from emp where job = 'manager' or job = 'salesman';

//查询工资大于2500，并且部门编号为10或20部门的员工
select deptno,ename,sal from emp where sal > 2500 and (deptno = 10 or deptno = 20);

// in 包含，相当于多个or（not in 不在这个范围）
//查询工作岗位时manager和salsman的员工
select empno,ename,job from emp where job = 'manager' or job = 'salesman';
select empno,ename,job from emp where job in('manager','salesman');

//查询薪资等于800和5000的员工信息
select empno,ename,job,sal from emp where sal in(800,5000);

// like 称为模糊查询，支持%或下划线匹配
// %匹配任意个字符
// 下划线，一个下划线只匹配一个字符
//（%是一个特殊字符，_也是一个特殊字符）
// 找出名字中含有o的？
select ename from emp where ename like '%o%';
// 找出名字中以 t 结尾的
select ename from emp where ename like '%t';
// 找出名字中以k开始的
select ename from emp where ename like 'k%';
// 找出名字中第二个字母是a的
select ename from emp where ename like '_a%';
// 找出名字中第三个字母是r的
select ename from emp where ename like '__r%'
// 找出名字中有"_"的
// \转义字符
select name from t_student where name like '%\_%';

    注意：

    • 在使用between...and...的时候要遵循左小右大的原则
    • between..and...是闭区间
    • 在数据库中null不能使用等号进行衡量。需要使用is null。因为数据库中的null代表什么也没有，他不是一个值，所以不能使用等号衡量。
    • and 和 or同时出现时，and优先级较高。如果想让or先行，需要加上小括号
    • 如果不确定优先级，可以加上小括号
    • in不是一个区间。in后面跟的是具体的值
    • 找出名字中有“_”的数据，可以使用转义字符''

排序

// 查询所有员工薪资，排序
select ename,sal from emp order by sal;

// 查询所有员工薪资，指定降序
select ename,sal from emp order by sal desc;

// 查询所有员工薪资，指定升序
select ename,sal from emp order by sal asc;

// 查询员工名字和薪资，要求按照薪资升序，如果薪资一样的胡啊，再按照名字升序排列
select ename,sal from emp
order by 
	sal asc, ename asc;//sal在前，起主导地位
	
//根据字段的位置也可以排序
//按照查询结果的第2列sal排序
select ename,sal from emp order by 2;

//找出工资在1250到3000之间的员工信息，要求按照薪资降序排列
//执行顺序from-->where-->select-->order by(排序总是在最后)
select 
	ename,sal
from 
	emp 
where 
	sal between 1250 and 3000 
order by 
	sal desc;
  • 排序，默认为升序
  • 不建议在开发中根据字段的位置进行排序，因为不健壮，列的顺序很容易发生改变

分组查询（非常重要）

  1. 什么是分组中查询

    • 在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作，这个时候就需要进行分组查询
    //将之前的关键字全部组合在一起
select
	...
from
	...
where
	...
group by
	...
order by
//执行顺序from->where->group by->select->order by

//找出每个工作岗位的工资和
//先从emp表中查询数据，根据job字段进行分组，然后对每一组数据进行sum(sal)
select sum(sal) from emp group by job;
//找出每个部门的最高薪资
//按照部门编号分组，求每一组的最大值
select deptno,max(sal) from emp group by deptno;
//找出每个部门不同岗位的最高薪资
select job,deptno,max(sal) from emp group by deptno, job;

//找出每个部门最高薪资，要求显示最高薪资大于3000的
//第一步：找出每个部门最高薪资
select deptno,max(sal) from emp group by deptno;
//第二步：要求显示最高薪资大于3000
// having 可以对分完组之后的数据进一步过滤
select deptno,max(sal) from emp group by deptno having max(sal) > 3000;
select deptno,max(sal) from emp where sal > 3000 group by deptno;
//找出每个部门平均薪资高于2500的
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;

select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
 
//找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除manager岗位之外，要求按照平均薪资降序拍
select 
	job, avg(sal) as acgsal
from 
	emp
where
	job <> 'MANGER'
group by
	job
having
	avg(sal) > 1500
order by
	avgsal desc;

    说明：

    • 分组函数不能直接使用在where后面

      因为分组函数在使用的时候必须先分组之后才能使用。

      where执行的时候，还没有分组，所以where后面不能出现分组函数

    • 在一条select语句中，如果有group by语句的话，

      select后面只能跟：参加分组的字段，以及分组函数

      其他的一律不能跟

    • having不能单独使用。having不能代替where，having必须与group by联合使用

    • 能使用where过滤掉的，尽量使用

    • where和having，优先选择where，，然后再选择having

    • 执行顺序：

      1. from
      2. where
      3. group by
      4. having

      从某张表中查询数据，

      先经过where条件筛选出有价值的数据

      对这些有价值的数据进行分组

      分组之后可以使用having继续筛选

      select查询出来。

      最后排序输出

连接查询（最重要）

  • 什么是连接查询

    • 从一张表中单独查询，称为单表查询
    • emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。这种跨表查询，多张表联合起来查询数据，被称为连接查询。

  • 当两张表进行连接查询，没有任何限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象

  • 怎么避免笛卡尔积现象？

    • 连接时加条件，满足这个条件的记录被筛选出来
    select ename,dname from emp,dept where emp.deptno = dept.deptno;

    ​ 最终查询结果条数是14条，但是匹配的过程中，匹配的次数没有减少，只不过进行了四选一

    注意：

    • 通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免笛卡尔积现象

  • 连接查询的分类

    1. 根据语法的年代分类;
    • SQL92：1992年的时候出现的语法
    • SQL99：1999年的时候出现的语法
    1. 根据表的连接方式分类：

    • 内连接（A和B连接，AB两表没有主次关系）

      • 特点：完成能够匹配上这个条件的数据查询出来

      • 等值连接

        • 条件是等量关系，所以是等值连接
        // 查询每个员工所在部门名称，显示员工名和部门名
select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;//(SQL92的语法)
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

        说明：

        • SQL92缺点：结果不清晰，表的连接条件，和后期进一步筛选的条件都放where后面。

        • SQL99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where条件

          select
	...
from
	a
inner join
//inner可以省略，带着inner可读性更好，一看就是内连接
	b
on
	a和b的连接条件
where
	筛选条件

      • 非等值连接

        • 条件不是一个等量关系，称为非等值连接
        //找出每个员工的薪资登记，要求显示员工名、薪资薪资等级
select
	e.ename, e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

      • 自连接

        • 技巧：一张表看做两张表
        // 查询员工的上级领导，要求显示员工名和对应领导名
// 技巧：一张表看程两张表
select
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
join
	emp b
on
	a.mgr = b.empno;//员工的领导编号 = 领导的员工编号

    • 外连接

    • 在外连接当中，两张表连接，产生了主次关系

      • 右外连接（右连接）

        select
	e.ename,d.dname
from 
	emp e right outer join dept d
on
	e.deptno = d.deptno;
        • right表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表

      • 左外连接（左连接）

        select
	e.ename,d.dname
from
	emp e left outer join dept d
on
	e.deptno = d.deptno;
        • 任何一个右连接都有左连接的写法
        • 任何一个左连接都有右连接的写法
        • outer可以省略，带着可读性强

      • 说明：

        • 外连接的查询结果条数一定是 >= 内连接的查询结果条数

          //查询每个员工的上级领导，要求显示所有员工的名字和领导名
select
	a.ename as '员工名',b.ename as '领导名'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;

    • 全连接（几乎很少有）

    1. 三张表，四张表怎么连接？

      • 语法：

        select
	...
from
	a
join
	b
on
	a和b的连接条件
join
	c
on
	a和c的连接条件
join
	d
on
	a和d的连接条件

        一条SQL中内连接和外连接可以混合。都可以出现！

        //找出每个员工的部门名称以及工资等级，要求显示员工名、薪资、部门名、薪资等级
select
	e.ename,e.sal,d.dname,s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal;
 
//找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级
select
	a.ename as '员工名',b.ename as '领导名',d.dname,
	a.sal,s.grade
from
	emp as a
left join
	emp as b
on
	a.mgr = b.empno
join
	dept as d
on
	a.deptno = d.deptno
join
	salgrade as s
on
	a.sal between s.losal and s.hisal
order by
	s.grade asc, a.sal desc;

    2. 子查询

      1. select语句中嵌套select语句，被嵌套的select语句称为子查询。

      2. 出现位置

        select
	...(select)
from
	...(select)
where
	...(select)
        • where子句中的子查询
        //找出比最低工资高的员工姓名和工资
//第一步：查询最低工资
select min(sal) from emp;
//第二步：找出>800的
select ename,sal from where sal > 800;
//第三步：合并
select ename,sal from emp where sal > (select min(sal) from emp);

        • from子句中的子查询

          //找出每个岗位的平均薪资的薪资等级
//第一步：找出每个岗位的平均工资（按照岗位分组求平均值）
select job,avg(sal) from emp group by job;
//第二步：克服心理障碍，把以上的查询结果当成一张真是存在的表t
select 
	t.*,s.grade
from
	t
join
	salgrade as s
on
	t.avg(sal) between s.losal and s.hisal;
//第三步：
select 
	t.*,s.grade
from
	(select job,avg(sal) as avgsal from emp group by job) t
join
	salgrade as s
on
	t.avgsal between s.losal and s.hisal;
          • 注意：from后面的子查询，可以将子查询的查询结果当做一张临时表（技巧）

        • select后面出现的子查询

          //找出每个员工的部门名称，要求显示员工名，部门名
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

          注意：

          • 对于select后面的子查询来说，这个子查询只能返回1条结果，多于1条就会报错

    3. union合并查询结果集

      • 要求两结果集的列数相同
      • union :得到两个查询结果的并集，并且自动去掉重复行。不会排序
      • union all:得到两个查询结果的并集，不会去掉重复行。也不会排序
      • intersect:得到两个查询结果的交集，并且按照结果集的第一个列进行排序
      • minus:得到两个查询结果的减集，以第一列进行排序
      • oracle要求结果集合并时，列和列的数据类型也要相同
      //查询工作岗位是mananager和salesman的员工
//方法一：
select ename,job from emp where job = 'manager' or job = 'salesman';
//方法二：
select ename,job from emp where job in('manager', 'salesman');
//方法二：
select ename,job from emp where job = 'manager'
union
select ename,job from emp where job = 'salesman';

      • union的效率要高些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。。。

      • 但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接

      • union把乘法变成了加法运算

        MySQL学习笔记（二）

    4. limit取部分数据

      1. 完整用法：limit startIndex, length
        • startIndex是起始下标，length是长度
        • 起始下标从0开始
      2. 缺省用法：limit 5;//这是取前5条

      将查询结果集的一部分取出来，通常使用在分页查询当中。

      分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差，

      可以一页一页的翻页看。

      //按照薪资降序，取出排名在前5的员工
select
	ename,sal
from
	emp
order by
	sal desc
limit 5;
//取出工资在3到5名的员工
select ename,sal from emp order by sal desc limit 2,3;
//取出工资排名在[5-9]名的员工
select ename,sal from emp order by sal desc limit 4, 5;

      注意：

      • mysal中limit在order by 之后执行！！！！！

    5. 分页

      每页显示3条记录

      第1页：limit 0,3

      第2页：limit 3,3

      第3页：limit 6,3

      第4页：limit 9,3

      每页显示pageSize条记录

      ​ 第pageNo页：limit (pageNo - 1) * pageSize , pageSize

//DQL语句大总结
select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...
//执行顺序
//1.from
//2.where
//3.group by
//4.having
//5.select
//6.order by
//7.limit..
上一篇：union / limit


下一篇：005、条件查询(where)、模糊查询(like)