【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（一）模式、表、索引与视图

📋 前言

⏰诗赋清音：墨激雷霆势，心随碧波飘。山河承豪情滔天，梦御风云志浩荡。

🎉欢迎大家关注🔍点赞👍收藏⭐️留言📝

🔔作者留言：

欢迎来到我的【SQL Server】魔法学堂！这里是探索数据库世界的秘境，我的学习笔记博客为你打开SQL Server的魔法之门。在这里，我不仅分享SQL Server的基础知识和高级技巧，还有着涉猎实用技术和项目经验的魔法药水。无论你是新手还是数据库巫师，这个魔法堂会为你施展出奇幻的学习魔法，帮助你在SQL Server的魔法森林中踏上一场奇妙之旅。准备好了吗？跟着我，让我们一起编织属于自己的SQL Server魔法代码！

🌌基础知识

sqlserver语句区分大小写和单双引号吗？

在 SQL Server 中，标识符（例如表名、列名、数据库名等）默认是不区分大小写的，但是字符串常量是区分大小写的【仅支持英文版双引号（""）】。

例如，以下查询是有效的：

SELECT column1 FROM TableName WHERE column2 = 'Value';

在这里，column1、TableName 和 Value 不区分大小写，但字符串 'Value' 区分大小写。

注释

在 SQL Server 中，有两种常见的注释格式：单行注释和多行注释。

1. 单行注释： 使用 -- 号可以在 SQL Server 中添加单行注释。注释从 -- 开始，一直到行尾结束。

-- 这是单行注释
SELECT column1 FROM TableName;

1. 多行注释： 支持用 /* */ 形式的多行注释，但可用多个单行注释

/*这是
  多行
  注释*/
SELECT column1 FROM TableName;

查看当前用户名

select current_user;

🌌第三章 关系数据库标准语言SQL

🌍1. SQL概述

1. SQL: 结构化查询语言，是关系数据库的标准语言

SQL是在1974年由Boyce和Chamberlin提出，由IBM公司在System R上实现

目前没一个DBS支持SQL标准的所有概念+特征

2. 特点：

1. 综合统一
2. 高度非过程化
3. 面向集合的操作
4. 以同一种语法结构提供多种使用方式
5. 语言简洁，易学易用

3. SQL基本概念

1. 基本表：本身独立存在的表
   SQL中一个关系就对应一个基本表
   一个（或多个）基本表对应一个存储文件
   一个表可以带若干索引
2. 存储文件：逻辑结构组成了关系数据库的内模式，物理结构对用户是隐蔽的
3. 视图：从一个或几个基本表导出的表
   数据库中只存放视图的定义而不存放视图对应的数据，视图是虚表
   用户可以在视图上再定义视图

注意：

• 一个DBS的实例可包含多个数据库
• 一个数据库可包含多个模式
• 一个模式可包含多个表、索引、视图...

🌍2. 学生-课程 数据库

学生-课程模式 S-T :    

1. 学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
2. 课程表：Course(Cno,Cname,Cpno,Ccredit)
3. 学生选课表：SC(Sno,Cno,Grade)

示例：

🌍3. 数据定义

其中，SQL不支持修改模式和视图，只能删除后重建。

🪐3.1 模式的定义与删除

使用SSMS工具进行操作，可参考文章：SSMS快速上手

创建定义：

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

[<表定义子句>|<视图定义子句>|<授权定义子句>]

删除定义：

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

1. CASCADE（级联）：删除模式的同时把该模式中所有的数据库对象全部删除
2. RESTRICT（限制）：如果该模式定义了下属的数据库对象（如表、视图等），则拒绝删除
   

数据库可有多个实例，一个实例可有多个模式，一个模式可有多个表、视图、索引等等。

示例：

创建示例

【1】为用户WANG定义一个学生-课程模式S-T

--除了英文的双引号，其他引号都是错误：'S-T' is error!
--建立索引
create schema "S-T" authorization dbo;
 
