1500字范文 > 数据库SQL语言从入门到精通--Part 4--SQL语言中的模式基本表视图

数据库SQL语言从入门到精通--Part 4--SQL语言中的模式基本表视图

时间：2022-01-12 18:50:26

数据库从入门到精通合集（超详细，学习数据库必看）

前言：

使用SQL语言时，要注意SQL语言对大小写并不敏感，一般使用大写。所有符号一定是西文标点符号（虽然是常识，但我还是提一嘴）

1、模式的定义与删除

1.1 定义模式

在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句，语句如下：

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]

注：如果没有指定<模式名>，那么<模式名>隐含为<用户名> 。

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

其中这个TAB1的表有5列，第一列为短整型，第二列为整型，第三列为定长字符串，第四列为定点数，第五列跟第四列一样。稍微了解一下，后面将会详细的介绍，表的定义。

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

1.2 删除模式

语句格式：

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

CASCADE（级联）连带将模式中的数据库对象都删除

删除模式的同时把该模式中所有的数据库对象全部删除，即所有依赖此模式的对象都会被删除。RESTRICT（限制）有下属数据库对象时，不删除

如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。仅当该模式中没有任何下属的对象时才能执行。

例子：

--删除模式ZHANG，则删除失败，因为模式中定义了TAB1DROP SCHEMA ZHANG RESTRICT;

--删除模式ZHANG，同时该模式中定义的表TAB1也被删除DROP SCHEMA ZHANG CASCADE;

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

数据类型

其他的

2.1 定义表

语句格式：

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

注：如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

例子：

--建立一个学生选课表SCCREATE TABLE SC( Sno CHAR(9), Cno CHAR(4) UNIQUE, --只涉及一个属性，可以定义为列级完整性约束条件Grade SMALLINT，PRIMARY KEY (Sno,Cno), --主码由/*约束条件涉及到该表的两个属性列，必须作为表级完整性进行定义*/FOREIGN KEY (Sno) REFERENCES Student(Sno), /* 表级完整性约束条件，Sno是外码，被参照表是Student */FOREIGN KEY (Cno)REFERENCES Course(Cno) /* 表级完整性约束条件， Cno是外码，被参照表是Course*/);

常见的五种完整性约束：

1.NOT NULL（非空)约束：只用于定义列约束。

CREATTABLE Employee(Emp_id int not null,Emp_name varchar(10) not null,EMP_address varchar(40) ,)

创建之后，如果往表Employee表中非空约束中插入空值将会出错。

INSERT INTO Employee VALUES(1,NULL,'neimeng') /*报错Cannot insert the value NULL into column 'emp_name', table 'Student.dbo.Employee';column does not allow nulls. INSERT fails.*/

2.UNIQUE(惟一)约束：用于指明创建惟一约束的列上的取值必须惟一。

CREATTABLE Employee(Emp_id int UNIQUE,Emp_name varchar(10) NOT NULL,EMP_address varchar(40) ,)

如果向Employee插入数据时，如果两条记录的Emp_id不惟一，则会出现错误。

INSERT INTO Employee VALUES(123,'xiaoming','neimeng') INSERT INTO Employee VALUES(123,'xiaoWang','hebei')/*Violation of UNIQUE KEY constraint 'UQ__Employee__0051DEAE8'.Cannot insert duplicate key in object 'dbo.Employee'.*/

除了在定义列时添加UNIQUE约束外，也可以将unique约束作为表约束添加。即把它作为表定义的元素。

语法如下：

[CONSTRAINT constraint_name] UNIQUE (COL1,COL2,.....)

CREATTABLE Employee(Emp_id int UNIQUE,Emp_name varchar(10) NOT NULL,EMP_address varchar(40) ,constraint p_uniq unique(Emp_id ))

3.Primary KEY(主键)约束：

用于定义基本表的主键，起惟一标识作用，其值不能为null,也不能重复，以此来保证实体的完整性。

CREATTABLE Employee(Emp_id int Primary KEY,Emp_name varchar(10) NOT NULL,EMP_address varchar(40) ,)

如果向Employee表插入的Emp_id 重复了或者插入时Emp_id 为NULL值，则会出错。

可以在创建表时，创建主键约束，也可创建表完成以后，创建主键。

例如：

alter table EmployeeInfoadd constraint e_prim primary key(emp_id)

PRIMARY KEY与 UNQIUE 的区别：

1.在一个表中，只能定义一个PRIMARY KEY约束，但可定义多个UNQIUE 约束。

2.对于指定为primary key的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于unique所约束的惟一键，则允许为null,只是null值最多有一个。

** 4.FOREGIN KEY(外键)约束：**

定义了一个表中数据与另一个表中的数据的联系。

FOREGIN KEY约束指定某一个列或一组列作为外部键，其中包含外部键的表称为子表，包含外部键所引用的主键的表称为父表。系统保证，表在外部键上的取值要么是父表中某一主键，要么取空值，以此保证两个表之间的连接，确保了实体的参照完整性。

通俗的说，外键是对另一个表中主键的引用。如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键（FK）。即，当一张二维表（如表A）的主关键字被包含在另一张二维表（如表B）中时，A表中的主关键字便成为B表的外关键字。

2.2 定义表的所属模式

方法一：在表名中明显地给出模式名

Create table"S-T".Student(......);/*模式名为 S-T*/Create table "S-T".Cource(......);Create table "S-T".SC(......);

方法二：在创建模式语句中同时创建表

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

方法三：设置所属的模式

创建基本表（其他数据库对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名若搜索路径中的模式名都不存在，系统将给出错误

显示当前的搜索路径： SHOW search_path; 搜索路径的当前默认值是：$user， PUBLIC

