由于csdn的界面太乱感觉不适合写笔记。所以又把csdn写的笔记搬到语雀了。

学习笔记内容基于《SQL基础第二版》可以在图灵社区或者微信读书上查看。

1. 初探SQL

1.1 SQL是什么

SQL全称Structured Query Language，即结构化查询语言。是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统（RDBMS）。

还有一种是应用于非关系型数据库系统的查询语言NoSQl（ Not Only SQL，不仅仅是SQL），NoSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。开源的NoSQl数据库有Membase和 MongoDB 等。

2. 初探数据库

2.1 数据库是什么

数据库是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。该数据集合称为数据库（Database，DB）。
用来管理数据库的计算机系统称为数据库管理系统（Database Management System，DBMS）。

所以人们常说的数据库，如MySQL、SQL Server、DB2这些，实际上指的是数据库管理系统。

2.2 为什么使用数据库管理系统

我们通过计算机管理数据的时候，通常使用文本文件或者Excel那样的电子制表软件就可以完成了，非常简单。

通过文本文件或者电子制表软件来管理数据的方法非常简便，但也有不足。下面就举几个有代表性的例子。

  • 无法多人共享数据
    虽然现在的在线协同办公软件可以处理电子表格的即时共享与同步修改，但即时共享的数据量、数据类型、和共享人数都有所局限。而数据库管理系统理论上是可以储存各种各样的数据类型，并且支持高几个数量级的多用户同时访问。


  • 无法提供操作大量数据所需的格式
    要想瞬间从几十万或者上百万的数据中获取想要的数据，必须把数据保存为适当的格式，但是文本文件和Excel工作表等无法提供相应的格式。


  • 实现读写自动化需要编程能力
    通过编写计算机程序（以下简称程序）可以实现数据读取和编辑自动化，但这必须以了解数据结构为前提，还需具备一定的计算机编程技术。


  • 无法应对突发事故
    当文件被误删、硬盘出现故障等导致无法读取的时候，可能会造成重要数据丢失，同时数据还可能被他人轻易读取或窃用。

DBMS可以克服这些不足，实现多个用户同时安全简单地操作大量数据。这也是我们一定要使用DBMS的原因。
Task01：初识SQL 与 数据库 - 图1

2.3 数据库管理系统的种类

DBMS 主要通过数据的保存格式（数据库的种类）来进行分类，现阶段主要有以下 5 种类型.

  • 层次数据库（Hierarchical Database，HDB）
    层次模型数据库系统是最早研制成功的数据库系统，这种数据库最成功的典型是IMS。
    IMS的全称时Information Management System，由IBM公司研制成功。

  • 关系数据库（Relational Database，RDB）
    这种类型的 DBMS 称为关系数据库管理系统（Relational Database Management System，RDBMS）。比较具有代表性的 RDBMS 有如下 5 种。
          ①Oracle Database：甲骨文公司的RDBMS
          ②SQL Server：微软公司的RDBMS
          ③DB2：IBM公司的RDBMS
          ④PostgreSQL：开源的RDBMS
          ⑤MySQL：开源的RDBMS

  • 面向对象数据库（Object Oriented Database，OODB）
    编程语言当中有一种被称为面向对象语言的语言 。把数据以及对数据的操作集合起来以对象为单位进行管理，因此得名。面向对象数据库就是用来保存这些对象的数据库。


  • XML数据库（XML Database，XMLDB）
    最近几年，XML 作为在网络上进行交互的数据的形式逐渐普及起来。XML数据库可以对XML形式的大量数据进行高速处理。


  • 键值存储系统（Key-Value Store，KVS）
    这是一种单纯用来保存查询所使用的主键（Key）和值（Value）的组合的数据库。具有编程语言知识的读者可以把它想象成关联数组或者散列（hash）。近年来，随着键值存储系统被应用到Google等需要对大量数据进行超高速查询的Web服务当中，它正逐渐为人们所关注。
    举例：MongoDB

本次使用的是SQL 语言的数据库管理系统，也就是关系数据库管理系统（RDBMS）的操作方法。

