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

1、SQL Server数据库教程从基础到应用,2015/09/05,学习目标,掌握数据库相关基础知识 掌握简单的T-SQL语句,第0章 数据库基础,什么是数据库 数据库是指长期储存在计算机内的、有组织的、可共享的数据集合。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。 数据库由数据库管理系统（DBMS）统一管理，数据的插入、修改和检索均要通过数据库管理系统进行。数据管理员（DBA）负责创建、监控和维护整个数据库。,数据库模型,层次模型 网状模型 关系模型 关系模型是目前最为流行的一种数据模型，用二维表格结构表示实体集。,数据库对

2、象,表： 由行和列组成 每列又称为一个字段，每列的标题称为字段名 一行数据称为一个或一条记录 一个数据库表由一条或多条记录组成，没有记录的表称为空表。 每个表中通常都有一个主关键字（也叫主键），用于唯一地确定一条记录。主键不允许空值。不能存在具有相同的主键值的两个行,字段名,记录,主键,概念数据模型与逻辑数据模型转换,E-R图 同 关系模式转换 1：1的联系 转换为关系模式： bjb(班级编号，院系，专业，人数) bzb(学号，姓名) syb(学号，班级编号) 注意：联系关系模式的属性包括 包括联系属性，以及各实体的 主码；主码选实体集任意一方 主码。,1,1,联系,实体,属性,概念数据模型与

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

4、Studio。 （2）使用“Windows身份验证”连接到SQL Server 2012数据库实例。 （3）展开SQL Server 实例，右击“数据库”，然后在弹出的快捷菜单中选择“新建数据库存”命令，打开“新建数据库”对话框。 （4）在“新建数据库”对话框中，可以定义数据库的名称、数据文件和日志文件的逻辑名称和路径、文件组、初始大小和增长方式等。输入数据库名称（pxscj）student。P(29)图,使用SQL语句创建数据库,2、使用T-SQL 语句创建数据库。（命令方式） 在SQL Server Management Studio中，单击标准工具栏的“新建查询”按钮，启动SQL编辑器窗

5、口，在光标处输入T-SQL语句，单击“执行”按钮。SQL编辑器就提交用户输入的T-SQL语句，然后发送到服务器执行，并返回执行结果。P(35)页 例1：创建一个只含一个数据文件和一个事务日志文件的数据库,create database student1 on (name=student1_dat, filename=d:student_dat.mdf, size=10, maxsize=50, filegrowth=20%) log on (name=student1_log, filename=d:student_log.ldf, size=4, maxsize=25, filegrowth

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

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

8、ALTER DATABASE 修改数据库 ADD FILE(FILEGROUP) 增加文件(文件组) MODIFY FILE 更改文件属性 REMOVE FILE 文件名 删除数据文件 DROP DATABASE 删除数据库,使用SQL语句修改、删除数据库,课本例子：P38-40页 例2-8 例2-10 例2-11 例2-12 例2-13,练习： 1、修改数据库pxscj的属性，将日志文件的初始大小改为 15MB，增长方式改为15%。 2、为数据库pxscj添加文件组pgroup，并为此文件组添加 一个数据文件，大小为25MB，存储路径为“D：data”,表的创建和操作,表结构：,表创建和操作

9、,界面方式：,表创建和操作,界面方式：,表创建和操作,命令方式： 用SQL语句创建如下两个基本表：学生表(Student)、课程表（Course），结构如下所示 Student表,表创建和操作,Create table Student ( Sno char(7) primary key, Sname char(10) not null, Ssex char (2) check(Ssex=男or Ssex=女), Sage int check(Sage between 15 and 45), Sdept varchar(20) default(计算机系) ),表创建和操作,Course表结构,表

10、创建和操作,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(Period0)， ),表创建和操作,修改表结构： P57页 例3-3 例3-4 P59页图 操作表数据（界面方式）,表创建和操作,命令方式操作表数据： 向表xsb中插入记录 INSERT INTO xsb VALUERS(201502,王海，1，19

11、990501，电子商务，50，NULL) (201504,李华，1，19990604，电子商务，NULL，NULL) INSERT INTO xsb（学号，姓名，性别，专业） VALUERS(201502,王海，1，电子商务),表创建和操作,在xsb1中生成计算机专业学生的xsb2。 1）INSERT INTO xsb2 SELECT * FROM xsb1 WHERE 专业=计算机 2）SELECT * INTO xsb2 FROM xsb1 WHERE 专业=计算机,表创建和操作,练习：1、由cjb生成成绩表大于90分的cjb1 1）INSERT INTO cjb1 SELECT * FR

