学习MySql第四天

复习

where

and or like < > <= >= != <> between and is not in
like

% 0个,一个,多个字符 _ 仅代替一个字符
order by

asc desc
limit
- limit num1,num2
- num1---从第几条数据开始
- num2---显示多少条数据
- 一页显示8条,第三页 limit 16,8
常用函数
- concat -日期函数
  - now()
  - curdate()
  - curtime
  - date()
  - time()
  - extract(from 时间)
    - %c
    - %m
    - %y
    - %Y
    - %d
    - %h
    - %H
    - %i
    - %s
- strtodate
- ifnull(参数1,参数2) comm null--->0
6.聚合函数
- sum(列)
- avg(列)
- count() * 1 符合where 条件的有多少条目总数
- max()
- min()

7.练习

18.案例：查询品牌是联想,且价格在40000以上的商品名称和价格

select title,price 
from t_item
where title like '%联想%' and price >40000;

19.案例：查询品牌是三木,或价格在50以下的商品名称和价格

select title,price
from t_item
where title like '%三木%' or price <50;

20.案例：查询品牌是三木、广博、齐心的商品名称和价格

selectd title,price
from t_item
where title like '%三木%' or title like '%广博%' or title like '%齐心%';

21.案例：查询品牌不是联想、戴尔的商品名称和价格

select title,price
from t_itme
where title not like '%联想%' and title not like '%戴尔%';

22.案例：查找品牌是联想且价格大于10000的电脑名称

select title
from t_item
where title like '%联想%' and price >10000 and title like '%电脑%';

23.案例：查询联想或戴尔的电脑名称列表

select title
from t_item
where (title like '%联想%' or title like '%戴尔%') and title like '%电脑%';

24.案例：查询联想、戴尔、三木的商品名称列表

select *
from t_item
where title like '%联想%' or title like '%戴尔%' or title like '%三木%';

25.案例：查询不是戴尔的电脑名称列表

select title
from t_item
where title not like '%戴尔%' and title like '%电脑%';

26.案例：查询所有是记事本的商品品牌、名称和价格

select item_type,title,price
from t_item
where title like '%记事本%'

distinct
- 查询emp表中所有的职位(去重)
  
  select distinct job from emp;
- 查询emp表中部门编号(去重)
  
  select distinct deptno from emp;

分组查询

- 查看每个部门的平均工资--错误演示

select avg(sal),deptno 
from emp
order by deptno;

select avg(sal),deptno 
from emp
group by deptno;

select mgr,count(*)
from emp
group by mgr;

分组查询通常和聚合函数一起使用
一般情况下,查询字段中出现聚合函数和普通列,一起查询的时候,那么分组的条件就是普通列
当select子句中含有聚合函数时,凡是不在聚合函数中的其他单独字段,都必须出现在group by子句中.
group by子句要写在order by之前,where后
group by 可以根据多个字段分组
- 查看同部门同职位的平均工资
  
  select deptno,job,avg(sal) from emp group by deptno,job;
  - 查询部门平均工资 select deptno avg(sal) from emp group by deptno;
  - 查询每个领导有多少个员工,显示领导id和员工数量
  select mgr,count(*) from emp group by mgr;

练习

1.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。

    select deptno,count(*),sum(sal)
    from emp
    group by deptno
    order by count(*),sum(sal) desc;


    2.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。

    select deptno,avg(sal),min(sal),max(sal)
    from emp
    where sal>=1000 and sal<=3000
    group by deptno
    order by avg(sal);

    3.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列

    select count(*),sum(sal),avg(sal),min(sal),job
    from emp
    where mgr is not null
    group by job
    order by count(*) desc,avg(sal);

    4.案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资

        select empno,ename,job,sal
        from emp
        where sal between 1000 and 3000;

    5.案例：查询emp表中奖金在500~2000之间所有员工的编号，姓名，工资以及奖金

        select empno,ename,sal,comm 
        from emp
        where comm between 500 and 2000;

    6.案例：查询员工的编号是7369，7521，XXXX

        select * from emp
        where empno in(7369,7521,XXXX);  

    7.案例：查询emp表中，职位是ANALYST，

        select * from emp
        where job='ANALYST'

    8.案例：查询emp表中职位不是ANALYST,

        select * from emp
        where job!='ANALYST'

