MySql（一）

一、语句

1、USE 语句

data 库加粗显示
USE data

2、SELECT语句

SELECT + 字段名 AS + 字段别名 FROM + 表名 WHERE + 条件  ORDER BY + 字段名

DISTINCT ：去重        
ORDER BY ：排序
字段名可以使用 （  +   -  *   /  ）得到新的字段
字段名 + AS + 字段别名

eg:

SELECT DISTINCT
    last_name,
    first_name AS 'first name',
    points,
    points + 10 * 100  AS discount_factor
FROM customers
ORDER BY last_name

3、WHERE语句

SELECT * FROM Customers WHERE points > 3000

SELECT * FROM Customers WHERE state = 'VA'

SELECT * FROM Customers WHERE state <> 'VA'

SELECT * FROM Customers WHERE DATA > '1999-12-01'

SELECT * FROM Customers WHERE DATA > '1999-12-01' AND points > 1000

SELECT * FROM Customers WHERE NOT DATA > '1999-12-01' AND points > 1000

我们可以用 > >= < <= = != <>
<>表示不等于
条件是字符串的要加''
AND 后加另一个条件 并且两个条件都成立
OR  后加另一个条件 并且两个条件成立一个就可以
AND 优先执行于 OR
NOT 用来否定一个条件

4、IN运算符

SELECT * FROM Customers WHERE state = 'VA' OR state = 'GA' OR state = 'FL'
我们有更好的方式来实现上面的语句
SELECT * FROM Customers WHERE state IN ('VA','GA','FL')

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

5、BETWEEN运算符

SELECT * FROM customers WHERE points >= 1000 AND points <= 4000
我们有更好的方式来实现上面的语句
SELECT * FROM customers WHERE points BETWEEN 1000 AND 4000

6、LIKE运算符

搜索last_name以b打头的
SELECT * FROM customers WHERE last_name LIKE 'b%'
搜索last_name以b结尾的
SELECT * FROM customers WHERE last_name LIKE '%b'
搜索last_name内带有b的
SELECT * FROM customers WHERE last_name LIKE '%b%'
搜索last_name内两个字符的且第二个字符为b
SELECT * FROM customers WHERE last_name LIKE '_b'
搜索last_name内个六字符的且第一个字符为y第最后一个字符为b
SELECT * FROM customers WHERE last_name LIKE 'y____b'

7、REGEXP运算符

搜索last_name内带有b的
SELECT * FROM customers WHERE last_name LIKE '%b%'
我们有更好的方法来实现上面的语句
SELECT * FROM customers WHERE last_name REGEXP 'b'
以b开头我们可以
SELECT * FROM customers WHERE last_name REGEXP '^b'
以b结尾我们可以
SELECT * FROM customers WHERE last_name REGEXP 'b$'
查询last_name 中有bas或asd或qwe
SELECT * FROM customers WHERE last_name REGEXP 'bas|asd|qwe'
查询last_name 中有bas开头或asd结尾或qwe
SELECT * FROM customers WHERE last_name REGEXP '^bas|asd$|qwe'
查询last_name 中e前面有qwr的
SELECT * FROM customers WHERE last_name REGEXP '[qwr]e'
查询last_name 中e后面有qwr的
SELECT * FROM customers WHERE last_name REGEXP 'e[qwr]'
查询last_name 中e前面有abcdef的
SELECT * FROM customers WHERE last_name REGEXP '[a-f]e'
查询last_name 中e后面有abcdef的
SELECT * FROM customers WHERE last_name REGEXP 'e[a-f]'

8、IS NULL 运算符

搜索所有缺失值的字段
SELECT * FROM customers WHERE phone IS NULL
搜索所有没缺失值的字段
SELECT * FROM customers WHERE phone IS NOT NULL

9、ORDER BY 子句

根据列排序(降序)
SELECT * FROM name ORDER BY data DESC

根据 state降序 first_name升序
SELECT * FROM name ORDER BY state DESC , first_name

10、LIMIT 子句（永远在最后）

查询前三条
SELECT * FROM customers LIMIT 3
查询第七八九条（跳过前六条，查三条）
SELECT * FROM customers LIMIT 6，3

二、操作

1、内连接

连接orders表和customers
SELECT * 
FROM orders 
JOIN customers 
    ON orders.customers_id = customers.customers_id

2、跨数据库连接

和其他数据库的表进行连接
SELECT * 
FROM order_iteam oi  
JOIN sql_inventory.products p 
    ON oi.product_id = p.product_id

3、自连接

找到需要的库
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

4、多表连接

找到自己需要的库
USE sql_store
多表连接
SELECT
    O.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    os.name AS status
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN order_statuses os
    ON o.status = os.order_status_id

5、复合连接条件

复合连接条件
SELECT *
FROM order_items oi
JOIN order_iteam_notes oin
    ON oi.order_id= oin.order_id
    AND oi.product_id = oin.product_id 

6、隐式连接语法（不建议使用）

连接orders表和customers
SELECT * 
FROM orders o , customers c
WHERE o.customers_id = c.customers_id

7、外连接

外连接分为（左连接和右连接）
左连接（不管条件是什么左表都会被查出）
SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customer c   (左表)
LEFT JOIN order o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id

右连接（不管条件是什么右表都会被查出）
SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customer c
RIGHT JOIN order o    （右表）
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id

8、多表外连接

左连接（不管条件是什么左表都会被查出）
SELECT 
    c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
FROM customer c   
LEFT JOIN order o
    ON c.customer_id = o.customer_id
LEFT JOIN shipper sh
    ON o.shipper_id= sh.shipper_id
ORDER BY c.customer_id
( c 对于 o 来说 c是左表   o 对于 sh 来说 o是左表 )
（多表连接不推荐右连接）

9、自外连接

自外连接
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

10、USING子句

左连接
SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customer c   (左表)
LEFT JOIN order o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id

我们可以用USING来简写上面的语句
SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customer c   (左表)
LEFT JOIN order o
    --ON c.customer_id = o.customer_id   (只有连接字段名相同时才可以使用)
    USING（customer_id）
ORDER BY c.customer_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 order_items oi
JOIN order_item_notes oin
   -- ON oi.order_id = oin.order_id
   -- AND oi.product_id = oin.product_id
    USING (order_id,product_id)

11、自然连接

自然连接系统自动寻找相同的字段进行连接（不推荐使用）
SELECT *
FROM orders o
NATURAL JOIN customers c

12、交叉连接

一个表的每一条记录和另一个每条记录相结合
SELECT *
FROM customers c
CROSS JOIN products p

第二种写法
SELECT *
FROM customers c ，products p

13、联合

UNION关键字 
（可以在不同的表进行查询联合，但是查询的列数必须相同）
（第一段查询的名字决定联合后列的名字）

SELECT 
    order_id,
    order_data,
    'Archived' AS status
FROM  orders
WHERE order_data >= '1999-01-01'
UNION
SELECT 
    order_id,
    order_data,
    'Active' AS status
FROM  orders
WHERE order_data < '1999-01-01'