ORDER/JOIN/LEFT&RIGHT/USING

1.SELECT * FORM

primary keys

多个条件排序,先按照状态排序，后按照firstname
ORDER BY state，first_name DESC

SELECT * FROM WHERE ID=2 ORDER BY Q%A DESC

2.JOIN

SELECT * FROM orders JOIN customers ON order.customer_id =customers.customer_id;

还可以简化
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

注意如果当前是另一个数据，当你想要SELECT 其他数据库的表单时，应该使用前缀

1. 复杂主键表——合并表单

比如说，超市中订单号Order_id为2的订单买了三个产品，分别对应三个数量和三个单价
怎样只让一个Order_id显示三个产品，并不是显示三条记录

SELECT * FROM orders o JOIN customers c ON

尽量不要使用联合语句如(因为要使用WHERE)
SELECT * FROM orders o，customers c WHERE o.customer_id=c.customer_id
尽量使用显示语法JOIN … ON

4.INNER JOIN /OUTER JOIN

INNER JOIN 就是
SELECT c.customer_id,c.first_name,o.order_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id ORDER BY c.customer_id
但是只能联合符合条件的记录

使用外部连接 LEFT JOIN/RIGHT JOIN
左连接：无论是否满足条件，都会返回所有的，但会返回是否正确（不正确返回NULL）

右连接：则是会返回所有右表表单的记录

5.USING 语句—->替换ON
eg:
SELECT
o.order_id,
c.first_name
FROM orders o
JOIN customers c
—-ON o.customer_id=c.customer_id
USING(customer_id)

如果有两个JOIN条件
可以这样使用USING(name相同)
SELECT
o.order_id,
c.first_name
FROM orders o
JOIN customers c
—-ON o.customer_id=c.customer_id
—JOIN customers c
—-ON o.product_id=c.product_id
USING(customer_id,product_id)

6.NATURAL JOIN——有点危险，不鼓励使用
SELECT *
FROM orders o
NATURAL JOIN customers c

7.交叉连接
SELECT
c.first_name AS customer,
p.name AS product
FORM customers c
CROSS JOIN products p
ORDER BY c.firsr_name
如果是显示连接

SELECT
c.first_name AS customer,
p.name AS product
FROM customers c,orders o
ORDER BY c.first_name

8.添加判断—-可以利用UNION
1)目标是19年之后设定状态为Active，之前为Archived，添加一列状态
SELECT
order_id,
order_date,
‘Active’ AS status
FROM orders
WHERE order_date>=’2019-01-01’
UNION
SELECT
order_id,
order_date,
‘Archived’ AS status
FROM orders
WHERE order_date<=’2019-01-01’

9.注意会使用UNION前方的第一个名字
SELECT name
FROM shippers
UNION
SELECT first_name
FROM customers

出来的表的列名就是name