数据库和SQL

1.1 数据库是什么

数据库（Database，DB）：将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。

数据库管理系统（Database Management System，DBMS）：用来管理数据库的计算机系统。

DBMS分类

• 层次数据库（Hierarchical Database，HDB）

  把数据通过层次结构（树形结构）的方式表现出来，现在已经很少使用了。

• 关系数据库（Relational Database，RDB）

  关系数据库是现在应用最广泛的数据库。采用由行和列组成的二维表来管理数据；同时，它还使用专门的SQL（Structured Query Language，结构化查询语言）对数据进行操作。

  关系数据库管理系统（Relational Database Management System，RDBMS），比较具有代表性的RDBMS 有如下5 种：

• Oracle Database：甲骨文公司的RDBMSSQL

• Server ：微软公司的RDBMS

• DB2：IBM 公司的RDBMS

• PostgreSQL：开源的RDBMS

• MySQL ：开源的RDBMS

• 面向对象数据库（Object Oriented Database，OODB）

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

• XML数据库（XML Database，XMLDB）

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

• 键值存储系统（Key-Value Store，KVS）

  这是一种单纯用来保存查询所使用的主键（Key）和值（Value）的组合的数据库。

1.2 数据库的结构

RDBMS的常见系统结构

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

• 服务器

• 用来接收其他程序发出的请求，并进行相应处理的程序（软件），或者是安装了此类程序的设备（计算机）。

• RDBMS 也是一种服务器，它能够从保存在硬盘上的数据库中读取或变更数据。

• 客户端

• 指向服务器发出请求的程序（软件），或者是安装了该程序的设备（计算机）。

• RDBMS 客户端将想要获取或变更的信息通过SQL 语句发送给RDBMS 服务器。RDBMS 根据SQL 语句返回所请求的数据，或者对数据库中的数据进行更新。

表的结构

关系数据库通过类似Excel 工作表那样的、由行和列组成的二维表来管理数据。用来管理数据的二维表在关系数据库中简称为表。

• 列：表的列（垂直方向）称为字段，它代表了保存在表中的数据项目。对于列的约束比Excel 更加严格，定义为数字的列只能输入数字，定义为日期的列只能输入日期。

• 行：表的行（水平方向）称为记录，它相当于一条数据。关系数据库必须以行为单位进行数据读写 。

• 单元格：行和列交汇的方格称为单元格（实际上没有专门的称谓），一个单元格中只能输入一个数据 。

1.3 SQL概要

标准SQL

国际标准化组织（ISO）为SQL 制定了相应的标准，以此为基准的SQL 称为标准SQL。

完全基于标准SQL 的RDBMS 很少，通常需要根据不同的RDBMS来编写特定的SQL 语句。

SQL 语句及其种类

SQL 用关键字、表名、列名等组合而成的一条语句（SQL 语句）来描述操作的内容。

关键字是指那些含义或使用方法已事先定义好的英语单词。

SQL 语句可以分为以下三类：

• DDL（Data Definition Language，数据定义语言）

  用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令：

• CREATE：创建数据库和表等对象

• DROP：删除数据库和表等对象

• ALTER：修改数据库和表等对象的结构

• DML（Data Manipulation Language，数据操纵语言）

  用来查询或者变更表中的记录。DML 包含以下几种指令：

• SELECT：查询表中的数据

• INSERT：向表中插入新数据

• UPDATE：更新表中的数据

• DELETE：删除表中的数据

• DCL（Data Control Language，数据控制语言）

  用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令：

• COMMIT：确认对数据库中的数据进行的变更

• ROLLBACK：取消对数据库中的数据进行的变更

• GRANT：赋予用户操作权限

• REVOKE：取消用户的操作权限

SQL基本书写规则

• SQL 语句要以分号（;）结尾

  一条SQL 语句可以描述一个数据库操作。在RDBMS 当中，SQL 语句也是逐条执行的。

• SQL 不区分关键字的大小写

  SQL 语句不区分大小写，表名和列名也是如此。但是插入到表中的数据是区分大小写的。

  建议按以下规则书写SQL语句：

• 关键字大写

• 表名的首字母大写

• 其余（列名等）小写

• 常数的书写方式是固定的

  在SQL 语句中直接书写的字符串、日期或者数字等称为常数。常数的书写方式如下所示。

• 含有字符串的时候，需要使用单引号（'）将字符串括起来。

• 含有日期的时候，需要使用单引号（'）将其括起来。日期的格式有很多种（'26 Jan 2010' 或者'10/01/26' 等）。

• 书写数字的时候，不需要使用任何符号标识，直接书写数字即可。

• 单词需要用半角空格或者换行来分隔

  SQL 语句的单词之间需使用半角空格或换行符来进行分隔。

