《SQL 必知必会》- 阅读索引

2019-02-24  本文已影响12人  TWLESVCNZ

阅读总结

用于了解 SQL 的基础知识还是非常好的。

在使用时可以具体看 DBMS 文档。

以下 SQL 以 cnpmjs.org 数据库为例。

[toc]

第 1 课 了解 SQL

1.1 数据库基础

1.1.1 数据库

1.1.2 表

1.1.3 列

1.1.3 行

1.1.5 主键

1.2 什么是 SQL

1.3 动手实践

1.4 小结

第 2 课 检索数据

2.1 SELECT 语句

2.2 检索单个列

SELECT name FROM USER

2.3 检索多个列

SELECT id, name FROM user

2.4 检索所有列

SELECT * FROM user

2.5 检索不同的值

SELECT DISTINCT name FROM module

2.6 限制结果

# MySQL、MariaDB、PostgreSQL、SQLite
SELECT name from user LIMIT 10

# 第 5 行后的 10 行
SELECT name from user LIMIT 10 OFFSET 5

2.7 使用注释

SELECT name FROM user -- 注释
# 注释
/*
 * 注释
 */

2.8 小结

第 3 课 排序检索数据

3.1 排序数据

SELECT name from user ORDER BY name
SELECT name from user ORDER BY id

3.2 按多个列排序

SELECT id, name, email from user ORDER BY name, id

3.3 按列位置排序

SELECT id, name, email from user ORDER BY 2, 3
# =
SELECT id, name, email from user ORDER BY name, email

3.4 指定排序方向

# 先按 name 降序，再按 email 升序
SELECT id, name, email from user ORDER BY name DESC, email

3.5 小结

第 4 课 过滤数据

4.1 使用 WHERE 子句

SELECT * from user WHERE name = 'xiaoming'

4.2 WHERE 子句操作符

操作符 说明
=
<> 不等于
!= 不等于
<
<=
>
>=
BETWEEN ... AND ...
IS NULL

4.2.1 检查单个值

SELECT * from user WHERE id < 10

4.2.2 不匹配检查

SELECT * from user WHERE npm_user <> 1

4.2.3 范围值检查

SELECT * from user WHERE id BETWEEN 10 AND 100

4.2.3 空值检查

SELECT * from user WHERE email IS NULL

4.3 小结

第 5 课 高级数据过滤

5.1 组合 WHERE 子句

5.1.1 AND 操作符

SELECT *
FROM user
WHERE npm_user = 1 AND email IS NULL

5.1.2 OR 操作符

5.1.3 求值顺序

# npm_user = 0 或 1，同时 email 为空值
SELECT *
FROM user
WHERE (npm_user = 1 OR npm_user = 0) AND email IS NULL

5.2 IN 操作符

SELECT * 
FROM module
WHERE authoer IN ('xiaoming', 'xiaohong')
ORDER BY name

5.3 NOT 操作符

SELECT *
FROM user
WHERE NOT (npm_user = 1 AND id > 10)

5.4 小结

第 6 课 用通配符进行过滤

6.1 LIKE 操作符

6.1.1 百分号（%）通配符

# xiao 开头的 name
SELECT * 
FROM user
WHERE name LIKE 'xiao%'

6.1.2 下划线（_）通配符

# 两个字符的 163 邮箱
SELECT * 
FROM user
WHERE email LIKE '__@163.com'

6.1.3 方括号（[]）通配符

# 非 a 和 b 开头的 name
SELECT *
FROM user
WHERE name LIKE '[^ab]%'

6.2 使用通配符的技巧

6.3 小结

第 7 课 创建计算字段

7.1 计算字段

7.2 拼接字段

SELECT CONCAT(name, '[', email, ']') AS info
FROM user
WHERE id < 100

7.3 执行算术计算

SELECT dist_size / 1024 AS dist_kb
FROM module
LIMIT 100

7.4 小结

第 8 课 使用函数处理数据

8.1 函数

8.2 使用函数

8.2.1 文本处理函数

SELECT UPPER(name) AS name_upcase, email
From user
函数 说明
LEFT 返回字符串左边的字符
LENGTH 返回字符串长度
LOWER 返回字符串小写
UPPER 返回字符串大写
LTRIM 去字符串左边空格
RIGHT 返回字符串右边的字符
RTRIM 去字符串右边空格
SOUNDEX 返回字符串的 SOUNDEX 值（语音描述）

8.2.2 日期和时间处理函数

