数据库 笔记

一、数据库的一些概念

数据库的概念：

数据库指的是以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。

数据库管理系统：

数据库管理系统（Database Management System，简称DBMS）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。例如:MySQL就是一个数据库管理系统。

关系数据库：

所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

关系数据库管理系统的特点：

• 数据以表格的形式出现;
• 每行为各种记录名称;
• 每列为记录名称所对应的数据域;
• 许多的行和列组成一张表单;
• 若干的表单组成 database;

关系模型的结构的一些概念：

表的结构

• 表

  关系模型的数据结构单一，采用二维表结构来表示实体和实体之间的关系，是一种按行与列排列的具有相关信息的逻辑组。一个关系对应一个二维表。一个数据库可以包含任意多个数据表。

• 属性

  表中每一列（也称字段、数据项）描述实体集的某个特征，在关系数据库中被称为属性；每一个属性都有自己的取值范围，称为域。

• 元组

  表中的每一行由一个实体的相关属性取值构成，称为元组，它相对完整地描述了一个实体。一个元组在关系数据库中也被称为一条记录。元组的一个属性值称为分量。

关键字

在一个关系模式中，关键字是保证表中记录具有唯一性的一种机制。

• 候选关键字

  关系表中按应用语义能唯一标志元组的最小属性的集合。在最简单的情况下，候选关键字只包含一个属性。在最极端的情况下，关系模式的所有属性组是这个关系模式的候选关键字，称为全码。

• 主关键字（主键）

  用户指定的用来在关系表中唯一标识元组的一个候选关键字。若一个关系有多个候选关键字，则只选定其中的一个为主关键字。主关键字的值不能为空、不能重复。

• 外关键字 （外键）

  一个表中的主关键字与另一个表中与该主关键字相同的属性建立联系，这个起到联系作用的属性，称为另一个表的外关键字，简称外键。

关系模式

表中的行定义（即表头）是实体集相关属性的集合，称为该表的关系模式。关系模式就是对关系的描述，包括关系名、组成该关系的属性名、属性向域的映像。

关系模式通常记为：(属性名1,属性名2,…属性名n)，其中主关键字加下划线。

二、关系数据库的基本操作

关系数据库的基本操作包括：查、增、删、改。这些操作，我们可以通过一种语言去执行，这种语言就是 SQL （Structured Query Language），即结构化查询语言。

SQL

SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。SQL 语句用于取回和更新数据库中的数据。

数据操作语言 (DML)

SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。查询和更新指令构成了 SQL 的 DML 部分：

• SELECT - 从数据库表中获取数据
• UPDATE - 更新数据库表中的数据
• DELETE - 从数据库表中删除数据
• INSERT INTO - 向数据库表中插入数据

数据定义语言 (DDL)

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。SQL 中最重要的 DDL 语句:

• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

SQL指令

SELECT

SELECT 最常用的方式是将数据从数据库中的表中选出，即从 (FROM) 数据库中的表中选出 (SELECT)。(表格是一个数据库内的结构，它的目的是储存数据。)一个最基本的 SQL 架构如下所示：

SELECT “列名” FROM “表名称”

例如：

SELECT Name FROM city;

上述语句，会返回表 city 中的 Name 这一列的全部值。

DISTINCT

SELECT 指令让我们能够读取表中一个或数个列的所有数据，得到的数据的值可能会有重复。当我们需要找出表中的某列数据有哪些不同的值时，只要在 SELECT 后加上一个 DISTINCT 就可以了。也就是说 SELECT 会将符合条件的数据全都选出来，而 DISTINCT 会选择出这个表的列中那些 不同的值。即使每个值的出现多次，使用 DISTINCT 后会返回唯一不同的值。在 SQL 中，使用 DISTINCT 的语法如下：

SELECT DISTINCT “列名” FROM “表名称”

例如：

SELECT DISTINCT Language FROM countrylanguage;

上述语句，会返回表 countrylanguage 中的属性 Language 这一列在表中的不同的值。

WHERE

当我们需要选择性地从表中选取数据时，就需要用到 WHERE 这个指令。使用 WHERE 指令的语法如下：

SELECT “列名称” FROM “表名称” WHERE “条件”（列 运算符 值）

例如：

SELECT CountryCode FROM countrylanguage WHERE Language = "English";

上述语句，会返回表 countrylanguage 中 Language 的值为 Enlish 的 CountryCode 的列。

AND OR

上面的指令都是基于一个条件对数据进行选择，当我们想通过复杂条件选择数据时，可以将多个简单条件连接成复杂条件。拿 WHERE 来说，AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。使用 AND　OR 指令的语法如下：

