《SQL学习指南》阅读总结

1. 写在前面

SQL语句在工作中经常使用，之前阅读过《SQL必知必会》，但并没有系统的学习过SQL，国庆8天假期，借此机会系统的阅读了《SQL学习指南》一书。越来越发现每次阅读完一本书，如果能够好好的总结，用自己的语言复述一遍书中的主要知识点，往往能够得到更多收获。由于本文完全是我对着该书目录，按照自己的记忆进行的简单知识总结，所以文中知识点并不全面和严谨，仅供参考。

2. 阅读总结

SQL语言在多种数据库中都得到了广泛应用，无论是MYSQL、Orcale、SQL Server等关系型数据库，还是Hive、HBase等大数据组件，都可以使用SQL语言进行数据的查询等操作(HBase需要通过Phoenix来进行SQL查询，Phoenix是构建在HBase上的一个SQL层）。同时，不仅是数据库管理人员，程序开发人员、数据统计/挖掘人员也需要掌握一定的SQL。SQL语言主要包括实现对数据库的CRUD操作。

2.1 MySQL数据类型

2.1.1 字符数据类型

字符型数据主要为char(n)和varchar(n)，两种，char为定长类型，n为数据长度，当数据长度不够长时，向右使用空格补足。vachar可以做到变长，n为最大数据长度。因为varchar最大可以做到65535，所以一般情况下varchar即可满足字符类型的大部分需要，对于文本数据，则需要使用text类型。

2.1.2 数值类型

数值类型主要包括float、double、int等，而int由分为tinyint、smallint、smallint、int、mediumint、longint等，大小范围不同，当数据为无符号类型时，可以添加unsigned修饰，大小范围可扩大一倍。

2.1.3 时间类型

时间类型主要包括date(YYYY-MM-DD)、datetime(YYYY-MM-DD hh:mm:ss)、timestamp(YYYY-MM-DD hh:mm:ss)等几种，其中datetime范围比timestamp大，且timestamp的最大范围到2037年，datetime的最大范围到9999年，这是需要注意的一点。

2.2 MySQL数据操作

2.2.1 常用操作

代码以《SQL学习指南》书中提供的bank数据库为例。

1. mysql -u root -p 进入mysql命令行模式，user为root, 默认password为空
2. show databases 查看数据库列表
3. use bank 进入bank数据库
4. show tables; 可以查看bank数据库下的存在的数据表列表
5. desc account; 可以查看account表的表结构
6. CREATE TABLE test (id int auto_increment, name varchar(30) NOT NULL, city varchar(20), PRIMARY KEY id_primary_key (id)); 可以创建一个名为test的表，其中id为int类型，自增， name为varchar(30)类型，不能为NULL， city为varchar(20)类型，默认可以为NULL，并指定id为主键，主键不能为NULL
7. DROP TABLE test; 可以删除test表
8. INSERT INTO test (id, name, city) VALUES (null, 'Albert Lee', 'Beijing'; 可以向test表中添加数据，其中id可以省略
9. UPDATE test SET name='Devin Lee', city='Shenzhen' WHERE id=1;可以修改test表中id=1的行，将name和city都修改为指定数据，进行UPDATE操作时务必添加WHERE条件，指定需要修改的行，否则会对表中所有行进行UPDATE操作
10. DELETE FROM test WHERE id=1; 可以删除id=1的行
11. SELECT * FROM test; 可以查询test表，也可以指定所要查询的特定列，也可以使用WHERE添加过滤信息
12. ALTER TABLE test DROP COLUMN city;删除city列
13. ALTER TABLE test CHANGE name name2 varchar(50) not null;将name列修改为name2，且类型更改为varchar(50)
14. ALTER TABLE test ADD post_code vahrchar(20) not null default '0';添加post_code列，不允许为NULL，且默认值为’0’

    2.2.2 关于字符串函数与时间函数

    concat函数可以在select中使用对字符串进行拼接，like函数可以在where中使用实现模糊查询，其中%代表任意个(包括0个)字符，_ 表示一个字符。
    时间函数较多，包括将字符串转换为时间类型，提取年月日，对时间类型增加指定时间等操作。UNIX_TIMESTAMP('1999-10-10') 将日期转换为UNIX时间戳，FROM_UNIXTIME(875996580) 将UNIX时间戳转换为日期。

    2.2.3 非关联子查询与关联子查询

    非关联子查询与关联子查询是非常重要的概念。两者之间的区别在于，非关联子查询独立于外部表，在查询时首先运行子查询，当非关联子查询整体都运行完，才运行外部查询。关联子查询与外部表相关联，关联子查询是对外部查询的每一行都运行一次关联子查询。

    select * from account where open_branch_id in (select branch_id from branch where name like ('W%'));

该语句即为一条包含非关联子查询的SQL语句，首先运行select branch_id from branch where name like ('W%')从branch表中查询出name以W开头的branch_id，然后 在account中使用where open_branch_id in过滤条件查询出account表中open_branch_cd符合子查询结果的行。该语句首先运行子查询，带子查询整体运行完，使用查询出的结果集作为外部的过滤集，再进行外部查询。因此该语句的子查询属于非关联子查询。

select a.account_id, (select b.name from branch b where a.open_branch_id = b.branch_id) branch_name from account a;

account表共24行数据，该语句对account表的每一行都会运行一次select b.name from branch b where a.open_branch_id = b.branch_id子查询且子查询中where的过滤条件用到了外部表account，因此该语句中的子查询属于关联子查询。

2.2.4 关于连接

SQL中经常需要使用多个表进行联合查询，这种情况下就需要用到连接操作JOIN。常用的JOIN分为inner join、left outer join、right outer join以及accross join。

select a.account_id, b.name from account inner join branch b on a.open_branch_id=b.branch_id;

inner join内连接为默认连接，该语句即为一个内连接操作，内连接根据连接条件，将两个表的数据连接在一起，假如account表中的某行数据open_branch_id在branch表中没有对应的branch_id或branch表中某行数据的branch_id在account表中没有对应的open_branch_id，则该行不会在结果集中出现。即inner join内连接的结果集只会包含在两个表中都存在对应的数据。
left outer join与right outer join相对应。

select a.account_id, b.name from account left outer join branch b on a.open_branch_id=b.branch_id;

该语句作为左外连接，结果集中以join左边的account表为准，包含account表的所有行，若account表的某行在branch表中没有与之对应的，则结果集中该行的b.name设为NULL。

select a.account_id, b.name from account right outer join branch b on a.open_branch_id=b.branch_id;

该语句作为右外连接，结果集中以join右边的branch表为准，包含branch表的所有行，若branch表的某行在account表中没有与之对应的，则结果集中该行的a.account_id设为NULL。
left outer join 与right outer join是可以相互转换的，只需要调换两表的位置即可。

2.2.5 关于分组与HAVING

在SQL语句中，我们经常需要对查询的数据按照特定列进行分组，求数据的MAX()最大值、MIN()最小值、AVG()平均值、COUNT()行数以及SUM()求和等操作，因此需要GROUP BY()命令，同时也可以对结果集按照分组后的结果进行过滤，比如SUM()>100的过滤条件，这种情况下，WHERE语句是无法满足的，因为WHERE的过滤条件只能是分组之前的，针对分组之后的过滤操作，需要使用HAVING命令。所以在SQL语句中GROUP BY与HAVING命令在WHERE之后。

select open_branch_id, sum(avail_balance) abc from account where open_date > '2004-06-01' group by open_branch_id having abc>10000;

该语句中对account表首先使用where过滤出open_date > ‘2004-06-01’的行，并使用group by 对这些行按照open_branch_id分组对avail_balance求和，然后使用having过滤出sum(avail_balance)>10000的行，得出最后的结果集。

2.2.6 关于索引与约束

当我们使用SQL查询特定数据时，比如where branch_id =2这种过滤条件，MySQL服务器如果扫描branch表的所有行来查找，性能会很差。这时候就需要用到INDEX索引，我们可以在branch表中对branch_id列建索引，索引相当于对branch_id建了另一张表，记录了每个branch_id在branch表中对应的行位置，有了这个索引，当根据branch_id查找特定行时，不需要扫描branch整张表，而是根据索引查找branch对应的行，性能会好很多。
我们可以在create创建表时在表中添加索引，索引分为normal普通索引、unique唯一索引和full text文本索引，也可以使用ALTER branch ADD INDEX branch_index (branch_id);在branch表中为branch_id列添加索引。SHOW INDEX FROM branch;
SQL中的约束包括唯一约束、外键约束、检查约束等，约束使用CONSTRAINT关键字修饰，KEY相当于索引约束，在创建PRIMARY KEY主键时自动创建唯一约束和唯一索引，创建FOREIGN KEY外键时需要使用REFERENCES指定关联的外部表和列，并自动创建外键约束和普通索引。

2.2.7 关于锁与事务

1. MySQL根据存储引擎的不同锁机制也不相同，有表锁、页面锁、行锁等，保证了并发性。
2. 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
3. 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
4. 事务用来管理 insert,update,delete 语句
5. 事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
6. 事务的原子性：一组事务，要么成功；要么撤回。
7. 稳定性 ：有非法数据（外键约束之类），事务撤回。
8. 隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
9. 可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得
10. BEGIN TRANSACTION 开始事务
11. COMMIT 提交事务
12. ROLLBACK 回滚
13. SAVEPOINT 设置还原点
14. ROLLBACK TO a 回滚至a还原点
15. 事务分为自动提交和手动提交，可以进行设置

    2.2.8 关于视图

    SQL中的视图主要用途为隐藏敏感信息或向用户提供有限的数据。

    CREATE VIEW customer_vw (cust_id, fed_id, city) AS (SELECT cust_id, SUBSTRING(fed_id, 8, 4), city FROM customer);

该语句创建了customer_vw视图，向用户提供了customer表的cust_id、SUBSTRING(fed_id, 8, 4)、city列，隐藏了fed_id列的敏感信息。
然后我们可以像查询正常表一样，查询customer_vw视图了，需要注意的是向视图中使用INSERT/UPDATE操作时，并不是所有视图都可以，需要根据视图的创建语句而定。