--删除索引
drop schema "S-T"; 

【2】   CREATE SCHEMA AUTHORIZATION WANG;

/*  该语句没有指定<模式名>，
则<模式名>隐含为<用户名>，即为WANG*/
CREATE SCHEMA AUTHORIZATION WANG;
 
-- 删除模式及其所有对象
DROP SCHEMA WANG CASCADE;

【3】为用户dbo创建了一个模式TEST，并且在其中定义一个表TAB1

--为用户dbo创建了一个模式TEST，并且在其中定义一个表TAB1
create schema "S-T" authorization dbo
create table tab1(col1 char(3),
          col2 char(4),
          col3 int,
          col4 time,
          col5 varchar(10),
          col6 smallint
          )
 
--删除表和模式
drop table  dbo.tab1;
drop schema "S-T";

删除示例：

【1】DROP SCHEMA ZHANG CASCADE;

--删除表和模式
drop table  TEST.TAB1;
drop schema "TEST";

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

🌕1. 定义+修改+删除基本表

1. 创建：

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

说明：

• <表名>：所要定义的基本表的名字,如Student
• <列名>：组成该表的各个属性（列）,如Sname
• <列级完整性约束条件>：涉及相应属性列的完整性约束条件，如primary key\not null...
• <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件 ,如foreign key (s) references tabx(ss);
• 如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则都行。 如有多个primary key(key1,key2...);

2. 修改

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

说明：

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

3. 删除

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

说明：

• CASCADE：删除该表没有限制。
  在删除基本表的同时，相关的依赖对象一起删除
• RESTRICT：删除表是有限制的。
  欲删除的基本表不能被其他表的约束所引用
  如果存在依赖该表的对象，则此表不能被删除

示例：

创建示例：

【1】建立“学生”表Student。学号是主码，姓名取值唯一。

--创建表Student
create table Student(
          Sno varchar(20) primary key, /* 列级完整性约束条件,Sno是主码*/    
          Sname varchar(20) unique,/* Sname取唯一值*/
          Ssex varchar(2),
          Sage smallint,
          Sdept varchar(20)
          );
--删除表Student
drop table Student;

【2】建立一个“课程”表Course

--创建表Course
create table Course(
          Cno varchar(20) primary key,
          Cname varchar(20),
          Cpno varchar(20),
          Ccredit smallint,
          /*Cpno是外码，被参照表是Course，被参照列是Cno*/
          foreign key (Cpno) references Course(Cno),
          );
 
--删除表 Course
drop table Course;

【3】建立一个学生选课表SC

--创建表SC
create table SC(
        Sno varchar(20),
        Cno varchar(20),
        Grade smallint,
        /* 主码由两个属性构成，必须作为表级完整性进行定义*/
        primary key(Sno,Cno),
                /* 表级完整性约束条件，Sno是外码，被参照表是Student */
        foreign key (Sno) references Student(Sno),
                /* 表级完整性约束条件， Cno是外码，被参照表是Course*/
        foreign key (Cno) references Course(Cno)
        );
--删除表SC
drop TABLE SC;

修改示例：

【1】 向Student表增加“入学时间”列，其数据类型为日期型

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

注：不管基本表中原来是否已有数据，新增加的列一律为空值

【2】 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

--将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。
ALTER TABLE Student 
ALTER COLUMN Sage INT;

【3】 增加课程名称必须取唯一值的约束条件。

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

删除示例：

【1】删除Student表

DROP TABLE  Student  CASCADE;
/*基本表定义被删除，数据被删除
表上建立的索引、视图、触发器等一般也将被删除 */

【2】若表上建有视图，选择RESTRICT时表不能删除;选择CASCADE时可以删除表，视图也自动删除。    

CREATE VIEW IS_Student      
AS 
SELECT Sno,Sname,Sage
FROM  Student
WHERE Sdept='IS';
 
DROP TABLE Student RESTRICT;   
--ERROR: cannot drop table Student because other objects depend on it

