# SQL学习
## 基础（90%可以用到其它DBMS）（database management system：数据库管理系统、关系型数据库）
- 增删改查：
- inserting data：
- deleting data：
- updating data：
- retrieving data：
- 汇总数据(summarizing data)
- 使用子查询来复杂查询(writing complex queries)
- 基本内置函数(built-in Functions)
- 用于和数字、日期、文本数据打交道。
- 创建视图（views）
- 存储过程和函数（Stored procedures）
- 简单子句：
- SELECT：
- 可以使用算数表达式
- SELECT customer_id, first_name（选择的是列）
FROM customers
-- WHERE customer_id = 1 （-- 为注释）
ORDER BY first_name
- FROM、WHERE、ORDER BY三个字句都是可选的 ，但是顺序不可以改变
- 列名不能有空格，要加空格的话加 " "或‘ ‘
- DISTINCT关键字：
- 去除重复项（去重）
- AS：
- 起别名
- WHERE：
- !=相同于<>
- 可以使用算数表达式
- AND/OR/NOT（与或非）：
- AND优先级高于OR
- IN:
- 同一系列值比较一个属性
- 例：WHERE state IN(‘VA‘, ‘FL‘, ‘GA‘) 等同于 WHERE state = ‘VA‘ OR state = ‘FL‘ OR state = ‘GA‘
- BETWEEN:
- 取一个闭区间
- 例： WHERE points BETWEEN 1000 AND 3000 等同于 WHERE points >= 1000 AND points <= 3000
- 如何检索遵循特定字符串模式的行：
- LIKE：
- WHERE last_name LIKE ‘%b%‘ (b不区分大小写，%表示任意字符)
- WHERE last_name LIKE ‘_y‘ (_表示一个字符)
- WHERE address LIKE ‘%trail%‘ OR
address LIKE ‘%avenue%‘
- REGEXP：
- 正则表达式regular expression缩写
- 搜索字符串时极其强大
- "^"表示字符串的开头
- WHERE last_name REGEXP ‘^my|se‘
- "$"表示字符串的末尾
- WHERE last_name REGEXP ‘ey$|on$‘
- "|"表示： 或（OR）多个搜寻模式
- WHERE first_name REGEXP ‘elka|ambur‘
- "[]":匹配任意在括号里列举的单子符
- WHERE last_name REGEXP ‘b[ru]‘
- "[a-f]":闭区间a到f的任意字母
- 如何搜索缺失了属性的记录（值为null的记录）：
- IS NULL
- WHERE shipped_date IS NULL
- ORDER BY:
- 默认列升序排序
- DESC(降序) ORDER BY first_name DESC
- 可以用别名：
- SELECT *
FROM order_items
WHERE order_id = 2
ORDER BY quantity * unit_price DESC
- SELECT * , quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESC
- 如何限定查询返回的记录：
- LIMIT
- LIMIT字句永远要放在最后
- LINIT 3 （显示前三行记录）
- 可以用于分页
- --page 1: 1-3
--page 2: 4-6
--page 3: 7-9
LIMIT 6, 3(6为偏移量，跳过前六个记录，获取3个记录)
- 连接:
- 内连接：
- INNER JOIN （INNER关键字 可写可不写）
- SELECT order_id， order.customer_id, first_name, last_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
- 给表起别名：
SELECT order_id， o.customer_id, first_name, last_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
- 跨数据库连接：
- 要给不在当前数据库的表加 前缀（数据库名称）
- USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
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 manager
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id
- 多表连接：
- USE sql_store;
SELECT
o.order_,
o.order_date,
c.firet_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
- 复合连接：
- 用于有复合主键表的连接
- SELECT *
FROM order_items oi
JOIN order_items_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id
- 隐式连接语法 ：
- 不建议用，如果忘了WHERE字句会变成交叉连接
- SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
等同于
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
- 外连接：
- OUTER JOIN（OUTER可省去）
- FROM 里的是左表，JOIN里的是右表
- 尽量使用左连接
- 左连接（LEFT JOIN）：
- 所有左表的记录，会被返回（不管条件ON正确不正确）。
- SELECT p.product_id,
name,
oi.quantity
FROM products p
LEFT JOIN order_items oi
ON p.product_id = oi.product_id
- 右连接（RIGHT JOIN）：
- 所有右表的记录，会被返回（不管条件ON正确不正确）。
- USING
- ON条件两个表中名称相同时使用
- 内连接和外连接都可以使用
- USING（customer_id）
等同于 ON o.customer_id = c.customer_id
- 自然连接：
- NATURAL JOIN
- 危险：
- 没有ON条件，基于共同的列连接
- 让数据库引擎自己猜该怎么连接，我们无法控制它 （不建议使用）
- 交叉连接：
- CROSS JOIN
- 用途：
- 例：衣服的各种尺寸和各种颜色
- 结合或者连接第一个表的每条记录和第二个表的每条记录
- SELECT *
FROM customers c
CROSS JOIN products p（显示连接更清楚）
等同于
FROM customers c，products p（隐式连接）
顾客表里的每条记录都会和产品表里的每条记录结合
- 结合：
- UNION：
- 合并的列数必须相同，否则会报错
- 合并后的列名取于第一个
- 合并多个查询的结果
- SELECT customer_id,
first_name,
points,
‘Bronze‘ AS type
FROM customers
WHERE points < 2000
UNION
SELECT customer_id,
first_name,
points,
‘Silcer‘ AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT customer_id,
first_name,
points,
‘gold‘ AS type
FROM customers
WHERE points > 3000
ORDER BY first_name
- 行连接：
- 合并多段查询的记录
- 有效的和无效的相连接
- 不同表和相同表都可以使用
- 插入、更新、删除：
- 插入：
- 单行：
- INSERT INTO customers(列名)
VALUES(
值
)
- 多行：
- INSERT INTO customers(列名)
VALUES()，
(),
()
- 插入分层行：
- 往多表插入数据
- 父子关系表
- 复制数据：
- 复制的新表没有主键和自增
- CREATE TABLE orders_archived AS
SELECT * FROM orders(子查询)
子查询实例：
- INSERT INTO orders_archived
SELECT * FROM orders WHERE order_date < ‘2019-01-01‘
- CREATE TABLE invoices_archived AS
SELECT i.invoice_id,
i.number,
c.name AS client,
i.invoice_total,
i.payment_total,
i.invoice_date,
i.payment_date,
i.due_date
FROM invoices i
JOIN clients c
USING (client_id)
WHERE payment_date IS NOT null
- 更新：
- UPDATE
- 更新单行：
- UPDATE invoices
SET payment_total = 10, payment_date = ‘2019-01-05‘（指定一列或者多列的新值）
WHERE invoice_id = 1 (需要被更新的记录)
- 更新多行：
- UPDATE customers
SET points = points + 50
WHERE birth_date < ‘1990-01-01‘customers
- 使用子查询（很强大）：
- 子查询（另一段SQL语句里的选择语句）
- 可以作为WHERE语句里的筛选条件
- UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id IN
( SELECT client_id
FROM clients
WHERE state IN (‘CA‘, ‘NY‘)
)
- UPDATE orders
SET commments = ‘gold customer‘
WHERE customer_id IN
(SELECT customer_id
FROM customers
WHERE points > 3000)
- 删除：
- DELETE FROM invoices
WHERE client_id = (
SELECT *
FROM clients
WHERE name = ‘Myworks‘
)
- 恢复数据库：
- file里sql-script
- 数据汇总：
- 聚合函数：
- 使用了聚合函数应该要进行分组
- 只运行非空值
- SELECT
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total) AS total,
COUNT(invoice_total) AS number_of_invoices
COUNT(*) AS total_records (全部记录，包含非空值)
FROM invoices
- DISTINCT(去重取唯一的)
- COUNT(DISTINCT client_id) AS total_records
- SELECT
‘First half of 2019‘ AS data_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
BETWEEN ‘2019-01-01‘ AND ‘2019-06-30‘
UNION
SELECT
‘Last half of 2019‘ AS data_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
BETWEEN ‘2019-06-31‘ AND ‘2019-12-31‘
UNION
SELECT
‘all of 2019‘ AS data_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
BETWEEN ‘2019-01-01‘ AND ‘2019-12-31‘
- GROUP BY（分组查询）：
- SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
ORDER BY total_sales DESC
- HAVING：
- 可以在分组（GROUP UP）后筛选--充当WHERE
- WITH ROLLUP（汇总数据）:
- GROUP BY client_id WITH ROLLUP
- 只在MYSQL中可以使用
- 复杂查询：
- 子查询（内查询）是在另一个sql语句中的选择语句
- SELECT *
FROM products
WHERE unit_price >
(SELECT unit_price
FROM products
WHERE product_id = 3)
- IN运算符写子查询：
- SELECT *
FROM products
WHERE product_id NOT IN
(
SELECT DISTINCT product_id
FROM order_items
)
- SELECT *
FROM clients
WHERE client_id NOT IN
(
SELECT DISTINCT client_id
FROM invoices
)
- 子查询vs连接
- 用JOIN重写子查询或取反
- 用子查询：
- SELECT customer_id,
first_name,
last_name
FROM customers
WHERE customer_id IN
(
SELECT customer_id
FROM order_items oi
JOIN orders o
USING (order_id)
WHERE product_id = 3
)
- 用连接：（优选）
- SELECT DISTINCT customer_id,
first_name,
last_name
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3
- ALL关键字：
- 用于子查询返回一列值，和每一个比较都要符合(大于最大，小于最小)
- 可以和聚合函数max、min转换
- SELECT *
FROM invoices
WHERE invoice_total > ALL (
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
- SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
)
- ANY:
- = ANY相当于IN
- 相关子查询：
- 父表里的每一个员工，它都会执行一次子查询
- 执行的慢 、占用更多存储
- 子查询和父查询存在相关性 --子查询引用了父查询里表的别名
- SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE office_id = e.office_id
)
- EXISTS运算符：
- SELECT *
FROM products
WHERE product_id NOT IN (
SELECT product_id
FROM order_items
)
- SELECT *
FROM products p
WHERE NOT EXISTS (
SELECT product_id
FROM order_items
WHERE product_id = p.product_id
)
- SELECT子句中获得子查询：
- 表达式中不能使用列的别名
- SELECT
client_id,
c.name,
(SELECT SUM(invoice_total)
FROM invoices
WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total)
FROM invoices) AS average,
(SELECT total_sales - average) AS difference
FROM clients c
- FROM子句中使用子查询：
- 变得复杂，可以作为视图存储在数据库中
- SELECT *
FROM(
SELECT
client_id,
c.name,
(SELECT SUM(invoice_total)
FROM invoices
WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total)
FROM invoices) AS average,
(SELECT total_sales - average) AS difference
FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL
- 内置函数：
- 用于应对数值、日期时间、字符串值
- 数值：
- ROUND（5.7366，2） 四舍五入函数（值，精确精度）
- TRUNCATE()--截断函数
- CEILING(2.6)--取整-返回大于或等于这个数的最小整数
- FLOOR（2.6）--地板函数-返回小于或等于这个数字的最大整数
- ABS(-2.6)--计算绝对值
- RAND()--用来生成0-1之间的随机浮点数
- 字符串：
- LENGTH(‘asdas‘)--得到字符串中的字符数
- UPPER(‘asdas‘)--将字符串转化成大写字母
- LOWER(‘ASDAS‘)--将字符串转化成小写字母
- 去除字符串中不需要的空格：
- LTRIM(左修整-left trim) LTRIM(‘ ASDAD‘)--去除字符串左侧的空白字符
- RTRIM(‘ASDAS ‘)--去除字符串左侧的空白字符
- TRIM(‘ asd ‘)--去除所有空白字符
- LEFT(‘sdfdaadasfrs‘，4)--返回字符串左侧的几个字符
- RIGHT(‘asdasdqasd‘, 6)--返回字符串右侧的几个字符
- SUBSTRING(‘afdasdasa‘,起始位置，长度)--字符截取函数--可以得到一个字符串中任何位置的字符
- 第三个参数为可选，默认为返回到末尾
- LOCATE(‘b‘,‘sadasfbkl‘ )-- 返回第一个字符或者一串字符匹配位置
- 不区分大小写，没有返回0
- REPLACE(‘zhangziyang‘, ‘yang‘, ‘hai‘ )--替换一段字符串
- CONCAT(‘first‘, ‘LAST‘)--串联字符串
- 日期和时间：
- NOW()--电脑当前日期和时间
- CURDATE()--当前日期
- CURTIME()--当前时间
- YEAR(NOW())--MONTH、DAY、HOUR、MINUTE、SWCOND截取当前年、月、日、时、分、秒--返回整数值
- DAYNAME(NOW())--MONTHNAME获取星期数、月份数--返回字符串格式
- EXTRCT(YEAR FROM NOW())---返回2019---(想获取的单位 FROM 时间日期值)--返回字符串格式
- 例：返回当前年下的订单
- SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW())
- 日期格式函数：DATE_FORMAT()
- DATE_FORMAT(NOW(), ‘%M %d %Y‘)
- 时间格式函数：TIME_FORMAT()
- TIME_FORMAT(NOW(), ‘%H:%i %p‘)
- 计算日期和时间的函数：
- DATE_ADD(NOW(), INTERVAL 1/-1 DAY/YEAR)--增加或减少
- DATEDIFF(‘2021-07-04‘,‘2021-06-17‘)--计算日期间隔，返回天数--来上海了17天
- TIME_TO_SEC()---返回从零点计算的秒数--TIME_TO_SEC(‘09:02‘) - TIME_TO_SEC(‘09:00‘)
- IFNULL：
- SELECT
order_id,
IFNULL(shipper_id, ‘未分配‘) AS shipper---如果shipper_id为空(null),改为未分配---替换空值
FROM orders
- COALESCE(shipper_id, comments, ‘未分配‘)：---如果如果shipper_id为空(null)，返回comments列的值，
如果comments列的值为空，shipper_id改为未分配---提供一堆值，返回第一个非空值
- 例：SELECT
CONCAT(first_name,‘ ‘, last_name) AS customer,
COALESCE(phone,‘UNKNOW‘) AS phone
FROM customers
- IF函数： 用来检验--允许单一的表达式
- IF(表达式，为真时返回的值，为假时返回的值) ---可以返回任何值
- 例：SELECT
order_id,
order_date,
IF(YEAR(order_date) = YEAR(NOW()), ‘活跃‘, ‘归档‘) AS category
FROM orders
- 例：SELECT
product_id,
name,
COUNT(*) AS orders,
IF(COUNT(*) > 1, ‘many times‘, ‘once‘) AS Frequence
FROM products p
JOIN order_items oi USING(product_id)
GROUP BY product_id, name
- CASE运算符：
- CASE
WHEN THEN --每个when子句都有一个测试表达式，满足就返回THEN后的值
END --关闭通道
-例：
- SELECT
order_id,
CASE
WHEN YEAR(order_date) = YEAR(NOW()) THEN ‘ACTIVE‘
WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN ‘LAST_year‘
WHEN YEAR(order_date) < YEAR(NOW()) - 1 THEN ‘Archived‘
ELSE ‘Future‘
END AS category
FROM orders
- SELECT
CONCAT(first_name, ‘ ‘, last_name) AS customer,
points,
CASE
WHEN points > 3000 THEN ‘Gold‘
WHEN points >= 2000 THEN ‘Silver‘
ELSE ‘Bronze‘
END AS category
FROM customers
ORDER BY points DESC
- 视图：
- 解决繁琐的查询---相当于一张虚拟表
- 把查询或子查询存到视图里
- 视图不存储数据，数据存在表中
- CREATE VIEW AS
- 例：
- CREATE VIEW sales_by_client AS
SELECT
c.client_id,
c.name,
SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name
- CREATE VIEW clients_balance AS
SELECT
c.client_id,
c.name,
SUM(i.invoice_total - i.payment_total) AS blance
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name
- 删除视图：
- DROP VIEW 视图名
- 重建视图：
- CREATE OR REPLACE VIEW 视图名 AS
- 可更新视图：
- 没有：DISTINCT、聚合函数、GROUP/HAVING、UNION就为可更新视图
- 末尾加：WITH CHECK OPTION 防止UPDATE和DELETE语句将行从视图中删除
## 进阶
- 触发器（triggers）
- 事件（events）
- 事务和并发（transactions）
- 并发（concurrency）
## 高阶
- 设计数据库（Designing Databases）
- 索引（Indexing for high performance）
- 数据库安全（Securing Databases）
SQL学习