SQLServer数据库基础课程(总复习)

1、.SQL Server数据库教数据库教程从基础到应用程从基础到应用2015/09/05.学习目标学习目标掌握数据库相关基础知识掌握数据库相关基础知识掌握简单的掌握简单的T-SQLT-SQL语句语句.第第0 0章章 数据库基础数据库基础什么是数据库什么是数据库 数据库是指长期储存在计算机内的、有组织的、数据库是指长期储存在计算机内的、有组织的、可共享的可共享的数据集合数据集合。数据库中的数据按一定的数。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户较高的数据独立性和易扩展性，并可为各种用户共享

2、。共享。 数据库由数据库由数据库管理系统（数据库管理系统（DBMSDBMS）统一管理，数据的插统一管理，数据的插入、修改和检索均要通过数据库管理系统进行。数据管入、修改和检索均要通过数据库管理系统进行。数据管理员（理员（DBADBA）负责创建、监控和维护整个数据库。）负责创建、监控和维护整个数据库。.数据库模型数据库模型层次层次模型模型网状网状模型模型关系关系模型模型 关系模型是目前最为流行的一种数据模型，关系模型是目前最为流行的一种数据模型，用用二维表格二维表格结构表示实体集。结构表示实体集。.数据库对象数据库对象表：表： 由由行行和和列列组成组成 每列又称为一个每列又称为一个字段字段，每列

3、的标，每列的标题称为字段名题称为字段名 一行数据称为一个或一条一行数据称为一个或一条记录记录 一个数据库表由一条或多条记录一个数据库表由一条或多条记录组成，没有记录的表称为组成，没有记录的表称为空表。空表。 每个表中通常都有一个主关键字每个表中通常都有一个主关键字（也叫主键），用于唯一地确定（也叫主键），用于唯一地确定一条记录。主键不允许空值。不一条记录。主键不允许空值。不能存在具有相同的主键值的两个能存在具有相同的主键值的两个行行字段名字段名记录记录主键主键.概念数据模型与逻辑数据模型转换概念数据模型与逻辑数据模型转换E-R图 同 关系模式转换1：1的联系 转换为关系模式：转换为关系模式：

4、bjb(班级编号班级编号，院系，专业，人，院系，专业，人数数) bzb(学号学号，姓名，姓名) syb(学号学号，班级编号，班级编号) 注意：联系关系模式的属性包注意：联系关系模式的属性包括括 包括联系属性，以及各实体的包括联系属性，以及各实体的 主码；主码选实体集任意一方主码；主码选实体集任意一方 主码。主码。 11联系联系实体实体属性属性.概念数据模型与逻辑数据数据模型转换概念数据模型与逻辑数据数据模型转换1：n联系 E-R图 转换为转换为关系模式：关系模式： bjb(班级编号班级编号，专业，人数，专业，人数) xsb(学号学号，姓名，性别，专业，姓名，性别，专业) syb(学号学号，班级

5、编号，班级编号)n1.概念数据模型逻辑数据数据模型转换概念数据模型逻辑数据数据模型转换 n：n联系联系 E-R图图 转换为转换为关系模式：关系模式： xsb(学号学号，姓名，性别，专业，姓名，性别，专业) kcb(课程编号课程编号，课名，学期，课名，学期，学时，学分学时，学分 学分，学时学分，学时) cjb(学号，课程编号，学号，课程编号，成绩成绩) 11nn成绩.使用使用SQL语句创建数据库语句创建数据库一、创建数据库一、创建数据库1 1、利用对象资源管理器创建用户数据库：（界面方式）、利用对象资源管理器创建用户数据库：（界面方式）（1 1）选择）选择“开始开始”“”“程序程序”Micros

6、oft SQL Server ”Microsoft SQL Server 20201212，打开，打开SQL Server Management StudioSQL Server Management Studio。（2 2）使用）使用“Windows“Windows身份验证身份验证”连接到连接到SQL Server 20SQL Server 201212数数据库实例。据库实例。（3 3）展开）展开SQL Server SQL Server 实例，右击实例，右击“数据库数据库”，然后在弹出，然后在弹出的快捷菜单中选择的快捷菜单中选择“新建数据库存新建数据库存”命令，打开命令，打开“新建数据新建