SELECT “列名” FROM “表名称” WHERE “简单条件” {[AND|OR] “简单条件”}+

例如：

SELECT CountryCode FROM countrylanguage WHERE Language = English AND IsOfficial = "T";

上述语句，会返回表 countrylanguage 中 Language 的值为 Enlish 且 IsOfficial 为 T 的列。

IN

IN 这个指令允许我们在 WHERE 子句中规定多个值。IN 指令的语法为下：

SELECT “栏位名”　FROM “表名称”　WHERE “列名” IN (‘值一’, ‘值二’, …)

例如：

SELECT CountryCode FROM countrylanguage WHERE Language IN ("Enlish","French");

上述命令，会返回表　countrylanguage 中 Language 的值满足条件在　Enlish　和　French　这两个值中的列。

BETWEEN

BETWEEN 操作符在 WHERE 子句中使用，经常和　AND 连用。使用 BETWEEN … AND 选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。。BETWEEN　这个子句的语法如下：

SELECT “列名”　FROM “表名称” WHERE “列名” BETWEEN ‘值一’ AND ‘值二’

例如：

SELECT CountryCode FROM countrylanguage WHERE Percentage BETWEEN "10.0" AND "35.0";

上述语句，会返回表　countrylanguage 中 Percentage 值在 10.0 到 35.0 之间的列。

LIKE

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式(pattern)。对比之下，在使用 IN 指令的时候，我们完全地知道我们需要的
条件；在使用 BETWEEN 指令的时候，我们则是列出一个范围。使用 LIKE 指令的语法如下：

SELECT “列名” FROM “表名称” WHERE “列名” LIKE {模式}

例如：

SELECT CountryCode FROM countrylanguage WHERE CountryCode LIKE "%A%";

上述语句，会返回表　countrylanguage 中 CountryCode 以A开头的列。LIKE　后的模式，通常是一个通配符。

ORDER BY

ORDER BY 语句用于根据指定的列对结果集进行排序,默认按照升序（ASC）对记录进行排序。如果希望按照降序对记录进行排序，可以使用 DESC 关键字。ORDER BY 的语法如下:

SELECT “列名” FROM “表名称” [WHERE “条件”] ORDER BY “列名” [ASC, DESC]

例如：

SELECT CountryCode,Percentage FROM countrylanguage WHERE Percentage BETWEEN "25.0" AND "55.0" ORDER BY Percentage DESC;

上述语句，会返回表　countrylanguage 中包含 CountryCode 和 Percentage 的列，这些列按照 Percentage 的值降序排列，且满足 Percentage 值介于25.0和55.0之间。

函数

除了对数据的查询，我们有的时候可能会想对这些数据做一些运算，比如将它们总合起来，或是找出它们的平均值，而SQL　有提供一些这一类的函数。

• AVG (平均)
• COUNT (计数)
• MAX (最大值)
• MIN (最小值)
• SUM (总合)

使用函数的语法是：

SELECT “函数名”(“列名”) FROM “表名称”

例一：

SELECT SUM(Population) FROM city WHERE CountryCode = "AFG";

上述语句，会返回表 city 中 CountryCode 等于 AFG 的 Population 总合。

例二：

SELECT COUNT(CountryCode) FROM city WHERE CountryCode = "NLD";

上述语句，会返回表 city 中 CountryCode 等于 NLD 的个数。

例三：

SELECT COUNT(DISTINCT CountryCode) FROM city WHERE CountryCode IS NOT NULL;

上述语句，会返回表 city 中 CountryCode 的个数。COUNT 和 DISTINCT 经常被合起来使用，目的是找出表中某列有多少不同的数值。

例四：

SELECT AVG(Population) FROM city WHERE CountryCode = "DZA";

上述语句，会返回表 city 中 CountryCode 等于 DZA 的Population 的平均值。

GROUP BY

GROUP BY 语句用于结合合计函数 (比如 SUM)，根据一个或多个列对结果集进行分组。使用 GROUP BY 的语法为：

SELECT “列名1”, SUM(“列名2”) FROM “表名称” GROUP BY “列名1”

例如：

SELECT CountryCode,SUM(Population) FROM city GROUP BY CountryCode;

上述语句，会返回表 city 中 每个 CountryCode 对应的总人数，结果按照 CountryCode 进行分组。

HAVING

