首页
登录 | 注册

数据库MySQL--连接查询

例子文件1:https://files.cnblogs.com/files/Vera-y/myemployees.zip

例子文件2:https://files-cdn.cnblogs.com/files/Vera-y/girls.zip

连接查询:又称多表查询,当查询的字段来自多个表时,将会运用到连接查询

连接查询的分类:

1.按年代分类:

  sql92标准:92年推出的标准,仅仅支持内连接

语法: select 查询列表

    from 表

    where 连接条件、(筛选条件)

    (group by 分组)

    (having 筛选条件)

    (order by 排序列表)

  sql99标准:99年推出的标准,MySQL中99标准支持内连接,外连接(全外连接不支持),交叉连接

语法: select 查询列表

    from 表1 (连接类型)

    join 表2

    on 连接条件

    (where筛选条件)

    (group by 分组)

    (having 筛选条件)

    (order by 排序列表)

2.按功能分类:

  内连接:等值连接,非等值连接,自连接

  外连接:左外连接,右外连接,全外连接

  交叉连接

spl99 标准功能分类连接类型:

  内连接:inner  (可以省略)

  外连接:左外:left (outer)、右外:right(outer)、全外:full(outer)

  交叉连接:cross

壹:内连接

一、等值连接

1.简单基础的等值查询

例1.查询员工名和对应的部门名

sql92:

  SELECT last_name,department_name
  FROM employees,departments
  WHERE employees.department_id = departments.department_id;   

  # 注意两个表是通过什么连接的,这里是通过department_id连接的(又称键)

sql99:

  SELECT last_name,department_name
  FROM employees INNER JOIN departments
  ON employees.department_id = departments.department_id;

例2.查询员工名、工种号、工种名

SELECT last_name,employees.job_id,job_title
FROM employees,jobs      # 在这里可以给表起别名
WHERE employees.job_id = jobs.job_id;

