SQL数据定义、更新，视图

傷城~ 2023-06-14 04:13 19阅读 0赞

### 文章目录 ###

*   *  1 数据定义语言
     *   *  1.1 数据库的定义
         *  1.2 基本表的定义
         *   *  1.2.1 创建基本表
             *  1.2.2 基本表的修改
             *  1.2.3 基本表的删除
     *  2 SQL更新语言
     *   *  2.1 插入数据
         *  2.2 删除数据
         *  2.3 修改数据
     *  3 视图
     *   *  3.1 定义视图
         *  3.2 查询视图
         *  3.3 视图更新
         *  3.4 删除视图

## 1 数据定义语言 ##

### 1.1 数据库的定义 ###

数据库作为一个整体存放在外存的物理文件中。物理文件有两种：

*  一是数据文件（data file），存放数据库中的对象数据；
 *  二是日志文件（log file），存放用于恢复数据库的冗余数据。

一个数据库可以有多个物理文件，可以将一个或若干个物理文件设置为一个逻辑设备;一个数据库可以有多个逻辑设备，必须在定义数据库时进行定义。定义数据库对象时只需要指明该对象存放在哪个逻辑设备上，而不必关心更多的物理细节;由逻辑设备与物理文件进行联系，从而实现数据库的逻辑模式与存储模式的独立。

USE master;
    GO
    --在创建 ScoreDB 数据库之前删除名为 ScoreDB 的数据库（如果它存在）。
    IF DB_ID (N'ScoreDB') IS NOT NULL
    DROP DATABASE ScoreDB;
    GO
    --以下示例会创建数据库 ScoreDB。 由于未使用关键字 PRIMARY，因此第一个文件 (ScoreDB_data) 将成为主文件。 
    --因为在 ScoreDB_data 文件的 SIZE 参数中没有指定 MB 或 KB，将使用 MB 并按 MB 分配。 
    --ScoreDB_log 文件以 MB 为单位进行分配，因为 SIZE 参数中显式声明了 MB 后缀。
    CREATE DATABASE ScoreDB
    ON								--定义数据文件
    	( NAME=ScoreDB_data,		--逻辑文件名
    								--物理文件名
    	  FILENAME='G：\ProgramFiles\MSSQL14.SQLEXPRESS01\MSSQL\DATA\ScoreDB_data.mdf' ,
    	  SIZE=10,					--文件初始大小为5MB
    	  MAXSIZE=200,				--文件最大为200MB
    	  FILEGROWTH=5				--增量为5MB
    	)
    LOG ON							--定义日志文件
    	( NAME=ScoreDB_log,			--逻辑文件名
    	  FILENAME='G：\ProgramFiles\MSSQL14.SQLEXPRESS01\MSSQL\DATA\ScoreDB_log.ldf' ,
    								--物理文件名
    	  SIZE=10MB,				--文件初始大小为5MB
    	  MAXSIZE=200MB,			--文件最大为200MB
    	  FILEGROWTH=5MB			--增量为5MB
    	)
    GO

### 1.2 基本表的定义 ###

创建了数据库后，就可以在数据库中建立基本表。通过将基本表与逻辑设备相关联，使得一个基本表可以放在一个数据文件上，也可以放在多个数据文件上。

SQL 中的基本数据类型是：

*  整型：int(4B)、smallint(2B)、tinyint(1B);
 *  实型：float、real(4B)、decimal(p, n)、numeric(p, n);
 *  字符型：char(n)、varchar(n)、text;
 *  二进制型：binary(n)、varbinary(n)、image;
 *  逻辑型：bit，只能取0和1，不允许为空
 *  货币型：money(8B，4位小数)、small money(4B，2位小数);
 *  时间型：datetime(4B，从 1753-01-01开始)、smalldatetime(4B，从 1900-01-01开始）。

其中：image为存储图像的数据类型，text 存放大文本数据。

#### 1.2.1 创建基本表 ####

当创建了一个基本表，与该基本表相关的描述信息会存入到数据库系统表中。创建基本表操作的语法为:

CREATE TABIE <tableName>
    (
        <columnNamel> <dataType> [DEFAULT <defaultvalue>][NULL | NOT NULL] [,
    	<columnName2> <dataType> [DEFAULT <defaultvalue>][NULL | NOT NULL]…]
    	[, CONSTRAINT <constraintName1>] 
    	   {
       UNIQUE | PRIMARY KEY} (<columName1> [, <columName2>…])[,…n]
    	]
    	[, CONSTRAINT <constraintName2>] 
    	   FOREIGN KEY (< columName1> [, < columiName2>…])
    	   	 REFERENCE [<dbName>.owner.]<refTable> (<refColumn1> [, <refColum2>…])[,…n]
    	]
    )[ON <filegroupName>]