练习

-- 查询出所有分类商品所对应的库存总量

    select category_id,sum(num)
    from t_item
    group by category_id;

-- 查询出所有分类商品所对应的平均单价

    select category_id,avg(price)
    from t_item
    group by category_id;

-- 1. 每个部门的人数

        select deptno,count(*)
        from emp
        group by deptno;

-- 2. 每个部门中，每个主管的手下人数

    select deptno,mgr,count(*)
    from emp
    group by deptno,mgr;

-- 3. 每种工作的平均工资

    select job,avg(sal)
    from emp
    group by job;

-- 提高题 4. 每年的入职人数

    select count(*),extract(year from hiredate) year
    from emp
    group by year;

有条件分组统计

HAVING 子句

错误演示

- 查询部门的平均工资,前提是该部门的平均工资高于2000

    select avg(sal),deptno
    from emp
    where avg(sal)>2000
    group by deptno;

正确效果

select avg(sal),deptno
    from emp
    group by deptno
    having avg(sal)>2000;

学习MySql第四天

group by 使用having过滤条件
在sql语句中添加having子句的原因,是因为where关键字无法与聚合函数一起使用
where条件用于过滤行数,having条件用于过滤分组数量

执行顺序,首先执行where,然后执行group by,根据一个或多个列进行分组,之后执行having.对分组以后的数据再次过滤.最后执行排序order by

-- 查询所有分类商品所对应的库存总量中,高于1000的总量

    select sum(num),category_id
    from t_item
    group by category_id
    having sum(num)>1000;


-- 查询所有分类商品所对应的平均单价中,低于100的均价

    select avg(price),category_id
    from t_item
    group by category_id
    having avg(price)<=100;

-- 查询编号238和编号917分类商品的平均单价

    select avg(price),category_id
    from t_item
    group by category_id
    having category_id in (238,917);

    select avg(price),category_id
    from t_item
    where category_id in (238,917)
    group by category_id;

总结,having子句经常跟聚合函数一起使用,如果没有使用到聚合函数,要注意是否可以写在where中,如果可以写在where,优先使用where过滤

课堂练习

1.案例：查询emp表中，每个部门的平均工资高于2000的部门的编号,部门的人数,平均工资,  最后根据平均工资进行升序排列。

    select deptno,count(*),avg(sal)
    from emp
    group by deptno
    having avg(sal)>2000
    order by avg(sal);

2.案例：查询emp表中名字中不是以'K'开头的信息,每个部门的最低工资高于1000的部门的编号，工资总和,平均工资以及最低工资，最后根据平均工资进行升序排列。

    select deptno,sum(sal),avg(sal),min(sal)
    from emp
    where ename not like 'K%'
    group by deptno
    having min(sal)>1000
    order by avg(sal) asc;

3.案例：查询emp表中部门编号是10,30号部门的员工,每个职业的最高工资低于5000的职业的名称,人数，平均工资，最高工资，最后根据人数进行升序排列，如果人数一致，根据最高工资进行降序排列。

    select job,count(*),avg(sal),max(sal)
    from emp
    where deptno in (10,30)
    group by job
    having max(sal)<=5000
    order by count(*),max(sal) desc;


4.案例：查询emp表中，每个部门的编号，人数，工资总和，最高工资以及最低工资，过滤掉最高工资是5000的部门，根据部门的人数进行升序排列，如果人数一致，则根据最高工资进行降序排列。

    select deptno,count(*),sum(sal),max(sal),min(sal)
    from emp
    group by deptno
    having max(sal)!=5000
    order by count(*),max(sal) desc;

5.案例：查询emp表中工资在1000~3000之间的员工信息，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，按照平均工资进行升序排列
    select deptno,sum(sal),avg(sal)
    from emp
    where sal>=1000 and sal<=3000
    group by deptno
    having avg(sal)>=2000
    order by avg(sal);

