《head first sql》读书笔记

      大学的时候虽然闲的蛋疼学了点mysql，毕业之后一直折腾前端的东西，也没有系统地学习数据库，前几天从大学城图书馆借了本《head first sql》原著，辅以知乎和stackoverflow总算啃完了。非常推荐的一本入门书，有高中词汇就够了。虽然读的过程中觉得过于小白，但对夯实基础很有帮助。

----这篇文章不涉及原理和优化，以后补上----

本书的脉络可以分为如下几个部分：

0.基本的CRUD语句。

1.设计表。

2.查询表。

3.安全读写。

1.表设计：

CRUD语句不必赘述了。关于库表如何设计，我确实受益匪浅。在介绍表的设计之前，我们应当先了解一些前期知识：

primary key：由一个或多个列组成的约束，用来确保每条记录都是唯一的。不能为空。

     当表中的primary key确定后，其他的列都以之为依赖。即primary key决定了记录中其他列的值。比如学生的学号，学号502对应了张三，唯一标识了张三，则其他列的数据只能是对应于张三的数据(张三的班级、成绩、班主任)。

foreign key：由一个或多个列组成，它的值对应(references)于另一张表的primary key。数据库可以通过它将两张表联系起来。

      表不仅仅是用来存储记录的，通过它的结构，我们应当能了解到它描述的是什么信息。这就要求我们对业务的理解要清晰而深刻。当然，仅仅对业务熟悉并不足以让我们设计出好的表，由此书中开始介绍库表设计的前三个范式：

1FN：表中应当有一个primary key，列(field)中的值应当反映一个拆分到原子层面的信息。

      所谓原子层面，就是信息被我们拆分到不需要再拆分的程度，这个度是以业务为需要的（比如小明家在某小区A栋701，我们在库表中就没必要把7和01拆分）。这样我们在执行sql时就可以直接按该值进行检索，而不必再对该值进行处理。这在进行复杂查询时是非常必要的，同时也让这张表直观反映它存储了哪些东西。

且相同类型的数据在表中应当只有一个列来容纳它。

      这是出于避免冗余、避免后期不必要的解构操作的考虑。一个列中的值包含了多个同类型信息是不合理的，比如同款玩具有多种颜色、一个人有多个爱好，此时应当把颜色、爱好独立成表，再通过某种方式与表关联（junction table）。

2NF：在1NF的基础上，表中不存在部分函数依赖(partial functional dependency)。

    所谓部分函数依赖，举例：表1中有A、B、C三列。A和B共同构成primary key，但是C依赖于B而不是A、B共同构成的主键。这时候可以把B、C抽离独立成表2，并用表2对应的记录的id取代B的位置。2NF是为了保证表中的各列都有唯一的依赖源primary key。

3NF：在2NF的基础上，表中不存在传递函数依赖(transitive functional dependency)。

    所谓传递函数依赖，举例：表1中有A、B、C三列。A是primary key，B依赖于A，但C依赖于B，这时候应当把B、C独立成表2，返回对应Id给表1。此时表1中的各列数据保证只依赖于主键A，表2的各列只依赖于B。

当然，范式是用来参考的，并不一定要严格遵守。关于范式的疑问，我请教了不同公司的4个后台，他们说有时候为了速度只遵循到2NF，还有人说早忘了范式这东西，但是设计库表还是看经验和对业务的理解。

除了范式之外，我们在设计表之前，还应当了解表与表之间会有哪些关系。

1对1:比如员工信息T1和员工工资T2，通过员工id关联起来，此时是T1的一条记录对应T2的一条记录。

1对多：比如公安部要记录居民的出生地信息，此时有一张居民表T1和一张城市表T2，T1中每个居民的出生地只会对应T2中的某个城市。但T2中的一个城市可以对应T1中的多个居民。此时构成一对多的关系。