7、数据库库”对话框。对话框。（4 4）在）在“新建数据库新建数据库”对话框中，可以定义数据库的名称、对话框中，可以定义数据库的名称、数据文件和日志文件的逻辑名称和路径、文件组、初始大小数据文件和日志文件的逻辑名称和路径、文件组、初始大小和增长方式等。输入数据库名称（和增长方式等。输入数据库名称（pxscjpxscj）studentstudent。P(29)P(29)图图.使用使用SQL语句创建数据库语句创建数据库2 2、使用、使用T-SQL T-SQL 语句创建数据库。（命令方式）语句创建数据库。（命令方式）在在SQL Server Management StudioSQL Server Man

8、agement Studio中，单击标准中，单击标准工具栏的工具栏的“新建查询新建查询”按钮，启动按钮，启动SQLSQL编辑器窗口，编辑器窗口，在光标处输入在光标处输入T-SQLT-SQL语句，单击语句，单击“执行执行”按钮。按钮。SQLSQL编辑器就提交用户输入的编辑器就提交用户输入的T-SQLT-SQL语句，然后发语句，然后发送到服务器执行，并返回执行结果。送到服务器执行，并返回执行结果。P(35)P(35)页页例例1 1：创建一个只含一个数据文件和一个事务日志：创建一个只含一个数据文件和一个事务日志文件的数据库文件的数据库.createcreate database student1 d

9、atabase student1on on (name=student1_dat,(name=student1_dat,d:student_datd:student_dat.mdf.mdf, ,size=10,size=10,maxsize=50,maxsize=50,=20%)=20%)log onlog on(name=student1_log,(name=student1_log,d:student_log.d:student_log.ldfldf, ,size=4,size=4,maxsize=25,maxsize=25,=5)=5) 回顾课本回顾课本3636页例页例2-72-7.创建

10、数据库创建数据库练习：练习：1、写出创建产品销售数据库、写出创建产品销售数据库cpxs，初始大小为，初始大小为10MB，最大为，最大为50MB，增长方式为，增长方式为20%；日志文；日志文件名初始为件名初始为4MB，最大为，最大为25MB，增长方式为，增长方式为5MB，存储路径为，存储路径为“D：SQLServer2012data”。.create database cpxson (name=cpxs_dat,D：SQL Server2012cpxs_dat.mdf,size=10MB,maxsize=50MB,)log on(name=cpxs_log,D：SQL Server2012cpx

11、s_log.ldf,size=4MB,maxsize=25MB,) .数据库修改、删除数据库修改、删除对数据库可修改以下几项：对数据库可修改以下几项： 增加或删除数据文件增加或删除数据文件 增加或删除日志文件增加或删除日志文件 增加或删除文件组增加或删除文件组 改变数据文件的大小和增长方式改变数据文件的大小和增长方式 改变日志文件的大小和增长方式改变日志文件的大小和增长方式 重命名数据库重命名数据库.数据库修改、删除（界面方式）数据库修改、删除（界面方式）“对象资源管理器对象资源管理器”窗口窗口点击所要修改的数据库点击所要修改的数据库右击右击鼠标选择鼠标选择“属性属性”，打开，打开“数据库属性

12、数据库属性”窗口窗口1 1、修改文件属性、修改文件属性.数据库修改、删除（界面方式）数据库修改、删除（界面方式）.数据库修改、删除（界面方式）数据库修改、删除（界面方式）2 2、增加辅助数据文件、增加辅助数据文件.数据库修改、删除（界面方式）数据库修改、删除（界面方式）3 3、增加文件组（输入文件组名称）、增加文件组（输入文件组名称）.使用使用SQLSQL语句修改、删除数据库语句修改、删除数据库命令方式命令方式ALTER DATABASE 修改数据库修改数据库ADD ) 增加文件增加文件(文件组文件组)MODIFY FILE 更改文件属性更改文件属性REMOVE FILE 文件名文件名 删除数