2.4 RDBMS的常见系统结构

使用RDBMS时，最常见的系统结构就是客户端/服务器类型（C/S类型）这种结构（图1-3）。

即Client/Server，还有一种系统结构是Browser/Server。Client/Server是建立在局域网的基础上的；Browser/Server是建立在广域网的基础上的。主要在设备要求、网络要求、用户群体、信息保密性、程序架构、系统维护等方面有所不同。
参考资料：https://zhidao.baidu.com/question/553162051341443132.html
Task01：初识SQL 与 数据库 - 图2

  • RDBMS通常使用客户端/服务器这样的系统结构。
  • RDBMS既可以和其客户端安装在同一台计算机上，也可以分别安装在不同的计算机上。这样一来，不仅可以通过网络使二者相互关联，还可以实现多个客户端访问同一个RDBMS。
  • 通过从客户端向服务器端发送SQL语句来实现数据库的读写操作。
  • 关系数据库采用被称为数据库表的二维表来管理数据。根据SQL语句的内容返回的数据同样必须是二维表的形式。
  • 数据库表由 表示数据项目的列（字段） 和 表示一条数据的行（记录） 所组成，以记录为单位进行数据读写。
  • 这里将行和列交汇的方格称为单元格，每个单元格只能输入一个数据。
    Task01：初识SQL 与 数据库 - 图3

3. SQL的使用

3.1 标准SQL

国际标准化组织（ISO）为SQL制定了相应的标准，以此为基准的SQL称为标准SQL（相关信息请参考专栏——标准SQL和特定的SQL）。
完全基于标准 SQL 的 RDBMS 很少，通常需要根据不同的 RDBMS 来编写特定的 SQL 语句，原则上，本课程介绍的是标准 SQL 的书写方式。

3.2 三类SQL 语句

根据对 RDBMS 赋予的指令种类的不同，SQL 语句可以分为以下三类：

  1. DDL（Data Definition Language，数据定义语言）
    用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令。
          · CREATE ： 创建数据库和表等对象
          · DROP ： 删除数据库和表等对象
          · ALTER ： 修改数据库和表等对象的结构
  1. DML（Data Manipulation Language，数据操纵语言）
    用来查询或者变更表中的记录。DML 包含以下几种指令。
          · SELECT ：查询表中的数据
          · INSERT ：向表中插入新数据
          · UPDATE ：更新表中的数据
          · DELETE ：删除表中的数据
  1. DCL（Data Control Language，数据控制语言）
    用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。
          · COMMIT ： 确认对数据库中的数据进行的变更
          · ROLLBACK ： 取消对数据库中的数据进行的变更
          · GRANT ： 赋予用户操作权限
          · REVOKE ： 取消用户的操作权限

实际使用的 SQL 语句当中有 90% 属于 DML（即人们口中常说的增删改查），教程里同样是以 DML 为中心进行讲解。

3.2 SQL的基本书写规则

  • SQL语句要以英文分号（ ; ）结尾，中文分号（；）不行
  • SQL 不区分关键字的大小写，但是插入到表中的数据是区分大小写的
  • win 系统默认不区分表名及字段名的大小写
  • linux / mac 默认严格区分表名及字段名的大小写
  • 本教程已统一调整表名及字段名的为小写，以方便初学者学习使用。··
  • 常数的书写方式是固定的
    ‘abc’, 1234, ‘26 Jan 2010’, ‘10/01/26’, ‘2010-01-26’…
  • 单词需要用半角空格或者换行来分隔
  • SQL 语句的单词之间需使用半角空格或换行符来进行分隔，且不能使用全角空格作为单词的分隔符，否则会发生错误，出现无法预期的结果。把输入法切换到英文输入状态可以极大地避免出现因为字符导致奇怪的语法问题。

请认真查阅《附录1 - SQL 语法规范》，养成规范的书写习惯。
附录1链接：http://datawhale.club/t/topic/798

3.3 创建数据库和表

3.3.1 数据库的创建（CREATE DATABASE 语句）
语法：

  1. CREATE DATABASE < 数据库名称 > ;

