第4章-关系数据库标准语言sql课件

第4章-关系数据库标准语言sql4.1.1SQLServer2000概述SQLServer是一个客户/服务器关系型数据库管理系统。使用Transact-SQL在客户机和SQLServer之间发送请求。4.1SQLServer简介SQLServer2000是Microsoft公司在2000年推出的SQLServer数据库管理系统。2①企业版(EnterpriseEdition)支持SQLServer2000的所有特性，可作为大型Web站点、企业的数据库服务器。②标准版(StandardEdition)用于小型的工作组或部门。4.1.1SQLServer2000概述

SQLServer2000的常用版本有：3③个人版(PersonalEdition)用于单机系统或客户机。④开发版(DeveloperEdition)：用于程序员开发应用程序，需要SQLServer2000作为数据存储设备。4.1.1SQLServer2000概述4SQLServer的软硬件平台要求:①硬件要求：目前的计算机CPU、内存和硬盘等都能满足SQLServer2000的要求。②软件要求：

SQLServer2000标准版和企业版只能安装在服务器版的操作系统中。Windows2000和WindowsXP操作系统，只能安装个人版和开发版的SQLServer2000。4.1.1SQLServer2000概述54.1.1SQLServer2000概述版本或组件操作系统要求企业版WindowsServer版标准版WindowsServer版个人版Windows各个版本开发版WindowsServer版WindowsWorkstation版WindowsProfessional版客户端Windows各个版本表4.1.1不同版本SQLServer2000对操作系统的要求64.1.2SQLServer2000的主要组件

SQLServer2000的安装：见“SQLServer2000安装”课件74.1.2SQLServer2000的主要组件

SQLServer2000的主要组件:SQLServer主要组件81.企业管理器企业管理器是最主要的管理工具，可以完成绝大部分数据库管理工作。主要为：①注册和管理SQLServer服务器②创建和管理数据库和数据库对象③备份和还原数据库和事务日志④设置任务调度、警报和提供服务器的控制功能⑤创建与管理用户账号和组4.1.2SQLServer2000的主要组件92.查询分析器查询分析器是一个图形化的数据库编程接口。通过查询分析器可以输入和执行各种Transact-SQL语句。例如：在“查询”窗口中输入语句：

SELECT学号,姓名,性别,出生日期

FROM学生

WHERE“班级”='计00-1'4.1.2SQLServer2000的主要组件104.1.2SQLServer2000的主要组件113.系统数据库SQLServer2000安装好后，系统自动创建6个系统数据库。它们存放在MicrosoftSQLServer默认安装目录下的MSSQL\Data文件夹中，数据库文件的扩展名为.mdf，数据库日志文件的扩展名为.ldf。4.1.2SQLServer2000的主要组件12(1)Master数据库Master数据库记录了SQLServer系统的所有信息。这些信息包括所有的登录信息、系统设置信息、SQLServer的初始化信息和其它系统数据库及用户数据库的相关信息。Master数据库对应的主数据文件是Master.mdf，日志文件是Mastlog.ldf。4.1.2SQLServer2000的主要组件13(2)Model数据库Model数据库是所有用户数据库和Tempdb数据库的模板数据库。当用户使用CREATEDATABASE命令建立新的数据库时，新数据库的第一部分总是通过复制Model数据库中的内容创建，剩余部分由空页填充。Model数据库对应的主数据文件是Model.mdf，日志文件是Modellog.ldf。4.1.2SQLServer2000的主要组件14(3)Msdb数据库Msdb数据库是代理数据库，主要用于SQLServerAgent,为其警报、任务调度和记录操作员的操作提供存储空间。Msdb数据库对应的主数据文件是Msdb.mdf，日志文件是Msdblog.ldf。4.1.2SQLServer2000的主要组件15(4)Tempdb数据库是一个临时数据库。

Tempdb数据库由整个系统的所有数据库使用。它为所有的临时表、临时存储过程及其它临时操作提供存储空间。SQLServer每次启动时，Tempdb数据库被重新建立。当用户与SQLServer断开联系时，其临时表和存储过程被自动删除。对应的主数据文件是Tempdb.mdf，日志文件是Templog.ldf。4.1.2SQLServer2000的主要组件16(5)Pubs数据库和Northwind数据库Pubs数据库:提供一个样本数据库作为学习工具。Northwind数据库：提供一个样本数据库作为学习工具。4.1.2SQLServer2000的主要组件174.SQLServer服务管理器服务管理器是在服务器端运行的程序，其界面如图所示。4.1.2SQLServer2000的主要组件服务管理器用来启动、暂停、继续和停止数据库服务器的实时服务。18在访问数据库之前，必须启动SQLServer数据库服务。SQLServer数据库服务的启动有两种方法：①启动Windows时自动启动，可以通过服务管理器进行设置。②通过“开始｜MicrosoftSQLServer｜服务管理器”进行启动。4.1.2SQLServer2000的主要组件194.1.3创建数据库和基本表1.创建数据库要创建数据库必须拥有创建数据库的权限。在初始情况下，只有系统管理员可以创建数据库。在数据库创建完成后，它的创建者自动成为其所有者。204.1.3创建数据库和基本表创建数据库的工作主要是:

