专栏首页用户7095785的专栏MySQL的单表多表查询

MySQL的单表多表查询

1.单表查询

#单表查询语法
    select <字段1,字段2....> from <表名>
        where <表达式>
        group by field 分组
        having 筛选
        order by fileld  排序
     limit 限制条数

#关键字的优先级
  from>where>group by>having>select>distinct>orderby>limit

#说明
  1.from      #找到表
  2.where     #通过where指定的约束条件,去表中提取记录
  3.group by  #进行分组,如果没有group by,则按整体为一组
  4.having   #将分组的结果进行过滤
  5.select   #执行select
  6.distinct   #去重
  7.order by  #进行排序
  8.limit    #限制结果的显示条数 

#查看帮助:help select

mysql> help select
Name: 'SELECT'
Description:
Syntax:
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [into_option]
    [FOR UPDATE | LOCK IN SHARE MODE]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

1.1.where约束条件

#where约束条件中可以使用

#1.比较运算符:>、<、 >=、 <>、!=

#例子:

#1.查看id大于5的员工姓名
mysql> select id,name from staff where id > 5;

#2.查看id小于且等于10的员工姓名
mysql> select id,name from staff where id <= 10;

#2.between 10 and 20 :值在10到20之间

#例子:

#1.查看工资在1万到2万之间的员工
mysql> select name,salary from staff where salary between 10000 and 20000;

#2.查看工资不在1万到2万之间的员工
mysql> select name,salary from staff where salary not  between 10000 and 20000;

#3.in(10,20,30) :值是10或20或30

#例子:

#1.查看工资是3000,或5000,或4000,或9000的员工
mysql> select name,salary from staff where salary=3000 or salary=5000 or salary=4000 or salary=9000;

#2.查看工资不是3000,3500,4000的员工
mysql> select name,staff from staff where salary not in(3000,3500,4000);

#4.like 'fdfdsf': parttern可以是%或_。 %表示任意多字符,_表示一个字符

#例子:

#查看wu后面有任意字符的列
mysql> select * from staff where name like 'wu%';

#5.逻辑运算符,在多个条件直接可以使用逻辑运算符 and(且) or(或) not(不)

#例子:

#1.查看岗位是IT部门,且薪资在10000或9000或30000的员工姓名、年龄
mysql> select name,age,salary from staff where post='IT' and salary=10000 or salary=9000 or salary=30000;

#2.查看岗位是IT部门,且薪资不在11000的员工姓名、年龄
mysql> select name,age,salary from staff where post='IT' and not salary=110000 

#练习

1. 查看岗位是teacher的员工姓名、年龄
        mysql> select name,age from staff where post='teacher';
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
        mysql> select name,age from staff where post='teacher' and age > 30;
3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
        mysql> select name,age,salary from staff where post='teacher' and salary betweeen 9000 and 10000
4. 查看岗位描述不为NULL的员工信息
        mysql> select * from staff where post_comment is not null;
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
        mysql> select name,age,salary from staff where post='teacher' and salary=10000 or salary=9000 or salary=30000;
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
        mysql> select name,age,salary from staff where post='teacher' and not salary=110000 or salary=9000 or salary=30000
7. 查看岗位是teacher且名字是wu开头的员工姓名、年薪
        mysql> select name,salary*12 from staff where post='teacher' and name like 'wu%';

1.2.group by分组查询

#分组:指的是将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或性别分组等

#注意1:分组是发生在where之后,即分组是基于where之后得到的记录而进行的

#注意2:进行分组后,如group by post,只能查看post字段,如果要查看组内信息,需要借助聚合函数

#为何要进行分组呢?

1.获取每个部门的员工数
2.获取每个部门的最高工资
3.获取男生人数和女生人数

#提示:如果先分组,必须要把全局的sql模块改为ONLY_FULL_GROUP_BY

#修改方法:

  1.登录进去改mysql>set global sql_mode='ONLY_FULL_GROUP_BY';

  2.进入/etc/my.cnf配置文件设置sql_mode='ONLY_FULL_GROUP_BY'

#设置之后查看

mysql> select @@global.sql_mode;
+--------------------+
| @@global.sql_mode  |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)

#设置分组之后只能查看当前字段,如果看全部字段就会报错,需要借助聚合函数查看

