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

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

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

前言：

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

1、模式的定义与删除

1.1 定义模式

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

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

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

例子： 为用户 ZHANG 创建了一个模式 TEST，并且在其中定义一个表 TAB1
其中这个 TAB1 的表有 5 列，第一列为短整型，第二列为整型，第三列为定长字符串，第四列为定点数，第五列跟第四列一样。稍微了解一下，后面将会详细的介绍，表的定义。

1
2
3
4
5
6
7
8
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 删除模式

语句格式：

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

  1. CASCADE（级联）连带将模式中的数据库对象都删除
    删除模式的同时把该模式中所有的数据库对象全部删除 ，即 所有依赖此模式的对象都会被删除。
  2. RESTRICT（限制）有下属数据库对象时，不删除
    如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。 仅当该模式中没有任何下属的对象时才能执行。

例子：

1
2
--删除模式ZHANG， 则删除失败，因为模式中定义了TAB1
DROP SCHEMA ZHANG RESTRICT;
1
2
--删除模式ZHANG， 同时该模式中定义的表TAB1也被删除
DROP SCHEMA ZHANG CASCADE;

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

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

其他的

数据类型 含义
TIMES TAMP 时间戳类型
INTERVAL 时间间隔类型
2.1 定义表

语句格式：

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

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

例子：

1
2
3
4
5
6
7
8
9
10
11
--建立一个学生选课表SC
CREATE 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（非空)约束： 只用于定义列约束。

1
2
3
4
5
6
CREAT　TABLE　 Employee
(
   Emp_id int not null,
    Emp_name varchar(10) not null,
    EMP_address varchar(40) ,
)

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

1
2
3
4
5
6
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(惟一)约束：用于指明创建惟一约束的列上的取值必须惟一。

1
2
3
4
5
6
CREAT　TABLE　 Employee
(
    Emp_id int UNIQUE,
    Emp_name varchar(10) NOT NULL,
    EMP_address varchar(40) ,
)

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

1
2
3
4
5
6
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 约束作为表约束添加。即把它作为表定义的元素。
语法如下：

1
[CONSTRAINT constraint_name] UNIQUE (COL1,COL2,.....)
1
2
3
4
5
6
7
CREAT　TABLE　 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,也不能重复，以此来保证实体的完整性。

1
2
3
4
5
6
CREAT　TABLE　 Employee
(
    Emp_id int Primary KEY,
    Emp_name varchar(10) NOT NULL,
    EMP_address varchar(40) ,
)

如果向 Employee 表插入的 Emp_id 重复了或者插入时 Emp_id 为 NULL 值，则会出错。
可以在创建表时，创建主键约束，也可创建表完成以后，创建主键。

例如：

1
2
alter table EmployeeInfo
add 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 定义表的所属模式

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

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

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

1
2
3
4
5
6
7
8
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

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

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

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

2.3 修改表

语句格式：

1
2
3
4
ALTER TABLE <表名>
[ADD <新列名><数据类型>[完整性约束]]
[DROP <完整性约束名>]
[ALTER COLUMN <列名> <数据类型>];
指令 含义
ADD 子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件
DROP COLUMN 子句用于删除表中的列

如果指定了 CASCADE 短语，则自动删除引用了该列的其他对象
如果指定了 RESTRICT 短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列
DROP CONSTRAINT 子句用于删除指定的完整性约束条件
ALTER COLUMN 子句用于修改原有的列定义，包括修改列名和数据类型

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

1
ALTER TABLE Course ADD C_start DATE;

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

1
ALTER TABLE Course ALTER COLUMN Ccredit INT ;

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

1
ALTER TABLE Course ADD UNIQUE(Cname);
2.4 删除表

语句格式：

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

  1. RESTRICT：删除表是有限制的
    欲删除的基本表不能被其他表的约束所引用；
    如果存在依赖该表的对象，则此表不能被删除
  2. CASCADE：删除该表没有限制
    在删除基本表的同时，相关的依赖对象（表定义、数据、索引、视图、触发器等）一起删除

3.索引的建立与删除

  • 建立索引的目的：加快查询速度
  • 谁可以建立索引
    DBA 或 表的属主（即建立表的人）
    DBMS 一般会自动建立以下列上的索引
    PRIMARY
    KEY UNIQUE
  • 索引的维护：
    DBMS 自动完成
  • 索引的使用
    DBMS 自动选择是否使用索引以及使用哪些索引
  • RDBMS 中索引一般采用 B+树、HASH 索引来实现
    B+树索引具有动态平衡的优点
    HASH 索引具有查找速度快的特点
  • 采用 B+树，还是 HASH 索引 则由具体的 RDBMS 来决定
  • 索引是关系数据库的内部实现技术，属于内模式的范畴
  • CREATE INDEX 语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引
3.1 创建索引

语句格式：

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

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

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

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

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

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

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

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

1
2
3
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 修改索引

语句格式：

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

例子：
将 SC 表的 SCno 索引名改为 SCSno

1
ALTER INDEX SCno RENAME TO SCSno;
3.3 删除索引

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

1
DROP INDEX <索引名>;

例子：
删除 Student 表的 Stusname 索引

1
DROP INDEX Stusname;

4.视图的创建与删除

4.1 创建视图

语句格式

1
2
3
CREATE  VIEW  <视图名>  [(<列名>  [,<列名>]…)]
AS  <子查询>
[WITH  CHECK  OPTION];
  1. 子查询可以是任意的 SELECT 语句，是否可以含有 ORDER BY 子句和 DISTINCT 短语，则决定具体系统的实现
  2. WITH CHECK OPTION：对视图进行 UPDATE，INSERT 和 DELETE 操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

