《数据库原理》三章读书笔记

结构化查询语言

结构化查询语言(Structured Query Language)简称SQL，由IBM开发，并被美国标准制定委员会（ANSI）定位国家标准。SQL命令可以分为几大类，今天主要介绍其中两类，数据定义语言DDL(data definition Language)和数据操作语言DML(Data Manipulation Language)。

数据定义语言DDL

DDL用于创建和改变数据库结构

注：本文使用的数据库系统为MySQL和SQLite，相关环境请自行搭建

创建数据库

命令：CREATE DATABASE demo;

在SQLite的命令行中，直接使用sqlite3 demo可以直接创建和使用名字为demo的数据库。打开MySQL可视化工具MySQLWorkbench，创建一个名为demo的数据库。

创建数据表

CREATE TABLE NewTableName(
    ColumnName DataType OptionalConstraint,
    ColumnName DataType OptionalConstraint,
    ColumnName DataType OptionalConstraint,
    optional table constraint
);

NewTableName为表名，ColumnName为数据表中的列名，也就是字段名，DataType为该列的数据类型，OptionalConstraint为该列的数据约束，是可选的，常见的数据约束有：PRIMARY KEY，NULL，NOT NULL，UNIQUE，FOREIGN KEY。optional table constraint为表约束，可以定义表的主键和参照完整性约束等。SQLite数据类型可以查考这里。MySQL的数据类型可以查考这里。现在我们在刚才的demo数据库中创建四个Table，MySQL语句如下：

下载以上SQL

SQLite语句如下：

下载以上SQL

CHAR和VARCHAR

都是表示字符，但是CHAR是固定长度的，括号内可声明最大长度，存储时不能超过最大长度，而无论比最大长度少多少，存储时都存储最大长度，不足用空格代替。而VARCHAR存储的长度是可变的，根据实际的长度存储，不过这就需要额外的空间来存储它的长度，才能在内存或者硬盘中组织不定长度的值。

NUMERIC(5, 2)

表示带两位小数的八位数字，如果插入数据位123.34，则该数据存储为12334，不会存储小数点。

CONSTRAINT XX_PK 和CONSTRAINT XX_FK

都为约束名，一个是主键的约束名称，一个是外键的约束名称，习惯性用表名加PK表示主键，表名加FK表示主键，定义这个约束名是为了方便以后对数据库的操作，比如删除某个表的主键。

CONSTRAINT ASS_PRO_FK FOREIGN KEY (ProjectID) REFERENCES PROJECT(ProjectID) ON UPDATE NO ACTION ON DELETE CASCADE

解释：定义一个约束名为ASS_PRO_FK的外键ProjectID，这个外键关联到PROJECT表中的主键ProjectID，当更新时，外键不做任何处理（实际上该ProjectID在PROJECT中永远不会更新，因为它是自增长的），当删除PROJECT某一行时，同时也删除所有在ASSIGNMENT中ProjectID为同样值的数据。然后再看CONSTRAINT ASS_EMP_FK FOREIGN KEY (EmployeeNumber) REFERENCES EMPLOYEE(EmployeeNumber) ON UPDATE NO ACTION ON DELETE NO ACTION，更新EmployeeNumber时没有操作，原因同样是EmployeeNumber是不会变的，但是当删除一个Employee时，就和上面不一样了，这里不会有任何操作，这是什么原因呢？其实这个是和实际的业务规则有关系的，删除一个项目，我们会把项目的所有指派全部删了，因为没有项目的指派是没有意义的。但是如果一个员工离职了，指派还是有意义的，我们可以指派另一个员工嘛，这就是我们的业务规则。NO ACTION是默认的，我们可以不定义，但是一般情况下我们需要显示的展示出来，这是为了生成更好的文档。

数据操作语言DML

DML用于数据的修改操作，包括：插入、修改和删除

插入数据

下载以上SQL

SQLite插入数据的SQL和上面一致

插入数据的SQL很好理解，值得注意的是，除了数字类型的数据，其他的在赋值时都需要加引号（普通的、无方向的）。

查询数据

从表中读取所有列

SELECT * FROM EMPLOYEE;

结果：

从表中读取指定列

SELECT FirstName, LastName, Email FROM EMPLOYEE;

结果：

从单个表中读取指定行

SELECT * FROM DEPARTMENT WHERE Phone = '360-2858310';

从单个表中读取指定行和列

SELECT DepartmentName FROM DEPARTMENT WHERE Phone = '360-2858310';

