MYSQL学习路线（1）

了解：

关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有模式、表、视图和索引等。因此SQL的数据定义功能包括模式定义、表定义、视图和索引的定义。

SQL的数据定义语句

模式对象	操作方式
	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

一个关系数据库管理系统的实例中可以建立多个数据库，一个数据库中可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库对象。

1、模式的定义与删除：

定义模式：

CREATE SCHEMA<模式名>AUTHORIZATION<用户名>

如果没有指定<模式名>,那么<模式名>隐含为<用户名>。定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，例如基本表、视图、索引等。要创建模式，调用该命令的用户必须拥有数据库管理员权限，或者获得了数据库管理员授予的CREATE SCHEMA的权限。

案例：为用户ZHANG创建一个模式TEST，并且在其中定义一个表TAB1

CERATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1(COL1 SMALLINT,
                  COL2 INT,
                  COL3 CHAR(20),
                  COL4 NUMERIC(10,3),
                  COL5 DECIMAL(5,2)
                  );

删除模式：

DROP SCHEMA<模式名><CASCADE|RESTRICT>

                   其中CASCADE和RESTRICT两者必选其一。CASCADE（级联）表示在删除模式的同时把该模式中所有的数据库对象全部删除；RESTRICT（限制）表示如果该模式中已定义了下属的数据库对象（如表、视图等）则拒绝该删除语句的执行，只有当该模式中没有任何下属的对象时才能执行DROP SCHEMA语句。

2、基本表的定义、删除与修改

定义基本表：

CREATE TABLE<表名>(
       <列名><数据类型>[列级完整性约束条件]
       [,<列名><数据类型>[列级完整性约束条件]]
       ......
       [,<表级完整性约束条件>]);

创建了一个模式就建立了一个数据库的命名空间，一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表。

案例：建立一个学生表Student，建立一个课程表Course，建立学生选课表SC

/*Student表*/
CREATE TABLE Student
    (Sno CHAR(9) PRIMARY KEY, /*列级完整性约束条件，Sno是主码*/
     Sname CHAR(20) UNIQUE,  /*Sname取唯一值*/
     Ssex CHAR(2),
     Sage SMALLINT,
     Sdept CHAR(20)
    );

/*Course表*/
CREATE TABLE Course
    （Cno CHAR(4) PRIMARY KEY, /*列级完整性约束条件，Cno是主码*/
      Cname CHAR(40) NOT NULL, /*列级完整性约束条件，Cname不能取空值*/
      Cpno CHAR(4),            /*Cpno的含义是先修课*/
      Ccredit SMALLINT，
      FOREIGN KEY (Cpno) REFERENCES Course(Cno)
            /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
    );

/*建立学生选课表SC*/
CREATE TABLE SC
    (Sno CHAR(9),
     Cno CHAR(4),
     Grade SMALLINT,
     PEIMARY KEY(Sno,Cno),/*主码有两个属性构成，必须作为表级完整性进行定义*/
     FOREING KEY(Sno) REFERENCES Student(Sno),
        /*表级完整性约束条件，Sno是外码，被参照表是Student*/
     FOREING KEY(Cno) REFERENCES Course(Cno)
        /*表级完整性约束条件，Cno是外码，被参照表是Course*/
    );

数据类型

数据类型	含义
CHAR(n),CHARACTER(n)	长度为n的定长字符串
VARCHAR(n),CAHRACTERVARYING(n)	最大长度为n的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT,INTEGER	长整数（4字节）
SMALLINT	短整数（2字节）
BIGINT	大整数（8字节）
NUMERIC(p,d)	定点数，由p位数字（不包括符号、小数点）组成，小数点后面有d位数字
DECIMAL(p,d),DEC(p,d)	同NUMERIC
REAL	取决于机器精度的单精度浮点数
DOUBLE PRECISION	取决于机器精度的双精度浮点数
FLOAT(n)	可选精度的浮点数，精度至少为n位数字
BOOLEAN	逻辑布尔量
DATE	日期，包含年、月、日，格式为YYYY-MM-DD
TIME	时间，包含一日的时、分、秒，格式为HH:MM:SS
TIMESTAMP	时间戮类型
INTERVAL	时间间隔类型

修改基本表：

ALTER TABLE<表名> 
    [ADD [COLUMN] <新列名><数据类型>[完整性约束]]
    [ADD <表级完整性约束>]
    [DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
    [DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]]
    [ALTER COLUMN <列名><数据类型>];

ADD：用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。

DROP COLUMN：删除表中的列。指定RESTRICT短语，如果该列被其他对象引用，RDBMS将拒绝删除该列。

ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。

案列：

/*向Student表增加“入学时间”列，其数据类型为日期型*/
ALTER TABLE Student ADD S_entrance DATE;

/*将年龄的数据类型由字符型改为整数*/
ALTER TABLE Student ALTER COLUMN Sage INT;

/*增加课程名称必须取唯一值的约束条件*/
ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表：

DROP TABLE<表名> [RESTRICT|CASCADE];

若选择RESTRICT,则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用（如CHECK，FOREIGN KEY等约束）,不能有视图，不能有触发器（tigger)，不能有存储过程或函数。如果存在这些依赖该表的对象，则此表不能被删除。

案列：

CREATE VIEW IS_Student  /*Student表上建立视图*/
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

DROP TABLE Student RESTRICT;  /*删除Student表*/
--ERROR:cannot drop table Student because other objects depend on it
    /*系统返回错误信息，存在依赖该表的对象，此表不能被删除*/

DROP TABLE Student CASCADE;  /*删除Student表*/
--NOTICE:drop cascades to view IS_Student
    /*系统返回提示，此表上的视图也被删除*/

SELECT * FROM IS_Student;
--ERROR:relation "IS_Student" does not exist

3、索引的建立与删除

当表的数据量比较大时，查询操作会比较耗时。建立索引是加快查询速度的有效手段。数据库索引类似于图书后面的索引，能快速定位到需要查询的内容。用户可以根据应用环境的需要在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。常见索引包括顺序文件上的索引、B+树索引、散列索引、位图索引等。

建立索引：

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>（<列名>[<次序>][,<列名>[<次序>]]...）;

<表名>是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，可选ASC（升序）或DESC（降序）,默认ASC。UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表示要建立的索引是聚簇索引。

案列

/*Student表按学号升序建唯一索引*/
CREATE UNIQUE INDEX Stusno ON Student(Sno);

/*SC表按学号升序和课程号降序建唯一索引*/
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

修改索引：

ALTER INDEX <旧索引号> RENAME TO <新索引号>;

/*案列*/
/*将SC表的SCno索引名改为SCSno*/
ALTER INDEX SCno RENAME To SCSno;

删除索引：

DROP INDEX <索引名>;

索引一经建立就由系统使用和维护,不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增、删、改频繁，系统会花费许多时间来维护索引，从而降低了查询效率，这时可以删除一些不必要的索引。

4、数据字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。在进行查询优化和查询处理中，数据字典中的信息是其重要的依据。

-------数据查询《MYSQL学习路线（2）》