mysql> select * from staff group by post; #报错
mysql> select post from staff group by post; #查看当前字段不报错

#聚合函数

#提示:聚合函数聚合的是组的内容,如果没有分组,默认为一组

1.max()  #求最大值
2.min()  #求最小值
3.avg()  #求平均值
4.sum()    #求和
5.count()  #求总个数
6.group_concat()  #查字段

#例子:

#1.查询每个部门有多少个员工
select post,count(id) from staff group by post;

#2.查询每个部门的最高薪水
select post,max(salary) from staff group by post;

#3.查询每个部门的最低薪水
select post,min(salary) from staff group by post;

#4.查询每个部门的平均薪水
select post,avg(salary) from staff group by post;

#5.查询每个部门的所有薪水
select post,sum(salary) from staff group by post;

#练习

1. 查询岗位名以及岗位包含的所有员工名字
    mysql> select post,group_concat(name) from staff group by post;

2. 查询岗位名以及各岗位内包含的员工个数
    mysql> select post,count(1) from staff group by post;

3. 查询公司内男员工和女员工的个数
    mysql> select group_concat(sex) from staff;

4. 查询岗位名以及各岗位的平均薪资
    mysql> select post,avg(salary) from staff group by post;

5. 查询岗位名以及各岗位的最高薪资
    mysql> select post,max(salary) from staff group by post;

6. 查询岗位名以及各岗位的最低薪资
    mysql> select post,min(salary) from staff group by post;

1.3.having过滤

#注意点:

1.执行优先级从高到低:where > group by > having
2.where发生在分组group by之前,因而where中可以有任意字段,但是不能使用聚合函数
3.having发生在分组group by之后,因而having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数

#例子:

1.过滤工资大于10000的员工
mysql> select * from staff having salary>10000;

#练习

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
    select post,group_concat(name),count(1) from staff group by post having count(1) < 2;

2. 查询各岗位平均薪资大于10000的岗位名、平均工资
    select post,avg(salary) from staff group by post having avg(salary) > 10000;

3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from staff group by post having avg(salary) between 10000 and 20000;

1.4.order by查询排序

#提示:asc:升序,desc:降序

#按单列排序(默认是升序)

#例子:

#1.按年龄大小排序(默认是asc升序)
mysql> select id,name,age from employee order by age;

#2.按年龄排序,desc倒序,倒过来排
mysql> select id,name,age from employee order by age desc;

#按多列排序

#例子:

#先按照age(年龄)升序排序,如果年纪相同,则按照id降序
mysql> select id,name,age from staff order by age asc,id desc;

1.5.limit限制查询的记录条数

#例子:

#1.查看id列按升序排序前3列,默认从0开始,查询出第一条
mysql> select id,name from staff order by id asc limit 3;
+----+---------+
| id | name    |
+----+---------+
|  1 | guo     |
|  2 | ke      |
|  3 | wu      |
+----+---------+
3 rows in set (0.00 sec)


#2.查看第1到5列
mysql> select id,name from staff order by id asc limit 0,5;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | guo       |
|  2 | ke        |
|  3 | wu        |
|  4 | yan       |
|  5 | liu       |
+----+-----------+
5 rows in set (0.00 sec)
##从0开始,先查询出第一条,然后包含这一条在内往后查5条,也就是1-5

#3.查看6-10列
mysql> select id,name from staff order by id asc limit 5,5;
+----+------------+
| id | name       |
+----+------------+
|  6 | jing       |
|  7 | xin        |
|  8 | xiae       |
|  9 | 歪个        |
| 10 | 丫的        |
+----+------------+
5 rows in set (0.00 sec)
#从5开始,即先查询出第6条,然后包含在这一条在内让后查5条,也就是6-10

2.多表查询

#多表查询的语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

#数据准备:准备两张表,部门表(department)、员工表(employee)

create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('guo','male',18,200),
('ke','female',48,201),
('wu','male',38,201),
('liu','female',28,202),
('zhang','male',18,200),
('xiao','female',18,204)
;

#查看表结构
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | YES  |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| dep_id | int(11)               | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select * from department;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
4 rows in set (0.00 sec)

