SQL整理（一）

一、数据库

1. 数据库（DB）：以易于访问格式存储的数据集合
2. 分类：关系数据库（相互关联的表，可以使用SQL管理）和非关系数据库
3. 数据库管理系统（DBMS）

二、SQL语法
USE 数据库名字；

1. 查询【DML】：
   （1）基本语法：
   SELECT 项名字
   FROM 表名字
   WHERE 筛选条件
   ORDER BY 项名字 LIMIT 排序后显示数量
   ——注释

① SELECT
i. 所有列 *
ii. 自定义想要的项及顺序
iii. 简单运算 age*(10+1) mod 2
iv. 重新命名 AS
v. 去重（所有项都一致才算重复） DISTINCT
【eg1】

SELECT *
FROM list
——从表list中选择所有记录

【eg2】

SELECT name, price, (price*(10+1) mod 2) AS ’new price’
FROM list
——从表list中选择name、price、(price*(10+1) mod 2)作为new price

【eg3】

SELECT DISTINCT name
FROM list
——从表list中选择不重复的名字

② FROM：
i. 别名 直接添加在表的名字后
【eg1】

SELECT *
FROM list l

③ WHERE：
i. 大小（先后）> >= < <= = != <>
ii. 某项为空 =NULL、!=NULL、IS NULL、IS NOT NULL；含NULL的运算结果是NULL
iii. 连接筛选条件AND / OR，且AND优先级高于OR
iv. 符合备选IN(数字,数字) / NOT IN(‘字符串’)和between…and …
v. 模糊查询like ’%或_’ 和regexp ‘^ $ | [] [-]’

【eg】

SELECT *
FROM products
WHERE points>3000 
WHERE birdate>‘1990-01-01‘ OR points>1000 AND state=’VA’ ——birdate在1990.1.1以后或 积分超过1000且在VA ，AND优先级高于OR
WHERE state IN (‘VA’,’FL’,’GA’) ——state项值为VA或FL或GA’
WHERE state NOT IN (‘VA’,’FL’,’GA’)
WHERE points BETWEEN 1000 AND 3000 ——积分在1000-3000之间，左开右开
WHERE name LIKE ’%b%’/’%b’/’b% ’——名字含有b/以b结尾/以b开头
WHERE name LIKE ‘_____y’/’’b_y’ ——_占位，名字符合格式
WHERE name REGEXP ‘b[a-h]’ ——名字为b后接abcdefgh中任一字符
WHERE name REGEXP ‘[RU]B’ ——名字含有RB或UB
WHERE name REGEXP ‘ELKA|AMBUR’ ——名字含有ELKA或AMBUR
WHERE name REGEXP ‘EY$|ON$’ ——名字以EY或ON结尾
WHERE name REGEXP ‘^MY|SE’ ——名字以MY开头或含有SE
WHERE phone IS NULL 

（[^…]、*、+、{n}、{n,m}待整理）

④ ORDER BY：
i. 列名
ii. 列的序号
iii. 升序为默认，降序 DESC
iv. 可以用多个

【eg】

ORDER BY state, name DESC
ORDER BY 1,2

⑤ LIMIT：
i. 前多少的个数
ii. 最多显示全部数据不显示空
iii. 偏移量，个数

【eg】

LIMIT 3
LIMIT 300 ——如果没有300个数据，就有多少显示多少
LIMIT 6,3 ——偏移量为6，显示三个，即显示位次为6，7，8的记录

（2）联接：
① 内部联接（INNER）JOIN：
i. FROM 表1 JOIN 表2 ON 表1联接使用的列=表2联接使用的列
ii. FROM 表1 JOIN 表2 USING(相同的列名)
iii. 可用逻辑连接词联接多个匹配的列
iv. 可自我联接但要设置别名以供区分
v. 可多表联接
vi. 隐式写法：FROM两个表，WHERE联接条件
【eg1】

SELECT *
FROM orders
JOIN customers 
	ON orders.id=customers.id
JOIN list
	USING(number)

【eg2】

SELECT id
FROM orders
JOIN customers ON orders.id=customers.id
——错误，两个表都有id，不知道要select哪个
SELECT o.id
FROM orders o
JOIN customers ON o.id=customers.id
——正确

【eg3】

SELECT e. employee id ,e.name, m.name as manager
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id ——自我链接设两个别名就可以了
JOIN employees m ON e.reports_to = m.employee_id AND e.name=m.name ——联合主键

【eg4】

SELECT *
FROM orders o,customers c
WHERE o.customers_id=c.customers_id 
——隐式联接

② 外部联接（OUTER）JOIN：
i. FROM 等式左侧项所在表名（一般是含空值的）
LEFT/RIGHT JOIN 等式右侧表名 ON 表1联接使用的列=表2联接使用的列
ii. 尽量避免使用右联接，
【eg】

SELECT*
FROM orders o
LEFT JOIN employees ON e.reports_to=m.employees_id

③ 自然联接：
i. NATURAL JOIN不常用
④ 交叉联接：
i. CROSS JOIN 连接结果为笛卡尔积
ii. 隐式写法：FROM两个表，WHERE处没有联接条件
【eg1】

SELECT *
FROM size
CROSS JOIN color

【eg2】

SELECT *
FROM size, color

（3）联合查询结果：
UNION
相同类型才可以联合
2. 增加数据
（1）插入数据：
INSERT INTO 表名/列名
VALUES ()

【eg1】

INSERT INTO list
VALUES (default,’john’,’smith’,NULL,default) ——default

【eg2】

INSERT INTO product (name,qulity,prize)
VALUES (‘a’,’a’,1),(‘b’,’a’,3)

【eg2】

INSERT INTO oders(customer_id,order_date,status)
VALUES (1,’2019-01-01’,1);
INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1,1,2.95),(LAT_INSERT_ID(),2,1,3.95)
——LAST_INSERT_ID()是最近一个 insert动作（中第一条信息）的主键值，只适用与自增主键

（2）复制表：
CREATE TABLE 复制后表的名字 AS
SELECT
FROM
WHERE

【eg1】

CREATE TABLE list AS
SELECT *
FROM orders
WHERE date<’2020-01-01’

【eg2】

CREATE TABLE copy AS 
SELECT i.invoice_id, i,number, c.name=client
FROM invoice i
JOIN clients c USING (client_id)
WHERE status!=NULL

3. 更新表中数据
   UPDATE 表名
   SET 项=更新后的值
   WHERE 更新条件

【eg】

UPDATE orders
SET comments=’gold’
WHERE customer_ID IN (SELECT customer_ID FROM orders WHERE poins>3000)

4. 删除表中数据
   DELETE
   FROM 表名
   WHERE 删除条件

【eg】

DELETE FROM invoices
WHERE client_id=(SELECT id FROM clients WHERE name=’works’)
——删除invoices表中client_id值与表clients中名字为works的记录对应的数据