搭配实例的sql笔记（更新ing）

mysql和本笔记使用的例子文件都在里面

链接：https://pan.baidu.com/s/1Bbk7_bKZJWBEZjU6QDHM2w
提取码：u4bz

本文语法全部大写 实际上可以不大写
这仅仅作为一种规范

语法

检索单一表数据

SELECT

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

DISTINCT关键字 可以去重

SELECT DISTINCT state
FROM customers

WHERE

SELECT *
FROM customers
WHERE birth_date > '1990-01-01'

AND OR NOT

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

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' or points >1000

AND 优先级大于 OR 记不住可以加括号 加括号是好习惯

SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01' or points >1000)

IN

SELECT *
FROM customers
WHERE state='VA' OR state ='GA' OR state='FL'

IN也可以完成上面做的事 如下

BETWEEN

SELECT *
FROM customers
WHERE points >=1000 AND POINTS <=3000

BETWEEN也可以做上面的事 如下

接下来 我将很少截图了 我觉得直接写即可

LIKE

SELECT *
FROM customers
WHERE last_name LIKE '%b%'

查找customers里 last_name含有b的项

SELECT *
FROM customers
WHERE last_name LIKE 'b____y'

查找customers里 last_name是首字母为b 中间有四个未知字母 尾字母为y的项

REGEXP

简单举几个例子√

SELECT *
FROM customers
WHERE last_name REGEXP 'field|mac'

查询包含field 或 mac的

---

SELECT *
FROM customers
WHERE last_name REGEXP 'field$|mac|rose'

查询以field结尾 或 包含mac rose 的

---

SELECT *
FROM customers
WHERE last_name REGEXP '[gim]e'

查询包含ge 或 ie 或 me的

---

SELECT *
FROM customers
WHERE last_name REGEXP '[a-h]e'

查询包含ae be ce… he的

^ 开头
$ 结尾
| 或
[abcd] ，[a-f] 见实例

IS NULL

SELECT *
FROM customers
WHERE phone IS NULL

查询电话号为空的项

ORDERED

SELECT *
FROM customers
ORDER BY first_name DESC

按first_name反着排列

SELECT *
FROM customers
ORDER BY state DESC,first_name DESC

如果按state逆序比较优先级一致 那么将按name逆序为规则进行排列
先按第一个 再按第二个排列

LIMIT

SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3

先按积分降序排列每项 取前三个

SELECT *
FROM customers
ORDER BY points DESC
LIMIT 6,3

排序后 跳过前六条 取接下来的三条

检索多表数据

INNER JOIN

SELECT order_id,o.customer_id,first_name,last_name
FROM orders o
INNER JOIN customers c
	ON o.customer_id=c.customer_id 

连接orders和cutomers两个表

SELECT * FROM order_items oi
JOIN sql_inventory.products p
	ON oi.product_id=p.product_id

跨库合并表

USE sql_hr;
SELECT *
FROM employees e
JOIN employees m
 ON e.reports_to=m.employee_id

自合并 建议自行尝试并理解

USE sql_invoicing;
SELECT 
*
FROM payments p
JOIN payment_methods m
 ON  p.payment_id=m.payment_method_id
JOIN clients c
 ON p.client_id=c.client_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

隐式合并语法

SELECT 
	p.product_id,
    p.name,
    o.quantity
FROM  products p
LEFT JOIN order_items o
ON p.product_id=o.product_id
 ORDER BY p.product_id

外连接