13、据文件删除数据文件DROP DATABASE 删除数据库删除数据库.使用使用SQL语句修改、删除数据库语句修改、删除数据库课本例子：课本例子：P38-40页页例例2-8例例2-10例例2-11例例2-12例例2-13.练习：练习：1 1、修改数据库、修改数据库pxscjpxscj的属性，将日志文件的初始大小改的属性，将日志文件的初始大小改为为15MB15MB，增长方式改为，增长方式改为15%15%。2 2、为数据库、为数据库pxscjpxscj添加文件组添加文件组pgrouppgroup，并为此文件组添，并为此文件组添加加一个数据文件，大小为一个数据文件，大小为25MB25MB，存储路径为存储

14、路径为“D“D：data”data”.表的创建和操作表的创建和操作表结构：表结构：.表创建和操作表创建和操作界面方式：界面方式：.表创建和操作表创建和操作界面方式：界面方式：.表创建和操作表创建和操作命令方式：命令方式：用用SQL语句创建如下两个基本表：学生表语句创建如下两个基本表：学生表(Student)、课程表、课程表（Course），结构如下所示），结构如下所示StudentStudent表表.表创建和操作表创建和操作Create table Student(Sno char(7) primary key,Sname char(10) not null,Ssex char (2) che

15、ck(Ssex=男男or Ssex=女女),Sage int check(Sage between 15 and 45),Sdept varchar(20) default(计算机系计算机系).表创建和操作表创建和操作 Course表结构表结构.表创建和操作表创建和操作Create table course(Cno char(10) primary key,Cname varchar(20) not null,Ccredit int check(Scteditbetween 0 and 50),Semester int check(Semester0),Period int check(Per

16、iod0)，).表创建和操作表创建和操作修改表结构：修改表结构：P57页页 例例3-3 例例3-4P59页图页图 操作表数据（界面方式）操作表数据（界面方式）.表创建和操作表创建和操作命令方式操作表数据：命令方式操作表数据：向表向表xsb中插入记录中插入记录INSERT INTO xsb VALUERS(201502,王海王海，1，19990501，电子商务电子商务，50，NULL)(201504,李华李华，1，19990604，电子商务电子商务，NULL，NULL)INSERT INTO xsb（学号，姓名，性别，专业）（学号，姓名，性别，专业）VALUERS(201502,王海王海，1，电

17、子商务电子商务).表创建和操作表创建和操作在在xsb1xsb1中生成计算机专业学生的中生成计算机专业学生的xsb2xsb2。1 1）INSERT INTO xsb2INSERT INTO xsb2 SELECT SELECT * * FROM xsb1 FROM xsb1 WHERE WHERE 专业专业= =计算机计算机2 2）SELECT SELECT * * INTO xsb2 INTO xsb2 FROM xsb1 FROM xsb1 WHERE WHERE 专业专业= =计算机计算机.表创建和操作表创建和操作练习：练习：1 1、由、由cjbcjb生成成绩表大于生成成绩表大于9090分

18、的分的cjb1cjb11 1）INSERT INTO cjb1INSERT INTO cjb1 SELECT SELECT * * FROM cjb FROM cjb WHERE WHERE 成绩成绩=90=90分分2 2）SELECT SELECT * * INTO cjb1 INTO cjb1 FROM cjb FROM cjb WHERE WHERE 成绩成绩=90=90分分.表创建和操作表创建和操作删除符合条件记录：删除符合条件记录：DELETEDELETE FROM xsb FROM xsb WHERE WHERE 总学分总学分=0=0 GO GO.数据库的查询数据库的查询.常用术语

19、常用术语.关键码关键码超键 在关系模式中，能唯一标识元组的属性集称为超键（Super Key）候选键 如果一个属性集能唯一标识元组，且又不含有多于属性，那么这个属性集被称为候选键（Candidate Key）主键 关系模式中用户正在使用的候选键称之为主键（Primary Key）外键 如果关系模式R中某属性集是其他模式的候选键，那么该属性对模型R而言是外键.FK_银行卡信息表_REFERENCE_身份证信息表银行卡信息表卡号姓名性别身份证号有效期发卡行卡的类型联系方式金额身份证信息表身份证号姓名性别出生日期籍贯民族家庭地址有效期发卡所照片.数据库对象数据库对象外键：存在于B表中的A表的主键.数

20、据库对象数据库对象索引（Index） 索引是根据指定的数据库表列建立起来的顺序。它提供了快速访问数据的途径，并且可监督表的数据，使其索引所指向的列中的数据不重复。视图（View） 是一个虚拟的表，在数据库中并不实际存。 视图是由查询数据库表产生的，视图可以用来控制用户对数据的访问，并能简化数据的显示，即通过视图只显示那些需要的数据信息。 触发器（Trigger） 触发器是一个用户定义的SQL事务命令的集合。当对一个表进行插入、更改、删除时，这组命令就会自动执行。存储过程（Stored Procedure） 存储过程是为完成特定的功能而汇集在一起的一组SQL 程序语句，经编译后存储在数据库中的S

21、QL 程序。 .SQL Server SQL Server 关系数据库简介关系数据库简介 SQL Server是由Microsoft开发和推广的关系数据库管理系统,最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，并于1988年推出了第一个OS/2版本。SQL Server近年来不断更新版本： 19961996年，Microsoft 推出了SQL Server 6.5版本； 19981998年，SQL Server 7.0版本和用户见面； 20002000年，SQL Server 2000推出； 20052005年，微软发布了SQL Server 2005 2

22、0082008年，微软发布了最新的SQL Server 2008.SQL Server SQL Server 常见版本常见版本企业版（Enterprise Edition）标准版（Standard Edition）个人版（Personal Edition）开发者版（Developer Edition）.安装、运行安装、运行 SQL Server 2000SQL Server 2000 的软件的软件需求需求SQL Server 2000企业版必须运行于安装Windows NT Server Enterprise Edition 4.0或者Windows2000 Advanced Server以及

23、更高版本的操作系统下。SQL Server 2000标准版必须运行于安装Windows NT Server Enterprise Edition 4.0、Windows NT Server 4.0、Windows 2000 Server以及更高版本的操作系统下。SQL Server 2000 个人版可在多种操作系统下运行，如可运行于Windows 9x，Windows NT 4.0或Windows 2000的服务器版或工作站版的操作系统下。SQL Server 2000 开发者版可运行于上述Windows 9x以外的所有操作系统下。.SQL ServerSQL Server安装安装.SQL S

24、erver SQL Server 系统数据库简介系统数据库简介Master数据库是SQL Server系统最重要的数据库，它记录了SQL Server系统的所有系统信息。这些系统信息包括所有的登录信息、系统设置信息、SQL Server的初始化信息和其它系统数据库及用户数据库的相关信息。Model数据库是所有用户数据库和Tempdb数据库的模板数据库，它含有Master数据库所有系统表的子集，这些系统数据库是每个用户定义数据库需要的。Msdb数据库是代理服务数据库，为其警报、任务调度和记录操作员的操作提供存储空间。Tempdb是一个临时数据库，默认大小是8M；它为所有的临时表、临时存储过程及其

25、它临时操作提供存储空间。Pubs和Northwind数据库是两个示例数据库.SQL Server SQL Server 系统表简介系统表简介1. sysobjects表SQL Server 的主系统表sysobjects出现在每个数据库中，它对每个数据库对象含有一行记录。2. syscolumns表系统表syscolumns出现在master数据库和每个用户自定义的数据库中，它对基表或者视图的每个列和存储过程中的每个参数含有一行记录。3. sysindexes表系统表sysindexes出现在master数据库和每个用户自定义的数据库中，它对每个索引和没有聚簇索引的每个表含有一行记录，它还对包

26、括文本/图像数据的每个表含有一行记录。.SQL Server SQL Server 系统表简介系统表简介4. sysusers表系统表sysusers出现在master数据库和每个用户自定义的数据库中，它对整个数据库中的每个Windows NT用户、Windows NT用户组、SQL Server 用户或者SQL Server 角色含有一行记录。5. sysdatabases表系统表sysdatabases对SQL Server 系统上的每个系统数据库和用户自定义的数据库含有一行记录，它只出现在master数据库中。6. sysdepends表系统表Sysdepends对表、视图和存储过程之间

27、的每个依赖关系含有一行记录，它出现在master数据库和每个用户自定义的数据库中。7. sysconstraints表系统表sysconstraints对使用CREATE TABLE或者ALTER TABLE语句为数据库对象定义的每个完整性约束含有一行记录，它出现在master数据库和每个用户自定义的数据库中。.SQL Server SQL Server 工具和实用程序工具和实用程序企业管理器服务管理器查询分析器事件探查器导入和导出数据联机帮助文档.企业管理器企业管理器（Enterprise Enterprise ManagerManager）.服务管理器（服务管理器（ Service Man

28、ager ManagerService Manager Manager）.查询分析器查询分析器（Query AnalyzerQuery Analyzer）.事件探查器事件探查器.导入和导出数据导入和导出数据.联机帮助文档联机帮助文档.注册一个新的数据库注册一个新的数据库.链接服务器链接服务器.备份数据库备份数据库.还原数据库还原数据库.分离数据库分离数据库.附加数据库附加数据库.数据库维护计划数据库维护计划.生成数据库结构脚本生成数据库结构脚本.标准标准SQLSQL语言语言SQL（Structured Query Language）语言最早是由Boyce和Chamberlin在1974年提出，

29、起初称之为SEQUEL(Structured English Query Language )。设计目的是为了便于对System R数据库系统存储数据进行操作。1986年ANSI批准SQL语言作为关系型数据库语言的标准，1987年ISO也通过了这一标准。目前，几乎所有的关系型数据库管理系统都支持SQL语言。 .Transact-SQLTransact-SQL语言语言数据定义语言数据定义语言（DDL，Data Definition Language）包括创建数据库表结构、创建视图和创建表的索引，以及删除已经存在的表、视图和索引等。数据定义语句的SQL动词是CREATE，DROP 和ALTER。

30、数据操纵语言数据操纵语言（DML，Data manipulation Language）主要用于创建数据库表后，查询、输入、删除和修改表中的数据。 SQL动词是SELECT ，INSERT，DELETE和UPDATE。数据控制语言数据控制语言（DCL，Data Control Language）包括数据库事务管理功能和数据保护功能，对数据库提供完整性约束控制，控制数据库的安全性；提供了多用户并发控制和恢复的功能，支持事务提交和回滚等。SQL动词是GRANT和REVOKE。系统存储过程系统存储过程（System Stored Procedure）存储过程是SQL语句和流程控制语句的预编译集合，是

31、封装重复任务操作的方法。.SQLServerSQLServer的对象命名约定的对象命名约定三段式名字标识对象：.前两者可以省略，数据库名默认是当前数据库，所有者默认式数据库的所有者dbo，如：Pubs.dbo.authorsNorthwind.dbo.scNorthwind.dbo.sc.grade.创建练习数据库和表创建练习数据库和表使用企业管理器添加数据库.创建练习数据库和表创建练习数据库和表使用企业管理器分别添加表（c，s，sc）.创建练习数据库和表创建练习数据库和表使用企业管理器添加各个表的记录（c，s，sc）.Select Select 语句语句查询课程表，查询学生表查询课程表，只显

32、示课程名和教师名.别名别名ASAS使用别名是增加select语句的可读性，语法是：数据表名 as 数据表别名如：Select e.员工编号，e.员工姓名 from 员工表 as eSelect ame, p.teacher from c as p.UnionUnion将两个或两个以上的查询结果合并为一个结果集,语法：Select 语句Union all Select 语句默认情况下，union将从最终结果集中删除重复的记录；如果希望保留所有记录，使用all关键字.条件查询条件查询Select * from 表名 where 查询条件例如：Select * from s where sname=

33、wang.模糊查询模糊查询Like关键字的提供使我们的模糊查询有了基础，Like关键字后提供了_下划线和%百分号这两种通配符。.多表查询多表查询 - -联接查询联接查询 Select * from s, sc得到一个非常大的结果集:s表和sc表的笛卡尔积 Select * from s,sc where s.s#=sc.s#Select * from s,sc,c where s.s#=sc.s# and c.c#=sc.c#Select sname,cname,grade from s,sc,c where s.s#=sc.s# and c.c#=sc.c#查询参加了缺陷管理课程考试的同学：

34、Select sname from s,sc,c where s.s#=sc.s# and c.c#=sc.c# and cname=缺陷管理.多表查询多表查询 - - 嵌套查询嵌套查询查询wang同学的考试成绩：Select * from sc where s# = (select s# from s where sname=wang)查询参加了缺陷管理课程考试的同学：Select * from sc where c# =(select c# from c where cname=缺陷管理)Select sname from s where s# in(Select s# from sc w

35、here c#=(select c# from c where cname=缺陷管理).联接查询联接查询 vs. vs. 嵌套查询嵌套查询.Group byGroup bySelect sex from s group by sexSelect count(*),sex from s group by sexSelect sname,sum(grade),count(*) from s,sc where s.s#=sc.s# group by sname.HavingHaving帮助我们对需要分组的数据做一个过滤，例如：Select sname,sum(grade),count(*) from

36、 s,sc where s.s#=sc.s# group by sname having snamechen这样我们就可以把chen的记录过滤掉.Order byOrder byORDER BY是一个可选的子句，它允许你根据指定要order by的列来以 上 升 或 者 下 降 的 顺 序 来 显 示 查 询 的 结 果 。 例 如 ： A S C = A s c e n d i n g O r d e r 这 个 是 缺 省 的DESC = Descending Order Select * from sc order by grade descSelect top 3 * from sc

37、order by grade desc.JoinJoin内 联 接 （内 联 接 （ I N N E R JOIN）左联接（左联接（LEFT JOIN）右 联 接 （右 联 接 （ R I G H T JOIN）全联接（全联接（FULL JOIN）.内联接（内联接（INNER JOININNER JOIN）内联接会返回两个表中所匹配的所有的行。假如 Employees 中的某些行与 Orders 中的行不匹配，那些行就不会被列出来。.左联接（左联接（LEFT JOINLEFT JOIN）左联接会从第一个表 (Employees) 那里返回所有的行，即使在第二个表 (Orders) 中没有匹配的

38、行。假如 Employees 中的某些行没有在 Orders 中可匹配的行，这些行也会被列出来。.右联接（右联接（RIGHT JOINRIGHT JOIN）右联接会从第二个表格 (Orders) 那里返回所有的行，即使在第一个表格中没有可匹配的行。假如 Orders 中 的 某 些 行 没 有 在 Employees 可匹配的行，那些行也会被列出。.全联接（全联接（FULL JOINFULL JOIN） 全联接会从两个表格 (Orders) 那里返回所有的行，即使在第一个表格或第二个中没有可匹配的行。 .InsertInsert语句语句INSERT INTO s (s#, sname, age

39、, sex) VALUES (S101, jiang, 39, M)Insert into student(s#,sname,age,sex) select s#,sname,age,sex from s.DeleteDelete语句语句DELETE FROM studentdelete from student where sname = jiang.Update Update 语句语句UPDATE student SET sname = chennengji WHERE sname = chen and age = 22UPDATE student SET age=age+1 WHERE sname = chennengji.事务事务事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。