SQL语句:连接查询

连接查询:

  • 也叫多表查询。常用于查询字段来自于多张表
  • 如果直接查询两张表,将会得到笛卡尔积

mysql> select name, dept_name from employees, departments;
  • 通过添加有效的条件可以进行查询结果的限定

 mysql> select name, dept_name from employees, departments where employees.dept_id=departments.dept_id;

连接分类

按功能分类

  • 内连接(重要)
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接(重要)
    • 右外连接(重要)
    • 全外连接(mysql不支持,可以使用UNION实现相同的效果)
  • 交叉连接

按年代分类

  • SQL92标准:仅支持内连接
  • SQL99标准:支持所功能的连接

SQL99标准多表查询

  • 语法格式

SELECT 字段... 
FROM 表1 [AS] 别名 [连接类型]
JOIN 表2 [AS] 别名
ON 连接条件
WHERE 分组前筛选条件
GROUP BY 分组
HAVING 分组后筛选条件
ORDER BY 排序字段

内连接

  • 语法格式

select 查询列表
from 表1 别名
inner join 表2 别名 on 连接条件
inner join 表3 别名 on 连接条件
[where 筛选条件]
[group by 分组]
[having 分组后筛选]
[order by 排序列表]

等值连接

  • 查询每个员工所在的部门名

mysql> select name, dept_name
    -> from employees
    -> inner join departments
    -> on employees.dept_id=departments.dept_id;
  • 查询每个员工所在的部门名,使用别名

mysql> select name, dept_name
    -> from employees as e
    -> inner join departments as d
    -> on e.dept_id=d.dept_id;
  • 查询每个员工所在的部门名,使用别名。两个表中的同名字段,必须指定表名

mysql> select name, d.dept_id, dept_name
    -> from employees as e
    -> inner join departments as d
    -> on e.dept_id=d.dept_id;
  • 查询11号员工的名字及2018年每个月工资

mysql> select name, date, basic+bonus as total
    -> from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018 and e.employee_id=11;
  • 查询2018年每个员工的总工资

mysql> select name, sum(basic+bonus) from employees
    -> inner join salary
    -> on employees.employee_id=salary.employee_id
    -> where year(s.date)=2018
    -> group by name;
  • 查询2018年每个员工的总工资,按工资升序排列

mysql> select name, sum(basic+bonus) as total from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018
    -> group by name
    -> order by total;
  • 查询2018年总工资大于30万的员工,按工资降序排列

 mysql> select name, sum(basic+bonus) as total from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018
    -> group by name
    -> having total>300000
    -> order by total desc;

非等值连接

附:创建工资级别表

创建表语法:

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

创建工资级别表:

id:主键。仅作为表的行号
grade:工资级别,共ABCDE五类
low:该级别最低工资
high:该级别最高工资

mysql> use nsd2021;
mysql> create table wage_grade
    -> (
    -> id int,
    -> grade char(1),
    -> low int,
    -> high int,
    -> primary key (id));

向表中插入数据:

  • 语法:

INSERT INTO 表名称 VALUES (值1, 值2,....);

向wage_grade表中插入五行数据:

mysql> insert into wage_grade values
    -> (1, 'A', 5000, 8000),
    -> (2, 'B', 8001, 10000),
    -> (3, 'C', 10001, 15000),
    -> (4, 'D', 15001, 20000),
    -> (5, 'E', 20001, 1000000);
  • 查询2018年12月员工基本工资级别

mysql> select employee_id, date, basic, grade
    -> from salary as s
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date)=2018 and month(date)=12;
  • 查询2018年12月员工各基本工资级别的人数

mysql> select grade, count(*)
    -> from salary as s
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date)=2018 and month(date)=12
    -> group by grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
| A     |       13 |
| B     |       12 |
| C     |       30 |
| D     |       32 |
| E     |       33 |
+-------+----------+
5 rows in set (0.00 sec)
  • 查询2018年12月员工基本工资级别,员工需要显示姓名

mysql> select name, date, basic, grade
    -> from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date)=2018 and month(date)=12;

自连接

  • 要点:

    • 将一张表作为两张使用
    • 每张表起一个别名
  • 查看哪些员的生日月份与入职月份相同

mysql> select e.name, e.hire_date, em.birth_date
    -> from employees as e
    -> inner join employees as em
    -> on month(e.hire_date)=month(em.birth_date)
    -> and e.employee_id=em.employee_id;
+-----------+------------+------------+
| name      | hire_date  | birth_date |
+-----------+------------+------------+
| 李玉英    | 2012-01-19 | 1974-01-25 |
| 郑静      | 2018-02-03 | 1997-02-14 |
| 林刚      | 2007-09-19 | 1990-09-23 |
| 刘桂兰    | 2003-10-14 | 1982-10-11 |
| 张亮      | 2015-08-10 | 1996-08-25 |
| 许欣      | 2011-09-09 | 1982-09-25 |
| 王荣      | 2019-11-14 | 1999-11-22 |
+-----------+------------+------------+
7 rows in set (0.00 sec)

外连接

  • 常用于查询一个表中有,另一个表中没有的记录

  • 如果从表中有和它匹配的,则显示匹配的值

  • 如j要从表中没有和它匹配的,则显示NULL

  • 外连接查询结果=内连接查询结果+主表中有而从表中没有的记录

  • 左外连接中,left join左边的是主表

  • 右外连接中,right join右边的是主表

  • 左外连接和右外连接可互换,实现相同的目标

左外连接

  • 语法

SELECT tb1.字段..., tb2.字段
FROM table1 AS tb1
LEFT OUTER JOIN table2 AS tb2 
ON tb1.字段=tb2.字段
  • 查询所有部门的人员以及没有员工的部门

mysql> select d.*, e.name
    -> from departments as d
    -> left outer join employees as e
    -> on d.dept_id=e.dept_id;

右外连接

  • 语法

SELECT tb1.字段..., tb2.字段
FROM table1 AS tb1
RIGHT OUTER JOIN table2 AS tb2 
ON tb1.字段=tb2.字段
  • 查询所有部门的人员以及没有员工的部门

mysql> select d.*, e.name
    -> from employees as e
    -> right outer join departments as d
    -> on d.dept_id=e.dept_id;

交叉连接

  • 返回笛卡尔积
  • 语法:

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
  • 查询员工表和部门表的笛卡尔积

mysql> select name, dept_name
    -> from employees
    -> cross join departments;



作者:一个小运维
链接:https://www.jianshu.com/p/6cd2c076eb28

相关资源:

redis哨兵配置文件 - Dcsdn 

GitOps 初探 - Dcsdn 

rabbitmq入门和实战 - Dcsdn

  • 10
    点赞
  • 82
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值