SQL学习笔记
F9执行
表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。
简介
为什么需要数据库？
如果要保存学校所有班级的信息，可以写入另一个CSV文件。
但是，随着应用程序的功能越来越复杂，数据量越来越大，如何管理这些数据就成了大问题：
- 读写文件并解析出数据需要大量重复代码；
- 从成千上万的数据中快速查询出指定数据需要复杂的逻辑。
如果每个应用程序都各自写自己的读写数据的代码，一方面效率低，容易出错，另一方面，每个应用程序访问数据的接口都不相同，数据难以复用。
所以，数据库作为一种专门管理数据的软件就出现了。应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件，那是数据库软件的事情，应用程序自己并不关心。
数据模型
数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：
- 层次模型
- 网状模型
- 关系模型
随着时间的推移和市场竞争，最终，基于关系模型的关系数据库获得了绝对市场份额。
主流关系数据库
目前，主流的关系数据库主要分为以下几类：
- 商用数据库，例如：Oracle，SQL Server，DB2等；
- 开源数据库，例如：MySQL，PostgreSQL等；
- 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
- 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。
SQL
什么是SQL？SQL是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库，都支持SQL，这样，我们通过学习SQL这一种语言，就可以操作各种不同的数据库。
虽然SQL已经被ANSI组织定义为标准，不幸地是，各个不同的数据库对标准的SQL支持不太一致。并且，大部分数据库都在标准的SQL上做了扩展。也就是说，如果只使用标准SQL，理论上所有数据库都可以支持，但如果使用某个特定数据库的扩展SQL，换一个数据库就不能执行了。例如，Oracle把自己扩展的SQL称为PL/SQL，Microsoft把自己扩展的SQL称为T-SQL。
现实情况是，如果我们只使用标准SQL的核心功能，那么所有数据库通常都可以执行。不常用的SQL功能，不同的数据库支持的程度都不一样。而各个数据库支持的各自扩展的功能，通常我们把它们称之为“方言”。
总的来说，SQL语言定义了这么几种操作数据库的能力：
DDL：Data Definition Language
DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
DML：Data Manipulation Language
DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
DQL：Data Query Language
DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。
语法特点
SQL语言关键字不区分大小写！！！但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。
数据类型
| 名称 | 类型 | 说明 |
|---|---|---|
| INT | 整型 | 4字节整数类型，范围约+/-21亿 |
| BIGINT | 长整型 | 8字节整数类型，范围约+/-922亿亿 |
| REAL | 浮点型 | 4字节浮点数，范围约+/-1038 |
| DOUBLE | 浮点型 | 8字节浮点数，范围约+/-10308 |
| DECIMAL(M,N) | 高精度小数 | 由用户指定精度的小数， 例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算 |
| CHAR(N) | 定长字符串 | 存储指定长度的字符串， 例如，CHAR(100)总是存储100个字符的字符串 |
| VARCHAR(N) | 变长字符串 | 存储可变长度的字符串， 例如，VARCHAR(100)可以存储0~100个字符的字符串 |
| BOOLEAN | 布尔类型 | 存储True或者False |
| DATE | 日期类型 | 存储日期，例如，2018-06-22 |
| TIME | 时间类型 | 存储时间，例如，12:20:59 |
| DATETIME | 日期和时间类型 | 存储日期+时间，例如，2018-06-22 12:20:59 |
主键
用来唯一定位记录的
选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。
对于大部分应用来说，通常自增类型的主键就能满足需求。
如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。
联合主键
关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。
对于联合主键，允许一列有重复，只要不是所有主键列都重复即可。
主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。
可以使用多个列作为联合主键，但联合主键并不常用。
外键
把数据与另一张表关联起来，这种列称为外键。
外键并不是通过列名实现的，而是通过定义外键约束实现的：
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
其中，ALTER TABLE students 更改表students，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。
通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。
由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。
要删除一个外键约束，也是通过ALTER TABLE实现的：
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;
关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。
索引
索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。批注：排序。
可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。
增加唯一索引
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
批注：增加唯一索引的作用大概是让数据库帮忙检查错误吧。
查询
基本查询
SELECT * FROM <表名>
SELECT * FROM students
SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询
SELECT语句其实并不要求一定要有FROM子句。
不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。
条件查询
SELECT * FROM 会查询整张表，但有时候我们只想查找特定条件的数据：
SELECT * FROM <表名> WHERE <条件表达式>
SELECT * FROM students WHERE score >= 80;
可以使用NOT、AND、OR编写条件
如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级：
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
常用的条件表达式
| 条件 | 表达式举例1 表达式举例2 | 说明 |
|---|---|---|
| 使用=判断相等 | score = 80 name = 'abc' | 字符串需要用单引号括起来 |
| 使用>判断大于 | score > 80 name > 'abc' | 字符串比较根据ASCII码，中文字符比较根据数据库设置 |
| 使用>=判断大于或相等 | score >= 80 name >= 'abc' | 无 |
| 使用<判断小于 | score < 80 name <= 'abc' | 无 |
| 使用<=判断小于或相等 | score <= 80 name <= 'abc' | 无 |
| 使用<>判断不相等 | score <> 80 name <> 'abc' | 无 |
| 使用LIKE判断相似 | name LIKE 'ab%' name LIKE '%bc%' | %表示任意字符，例如'ab%'将匹配'ab'，'abc'，'abcd' |
投影查询
使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。
批注：查询指定列。
SELECT语句可以对结果集的列进行重命名。例如：
SELECT id, score points, name FROM students WHERE gender = 'M';
id points name
1 90 小明
3 88 小军
6 55 小兵
7 85 小林
9 89 小王
排序
使用 ORDER BY 进行排序。
正序排列：
SELECT id, name, gender, score FROM students ORDER BY score;
反序排列：
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
分页
使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。
可以通过 LIMIT
LIMIT 3 OFFSET 9;
上述查询对结果集从9号记录开始，取3条。注意SQL记录集的索引从0开始。
OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。
聚合查询
如果我们要统计一张表的数据量，例如，想查询students表一共有多少条记录，难道必须用SELECT * FROM students查出来然后再数一数有多少行吗？
这个方法当然可以，但是比较弱智。对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。
统计总行数：
SELECT COUNT(*) FROM students;
10
其他聚合函数：
| 函数 | 说明 |
|---|---|
| SUM | 计算某一列的合计值，该列必须为数值类型 |
| AVG | 计算某一列的平均值，该列必须为数值类型 |
| MAX | 计算某一列的最大值 |
| MIN | 计算某一列的最小值 |
分组
根据班级id class_id进行分组查询：
SELECT COUNT(*) num FROM students GROUP BY class_id;
num
4
3
3
多表查询
SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。
批注：作用不明。
SELECT * FROM <表1> <表2>
SELECT * FROM students, classes;
id class_id name gender score id name
1 1 小明 M 90 1 一班
1 1 小明 M 90 2 二班
这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。
这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。
你可能还注意到了，上述查询的结果集有两列id和两列name，两列id是因为其中一列是students表的id，而另一列是classes表的id，但是在结果集中，不好区分。两列name同理
要解决这个问题，我们仍然可以利用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：
SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;
sid name gender score cid cname
1 小明 M 90 1 一班
1 小明 M 90 2 二班
1 小明 M 90 3 三班
普通的多表查询
SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;
多表查询且设置别名
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c;
多表查询+条件查询
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;
连接查询
内连接
选出所有学生，同时返回班级名称
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
id name class_id class_name gender score
1 小明 1 一班 M 90
2 小红 1 一班 F 95
3 小军 1 一班 M 88
注意INNER JOIN查询的写法是：
先确定主表，仍然使用FROM <表1>的语法；
再确定需要连接的表，使用INNER JOIN <表2>的语法；
然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
可选：加上WHERE子句、ORDER BY等子句。
修改
- INSERT：插入新记录；
- UPDATE：更新已有记录；
- DELETE：删除已有记录。
INSERT
INSERT语句的基本语法是：
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
例如，插入一条记录：
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
插入两条记录：
INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);
SELECT * FROM students;
UPDATE
UPDATE语句的基本语法是：
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
例如：
UPDATE students SET name='大牛', score=66 WHERE id=1;
在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：
UPDATE students SET score=score+10 WHERE score<80;
DELETE
DELETE语句的基本语法是：
DELETE FROM <表名> WHERE ...;
例如：
DELETE FROM students WHERE id>=5 AND id<=7;
DELETE FROM students WHERE id=999;
事务
在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。例如，一个转账操作：
-- 从id=1的账户给id=2的账户转账100元
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
这两条SQL语句必须全部执行，或者，由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销。
这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。
要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务，例如，把上述的转账操作作为一个显式事务：
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
主流关系数据库
目前，主流的关系数据库主要分为以下几类：
- 商用数据库，例如：Oracle，SQL Server，DB2等；
- 开源数据库，例如：MySQL，PostgreSQL等；
- 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
- 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。