(注:虽然可以起别名,但是一旦起了别名,使用到表名的地方就一定要用别名代替,否则报错

2. 加筛选条件的等值查询

 例1.查询有奖金的员工名、部门名 

sql92:

  SELECT last_name, department_name
  FROM employees,departments
  WHERE commission_pct IS NOT NULL
  AND employees.department_id=departments.department_id;

sql99:

  SELECT last_name,department_name
  FROM employees INNER JOIN departments
  ON employees.department_id=departments.department_id
  WHERE commission_pct IS NOT NULL;

例2.查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM departments,locations
WHERE departments.location_id=locations.location_id
AND city LIKE '_o%';

3.添加分组

例1.查询每个城市的部门个数 

sql92:

  SELECT COUNT(*),city
  FROM departments,locations
  WHERE departments.location_id=locations.location_id
  GROUP BY city;

sql99:

  SELECT COUNT(*),city
  FROM departments INNER JOIN locations
  ON departments.location_id=locations.location_id
  GROUP BY city;

例2.查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name,MIN(salary),departments.manager_id
FROM departments,employees
WHERE departments.department_id=employees.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,departments.manager_id;

可能出现的一个错误:https://www.cnblogs.com/Vera-y/p/10923850.html

3.添加排序

例1:查询每个工种的工种名和员工的个数,并且按员工个数排降序 

SELECT job_title,COUNT(*)
FROM jobs, employees
WHERE jobs.job_id=employees.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

例2.查询那个部门的员工个数>3的部门名和员工个数,并按个数降序

sql92:

  SELECT department_name,COUNT(*)
  FROM departments,employees
  WHERE departments.department_id=employees.department_id
  GROUP BY department_name
  HAVING COUNT(*)>3
  ORDER BY COUNT(*) DESC;

sql99:

  SELECT department_name,COUNT(*)
  FROM departments INNER JOIN employees
  ON departments.department_id=employees.department_id
  GROUP BY department_name
  HAVING COUNT(*)>3
  ORDER BY COUNT(*) DESC;

4.三表连接

例:查询员工名、部门名、和所在城市  

sql92:

  SELECT last_name,department_name,city
  FROM employees,departments,locations
  WHERE employees.department_id=departments.department_id
  AND departments.location_id=locations.location_id;

sql99:

  SELECT last_name,department_name,city
  FROM employees
  INNER JOIN departments ON employees.department_id=departments.department_id
  INNER JOIN locations ON departments.location_id=locations.location_id;

  (注:顺序问题:两个表一定要有连接条件才能让两个表相连)

二、非等值连接

案例:查询员工的工资和工资级别 

sql92:

  SELECT salary,grade_level
  FROM employees,job_grades
  WHERE salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;   # 这里用between and 限制了范围

sql99:  

  SELECT salary,grade_level
  FROM employees
  JOIN job_grades    # 省略了inner
  ON employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;

  

三、自连接

自个儿的表和自个儿的表连接

案例:查询姓名中包含k的员工名和上级领导的名称

sql92:

  SELECT e.employee_id, e.last_name,m.employee_id,m.last_name
  FROM employees AS e,employees AS m # 这里的别名用作区分为两个表
  WHERE e.manager_id=m.manager_id
  AND e.last_name LIKE '%k%';

sql99:

  SELECT e.employee_id, e.last_name,m.employee_id,m.last_name
  FROM employees AS e
  JOIN employees AS m       # 这里省略了inner
  ON e.manager_id=m.manager_id
  WHERE e.last_name LIKE '%k%';

贰:外连接

(sql99标准的外连接)

1、外连接:一个表中有,另一个表中没有的

   外连接的查询结果为主表中的所有记录

  若从表中有和主表匹配的,则显示匹配值

  若从表中没有和主表匹配的,则用null填充

  即:外连接结果=内连接结果+主表中有而从表中没有的记录

2、左外连接:left join 左边的是主表

   右外连接:right join 右边的是主表

   左外和右外交换两个表的顺序可以实现同样的效果

3、全外连接=内连接的结果+表1中有但表2中没有+表2中有表1中没有

4、交叉连接:笛卡尔乘积

例1.查询男朋友不在男神表的女神名    # 最终查询的为女神名来自与女神表,所以主表为女神表

左外连接:

  SELECT beauty.`name`,beauty.*
  FROM beauty
  LEFT OUTER JOIN boys
  ON beauty.boyfriend_id=boys.id
  WHERE boys.id IS NULL;        # 主键是不可能本身就为空的,所以只能是匹配后为空

右外连接:

  SELECT beauty.`name`,beauty.*
  FROM boys
  RIGHT OUTER JOIN beauty
  ON beauty.boyfriend_id=boys.id
  WHERE boys.id IS NULL; 

例2.查询那个部门没有员工     # 最终查询的是部门的,所以部门表为主表

左外连接:  

  SELECT departments.*, employees.employee_id
  FROM departments
  LEFT JOIN employees
  ON employees.department_id = departments.department_id
  WHERE employees.department_id IS NULL;

右外连接:

  SELECT departments.*, employees.employee_id
  FROM employees
  RIGHT JOIN departments
  ON employees.department_id = departments.department_id
  WHERE employees.department_id IS NULL;

例3.查询部门名为SAL 或IT的员工信息
  SELECT departments.department_name,employees.*
  FROM departments
  LEFT JOIN employees
  ON departments.department_id = employees.department_id
  WHERE departments.department_name IN('SAL','IT');

例4、交叉连接

  SELECT beauty.*,boys.*

  FROM beauty

  CROSS JOIN boys;

有关几个查询的小统计:

数据库MySQL--连接查询

 


相关文章

  • 学了很多乱七杂八的东西,但是依然停留在前端,在工作中一直和后端交流,但是不太了解数据库是怎么回事,为了加强学习,准备学习一些关于数据库相关的东西. 说起数据库可能会有很多很多,SQLServer.Oracle.Sybase等等等,还有就是要 ...
  • 快三个月没写博客了,一直在忙着准备面试和去面试的路上,所以没时间写,也没什么想写的.现在告一段落,就总结一波! 面经 本人真的是双非一本.为什么加“真的”?因为有的人也写着"双非一本,进入阿里",但是某电子科技大学,比9 ...
  • 高并发请求的缓存设计策略
    前几天,我司出了个篓子.当时正值某喜闻乐见的关键比赛结束,一堆人打开我司app准备看点东西,结果从来没有感受到过这么多关注量的该功能瞬间幸福到眩晕,触发了熔断,结果就是大量兴致冲冲打开app准备看该比赛结果的人被迫刷了十分钟三天前的野外跑酷 ...
  • .NET Core Dapper操作mysql数据库
    前言 现在ORM盛行,市面上已经出现了N款不同的ORM套餐了.今天,我们不谈EF,也不聊神马黑马,就说说 Dapper.如何在.NET Core中使用Dapper操作Mysql数据库呢,让我们跟随镜头(手动下翻)一看究竟. 配置篇 俗话说得 ...
  • Spring Boot入门(五):使用JDBC访问MySql数据库
    本系列博客记录自己学习Spring Boot的历程,如帮助到你,不胜荣幸,如有错误,欢迎指正! 在程序开发的过程中,操作数据库是必不可少的部分,前面几篇博客中,也一直未涉及到数据库的操作,本篇博客 就讲解下在Spring Boot中如何使用 ...
  • 查询数据库中所有表名 select table_name from information_schema.tables where table_schema='tools' and table_type='base table'; 查询指定 ...

2019 cecdns.com webmaster#cecdns.com
12 q. 0.069 s.
京ICP备10005923号