确定数据库名称；确定数据文件的名称、存放位置和增长信息等；确定事务日志文件名称、存放位置和增长信息等。21创建数据库方法有三种：使用企业管理器创建数据库；使用Transact-SQL语言创建;使用向导创建数据库（略讲）。4.1.3创建数据库和基本表本节介绍使用企业管理器创建数据库。22使用企业管理器创建数据库(电脑操作演示，建立student数据库)如下图4.1.3创建数据库和基本表2324每个数据库最多可创建200万个基本表，用户创建基本表时，最多可以定义1024个字段。有两种方法创建基本表：a.使用Transact-SQL创建基本表；b.使用企业管理器创建基本表。2.创建基本表

本节介绍使用企业管理器创建基本表。4.1.3创建数据库和基本表25创建基本表的主要工作：确定基本表名；确定每个字段的名称、数据类型、字段宽度、是否可为空等；确定每个表的主键。(1)数据类型常用数据类型见下表所示。4.1.3创建数据库和基本表26数据类型说明数据类型说明Bit二进制位类型Money货币类型Tinyint无符号单字节整型Datetime日期/时间型Smallint短整型,2个字节ImageOLE对象Int长整型,4个字节Varchar(n)最长为n的可变长文本类型Bigint大整型,8个字节Text(n)长为n的可变长文本类型,长度最大231-1Decimal,Numeric数值类型(固定精度和小数位)Char(n),固定长度为n的文本类型Real单精度浮点型,4字节Binary二进制类型float双精度浮点型,8字节VarBinary可变长二进制类型表4.1.2常用的数据类型27(2)在企业管理器中创建基本表选定数据库，在其下“表”项上打开快捷菜单(如下图),选择“新建表”菜单命令，进入其编辑器。如图所示.4.1.3创建数据库和基本表28三、创建基本表294.1.4管理数据库和基本表1.删除数据库和基本表选择要删除的数据库或表，按键盘上的“删除”键，或打开快捷菜单，选择“删除”菜单命令即可。（电脑操作演示）2.修改数据库和基本表结构（电脑操作演示）见下页图。304.1.4管理数据库和基本表“设计表”项是修改表结构“打开表”项是浏览表数据313.备份和恢复数据库(1)备份数据库：为数据库建立备份，以便数据库发生故障后，用备份恢复数据库。选择要备份的数据库，然后，方法1：打开快捷菜单，选择“所有任务”菜单项，再选择“备份数据库”菜单命令。方法2：选择“工具/备份数据库”菜单命令。(2)恢复数据库：（电脑操作演示）4.1.4管理数据库和基本表324.1.4管理数据库和基本表334.导入和导出数据数据的导入和导出指将其他SQLServer服务器上数据库或其他数据源数据(如Excel工作表)导入到本地SQLServer服务器上的数据库中,或反之。数据的导入和导出利用SQLServer提供的数据转换服务DTS来完成。

例如：将教学数据库数据导出到Excel表格中(电脑操作演示)。4.1.4管理数据库和基本表344.1.4管理数据库和基本表354.1.4管理数据库和基本表364.1.4管理数据库和基本表374.1.4管理数据库和基本表384.2SQL语言SQL是StructuredQueryLanguage的缩写,它是1974年由Boyce和Chamberlin提出的，并在IBM公司SanJose实验室研制的关系数据库实验系统SYSTEMR上实现。由于SQL功能丰富、语言简洁、易学易用，很受用户欢迎。为此，80年代，计算机商家们纷纷推出各自支持SQL的软件或者与SQL的接口软件。现在SQL已是世界上最广泛使用的数据库语言。394.2SQL语言SQL语言由三部分组成，包括数据定义语言DDL、数据操作语言DML、数据控制语言DCL。也就是SQL语言具有数据查询、数据操纵、数据定义和数据控制功能。其中最重要的是数据查询功能。SQL语言是一种非过程化的语言。用SQL语句解决一个问题时，用户只需要告诉系统要干什么就可以了，实现过程是由系统自动完成的。另外，SQL语言还具有面向集合操作，语法简单，易学易用等特点。40分类命令动词功能数据查询SELECT查询数据数据定义CREATE建立基本表、视图等DROP建立基本表、视图等ALTER修改基本表、视图等数据操纵INSERT插入记录UPDATE修改记录DELETE删除记录数据控制GRANT授权REVOKE回收权限表4.2.1常用的SQL命令4.2SQL语言414.2.1数据定义SQL的数据定义功能主要包括三部分：基本表的定义、视图的定义和索引的定义。421.CREATETABLE语句—表定义CREATETABLE