WHERE字句

WHERE字句中可以使用的条件：=、>、<、>=、<=、!=、IN、NOT IN、BETWEEN AND、IS NULL、IS NOT NULL、LIKE，这些条件的判断都很容易理解，条件右边的值同样是除了数字以外，都需要加引号。LIKE和通配符符配合使用，通配符有_和%，前者表示一个未指定的字符，后者表示一个或多个为指定的字符。使用BETWEEN AND时，注意是包括两端的值的。这些条件都可以使用AND和OR连接。

对结果进行排序

SELECT * FROM DEPARTMENT ORDER BY BudgetCode;

默认是升序，如果需要降序排列则使用

SELECT * FROM DEPARTMENT ORDER BY BudgetCode DESC;

升序的关键字为ASC。可以使用多个列来排序，用逗号隔开，越靠前优先级越高

SELECT * FROM DEPARTMENT ORDER BY BudgetCode DESC, OfficeNumber;

分页查询

SELECT * FROM table_name LIMIT [no of rows] OFFSET [row num]

检索记录行 6-15：

SELECT * FROM table LIMIT 5,10;

为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为-1，检索记录行 96-Last:

SELECT * FROM table LIMIT 95,-1;

如果只给定一个参数，它表示返回最大的记录行数目：

SELECT * FROM table LIMIT 5;

检索前5个记录行，换句话说，LIMIT n等价于LIMIT 0, n。

内置函数和计算

内置行数有：COUNT、SUM、AVG、MAX和MIN：

SELECT COUNT(DepartmentName) AS DepartmentNum FROM DEPARTMENT;

SELECT ProjectName, (MaxHours / 8) AS Day FROM PROJECT;

SELECT MIN(MaxHours) AS MinCost FROM PROJECT;

这些函数都是对结果进行计算，一般来说不能用于WHERE字句。

对结果进行分组

SELECT Department, COUNT(*) FROM EMPLOYEE GROUP BY Department;

结果按照部门名字分组，别显示出部门名字和该部门的数量，如果要在结果中再继续筛选，可以对组使用HAVING

SELECT Department, COUNT(*) AS Number FROM EMPLOYEE GROUP BY Department HAVING COUNT(8) > 2;

GROUP BY和WHERE同时使用时，一般将WHERE字句放在前面，所以WHERE先被运行。上面使用了AS，我们使用函数计算的数据列展示时是没有列名的，我们可以通过AS给它指定一个，同样的，即便是有列名的列，我们也可以通过AS给它换一个用于展示的列名，如下：

SELECT Department, COUNT(*) FROM EMPLOYEE GROUP BY Department;

SELECT Department AS Name, COUNT(*) AS Number FROM EMPLOYEE GROUP BY Department;

使用子查询处理多个表

有时我们需要用一个表中的数据作为条件在另一个表中查数据，我们就可以使用子查询

SELECT * FROM EMPLOYEE WHERE Department = (SELECT DepartmentName FROM DEPARTMENT WHERE Phone = '360-2858210');

在DEPARTMENT中查出电话号码为360-2858210的部门名字，再在EMPLOYEE中查出该部门名字下的所有员工。子查询可以扩展到3层、4层或更多层。

使用内连接连接查询多个表

刚才的子查询处理虽然用了多个表，但是最终显示的数据只是来自某一个表，如果我们要显示两个表或者更多表中的数据怎么办？这是我们可以使用join（连接）操作

SELECT FirstName, LastName, HoursWorked From EMPLOYEE AS E,ASSIGNMENT AS A WHERE E.EmployeeNumber = A.EmployeeNumber;

这样就从EMPLOYEE和ASSIGNMENT中分别查处了不同的信息。我们也可以使用JOIN…ON实现上面的查询：

SELECT FirstName, LastName, HoursWorked From EMPLOYEE AS E JOIN ASSIGNMENT AS A ON E.EmployeeNumber = A.EmployeeNumber;

外链接

通过上面的查询，我们可以产出所有分配了任务的员工在EMPLOYEE和ASSIGNMENT的信息，但是如果想显示没有被分配项目的员工信息怎么办？这个时候我们就可以使用外链接LEFT JOIN…ON，它会连接左边表的所有数据

SELECT FirstName, LastName, HoursWorked From EMPLOYEE AS E LEFT JOIN ASSIGNMENT AS A ON E.EmployeeNumber = A.EmployeeNumber;

同样的，可以使用RIGHT JOIN…ON，这里就不再演示了。