多对多：比如某会所要记录会员的日常爱好，有一张会员表T1和兴趣表T2.一个会员可以有多个兴趣，一个兴趣可以被多个会员勾选。此时会员与兴趣构成了多对多关系。

  当处理多对多的表关系时，比较推荐的做法是创建一张中间表(junction table)，比如上面的例子：

至此，我们知道了一张表要有主键，作为其他列的唯一依赖源，并保证每条记录的唯一性。表与表之间可以通过外键联系起来。表与表之间可能存在1对1、1对多、多对多的关系。

2.表查询：

  初学者很容易掌握CRUD的语句创建/删除一张表，插入、更新、删除记录，辅以DISTINCT、COUNT、LIMIT、GROUP BY、ORDER BY和工具函数sum()、avg()、max()、min()等对结果集进行排序、筛选。但是日常业务中我们往往需要将数据拆分成多张表，比如需要同时获取员工信息主表和与之相关的几张表的数据，以获取完整的信息。这时候sql给我们提供了以下几种关联方式：

2.1 subquery

我们对库表记录的增删查改称为查询(query)，subquery(或叫inner query)即在一个查询语句(containing query，或叫outer query)内部嵌另一个子查询。

子查询语句可以放在where从句里作为条件判断的依据，如下：

//shop_toy_table是商店里有的玩具，children_want_toy_table是孩子们想要的玩具，找出商店里孩子们想要的玩具的信息
SELECT name, type, price 
FROM shop_toy_table  
where type IN 
(SELECT DISTINCT type FROM children_want_toy_table); //如果where从句是一个相等比较，子查询的结果必须是一个值。

子查询也可以放在FROM关键字前面，将子查询的结果集作为一个列。

//my_contacts是联系人列表，zip_code是区码列表，找出联系人名字及其对应的所在州名称
SELECT mc.name,
(SELECT state FROM zip_code
WHERE mc.zip_code = zip_code) AS state
FROM  my_contacts AS mc;  //mc是outer query的别名，在子查询中要依赖mc这张表的zip_code作为筛选条件

根据inner query 和 outer query是否存在依赖关系，将子查询分为两种：non-correlated subquery(非关联子查询) 和 correlated subquery(关联子查询).上面第一个例子是非关联的，第二个例子因为要用到外部查询的表，所以是关联的。

2.2 JOIN

    子查询可以将多个查询组合成一个，因此使用子查询时我们不需要特意记录查询过程的中间结果，就像数学运算中(1+5)*6,括号中的运算是多少并不重要，因为我们只需要最后的结果。但是，如果我们上国内外网站查subquery和join，10个帖子有10个会告诉你使用subquery效率不高，大多数时候不如join。

    join分为inner join和outer join，当我们使用最原始的select查询多张表的column时，其实数据库默认使用的就是inner join。

INNER JOIN:内连接，主要分为交叉连接、相等连接、自然连接。

  在使用内连接的时候，SQL定义了两种语法表示连接，如下。

//隐式连接
SELECT t1.boy, t2.toy
FROM t1,t2;
//显式连接
SELECT t1.boy, t2.toy
FROM t1 inner join t2;

上面select的结果是t1和t2的笛卡尔积，即如果t1有5条记录，t2有10条记录，上面select的结果就是t1的每条记录对应上t2的每条记录，即5x10共50条记录。这种连接方式就是交叉连接，一种非常没有效率的查询。它先配对出所有可能的组合，再通过where从句对集合进行筛选。

相等连接：这种方式的特点是它的比较条件是一个相等表达式，如下：

SELECT * FROM 
A INNER JOIN B
ON A.id = B.id;

SQL提供一种简短符号表示相等连接：

SELECT * FROM 
A INNER JOIN B
USING(id);

自然连接：相等连接的进一步特例化，两张表中如果有同名的列，结果集里只会保留一个同名列。

SELECT * FROM 
A NATURAL JOIN B；

事实上，当我们对两张表进行关联查询的时候，我们很少会需要交叉连接来获取AxB的所有排列组合结果。我们更多是要抽取两张表中对应的记录，因此在生产中，INNER JOIN的本质是对两张表的纪录做一个交集操作。

    比如员工信息表有员工id和部门id，我们会关联员工信息表和部门信息表，获取需要的员工资料：

