写在前面：这学期的数据库sql server结课了，为了期末的考试，把老师讲的重点汇总一下，大多数都是书上的内容，希望期末考个好成绩。内容有点多，分几次写吧。
第一章 数据库概述
1.1数据库系统概述
一、数据库（DB） 数据库系统（DBS） 数据库管理系统（DBMS）
数据库管理员（DBA）
DBS 包括 DB、DBMS、DBA
1.2数据管理技术的发展
1.人工管理截断
2.文件系统阶段
3.数据库系统阶段
1.3数据库系统结构
1.3.1数据库系统的三级模式结构
1.外模式：又称子模式或用户模式，一个数据库可以有多个外模式。
2.模式：又称概念模式或逻辑模式，一个数据库只有一个模式。
3.内模式：又称存储模式，一个数据库只有一个内模式。
1.3.2数据库的二级映射功能和数据独立性
1.外模式/模式映射
2.模式/内模式映射
1.4数据模型
1.4.1两类数据模型
第一类：概念模型
第二类：逻辑模型，其中包含物理模型
1.4.2概念模型
1.概念模型的基本概念
（1）实体（Entity）:客观存在并可相互区别的事物成为实体。
（2）属性（Attribute）:实体所具有的某一特性成为属性。
（3）联系（Relationship）:实体之间的联系，可分为两个实体集之间的联系、多个实体集之间的联、单个实体集之间的联系。
三种联系：
一对一的联系（1:1）
一对多的联系（1:n）
多对多的联系（m:n）
2.概念模型的表示方法
概念模型较常用的表示方法是实体-联系模型（Entity-Relationship Model,E-R模型）。
在E-R模型中：
（1）实体用矩形框表示，把实体名写在矩形框内。
（2）属性采用椭圆框表示，把属性名写在椭圆框内，并用无向边将其与对应的实体框相连。
（3）联系采用菱形框表示，把联系名写在菱形框中，用无向边将参加联系的实体矩形框分别与菱形框相连，并将属性框与菱形框也用无向边连上。
1.4.3数据模型组成要素
1.数据结构
2.数据操作
3.数据完整性约束
1.4.4常用的数据模型
1.层次模型
2.网状模型
3.关系模型
习题1
1.数据库中存储的是：(数据以及数据之间的联系)
2.信息的数据表现形式是：(文字、图形、声音、视频等都可)
3.数据库具有(数据结构化)、最小的冗余度和较高的程序与数据的独立性等特点。
4.数据库（DB）、数据库系统（DBS）、数据库管理系统（DBMS）的关系是（DBS包括DBMS和DB）
5.下面不属于数据模型要素的是(数据控制)
6.数据管理技术的发展阶段中，数据库系统阶段与文件系统阶段的根本区别是数据库系统（数据可共享）
7.数据库三级模式体系结构的划分，有利于保持数据库的（数据独立性）
8.在数据库三级模式结构中，内模式有（1个）
9.一个数据库系统的外模式（可以有多个）
10.要保证数据库的数据独立性，需要修改的是（三层模式之间的两层映射）
11.数据模型由数据结构、数据操作和（数据完整性约束）组成
12.数据库系统的三级模式包括外模式、模式和（内模式）
13.数据库的特性包括共享性、独立性、完整性和（减少数据冗余）
第二章 关系数据库
2.1关系模型
2.1.1关系数据结构
1.关系
（1）域（Domain）:域是一组具有相同数据类型的值的集合。
（2）笛卡尔积（Cartesian Product）:域之间的元素两两相乘
关系的元组、属性和候选码
关系是笛卡尔积的有限子集，所以关系也是一个二维表。
元组（Tuple）：表的每行对应一个元组。
属性（Attribute）:表的每列对应一个域。由于域可以相同，为了加以区分，必须对每列起一个唯一的名字，成为属性。
候选码（Candidate Key）：若关系中某一属性组的值能唯一地标识一个元组，则称该属性组为候选码，又称为候选键。
主码（Primary Key）：在一个关系中有多个候选码，从中选定一个作为主码。
2.关系模式
定义2.4 关系模式（Relation Schema）可以形式化地表示为R(U,D,DOM,F)
其中，R是关系名，U是组成改关系的属性名集合，D是属性所来自的域，DOM是属性向域的映射集合，F是属性间的属性依赖关系集合。
2.1.2关系操作
1.基本的关系操作
5种基本操作：并、差、笛卡尔积、选择、投影
2.关系操作语言
（1）关系代数语言
（2）关系演算语言
（3）结构化查询语言
2.1.3关系的完整性
1.实体完整性（Entity Integrity）
规则2.1实体完整性规则 若属性（一个或一组属性）A是基本关系R的主属性，则A不能取空值。（不能为空且唯一）
2.参照完整性（Referential Integrity）
参照完整性一般指多个实体之间的关系，一般用外码实现。
【例】学生实体与学院实体可用以下的关系表示，其中的主码用颜色标识。
学生（学号，姓名，性别，出生日期，专业，总学分）
学院（学院号，学院名，院长）
3.用户定义完整性（User-defined Integrity）
习题2
1.关系模型中的一个候选键（可由一个或多个其值能唯一地标识该关系模式中任意元组的属性组成）
2.设关系R中有4个属性3个元组，设关系S中有6个属性4个元组，则R×S属性和元组个数分别是（10和12）
解析：列(属性)相加，行(元组)相乘
3.如果关系中某一属性组的值能唯一地标识一个元组，则称之为（候选码）
4.以下对关系性质的描述中，哪一项是错误的？（关系中允许出现相同的元组）
解析：关系元组，肯定存在主键，主键字段是不允许有重复的
5.关系模型上的关系操作包括（关系代数和关系演算）
6.关系中主码不允许取空值是符合（实体完整性）约束规则
7.5种基本关系运算是（∪、-、×、σ、π）
解析：并（U）、差（-）、笛卡儿积（×）、选择（σ）、投影（π）
8.集合R与S的交可用关系代数的基本运算表示为（R-(R-S)）
9.把关系R与S进行自然连接时舍弃的元组放到结果关系中去的操作是（外连接）
10.关系演算是用（谓词）来表达查询要求的方式。
11.关系模型由关系数据结构、关系操作和（关系完整性）三部分组成
12.关系操作的特点是（集合）操作方式
13.在关系模型的三种完整性约束中，（实体完整性）是关系模型必须满足的完整性约束条件，由DBMS自动支持。
14.一个关系模式可以形式化地表示为（R(U,D,DOM,F)）
15.关系操作语言可分为关系代数语言、关系演算语言和（结构化查询语言）
16.查询操作的5种基本操作是（并）、差、笛卡尔积、选择、投影
第3章 关系数据库规范化理论
范式，就是标准
第4章 数据库设计
4.1数据库设计概述
数据库设计的基本步骤
（1）需求分析阶段
（2）概念结构设计阶段
（3）逻辑结构设计阶段
（4）物理结构设计阶段
（5）数据库实施阶段
（6）数据库运行和维护阶段
4.4逻辑结构设计
4.4.2 E-R模型向关系模型的转换
由E-R模型向关系模型转换有以下两个规则：
规则1：一个实体转换为一个关系模型。
实体的属性就是关系的属性，实体的码就是关系的码
规则2：实体间的联系转换为关系模型有以下不同的情况。
【例4.5】1:1的联系的E-R模型如图4.10所示，将 E-R模型转换为关系模型。
方案一：联系转换为独立的关系模型，则转换后的关系模型为
学校（学校编号，名称，地址）
校长（校长编号，姓名，职称）
任职（学校编号，校长编号）
方案二：联合合并到“学校”关系模型中，则转换后的关系模型为
学校（学校编号，名称，地址，校长编号）
校长（校长编号，姓名，职称）
方案三：联合合并到“校长”关系模型中，则转换后的关系模型为
学校（学校编号，名称，地址）
校长（校长编号，姓名，职称，学校编号）
在1:1的联系中，一般不将联系转换为一个独立的关系模式，这是由于关系模式个数多，对应的表也很多，查询的时会降低查询效率。
【例4.5】1:n的联系的E-R模型如图4.11所示，将E-R模型转换为关系模型。
方案一：联系转换为独立的关系模型，则转换后的关系模型为
班级（班级编号，教室号，人数）
学生（学号，姓名，性别，出生日期，专业，总学分）
属于（学号，班级编号）
方案二：联系合并到n端实体对应的关系模型中，则转换后的关系模型为
班级（班级编号，教室号，人数）
学生（学号，姓名，性别，出生日期，班级编号）
同样，在1:n的联系中，一般也不将联系转换为一个独立的关系模型。
【例4.6】m:n的联系的E-R模型如图4.12所示，将E-R模型转换为关系模型
对于m:n联系，必须转换为独立的关系模式，转换后的关系模式为：
学生(学号, 姓名, 性别, 出生日期，专业，总学分)
课程(课程号,课程名,学分，教师号)
选课(学号,课程号,成绩)
习题4
1.数据库设计中概念结构设计的主要工具是（E-R模型）
2.数据库设计人员和用户之间的沟通信息的桥梁是（实体联系图）
3.概念结构设计阶段得到的结果是（E-R模型表示的概念模型）
4.在关系数据库设计中，设计关系模式是（逻辑结构设计阶段）的任务。
5.生成DBMS系统支持的数据模型是在（逻辑结构设计）阶段完成的
6.在关系数据库设计中，对关系进行规范化处理，使关系达到一定的范式，是（逻辑结构设计阶段）的任务。
7.逻辑结构设计阶段得到的结果是（某个DBMS所支持的数据结构）
8.员工性别的取值，有的用“男”和“女”，有的用“1”和”0“，这种情况属于（属性冲突）
9.将E-R模型转换为关系模型的过程属于（逻辑结构设计阶段）
10.根据需求建立索引是在（物理结构设计）阶段完成的。
11.物理结构设计阶段得到的结果是（包括存储结构和存取方法的物理结构）
12.在关系数据库设计中，设计视图是（逻辑结构设计阶段）的任务。
13.进入数据库实施阶段，下述工作中，（扩充功能）不属于实施阶段的工作。
14.在数据库物理设计中，评价的重点是（时间和空间效率）
15.数据库设计6个阶段为：需求分析阶段，概念结构设计阶段，（逻辑结构设计阶段），物理结构设计阶段，数据库实施阶段，数据库运行和维护阶段。
16.结构化分析方法通过数据流图和（数据字典）描述系统。
17.概念结构设计阶段的目标是形成整体（数据库）的概念结构。
18.描述概念模型的有力工具是（E-R模型）
19.逻辑结构设计是将E-R模型转换为（关系模型）
20.数据库在物理设备上的存储结构和（存取方法）称为数据库的物理结构。
21.对物理结构进行评价的重点是（时间和空间效率）
22.在数据库运行阶段经常性的维护工作有（数据库的备份和恢复），数据库的安全性和完整性控制，监视，分析，调整数据库性能，数据库的重组和重构。
第6章 创建数据库和创建表
6.1 SQL Server数据库概述
- 数据库对象
SQL Server 的数据库对象包括表(table)、视图(view)、索引(index)、存储过程(stored procedure)、触发器(trigger)等。
●表：表是包含数据库中所有数据的数据库对象，由行和列构成，它是最重要的数据库对象。
●视图：视图是由一个表或多个表导出的表，又称为虚拟表。
●索引：加快数据检索速度并可以保证数据唯一性的数据结构。
● 存储过程：为完成特定功能的T-SQL语句集合，编译后存放于服务器端的数据库中。
● 触发器：它是一种是特殊的存储过程，当某个规定的事件发生时，该存储过程自动执行。 - 系统数据库和用户数据库
（1）系统数据库
SQL Server 在安装时创建4个系统数据库：master、model、msdb和tempdb。
（2）用户数据库
用户数据库是由用户创建的数据库。
6.1.2物理数据库
1.页和区
页和区是SQL Server 数据库的两个主要数据存储单位。
页：每个页的大小是8KB，每1MB的数据文件可以容纳128页，页是SQL Server中用于数据存储的最基本单位。
区：每8个连接的页组成一个区，区的大小是64KB，1MB的数据库有16个区，区用于控制表和索引的存储。
2.数据库文件
SQL Server 采用操作系统文件来存放数据库，使用的文件有主数据文件、辅助数据文件、日志文件三类。
（1）主数据文件(Primary)
主数据文件用于存储数据，每个数据库必须有也只能有一个主文件，它的默认扩展名为.mdf。
（2）辅助数据文件(Secondary)
辅助数据文件也用于存储数据，一个数据库中辅助数据文件可以创建多个，也可以没有，辅助数据文件的默认扩展名为.ndf。
（3）日志文件(Transaction Log)
日志文件用于保存恢复数据库所需的事务日志信息。每个数据库至少有一个日志文件，也可以有多个，日志文件的扩展名为.ldf。
6.3.2数据类型
1．整数型：int，smallint，tiny，bigint
整数型包括4种类型，从标识符的含义就可以看出，它们的表示数范围逐渐缩小。
类型 名称 数范围 精度 存储字节
Bigint 大整数 263～2631 19 8
int 整数 231～2311 10 4
Smallint 短整数 215～2151 5 2
Tinyint 微短整数 0～255 3 1
2．精确数值型：decimal，numeric
3．浮点型：real，float
浮点型不能精确表示数据的精度，用于处理取值范围非常大且对精确度要求不太高的数值量，即近似数值数据类型。
4．货币型：money，smallmoney
5．位型：bit
6.字符型：
char (n)的长度为n。
varchar（n）的长度为输入字符串的实际字符个数，而不一定是n。
7.Unicode字符型:
nchar[(n)]:nchar[(n)]为包含n个字符的固定长度 Unicode 字符型数据，n的值在1与4000之间，缺省为1，长度2n字节。若输入的字符串长度不足n，将以空白字符补足。
nvarchar[(n)]:nvarchar[(n)]为最多包含n个字符的可变长度Unicode字符型数据，n的值在1与4000之间，缺省为1。长度是所输入字符个数的两倍。
实际上，nchar、nvarchar与char、varchar的使用非常相似，只是字符集不同（前者使用Unicode字符集，后者使用ASCII字符集）。
8. 文本型
text类型可以表示最大长度为 231-1（2147483647）个字符，其数据的存储长度为实际字符数个字节。
ntext类型可表示最大长度为 230-1（1073741823）个Unicode字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。
9．二进制型
二进制数据类型表示的是位数据流，包括binary（固定长度）和varbinary（可变长度）两种。
10．日期时间类型
日期时间类型数据用于存储日期和时间信息。
datetime：datetime类型可表示的日期范围从1753 年1月1日到9999年12月31日的日期和时间数据
11．时间戳型
标识符是timestamp。若创建表时定义一个列的数据类型为时间戳类型，那么每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。
12．图像数据类型
标识符是image，它用于存储图片、照片等。实际存储的是可变长度二进制数据，介于0与231-1（2147483647）字节之间。
13．其他数据类型
除了上面所介绍的常用数据类型外，SQL Server 2008还提供了其他几种数据类型：cursor、sql_variant、table和uniqueidentifier。
cursor：是游标数据类型，用于创建游标变量或定义存储过程的输出参数。
sql_variant：是一种存储SQL Server支持的各种数据类型（除text、ntext、image、timestamp和sql_variant外）值的数据类型。sql_variant的最大长度可达8016字节。
table：是用于存储结果集的数据类型，结果集可以供后续处理。
uniqueidentifier：是唯一标识符类型。系统将为这种类型的数据产生唯一标识值，它是一个16字节长的二进制数据。
xml：是用来在数据库中保存xml文档和片段的一种类型，但是此种类型的文件大小不能超过2GB。
6.3.3 表结构设计
创建表的核心是定义表结构及设置表和列的属性，创建表以前，首先要确定表名和表的属性，表所包含的列名、列的数据类型、长度、是否为空、是否主键等，这些属性构成表结构。
习题6
1.在SQL Server中创建用户数据库，其大小必须大于或等于（master数据库的大小）
2.在SQL Server中，如果数据库rempdb的空间不足，可能会造成一些操作无法进行，此时需要扩大tempdb的空间。下列关于扩大tempdb空间的方法，错误的是（删除tempdb中的日志内容，以获得更多的数据空间）
3.在SQL Server中创建用户数据库，实际就是定义数据库所包含文件以及文件的属性。下列不属于数据文件属性的是（文件结构）
4.SQL Server数据库是由文件组成的 ，下列关于数据库所包含文件的说法中，正确的是（一个数据库可包含多个次要数据文件和多个日志文件）
5.在SQL Server系统数据库中，存放用户数据库公共信息的是（master）
6.出生日期字段不宜选择（float）
7.性别字段不宜选择（float）
8.（专业）字段可以采用默认值。
9.设在SQL Server中，某关系表需要存储职工的工资信息，工资的范围为2000~6000，采用整型类型存储。下列数据类型中最合适的是（smallint）
10.从用户的观点看，组成数据库的（逻辑成分）称为数据库对象
11.SQL Server的数据库对象包括表、（视图）、索引、存储过程、触发器等。
12.SQL Server的物理数据库框架包括页和区、（数据库文件）、数据库文件组等。
13.SQL Server数据库每个页的大小是8KB，每个区的大小是（64KB）
14.SQL Server使用的数据库文件有主数据文件、辅助数据文件、（日志文件）三类。
15.表结构包括一组固定的列，列由（数据类型）、长度、语序Null值等组成。
16.空值通常表示未知、（不可用）或将在以后添加的数据
17.创建表以前，首先要确定表名和表的属性，表所包含的（列名）、列的数据类型、是否为空、是否主键等，进行表结构设计。
18.整数型包括bigint、int、smallint和（tinyint）四类
19.字符型包括固定长度字符数据类型和（可变长度字符数据类型）两类
20.Unicode字符型用于支持国际上（非英语语种）的字符数据的存储和处理。
第7章 数据定义语言和数据操纵语言
7.1 T-SQL概述
7.1.1 T-SQL的语法约定
T-SQL的语法约定如表7.1所示，在T-SQL中不区分大写和小写。
7.2 数据定义语言
7.2.1 数据定义语言用于数据库
- 创建数据库
语法格式：
CREATE DATABASE database_name
[ [ON [filespec] ]
[LOG ON [filespec] ]
]
<filespec>::=
{(
NAME = logical_file_name ,
FILENAME = ' os_file_name '
[, SIZE = size]
[, MAXSIZE = {max_size | UNLIMITED }]
[, FILEGROWTH = growth_increament [ KB | MB | GB | TB | % ]] )
}
说明：
● database_name：创建的数据库名称，命名须唯一且符合SQL Server 2008的命名规则，最多为128个字符。
● ON子句：指定数据库文件和文件组属性。
● LOG ON子句： 指定日志文件属性。
● filespec：指定数据文件的属性，给出文件的逻辑名、存储路径、大小及增长特性。
● NAME为filespec定义的文件指定逻辑文件名。
● FILENAME为filespec定义的文件指定操作系统文件名，指出定义物理文件时使用的路径和文件名。
● SIZE子句：指定filespec定义的文件的初始大小。
● MAXSIZE子句：指定filespec定义的文件的最大大小。
● FILEGROWTH子句：指定filespec定义的文件的增长增量。
【例7.1】使用最简单的创建数据库语句，创建Store数据库。
CREATE DATABASE Store
【例7.2】指定数据文件和事务日志文件创建Store2数据库。
在SQL Server 查询分析器中输入以下语句：
CREATE DATABASE Store2
ON
(
NAME=' Store2 ',
FILENAME='C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Store2.mdf',
SIZE=5MB,
MAXSIZE=30MB,
FILEGROWTH=1MB
)
LOG ON
(
NAME='test_log',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Store2_log.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=10%
)
【例7.3】 创建Store3数据库，其中主数据文件为20MB，最大大小不限，按1MB增长；1个日志文件，大小为1MB，最大大小为20MB，按10%增长。
在SQL Server 查询分析器中输入以下语句：
CREATE DATABASE Store3
ON
(
NAME='Store3',
FILENAME='C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Store3.mdf',
SIZE=20MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=1MB
)
LOG ON
(
NAME='Store3_log',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Store3_log.ldf',
SIZE=1MB,
MAXSIZE=20MB,
FILEGROWTH=10%
)
【例7.4】 使用文件组创建数据库Store4。要求：主文件组包括文件test3_dat1，文件初始大小为15MB，最大45MB，按4MB增长；另有1个文件组名为test3gp，包括文件test3_dat2，文件初始大小为5MB，最大为20MB，按10%增长；
在SQL Server 查询分析器中输入以下语句：
CREATE DATABASE Store4
ON
PRIMARY
(
NAME = 'Store4_dat1',
FILENAME = 'D:\data\Store4_dat1.mdf',
SIZE =15MB,
MAXSIZE = 45MB,
FILEGROWTH = 4MB
),
FILEGROUP test3gp
(
NAME = 'Store4_dat2',
FILENAME = 'D:\data\Store4_dat2.ndf',
SIZE = 5MB,
MAXSIZE = 20MB,
FILEGROWTH = 10
)
- 修改数据库
语法格式：
ALTER DATABASE database
{
ADD FILE filespec
| ADD LOG FILE filespec
| REMOVE FILE logical_file_name
| MODIFY FILE filespec
| MODIFY NAME = new_dbname
}
说明：
● database：需要更改的数据库名称。
● ADD FILE子句：指定要增加的数据文件。
● ADD LOG FILE子句：指定要增加的
日志文件。
● REMOVE FILE子句：指定要删除的
数据文件。
● MODIFY FILE子句：指定要更改的
文件属性。
● MODIFY NAME子句：重命名数据库。
【例7.5】在Store3数据库中，增加一个数据文件Store3add，大小为10MB，最大为50 MB，按5MB增长。
ALTER DATABASE Store3
ADD FILE
(
NAME = 'Store3add',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Store3add.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB
)
- 使用数据库
使用数据库使用USE语句
语法格式：
USE database_name
其中，database_name是使用的数据库名称。 - 删除数据库
语法格式：
DROP DATABASE database_name
其中，database_name是要删除的数据库名称
7.2.2 数据定义语言用于表
- 创建表
(1) 使用CREATE TABLE语句创建表
语法格式：
CREATE TABLE 表名
(
{ <列定义> | <计算列定义> | <列集>}
[ <表约束> ] [ ,...n ]
)
[ ; ]
列的定义可以是下列2种。
1）列
<列定义> ::=
列名 <数据类型> /指定列名、列的数据类型/
[ NULL | NOT NULL ] /指定是否为空/
[
[ CONSTRAINT 约束名 ]
DEFAULT 常量表达式 /指定默认值/
]
| [ IDENTITY [ (初值, 增量) ] /指定列为标识列/
[ ROWGUIDCOL ] /指定列为全局标识符列/
[ <列约束> … ] /指定列的约束/
<数据类型> ::=
类型名 [ ( 精度 [ , 小数位 ] | max ]
2）计算列
计算列中的值是通过其他列计算出来的，该列实际并不存放值。
<计算列定义> ::=
列名 AS 计算列表达式
[ PERSISTED [ NOT NULL ] ]
如果没有使用PERSISTED关键字，则在该计算机列上不能添加PRIMARY KEY、UNIQUE、DEFAULT等约束条件。
【例7.7】 使用T-SQL语句，在StoreSales数据库中创建Employee表。
USE StoreSales
CREATE TABLE Employee
(
EmplID char(4) NOT NULL PRIMARY KEY,
EmplName char(8) NOT NULL,
Sex char(2) NOT NULL,
Birthday date NOT NULL,
Address char(20) NULL,
Wages money NOT NULL,
DeptID char(4) NOT NULL
)
GO
【例7.8】在Store数据库中创建Consumer表。
USE Store
CREATE TABLE Consumer
(
ConsumerID int,
Name char(8),
Sex char(2),
Address char(40)
)
(2). 由其它表创建新表
使用SELECT INTO 语句创建一个新表，并用SELECT的结果
集填充该表。
语法格式：
SELECT 列名表 INTO 表1 FROM 表2
该语句的功能是由“表2”的“列名表”来创建新表“表1”。
【例7.9】在StoreSales数据库中，由Goods表创建Goods2表。
USE StoreSales
SELECT GoodsID, GoodsName, Classification INTO Goods2
FROM Goods
- 修改表
语法格式:
ALTER TABLE 表名
{
ALTER COLUMN 列名{, …} /*修改列属性*/
| ADD /*添加列*/
{
<列的定义>
} [ , ... ] <表约束>
| DROP /*删除列*/
{
[ CONSTRAINT ] 约束名 /*删除约束*/
| COLUMN 列名
} [ , ... ]
}
【例7.10】在Goods2表中新增加一列Remarks、修改列
Remarks 数据类型长度、删除列Remarks 。
USE StoreSales
ALTER TABLE Goods2 ADD Remarks char(10)
ALTER TABLE Goods2 ALTER COLUMN Remarks char(12)
ALTER TABLE Goods2 DROP COLUMN Remarks
- 删除表
使用DROP TABLE语言删除表。
语法格式：
DROP TABLE table_name
其中，table_name是要删除的表的名称。
【例7.11】删除StoreSales数据库中Goods2表。
USE StoreSales
DROP TABLE Goods2
7.3.1 插入语句
INSERT语句用于向数据表或视图中插入由VALUES指定的各列值的行。
语法格式：
INSERT [ TOP ( 表达式 ) [ PERCENT ] ]
[ INTO ] 表名 | 视图名
[ ( 列表 ) ]
VALUES ( DEFAULT | NULL | 表达式…) /*指定列值*/
| DEFAULT VALUES /*强制新行包含为每个列定义的默认值*/
| SELECT命令
【例7.12】使用简单的插入语句向Consumer表中插入一个客户记录。
USE Store
INSERT INTO Consumer values (1,'刘宇豪','男','仁厚街21号')
由于插入的数据包含各列的值并按表中各列的顺序列出这些值，所以省略列名表(colume_list)。
【例7.13】显式指定列名表向Consumer表中插入一个客户记录。
USE Store
INSERT INTO Consumer (ConsumerID, Name, Sex, Address) values (1,'刘宇豪','男','仁厚街21号')
本例与上例功能完全相同，但本例显式列出列名表(colume_list)，显示列表可用于插入值少于列的个数或插入与列的顺序不同的数据。
【例7.14】向Employee表插入表6.1各行数据。
向Employee表插入表6.1各行数据的语句如下：
USE StoreSales
INSERT INTO Employee VALUES
('E001','孙勇诚','男','1981-09-24','东大街28号',4000,'D001'),
('E002','罗秀文','女','1988-05-28','通顺街64号',3200,'D002'),
('E003','刘强','男','1972-11-05','玉泉街48号',6800,'D004'),
('E004','徐莉思','女','1985-07-16','公司集体宿舍',3800,'D003'),
('E005','廖小玉','女','1986-03-19',NULL,3500,'D001'),
('E006','李清林','男','1976-12-07','顺城街35号',4200,'D001');
GO
7.3.2 修改语句
UPDATE语句用于修改数据表或视图中特定记录或列的数据，它的基本
语法格式如下：
UPDATE [ TOP ( 表达式 ) [ PERCENT ] ]
{ 表名 | 视图名 }
SET { 列名=表达式, … } /*赋予新值*/
[ WHERE <查找条件> | … ] /*指定条件*/
……
【例7.15】 在Consumer表中将ConsumerID为1的客户的
Address修改为’沙湾路35号’。
USE Store
UPDATE Consumer
SET Address ='沙湾路35号'
WHERE ConsumerID=1
7.3.3 删除语句
DELETE语句用于删除表或视图中的一行或多行记录，它的基本语法格式如下：
DELETE [FROM] { table_name | view_name }
[WHERE <search_condition>]
该语句的功能为从table_name指定的表或view_name所指定的视图中删除满足<search_condition>条件的行，若省略该条件，则删除所有行。
【例7.16】删除Consumer表中客户号为1的记录。
USE Store
DELETE Consumer
WHERE ConsumerID=1
本章练习题请参考我的文章：数据库操纵语言笔记
第8章 数据查询语言
8.1 基本查询
T-SQL对数据库的查询使用SELECT语句，SELECT语句具有灵活的使用方式和强大的功能，SELECT语句的基本语法格式如下：
语法格式:
SELECT select_list /*指定要选择的列*/
FROM table_source /*FROM子句，指定表或视图*/
[ WHERE search_condition ] /*WHERE子句，指定查询条件*/
[ GROUP BY group_by_expression ] /*GROUP BY子句，指定分组表达式*/
[ HAVING search_condition ] /*HAVING子句，指定分组统计条件*/
[ ORDER BY order_expression [ ASC | DESC ]]
/*ORDER子句，指定排序表达式和顺序*/
8.1.1 SELECT子句
投影查询通过SELECT语句的SELECT子句来表示，SELECT子句
用于选择表中的部分或全部列，并组成结果表。
语法格式:
SELECT [ ALL | DISTINCT ] [ TOP n [ PERCENT ] [ WITH TIES ] ]
<select_list>
select_list指出了结果的形式，其格式为：
{ * /*选择当前表或视图的所有列*/
| { 表名 | 视图名 | 表别名 } . * /*选择指定的表或视图的所有列*/
| { 列名 | expression | $IDENTITY | $ROWGUID [ [ AS ] column_alias ]
/*选择指定的列并更改列标题，为列指定别名，还可用于为表达式
结果指定名称，*/
| column_alias = expression
} [ , … n ]
1．投影指定的列
使用SELECT语句可选择表中的一个列或多个列，如果是多个列，
各列名中间要用逗号分开。
语法格式:
SELECT column_name [ , column_name…]
FROM table_name
WHERE search_condition
其中，FROM子句用于指定表，WHERE在该表中检索符合
search_condition条件的列。
【例8.1】 查询StoreSales数据库中Employee表的所有员工的员工号、
姓名和部门号。
USE StoreSales
SELECT EmplID, EmplName, DeptID
FROM Employee
2．投影全部列
在SELECT子句指定列的位置上使用*号时，则为查询表中所有列。
【例8.2】查询StoreSales数据库中Employee表的所有列。
USE StoreSales
SELECT *
FROM Employee
该语句与下面语句等价：
USE StoreSales
SELECT EmplID, EmplName, Sex, Birthday, Address, Wages, DeptID
FROM Employee
3．修改查询结果的列标题
为了改变查询结果中显示的列标题，可以在列名后使用AS子句，
语法格式如下：
AS column_alias
其中column_alias是指定显示的列标题，AS可省略。
【例8.3】查询Employee表中的EmplID, EmplName, DeptID，并将
结果中各列的标题分别修改为员工号、姓名和部门号。
USE StoreSales
SELECT EmplID AS '员工号', EmplName AS '姓名', DeptID AS '部门号'
FROM Employee
4．去掉重复行
去掉结果集中的重复行可使用DISTINCT关键字，其语法格式是：
SELECT DISTINCT column_name [ , column_name…]
【例8.4】查询Employee表中DeptID列，消除结果中的重复行。
USE StoreSales
SELECT DISTINCT DeptID
FROM Employee
8.1.1 SELECT子句
4．去掉重复行
去掉结果集中的重复行可使用DISTINCT关键字，其语法格式是：
SELECT DISTINCT column_name [ , column_name…]
【例8.4】查询Employee表中DeptID列，消除结果中的重复行。
USE StoreSales
SELECT DISTINCT DeptID
FROM Employee
WHERE子句用于指定查询条件，该子句位于FROM子句的后面，
选择查询通过WHERE子句实现。
语法格式：
WHERE <search_condition> 其中search_condition为查询条件。
8.1.3 WHERE子句
1．表达式比较
比较运算符用于比较两个表达式值，共有9个，分别是 =（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<>（不等于）、!=（不等于）、!<（不小于）、!>（不大于）。比较运算的格式为：
表达式1 {比较运算符} 表达式2
【例8.6】查询Employee表中员工月工资在3000元至4500元之间的名单。
USE StoreSales
SELECT *
FROM Employee
WHERE Wages >=3000 AND Wages <=4500
【例8.7】查询Employee表中部门号为D001或性别为男的员工。
USE StoreSales
SELECT *
FROM Employee
WHERE DeptID='D001' OR Sex='男'
2．范围比较
BETWEEN、NOT BETWEEN、IN是用于范围比较的三个关键字，用于
查找字段值在（或不在）指定范围的行。
【例8.8】查询Employee表中部门号为D002、D003、D004的员工。
USE StoreSales
SELECT *
FROM Employee
WHERE DeptID IN ('D002', 'D003', 'D004')
3．模式匹配
字符串模式匹配使用LIKE谓词，返回逻辑值TRUE或FALSE ，LIKE谓
词表达式的语法格式如下：
表达式 [ NOT ] LIKE 模式串 [ ESCAPE 转义符 ]
说明：
（1）表达式：一般为字符串表达式，在查询语句中可以是列名。
（2）模式串：可以使用通配符，%：代表0或多个字符，_：代表一个
字符。
（3）转义符：应为有效的SQL Server字符，没有默认值，且必须为单
个字符。当模式串中含有与通配符相同的字符时，应通过该字符前的
转义符指明其为模式串中的一个匹配字符。使用ESCAPE可指定转义
符。
（4）NOT LIKE：使用NOT LIKE与LIKE的作用相反。
【例8.9】查询Employee表中姓李的员工。
USE StoreSales
SELECT *
FROM Employee
WHERE EmplName LIKE '李%'
4．空值使用
空值是未知的值，判定一个表达式的值是否为空值时，使用
IS NULL关键字，语法格式如下：
表达式 IS [ NOT ] NULL
【例8.10】查询地址为空值的员工情况。
USE StoreSales
SELECT *
FROM Employee
WHERE Address IS NULL
8.1.4ORDER BY子句
为了使查询结果有序输出，需要使用ORDER BY子句，可按照一个或多个
字段的值进行排序，ORDER BY子句的格式如下：
[ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,…n ]
【例8.11】将D001部门的员工按出生时间先后排序。
USE StoreSales
SELECT *
FROM Employee
WHERE DeptID ='D001'
ORDER BY Birthday
8.2 统计计算
8.2.1 聚合函数
T-SQL提供聚合函数实现数据统计或计算，用于计算表中的数据，返回单个计算结果。聚合函数一般参数语法格式如下：
( [ ALL | DISTINCT ] expression )
其中，ALL表示对所有值进行聚合函数运算，ALL为默认值，DISTINCT表示去除重复值，expression指定进行聚合函数运算的表达式。
1.MAX、MIN和AVG
MAX、MIN和AVG分别用于求表达式中所有值项的最大值、最小值和平均值，
语法格式为：
MAX / MIN /AVG ( [ ALL | DISTINCT ] 表达式 )
【例8.12】查询D001部门员工的最高工资、最低工资、平均工资。
USE StoreSales
SELECT MAX(Wages) AS '最高工资',MIN(Wages) AS '最低工资',AVG(Wages)
AS '平均工资'
FROM Employee
WHERE DeptID ='D001‘
2.COUNT
COUNT用于统计组中满足条件的行数或总行数，格式为：
COUNT ( { [ ALL | DISTINCT ] 表达式 } | * )
【例8.13】查询部门号为D001的员工总人数。
USE StoreSales
SELECT COUNT(*) AS '总人数'
FROM Employee
WHERE DeptID ='D001'
3.SUM
SUM用于求表达式中所有值项的总和，格式为：
SUM ( [ ALL | DISTINCT ] 表达式 )
查询所有员工工资总额。
USE StoreSales
SELECT SUM(Wages) AS ‘工资总额'
FROM Employee
8.2.2 GROUP BY子句
GROUP BY子句用于将查询结果表按某一列或多列值进行分组，
其语法格式为如下：
[ GROUP BY [ ALL ] group_by_expression [,…n]]
其中，group_by_expression为分组表达式，通常包含字段名，ALL
显示所有分组，WITH指定CUBE或ROLLUP操作符，在查询结果
中增加汇总记录
【例8.14】求各部门的平均工资和人数。
USE StoreSales
SELECT DeptID AS '部门号', AVG(Wages) AS '平均工资', COUNT(*) AS '人数'
FROM Employee
GROUP BY DeptID
8.2.3 HAVING子句
HAVING子句用于对分组按指定条件进一步进行筛选，最后只
输出满足指定条件的分组， HAVING子句的格式为：
[ HAVING <search_condition> ]
其中，search_condition为查询条件，可以使用聚合函数。
当WHERE子句、GROUP BY子句、HAVING子句在一个
SELECT语句中时，执行顺序如下：
（1）执行WHERE子句，在表中选择行。
（2）执行GROUP BY子句，对选取行进行分组。
（3）执行聚合函数。
（4）执行HAVING子句，筛选满足条件的分组。
【例8.15】列出平均工资大于或等于3800元的部门编号和平均工资。
USE StoreSales
SELECT DeptID AS '部门号', AVG(Wages) AS '平均工资'
FROM Employee
GROUP BY DeptID
HAVING AVG(Wages)>=3800
8.3 连接查询
8.3.1 连接谓词
连接谓词在WHERE子句中使用比较运算符给出连接条件对表进行
连接，在FROM子句中指定要连接的表，其一般语法格式为：
[<表名1.>] <列名1> <比较运算符> [<表名2.>] <列名2>
比较运算符有：<、<=、=、>、>=、!=、<>、!<、!>
由于连接多个表存在公共列，为了区分是哪个表中的列，引入表名
前缀指定连接列。例如，student.stno表示student表的stno列，
score.stno表示score表的stno列
经常用到的连接如下：
● 等值连接：表之间通过比较运算符“=”连接起来，称为等值连接。
● 非等值连接：表之间使用非等号进行连接，则称为非等值连接。
● 自然连接：如果在目标列中去除相同的字段名，称为自然连接。
● 自连接：将同一个表进行连接，称为自连接。
【例8.16】查询员工及其所属的部门的情况。
USE StoreSales
SELECT Employee.*, Department.*
FROM Employee, Department
WHERE Employee.DeptID=Department.DeptID
该语句采用等值连接
【例8.17】对上例进行自然连接查询。
USE StoreSales
SELECT Employee.*, Department.DeptName
FROM Employee, Department
WHERE Employee.DeptID=Department.DeptID
【例8.18】查询所有员工的销售单，要求有姓名、订单号、商品号、商品名
称、订单数量、折扣总价、总金额。
题意分析：
（1）查询姓名、订单号、商品号、商品名称、订单数量、折扣总价、总金额，涉及到4个表：Employee 、Goods、SalesOrder ，OrderDetail，可选用多表连接。
（2）连接可用谓词连接或JOIN连接，这里选用谓词连接，后面的例题选用JOIN连接，注意比较谓词连接与JOIN连接写法的不同。
USE StoreSales
SELECT EmplName, b.OrderID, c.GoodsID, GoodsName, Quantity,
DiscountTotal, Cost
FROM Employee a, SalesOrder b, OrderDetail c, Goods d
WHERE a.EmplID=b.EmplID AND b.OrderID=c.OrderID AND
c.GoodsID=d.GoodsID
该语句用谓词连接实现了4个表的连接，并采用别名以缩写表名, 本例中
为Employee指定的别名是a, 为SalesOrder指定的别名是b, 为OrderDetail指
定的别名是c，为Goods指定的别名是d
【例】查询所有员工信息及部门信息，要求有员工号、姓名、部
门号、部门名称。
USE StoreSales
select EmplID,EmplName,DeptName,b.DeptID
from dbo.Department a ,dbo.Employee b
where a.DeptID=b.DeptID
select EmplID,EmplName,DeptName,b.DeptID
from dbo.Department a join dbo.Employee b
on a.DeptID=b.DeptID
8.3.2 JOIN连接
以JOIN关键字指定连接的表示方式在FROM子句中建立，这样有
助于将连接操作和WHERE字句中的搜索条件区分开，在T-SQL中
推荐使用这种方式。JOIN连接在FROM子句的< 连接表 >中指定。
语法格式 ：
<连接表> ::=
{
<表源> <类型> <表源> ON <查询条件>
| <表源> CROSS JOIN <表源>
| 左表源 { CROSS | OUTER } APPLY 右表源
| [ ( ) <连接表> [ ] ]
}
说明：
（1）<表源>：准备要连接的表。
（2）<类型>：表示连接类型。
格式为：
<类型> ::=
[ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <连接提示 > ] ] JOIN
其中，INNER表示内连接，OUTER表示外连接。
（3）ON：用于指定连接条件，<查询条件>为连接的条件。
（4）APPLY运算符：使用APPLY运算符可以为实现查询操作的外部表表达式
返回的每个行调用表值函数。
（5）CROSS JOIN：表示交叉连接。
- 内连接
内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。
内连接是系统默认的，可省略INNER关键字。
【例8.19】查询所有员工的销售单，要求有姓名、订单号、商品号、
商品名称、订单数量、折扣总价、总金额，采用内连接。
USE StoreSales
SELECT EmplName, b.OrderID, c.GoodsID, GoodsName, Quantity,
DiscountTotal, Cost
FROM Employee a JOIN SalesOrder b ON a.EmplID=b.EmplID
JOIN OrderDetail c ON b.OrderID = c.OrderID
JOIN Goods d ON c.GoodsID = d.GoodsID
该语句采用JOIN连接中的内连接，实现4个表的连接，省略INNER关键字，查询结果与例8.23相同。
2. 外连接
在内连接的结果表，只有满足连接条件的行才能作为结果输出。外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接有以下3种：
● 左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行；
● 右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行；
● 完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。
【例8.20】对StoreSales数据库，员工表Employee左外连接销售表SalesOrde。
USE StoreSales
SELECT a.EmplID, a.EmplName, b.OrderID, b.Cost
FROM Employee a LEFT OUTER JOIN SalesOrder b ON a.EmplID =b.EmplID
该语句采用左外连接。
【例8.21】对StoreSales数据库，员工表Employee右外连接销售表
SalesOrde。
在SalesOrde表中，插入一条记录
USE StoreSales
INSERT INTO SalesOrder VALUES ('S00004','','','2017-07-14',11337)
对员工表Employee和销售表SalesOrde进行右外连接。
USE StoreSales
SELECT a.EmplID, a.EmplName, b.OrderID, b.Cost
FROM Employee a RIGHT OUTER JOIN SalesOrder b ON a.EmplID =b.EmplID
【例8.22】对StoreSales数据库，员工表Employee全外连接销售表SalesOrde。
USE StoreSales
SELECT a.EmplID, a.EmplName, b.OrderID, b.Cost
FROM Employee a FULL OUTER JOIN SalesOrder b ON a.EmplID =b.EmplID
该语句采用全外连接。
3. 交叉连接
【例8.23】采用交叉连接查询员工表Employee和部门表Department
所有可能组合。
USE StoreSales
SELECT a. DeptID, a. DeptName, b.EmplID, b.EmplName
FROM Department a CROSS JOIN Employee b
8.4 嵌套查询
在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询
块。在WHERE子句或HAVING子句所指定条件中，可以使用另
一个查询块的查询的结果作为条件的一部分，这种将一个查询
块嵌套在另一个查询块的子句指定条件中的查询称为嵌套查询。
T-SQL允许SELECT多层嵌套使用。
8.4.1 IN子查询
IN子查询用于进行一个给定值是否在子查询结果集中的判断，语法格式如下：
表达式 [ NOT ] IN ( 子查询 )
当表达式与子查询的结果集中的某个值相等时，IN谓词返回
TRUE，否则返回FALSE。若使用了NOT，则返回的值相反
【例8.24】列出销售部和物资部所有员工的情况。
USE StoreSales
SELECT * FROM Employee
WHERE DeptID IN ( SELECT DeptID
FROM Department
WHERE DeptName='销售部' OR DeptName='物资部' )
该语句采用IN子查询。
1462
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