当我们想对函数产生的值来设定条件时，比如我们想知道那个 CountryCode 的总人数超过10000000，在这个情况下，我们不能使用 WHERE 的指令。这时，我们可以使用 HAVING 的指令，来达到这个目标。 需要注意的是 HAVING 子句通常是在一个 SQL 句子的最后；而且，一个含有 HAVING 子句的 SQL 并不一定要包含 GROUP BY 子句。使用 HAVING 的语法如下：

SELECT “列名1”, SUM(“列名2”) FROM “表名称” GROUP BY “列名1” HAVING (函数条件)

例如：

SELECT CountryCode,SUM(Population) FROM city GROUP BY CountryCode HAVING SUM(Population) > "10000000";

上述语句，会返回表 city 中 Population 总数大于10000000的结果，结果按照 CountryCode 进行分组。

ALIAS

在 SQL 语言中，可以通过 ALIAS 为列名称和表名称指定别名。最常用到的别名有两种：列别名及表别名。SQL　中使用列名和表别名的语法如下：

SELECT “表别名”.”列名1” “列别名” FROM “表名称” “表别名”

例如：

SELECT c1.District Area FROM city c1 GROUP BY c1.District;

上述语句，会返回表 city 中的 District 这一列。不同的是，返回的结果中 District 这一列有了别名 Area。

表连接

之前，我们都是在一张表中读取数据，操作相对比较简单。但实际情况下，我们经常需要从多个数据表中读取数据。在　SQL　中，JOIN 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

在 MySQL 中 JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

要能进行多表查询，那么被查询的表之间必须要有联系，而数据库中的表可通过键将彼此联系起来。

主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。

例如：

SELECT
    c1. NAME CountryName,
    c2. LANGUAGE Country_Language
FROM
    city c1
INNER JOIN countrylanguage c2 ON c1.CountryCode = c2.CountryCode
GROUP BY
    c1. NAME;

注： INNER JOIN 与 JOIN 是相同的。

表格处理

前面我们基本都是在数据库管理系统中存在数据库的情况下，获取数据库中的数据。当然，我们也可以自己创建数据库，根据自己的需要建表存储数据。

CREATE DATABASE

在 SQL 中，使用 CREATE DATABASE 来创建数据库。使用的语法 CREATE DATABASE 语法如下：

CREATE DATABASE database_name

例如：

CREATE DATABASE store;

上述语句，创建了一个名叫 store 的数据库。

CREATE TABLE

创建好数据库，我们就需要建立表格来存储数据。使用 CREATE TABLE 的语法是：

CREATE TABLE 表名称 ( 列名称1 数据类型, 列名称2 数据类型, 列名称3 数据类型, …. )

例如：

CREATE TABLE Customer
( Id int,
  First_Name char(50),
  Last_Name char(50),
  Address char(50),
  City char(50),
  Country char(25),
  Birth_Date date );

上述语句，创建了一个名叫 Customer 的表,并定义了表中存储数据。

在创建表时，我们需要对表中要存储的数据进行限制，常见的限制有以下几种：

• NOT NULL
• UNIQUE
• CHECK
• Primary Key
• Foreign Key

NOT NULL

在没有做出任何限制的情况下，某一列的值是允许有 NULL 值的。如果我们不允许某一列的值含有 NULL 值，我们就需要对那个列做出 NOT NULL 的指定。

例如：

CREATE TABLE Customer
( SID integer NOT NULL,
  Last_Name varchar (30) NOT NULL,
  First_Name varchar(30));

上述语句，”SID” 和 “Last_Name” 这两列不允许有 NULL 值，而 “First_Name” 这一列是可以有 NULL 值的。

UNIQUE

UNIQUE 限制是保证一个栏位中的所有资料都是有不一样的值。

例如：

CREATE TABLE Customer
( SID integer Unique,
  Last_Name varchar (30),
  First_Name varchar(30));

上述语句，”SID” 这列不能有重复值存在，而 “Last_Name” 及 “First_Name” 这两列则是允许有重复值存在。

注意：
一个被指定为主键的列也一定会含有 UNIQUE 的特性。相对来说，一个UNIQUE 的列并不一定会是一个主键。

CHECK

CHECK 约束用于限制列中的值的范围。如果对单个列定义 CHECK 约束，那么该列只允许特定的值。如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

例如：

CREATE TABLE Customer
( SID integer CHECK (SID > 0),
  Last_Name varchar (30),
  First_Name varchar(30));

上述语句，”SID” 这列的值只能包含大于 0 的整数。

注意：
CHECK 限制目前尚未被执行于 MySQL 数据库上。

Primary Key