例子：创建本次教程使用的，一个名为 shop 的数据库。

  1. CREATE DATABASE shop ;

Task01：初识SQL 与 数据库 - 图4

3.3.2 表的创建（CREATE TABLE 语句）
语法：

  1. CREATE TABLE < 表名 >
  2. ( < 列名 1> < 数据类型 > < 该列所需约束 > ,
  3.   < 列名 2> < 数据类型 > < 该列所需约束 > ,
  4.   < 列名 3> < 数据类型 > < 该列所需约束 > ,
  5.   < 列名 4> < 数据类型 > < 该列所需约束 > ,
  6.   .
  7.   .
  8.   .
  9.   < 该表的约束 1> , < 该表的约束 2> ,……);

例子：创建本课程用到的商品表 product 。

注意，需要切换到具体的数据库，才可以进行创表操作！
切换到某一数据库的语法为 USE <数据库名> ; 。

  1. CREATE TABLE product
  2. (product_id CHAR(4) NOT NULL,
  3.  product_name VARCHAR(100) NOT NULL,
  4.  product_type VARCHAR(32) NOT NULL,
  5.  sale_price INTEGER ,
  6.  purchase_price INTEGER ,
  7.  regist_date DATE ,
  8.  PRIMARY KEY (product_id));

Task01：初识SQL 与 数据库 - 图5

3.4 命名规则

  • 只能使用 半角英文字母、数字、下划线（_） 作为数据库、表和列的名称
  • 名称必须以半角英文字母开头

表1-3 商品表和 product 表列名的对应关系
Task01：初识SQL 与 数据库 - 图6

3.5 数据类型的指定

数据库创建的表，所有的列都必须指定数据类型，每一列都不能存储与该列数据类型不符的数据。

四种最基本的数据类型

  • INTEGER 型
    用来指定存储整数的列的数据类型（数字型），不能存储小数。
  • CHAR 型
    用来存储定长字符串，当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足，由于会浪费存储空间，所以一般不使用。
  • VARCHAR 型
    用来存储可变长度字符串，定长字符串在字符数未达到最大长度时会用半角空格补足，但可变长字符串不同，即使字符数未达到最大长度，也不会用半角空格补足。
  • DATE 型
    用来指定存储日期（年月日）的列的数据类型（日期型）。

此外还有布尔值、NULL 值、单精度浮点数、双精度浮点数以及四种基本类型的拓展使用等等。

3.6 约束的设置

约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能。

  • NOT NULL是非空约束，即该列必须输入数据。
  • PRIMARY KEY是主键约束，代表该列是唯一值，可以通过该列取出特定的行的数据。

此外还有默认值DEFAULT、唯一约束UNIQUE、外键约束FOREIGN KEY等创表时的常用约束。

3.7 表的删除和更新

  • 删除表的语法：

    1. DROP TABLE < 表名 > ;

  • 删除 product 表
    需要特别注意的是，删除的表是无法恢复的，只能重新插入，请执行删除操作时要特别谨慎。

    1. DROP TABLE product;

Task01：初识SQL 与 数据库 - 图7

  • 添加列的ALTER TABLE语句
    1. ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;

添加一列可以存储100位的可变长字符串的 product_name_pinyin 列

  1. ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);

Task01：初识SQL 与 数据库 - 图8

  • 删除列的 ALTER TABLE 语句
    1. ALTER TABLE < 表名 > DROP COLUMN < 列名 >;

删除 product_name_pinyin 列

  1. ALTER TABLE product DROP COLUMN product_name_pinyin;

Task01：初识SQL 与 数据库 - 图9

ALTER TABLE 语句和 DROP TABLE 语句一样，执行之后无法恢复。误添的列可以通过 ALTER TABLE 语句删除，或者将表全部删除之后重新再创建。

3.8 向表中插入数据

基本语法：

  1. INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);

对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES子句的值会默认按照从左到右的顺序赋给每一列。

  1. -- 包含列清单
  2. INSERT INTO product (product_id, product_name, product_type, 
  3. sale_price, purchase_price, regist_date) 
  4. VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
  6. -- 省略列清单
  7. INSERT INTO product
  8. VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