6.案例：查询emp表中名字不是以‘S’开头，每个职位的名字，人数，工资总和，最高工资，过滤掉工资是3000的职位，根据人数进行升序排列，如果人数一致，根据工资总和进行降序排列。

    select job,count(*),sum(sal),max(sal)
    from emp
    where ename not like 'S%' and sal!=3000
    group by job
    order by count(*) ,sum(sal) desc;


7.案例：查询emp表的信息，每个职位的名称，人数，平均工资，最低工资，过滤掉平均工资是3000的职位信息，根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列

    select job,count(*),avg(sal),min(sal)
    from emp
    group by job
    having avg(sal)!=3000
    order by count(*) desc,avg(sal);

子查询

MySQL子查询

子查询是指,在DML语句,嵌套了另外一个查询(DQL)语句
某些DDL也可以使用子查询
子查询语句,称为内部查询,而包含子查询的查询语句,称为外部查询
常用的子查询会出现两种
- 外部查询的where子句使用子查询
- 子查询在from后面,用子查询的结果集充当一张表
子查询可以在表达式的任何地方使用,但是必须在括号中关闭

子查询可以嵌套在另外一个子查询中

1.案例：拿最低工资的员工信息

    select min(sal) from emp

    select * 
    from emp
    where sal=(select min(sal) from emp);

2.案例：工资多于平均工资的员工信息

    select avg(sal) from emp;

    select *
    from emp
    where sal>(select avg(sal) from emp);

3.案例：最后入职的员工信息
    select empno,ename,hiredate
    from emp
    where hiredate=(select max(hiredate) from emp);

4.案例：查询出有商品的 分类信息

    select distinct category_id from t_item;

    select *
    from t_item_category
    where id in (select distinct category_id from t_item);

5.案例：查询工资高于20号部门最高工资的员工的所有信息

    select max(sal)
    from emp
    where deptno=20;

    select *
    from emp
    where sal>(select max(sal)
               from emp
               where deptno=20);

6.案例：查询emp表中姓名是‘KING’所属的部门的编号，名称

    select deptno 
    from emp
    where ename='KING';

    select deptno,dname 
    from dept
    where deptno=(select deptno 
                  from emp
                  where ename='KING');


7.案例：查询部门名称是SALES的部门下所有员工的编号，姓名，职位，以及所属部门的编号

    select empno,ename,job,deptno
    from emp
    where deptno=(select deptno
                  from dept
                  where dname='SALES');

8.案例：查询部门地址是DALLAS的部门下所有员工的所有信息(查询在DALLAS工作的所有员工信息)

    select *
    from emp
    where deptno=(select deptno 
                  from dept
                  where loc='DALLAS');

9.案例：查询跟JONES同样工作的员工的所有信息（包含JONES）

    select *
    from emp
    where job=(select job from emp
               where ename='JONES');

不包含JONES

    select *
    from emp
    where job=(select job from emp
               where ename='JONES') and ename <>'JONES';

关联查询数据

从多张表中查询相应记录信息emp.deptno dept.deptno
关联查询的重点在于这些表中记录的对应关系,这个关系也称为连接条件(关联条件)
查看每个员工的名字以及所在部门的名字

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
如果不写关联关系

select e.ename,d.dname from emp e,dept d;

笛卡尔积

当多表关联时,如果没有写关联条件,返回的结果集是这几张表条目数的乘积,这个乘积就叫做笛卡尔积
多数情况下,笛卡尔积是无意义的

非常耗费资源,要尽量避免

1.查看在new york工作的员工

    select e.ename,d.loc
    from emp e,dept d
    where e.deptno=d.deptno and d.loc='NEW YORK';

2.查看工资高于3000的员工，名字，工资，部门名，所在地

select e.ename,e.sal,d.dname,d.loc
from emp e,dept d       
where e.deptno=d.deptno and e.sal>3000;

等值连接/内连接

语法

select * from A,B where A.某字段=B.某字段;
select * from A join B on A.某字段=B.某字段;
完整版:
select * from A [inner] join B on A.某字段=B.某字段;