主键（Primary Key）在前面已经多次提到了，它是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。主键可以是表中的一个列，或是一个人造列 (与表没有关系的列)。主键可以包含一或多个列。当主键包含多列时，称为组合键 (Composite Key)。

主键可以在创建新表时设定 (使用 CREATE TABLE 语句)，或是以改变现有表架构的方式设定 (使用 ALTER TABLE)。例如，MySQL 中指定 Primary Key：

CREATE TABLE Customer
( SID integer,
  Last_Name varchar(30),
  First_Name varchar(30),
  PRIMARY KEY (SID));

或者是改变现有表架构的方式设定：

ALTER TABLE Customer ADD PRIMARY KEY (SID);

Foreign Key

外键（Foreign Key）是一个(或数个)指向另外一个表格主键的列。外键的目的是确定资料的参考完整性(referential integrity)。也就是说，只有被准许的资料值才会被存入数据库内。

例如，MySQL 中设置 Foreign Key：

CREATE TABLE ORDERS
( Order_ID integer,
  Order_Date date,
  Customer_SID integer,
  Amount double,
  Primary Key (Order_ID),
  Foreign Key (Customer_SID) references CUSTOMER(SID));

同样的，在建好表之后，我们依然可以通过改变现有表架构的方式设定外键：

ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);

CREATE VIEW

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。SQL中，建立一个视图的语法如下：

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

例如：

CREATE VIEW New_City AS SELECT Name,District,Population FROM city;

这样，我们就有了一个名为 New_City 的视图。当然，我们可以从视图中获取数据：

SELECT * FROM New_City;

CREATE INDEX

INDEX （索引）是由一个表中的一列或者若干列的值与其对应的记录在数据表中地址所组成。用户无法看到索引，它们只能被用来加速搜索/查询。

数据库中一个表的存储由两部分组成：数据页面和索引页面。

创建索引的目的是为了改善查询性能、加快依据索引字段对表中数据行的检索、强制保持表的数据唯一性等。

索引虽然能改善查询性能，但也耗费了磁盘空间，并且当对数据表进行数据增加、修改或删除时，系统需要花费一些时间来维护索引，所以通常不在一个表上建立太多索引，也不建立不常使用的索引。

一般来说，需要在下面这些地方建立索引：

• 在主键和外键上创建索引
• 在检索频繁的字段建立索引
• 在经常需要排序的字段上建立索引

在 SQL　中，建立索引的语法如下：

CREATE INDEX “INDEX_NAME” ON “TABLE_NAME” (COLUMN_NAME)

例如：

CREATE INDEX IDX_POPULATION ON city (Population);

注意
索引的命名并没有一个固定的方式。通常会用的方式是在名称前加一个字首，例如”IDX_” ，来避免与数据库中的其他物件混淆。另外，在索引名之内包括表名及列名也是一个好的方式。

ALTER TABLE

在表被建立在数据库中后，我们常常会发现，这个表的结构需要有所改变。比如，增加一列、删去一列、改变列名称、改变列的数据类型。在 SQL 中， ALTER TABLE 语句用于在已有的表中添加、修改或删除列。使用 ALTER TABLE 的语法如下：

ALTER TABLE “table_name” [ 改变方式 ]

[ 改变方式 ] 的详细写法会依我们想要达到的目标而有所不同。比如，增加一列：

ALTER TABLE city ADD Country char(10);

删除一列：

ALTER TABLE city Drop Country;

改变列的类型：

ALTER TABLE city Modify Country char(5);

DROP

通过使用 DROP 语句，可以轻松地删除索引、表和数据库。

例如，删除一个表：

DROP TABLE city;

或者是删除一个索引：

DROP INDEX IDX_POPULATION;

TRUNCATE TABLE

使用 DROP　TABLE　指令会删除表和表中的数据。而使用　TRUNCATE TABLE 的指令，表中的数据会完全消失，可是表本身会继续存在。 TRUNCATE TABLE 的语法为下：

TRUNCATE TABLE “表格名”

例如：

TRUNCATE TABLE city;

INSERT INTO

到目前为止，我们都是操作如何把数据从表中取出。那么，怎么往表中添加数据呢？在 SQL 中，我们可以通过 INSERT INTO 向表中添加数据。使用 INSERT INTO 的语法如下：

INSERT INTO “表格名” (“列1”, “列2”, …) VALUES (“值1”, “值2”, …)

或者,将其它表中的数据作为值：

INSERT INTO “表格1” (“栏位1”, “栏位2”, …) SELECT “栏位3”, “栏位4”, … FROM “表格2”;