SELECT * from
employee_info AS e INNER JOIN dpt_info AS d
ON e.dpt_id = d.dpt_id;

  每次员工信息表和部门信息表匹配时，只有部门信息表中dpt_id与每个员工的dpt_id一致的结果被保留到结果集。

OUTER JOIN：外连接，分为左连接、右连接、全连接。

  左连接会保留左表的所有记录，右表如果没有匹配的记录，则其值返回NULL。

  比如员工信息表有100个人，今天老板新招了一个监察员小明，让他监察公司各部门的工作日常风纪。此时员工信息表多了第101条记录，但是小明暂时不从属于任何一个部门，其dpt_id为null。

SELECT * from
employee_info AS e LEFT JOIN dpt_info AS d
ON e.dpt_id = d.dpt_id;

执行上面的左连接后，因为除小明以外的每个员工只从属于一个部门，左表的每一条记录在右表中只有一个匹配的记录。而执行到小明这条记录时，因为小明的dpt_id为null，右表中没有与之匹配的记录，因而返回null。所以结果表共101条记录。

左连接操作是如何得出结果集的？可以简单地理解为，左表的每一条记录，分别与右表的每一条记录匹配，每一次匹配成功的记录都放到结果集里。左表第一条记录匹配完，左表第二条记录开始与右表匹配...直到左表最后一条记录匹配完毕。期间如果左表中的一条记录与右表全部记录匹配都失败了，结果集就插入一条记录，有左表的信息和右表的null。

右连接和左连接其实是一回事，只是顺序变了而已。右表中的每条记录依次与左表匹配，所以右表有100条记录的话，右连接的结果表也至少有100条记录。但是大部分时候右连接很少使用，因为都可以用左表实现。

全连接：是左、右连接的并集。但是mysql和几个主流的数据库都没有实现全连接的语法，我这里就懒得讲了。

2.3 VIEW

除了subquery和join之外，查询表还有一种很好用的手段，VIEW。所谓VIEW其实是一段sql语句的封装。就像我们在C语言中定义一个宏，或着一个函数名。

CREATE VIEW employee_salary AS  //创建view
SELECT * FROM 
employee e INNER JOIN salary s
ON e.id = s.id;

这样我们就定义了一个VIEW，以后只要我们select * from employee_salary;就能得到员工信息及薪资。其实在执行这条select命令时，数据库把它看作下面的命令：

SELECT * FROM 
(SELECT * FROM 
employee e INNER JOIN salary s
ON e.id = s.id) AS employee_salary;

使用view的好处是让我们不必每次都重复写一堆sql。通过view我们还能对库表进行增删查改，但是大部分时候没必要那样做。这里也不展开讲了。

DROP VIEW employee_salary;  //删除view

实际上VIEW分为可UPDATE的VIEW和不可UPDATE的VIEW。

可UODATE的VIEW包含源表所有NOT NULL的列。

不可UPDATE的VIEW不包含源表所有NOT NULL的列，因此是不允许插入和更新的。

3 安全读写

当我们进行团队协作时，数据库不是由你一个人来操作的，经常会分配某些人做某些部分，为了保证数据库中数据的安全，此时就有必要进行权限分配管理。

对了，在进行插入操作时，SQL有个CHECK手段，CREATE TABLE或ALTER TABLE时加入：

columnName dataType CHECK ( columnName condition ) //符合condition条件的才会被插入，否则报错。mySQL没有check但是有trigger

3.0 事务(transaction)

transaction中文叫事务，指的是一段特定的SQL操作，当操作失败时，会将库表的数据回滚到执行这段事务之前。

 START TRANSACTION；
  some sql query；//这里写我们要执行的sql操作
 COMMIT;//如果成功就commit，commit之后数据被真正修改
 ROLLBACK; //如果失败就rollback，rollback之后库表数据恢复操作前状态

