数据库（三）—— 关系数据库标准语言SQL（Mysql）

一、SQL概述

1、SQL的特点

1.1 综合统一

满足以下操作要求：

① 定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库；

② 对数据库中的数据进行查询和更新；

③ 数据库重构和维护；

④ 数据库安全性、完整性以及事务控制；

⑤ 嵌入式SQL和动态SQL定义。

1.2 高度非过程化

        用户无须了解存储路径，存取路径的选择以及SQL的操作过程由系统自动完成，这不仅减轻了用户负担，而且提高了数据独立性。

1.3 面向集合的操作方式

        SQL采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且插入、删除、更新操作的对象也可以是元组的集合。

1.4 以同一种语法结构提供多种使用方式

        SQL既是独立的语言，又是嵌入式语言。用户可以在终端键盘上直接键入SQL命令对数据库进行操作，也能够嵌入到高级程序语言（JAVA、C、C++）中，供程序员设计程序时使用。

1.5 语言简洁，易学易用

SQL功能	使用动词
数据查询	SELECT
数据定义	CREATE、ALTER、DROP
数据操作	INSERT、UPDATE、DELETE
数据控制	GRANT、REVOKE

2、SQL的基本概念

        支持SQL的关系数据库同样支持关系数据库的三级模式结构。外模式包含若干视图和部分基本表，数据库模式包含若干基本表，内模式包含若干存储文件。

        基本表和视图一样，都是关系。基本表是本身独立存在的表，一个关系就对应一个基本表，一个或多个基本表对应一个存储文件。一个表可以带若干索引，索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。 

        视图是从一个或几个基本表导出的表，它本身不独立存储在数据库中，视图只是一个虚拟表，它只是在概念上与基本相同。

        下面以Mysql为例，以Oracle为例可以参考：Oracle使用（二）—— 一些常用的SQL命令和基础知识（包含权限、序列、视图、索引、完整性约束、事务等） 

二、数据定义

1、数据库模式定义

数据库模式定义包含数据库的创建、选择、修改、删除、查看等操作。

⑴ 创建数据库

在Mysql中，可以使用create database或create schema创建数据库，其语法格式如下：

