mysql>SELECT last_name, salary, grade_level
->FROM employees e,job_grades j
->WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
+-------------+----------+-------------+| last_name | salary | grade_level |+-------------+----------+-------------+| King |24000.00| E || Kochhar |17000.00| E || De Haan |17000.00| E || Hunold |9000.00| C || Ernst |6000.00| C || Austin |4800.00| B || Pataballa |4800.00| B || Lorentz |4200.00| B || Greenberg |12000.00| D || Faviet |9000.00| C || Chen |8200.00| C || Sciarra |7700.00| C || Urman |7800.00| C || Popp |6900.00| C || Raphaely |11000.00| D || Khoo |3100.00| B || Baida |2900.00| A || Tobias |2800.00| A || Himuro |2600.00| A || Colmenares |2500.00| A || Weiss |8000.00| C || Fripp |8200.00| C || Kaufling |7900.00| C || Vollman |6500.00| C || Mourgos |5800.00| B || Nayer |3200.00| B || Mikkilineni |2700.00| A || Landry |2400.00| A || Markle |2200.00| A || Bissot |3300.00| B || Atkinson |2800.00| A || Marlow |2500.00| A || Olson |2100.00| A || Mallin |3300.00| B || Rogers |2900.00| A || Gee |2400.00| A || Philtanker |2200.00| A || Ladwig |3600.00| B || Stiles |3200.00| B || Seo |2700.00| A || Patel |2500.00| A || Rajs |3500.00| B || Davies |3100.00| B || Matos |2600.00| A || Vargas |2500.00| A || Russell |14000.00| D || Partners |13500.00| D || Errazuriz |12000.00| D || Cambrault |11000.00| D || Zlotkey |10500.00| D || Tucker |10000.00| D || Bernstein |9500.00| C || Hall |9000.00| C || Olsen |8000.00| C || Cambrault |7500.00| C || Tuvault |7000.00| C || King |10000.00| D || Sully |9500.00| C || McEwen |9000.00| C || Smith |8000.00| C || Doran |7500.00| C || Sewall |7000.00| C || Vishney |10500.00| D || Greene |9500.00| C || Marvins |7200.00| C || Lee |6800.00| C || Ande |6400.00| C || Banda |6200.00| C || Ozer |11500.00| D || Bloom |10000.00| D || Fox |9600.00| C || Smith |7400.00| C || Bates |7300.00| C || Kumar |6100.00| C || Abel |11000.00| D || Hutton |8800.00| C || Taylor |8600.00| C || Livingston |8400.00| C ||Grant|7000.00| C || Johnson |6200.00| C || Taylor |3200.00| B || Fleaur |3100.00| B || Sullivan |2500.00| A || Geoni |2800.00| A || Sarchand |4200.00| B || Bull |4100.00| B || Dellinger |3400.00| B || Cabrio |3000.00| B || Chung |3800.00| B || Dilly |3600.00| B || Gates |2900.00| A || Perkins |2500.00| A || Bell |4000.00| B || Everett |3900.00| B || McCain |3200.00| B || Jones |2800.00| A || Walsh |3100.00| B || Feeney |3000.00| B || OConnell |2600.00| A ||Grant|2600.00| A || Whalen |4400.00| B || Hartstein |13000.00| D || Fay |6000.00| C || Mavris |6500.00| C || Baer |10000.00| D || Higgins |12000.00| D || Gietz |8300.00| C |+-------------+----------+-------------+107rowsinset (0.00 sec)
mysql>
# SQL92语法实现内连接：见上，略
# SQL92语法实现外连接：使用 +----------MySQL不支持SQL92语法中外连接的写法！
mysql>SELECT employee_id,department_name
->FROM employees e,departments d
->WHERE e.`department_id` = d.department_id(+); #左外连接
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')'at line 3
mysql>
#右外连接
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id`(+) = d.department_id;
3. SQL99语法实现多表查询
使用JOIN...ON子句创建连接的语法结构
可以使用 ON 子句指定额外的连接条件
3.1 内连接(INNER JOIN)的实现
语法：
# 其中 INNER 可以省略
SELECT 字段列表 FROM A表 INNERJOIN B表 ON 关联条件 WHERE 等其他子句;
例子：
SELECT last_name,department_name
FROM employees e INNERJOIN departments d
ON e.department_id = d.department_id;
SELECT last_name, department_name, city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;
3.2 外连接(OUTER JOIN)的实现
左外连接(LEFT OUTER JOIN)
语法：
# 实现查询结果是A
# 其中LEFT后面的OUTER可以省略
SELECT 字段列表 FROM A表 LEFTOUTERJOIN B表 ON 关联条件 WHERE 等其他子句;
举例：
SELECT last_name, department_name
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id;
右外连接(RIGHT OUTER JOIN)
语法：
# 实现查询结果是B
# 其中RIGHT后面的OUTER可以省略
SELECT 字段列表 FROM A表 RIGHTOUTERJOIN B表 ON 关联条件 WHERE 等其他子句;
举例：
SELECT last_name, department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id;
满外连接(FULL OUTER JOIN)
语法：
# 实现查询结果是A和B
SELECT 字段列表 FROM A表 FULLJOIN B表 ON 关联条件 WHERE 等其他子句;
举例：
SELECT last_name, department_name
FROM employees e FULLJOIN departments d
ON e.department_id = d.department_id;
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替
4. UNION的使用
合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集
语法格式：
SELECTcolumn,... FROM table1
UNION [ALL]
SELECTcolumn,... FROM table2
UNION操作符
UNION ALL操作符
5. 7种SQL JOINS的实现
5.1 如下图7种形式：
# 中图： 内连接
SELECT employee_id, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
# 左上图： 左外连接
SELECT employee_id, department_name
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id;
# 右上图： 右外连接
SELECT employee_id, department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id;
# 左中图：
SELECT employee_id, department_name
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id ISNULL;
# 右中图：
SELECT employee_id, department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id ISNULL;
# 左下图：满外连接
# 方式一： 左上图 UNIONALL 右中图
SELECT employee_id, department_name
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id
UNIONALLSELECT employee_id, department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id ISNULL;
# 方式二： 左中图 UNIONALL 右上图
SELECT employee_id, department_name
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id ISNULLUNIONALLSELECT employee_id, department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id;
# 右下图：左中图 UNION AL 右中图
SELECT employee_id, department_name
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id ISNULLUNIONALLSELECT employee_id, department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id ISNULL;
5.2 语法格式小结
左中图
#实现A - A∩B
select 字段列表
from A表 leftjoin B表
on 关联条件
where 从表关联字段 isnulland 等其他子句;
右中图
#实现B - A∩B
select 字段列表
from A表 rightjoin B表
on 关联条件
where 从表关联字段 isnulland 等其他子句;
左下图
#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 leftjoin B表
on 关联条件
where 等其他子句
unionselect 字段列表
from A表 rightjoin B表
on 关联条件
where 等其他子句;
#实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B）
#使用左外的 (A - A∩B) union 右外的（B - A∩B）
select 字段列表
from A表 leftjoin B表
on 关联条件
where 从表关联字段 isnulland 等其他子句
unionselect 字段列表
from A表 rightjoin B表
on 关联条件
where 从表关联字段 isnulland 等其他子句
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！