其中：

*  <tableName>：基本表的名称，最多可包含128个字符
 *  <columnName>：基本表中的列名，在表内唯一；
 *  <dataType>：指定列的数据类型;
 *  DEFAULT ：为列设置省值，属于可选项；
 *  NULL | NOT NULL：为列设置是否允许为空值，属于可选项；
 *  <constraintName＞：定义约束的名字，属于可选项;
 *  UNIQUE：建立唯一索引;
 *  PRIMARY KEY：建立主码；
 *  FOREIGN KEY：建立外码;
 *  ON <filegroupName>： 将数据库对象放在指定的逻辑设备(组)上，该逻辑设(组)必须是在创建数据库时定义的，或者使用数据库的修改命令已加入到数据库中的逻辑设备(组)，缺省该项时自动将对象建立在主逻辑设备上。

建议：最好不要将用户数据库对象(如基本表、索引等)建立在主逻辑设备上，因为主 逻辑设备存放了数据库的系统管理信息。

DROP TABLE IF EXISTS [dbo].[Student]
    GO
    CREATE TABLE Student
    (
    	studentNo	char(7)						NOT NULL    --学号
    		CHECK (studentNo LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),	
    	studentName varchar(20)					NOT NULL,	--姓名
    	sex			char(2)						NULL,		--性别
    	birthday	datetime					NULL,		--出生日期
    	native		varchar(20)					NULL,		--籍贯
    	nation		varchar(30)	DEFAULT '汉族'	NULL,		--民族
    	classNo		char(6)						NULL,		--所属班级
    	CONSTRAINT StudentPK PRIMARY KEY (studentNo),
    	CONSTRAINT StudentFK FOREIGN KEY (classNo) REFERENCES Class(classNo)
    );
    GO

#### 1.2.2 基本表的修改 ####

可以通过 ALTER TABLE 命令来修改基本表的结构，如扩充列等。  
修改基本表操作的语法为:

*  增加列(新增一列的值为空值)

ALTER TABLE <tableName>
    	ADD <columnName> <dataType>

*  增加约束。

ALTER TABLE <tableName>
    	ADD CONSTRAINT <constraintName>

*  删除约束。

ALTER TABLE <tableName>
    	DROP <constraintName>

*  修改列的数据类型

ALTER TABLE <tableName>
    	ALTER COLUMN <columnName> <newDataType>

其中，<tableName>为要修改的基本表名。

#### 1.2.3 基本表的删除 ####

DROP TABLE <tableName> [RESTRICT | CASCADE]

*  若选择**RESTRICT**，则基本表的删除有限制条件，即该基本表不能有视图，触发器以及被其他基本表所引用(如检查约束CHECK,外码约束 FOREIGN KEY)，该项为缺省值
 *  若选择**CASCADE**，则该基本表的删除没有限制条件，在删除基本表的同时，也删除建立在该基本表上的所有索引、完整性规则、触发器和视图等，删除基本表时，系统会同时从数据库系统表中将该基本表的描述一起删除，

注意:SQL Server不支持 \[RESTRICT | CASCADE\]选项

## 2 SQL更新语言 ##

### 2.1 插入数据 ###

*  插入一个元组

INSERT INTO <tableName> [(<columnName1> [, <columnName2>…])]
    	VALUES(<value1> [, <value2>…])

*  插入多个元组

INSERT INTO <tableName> [(<columnName1> [, <columnName2>…])]
    	<subquery>

例如：

CREATE TABLE StudentNation (
    	studentNo	char(7)					NOT NULL,	--学号
    	courseNo	char(3)					NOT NULL,	--课程号
    	termNo		char(3)					NOT NULL,	--学期号
    	score		numeric(5,1) DEFAULT 0  NOT NULL	--成绩
    		CHECK (score BETWEEN O.O AND 100.0),
    	CONSTRAINT studentNationPK PRIMARY KEY (studentNo, courseNo, termNo)
    )
    
    --执行如下插入语句:
    
    INSERT INTO StudentNation
    	SELECT *
    	FROM Score
    	WHERE studentNo IN(SELECT studentNo FROM Student WHERE nation<>'汉族')
    
    INSERT INTO StudentNation (studentNo, courseNo, termNo)
    	SELECT studentNo, courseNo, termNo
    	FROM Score
    	WHERE studentNo IN (SELECT studentNo EROM Student WHERE nation='汉族')

### 2.2 删除数据 ###

DELETE FROM <tableName> [WHERE <predicate>]

例如：

DELETE FROM SCORE
    WHERE studentNo='1600001'