Task01：初识SQL 与 数据库 - 图10

原则上，执行一次 INSERT 语句会插入一行数据。插入多行时，通常需要循环执行相应次数的 INSERT 语句。其实很多 RDBMS 都支持一次插入多行数据

  1. -- 通常的INSERT
  2. INSERT INTO product VALUES ('0002', '打孔器', 
  3. '办公用品', 500, 320, '2009-09-11');
  4. INSERT INTO product VALUES ('0003', '运动T恤', 
  5. '衣服', 4000, 2800, NULL);
  6. INSERT INTO product VALUES ('0004', '菜刀', 
  7. '厨房用具', 3000, 2800, '2009-09-20');
  9. -- 多行INSERT （ DB2、SQL、SQL Server、 PostgreSQL 和 MySQL多行插入）
  10. INSERT INTO product VALUES ('0002', '打孔器', 
  11. '办公用品', 500, 320, '2009-09-11'),
  12. ('0003', '运动T恤', '衣服', 4000, 2800, NULL),
  13. ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');  
  15. -- Oracle中的多行INSERT
  16. INSERT ALL INTO product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')
  17. INTO product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL)
  18. INTO product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')
  19. SELECT * FROM DUAL;  
  20. -- DUAL是Oracle特有（安装时的必选项）的一种临时表A。因此“SELECT *FROM DUAL” 部分也只是临时性的，并没有实际意义。

Task01：初识SQL 与 数据库 - 图11

INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。想要插入 NULL 的列一定不能设置 NOT NULL 约束。

  1. INSERT INTO product (product_id, product_name, product_type, 
  2. sale_price, purchase_price, regist_date) VALUES ('0006', '叉子', 
  3. '厨房用具', 500, NULL, '2009-09-20');

Task01：初识SQL 与 数据库 - 图12

还可以向表中插入默认值（初始值）。可以通过在创建表的CREATE TABLE 语句中设置DEFAULT约束来设定默认值。

  1. CREATE TABLE ProductCopy
  2. (product_id CHAR(4) NOT NULL,
  3.  product_name VARCHAR(100) NOT NULL,
  4.  product_type VARCHAR(32) NOT NULL,
  5.  sale_price INTEGER DEFAULT 0, /* 销售单价的默认值设定为0 */
  6.  purchase_price INTEGER ,
  7.  regist_date DATE ,
  8.  PRIMARY KEY (product_id));

Task01：初识SQL 与 数据库 - 图13

可以使用INSERT … SELECT 语句从其他表复制数据。

  1. -- 将商品表中的数据复制到商品复制表中
  2. INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
  3. SELECT product_id, product_name, product_type, sale_price, 
  4. purchase_price, regist_date
  5. FROM product;

Task01：初识SQL 与 数据库 - 图14

插入默认值时也可以不使用 DEFAULT 关键字，只要在列清单和 VALUES 中省略设定了默认值的列就可以了。

  1. -- 通过显式方法插入默认值
  2. -- 在 VALUES 子句中指定 DEFAULT 关键字
  3. INSERT INTO ProductCopy (product_id, product_name, product_type,
  4. sale_price, purchase_price, regist_date)
  5. VALUES ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28') ;
  7. -- 通过隐式方法插入默认值
  8. INSERT INTO ProductCopy (product_id, product_name, product_type,
  9. /* 省略了sale_price这列 */ purchase_price, regist_date) 
  10. VALUES ('0007', '擦菜板', '厨房用具',  /* 同样值也省略 */  790, '2009-04-28');

Task01：初识SQL 与 数据库 - 图15

本节product表插入数据sql如下：
这里暂且跳过，下面会用到这些内容，到时再回过来看。

  1. -- DML ：插入数据
  2. START TRANSACTION;
  3. INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
  4. INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
  5. INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
  6. INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
  7. INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
  8. INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
  9. INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
  10. INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
  11. COMMIT;