8.2.3 数值处理函数

函数 说明
ABS 绝对值
COS 余弦值
EXP 指数值
PI 圆周率
SIN 正弦值
SQRT 平方根
TAN 正切

8.3 小结

第 9 课 汇总数据

9.1 聚集函数

函数 说明
AVG 某列的平均值
COUNT 某列的行数
MAX 某列的最大值
MIN 某列的最小值
SUM 某列之和

9.1.1 AVG() 函数

SELECT AVG(dist_size) 
AS avg_dist_size 
FROM module

SELECT AVG(dist_size)
AS avg_babel_dist_size
FROM module
WHERE name LIKE '@babel/%'

9.1.1 COUNT() 函数

SELECT COUNT(*) AS user_count FROM user;
SELECT COUNT(email) AS user_with_email_count FROM user;

9.1.3 MAX() 函数

SELECT MAX(dist_size) AS max_dist_size FROM module

9.1.4 MIN() 函数

9.1.5 SUM() 函数

SELECT SUM(dist_size) AS total_dist_size FROM module
SELECT SUM(dist_size*LENGTH(name)) AS for_test FROM module

9.2 聚集不同的值

SELECT AVG(DISTINCT dist_size) 
AS avg_dist_size 
FROM module

9.3 组合聚集函数

SELECT 
MAX(dist_size) AS max_dist_size,
MIN(dist_size) AS min_dist_size,
FROM module

9.4 小结

第 10 课 分组数据

10.1 数据分组

10.2 创建分组

# 每个包及其版本数
SELECT name, COUNT(*) AS version_count 
FROM module 
GROUP BY name

10.3 过滤分组

# 版本数大于 100 的包及其版本数
SELECT name, COUNT(*) AS version_count 
FROM module 
GROUP BY name 
HAVING COUNT(*) >= 100
# 体积大于 1024 的版本数大于 100 的包及其版本
SELECT name, COUNT(*) AS version_count 
FROM module 
WHERE dist_size > 1024
GROUP BY name 
HAVING COUNT(*) >= 100

10.4 分组和排序

ORDER BY GROUP BY
对产生的输出排序 对行分组，输出不一定是分组的顺序
任意列都可以使用 只能使用选择列或表达式列
不一定需要 在与聚集函数一起使用时必要

10.5 SELECT 子句顺序

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

10.6 小结

第 11 课 使用子查询

11.1 子查询

11.2 利用子查询进行过滤

# 内部作者的包
SELECT DISTINCT name
FROM module
WHERE author IN (
    SELECT name 
    FROM user 
    WHERE npm_user = 0
)

11.3 作为计算字段使用子查询

# 每个有包的作者的包的数量
SELECT name, (
    SELECT COUNT(DISTINCT name) 
    FROM module
    WHERE module.author = user.name
) AS module_count
FROM user
HAVING module_count > 0

11.4 小结

第 12 课 联结表

12.1 联结

12.1.1 关系表

12.1.2 为什么使用联结

12.2 创建联结

SELECT module.name, version, user.name, email
FROM module, user
WHERE module.author = user.name

12.2.1 WHERE 子句的重要性

12.2.2 内联结

SELECT module.name, version, user.name, email
FROM module INNER JOIN user
ON module.author = user.name

12.2.3 联结多个表

12.3 小结

第 13 课 创建高级联结

13.1 使用表别名

SELECT m.name, version, u.name, email
FROM module AS m INNER JOIN user AS u
ON m.author = u.name

13.2 使用不同类型的联结

13.2.1 自联结

# 和 babel 同作者的包
SELECT DISTINCT m1.name, m1.author
FROM module AS m1, module AS m2
WHERE m1.author = m2.author AND m2.name = 'babel'

13.2.2 自然联结

13.3.3 外联结

# 作者及其包
SELECT DISTINCT u.name, u.email, m.name
FROM user AS u LEFT OUTER JOIN module AS m
ON u.name = m.author

13.3 使用带聚集函数的联结

# 所有没有发过包的用户
SELECT u.name, u.email, COUNT(DISTINCT m.name) AS module_count
FROM user AS u LEFT OUTER JOIN module AS m
ON u.name = m.author
GROUP BY u.name
HAVING module_count < 1

13.4 使用联结和联结条件

13.5 小结

第 14 课 组合查询

14.1 组合查询

14.2 创建组合查询

14.2.1 使用 UNION

