SQL数据库学习笔记-day2

b站教程：【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！ https://www.bilibili.com/video/BV1UE41147KC?p=18&vd_source=3c8189c0e8b70cedb5c70edc3d1b8684

内连接 INNER JOIN

JOIN (默认INNER JOIN)
可以省略INNER
（一个database里的）

SELECT order_id,o.customer_id,first_name,last_name
--有多个列同名时需要指出
FROM sql_store.orders o
-- 用o给orders重命名，以下可以用o代指orders
INNER JOIN customers 
ON sql_store.o.customer_id = sql_store.customers.customer_id

--JOIN后加其他表名，两个表需要有相同列，ON后

跨数据库连接 Joining Across Databases

在哪个数据库下，选中哪个数据库就是黑色加粗，可以不用在FROM里加前缀，如果要访问其他数据库需要加前缀名。
选中的另一种方式，在最上面加入语句USE sql_store;
只需要给不在当前数据库的表加前缀

USE sql_store;--选中
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
	ON oi.product_id = p.product_id

子连接 Self Joins

--同一个表中，自己的某列与另一列相关，例如本表，上司列对应的id是该表中其他列人员的id，建立内连接查询
USE sql_hr;

SELECT *
FROM employees e
JOIN employees m 
		ON e.reports_to = m.employee_id
--即会显示该员工的上司的具体信息在后面

优化一下查询结果

多表连接 Joining Multiple Tables

把一个表与其他多个表连接

USE sql_store;

SELECT *
FROM orders o
JOIN customers c
		ON o.customer_id = c.customer_id
JOIN order_statuses os
		ON o.status = os.order_status_id
--多个表就多个限制

复合连接条件 Compound Join Conditions

有时一列不能完全识别，由于有重复值，需要两列一起识别
composite primary key（复合主键）两者的结合唯一确定这笔订单

USE sql_store;

SELECT *
FROM order_items oi
JOIN order_item_notes oin
		ON oi.order_id = oin.order_id
        AND oi.product_id = oin.product_id
--复合主键来连接时，使用AND并列多个条件

隐式连接 Implicit Join Syntax

SELECT *
FROM orders o
JOIN customers c
		ON o.customer_id = c.customer_id
--Implpicit Join Syntax,上下相同的功能，下面为隐式连接
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

外连接 Outer Joins

LEFT JOIN和RIGHT JOIN都是简写，展开为LEFT OUTER JOIN和RIGHT OUTER JOIN

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
JOIN orders o
		ON o.customer_id = c.customer_id
ORDER BY c.customer_id
--此时只能看到有订单的顾客，有些没订单的，即不在orders表中的customers，未返回

SQL中有LEFT JOIN和RIGHT JOIN
使用LEFT JOIN则会返回所有customers的记录（即FROM后的表的所有记录），无论ON后面的条件是不是满足

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
--左连接
LEFT JOIN orders o
		ON o.customer_id = c.customer_id
ORDER BY c.customer_id

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
--右连接
RIGHT JOIN orders o
		ON o.customer_id = c.customer_id
ORDER BY c.customer_id
--得到的结果同内连接，即返回了所有在orders中的记录

多表外连接 Outer Join Between Multiple Tables

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
    
FROM customers c
LEFT JOIN orders o
		ON o.customer_id = c.customer_id
LEFT JOIN shippers sh
		ON o.shipper_id = sh.shipper_id
--两个左连接
ORDER BY c.customer_id

自外连接 Self Outer Joins

USE sql_hr;

SELECT 
	e.employee_id,
    e.first_name,
    m.first_name AS manager
    
FROM employees e
JOIN employees m
	ON e.reports_to = m.employee_id
    --该内连接只会返回有"上司"的人
    --修改-->LEFT JOIN

USING子句 The USING Clause

USE sql_store;
SELECT 
	c.customer_id,
    c.first_name
FROM orders o
JOIN customers c
		USING (customer_id)
		--ON o.customer_id = c.customer_id（同USING）

USE sql_store;
SELECT 
	c.customer_id,
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c
		USING (customer_id)
LEFT JOIN shippers sh
	USING (shipper_id)

但是USING关键字只能在不同表中列的名字完全一样才能用

USE sql_store;

SELECT *
FROM order_items oi
JOIN order_item_notes oin
		ON oi.order_id = oin.order_id
        AND oi.product_id = oin.product_id

优化：

USE sql_store;

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	USING(order_id,product_id)
--使用逗号隔开即可

自然连接 Natural Joins

通过自然连接，就不用打上具体列名了，数据库引擎会自己看着办，基于共同的列连接，即有相同名称的列

SELECT
	o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

交叉连接 Cross Joins

使用交叉连接结合或连接第一个表的每条记录和第二个表的每条记录
显示语法：

SELECT 
	c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p--显
ORDER BY c.first_name
--顾客表里的每条记录都会和产品表里的每条记录结合

隐式语法：

SELECT 
	c.first_name AS customer,
    p.name AS product
FROM customers c, products p--隐
ORDER BY c.first_name
--顾客表里的每条记录都会和产品表里的每条记录结合

联合 Unions

结合多张表的行

--例：基于同一个表格（也可以不同表格）
SELECT 
	order_id,
    order_date,
    'Active' AS status
FROM orders
WHERE order_date>='2019-01-01'
--上面返回2019年及之后的订单并赋值一列状态status

UNION	--通过UNION合并多段查询的记录

SELECT 
	order_id,
    order_date,
    'Acchived' AS status
FROM orders
WHERE order_date<'2019-01-01'
--上面返回2019年之前的订单并赋值一列状态status

也可以基于不同表格查询，将结果合并到一个结果集

SELECT first_name
FROM customers

UNION

SELECT name
FROM shippers
--此时输出的列名为：first_name

注意：想让查询返回的数量一定要一样(即SELECT后的列数量)