表名(列名1数据类型[列级完整性约束条件][，列名2数据类型[列级完整性约束条件]]…[，表级完整性约束条件])定义表时，要给出<表名>，构成表的每个字段名及其数据类型和长度。主码、参照完整性定义、用户定义完整性。4.2.1数据定义43例：建立学生教学数据库的3个表。S(Sno,Sname,Ssex,Sage,Sdept)SC(Sno,Cno,Grade)（仅以此表举例）C(Cno,Cname,Pcno,Credit)4.2.1数据定义44CREATETABLESC(SnoCHAR(6)NOTNULL,

CnoCHAR(6)NOTNULL,

GradeSmallintCHECK(GradeBETWEEN0AND100),

CONSTRAINTpkPRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESS(Sno),FOREIGNKEY(Cno)REFERENCESC(Cno))4.2.1数据定义45说明：(1)字段级完整性约束①NOTNULL(不允许取空值)和NULL(允许取空值)②PRIMARYKEY(声明字段为主键)③UNIQUE(唯一性约束,字段取值不能重复)④FOREIGNKEY:参照完整性的外键约束。⑤DEFAULT<值>：为字段指定默认值。⑥CHECK子句：实现字段级用户定义约束。4.2.1数据定义46(2)表级完整性约束①PRIMARYKEY(声明字段为主键)②FOREIGNKEY:参照完整性的外键约束。③CHECK子句：实现用户定义完整性约束。4.2.1数据定义47CREATETABLESC(SnoCHAR(6)NOTNULLFOREIGNKEYREFERENCESS(Sno),CnoCHAR(6)NOTNULL,GradeSmallintdefault0,CONSTRAINTk1CHECK(GradeBETWEEN0AND100),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Cno)REFERENCESC(Cno))注：完整性约束可以使用CONSTRAINT关键字命名。否则系统将自动为每个约束命名。4.2.1数据定义482.ALTERTABLE语句—表的修改ALTERTABLE表名ADD新列名数据类型完整性约束DROP完整性约束名DROPCOLUMN列名ALTERCOLUMN列名数据类型新增的属性不能定义为“NOTNULL”因为关系的原来元组在新列上的值取空值。4.2.1数据定义49例1：向”学生”表增加“入学时间”字段，其数据类型为日期型；将“出生日期”的数据类型改为“日期型”；删除“所属系”字段。ALTERTABLE学生ADD入学时间DatetimeALTERTABLE学生ALTERCOLUMN出生日期DatetimeALTERTABLE学生DROPCOLUMN所属系4.2.1数据定义503.DROPTABLE语句—删除基本表当某个表不再需要时，可以删除。一般格式：DROPTABLE表名例：DROPTABLES

4.2.1数据定义514.建立索引当数据表中记录很多时，查询速度成为一个突出的问题。若要提高查询速度，可以在涉及的字段上建立索引。建立索引使用CREATE命令。语法格式为：CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名><列名1><次序1>[,…<列名n><次序n>]4.2.1数据定义52说明：①若查询中涉及到索引字段时，系统会自动选择合适的索引，大大提高查询速度。②索引一经建立，就由系统使用和维护它，不需要用户干预。当增加、修改、删除数据表中记录时，系统会自动维护索引。③维护索引需要花费一些时间，故建立多少索引，需要权衡后处理。④UNIQUE：每一个索引值只对应惟一的数据记录。4.2.1数据定义53⑤CLUSTER：建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。在一个表上只能建立聚簇索引，因此应在最常查询的列上建立聚簇索引以提高查询效率。⑥次序是指ASC或DESC，默认值为ASC。5．删除索引

DROPINDEX<索引名>4.2.1数据定义54说明：1．一个SELECT语句可由五个子句组成，但只有前两个子句是必不可少的，其它子句可以缺省。4.2.2SQL查询SELECT

[ALL|DISTINCT]目标列表FROM

表名1[，表名2]…[

WHERE

条件表达式1][

GROUPBY

列名1[HAVING

条件表达式2]][ORDERBY

列名2[ASC|DESC]]；552．SELECT子句告诉系统查找什么数据。所要的数据可以是表的属性、表达式或常量。3．FROM子句告诉系统从哪里查找数据。即给出查询涉及的一个或多个表(基本表,视图,查询表)。4．WHERE子句给出查询条件。只有满足条件的元组才是查询所要的。4.2.2SQL查询565．GROUPBY子句用于分组查询，它将结果按<列名1>的值进行分组，每组的元组在该属性列上值相等。