3.1 创建用户

//下面的语句在mySQL可用，不同数据库要查看文档哦
CREATE USER userName IDENTIFIED BY 'password'

3.2 赋予权限

  被root用户创建的新用户是没有任何权限的。root用户可以给每个普通用户赋予权限：能访问哪些库表或库表中的某些列、能执行哪些SQL命令等。下面的代码赋予了userName权限对table的某个列进行SELECT操作。

GRANT SELECT(columnName) ON  //SELECT 可以换成UPDATE、INSERT、DELETE 或者ALL表示全部操作权限 把括号去掉则可对该表所有列执行操作
table   //table名可以换成*表示全部表
TO userName WITH GRANT OPTION; //可以多个users，通过都好隔开如 TO alan，mima; with grant option表示可以把自己的权限赋予别人

如果在赋予权限的时候添加了WITH GRANT OPTION，userName用户可以将该权限再次赋予别人。

一种极端的情况是赋予某人权限查看所有数据库的所有表：

GRANT SELECT on *.*
TO userName;

GRANT命令可以用在VIEW上，和一般的表一样。但如果某个VIEW是不可UPDATE的，则不允许INSERT|UPDATE操作。

3.3撤销权限

REVOKE SELECT(columnName) ON  
table   
FROM userName;

也可以只剥夺他“赋予权限的能力”：

REVOKE GRANT OPTION ON
SELECT(columnName) ON  
table   
FROM userName;

默认情况下，如果root用户赋予A用户 SELECT的权限，而A用户又赋予B用户 这个权限。当root剥夺了A用户的SELECT权限时，B用户也会丧失SELECT权限。这是出于安全的考虑。当我们在使用revoke命令时，可以选择关键字CASCADE或RESTRICT。

REVOKE SELECT(columnName) ON  
table   
FROM userName CASCADE;

使用CASCADE时，如果被赋予权限的A用户将该权限授予用户B，则用户A、B的该权限都会被剥夺。DEVOKE命令默认为CASCADE。

使用RESTRICT时，如果用户A未将权限授予其他人，则直接剥夺A的该权限。如果A将权限赋予过其他人，则返回一个error。RESTRICT的用意在于提醒你，A用户将权限授予过其他人，你需要找出A授予的是谁，因为你可能只是想剥夺A这个人的权限，而不影响其他人。

3.4通过角色管理多个账户的权限

授予角色：

  当用户数量增多时，一个个给user授权时很麻烦的；可是多人共享一个user时，又不好规范不同使用者的行为。于是出现了一种解决方案，角色。角色可以看作一组权限的合集。可以将角色赋予某个user，此时该user就拥有了角色所有的权限。

root账户通过GRANT xxx ON  table To user直接给某个角色授权的同时，还可以继续将某个角色赋予user：

CREATE ROLE oneRole;  //创建角色
GRANT SELECT ON table TO oneRole; //给角色赋予权限
GRANT oneRole TO user;  //将角色赋予user

当我们像删除某个角色时：

DROP ROLE someRole;

如果role被删除了，被role授权的角色也会失去role所赋予的权限。一个user可以被多个角色授权。

要注意的是，mysql development release 8.0.1之前是没有实现role的。但是它的兄弟MariaDB已经实现了。

当我们通过GRANT命令给user授权时，我们通过添加WITH GRANT OPTION让user拥有授权能力。通过ROLE授权也有类似的命令：

GRANT oneRole TO user WITH ADMIN OPTION; //此时user可以对其他用户授予角色。

撤销角色：

同样的REVOKE命令，同样效果的CASCADE和RESTRICT。

REVOKE oneRole FROM user CASCADE;
REVOKE oneRole FROM user RESTRICT;

3.5创建角色同时授权

GRANT  SELECT ON
table
TO user
IDENTIFIED BY 'password'

由于授权之前，数据库会自动自动检查user是否存在，不存在会自动以该名称创建用户，然后执行命令。

--全剧终