mysql> select * from employee;
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | guo   | male   |   18 |    200 |
|  2 | ke    | female |   48 |    201 |
|  3 | wu    | male   |   38 |    201 |
|  4 | liu   | female |   28 |    202 |
|  5 | zhang | male   |   18 |    200 |
|  6 | xiao  | female |   18 |    204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)

#提示:观察两张表,发现department表中id=203部门在employee中没有对应的员工,发现employee中id=6的员工在department表中没有对应关系

#查看两个表的交叉连接

mysql> select * from employee,department

2.1.外链接操作

#包括:内连接、左连接、右连接、全外连接

#1.内连接:符合条件查询,只连接匹配的行

#查询员工对应的部门
#找两张表共有的部分
mysql> select * from employee inner join department on employee.dep_id = department.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | guo   | male   |   18 |    200 |  200 | 技术         |
|  2 | ke    | female |   48 |    201 |  201 | 人力资源     |
|  3 | wu    | male   |   38 |    201 |  201 | 人力资源     |
|  4 | liu   | female |   28 |    202 |  202 | 销售         |
|  5 | zhang | male   |   18 |    200 |  200 | 技术         |
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

#2.左连接:优先显示左边记录

#找出左表所有员工信息,包括没有部门的员工
mysql> select * from employee left join department on employee.dep_id = department.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | guo   | male   |   18 |    200 |  200 | 技术         |
|  5 | zhang | male   |   18 |    200 |  200 | 技术         |
|  2 | ke    | female |   48 |    201 |  201 | 人力资源     |
|  3 | wu    | male   |   38 |    201 |  201 | 人力资源     |
|  4 | liu   | female |   28 |    202 |  202 | 销售         |
|  6 | xiao  | female |   18 |    204 | NULL | NULL         |
+----+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)

#3.右连接:优先显示右边记录

#以右表为准,找出所有部门信息,包括没有员工的部门
mysql> select * from employee right join department on employee.dep_id = department.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | guo   | male   |   18 |    200 |  200 | 技术         |
|    2 | ke    | female |   48 |    201 |  201 | 人力资源     |
|    3 | wu    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | liu   | female |   28 |    202 |  202 | 销售         |
|    5 | zhang | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)

#4.全外连接:使用union连接

#显示左右两个表全部记录
mysql> select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | guo   | male   |   18 |    200 |  200 | 技术         |
|    5 | zhang | male   |   18 |    200 |  200 | 技术         |
|    2 | ke    | female |   48 |    201 |  201 | 人力资源     |
|    3 | wu    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | liu   | female |   28 |    202 |  202 | 销售         |
|    6 | xiao  | female |   18 |    204 | NULL | NULL         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)

2.2.符合条件查询

#例一:以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门

mysql> select employee.name,department.name  from employee,department where employee.dep_id = department.id and  age > 25;
+------+--------------+
| name | name         |
+------+--------------+
| ke   | 人力资源      |
| wu   | 人力资源      |
| liu  | 销售         |
+------+--------------+
3 rows in set (0.01 sec)

#例二:以内连接的方式查询employee和department表,并且以age字段的升序方式显示。

mysql> select employee.name,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;
+------+--------------+
| name | name         |
+------+--------------+
| liu  | 销售         |
| wu   | 人力资源     |
| ke   | 人力资源     |
+------+--------------+
3 rows in set (0.00 sec)

2.3.子查询

1.子查询是将一个查询语句嵌套到另一个查询语句中
2.内层查询语句的查询结果,可以为外层查询语句提供查询条件
3.子查询中可以包含:IN,NOT IN,ANY,ALL,EXISTS和NOT EXISTS等关键字
4.还可以包含比较运算符:=,!=,>,<等

#1.带in关键字的子查询

#例子:

#1.查询平均年龄在25岁以上的部门名
#思路:看到查平均年龄就是要分组,然后先查询出员工表里面大于25岁的,再作为结果给外层查询语句作为条件
mysql> select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
+------+--------------+
| id   | name         |
+------+--------------+
|  201 | 人力资源     |
|  202 | 销售         |
+------+--------------+
2 rows in set (0.01 sec)
    
#2.查看技术部员工姓名
#思路;先查部门表找出技术部,再作为条件查员工表,
mysql> select name from employee where dep_id in (select id from department where name='技术');
+-------+
| name  |
+-------+
| guo   |
| zhang |
+-------+
2 rows in set (0.00 sec)