【3】如果选择CASCADE时可以删除表，视图也自动被删除

DROP TABLE Student CASCADE;         
 --NOTICE: drop cascades to view IS_Student
SELECT * FROM IS_Student;
--ERROR: relation " IS_Student " does not exist 

🌕2. 数据类型

🌕3. 模式与表

1. 关系：每个基本表都属于某个模式，一个模式包含多个基本表

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)
                                           );
  
• 方法三：设置所属的模式

🪐3.3 索引的建立与删除

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

关系数据库管理系统中常见索引：

• 顺序文件上的索引
• B+树索引（参见爱课程网3.2节动画《B+树的增删改》）
• 散列（hash）索引
• 位图索引

特点：

• B+树索引具有动态平衡的优点
• HASH索引具有查找速度快的特点

1. 创建：

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

说明：

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

2. 修改

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

3. 删除

DROP INDEX <索引名>;
删除索引时，系统会从数据字典中删去有关该索引的描述。

示例：

创建示例：

【1】为学生-课程数据库中的Student，Course，SC三个表建立索引。Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引

--为学生-课程数据库中的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);

修改示例：

【2】将SC表的SCno索引名改为SCSno

--将SC表的SCno索引名改为SCSno
ALTER INDEX SCno RENAME TO SCSno;

删除示例：

【3】 删除Student表的Stusname索引

DROP INDEX Stusname;

🪐3.4 数据字典

数据字典是 DBMS内部的一组系统表

🌍4. 视图

视图的特点

1. 虚表，是从一个或几个基本表（或视图）导出的表
2. 只存放视图的定义，不存放视图对应的数据
3. 基表中的数据发生变化，从视图中查询出的数据也随之改变

🪐4.1  定义视图

🌕1. 建立视图

语句格式

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

说明：

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

组成视图的属性列名：全部省略或全部指定

• 全部省略: 由子查询中SELECT目标列中的诸字段组成
• 明确指定视图的所有列名:
  某个目标列是聚集函数或列表达式
  多表连接时选出了几个同名列作为视图的字段
  需要在视图中为某个列启用新的更合适的名字

注意：

1. 关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。
2. 在对视图查询时，按视图的定义从基本表中将数据查出。
3. 定义IS_Student视图时加上了WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS'的条件。
4. 若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图。

【1】建立信息系学生的视图。

       CREATE VIEW IS_Student

       AS

       SELECT Sno,Sname,Sage

       FROM     Student

       WHERE  Sdept= 'IS';

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

        CREATE VIEW IS_Student

       AS

       SELECT Sno,Sname,Sage

       FROM  Student

       WHERE  Sdept= 'IS'

       WITH CHECK OPTION;

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

       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';

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

       CREATE VIEW IS_S2

       AS

       SELECT Sno,Sname,Grade

       FROM  IS_S1

       WHERE  Grade>=90;

带表达式的视图

【1】定义一个反映学生出生年份的视图。

       CREATE  VIEW BT_S(Sno,Sname,Sbirth)

       AS

       SELECT Sno,Sname,2014-Sage

       FROM  Student;

分组视图

【1】将学生的学号及平均成绩定义为一个视图

          CREAT  VIEW S_G(Sno,Gavg)

            AS  

            SELECT Sno,AVG(Grade)

            FROM  SC

            GROUP BY Sno;

【1】将Student表中所有女生记录定义为一个视图

     CREATE VIEW F_Student(F_Sno,name,sex,age,dept)

     AS

     SELECT  *                        /*没有不指定属性列*/

     FROM  Student

     WHERE Ssex=‘女’;

缺点：修改基表Student的结构后，Student表与F_Student视图  的映象关系被破坏，导致该视图不能正确工作。

🌕2.删除视图

语句格式：

       DROP  VIEW  <视图名>[CASCADE];

说明：

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

【1】删除视图BT_S和IS_S1

       DROP VIEW BT_S;    /*成功执行*/

       DROP VIEW IS_S1;    /*拒绝执行*/      