1.查看在new york工作的员工

    select e.ename,d.loc
    from emp e,dept d
    where e.deptno=d.deptno and d.loc='NEW YORK';

    select e.ename,d.loc
    from emp e join dept d
    on e.deptno=d.deptno
    where d.loc='NEW YORK';

2.查看工资高于3000的员工，名字，工资，部门名，所在地

    select e.ename,e.sal,d.dname,d.loc
    from emp e join dept d
    on e.deptno=d.deptno
    where e.sal>3000;

不满足连接条件的记录是不会在关联查询中被查询出来的

select e.ename,e.sal,d.dname,d.loc,d.deptno
    from emp e join dept d
    on e.deptno=d.deptno;

左外连接

以join左侧表作为基准表(驱动表--所有数据都会被显示出来,不管是否符合连接条件),那么当该表中某条记录不满足连接条件时,来自右表的字段全部为null

语法 select * from A left join B on 连接条件;

select e.ename,e.sal,d.dname,d.loc,d.deptno
from dept d left join emp e
on e.deptno=d.deptno;

右外连接

以join右侧表作为基准表(驱动表--所有数据都会被显示出来,不管是否符合连接条件),那么当该表中某条记录不满足连接条件时,来自左表的字段全部为null

语法 select * from A right join B on 连接条件;

select e.ename,e.sal,d.dname,d.loc,d.deptno
from emp e right join dept d
on e.deptno=d.deptno;

关联查询数据案例

代码实践

-- 查询出所有可以匹配的商品分类及商品数据

    select *
    from t_item t1 join t_item_category t2
    on t1.category_id=t2.id;    

-- 查询出所有的分类,以及与之匹配的商品

    select *
    from t_item t1 right join t_item_category t2
    on t1.category_id=t2.id;



-- 查询出所有的商品,以及与之匹配的分类

    select *
    from t_item t1 left join t_item_category t2
    on t1.category_id=t2.id;

强化练习

练习

每个部门的人数,根据人数排序
每个部门中，每个主管的手下人数
每种工作的平均工资
每年的入职人数
少于等于3个人的部门
拿最低工资的员工信息
只有一个下属的主管信息
平均工资最高的部门编号
下属人数最多的人，查询其个人信息
拿最低工资的人的信息
最后入职的员工信息
工资多于平均工资的员工信息
查询员工信息，部门名称
员工信息，部门名称，所在城市
DALLAS 市所有的员工信息
按城市分组，计算每个城市的员工数量
查询员工信息和他的主管姓名
员工信息，员工主管名字，部门名
员工信息，部门名，和部门经理
员工和他所在部门名
案例：查询emp表中所有员工的编号，姓名，职位，工资以及工资的等级，根据工资的等级进行升序排列
案例：查询emp表中所有员工的编号，姓名，职位，工资以及该员工上级领导的编号，姓名，职位，工资
案例：查询emp表中名字中没有字母'K'的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址 select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e left join dept d on e.deptno=d.deptno where e.ename not like '%K%';
案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资
案例：查询emp表中所有员工的编号，姓名，职位，工资以及工资的等级，该等级的最低工资，按照员工的编号进行升序排列。 select e.empno,e.ename,e.job,e.sal,s.grade,s.losal from emp e left join salgrade s on e.sal between s.losal and s.hisal order by e.empno;

学习MySql第四天

复习

分组查询

练习

练习

有条件分组统计

HAVING 子句

课堂练习

子查询

MySQL子查询

关联查询数据

笛卡尔积

等值连接/内连接

左外连接

右外连接

关联查询数据案例

强化练习

练习

继续阅读

2022秋招面试总结（cpp+java+测开）百度测开一面字节后端一面虾皮后端一面虾皮后端二面

数据库之DDL操作数据库DDL操作数据库DDL操作数据表

数据库之DQL操作数据库

mysql优化（sql优化）

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

宝塔面板mysql恢复2018.1.8更新

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

SQL语言基础：常用的数据查询语句

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

mysql使用source命令导入.sql文件