注：

  1. 修改基表的结构后，可能导致表与视图的映象关系被破坏，从而导致该视图不能正确工作
  2. 组成视图的属性列名：全部省略或全部指定
  3. 子查询不允许含有 ORDER BY 子句和 DISTINCT 短语
  4. 全部省略或全部指定
    省略:由子查询中 SELECT 目标列中的诸字段组成
    明确指定视图的所有列名: - 某个目标列是集函数或列表达式 - 目标列为 * - 多表连接时选出了几个同名列作为视图的字段 - 需要在视图中为某个列启用新的更合适的名字

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

1
2
CREATE VIEW IS_Student
AS SELECT Sno,Sname,Sage FROM Student WHERE  Sdept= 'IS';

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

1
2
3
CREATE VIEW IS_Student
AS 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 号课程的学生的视图（包括学号、姓名、成绩）

1
2
3
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS SELECT Student.Sno,Sname,Grade FROM  Student,SC
WHERE  Sdept= 'IS' AND Student.Sno=SC.Sno AND SC.Cno= '1';
4.1.2 基于视图的视图

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

1
2
3
// IS_S2 基于视图IS_s1：FROM  IS_S1
CREATE VIEW IS_S2
AS SELECT Sno,Sname,Grade FROM  IS_S1  WHERE  Grade>=90;
4.1.3 带表达式的视图

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

1
2
3
// 表达式：2014-Sage
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS SELECT Sno,Sname,2014-Sage FROM  Student;
4.1.4 分组视图

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

1
2
3
// 分组：GROUP BY
CREAT  VIEW S_G(Sno,Gavg)
AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;
4.2 删除视图

语句的格式：

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

例子：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE VIEW IS_S2
AS SELECT Sno,Sname,Grade FROM  IS_S1  WHERE  Grade>=90;

--删除视图BT_S
DROP VIEW BT_S；

--删除视图IS_S1：
DROP VIEW IS_S1；--拒绝执行

--级联删除：
DROP VIEW IS_S1 CASCADE;

4.3 查询视图

用户角度： 查询视图与查询基本表相同
RDBMS 实现视图查询的方法：
视图消解法（View Resolution） - 进行有效性检查 - 转换成等价的对基本表的查询 - 执行修正后的查询

例子：
在信息系学生的视图中找出年龄小于 20 岁的学生

1
2
3
4
CREATE VIEW IS_Student
AS SELECT Sno,Sname,Sage FROM Student WHERE  Sdept= 'IS';

SELECT Sno,Sage FROM IS_Student WHERE Sage<20;

视图消解转换后的查询语句为：

1
2
3
SELECT  Sno，Sage
FROM  Student
WHERE  Sdept= 'IS'  AND  Sage<20；

有些情况下，视图消解法不能生成正确查询

在 S_G 视图中查询平均成绩在 90 分以上的学生学号和平均成绩

1
2
3
4
5
6
7
8
9
SELECT *
FROM   S_G
WHERE  Gavg>=90；
       S_G视图的子查询定义：
         CREATE VIEW S_G (Sno，Gavg)
         AS
SELECT  Sno，AVG(Grade)
FROM  SC
GROUP BY Sno；

错误：

1
2
3
4
SELECT Sno，AVG(Grade)
FROM     SC
WHERE  AVG(Grade)>=90
GROUP BY Sno；

正确：

1
2
3
4
SELECT  Sno，AVG(Grade)
FROM  SC
GROUP BY Sno
HAVING AVG(Grade)>=90；
4.4 更新视图

更新视图和更新基本表相同

注：一些视图是不可更新的——当对视图的更新无法转换成对基本表 SC 的更新时，如修改平均成绩视图中某个学生的平均成绩不可实现
举例：

向信息系学生视图 IS_S 中插入一个新的学生记录：200215129，赵新，20 岁

1
2
3
INSERT
INTO IS_Student
VALUES(‘95029’，‘赵新’，20)；

转换为对基本表的更新：

1
2
3
INSERT
INTO   Student(Sno，Sname，Sage，Sdept)
VALUES(‘200215129 '，'赵新'，20，'IS' )；

删除信息系学生视图 IS_Student 中学号为 200215129 的记录

1
2
3
DELETE
FROM IS_Student
WHERE Sno= ' 200215129 '；

转换为对基本表的更新：

1
2
3
DELETE
FROM Student
WHERE Sno= ' 200215129 ' AND Sdept= 'IS'；
更新视图的限制：

一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

例：视图 S_G 为不可更新视图。

1
2
3
UPDATE  S_G
SET          Gavg=90
WHERE  Sno= ‘200215121’；

这个对视图的更新无法转换成对基本表 SC 的更新
实际系统对视图更新的限制

DB2 对视图更新的限制：
(1) 若视图是由两个以上基本表导出的，则此视图不允许更新。
(2) 若视图的字段来自字段表达式或常数，则不允许对此视图执行 INSERT 和 UPDATE 操作，但允许执行 DELETE 操作。
(3) 若视图的字段来自集函数，则此视图不允许更新。
(4) 若视图定义中含有 GROUP BY 子句，则此视图不允许更新。
(5) 若视图定义中含有 DISTINCT 短语，则此视图不允许更新。
(6) 若视图定义中有嵌套查询，并且内层查询的 FROM 子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。
(7) 一个不允许更新的视图上定义的视图也不允许更新

这里还有数据库相关的优质文章：快戳我，快戳我 😳

数据库
『数据库』数据库SQL语言从入门到精通-Part 4-SQL语言中的模式、基本表、视图
https://chiamzhang.github.io/2024/06/29/『数据库』数据库SQL语言从入门到精通-Part 4-SQL语言中的模式、基本表、视图/
Author
Chiam
Posted on
June 29, 2024
Licensed under