mysql连接查询

6. 连接查询

• 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
• 笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n
  • 发生原因：没有有效的连接条件
  • 如何避免：添加有效的连接条件
• 分类：
  • 按年代分类：
    • sql92标准：仅仅支持内连接
    • sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
  • 按功能分类：
    • 内连接
      • 等值连接
      • 非等值连接
      • 自连接
    • 外连接
      • 左外连接
      • 右外连接
      • 全外连接（mysql不支持）
    • 交叉连接
• sql92标准
  • 等值连接
    • 多表等值连接的结果为多表的交集部分
    • n表连接，至少需要n-1个连接条件
    • 多表的顺序没有要求
    • 一般需要为表起别名
    • 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
    • 查询女神名和对应的男神名：

SELECT
NAME,
boyname
FROM
boys,
beauty
WHERE beauty.boyfriend_id = boys.id ;

• • • 查询员工名和对应的部门名

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

• • 为表起别名
    • 提高语句的简洁度
    • 区分多个重名的字段
    • 注意：如果为表起了别名，则查询 的字段就不能使用原始的表明去限定
    • 查询员工名、工种号、工种名

SELECT
last_name,
e.`job_id`,
job_title
FROM
employees e,
jobs j
WHERE e.`job_id` = j.`job_id` ;

• • 两个表的顺序是否可以调换
    • 查询员工名、工种号、工种名

SELECT
last_name,
e.`job_id`,
job_title
FROM
jobs j ,
employees e
WHERE e.`job_id` = j.`job_id` ;

• • 可以加筛选
    • 查询有奖金的员工名、部门名

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

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

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

• • 可以加分组
    • 查询每个城市的部门个数

SELECT
COUNT(*) 个数,
city
FROM
departments d,
locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city ;

• • • 查询有将近的每个部门的部门名和部门的领导编号和该部门的最低工资

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

• • 可以加排序
    • 查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT
job_title,
COUNT(*) AS 个数
FROM
employees e,
jobs j
WHERE e.`job_id` = j.`job_id`
GROUP BY job_title
ORDER BY 个数 DESC ;

• • 可以实现三表连接：
    • 查询员工名、部门名和所在的城市

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

• • 非等值连接
    • 查询员工的工资和工资级别

SELECT
salary,
grade_level
FROM
employees e,
job_grades g
WHERE salary BETWEEN g.lowest_sal
AND g.highest_sal ;

• • 自连接
    • 查询 员工名和上级的名称

SELECT
e.employee_id,
e.last_name,
m.employee_id,
m.last_name
FROM
employees e,
employees m
WHERE e.`manager_id` = m.`employee_id` ;

• • 测试题：
    • 显示员工表的最大工资，工资平均值

SELECT
MAX(salary),
AVG(salary)
FROM
employees ;

• • • 查询员工表的employee_id，job_id，last_name，按department_id降序，salary升序

SELECT
employee_id,
job_id,
last_name
FROM
employees
ORDER BY department_id DESC,
salary ASC ;

• • • 查询员工表的job_id中包含a和e的，并且a在e的前面

SELECT
job_id
FROM
employees
WHERE job_id LIKE '%a%e%' ;

• • • 显示当前日期，以及去前后空格，截取子字符串的函数

select now();
select trim();
select substr(str, startIndex, [length])

原文链接：https://blog.csdn.net/qq_21579045/article/details/98111827