例如：

INSERT INTO city (Name,CountryCode,District,Population) VALUES ("NanJing","CHN","JiangSu",82302000);

UPDATE

我们有时候可能会需要修改表格中的数据。在这个时候，我们就需要用到 UPDATE 指令。这个指令的语法是：

UPDATE “表名” SET “列名1” = [新值] WHERE {条件}

例如：

UPDATE city SET Population = "1880000" WHERE Name = "Kabul" AND CountryCode = "AFG";

DELETE FROM

在某些情况下，我们会需要直接从数据库中去除一些数据。在 SQL 可以通过 DELETE FROM 指令来完成。它的语法是：

DELETE FROM “表格名” WHERE {条件}

例如：

DELETE FROM city WHERE Name = "MaiJi";

进阶SQL

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。UNION 的语法如下：

[SQL 语句 1] UNION [SQL 语句 2]

例如：

SELECT CountryCode FROM city UNION SELECT CountryCode FROM countrylanguage;

UNION ALL

UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。 UNION ALL 和 UNION 不同之处在于 UNION ALL 会将每一笔符合条件的数据都列出来，无论资料值有无重复。UNION ALL 的语法如下：

[SQL 语句 1] UNION ALL [SQL 语句 2]

例如：

SELECT CountryCode FROM city UNION ALL SELECT CountryCode FROM countrylanguage;

INTERSECT

和 UNION 指令类似，INTERSECT 也是对两个 SQL 语句所产生的结果做处理的。不同的地方是，UNION 是对结果做并集，而 INTERSECT 是交集。INTERSECT 的语法如下：

[SQL 语句 1] INTERSECT [SQL 语句 2]

注： MySQL 中无此用法

MINUS

MINUS 指令是运用在两个 SQL 语句上。它先找出第一个 SQL 语句所产生的结果，然后看这些结果有没有在第二个 SQL 语句的结果中。如果有的话，那这一笔数据就被去除，而不会在最后的结果中出现。如果第二个 SQL 语句所产生的结果并没有存在于第一个 SQL语句所产生的结果内，那这笔数据就被抛弃。MINUS，也就是差集。使用 MINUS 的语法如下：

[SQL 语句 1] MINUS [SQL 语句 2]

注： MySQL 中无此用法

子查询

我们可以在一个 SQL 语句中放入另一个 SQL 语句。当我们在 WHERE 子句或 HAVING 子句中插入另一个 SQL 语句时，我们就有一个子查询 (subquery) 的架构。 子查询的作用是什么呢？第一，它可以被用来连接表。另外，有的时候子查询是唯一能够连接两个表格的方式。子查询的语法如下：

SELECT “栏位1” FROM “表格” WHERE “栏位2” [ 比较运算素 ]
(SELECT “栏位1” FROM “表格” WHERE [ 条件 ])

[ 比较运算素 ] 可以是相等的运算素，例如 =, >, <, >=, <=. 这也可以是一个对文字的运算素，例如 “LIKE”。绿色的部分代表外查询，红色的部分代表内查询。

例如：

SELECT SUM(Population) FROM city WHERE CountryCode IN (SELECT CountryCode FROM countrylanguage WHERE Language = "French");

EXISTS

上面的子查询中，我们用 IN 来连接内查询和外查询,而 EXISTS 也是这样一种方式。在 SQL 中 EXISTS 是用来测试内查询有没有产生任何结果。如果有的话，系统就会执行外查询中的 SQL。若是没有的话，那整个 SQL 语句就不会产生任何结果。EXISTS 的语法是：

SELECT “栏位1” FROM “表格1” WHERE EXISTS (SELECT *
FROM “表格2” WHERE [条件])

例如：

SELECT SUM(Population) FROM city WHERE EXISTS(SELECT * FROM countrylanguage WHERE Language = "French");

CASE

CASE 是 SQL 用来做为 if-then-else 之类逻辑的关键字。 CASE 的语法如下：

SELECT CASE (“栏位名”) WHEN “条件1” THEN “结果1” WHEN “条件2” THEN “结果2” … [ELSE “结果N”] END FROM “表格名”

注意： “条件” 可以是一个数值或是公式。 ELSE 子句则并不是必须的。

例如：

SELECT NAME,CASE 
WHEN 'NanJing' THEN
    Population * 0.9
WHEN 'TianShui' THEN
    Population * 1.1
ELSE
    Population
END "New Population"
FROM city;

说明： 练习 SQL 语句的数据库结构及数据 在这里。数据库管理系统使用的是 MySQL。