12、OM cjb WHERE 成绩=90分 2）SELECT * INTO cjb1 FROM cjb WHERE 成绩=90分,表创建和操作,删除符合条件记录： DELETE FROM xsb WHERE 总学分=0 GO,数据库的查询,常用术语,关键码,超键 在关系模式中，能唯一标识元组的属性集称为超键（Super Key） 候选键 如果一个属性集能唯一标识元组，且又不含有多于属性，那么这个属性集被称为候选键（Candidate Key） 主键 关系模式中用户正在使用的候选键称之为主键（Primary Key） 外键 如果关系模式R中某属性集是其他模式的候选键，那么该属性对模型R而言是外键,数

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

14、L 程序语句，经编译后存储在数据库中的SQL 程序。,SQL Server 关系数据库简介,SQL Server是由Microsoft开发和推广的关系数据库管理系统,最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，并于1988年推出了第一个OS/2版本。SQL Server近年来不断更新版本： 1996年，Microsoft 推出了SQL Server 6.5版本； 1998年，SQL Server 7.0版本和用户见面； 2000年，SQL Server 2000推出； 2005年，微软发布了SQL Server 2005 2008年，微软发布了最新的SQ

15、L Server 2008,SQL Server 常见版本,企业版（Enterprise Edition） 标准版（Standard Edition） 个人版（Personal Edition） 开发者版（Developer Edition）,安装、运行 SQL Server 2000 的软件需求,SQL Server 2000企业版必须运行于安装Windows NT Server Enterprise Edition 4.0或者Windows2000 Advanced Server以及更高版本的操作系统下。 SQL Server 2000标准版必须运行于安装Windows NT Server

16、 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 Server安装,SQL Server 系统数据库简介,Master数据库是SQL Server系统最重要的数据库，它记录了SQL Server系统的所有

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

18、rver 的主系统表sysobjects出现在每个数据库中，它对每个数据库对象含有一行记录。 2. syscolumns表 系统表syscolumns出现在master数据库和每个用户自定义的数据库中，它对基表或者视图的每个列和存储过程中的每个参数含有一行记录。 3. sysindexes表 系统表sysindexes出现在master数据库和每个用户自定义的数据库中，它对每个索引和没有聚簇索引的每个表含有一行记录，它还对包括文本/图像数据的每个表含有一行记录。,SQL Server 系统表简介,4. sysusers表 系统表sysusers出现在master数据库和每个用户自定义的数据库中

19、，它对整个数据库中的每个Windows NT用户、Windows NT用户组、SQL Server 用户或者SQL Server 角色含有一行记录。 5. sysdatabases表 系统表sysdatabases对SQL Server 系统上的每个系统数据库和用户自定义的数据库含有一行记录，它只出现在master数据库中。 6. sysdepends表 系统表Sysdepends对表、视图和存储过程之间的每个依赖关系含有一行记录，它出现在master数据库和每个用户自定义的数据库中。 7. sysconstraints表 系统表sysconstraints对使用CREATE TABLE或者A