### 2.3 修改数据 ###

UPDATE <tableName>
    SET <columnName1>=<expr1> [, <columnName2>=<expr2>…]
    [FROM {
       <tableName1> | <queryName1> | <viewname1>} [[AS] <aliasName1>]
    	[,{
       <tableName2> | <queryName2> | <viewname2>} [[AS] <aliasName2>] … ]
    ]
    [WHERE <predicate>]

其中：

*  <tableName>：要进行修改数据的基本表名;
 *  SET <columnName1>=<expr1> \[, <columnName2>=<expr2>…\]：  
    用表边式的值替代指定属性列的值，一次可以修改多个属性列的值，之间以逗号分;
 *  \[WHERE <predicate>\]：指出被修改的元组所满足的条件，该项可以省略，若省略，表示修改基本表中的所有元组，WHERE子句中可以包含子查询，

例如：

UPDATE Class SET classNum=sCount
    FROM Class a, ( SELECT classNo, count(*) sCount
         			FROM Student
         			GROUP BY calssNo) b
    WHERE a.classNo=b.classNo

## 3 视图 ##

**视图是虚表，是从一个或几个基本表(或视图)中导出的表**，在**数据库系统表中仅存放了视图的定义，不存放视图对应的数据**。当**基本表中的数据发生变化时，从视图中查询出的数据也随之改变**。  
视图实现了数据库管理系统三级模式中的外模式，基于视图的操作包括:**查询**、**删除**、**受限更新**和**定义基于该视图的新视图**，视图的主要作用是:

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

### 3.1 定义视图 ###

使用视图前必须先定义视图：

CREATE VIEW <viewName> [(<columnName1> [, <columnName2>…])]
    	AS <subquery>
    	[WITH CHECK OPTION]

*  <viewName>：新建视图的名称，该名称在一个数据库中必须唯一;
 *  <columnName1> \[, <columnName2>…\]：视图定义的列名。如果列名省略  
    不写，则视图的列名自动取＜subquery>语句查询出来的列名。如果存在下列3种情况之一，则必须写视图的列名:

1.  <subquery>查询中的某个目标列是聚合函数或表达式;
2.  <subquery>查询中出现了多表连接中名称相同的列名；
3.  在视图中需要为某列取新的名称更合适。

*  AS <subquery>：子查询，不允许含有ORDER BY子句和DISTINCT短语；
 *  \[WITH CHECK OPTION\]：如果使用该选项，则表示在对视图进行更新（**插入、删除、修改**）操作时必须进行合法性检查，只有当更新操作的结果满足视图定义中谓词条件（<subquery>子查询中的条件表达式）时，该更新操作才被允许

例如：

CREATE VIEW StudentView1999
    AS
    	SELECT *
    	FROM Student
    	WHERE year(birthday)=1999

CREATE VIEW ScoreView (courseNo, courseName, courseCount, courseAvg)
    AS
    	SELECT a.courseNo, courseName, count(*), avg (score)
    	FROM Course a, Score b
    	WHERE a.courseNo=b.courseNo
    	GROUP BY a.courseNo, courseName
    
    --或
    
    CREATE VIEW ScoreView1
    AS
    	SELECT a.courseNo, courseName, count(*) courseCount, avg (Score) courseAvg
    	FROM Course a, Score b
    	WHERE a.courseNo=b.courseNo
    	GROUP BY a.courseNo, courseName

### 3.2 查询视图 ###

查询是对视图进行的最主要的操作。从用户的角度来看，查询视图与查询基本表的方式是完全一样的，从系统的角度来看，查询视图的过程是:

1.  进行有效性检查，检查查询中涉及的基本表和视图是否存在
2.  从数据库系统表中取出视图的定义，将视图定义的子查询与用户的查询结合起
3.  执行改写后的查询。

SELECT * 
    FROM StudentView1999
    WHERE classNo='CS1601'
    
    --该视图转化为如下查询：
    
    SELECT * 
    FROM Student
    WHERE year(birthday)=1999 AND classNo='CS1601'

### 3.3 视图更新 ###

视图更新指通过视图来插入、删除和修改基本表中的数据。由于视图是一个虚表，不实际存放数据，对视图的更新，最终要转换为对基本表的更新，因此，如果视图的定义中包含了表达式，或聚合运算，或消除重复值运算，则不能对视图进行更新操作。  
对视图进行更新操作，其限制条件比较多，建立视图的作用不是利用视图来更新数据库中的数据，而是简化用户的查询，以及达到一定程度的安全性保护，因此尽量不要对视图执行更新操作。

### 3.4 删除视图 ###

DROP VIEW <viewName> [CASCADE]