1.4 表的创建

数据库的创建 CREATE DATABASE

创建表之前，必须先创建用来存储表的数据库。CREATE DATABASE语句的语法如下：

CREATE DATABASE <数据库名称>;

如，创建名为shop的数据库：

CREATE DATABASE shop;

表的创建 CREATE TABLE

CREATE TABLE 语句的语法如下：

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

该语法描述了创建一个包含< 列名1>、< 列名2>、 ……的名称为< 表名> 的表。

每一列的数据类型是必须要指定的，

可以对列设置约束，约束可以在定义列的时候进行设置，也可以在语句的末尾进行设置。但是NOT NULL约束只能以列为单位进行设置。

如，在数据库中创建商品表（Product 表）的CREATE TABLE 语句：

CREATE TABLE Product
(product_id     CHAR(4)      NOT NULL,
 product_name   VARCHAR(100) NOT NULL,
 product_type   VARCHAR(32)  NOT NULL,
 sale_price     INTEGER      ,
 purchase_price INTEGER      ,
 regist_date    DATE         ,
 PRIMARY KEY (product_id));

命名规则

数据库、表和列的名称只能使用半角英文字母、数字、下划线（_）；

名称必须以半角英文字母开头；

名称不能重复，即在同一个数据库中不能创建两个相同名称的表，在同一个表中也不能创建两个名称相同的列。

数据类型的指定

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

数据类型表示数据的种类，包括数字型、字符型和日期型等。数据类型的种类很多，各个RDBMS 之间也存在差异。4种基本的数据类型如下：

• INTEGER型

  用来指定存储整数的列的数据类型（数字型），不能存储小数

• CHAR型

• CHAR 是CHARACTER（字符）的缩写，是用来指定存储字符串的列的数据类型（字符型）。

• 可以像CHAR(10) 或者CHAR(200) 在括号中指定该列可以存储的字符串的长度（最大长度）。字符串以定长字符串 的形式存储在被指定为CHAR 型的列中。所谓定长字符串，就是当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足。

• SQL 不区分英文字母的大小写，但是表中存储的字符串却是区分大小写的。

• VARCHAR型

• VARCHAR 型也是用来指定存储字符串的列的数据类型（字符串类型）。

• 也可以通过括号内的数字来指定字符串的长度（最大长度）。但该类型的列是以 可变长字符串 的形式来保存字符串。与定长字符串不同，可变长字符串即使字符数未达到最大长度，也不会用半角空格补足。

• DATE型

  用来指定存储日期（年月日）的列的数据类型（日期型）。

约束的设置

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

前文创建的Product 表中设置了两种约束。

1. 数据类型的右侧设置了NOT NULL 约束

   NULL 是代表空白（无记录）的关键字，在NULL 之前加上了表示否定的NOT，就是给该列设置了不能输入空白，也就是必须输入数据的约束。

product_id   CHAR(4)      NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32)  NOT NULL,

2. 语句末尾设置主键约束

   键，就是在指定特定数据时使用的列的组合。

   主键（primary key）就是可以特定一行数据的列。如果把product_id 列指定为主键，就可以通过该列取出特定的商品数据。

PRIMARY KEY (product_id)

1.5 表的删除和更新

表的删除 DROP TABLE

删除表时使用的DROP TABLE语句的语法：

DROP TABLE <表名>；

DROP 在英语中是“丢掉”，“舍弃”的意思。

删除的表是无法恢复的。即使是被误删的表，也无法恢复，只能重新创建，然后重新插入数据。

表定义的更新 ALTER TABLE

ALTER 在英语中就是“改变”的意思。ALTER TABLE 语句和DROP TABLE 语句一样，执行之后无法恢复。

• 添加列的语法：

ALTER TABLE <表名> ADD COLUMN <列的定义>；

例如，在Product 表中添加这样一列，product_name_pinyin（商品名称（拼音）），该列可以存储100 位的可变长字符串。

ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);

• 删除列的语法：

ALTER TABLE <表名> DROP COLUMN <列名>；

例如，删除之前添加的product_name_pinyin 列:

ALTER TABLE Product DROP COLUMN product_name_pinyin;

表中插入数据 INSERT

使用插入行的指令语句INSERT，就可以把数据都插入到表中了。开头的START TRANSACTION 语句是开始插入行的指令语句，结尾的COMMIT 语句是确定插入行的指令语句。

向Product 表中插入数据的SQL 语句如下所示。

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

表名的修改 RENAME

通常在RENAME 之后按照< 变更前的名称>、< 变更后的名称> 的顺序来指定表的名称。

例如，将错误命名的Poduc表的名称t变更为Product：

RENAME TABLE Poduct to Product;