发帖者 Su_Wenlin 十月 06, 2018

SQL 学习笔记

SQL 语言的分类

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL

1. 数据库查询语言 DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>

2. 数据操纵语言DML

数据操纵语言DML主要有三种形式：

1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE

3. 数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象：表、视图、
索引、同义词、聚簇等

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

4. 数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等。如授权，撤销授权，提交，回滚等。

其中，提交又有：

显式提交: COMMIT;
隐式提交: CREATE, DROP, ALTER, AUDIT, COMMENT, CONNECT, DISCONNECT, EXIT, GRANT, NOAUDIT, REVOKE, RENAME;
自动提交: SET AUTOCOMMIT ON;

CURD

增 INSERT INTO 表名(字段1, 字段2, 字段3) VALUES(内容1, 内容2, 内容3);
删 DELETE FROM 表名 WHERE 条件;
改 UPDATE 表名 SET 字段1=内容1, 字段2=内容2, WHERE 条件;
查 SELECT 字段 FROM 表名 WHERE 条件;

常见数据库的数据类型

char 文本，富文本，可变长度
int 整型数据
float 浮点型数据
date 日期
timestamp 时间戳
bool 布尔值

常见的 SQL 关键字

关键字	解释
SELECT	查询字段内的内容
DISTINCT	对查询结果进行去重操作
AS	对查询结果的字段进行重命名
WHERE	添加查询条件
AND	逻辑与
OR	逻辑或
IN	查询某个字段是否存在后续的内容
BETWEEN	获取两个区间内的值
LIKE	提供范围，其中`_`占位单个字符串，`%`占位所有字符串
REGEXP	正则表达式
IS NULL	字段的值为空
ORDER BY	升序排列，后置 DESC 则为降序
LIMIT	限制查询范围
JOIN	多表查询
USING	多表查询时若存在共同字段则返回结果
LEFT JOIN	所有 FROM 表中的记录，无论是否与 ON 逻辑成立，LEFT JOIN 的表的结果都会返回
RIGHT JOIN	所有 RIGHT JOIN 表中的记录，无论是否与 ON 逻辑成立，FROM 的表的结果都会返回
NATURAL JOIN	自动合并表（不推荐）
CROSS JOIN	交叉合并（用于多选项合并）
UNION	直接将两个查询语句连接而不用分号隔开并返回查询结果
INSERT INTO … VALUES …	添加值
UPDATE … SET … WHERE …	修改值
GROUP BY	对值进行分组
HAVING	将分组后的值按条件进行查询，相当于GROUP BY前的WHERE
ROLLUP	将分组后的表进行统计

SQL 常用内置函数

统计类函数	解释
MAX	获取最大值
MIN	获取最小值
AVG	获取平均值
SUM	取和
COUNT	统计查询结果，若需去重需要配合 DISTINCT 关键字

数学函数	解释
ROUND	四舍五入
TRUNCATE	直接截取
CEILING	向左取整
FLOOR	向右取整
ABS	取绝对值
RAND	取随机数

字符串类函数	解释
LENGTH	获取字符串长度
UPPER	全部大写
LOWER	全部小写
TRIM	去除字符串左右的空格
LTRIM	去除字符串左侧的空格
RTRIM	去除字符串右侧的空格
LEFT	取字符串从左起至指定的字符
RIGHT	取字符串从右起至指定的字符
SUBSTRING	取字符串内的指定范围内的字符
LOCATE	定位字符在字符串中的位置
REPLACE	替换字符串
CONCAT	拼接字符

时间戳函数	解释
NOW	当前时间
CURTIME	当前时间
EXTRACT	EXTRACT (YEAR FROM NOW())
DATE_FORMAT	格式化时间
DATE_ADD	DATE_ADD(NOW(), INTERVAL 1 DAY)
DATE_SUB	时间戳相减
DATEDIFF	时间戳间隔
TIME_TO_SEC	时间转换为秒

逻辑函数	解释
IFNULL	若为空则替换为某个字符串
COALESCE	若为空则替换为某个字段
IF	IF(expression, true, false)
CASE	CASE WHEN THEN ELSE END

几个数据库的基础知识

表的主键不做强制要求，但建议设立
主键值必须唯一
每行必须有一个主键，不可为空
主键值不可被修改
主键值被删除后不可重用
表 A 的主键，可以作为表 B 的字段，此时不受以上规则约束

使用例子

使用 Limit 限制搜索条目减少查询时间
```
SELECT * FROM data
limit 100;
```

升序 / 降序排列

SELECT * FROM data
order by dataID [desc];

查找包含某个字符串

SELECT * FROM data
where dataset like '%string%';

对数据进行分组并求出其中的数量

SELECT dataset1, count(dataset2), count(1), count(*) FROM data
group by dataset1;

数据去重

SELECT dataset1, count(distinct dataset2) FROM data
group by dataset1;

针对 group by 分组后的结果进行过滤

SELECT * FROM data
group by dataset1
having count(dataset2) >= 100;

Python 中常用的逻辑判断可用于 SQL

SELECT count(if(dataset1 like 'string') OR if(dataset2 BETWEEN 1 AND 100)) FROM data
group by dataset3;

别名 as 必须在下一个筛选关键字段后才能使用

SELECT dataset1 as d1 From data
group by dataset2 having d1 > 10;

字符串查找

SELECT left(dataset1, 1), locate('string', dataset1, 3),
length(dataset1),
right(dataset1, length(dataset1) - locate('string', dataset1)),
substr(dataset1, 0, 3)
FROM data

多表查询 (表的自合并)

USE sql_store;
-- Explicity
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id
-- Implicity
SELECT * FROM order_items oi JOIN sql_inventory.products p USING (product_id)

快速复制整个表

-- Whole
CREATE TABLE orders_archived AS 
SELECT * FROM orders
-- Limit
INSERT INTO orders_archived
SELECT * FROM orders
WHERE order_date < "2019-01-01"

更新表内容

UPDATE invoices
SET payment_total = 10, payment_date = '2019-08-09'
WHERE invoice_id IN ((1, 2) AND 
(SELECT client_id FROM clients WHERE name = 'MyWorks'))

删除表的内容

DELETE FROM invoices
WHERE invoice_id = (SELECT * FROM clients WHERE name = "MyWorks")

查询数据分组

SELECT client_id, SUM(invoice_total) AS total_sales
FROM invoices 
GROUP BY client_id

交叉合并

-- Explicity
SELECT c.first_name AS customer, p.name AS product FROM customers c CROSS JOIN products p ORDER BY c.first_name
-- Implicity 
SELECT c.first_name AS customer, p.name AS product FROM customers c, orders o ORDER BY c.first_name

参考资料

SQL必知必会

搜索此博客

Akanization