【1】要删除IS_S1，需使用级联删除：

          DROP VIEW IS_S1 CASCADE;            

🪐4.2  查询视图

用户角度：查询视图与查询基本表相同

关系数据库管理系统实现视图查询的方法：视图消解法

• 进行有效性检查
• 转换成等价的对基本表的查询
• 执行修正后的查询
  

【1】在信息系学生的视图中找出年龄小于20岁的学生。

      SELECT   Sno,Sage

      FROM      IS_Student

      WHERE   Sage<20;

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

SELECT  Sno,Sage      

FROM  Student

WHERE  Sdept= 'IS'  AND  Sage<20;

【1】查询选修了1号课程的信息系学生

SELECT  IS_Student.Sno,Sname

FROM     IS_Student,SC

WHERE  IS_Student.Sno =SC.Sno AND SC.Cno= '1';

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

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;

错误：

SELECT Sno，AVG(Grade)

FROM     SC

WHERE  AVG(Grade)>=90

GROUP BY Sno;

正确：

SELECT  Sno,AVG(Grade)

FROM  SC

GROUP BY Sno

HAVING AVG(Grade)>=90;

【1】也可以用如下SQL语句完成

   SELECT *

   FROM  (SELECT Sno,AVG(Grade)

              FROM  SC

               GROUP BY Sno) AS S_G(Sno,Gavg)

   WHERE Gavg>=90;

🪐4.3  更新视图

【1】将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。

UPDATE  IS_Student

SET  Sname= '刘辰'

WHERE  Sno= ' 201215122 ';

转换后的语句：

UPDATE  Student

SET Sname= '刘辰'

WHERE Sno= ' 201215122 ' AND Sdept= 'IS';

【1】向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁

INSERT

INTO IS_Student

VALUES(‘201215129’,’赵新’,20);

转换为对基本表的更新：

INSERT

INTO   Student(Sno,Sname,Sage,Sdept)

VALUES(‘200215129 ','赵新',20,'IS' );

【1】删除信息系学生视图IS_Student中学号为”201215129”的记录

DELETE

FROM IS_Student

WHERE Sno= ' 201215129 ';

转换为对基本表的更新：

DELETE

FROM Student

WHERE Sno= ' 201215129 ' AND Sdept= 'IS';

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

【1】定义的视图S_G为不可更新视图。

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

          CREAT  VIEW S_G(Sno,Gavg)

            AS  

            SELECT Sno,AVG(Grade)

            FROM  SC

            GROUP BY Sno;

UPDATE  S_G

SET          Gavg=90

WHERE  Sno= '201215121';

这个对视图的更新无法转换成对基本表SC的更新

DB2对视图更新的限制：

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

【1】将SC中成绩在平均成绩之上的元组定义成一个视图

     CREATE VIEW GOOD_SC

     AS

     SELECT  Sno,Cno,Grade

     FROM     SC

     WHERE Grade >

                             (SELECT AVG(Grade)

                               FROM     SC);

🪐4.4  视图的作用

1. 视图能够简化用户的操作
2. 视图使用户能以多种角度看待同一数据
3. 视图对重构数据库提供了一定程度的逻辑独立性
4. 视图能够对机密数据提供安全保护
5. 适当的利用视图可以更清晰的表达查询

📝总结

数据库SQL Server领域就像一片未被勘探的信息大海，引领你勇敢踏入数据科学的神秘领域。这是一场独特的学习冒险，从基本概念到算法实现，逐步揭示更深层次的模式分析、匹配算法和智能模式识别的奥秘。渴望挑战数据库SQL Server中的模式匹配学习路径和掌握信息领域的技术？不妨点击下方链接，一同探讨更多数据科学的奇迹吧。我们推出了引领趋势的💻 数据科学专栏：《数据之谜 | 数据奇迹解码》，旨在深度探索数据库SQL Server中模式匹配技术的实际应用和创新。🌐🔍