3.9【扩展内容】

  • 清空表内容
    1. TRUNCATE TABLE TABLE_NAME;

优点：相比drop/delete，truncate用来清除数据时，速度最快。
Task01：初识SQL 与 数据库 - 图16
这里只是表数据太少，删除时间有点小范围波动（即随机性带来的影响）。数据量大的时候才能明显看出差异。

  • 数据的更新
    基本语法：
    1. UPDATE <表名>
    2. SET <列名> = <表达式> [, <列名2>=<表达式2>...];  
    3. WHERE <条件>;  -- 可选，非常重要。
    4. ORDER BY 子句;  --可选
    5. LIMIT 子句; --可选

使用 update 时要注意添加 where 条件，否则将会将所有的行按照语句修改

  1. -- 修改所有的注册时间
  2. UPDATE product
  3.    SET regist_date = '2009-10-10';  
  4. -- 仅修改部分商品的单价
  5. UPDATE product
  6.    SET sale_price = sale_price * 10
  7.  WHERE product_type = '厨房用具';

这里我们用前面插入数据的方法，在product表插入数据。
Task01：初识SQL 与 数据库 - 图17

Task01：初识SQL 与 数据库 - 图18

Task01：初识SQL 与 数据库 - 图19

使用 UPDATE 也可以将列更新为 NULL（该更新俗称为NULL清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可。

  1. -- 将商品编号为0008的数据（圆珠笔）的登记日期更新为NULL  
  2. UPDATE product
  3.    SET regist_date = NULL
  4.  WHERE product_id = '0008';

Task01：初识SQL 与 数据库 - 图20

和 INSERT 语句一样， UPDATE 语句也可以将 NULL 作为一个值来使用。
但是，**只有未设置 NOT NULL 约束和主键约束的列才可以清空为NULL**。 如果将设置了上述约束的列更新为 NULL，就会出错，这点与INSERT 语句相同。

  • 多列更新

UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。

  1. -- 基础写法，一条UPDATE语句只更新一列
  2. UPDATE product
  3.    SET sale_price = sale_price * 10
  4.  WHERE product_type = '厨房用具';
  5. UPDATE product
  6.    SET purchase_price = purchase_price / 2
  7.  WHERE product_type = '厨房用具';

该写法可以得到正确结果，但是代码较为繁琐。可以采用合并的方法来简化代码。

  1. -- 合并后的写法
  2. UPDATE product
  3.    SET sale_price = sale_price * 10,
  4.        purchase_price = purchase_price / 2
  5.  WHERE product_type = '厨房用具';

需要明确的是，SET 子句中的列不仅可以是两列，还可以是三列或者更多。
Task01：初识SQL 与 数据库 - 图21

4.练习题

4.1 编写一条 CREATE TABLE 语句，用来创建一个包含表 1-A 中所列各项的表 Addressbook （地址簿），并为 regist_no （注册编号）列设置主键约束

表1-A 表 Addressbook （地址簿）中的列
Task01：初识SQL 与 数据库 - 图22

回答：

  1. CREATE TABLE Addressbook
  2. (regist_no INTEGER NOT NULL,
  3.  name VARCHAR(128) NOT NULL,
  4.  address VARCHAR(256) NOT NULL,
  5.  tel_no CHAR(10) ,
  6.  mail_address CHAR(20),
  7.  PRIMARY KEY (regist_no));

Task01：初识SQL 与 数据库 - 图23

4.2 假设在创建练习1.1中的 Addressbook 表时忘记添加如下一列 postal_code （邮政编码）了，请把此列添加到 Addressbook 表中。

  • 列名 ： postal_code
  • 数据类型 ：定长字符串类型（长度为 8）
  • 约束 ：不能为 NULL

回答：
Task01：初识SQL 与 数据库 - 图24

4.3 编写 SQL 语句来删除 Addressbook 表。
回答：
Task01：初识SQL 与 数据库 - 图25

4.4 编写 SQL 语句来恢复删除掉的 Addressbook 表。
查了一下删除没有备份的表，要恢复挺麻烦的。