资源简介

(共37张PPT)
关系数据库标准语言SQL
第3章
目录
01
SQL概述
02
数据库的创建
与管理
03
数据表及其操作
04
数据查询
05
视图
06
索引
本章主要内容
结构化查询语言（Structured Query Language，SQL）是关系数据库管理的标准语言，具有数据定义、数据查询、数据操纵和数据控制四个方面的功能。SQL结构简单，功能齐全，是目前广泛应用的关系数据库查询语言。
本章主要介绍SQL Server 2019数据库管理系统各种工具的使用和SQL的使用。
SQL概述
第3章
01
3.1.1 SQL的发展
1970年，Codd发表了关系数据库理论，从此奠定了关系数据库的理论基础。
1974年，博伊斯（Boyce）和钱伯林（Chamberlin）在关系数据库理论的基础上，提出了结构化英语查询语言（Structure English Query Language，SEQUEL）。
1975—1985年，IBM公司首先研制了关系数据库原型System R，该系统实现了对SEQUEL的支持。进而，IBM公司推出了商业数据库SQL/DS，极大地推动了SQL的发展。
3.1.1 SQL的发展
1986年10月，美国国家标准协会（American National Standards Institute，ANSI）首先推出了关系数据库标准SQL-86。
1987年6月，国际标准化组织（International Organization for Standardization，ISO）正式将SQL-86采纳为国际标准。
1992年11月，ISO公布了SQL的新标准，即SQL-92，SQL得到极大推广。
1999年，ISO推出了SQL-99标准，该标准对SQL进行进一步拓展，增加了对面向对象功能的支持。
尽管不同的数据库产品厂商对SQL标准在支持度上有略微的差异，但大多数都遵循SQL-99标准。本章主要介绍SQL的基本概念和基本功能。
3.1.2 SQL的特点
SQL的结构简单、功能强大和简单易学的优点使得SQL能够在众多的数据库查询语言中脱颖而出，被工业界和用户广泛接受。
功能全面：SQL是一种一体化的语言，它具有数据定义、数据查询、数据操纵和数据控制等方面的功能，用户通过SQL可以完成有关数据库的全部工作。
高度非过程化的语言：SQL是一种高度非过程化的语言，也就是说在使用SQL语句做想做的事情时，用户只需要声明想做什么，而无须具体指出做这件事情的详细步骤。
3.1.2 SQL的特点
不要求用户指定对数据的存取方法：所有SQL语句都使用查询优化器，它可以加快数据存取的速度。这种特性使用户更容易把精力集中于要得到的结果。查询优化器知道存在什么索引，以及在哪里使用索引合适，而用户不需要知道表是否有索引、表有什么类型的索引等具体内容。
易学易用：SQL语句结构简单，功能强大，核心任务只需要9个动词就可以完成。并且其语法也非常简单，非常接近英语自然语言，容易学习和掌握。
使用方式灵活：SQL既可以作为嵌入式语言，也可以直接以命令方式交互使用。SQL作为嵌入式语言使用时，SQL语句可以嵌入各种主流编程语言中，以供用户灵活使用。SQL作为交互式语言使用时，用户可以直接在数据库管理系统客户端输入SQL命令，查看执行结果。尽管SQL的使用方式不同，但SQL的语法结构基本上是一致的。这种以统一的语法结构提供两种不同使用方式的做法，为用户提供了极大的灵活性和方便性。
3.1.3 SQL的组成
SQL主要由数据定义语言（Data Definition Language，DDL）、数据操纵语言（Data Manipulation Language，DML）、数据查询语言（Data Query Language，DQL）和数据控制语言（Data Control Language，DCL）等几部分组成。
DDL：在数据库系统中，数据库及数据库中的表、视图和索引等数据库对象的建立、更改和删除都通过DDL完成，DDL包括CREATE、ALTER、DROP等关键字。
DML：DML是指用来添加、修改和删除数据库中数据的语句，包括INSERT、UPDATE和DELETE等关键字。
3.1.3 SQL的组成
DQL：查询操作是数据库中最基本的操作，DQL用于检索数据库中满足条件的数据。例如，使用SELECT关键字查询表中的记录。
DCL：DCL用来授予或回收访问数据库的某种特权，主要包括GRANT和REVOKE关键字。
数据库的创建与管理
第3章
02
3.2.1 数据库的存储结构
数据库的存储结构分为逻辑存储结构和物理存储结构两种。从逻辑存储结构的角度来看，SQL Server数据库将数据组织成若干个可视化的数据表、索引、视图、函数和存储过程等逻辑对象。从物理存储结构的角度来看，SQL Server数据库将数据组织成各种数据库文件，即数据文件和事务日志文件。
数据库的逻辑存储结构：数据库的逻辑存储结构主要应用于面向用户的数据组织和管理，如数据库的数据表、视图、数据类型、存储过程和触发器等。这些数据库对象是用户使用数据库的基本单位。
3.2.1 数据库的存储结构
数据库的物理存储结构：数据库的物理存储结构主要应用于面向计算机的数据组织和管理，如数据文件、表和视图的数据组织方式。每个SQL Server数据库被组织成数据文件和事务日志文件两种类型的操作系统文件。数据文件包含数据和对象，例如表、索引、存储过程和视图，又可分为主要数据文件和次要数据文件。事务日志文件包含恢复数据库中的所有事务所需的信息。为了便于分配和管理，可以将数据文件集合起来，放到文件组中。
（1）主要数据文件：主要数据文件（Primary Data File）包含数据库的启动信息，并指向数据库中的其他文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有且仅有一个主要数据文件。主要数据文件的默认扩展名是.mdf。
3.2.1 数据库的存储结构
（2）次要数据文件：次要数据文件（Secondary Data File）是可选的，由用户定义并存储用户数据。次要数据文件的默认扩展名是.ndf。
（3）事务日志文件：事务日志文件（Transaction Log File）保存用于恢复数据库的日志信息。当数据库被损坏的时候，管理员可以使用事务日志文件恢复数据库。每个数据库必须至少拥有一个事务日志文件。事务日志文件的默认扩展名是.ldf。
一个数据库应包含一个主要数据文件和至少一个事务日志文件。主要数据文件包含数据库中的所有数据。如果主要数据文件不能满足数据库中数据的存储需求，就需要多个次要数据文件。默认情况下，数据文件和事务日志文件被放在同一个驱动器上的同一个路径下。但是，在生产环境中，这可能不是最佳的方法。考虑到数据安全问题，建议将数据文件和事务日志文件放在不同的磁盘上。
3.2.1 数据库的存储结构
为了方便用户对数据库文件进行分配和管理，SQL Server 2019将文件分成不同的文件组。文件组主要包含主要文件组、用户定义文件组和默认文件组3种类型。
（1）主要文件组：每个数据库都有一个主要文件组，主要文件组包含主要数据文件和未放入其他文件组的所有次要数据文件。所有系统表都被分配到主要文件组中。
（2）用户定义文件组：用户定义文件组用于将数据文件集合起来，以便于进行管理、数据分配和放置。用户定义文件组是通过在CREATE DATABASE或ALTER DATABASE语句中使用FILEGROUP关键字指定的任何文件组。事务日志文件不属于任何文件组。
3.2.1 数据库的存储结构
（3）默认文件组：如果在数据库中创建对象时没有指定对象所属的文件组，对象将被分配给默认文件组。不管何时，只能将一个文件组指定为默认文件组。默认文件组中的文件必须足够大，能够容纳未分配给其他文件组的所有新对象。PRIMARY文件组是系统提供的默认文件组，除非使用ALTER DATABASE语句进行更改，但系统对象和表仍然分配给PRIMARY文件组，而不是新的默认文件组。
3.2.2 系统数据库
SQL Server 2019包含两种类型的数据库：用户数据库和系统数据库。用户数据库是由用户创建、存储用户数据和对象的数据库，例如图书馆管理系统数据库。系统数据库存放SQL Serve 2019的系统级信息，例如系统配置、数据库属性、登录账号、数据库文件、数据库备份、警报和作业等。打开SQL Server 2019的对象资源管理器，系统会自动创建4个系统数据库，分别为master数据库、model数据库、tempdb数据库、msdb数据库，如图所示。
3.2.2 系统数据库
master数据库：master数据库记录了SQL Server中的所有系统级别信息，例如系统中所有的登录账户、端点、系统配置信息、SQL Server的初始化信息及数据库错误信息等。此外，master数据库还记录所有其他数据库是否存在及这些数据库文件的位置。master数据库是SQL Server启动的第一个入口，记录了SQL Server的初始化信息。因此，master数据库是所有系统数据库的重中之重，如果master数据库被破坏，则SQL Server将无法启动。
model数据库：model数据库是SQL Server创建用户数据库的模板。当用户创建一个数据库时，model数据库的内容会自动复制到用户数据库中，因此SQL Server系统中必须有model数据库。如果对model数据库进行某些修改，例如修改数据文件的大小，新创建的用户数据库的内容将以修改后的model数据库为模板进行创建。
3.2.2 系统数据库
tempdb数据库：tempdb数据库是连接到SQL Server实例的所有用户都可用的全局资源。所有与系统连接的用户的临时表和临时数据库对象，以及SQL Server产生的其他临时性对象都存储于该数据库。tempdb数据库是临时的，每次启动SQL Server时，都要重新创建tempdb数据库，以便系统启动时，该数据库总是空的。tempdb数据库在断开连接时会自动删除保存的临时表和临时数据库对象，并且在系统关闭后没有活动连接。
msdb数据库：msdb数据库是SQL Server代理程序调度警报和作业以及记录操作员时使用的数据库。SQL Server代理程序能够按照系统管理员的设定监控用户对数据的非法操作，并能及时向系统管理员发出警报。当代理程序调度警报、作业和记录操作时，系统要用到或实时产生许多相关信息，这些信息一般存储在msdb数据库中。
3.2.3 创建用户数据库
在SQL Server 2019中，主要采用两种方法创建用户数据库：一种方法是使用SQL Server Management Studio的对象资源管理器，以图形化的方式创建用户数据库，此方法简单直观；另一种方法是使用T-SQL语句创建用户数据库，此方法可以保存创建数据库的脚本文件，可以在其他机器上使用此脚本文件创建相同的数据库。
3.2.3 创建用户数据库
（1）使用对象资源管理器创建用户数据库。
首先，在对象资源管理器窗口中，展开SQL Server服务器实例，用鼠标右键单击（简称右击）“数据库”选项，在弹出的快捷菜单中选择“新建数据库”命令，如图所示。
3.2.3 创建用户数据库
然后，在“新建数据库”窗口中的“常规”标签页中，如图所示，可以定义数据库名称、数据库所有者、是否使用全文检索、数据库文件（数据文件和事务日志文件）的逻辑名称、数据库文件的初始大小和增长方式等。
单击“新建数据库”窗口中的“常规”标签页上的“添加”按钮，可以为数据库添加次要数据文件和事务日志文件。
3.2.3 创建用户数据库
在“新建数据库”窗口中的“选项”标签页中可以选择定义排序规则、恢复模式、游标选项、兼容性级别等，如图所示。
在“新建数据库”窗口中的“文件组”标签页中可以选择添加数据库的文件组。
设置完成后单击“确定”按钮，TeachSystem数据库创建成功。
3.2.3 创建用户数据库
（2）使用T-SQL创建用户数据库
使用CREATE DATABASE语句可以创建数据库，在创建时指定数据库名称、数据库文件的存放位置、大小、文件的最大容量和文件的增量等。语法格式如右图所示。
3.2.3 创建用户数据库
【例3.1】创建数据库TeachSystem，该数据库存放在D:\Data下，数据文件的逻辑名称为TeachSystem，物理文件名为TeachSystem.mdf，初始大小为5MB，最大容量为100MB，增量为1MB；事务日志文件的名称为TeachSystem_log，物理文件名为TeachSystem.ldf，初始大小为10MB，最大容量为2GB，增量为10%。
CREATE DATABASE TeachSystem
ON PRIMARY
( NAME='TeachSystem',
FILENAME='D:\Data\TeachSystem.mdf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=1MB)
LOG ON
( NAME='TeachSystem_log',
FILENAME='D:\Data\TeachSystem_log.ldf',
SIZE=10MB ,
MAXSIZE=2GB,
FILEGROWTH=10%)
3.2.4 修改用户数据库
数据库创建完成后，用户在使用过程中可以根据需要对其原始定义的数据库参数进行修改。其中，修改数据库的方式有两种，一种是利用对象资源管理器修改数据库，另一种是利用T-SQL语句修改数据库的属性。
（1）使用对象资源管理器修改用户数据库
启动SQL Server Management Studio，在对象资源管理器中，右击TeachSystem数据库，在弹出的快捷菜单中选择“属性”命令，打开“数据库属性”窗口，在“数据库属性”窗口的“常规”标签页中，显示的是数据库的基本信息，如图所示，这些信息不能修改。
3.2.4 修改用户数据库
单击“文件”标签，如图所示，可以修改数据库的逻辑名称、初始大小和自动增长等属性，也可以根据需要添加数据文件和事务日志文件，还可以更改数据库的所有者。
在“数据库属性”窗口的“选项”标签页，单击“限制访问”下拉列表框，选择不同选项可以限制访问数据库的用户。
3.2.4 修改用户数据库
（2）使用T-SQL语句修改用户数据库
使用ALTER DATABASE语句修改用户数据库的语法形式如下。
3.2.4 修改用户数据库
【例3.2】为TeachSystem数据库增加一个数据文件和一个事务日志文件，数据文件的逻辑名为TeachSystem_data，初始
大小为10MB，最大容量为500MB，增量为5MB。事务日志文件的逻辑名为TeachSystem_data_log，初始大小为3MB，最大容量为100MB，增量为1MB。
ALTER DATABASE TeachSystem
ADD FILE
( NAME= TeachSystem_data,
FILENAME='D:\Data\TeachSystem_data.ndf',
SIZE=10MB,
MAXSIZE=500MB,
FILEGROWTH=5MB)
go
ALTER DATABASE TeachSystem
ADD LOG FILE
( NAME= TeachSystem_data_log,
FILENAME='D:\Data\TeachSystem_data_log.ldf ',
SIZE=3MB,
MAXSIZE=100MB,
FILEGROWTH=1MB)
go
3.2.4 修改用户数据库
【例3.3】修改TeachSystem数据库的名称为Teach。
ALTER DATABASE TeachSystem MODIFY NAME=Teach
数据库一经创建，由于许多应用程序会使用该数据库，因此原则上不允许修改数据库的名称。如果确实需要修改数据库的名称，则可以使用数据库定义语言进行修改。更改数据库的名称，除了使用SQL命令之外，也可以使用系统存储过程sp_rename。同理，也可以采用图形化界面修改数据库名称，启动SQL Server Management Studio，在对象资源管理器中，在选择的数据库上右击，在弹出的快捷菜单中选择“重命名”命令，然后输入数据库的新名称即可。
3.2.5 删除用户数据库
在SQL Server 2019中，除了系统数据库，用户创建的数据库都可以删除。数据库一旦被删除则不能够恢复，相应的数据文件和事务日志文件也会被删除。删除用户数据库的方式也有两种，一种是使用对象资源管理器删除用户数据库，另一种是采用T-SQL语句删除用户数据库。
（1）使用对象资源管理器删除用户数据库
启动SQL Server Management Studio，在对象资源管理器中，选中TeachSystem数据库，右击，在弹出的快捷菜单中选择“删除”命令，如图所示，然后打开“删除对象”对话框，单击“确定”按钮即可删除数据库。
3.2.5 删除用户数据库
（2）使用T-SQL语句删除用户数据库
使用DROP DATABASE命令可以一次删除一个或多个数据库。数据库的所有者和数据库管理员才有权限执行此命令，其语法格式如下。
DROP DATABASE database_name[,…]
【例3.4】删除用户创建的TeachSystem数据库。
DROP DATABASE TeachSystem
注意，用户只能删除自己创建的数据库，不能删除其他用户创建并且仍在使用的数据库。
3.2.6 查看数据库信息
（1）打开数据库
在用户连接上SQL Server时，系统会打开默认的数据库。一般用户需要具体指定连接到哪个数据库，或者从一个数据库切换到另一个数据库，可以在SQL编辑器中使用USE命令打开或者切换到不同数据库。
打开或切换数据库的命令格式如下。
USE database_name
其中，database_name为要打开或切换的数据库的名称。
例如打开TeachSystem数据库，则使用命令USE TeachSystem。
3.2.6 查看数据库信息
（2）查看数据库的属性信息
要确认数据库的当前状态，除了通过“数据库属性”窗口的各个标签页查看数据库属性以外，还可以采用T-SQL语句查看。例如，使用sys.databases数据库和文件目录视图可以查看有关数据库的基本信息，使用sys.database_files可以查看数据库文件的信息，使用sys.filegroups可以查看有关数据库文件组的信息，利用sys.master_files可以查看数据库文件的基本信息和状态信息，使用系统存储过程sp_helpdb可以查看数据参数信息，使用系统存储过程sp_dboption可以查看数据库的选项信息。
【例3.5】通过sys.database查看数据库的状态信息。具体的T-SQL语句如下。
SELECT name,state,state_desc FROM sys.databases
在查询设计器窗口中输入如上代码并执行。
3.2.6 查看数据库信息
【例3.6】通过sys.master_files查看数据文件和事务日志文件。具体的T-SQL语句如下。
SELECT name,physical_name,type,type_desc,state, state_desc FROM sys.master_files
在查询设计器窗口中输入如上代码并执行，就可以查看数据文件和事务日志文件的有关信息。
【例3.7】使用sp_helpdb查看数据库和数据库参数的有关信息。具体的T-SQL语句如下。
EXECUTE sp_helpdb 'TeachSystem'
在查询设计器窗口中输入如上代码并执行，
3.2.7 分离和附加用户数据库
在SQL Server系统中可以通过分离和附加用户数据库快速地把用户数据库从一台计算机移动到另外一台计算机。首先将数据库文件与该SQL Server系统进行分离，即脱离关系，这时在该服务器上无法连接该数据库，但数据库文件仍然在该服务器的硬盘上，然后把分离下来的数据库文件通过附加数据库的技术将数据库文件附加到另外一台服务器上，这样在另外一台服务器上就可以使用和管理该数据库。
（1）分离数据库
在SQL Server Management Studio中选中TeachSystem数据库，右击，在弹出的快捷菜单中依次选择“任务”→“分离”命令。
在弹出的对话框中进行设置。设置数据库TeachSystem的分离参数，单击“确定”按钮，即可完成操作。
3.2.7 分离和附加用户数据库
（2）附加数据库
在“对象资源管理器”中右击“数据库”选项，从弹出的快捷菜单中选择“附加”命令，如图所示，打开“附加数据库”对话框。
在弹出的“附加数据库”对话框中，单击“添加”按钮。在弹出的“定位数据库文件”对话框中，选择要添加的数据库的主要数据文件，如图所示。数据库TeachSystem的主要数据文件为TeachSystem.mdf。

展开更多......

收起↑