#3.查看不足1人的部门名
mysql> select name from department where id not in(select dep_id from employee grroup by dep_id);
+--------+
| name   |
+--------+
| 运营   |
+--------+
1 row in set (0.00 sec)

#2.带比较运算符的字查询

#例子:

#1.查询大于所有人平均年龄的员工名与年龄
#思路:先查询出所有人的平均年龄,然后再和原来的员工表进行比较
mysql> select name,age from employee  where age > (select avg(age) from employee);
+------+------+
| name | age  |
+------+------+
| ke   |   48 |
| wu   |   38 |
+------+------+
2 rows in set (0.00 sec)

#2.查询大于部门内平均年龄的员工名、年龄 
思路:
      (1)先对员工表(employee)中的人员分组(group by),查询出dep_id以及平均年龄。
       (2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。
       (3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。
mysql> select t1.name,t1.age from employee as t1
inner join
(select dep_id,avg(age) as avg_age from employee group by dep_id) as t2
on t1.dep_id = t2.dep_id 
where t1.age > t2.avg_age;

#3.带EXISTS关键字的子查询

#exists关键字表示存在,使用exists关键字时,内层查询不返回查询的记录,而是返回一个真假值,True或False,当返回True时,外层查询语句将进行查询,反之不进行查询

#例子:

#1.查询部门表里面有id=200的才会查询出外层的
mysql> select * from employee where exists (select id from department where id = '200');
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | guo   | male   |   18 |    200 |
|  2 | ke    | female |   48 |    201 |
|  3 | wu    | male   |   38 |    201 |
|  4 | liu   | female |   28 |    202 |
|  5 | zhang | male   |   18 |    200 |
|  6 | xiao  | female |   18 |    204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)

#2.如果查询部门表里面没有id=204的,所有不会查询外层的
mysql> select * from employee where exists (select id from department where id =204);
Empty set (0.00 sec)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • CentOS7.5源码编译安装mysql5.7.29

    #下载源码包,需要注意的是mysql5.7 编译安装需要boost 库,可以在官网下载含boost的源码包

    老油条IT记
  • MySQL的表操作

    #前言:我们说过,库相当于一个文件夹,表相当于文件夹里的一个个文件,表里面的一条记录相当于一行内容,表中的一条记录有对应的标题,称为表的字段

    老油条IT记
  • 数据库及MySQL概述

     DQL全称data query language,称为数据检索语句,作用是从表中获得数据

    老油条IT记
  • mysql学习总结04 — SQL数据操作

    mysql 中 SELECT 命令类似于其他编程语言的 print 或 write,可用来显示字符串、数字、数学表达式的结果等

    CS逍遥剑仙
  • 挽救数据库性能的30条黄金法则

    1. 优化查询,应尽量避免全表扫描,应该在用于检索数据和排序数据的字段上建立索引,如where子句用于搜索,order by子句用于排序,所以在这两个子句涉及到...

    蒙娜丽宁
  • Docker 介绍、核心概念、常用命令

    Docker支持将软件编译成一个镜像;然后在镜像中各种软件做好配置,将镜像发布出去,其他使用者可以直接使用这个镜像。运行中的这个镜像称为容器,容器启动后非常快速...

    桑鱼
  • docker--docker compose 编排工具

    根据前面所学的知识可知,想要使用Docker部署应用,就要先在应用中编写Dockerfile 文件来构建镜像。同样,在微服务项目中,我们也需要为每一个服务编写D...

    eadela
  • 虚拟机Docker安装MySQL5.7并配置宿主机访问

    Windows下VMware的Linux的Docker安装MySQL5.7,并配置Windows下可连接该MySQL。

    无道
  • 【漫画】我和产品经理真心相爱,惨遭家人反对

    正是金九银十跳槽季,为大家收集了2019年最新的面试资料,有文档、有攻略、有视频。有需要的同学可以在公众号【Java知己】,发送【面试】领取最新面试资料攻略!暗...

    淡定的蜗牛
  • Kubernetes(9:数据)

    emptyDir是Host上创建的临时目录,其优点是能够方便地为Pod中的容器提供共享存储,不需要额外的配置。它不具备持久性,如果Pod不存在了,emptyDi...

    用户5760343

扫码关注云+社区

领取腾讯云代金券