HAVING短语用来指定组应该满足的条件，只有满足条件的组才予输出。每组的数据作为查询结果的一行，有多少组，查询结果表就有多少行。6．如果有ORDER子句，则结果表还要按<列名2>的值的升序或降序排序。4.2.2SQL查询57以教学数据库为例介绍该命令的应用。学生(学号,姓名,性别,出生日期,班级,所属系,…)课程(课号,课名,学分,学时,开课系)选课(学号,课号,成绩,…)4.2.2SQL查询581.选择记录例1

查询年龄为空值的学生信息。例2查询在1978~1983年之间出生的学生信息(不在…之间)SELECT*FROM学生WHEREYEAR(出生日期)BETWEEN1978AND1982SELECT*FROM学生WHEREYEAR(GETDATE())-YEAR(出生日期)ISNULLGetdate():系统函数，返回系统当前日期。YEAR(日期)：系统函数，返回“日期”中的年份。592.选择字段例3查询选修了001课且有成绩的学生学号。SELECT学号FROM选课WHERE课号=‘001’And成绩ISNOTNULL例4查询有不及格成绩的学生学号。SELECTDISTINCT学号FROM选课WHERE成绩<6060例5查询数学系、计算机系和信息系学生的信息。SELECT*FROM学生WHERE所属系IN('数学系','计算机系','信息系')例6查询不是数学系、计算机系和信息系学生的信息。SELECT*FROM学生WHERE所属系NOTIN('数学系','计算机系','信息系')2.选择字段613.排序例7查询年龄大于20岁的学生信息，并将结果按学号排序显示。SELECT*FROM学生WHEREYEAR(GETDATE())-YEAR(出生日期)>20

ORDERBY

学号

例8查询学生选课信息，将结果按学号升序排列，学号相同时再按照成绩降序排列显示。SELECT*FROM选课ORDERBY学号,成绩DESC62合计函数SELECT子句中的目标列可以是合计函数，用于数据统计工作，主要有：

COUNT（*）

计算元组个数

COUNT（DISTINCT|ALL<列名>）

计算一列中值的个数

SUM（DISTINCT|ALL<列名>）计算一列值的总和

AVG（DISTINCT|ALL<列名>）计算一列值的平均值

MAX（<列名>）

求一列值中的最大值

MIN（<列名>）

求一列值中的最小值

4.分组63例9查询选修了课程的学生人数。

SELECTCOUNT(DISTINCT学号)FROM选课例10查询选002课的学生人数以及该课的最高、最低成绩、平均成绩。SELECTCOUNT(*)AS人数,MAX(成绩)AS最高成绩,MIN(成绩)AS最低成绩,AVG(成绩)AS平均成绩FROM选课WHERE课号='0002'4.分组64实际中我们常常会遇到这样的查询要求，如“查询每个部门的工资总和、平均工资和职工总数”等等。对这类查询，我们可以使用GROUPBY分组子句，按“部门”分组，同一部门的职工在一组，求出各部门的统计值。例11计算各系年龄大于22岁的学生的人数。SELECT所属系,COUNT(*)AS人数

FROM学生WHEREYEAR(GETDATE())-YEAR(出生日期)>=22GROUPBY所属系4.分组65例12找出学生人数不足10人的系及其相应的总人数。SELECT所属系，COUNT(*)AS人数FROM学生GROUPBY所属系HAVINGCOUNT(*)<10例13查询选修了3门以上课程的学生学号。SELECT学号FROM选课GROUPBY学号HAVINGCOUNT(*)>=34.分组66当查询涉及两个或两个以上的表时，要用连接查询。连接分为内连接和外连接。5.连接查询例14查询选修0002课成绩为90分以上的学生的学号和姓名。SELECT学生.学号,姓名FROM学生WHERE学生.学号=选课.学号AND

选课.课号='0002‘AND成绩>=9067例15查询至少选修了0001、0002两门课的学生学号。SELECTSC1.学号FROM选课ASSC1,选课ASSC2WHERESC1.学号=SC2.学号ANDSC1.课号='0001'ANDSC2.课号='0002'注意：自身连接时，要为表起别名。AS可省写。5.连接查询684.2.3数据更新语句SQL中数据更新包括插入数据、修改数据和删除数据三条语句。与查询语句SELECT不同，数据更新语句一次只能对一个表中数据进行更新操作。691.插入数据（INSERT）有两种格式：格式1：INSERTINTO表名[(字段1，字段2…)]VALUES(常量1，常量2…)

格式2：INSERTINTO表名[(字段1，字段2…)]子查询4.2.3数据更新语句70例16将‘计00-2’班所有学生选修“0001”课信息插入选课表。

(对于必修课，一个班级的全部学生都选，可成批插入选课记录)INSERTINTO选课(学号,课号)SELECT学生.学号,'0001'FROM学生WHERE班级='计00-2'