20、LTER TABLE语句为数据库对象定义的每个完整性约束含有一行记录，它出现在master数据库和每个用户自定义的数据库中。,SQL Server 工具和实用程序,企业管理器 服务管理器 查询分析器 事件探查器 导入和导出数据 联机帮助文档,企业管理器（Enterprise Manager）,服务管理器（ Service Manager Manager）,查询分析器（Query Analyzer）,事件探查器,导入和导出数据,联机帮助文档,注册一个新的数据库,链接服务器,备份数据库,还原数据库,分离数据库,附加数据库,数据库维护计划,生成数据库结构脚本,标准SQL语言,SQL（Structur

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

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

23、L语句和流程控制语句的预编译集合，是封装重复任务操作的方法。,SQLServer的对象命名约定,三段式名字标识对象： . 前两者可以省略，数据库名默认是当前数据库，所有者默认式数据库的所有者dbo，如： Pubs.dbo.authors Northwind.dbo.sc Northwind.dbo.sc.grade,创建练习数据库和表,使用企业管理器添加数据库,创建练习数据库和表,使用企业管理器分别添加表（c，s，sc）,创建练习数据库和表,使用企业管理器添加各个表的记录（c，s，sc）,Select 语句,查询课程表，查询学生表 查询课程表，只显示课程名和教师名,别名AS,使用别名是增加se

24、lect语句的可读性，语法是： 数据表名 as 数据表别名 如： Select e.员工编号，e.员工姓名 from 员工表 as e Select ame, p.teacher from c as p,Union,将两个或两个以上的查询结果合并为一个结果集,语法： Select 语句Union all Select 语句 默认情况下，union将从最终结果集中删除重复的记录；如果希望保留所有记录，使用all关键字,条件查询,Select * from 表名 where 查询条件 例如：Select * from s where sname=wang,模糊查询,Like关键字的提供使我们的模糊

25、查询有了基础，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# 查询参加了缺陷管理课程考试的同学： Select sname from s,sc,c where s.s

26、#=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 where c#= (select c# from c where cnam

27、e=缺陷管理),联接查询 vs. 嵌套查询,Group by,Select sex from s group by sex Select count(*),sex from s group by sex Select sname,sum(grade),count(*) from s,sc where s.s#=sc.s# group by sname,Having,帮助我们对需要分组的数据做一个过滤，例如： Select sname,sum(grade),count(*) from s,sc where s.s#=sc.s# group by sname having snamechen 这样

28、我们就可以把chen的记录过滤掉,Order by,ORDER BY是一个可选的子句，它允许你根据指定要order by的列来以上升或者下降的顺序来显示查询的结果。例如： ASC = Ascending Order 这个是缺省的DESC = Descending Order Select * from sc order by grade desc Select top 3 * from sc order by grade desc,Join,内联接（INNER JOIN） 左联接（LEFT JOIN） 右联接（RIGHT JOIN） 全联接（FULL JOIN）,内联接（INNER JOIN）

29、,内联接会返回两个表中所匹配的所有的行。假如 Employees 中的某些行与 Orders 中的行不匹配，那些行就不会被列出来。,左联接（LEFT JOIN）,左联接会从第一个表 (Employees) 那里返回所有的行，即使在第二个表 (Orders) 中没有匹配的行。假如 Employees 中的某些行没有在 Orders 中可匹配的行，这些行也会被列出来。,右联接（RIGHT JOIN）,右联接会从第二个表格 (Orders) 那里返回所有的行，即使在第一个表格中没有可匹配的行。假如 Orders 中的某些行没有在 Employees 可匹配的行，那些行也会被列出。,全联接（FULL

30、JOIN）,全联接会从两个表格 (Orders) 那里返回所有的行，即使在第一个表格或第二个中没有可匹配的行。,Insert语句,INSERT INTO s (s#, sname, age, sex) VALUES (S101, jiang, 39, M) Insert into student(s#,sname,age,sex) select s#,sname,age,sex from s,Delete语句,DELETE FROM student delete from student where sname = jiang,Update 语句,UPDATE student SET sname = chennengji WHERE sname = chen and age = 22 UPDATE student SET age=age+1 WHERE sname = chennengji,事务,事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。 事务操作的语法： BEGIN TRANSACTIONCOMMIT TRANSACTIO