sql99语法

• 语法：

select 查询列表

from 表1 别名 【连接类型】

join 表2 别名

on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序列表】

• 内连接（同上）：连接类型是inner
• 外连接
  • 左外：left 【outer】
  • 右外：right【outer】
  • 全外：full 【outer】
• 交叉连接：cross
• 内连接：
  • 语法：

select 查询列表

from 表1 别名

inner join 表2 别名

on 连接条件

…

• • 分类：

等值连接

非等值连接

自连接

• • 特点：
    • 添加排序、分组、筛选
    • inner可以省略
    • 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
    • inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
  • 等值连接：
    • 查询员工名、部门名

SELECT
last_name,
department_name
FROM
employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id` ；

• • • 查询名字中包含e的给员工名和工种名

SELECT
last_name,
job_title
FROM
employees e
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE last_name LIKE "%e%" ;

• • • 查询部门个数>3的城市名和部门个数

SELECT
city,
COUNT(*) 部门个数
FROM
departments d
INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING 部门个数 > 3 ;

• • • 查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序排序

SELECT
department_name,
COUNT(*) 员工个数
FROM
departments d
INNER JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数 > 3
ORDER BY 员工个数 DESC ;

• • • 查询员工名、部门名、工种名，并按部门名降序

SELECT
last_name,
department_name,
job_title
FROM
employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
ORDER BY d.`department_id` DESC ;

 

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

SELECT
salary,
grade_level
FROM
employees e
INNER JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal`
AND g.`highest_sal` ;

• • • 查询每个工资级别>20的个数，并且按工资级别降序

SELECT
COUNT(*),
grade_level
FROM
employees e
INNER JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal`
AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY grade_level DESC ;

 

• • 自连接
    • 查询员工的名字、上级的名字

SELECT
e.last_name,
m.last_name
FROM
employees e
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id` ;

• • • 查询姓名中包含字符k的员工的名字、上级的名字

SELECT
e.last_name,
m.last_name
FROM
employees e
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE "%k%" ;

 

• 外连接
  • 应用场景：用于查询一个表中有，另一个表没有的记录
  • 特点：
    • 外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null
    • 外连接查询结果=内连接结果+主表中有而从表中没有的记录
    • 左外连接：left join左边的是主表
    • 右外连接：right join右边的是主表
    • 左外和右外交换两个表的顺序，可以实现同样的效果
    • 圈外链接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的
  • 查询没有男朋友的女神名

SELECT
b.name,
bo.*
FROM
beauty b
LEFT JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.`id` IS NULL ;

• • 查询哪个部门没有员工
  • 左外：

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

• • 右外：

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

 

• • 全外连接
    • mysql不支持
    • 案例：

SELECT
b.*,
bo.*
FROM
beauty b FULL
OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id ;

 

• • 交叉连接（也就是笛卡尔乘积）
    • 案例：

SELECT
b.*,
bo.*
FROM
beauty b
CROSS JOIN boys bo ;

 

• sql92 和 sql99 pk
  • 功能：sql99支持的较多
  • 可读性：sql99实现连接条件和筛选条件的分离，可读性较高
• 练习：
  • 查询编号>3的女神的男朋友信息，如果有则列出详细信息，如果没有，则用null填充

SELECT
a.id,
a.name,
b.*
FROM
beauty a
LEFT JOIN boys b
ON a.`boyfriend_id` = b.`id`
WHERE a.`id` > 3 ;

• • 查询哪个城市没有部门

SELECT
city,
d.*
FROM
departments d
RIGHT JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_id IS NULL ;

 

• • 查询部门名为SAL或IT的员工信息

SELECT
d.`department_name`,
e.*
FROM
departments d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` = 'SAL'
OR d.`department_name` = 'IT' ;

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