设置搜索路径，然后定义基本表：

SET search_path TO "S-T",PUBLIC; //设置搜索路径Create table Student(......); //定义基本表

结果建立了S-T.Student基本表

2.3 修改表

语句格式：

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

例子：

向 Course表增加“开始时间”列，将其数据类型为日期型

ALTER TABLE Course ADD C_start DATE;

将Ccredit 分数的数据类型由短整数改为整数

ALTER TABLE Course ALTER COLUMN Ccredit INT ;

增加课程名称必须取唯一值得约束条件

ALTER TABLE Course ADD UNIQUE(Cname);

2.4 删除表

语句格式：

DROP TABLE <表名>［RESTRICT| CASCADE］;

RESTRICT：删除表是有限制的

欲删除的基本表不能被其他表的约束所引用；

如果存在依赖该表的对象，则此表不能被删除CASCADE：删除该表没有限制

在删除基本表的同时，相关的依赖对象（表定义、数据、索引、视图、触发器等）一起删除

3.索引的建立与删除

建立索引的目的：加快查询速度

谁可以建立索引

DBA 或表的属主（即建立表的人）

DBMS一般会自动建立以下列上的索引

PRIMARY

KEY UNIQUE

索引的维护：

DBMS自动完成

索引的使用

DBMS自动选择是否使用索引以及使用哪些索引

RDBMS中索引一般采用B+树、HASH索引来实现

B+树索引具有动态平衡的优点

HASH索引具有查找速度快的特点

采用B+树，还是HASH索引则由具体的RDBMS来决定

索引是关系数据库的内部实现技术，属于内模式的范畴

CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引

3.1 创建索引

语句格式：

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);

<表名>：要建索引的基本表的名字

索引：可以建立在该表的一列或多列上，各列名之间用逗号分隔

<次序>：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC

UNIQUE：此索引的每一个索引值只对应唯一的数据记录

CLUSTER：表示要建立的索引是聚簇索引

例子：

为学生-课程数据库中的Student，Course，SC三个表建立索引

Student表按学号升序建唯一索引

Course表按课程号升序建唯一索引

SC表按学号升序和课程号降序建唯一索引

CREATE UNIQUE INDEX Stusno ON Student(Sno); //默认升序CREATE UNIQUE INDEX Coucno ON Course(Cno);CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

聚簇索引：

将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：

将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

注：如果索引建的不对速度会大受影响

3.2 修改索引

语句格式：

ALTER INDEX <旧索引名> RENAME TO <新索引名>

例子：

将SC表的SCno索引名改为SCSno

ALTER INDEX SCno RENAME TO SCSno;

3.3 删除索引

删除索引时，系统会从数据字典中删去有关该索引的描述

语句格式：

DROP INDEX <索引名>;

例子：

删除Student表的Stusname索引

DROP INDEX Stusname;

4.视图的创建与删除

4.1 创建视图

语句格式

CREATE VIEW <视图名> [(<列名> [,<列名>]…)]AS <子查询>[WITH CHECK OPTION];

子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则决定具体系统的实现WITH CHECK OPTION：对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

注：

修改基表的结构后，可能导致表与视图的映象关系被破坏，从而导致该视图不能正确工作组成视图的属性列名：全部省略或全部指定子查询不允许含有ORDER BY子句和DISTINCT短语全部省略或全部指定

省略:由子查询中SELECT目标列中的诸字段组成

明确指定视图的所有列名: 某个目标列是集函数或列表达式目标列为 *多表连接时选出了几个同名列作为视图的字段需要在视图中为某个列启用新的更合适的名字

例1：建立信息系学生的视图

CREATE VIEW IS_StudentAS SELECT Sno,Sname,Sage FROM Student WHERE Sdept= 'IS';

例2：建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生

CREATE VIEW IS_StudentAS SELECT Sno,Sname,Sage FROM Student WHERE Sdept= 'IS'WITH CHECK OPTION;

透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）

对IS_Student视图的更新操作：

修改操作：自动加上Sdept= 'IS’的条件删除操作：自动加上Sdept= 'IS’的条件插入操作：自动检查Sdept属性值是否为’IS’如果不是，则拒绝该插入操作如果没有提供Sdept属性值，则自动定义Sdept为’IS’

4.1.1 基于多个基表的视图

例3：建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）

CREATE VIEW IS_S1(Sno,Sname,Grade)AS SELECT Student.Sno,Sname,Grade FROM Student,SCWHERE Sdept= 'IS' AND Student.Sno=SC.Sno AND o= '1';

4.1.2 基于视图的视图

例4：建立信息系选修了1号课程且成绩在90分以上的学生的视图

// IS_S2 基于视图IS_s1：FROM IS_S1CREATE VIEW IS_S2AS SELECT Sno,Sname,Grade FROM IS_S1 WHERE Grade>=90;

4.1.3 带表达式的视图

例5：定义一个反映学生出生年份的视图

// 表达式：-SageCREATE VIEW BT_S(Sno,Sname,Sbirth)AS SELECT Sno,Sname,-Sage FROM Student;

4.1.4分组视图

例6：将学生的学号及平均成绩定义为一个视图

// 分组：GROUP BYCREAT VIEW S_G(Sno,Gavg)AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;

4.2 删除视图

语句的格式：

DROP VIEW <视图名>[CASCADE];

该语句从数据字典中删除指定的视图定义如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除

例子：

CREATE VIEW IS_S2AS SELECT Sno,Sname,Grade FROM IS_S1 WHERE Grade>=90;--删除视图BT_SDROP VIEW BT_S；--删除视图IS_S1：DROP VIEW IS_S1；--拒绝执行--级联删除：DROP VIEW IS_S1 CASCADE;