SQL

SQL基础

选择语句

SELECT *
FROM customers
WHERE customer_id = 1
ORDER BY first_name

选择子句

SELECT 
    first_name, 
    last_name, 
    points, 
    points + 10, 
    points / 10 + 100
    (points + 10) * 100 AS discount_factor 
    (points + 10) * 100 AS 'discount factor' //列名中加入空格需要用引号(单双都可)
FROM customers

SELECT DISTINCT state         //获取一份州的唯一列表
FROM customers 

WHERE子句

SELECT *
FROM customers
WHERE points > 3000  
WHERE state = 'VA'  
WHERE state != 'VA'
WHERE state <> 'VA'  //和上面一样结果
WHERE birth_date > '1990-01-01'

AND OR NOT 运算符

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR 
(points > 1000 AND state = 'VA')

优先级：
AND > OR

NOT (birth_date > '1990-01-01' OR points > 1000)
birth_date <= '1990-01-01' AND points <= 1000 --两者等价

IN

WHERE state IN ('VA', 'FL', 'GA')
WHERE state NOT IN ('VA', 'FL', 'GA')

BETWEEN

WHERE points BETWEEN 1000 AND 3000 

LIKE

WHERE last_name LIKE 'b%'  --以b开头
'%b%'       --有b就可以
'%y'        --以y结尾
'___y'      --y前面有3个字符
'b__y'      --b和y中间有2个字符

% 任意字符数
_ 一个字符

WHERE ADDRESS LIKE '%trail%' OR 
ADDRESS NOT LIKE '%avenue%'

REGEXP

WHERE last_name REGEXP 'field' 
--任意位置存在都可以
'^field'
--必须以field开头
'field%'
--必须以field结尾
'field|mac'
--二者有一个就行
'field|mac|rose'
--三者
'^field|mac|rose'
--以field开头或有mac或有rose
'[gim]e'
--有 ge ie me
'[a-h]e'
--有ae be ... he 

缺失值

WHERE phone IS NULL

ORDER BY

SELECT * 
FROM customers
ORDER BY first_name 
--正序
ORDER BY first_name DESC
--倒叙
ORDER BY state, first_name
--先用州排序，再在州中用姓排
ORDER BY state DESC, first_name

SELECT birth_date, first_name, last_name, 10 AS points 
FROM customers
ORDER BY 1,2
--第一个第二个属性，避免使用

SELECT *，quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESC

LIMIT

LIMIT 3
--只找前三个，高于总数会全部显示
LIMIT 6，3
--跳过前六个去找7，8，9

LIMIT语句永远放在最后

连接

内连接

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

若是查找的列两个表中都存在，需要在前面加前缀：

SELECT orders.customer_id, first_name

SELECT order id，o.customer id，first_name
FROM orders o
JOIN customers c
ON o.customer id = c.customer_id
--给了表别名，别的地方也一定要用别名

跨数据库连接

USE sql_store
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
ON oi.product_id = p.product_id
--另一个数据库名为sql_inventory

自连接

SELECT *
FROM employees e
JOIN employees m
ON e.reports_to = m.employee id

多表连接

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

复合连接条件

SELECT*
FROM order_items oi
JOIN order_item notes oin
    ON oi.order_id = oin.order_id
    AND oi.product id = oin.product_id

隐式连接语法

SELECT *
FROM orders o，customers c
WHERE o.customer id = c.customer id

外连接

内连接只会返回满足条件的值

FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id
--左连接，返回左表所有数据 （customers），不管条件正不正确

RIGHT JOIN orders o
--右连接，返回右表所有的值（orders）

多表外连接

FROM customers CLEFT 
JOIN orders o
    ON c.customer id = o.customer id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id

自外连接

LEFT JOIN employees m= m.employee_id
    ON e.reports_to
--管理人员也会被返回

USING子句

--两个表中列名字一样
ON o.customer_id = c.customer_id
USING(customer id)

ON oi.order id = oin.order id AND
    oi.product id = oin.product id
USING(order_id，product id)

自然连接

--让引擎自己猜怎么连接
SELECT *
FROM orders o
NATURAL JOIN customers c

交叉连接

CROSS JOIN products p