SELECT name FROM user WHERE npm_user = 0
UNION
SELECT name FROM user WHERE email IS NULL
# 等于
SELECT name FROM user WHERE npm_user = 0 OR email IS NULL

14.2.2 UNION 规则

14.2.3 包含或取消重复的行

14.2.4 对组合查询结果排序

14.3 小结

第 15 课 插入数据

15.1 数据插入

15.1.1 插入完整的行

# 插入用户，可省略 allow null 列或有默认值的列
INSERT INTO user
(name, email, npm_user)
VALUES
('1', '2', 0)

15.1.2 插入部分行

15.1.3 插入检索出的数据

# 从 user2 中检索出 n 行数据插入 user
INSERT INTO user(name, email, npm_user)
SELECT name, email, npm_user FROM user2

15.2 从一个表复制到另一个表

# 创建新表 user2，并复制 user 数据
SELECT * INTO user2 FROM user
# mysql、sqllite 等
CREATE TABLE user2 AS
SELECT * FROM user

15.3 小结

第 16 课 更新和删除数据

16.1 更新数据

UPDATE user
SET email = NULL,
SET npm_user = 0
WHERE name = 'a'

16.2 删除数据

DELETE FROM user
WHERE name = 'a'

16.3 更新和删除的指导原则

16.4 小结

第 17 课 创建和操纵表

17.1 创建表

17.1.1 表创建基础

CREATE TABLE test (
    a CHAR(10) NOT NULL,
    b VARCHAR(1000) NOT NULL,
    c BIGINT(10) NOT NULL
)

17.1.2 使用 NULL 值

# 允许 b 为 NULL
CREATE TABLE test (
    a CHAR(10) NOT NULL,
    b VARCHAR(1000),
    c BIGINT(10) NOT NULL
)

17.1.3 指定默认值

CREATE TABLE test (
    a CHAR(10) NOT NULL DEFAULT 1,
    b VARCHAR(1000),
    t DATE NOT NULL DEFAULT CURRENT_DATE()
)

17.2 更新表

ALTER TABLE test
ADD d CHAR(20);
ALTER TABLE test
DROP COLUMN a;

17.3 删除表

DROP TABLE test;

17.4 重命名表

17.5 小结

第 18 课 使用视图

18.1 视图

18.1.1 为什么使用视图

18.1.2 视图的规划和限制

18.2 创建视图

18.2.1 利用视图简化复杂的联结

CREATE VIEW user_module AS
SELECT user.name , user.email, module.name as module
FROM user INNER JOIN module
on user.name = module.author;

18.2.2 用视图重新格式化检索出的数据

18.2.3 用视图过滤不想要的数据

18.2.4 使用视图与计算字段

18.3 小结

第 19 课 使用存储的过程

19.1 存储过程

19.2 为什么要使用存储过程

19.3 执行存储过程

19.4 创建存储过程

19.5 小结

第 20 课 管理事务处理

20.1 事务处理

20.2 控制事务处理

# MySQL
START TRANSACTION
# ...

20.2.1 使用 ROLLBACK

# 使用 ROLLBACK 撤销
DELETE FROM test;
ROLLBACK;

20.2.2 使用 COMMIT

# SQL server
BEGIN TRANSACTION;
DELETE ...
COMMIT TRANSACTION;

20.2.3 使用保留点

SAVEPOINT delete1;
# DELTE ...
ROLLBACK TO delete1;

20.3 小结

第 21 课 使用游标

21.1 游标

21.2 使用游标

21.3 小结

第 22 课 高级 SQL 特性

22.1 约束

22.1.1 主键

CREATE TABLE test (
    name CHAR(10) NOT NULL PRIMARY KEY;
);

ALTER TABLE test 
ADD CONSTRAINT PRIMARY KEY (name);

22.1.2 外键

CREATE TABLE module(
    author CHAR(20) NOT NULL REFERENCES user(name)
)

ALTER TABLE module
ADD CONSTRAINT FOREIGN KEY author REFERENCES user(name)

22.1.3 唯一约束

22.1.4 检查约束

CREATE TABLE module(
    dist_size INT(10) CHECK (dist_size < 1024)
)

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

22.2 索引

CREATE INDEX name_index ON user(name)

22.3 触发器

22.4 数据库安全

22.5 小结

附录 A 样例表脚本

附录 B 流行的应用程序

附录 C SQL 语句的语法

附录 D SQL 数据类型

附录 E SQL 保留字

上一篇下一篇

猜你喜欢

热点阅读