SQL数据类型、基本表的定义、索引定义

前言

由于最近做消息中心后台开发，需要进行数据库的操作，在编写SQL（mybatis）上略感吃力，于是复习一下大学时代所学习的数据库的相关基础知识，直接从SQL开始。

I.SQL与模式

II.本次学习所用到的几个表

Student

学号 Sno	姓名 Sname	性别 Ssex	年龄 Sage	所在系 Sdept
20021521	李勇	男	20	CS
20021522	刘晨	女	19	CS
20021523	王敏	女	18	MA
20021524	张力	男	19	IS

Course

课程号 Cno	课程名 Cname	先行课 Cpno	学分 Sage
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	PASCAL语言	6	4

CS

学号 Sno	课程号 Cno	成绩 Grade
20021521	1	92
20021521	2	85
20021521	3	88
20021522	2	90
20021522	3	80

III.SQL的数据定义语句

操作对象	创建	删除	修改
模式	create schema	drop schema
表	create table	drop table	alter table
视图	create view	drop view
索引	create index	drop index

IV.创建表

一般格式

create table <表名> (<列名> <数据类型> [列级完整性约束]
    [ , <列名> <数据类型> [列级完整性约束]]
    ...
    [,<表级完整性约束>] );

例如：

create table Student(
    Sno char(9) primary key,  /*列级完整性约束， Sno为主码*/
    Sname char(20) unique,   /*列级完整性约束， Sname唯一*/
    Ssex char(2),
    Sage smallint,
    Sdept char(20)
);

create table Course(
    Cno char(4) Primary key,  /*列级完整性约束， Cno为主码*/
    Cname char(40),
    Cpno char(4),                  /*Cpno是先行课*/
    Ccredit smallint,
    foreign key (Cpno) references Course(Cno)  /*表级完整性约束，Cpno 是外码，被参考Course,被参照Cno*/

);

create table SC(
    Sno char(9),
    Cno char(4),Grade smallint,
    primary key (Sno, Cno),   /* 主码有两个属性构成，必须作为表级完整性约束进行定义*/
    foreign key (Sno) references Student(Sno),  /*表级完整性约束，Sno 是外码，被参考Student,被参照Sno*/
    foreign key (Cno) references Course(Cno)   /*表级完整性约束，Cpno 是外码，被参考Course,被参照Cno*/
);

insert into Student(Sno, Sname, Sage, Ssex, Sdept) values('200215121', '李勇', 20, '男', 'CS');
insert into Student(Sno, Sname, Sage, Ssex, Sdept) values('200215122', '刘晨', 19, '女', 'CS');
insert into Student(Sno, Sname, Sage, Ssex, Sdept) values('200215123', '王敏', 18, '女', 'MA');
insert into Student(Sno, Sname, Sage, Ssex, Sdept) values('200215125', '张力', 19, '男', 'IS');


insert into Course(Cno, Cname, Ccredit) values(2, '数学', 2);
insert into Course(Cno, Cname, Ccredit) values(6, '数据处理', 2);
insert into Course(Cno, Cname, Cpno, Ccredit) values(7, 'PASCAL语言', 6, 4);
insert into Course(Cno, Cname, Cpno, Ccredit) values(5, '数据结构', 7, 4);
insert into Course(Cno, Cname, Cpno, Ccredit) values(4, '操作系统', 6, 3);
insert into Course(Cno, Cname, Cpno, Ccredit) values(1, '数据库', 5, 4);
insert into Course(Cno, Cname, Cpno, Ccredit) values(3, '信息系统', 1, 4);

insert into SC(Sno, Cno, Grade) values('200215121', '1', 92);
insert into SC(Sno, Cno, Grade) values('200215121', '2', 85);
insert into SC(Sno, Cno, Grade) values('200215121', '3', 88);
insert into SC(Sno, Cno, Grade) values('200215122', '2', 90);
insert into SC(Sno, Cno, Grade) values('200215122', '3', 80);

V.关于数据类型

各个数据库的数据类型不完全相同，选择数据类型的原则有两个，一个是取值范围，二是要做哪些数据运算。比如年龄：取值为100左右的正整数，取值范围很多数据类型都满足，比如char(3)、长整形、短整型；运算比如需要求平均年龄，这个只有长整形和短整型支持，考虑到存储空间的占用，故选用短整型。

数据类型	含义
char(n)	长度为n的定长字符串
varchar(n)	最大长度为n的变长字符串
int	长整数，也可以写成integer
smallint	短整数
numeric(p,d)	定点数，由p位数字（不包含小数点、符号）组成，小数点后面有d为数字
real	取决于机器精度的浮点数
double precision	取决于机器精度的双精度浮点数
float(n)	浮点数，精度至少为n位数字
date	日期，包含年、月、日，格式为 YYYY-MM-DD
time	时间，包含一日的时、分、秒，格式为 HH:MM-SS

更多数据类型请参考具体的数据库厂商说明书。

VI.修改基本表

一般格式

alter table <表名> 
    [add <列名> <数据类型> [完整性约束]]
    [drop <完整性约束 名>]
    [alter column<列名> <数据类型>];

例如：

alter table Student add S_ontrance date;  /*学生表增加入学时间列*/

alter table Student alter column Sage int; /*修改学生表的Sage列的数据类型为int，这个在mysql中执行错误*/

alter table Course add unique(Cname); /*增加课程名唯一的约束*/

VII.删除表

一般格式

drop table <表名> [restrict | cascade] 

restrict：删除表示有限制的，如果该表有外键、视图、触发器、存储过程、函数等约束，则删除失败，缺省为restrict

cascade: 删除没有条件，删除该表时，相关联的对象，视图等都会被删除，使用值需谨慎；不同的数据库厂商对的实现细节还是有差距的，比如mysql如果该表有外键的话即使指定cascade也是无法删除的。

比如：在mysq下

drop table Student restrict;

提示错误信息：
[SQL] drop table Student restrict ;
[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails

drop table Student cascade;

[SQL] drop table Student cascade;
[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails

验证一个表只有视图的情况下：restrict和cascade的差别：首先给SC表创建一个视图，然后删除该表，SC表无其他关联约束：

create view v_sc as select * from SC;
drop table SC restrict;
drop table SC cascade;

可以看到在mysql的实现下restrict，cascade 都可以删除表，但是视图没有删除，在这一点上表现是一致的。

VIII.建立索引

建立索引是加快查询的有效手段。

一般格式

create [unique] [cluster] index <索引名称> on <表名>(<列名>[<次序>] [,<列名>[<次序>]]...);

/**建立索引*/
create unique index StuNoIndex on Student(Sno);

IX.删除索引

一般格式

drop index<索引名称> on <表名>

drop index StuNoIndex on Student;

索引一旦建立，由系统使用并维护，不需用户干预。建立索引是为了减少查询时间，如果数据频繁增删改，系统会花费许多时间来维护索引，从而降低了数据库系统的效率，这个时候可以删除一些不必要的索引。在RDBMS中一般采用B+树、HASH索引来实现，B+树具有动态平衡的优点，Hash索引则具有查找快速的优点。索引是数据库的内部实现技术，属于内模式范畴。至于某一索引在创建时是采用B+树还是Hash索引则由具体的RDBMS来决定。