修改数据

UPDATE…SET可以修改已有数据，可以通过WHERE指定修改莫一行，SET后也可以修改指定列的值为多少。

UPDATE EMPLOYEE SET Email = 'Tom.Cruse@WPC.com' WHERE EmployeeNumber = 3;

删除数据

DELETE FROM可以删除行，可以通过WHERE字句指定某行或多行，如删除员工号为1的员工：

DELETE FROM EMPLOYEE WHERE EmployeeNumber = 1;

我们删除员工号为3的员工，但是并不能成功，为什么呢？因为在ASSIGNMENT中，该员工已经分配了任务，存在外键的参照完整性约束，所以不能被删除，我们必须将3员工所有分配的任务指派给其他人或者全部删除，才能删除员工3，这与我们的实际情况也是相符的

修改表名

RENAME TABLE…TO可以修改表名

RENAME TABLE PROJECT TO OLD_PROJECT;

删除表

DROP TableName可以删除表，和上面一样，表中存在任何包含或者可以包含需要实施参照完整性约束的值，删除不能生效。

DROP TABLE EMPLOYEE;

修改表结构

使用ALETER TABLE修改表的结构

增加列

ALTER TABLE EMPLOYEE ADD Sex INT;

修改列

先为所有数据添加一个默认的性别，然后增加Sex不为空的限制

UPDATE EMPLOYEE SET Sex = 1;

我们发现，直接在MySQL中执行此SQL更新所有行并不能成功，因为MySQL默认在安全模式下，不允许这种全局更新，所以我们先修改MySQL的安全模式，执行：

SET SQL_SAFE_UPDATES = 0;

再执行上面的SQL，则可以成功更新数据。现在我们修改列，增加一个不为空的约束

ALTER TABLE EMPLOYEE CHANGE Sex Sex INT NOT NULL;

可以看出，这个语句可以修改列名、列数据类型和约束。

删除列

ALTER TABLE EMPLOYEE DROP COLUMN Sex;

删除主键或外键

前面提到我们一般会给主键和外键定义一个约束名，现在大家应该知道他的作用了，我们可以使用约束名删除主键和外键约束

ALTER TABLE ASSIGNMENT DROP FOREIGN KEY ASS_EMP_FK;

增加主键或外键

ALTER TABLE ASSIGNMENT ADD CONSTRAINT ASS_EMP_FK FOREIGN KEY (EmployeeNumber)REFERENCES EMPLOYEE(EmployeeNumber) ON UPDATE NO ACTION ON DELETE NO ACTION;

SQLite的ALTER TABLE

在 SQLite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作。所有我们在SQLite中更新表名需要使用：

ALTER TABLE database_name.table_name RENAME TO new_table_name;

在表中增加列：

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

CHECK约束

在MySQL中，不能使用CHECK约束，而SQLite中可以。那么在MySQL如何限制一些输入值呢，有两种方式，第一种可以使SET和ENUM类型的数据类型：

CREATE TABLE TEST(
    sex ENUM('F', 'M') 
    );

INSERT INTO TEST VALUES ('Y'); //执行失败
INSERT INTO TEST VALUES ('F'); //执行成功

ENUM和SET都是比较特殊的字符串数据列类型，它们的取值范围是一个预先定义好的列表。ENUM或SET数据列的取值只能从这个列表中进行选择。ENUM和SET的主要区别是：ENUM只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。例如：ENUM(“N”,”Y”)表示，该数据列的取值要么是”Y”，要么就是”N”。SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。ENUM和SET的值是以字符串形式出现的，但在内部，MySQL以数值的形式存储它们。ENUM的合法取值列表中的字符串被按声明定义的顺序被编号，从1开始。在MySQL中另外一中约束的方法就是触发器。

Distinct关键字

我们可以使用Distinct消除重复的记录，如：

SELECT DISTINCT FirstName FROM EMPLOYEE;

SELECT COUNT(DISTINCT FirstName) FROM EMPLOYEE;

后者可以作为一种统计：员工中有多少个不同的名字。

完全连接和交叉连接

刚才我们提到了内连接和外连接（包括左连接和右连接），除了内外连接还有完全连接和交叉连接，全连接是在内连接的基础上，在不符合条件的数据上填写NULL，可以理解为左连接加上右连接，使用FULL JOIN关键字。交叉连接是将两个表进行组合，行数为两个表的行数乘积，使用CROSS JOIN关键字。我们用图形表示这些关系就一目了然了：