CREATE {DATABASE|SCHEMA}[IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET [=] charset_name|[DEFAULT]COLLATE[=]collation_name

        此语法中，“[]”标示其内容为可选项，“|”用于用于分隔花括号中的选择项，表示可任选一项，“db_name”用于标识具体的数据库名称，关键字“DEFAULT”用于指定默认值，关键字“CHARACTER SET”用于指定数据库字符集，关键字“COLLATE”用于指定字符 集的校对规则，关键字“IF NOT EXISTS”用于在创建数据库前进行判断，只有该数据库不存在时才执行创建操作，这样可避免出现数据库已经存在而再创建的错误。

示例如下：

mysql> create database if not exists learn_mysql;

⑵ 查看数据库

        在Mysql中，查看数据库的语法格式如下：

SHOW {DATABASES|SCHEMAS} [LIKE 'pattern'|WHERE expr]

         此语法中，可选项“LIKE”用于匹配指定的数据库名称，可选项“WHERE”从句用于指定数据库查询范围的条件，此语句只能查看用户权限范围内所能查看到的所有数据库名称。

示例如下：

mysql> show databases;

⑶ 选择数据库

        在Mysql中，使用USE语句可以实现从一个数据库切换到另一个数据库，只有使用USE命令指定某个数据库为当前数据库之后，才能对该数据库及其存储的数据对象进行各种操作。

示例如下：

use learn_mysql;

 ⑷ 修改数据库

 修改已有数据库learn_mysql的默认字符集和校对规则，其语法如下：

ALTER {DATABASE|SCHEMA} [db_name] alter_specification ...

 示例如下：

alter database learn_mysql default character set utf8 default collate utf8_general_ci;

 ⑸ 删除数据库

其语法如下：

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name

此语法中的关键字“IF EXISTS”用来判断是否存在要删除的数据库。另外，需要注意的是使用此语句进行删除时，会删除当前数据库下的所有表以及其中的数据，使用该语句时要谨慎。

示例如下：

drop database if exists learn_mysql;

2、表定义

        只有成功创建数据库之后，才能在数据库中创建数据表，数据表是关系数据库中最重要、最基本的数据对象，也是数据存储的基本单位。

        数据表被定义为字段的集合，创建数据表的过程，实质上就是定义每个字段的过程，同时也是实施数据完整性约束的过程。其中，确定表中每个字段的数据类型是创建表的重要步骤，而字段的数据类型则是定义该字段所能存储的数据值的类型。

⑴ 创建表

创建表的语法如下：

CREATE [TEMPORARY] TABLE user
(
	字段名1 数据类型 [列级完整性约束条件][默认值],
	字段名2 数据类型 [列级完整性约束条件][默认值],
	......
	[表级完整性约束条件]
)[ENGINE=引擎类型]

创建一个表的实例如下：

create table user
(
	oid int not null auto_increment comment '自增主键',
	username varchar(50) not null comment '用户名',
	pwd varchar(100) not null comment '用户密码',
	sex char(2) not null default '男' comment '用户性别',
	address varchar(200) comment '用户住址',
  phone varchar(20) comment '电话',
  primary key (oid)
)ENGINE=INNODB;

 创建表相关注意事项如下：

① 临时表与持久表

        使用关键字"TEMPORARY"创建的表为临时表，不用该关键字创建的表为持久表。临时表只对创建它的用户可见，当断开与该数据库的连接时，Mysql会自动删除它们。

② 数据类型

        数据库中每个列都应有适当的数据类型，用于限制或允许该列中存储的数据。数据类型可帮助正确的排序数据，并在优化磁盘使用方法起着重要的作用。

        在Mysql中，主要的数据类型包括数值类型、日期和时间类型、字符串类型、空间数据类型等。

③ 关键字AUTO_INCREMENT

        使用关键字"AUTO_INCREMENT"可以为表中数据类型为整型的列设置自增属性，从而能实现当插入NULL或数字0到该列时，该列的值会自动设置为"该列最大值加1"，AUTO_INCREMENT的顺序是从数字1开始的，同时每个表只能有一个AUTO_INCREMENT列，并且它必须被索引，其次该列的值是可以被覆盖的，当为该列指定一个尚未使用的唯一的值时，这个值将会替代系统自动生成的值，并且后续的增量将基于该值开始自增。

④ 指定默认值

        默认值是指向表插入数据时，如果没有明确给出某个列所对应的值，则数据库会为此列指定一个值，它使用关键字"DEFAULT"。如果没有指定默认值，则Mysql会自动分配一个，如果可以取NULL，则默认值为NULL，如果该列被定义为NOT NULL则默认值取决于该列的数据类型（对于非AUTO_INCREMENT列的数字类型，默认值是0，表中第一个TIMESTAMP列，默认值是当前的日期和时间）。

⑤ NULL值

        NULL值是指没有值或缺值，它是Mysql中没有指定DEFAULT列以及NOT NULL列的默认值。

⑥ 主键

        主键主要是通过PRIMARY KEY关键字来指定。主键值必须唯一，而且主键一定要为NOT NULL。

⑵ 更新表

        ALTER TABLE语句用来更改原有的表结构。

① ADD[COLUMN] 子句

        该子句可以用来向表中添加新列，且其可同时增加多个列。示例如下：

alter table user
add column nation char(2) not null default '汉' comment '民族' after phone;

         上例中，关键字"AFTER"在原表phone列后新增了一列nation，也可以通过关键字"FIRST" 将新列作为原表的第1列使用，若不指定这两个关键字，新列会自动添加到表的最后一列。类似的方法，可以使用ADDPRIMARY KEY子句、ADDFOREIGN KEY子句、ADD INDEX子句为原表添加一个主键、外键和索引等。

② CHANGE[COLOUMN]子句

        该子句可同时修改表中指定列的名称和数据类型，且可以同时放入多个该子句，彼此用逗号分隔。示例如下：

alter table user
change column nation nation_info varchar(