sql数据库（1）

*（返回所有列）

SELECT *
FROM customers（从这个表）
-- WHERE customer_id=1（筛选数据）
-- ORDER BY first_name（排序）

AS 给这一列取个别名

SELECT first_name, last_name,points,(points +10) *100 AS discount_factor
FROM customers

如果想要在列的名字里面加空格，需要加引号

SELECT first_name, last_name,points,(points +10) *100 AS 'discount factor'
FROM customers

删除重复项，DISTINCT关键字

SELECT DISTINCT state
FROM customers

AND ：两个条件要同时符合

SELECT *
FROM Customers
WHERE birth_date > '1990-01-01' AND points > 1000

OR ：两个条件满足一个条件即可

SELECT *
FROM Customers
WHERE birth_date > '1990-01-01' OR points > 1000

AND优先级高于OR

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

NOT ：否定条件

IN：

SELECT *
FROM Customers
WHERE state NOT IN ('VA','FL','GA')

BETWEEN AND：在这两者之间的条件

SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000

LIKE：

SELECT *
FROM customers
WHERE last_name LIKE '%y%' （代表名字含有y）
WHERE last_name LIKE '_____y' (代表y的前面有5个字符)

REGEXP :正则

^ : 以某某开头

$ : 以某某结尾

| ：相当于逻辑 OR

[abcd]: 匹配 [] 中的任意字符

SELECT *
FROM customers
WHERE last_name REGEXP 'field|mac'（名字含有field或者mac）
WHERE last_name REGEXP '[a-h]e' （含有 ae,be,ce,...,he)

IS NULL ：筛选出手机号码是null

SELECT *
FROM customers
WHERE phone IS NULL
WHERE phone IS NOT NULL（筛选出手机号码不是null的）

DESC： 降序排列

LIMIT：

SELECT *
FROM customers
LIMIT 6,3 （跳过前六条数据，拿到三条数据）

内连接 JOIN

SELECT order_id,o.customer_id,first_name,last_name
from orders o (取个别名o)
JOIN customers c (取个别名c)
ON o.customer_id = c.customer_id

跨数据库连接

USE sql_inventory;

SELECT *
FROM sql_store.order_items oi  （注意这里要加数据库前缀名字sql_store）
JOIN sql_inventory.products p 
ON oi.product_id = p.product_id

自连接

USE sql_hr;
SELECT e.employee_id,
        e.first_name,
        m.first_name AS manger
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id

多表连接

USE sql_invoicing;
SELECT 
        p.date,
        p.invoice_id,
        p.amount,
        c.name,
        pm.name
FROM payments p
JOIN clients c
    ON p.client_id = c.client_id
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_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
JOIN customers c
    ON o.customer_id = c.customer_id
    --下面这个就是隐式连接
SELECT *
FROM orders o, customers c
where o.customer_id = c.customer_id

外连接，分为左连接(left)和右连接(right),一般使用左连接，尽量避免使用右连接

SELECT 
    p.product_id,
    p.name,
    oi.quantity
FROM products p
LEFT JOIN order_items oi
    ON p.product_id = oi.product_id

自外连接，以下代码，如果是内连接的话，管理人员将不在列表出现，外连接（加上left）就会将管理人员和被管理的人员都查询出来。

USE sql_hr;
SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id

USING子句，内外连接都可以使用，如果有两个条件，还可以这样写USING(a,b)

SELECT 
    o.order_id,
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c
-- 以下两句等同
    -- ON o.customer_id = c.customer_id
    USING (customer_id)
LEFT JOIN shippers sh
    USING (shipper_id)

自然连接 NATURAL

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

交叉连接 CROSS

-- 隐式语法
SELECT 
    sh.name AS shipper,
    p.name AS product
FROM shippers sh,products p
ORDER BY sh.name

-- 显式语法
SELECT 
    sh.name AS shipper,
    p.name AS product
FROM shippers sh
CROSS JOIN products p
ORDER BY sh.name

联合：UNION 合并多个查询

SELECT first_name
FROM customers
UNION
SELECT name
FROM shippers