全套课件-《SQL-Server-2005数据库案例教程》-严波

第一章

数据库基础课程目标了解数据库的必要性和数据库的发展了解SQLServer2005的特性了解SQLServer2005的安装方法学会使用SSMS创建登录，创建，附加，分离数据库数据库的必要性存储大量的数据可以保持数据的一致性，完整性，降低数据冗余实现应用程序的数据共享和安全利用数据库可以智能的对数据进行分析和统计数据库的发展历史

第一代数据库——层次模型和网状模型第二代数据库——关系型数据库第三代数据库——面向对象数据库常用数据库介绍DBMS：数据库管理系统RDBMS：关系型数据库管理系统AccessOracleMySQLSybaseSQLServer

数据库的基本概念

数据和信息数据是信息的符号表示，或称载体；信息是数据的内涵，是数据的语义解释

数据库基本概念实体是客观存在的事物，比如：桌子，椅子，电脑……行(记录)数据库基本概念数据库是存放数据的仓库，是存储相关数据的集合，表是由行和列组成的…

订单

客户

产品

产品数据库表数据表存储过程视图….数据库基本概念数据冗余即相同的数据存在了多个地方

编号姓名年龄民族部门1王涛33汉族人事管理部2李梅27汉族人事管理部1127李梅21133王涛1部门编码民族编码年龄姓名编号回族2汉族1民族民族编码市场营销部2人事管理部1部门部门编码存在冗余

SQLServer2005概述

易管理性可用性可伸缩性安全性

SQLServer2005版本

学习版(ExpressEdition)工作组版(WorkgroupEdition)开发版(DeveloperEdition)标准版(StandardEdition)企业版(EnterpriseEdition)移动版(MobileEdition)SQLServer2005的主要组件

关系型数据库复制服务通知服务集成服务分析服务报表服务管理工具开发工具创建登录帐户数据库组成数据文件（主要/次要）和事务日志文件数据库主数据文件(.mdf)次要数据文件(.ndf)日志文件(.ldf)数据库系统数据库modeltempdbmsdbStudentsDBFlightsDB用户数据库文件组StudentsDB的物理文件结构StudentsDB.ldfE:\日志文件…

Course

Student

StuCourse

主文件组StudentsDB.mdfC:\用户定义文件组Students1.ndfStudents2.ndfD:\sys…

sys…

sysusers

sysobjects

新建数据库分离数据库附加数据库总结使用数据库可以安全，高效的实现数据的存储和管理数据库文件包括主数据文件，次要数据文件和日志文件，每个数据库至少要包含一个数据文件和日志文件文件组是逻辑上对数据文件进行管理的一种方式通过分离数据库可以将数据库分离成物理的文件附加数据库可以将物理文件恢复成数据库第二章SQLServer数据库表管理

了解数据库中的基本概念掌握数据完整性的含义学会创建主键和外键了解分区表，临时表和系统表的特点和作用学会用SSMS工具创建表，并设置空值约束，默认值以及标识列学会创建表之间的关系学会使用导入导出工具本章目标数据完整性数据完整性是指为了保证表中数据准确又有意义数据完整性可靠性准确性=完整性数据类型是否正确？——年龄必须是数字格式是否正确？——身份证号码必须是18位是否在允许的范围内？——性别只能是”男”或者”女”是否存在重复输入？——学员信息输入了两次是否符合其他特定要求？——年龄大于18岁的学员才可以考试……实体完整性江西南昌何静S0000005黑龙江五常王莉莉S0000004江西南昌张玉佳 S0000003上海姚明 S0000002山东威海何静S0000001….地址姓名学号姓名重复，但是可以通过学号唯一的区别每个学员约束方法：索引、UNIQUE约束、PRIMARYKEY约束或IDENTITY域完整性男何静S0000005女王莉莉S0000004女张玉佳 S0000003男姚明 S0000002女何静S0000001….性别姓名学号性别只能输入“男”或“女”引用完整性江西南昌何静S0000005黑龙江五常王莉莉S0000004江西南昌张玉佳 S0000003上海姚明 S0000002山东威海何静S0000001….地址姓名学号58数据库原理S000000390C#编程 S000000367数据库原理

S000000278C#编程 S000000189数据库原理S0000001….成绩科目 学号自定义完整性37860.98HY00000522300.450.95HY0000048061HY0000033989770.85 HY00000223450.98HY000001….总金额折扣会员编号用触发器实现随着用户销售额的不断增加，用户享受的折扣自动改变特殊表类型-已分区表编号姓名性别地址001王莉女广州002何俊男长沙003李仁男首尔004张艾女北京005刘明男上海北京女张艾004广州女王莉001姓名编号地址性别编号姓名性别地址002何俊男长沙003李仁男首尔005刘明男上海特殊表临时表：SQLServer在执行数据库操作的时候比如排序，分组等需要维护临时数据的表系统表SQLServer将定义服务器配置及其所有表的数据存储的特殊的表SQLServer数据类型分类备注和说明数据类型说明二进制数据类型存储非子符和文本的数据Image可用来存储图像文本数据类型字符数据包括任意字母、符号或数字字符的组合Char固定长度的非Unicode字符数据Varchar可变长度非Unicode数据Nchar固定长度的Unicode数据Nvarchar可变长度Unicode数据Text存储长文本信息Ntext存储可变长度的长文本日期和时间日期和时间在单引号内输入Datetime日期和时间数字数据该数据仅包含数字，包括正数、负数以及分数intsmallint整数floatreal数字货币数据类型用于十进制货币值MoneyBit数据类型表示是/否的数据Bit存储布尔数据类型创建表使用SMSS工具创建表并添加主键主键维护实体完整性选择具有唯一性的字段选择数据更新少的字段标识列默认值外键关系外键关系图外键关系维护从表中插入数据时只能插入在主表中存在的数据删除数据时只能先删除从表中的数据才能删除主表的数据删除表时，只能先删除从表再删除主表检查约束数据导入导出在不同的数据库，不同的服务器，不同的数据源….之间进行数据/结构传输的方式演示操作过程…..总结创建表的时候要设定字段名，选取合适的数据类型和约束如果要保证某个列唯一并且自动增长，设定IDENTITY约束主键是保证实体完整性的约束，建议每个表有一个主键外键保证表之间的引用完整性，从表中的数据依赖于主表借助于SQLServer2005的导入导出工具可以实现不同数据源之间的数据导入和导出第三章SQLServer数据管理

了解SQL和T-SQL通过T-SQL增加数据通过T-SQL删除数据通过T-SQL修改数据通过T-SQL查询数据本章目标

SQLSQL：StructuredQueryLanguage：结构化查询语言SQL是关系型数据库管理系统的标准语言SQL语句可以用来执行各种各样的操作，包括创建数据库对象，数据的增加，删除，修改等操作T-SQLT-SQL=Transact-SQLT-SQL是SQLServer对标准SQL的扩展T-SQL包含SQL的基本功能和指令T-SQL包含了大量的数据操作函数和语法结构T-SQL组成DML（数据操作语言）——查询、插入、删除和修改数据库中的数据；——SELECT、INSERT、UPDATE、DELETE等；DCL（数据控制语言）——用来控制存取许可、存取权限等；——GRANT、REVOKE等；DDL（数据定义语言）——用来建立数据库、数据库对象和定义其列——CREATETABLE、DROPTABLE等变量说明、流程控制、功能函数——定义变量、判断、分支、循环结构等——日期函数、数学函数、字符函数、系统函数等T-SQL中的运算符运算符含义=等于>大于<小于>=大于或等于<=小于或等于<>不等于!非示例DECLARE@nint SET@n=100 SELECT-@ngoDECLARE@var1char(10) DECLARE@var2char(10) SET@var1=’HELLO’ SET@var2=’WORLD!’ SELECT@var1+@var2go通配符通配符解释示例‘_’一个字符ALike'C_'%任意长度的字符串BLike'CO_%'[]括号中所指定范围内的一个字符CLike'9W0[1-2]'[^]不在括号中所指定范围内的一个字符DLike‘%[A-D][^1-2]'逻辑表达式逻辑表达式说明示例AND逻辑与1AND1=;1AND0=0;0AND0=0;OR逻辑或1OR1=1;1OR0=1;0OR0=0;NOT逻辑非NOT1=0;NOT0=1;数据类型数据类型类别具体类型精确数据bigint，decimal，int，numeric，smallint，money，tinyint，smallmoney，bit

近似数字float,real字符串char,varchar,textUnicode字符串nchar,nvarchar,ntext二进制字符串binary,image,varbinary日期类型datetime,smalldatetime其他类型Uniqueidentifier,xml,table等插入数据语法：INSERT[INTO]表名 [（列名，，，n)] VALUES(值，，，n)示例：INSERTINTOSTUDENTVALUES('S0000005','张宇','1982-1-20','南京','男')插入数据注意：对于字符类型和日期类型的数据插入时用单引号将值引起来插入的数据项必须符合字段的约束，否则系统报错不能为标识列指定值，因为它的数字是自动增长的注意事项如果在设计表的时候就指定了某列不允许为空，则必须插入数据具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值INSERTINTOStudent(Name,Address,Sex)VALUES(‘张小’,DEFAULT,’男’)插入多行数据

语法：INSERT[INTO]表名[(列名)]SELECT(列名，，，n)FROM表名示例：INSERTINTOStuTmp(StuNo,StuName,StuAddress)SELECTStuNo,StuName,StuAddressFROMStudent更新数据语法：UPDATE表名SET<字段名=新值>[WHERE更新条件]示例1：给全班考试不及格的学生加10分UPDATEStuScoreSETScore=Score+10WHEREScore<60更新数据示例2：把学生S0000004的出生日期加1天，地址改为沈阳UPDATEStudentSETStuDate=StuDate+1,StuAddress='沈阳'WHEREStuNo='S0000004‘删除数据语法：DELETEFROM表名[WHERE条件]示例1：删除临时表StuTmp中的数据示例2：删除考试不及格的成绩信息

DELETEFROMStuTmpDELETEFROMStu61ScoreWHEREStuScore<60清空表语法：TRUNCATETABLE表名示例：TRUNCATETABLEStuTmp删除数据总结DELETE删除数据时要写日志，而TRUNCATE不写，所以TRUNCATE删除数据不可恢复删除整个表的数据TRUNCATE比使用DELETE速度要快不能对由FOREIGNKEY约束引用的表使用TRUNCATE总结SQL是结果化的查询语言，是关系型数据库管理系统的标准语言增加单行数据用INSERTINTO……VALUES增加多行数据用INSERTINTO……SELECT….FROM删除数据用DELETEFROMWHERE…..快速删除整个表中的数据用TRUNCATETABLE…..修改数据用UPDATE表SET字段=表达式WHERE….第四章

检索数据本章目标掌握查询的基本语法掌握常用的SQL函数掌握TOP关键字和DISTINCT关键字的用法查询查询请求查询结果集结果集如何查询查询广州的所有学生信息：查询语法：SELECT<列名>FROM<表名>[WHERE<查询条件>][ORDERBY<排序的列名>][ASC或DESC]查询返回所有的行和列筛选部分字段SELECTStuNo,StuName,StuSexFROMStudentWHEREStuAddress='广州SELECT*FROMStudent查询-列名一.用as关键字SELECTStuNoas学号,StuNameas姓名,StuSexas性别,StuAddressas地址FROMStudentWHEREStuAddress='广州‘二.用空格SELECTStuNo学号,StuName姓名,StuSex性别,StuAddress地址FROMStudentWHEREStuAddress=‘广州’三.。用=号SELECT学号=StuNo,姓名=StuName,性别=StuSex,地址=StuAddressFROMStudent查询空记录

在查询中通过ISNULL和ISNOTNULL判断是否为空SELECTStuNo学号,StuName姓名,StuSex性别,StuAddress地址FROMStudentWHEREStuAddressISNOTNULL示例：查询地址不为空的学生信息复合查询条件示例:筛选考试科目代号为’002’并且成绩大于70分的信息SELECTStuNo学号,CourseNo课程号,Score成绩FROMStuScoreWHEREScore>70ANDCourseNo='002'ORDERBYCourseNo常量列SELECTStuNo学号,StuName姓名,StuSex性别,StuAddress地址,国家='中国'FROMStudent示例：查询学生信息，增加一列，显示国家名称为“中国”CASE关键字示例：根据学生成绩，划分不同的等级。成绩大于85分，显示优秀，小于60分显示不及格，其余的显示合格。

SELECTStuNo学号,CourseNo课程号,等级=CASEWHENScore>=85THEN'优秀'WHENScore>=60THEN'合格'ELSE'不及格'ENDFROMStuScore排序示例：由高到低显示学生成绩信息多列排序如果不写排序关键字默认为升序ASCSELECTStuNo学号,CourseNo课程号,Score成绩FROMStuScoreORDERBYScoreDESCSELECTStuNo学号,CourseNo课程号,Score成绩FROMStuScoreORDERBYScore，CourseNoSQLServer函数字符串函数日期函数数学函数系统函数字符串函数字符串函数日期函数数学函数系统函数Top关键字TOPn[PERCENT]常量：SELECTTOP5*FROMStuScoreORDERBYScoreDESC变量:declare@nintSET@n=3SElECTTOP(@n)*FROMSTUDENTDistinct关键字DISTINCT列名，，，n一。查询考试信息SELECTStuNoAS学号FROMStuScore二。查询参加了考试的学生SELECTDISTINCTStuNoAS学号FROMStuScore总结从表中筛选记录的时候用SELECT语句通过给列起别名的方式简化代码筛选记录的前N行时用TOP关键字过滤重复记录用DISTINCT关键字

第五章

复杂查询本章目标使用LIKE进行模糊查询掌握聚合函数的使用使用GROUPBy进行分组查询掌握多表关联查询模糊查询-LIKE示例：查询姓名叫”王**军“的学生信息通配符：%：包含零个或多个字符的任意字符串_下划线：任何单个字符[]：指定范围[a-f]或集合[acdef]中的任意单个字符[^]：不属于指定范围([a-f])或集合([abcdef])的任何单个字符。提问：在author表中查找以de开始并且其后的字母不为l的所有作者的姓氏SELECT*FROMSTUDENTWHEREStuNameLIKE‘王_军‘模糊查询-BETWEEN示例：查询出生日期在1980-1982年的学生信息

提问：查询出生日期不在1980-1982年的学生信息等价于SELECT*FROMSTUDENTWHEREStuDateBETWEEN'1980'AND'1982‘SELECT*FROMSTUDENTWHEREStuDate>='1980-01-01'ANDStuDATE<='1982-01-01’聚合函数提问：想知道班级学员的总分，平均分等统计数据该怎么实现？求最小值MIN求最大值MAX求总和SUM统计列中的数据项数COUNT（列名）统计记录行数COUNT（*）求平均值AVG描述函数名

聚合函数示例

示例1：求全班的平均分提问：求全班的最高分和最低分示例2：统计全班的学生人数SELECTAVG(Score)AS平均成绩FROMStuScoreSELECTCOUNT(*)总人数

FROMStudent提问：统计参加考试科目’001’的人数分组查询

提问：统计全班每个学生的平均成绩该如何实现？GROUPSELECTStuNo学号,AVG(Score)平均分FROMStuScoreGROUPBYStuNo提问：如何统计全班各门课程的平均成绩呢？HAVING过滤提问：查询全班平均成绩大于75分的学生信息SELECTStuNo学号,AVG(Score)平均分FROMStuScoreGROUPBYStuNoHAVINGAVG(Score)>75如果将HAVING改为WHERE会怎么样？关联查询提问：用户需要查看学生的详细信息以及成绩信息？所需要的数据分别在两个表中存储，如何同时访问这两个表的数据？多表关联分类内联接(INNERJOIN)外联接——左外联接(LEFTJOIN)——右外联接(RIGHTJOIN)——完整外联接(FULLJOIN)自联接(CROSSJOIN)内联接一.传统语法SELECT列名，，，，FROM表名，，，，WHERE条件二.SQL联接SELECT列名FROM表名[INNER]JOIN表名[JOIN表名....]ON连接条件WHERE条件内联接SELECT*FROMStudentA,StuScoreBWHEREA.StuNo=B.StuNoSELECT*FROMStudentINNERJOINStuScoreCourseONStudent.StuNo=Course.StuNo内联接-多表关联示例：查询参加了考试的学生信息，成绩信息和具体科目信息

SELECTA.StuNo学号,A.StuName姓名,A.StuSex性别,A.StuDate出生日期,A.StuAddress地址,C.CourseNo课程号,C.CourseName,B.Score成绩FROMStudentAJOINStuScoreBONA.StuNo=B.StuNoJOINCourseCONB.CourseNo=C.CourseNo传统语法如何写？左外联接SELECTA.StuNo学号,A.StuName姓名,A.StuSex性别,A.StuDate出生日期,A.StuAddress地址,B.CourseNo课程号,B.Score成绩FROMStudentALEFTJOINStuScoreBONA.StuNo=B.StuNo提问：这种写法返回结果相同吗SELECTB.StuNo学号,B.StuName姓名,B.StuSex性别,B.StuDate出生日期,V.StuAddress地址,A.CourseNo课程号,A.Score成绩FROMStuScoreALEFTJOINStudentBONA.StuNo=B.StuNo右外联接提问：以下SQL语句的输出结果是什么？SELECTA.StuNo学号,A.StuName姓名,A.StuSex性别,A.StuDate出生日期,A.StuAddress地址,B.CourseNo课程号,B.Score成绩FROMStudentARIGHTJOINStuScoreBONA.StuNo=B.StuNo完全外联接示例：查询学生信息以及成绩信息SELECTA.StuNo学号,A.StuName姓名,A.StuSex性别,A.StuDate出生日期,A.StuAddress地址,B.CourseNo课程号,B.Score成绩FROMStudentAFULLJOINStuScoreBONA.StuNo=B.StuNo提问：上述SQL语句返回多少条记录?自联接

提问：查询员工信息以及经理的信息SELECTA.*,B.ENameASMgrNameFROMEmployeeAJOINEmployeeBONA.Mgr=B.EmpNo总结模糊查询用LIKE，BETWEENAND关键字对数据进行统计使用SQL内置的聚合函数对数据进行分组用GROUPBY查询的数据来自于多个表用关联查询内联接筛选多表中完全匹配的行左外联接以左表为主筛选数据右外联接以右表为主筛选数据内联接将表自身通过别名关联第六章

高级查询本章目标掌握子查询的使用区分相关子查询和无关子查询掌握集合的操作UNION，EXCEPT，INTERSECT子查询问题:查询平均成绩最低的学生信息解决方法：一.首先查询平均成绩最低的学生编号二.以学号为条件查询学生信息子查询子查询就是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询基本语法：

SELECT*FROMStudentWHEREStuNo=(SELECTTOP1StuNoASStuNoFROMStuScoreGROUPBYStuNoORDERBYAVG(Score))SELECT列名……..FROM表名WHERE条件= (SELECT列名

FROM表名

WHERE条件）单行子查询子查询返回单个结果值关联语法实现方式：SELECT*FROMStudentAJOIN(SELECTTOP1StuNoASStuNoFROMStuScoreGROUPBYStuNoORDERBYAVG(Score))ASBONA.StuNo=B.StuNo单行子查询多层嵌套问题：查询高于全班平均成绩的学生的学生信息解决方法:一.查询平均成绩二.查询成绩大于平均成绩的学生学号三.查询学生信息SELECT*FROMStudentA,( SELECT*FROMStuScoreWHEREScore>( SELECTAVG(Score) FROMStuScore ))ASBWHEREA.StuNo=B.StuNo多行子查询多行子查询是指子查询（内部查询）返回多个值，外部查询通过IN（NOTIN）引入子查询。语法：SELECT列名。。。。。FROM表名WHERE条件[NOT]IN(SELECT列名

FROM表名

WHERE条件）IN子查询问题：查询男生的考试信息

子查询：SELECT*FROMStuScoreWHEREStuNoIN(SELECTStuNoFROMStudentWHEREStuSex='男')关联语法：SELECT*FROMStudentAJOINStuScoreBONA.StuNo=B.StuNoANDA.StuSex='男‘NOTIN子查询问题：查询不住在广州的学生SELECT*FROMStuScoreWHEREStuNoNOTIN(SELECTStuNoFROMStudent WHEREStuAddress='广州')关联查询：SELECT*FROMStuScoreAJOINStudentBONA.StuNo=B.StuNoWHEREB.StuAddress<>'广州'Exists子查询问题：查询参加了考试的学生解决方法：一.查找所有的学生二.判断该学生是否参加了考试，如果是则输出SELECT*FROMStudentWHEREEXISTS( SELECT*FROMStuScoreWHEREStuScore.StuNo=Student.StuNo)NOTExists子查询示例：查询没有参加考试的学生信息SELECT*FROMStudentWHERENOTEXISTS( SELECT*FROMStuScoreWHERE StuScore.StuNo=Student.StuNo)相关，无关子查询比较

相关子查询：首先执行外部查询，子查询根据外部查询获得值。子查询执行是重复的，为外部查询可能选择的每一行都执行一次无关子查询：先执行内部的子查询，然后将子查询的执行结果作为外部查询的条件再进行查询INSERT中的子查询问题：创建一个新表NewStudent，包含字段StuNo,StuName,StuAddress将Student中的数据插入NewStudent第一步：创建表SELECTStuNo,StuName,StuAddressINTONewStudentFROMStudentWHEREStuNoISNULL第二步：插入数据INSERTINTONewStudentSELECTStuNo,StuName,StuAddressFROMStudentUPDATE中的子查询问题：将居住在北京的学生的成绩加10分子查询语法：UPDATEStuScoreSETScore=Score+1WHEREStuNoIN(SELECTStuNoFROMStudentWHEREStuAddress='北京')关联语法：UPDATEStuScoreSETScore=Score+10FROMStudentWHEREStudent.StuNo=StuScore.StuNoANDStudent.StuAddress='北京'DELETE中的子查询问题：删除平均成绩不合格的学生信息

子查询语法：DELETEFROMStudentWHEREStuNoIN(SELECTStuNoFROMStuScoreGROUPBYStuNoHAVINGAVG(Score)<60)关联语法：DELETEFROMStudentFROMStudentAINNERJOIN(SELECTStuNoFROMStuScoreGROUPBYStuNoHAVINGAVG(Score)<60)ASBONA.StuNo=B.StuNoUNION运算符问题：将Student表和NewStudent表的数据合并起来SELECT*FROMNewStudentUNIONSELECTStuNo,StuName,StuAddressFROMStudentStudent表NewStudent表Student表UNIONALLSELECT*FROMNewStudentUNIONALLSELECTStuNo,StuName,StuAddressFROMStudentStudent表NewStudent表Student表EXCEPT运算符

问题：查询没有参加考试的学生

SELECTStuNoFROMStudentEXCEPTSELECTStuNoFROMStuScore

INTERSECT运算符

问题：查询参加了考试的学生SELECTStuNoFROMStudentINTERSECTSELECTStuNoFROMStuScore总结子查询就是嵌套在SELECT，INSERT，UPDATE和DELETE中的查询无关关子查询即子查询不依赖于外部查询，执行时先执行内部查询再执行外部查询，通常用比较运算符和IN，NOTIN关键字相关子查询即子查询的条件依赖于外部查询，执行时先执行外部查询，然后根据外部查询返回的记录行数重复执行内部查询通常用EXISTS，NOTEXISTS关键字结果集要进行合并时用UNION运算符，结果集取交集用

INTERSECT运算符。结果集相减用EXCEPT运算符。第七章数据库的设计课程内容回顾数据库有哪些基本操作？建库建表加约束创建登录帐户

基本的数据操纵语句有哪些？语法是？增（INSERT）删（DELETE）改（UPDATE）查（SELECT）常用的聚合函数有哪些？表连接分为哪几种类型？本章目标了解设计数据库的步骤掌握如何绘制数据库的E-R图理解数据库的规范化－三大范式规范数据库设计的必要性设计前提建楼需要根据图纸,户型设计好坏直接影响住宅质量实际项目中的数据库需要规范化的设计什么是数据库设计?什么是数据库设计?数据库设计就是规划和结构化数据库中的数据对象以及这些数据对象之间关系的过程。学生成绩数据库结构数据库设计的重要性良好的数据库设计节省数据的存储空间能够保证数据的完整性方便进行数据库应用系统的开发糟糕的数据库设计：数据冗余、存储空间浪费内存空间浪费数据更新和插入的异常软件项目开发周期需求分析阶段：分析客户的业务和数据处理需求;概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整;详细设计阶段：将E-R图转换为多张表，进行逻辑设计，并应用数据库设计的三大范式进行审核;代码编写阶段：选择具体数据库进行物理实现，并编写代码实现前端应用;软件测试阶段：……安装部署：……现实世界建模信息世界模型转换规范化数据库世界数据库数据库设计数据库的方法和步骤收集信息：

与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务学生管理系统的基本功能：基本信息维护,后台数据库需要存放系,班级,学生和课程等基本数据学生选课和成绩查询,后台数据库需要存放学生选课和成绩的基本数据设计数据库的方法和步骤标识对象（实体－Entity）

标识数据库要管理的关键对象或实体

实体一般是名词：系,班级,学生,课程设计数据库的方法和步骤系系号系名系主任电话班级班号班级名人数系号学生学号姓名年龄系号班级性别住址电话课程课程编号课程名称学分标识每个实体的属性（Attribute）设计数据库的步骤标识对象之间的关系（Relationship）系与班级、学生之间具有主从关系：我们需要知道每个班级及学生是属于哪个系的；一个学生可以选多门课程，每个课程也可有多个学生选择；绘制E-R图E-R（Entity－Relationship）实体关系图符合含义实体，一般是名词属性，一般是名词关系，一般是动词绘制E-R图属于

学生……姓名学号系号系名……系绘制E-R图映射基数一对一XXXXYYYYXXXXYYYY一对多XXXXY

YY多对一XXXXYYYY多对多客户订单产品MN1N1MM11M学号姓名性别年龄住址学号课程号成绩系号电话班号学生（student）学习属于属于属于选课（grade）系（depart）电话系主任系名系号M班级（class）班名班号人数系号1绘制E-R图学生管理E-R图

课程（course）课程名课程号学分分解1M如何将E-R图转换为表将各实体转换为对应的表，将各属性转换为各表对应的列标识每个表的主键列，需要注意的是：没有主键的表添加ID编号列，它没有实际含义，用于做主键或外键，例如学生表中的“studID”列，班级表中添加“classID”列，系信息表中的“deptid”列在表之间建立主外键，体现实体之间的映射关系将E-R图转化为数据表Class(班级)表Student（学生)表ClassID(班级号)-主键Clname（班级名）Ccount（人数）DeptID（系号）StudID(学号）-主键Sname（姓名）Ssex（性别）Sage（年龄）Saddr（住址）Stelephone（电话）DeptID（系号）ClassID（班号）Depart(系)表DeptID(系号）-主键Dname（系名）Dleader（系主任）Dtelephone（电话）CID(课程号）-主键Cname（课程名）Crecord（学分）Grade(选课)表StudID(学号）

-主键CID(课程号）-主键Score(成绩）Course(课程)表DeptID主键ClassID主键StudID主键CID主键StudID和CID共同是主键将E-R图转化为数据表Class(班级)表Student（学生)表ClassID（班级号）Clname（班级名）Ccount（人数）DeptID（系号）StudID（学号）Sname（姓名）Ssex（性别）Sage（年龄）Saddr（住址）Stelephone（电话）DeptID（系号）ClassID（班号）Depart(系)表DeptID（系号）Dname（系名）Dleader（系主任）Dtelephone（电话）Course(课程)表CID（课程号）Cname（课程名）Crecord（学分）Grade选课)表StudID（学号）CID（课程号）Score（成绩）添加各表之间的关系数据规范化仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创建好表的结构DrE.F.codd最初定义了规范化的三个级别，范式是具有最小冗余的表结构。这些范式是：第一范式(1stNF－FirstNormalForm)第二范式(2ndNF－SecondNormalForm)第三范式(3rdNF－ThirdNormalForm)第一范式(1stNF)课程名称学时数数据结构数据库原理48243015……第一范式的目标是确保每列的原子性如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）讲课实验课程名称数据结构数据库原理48243015……讲课时数实验时数第二范式(2ndNF)如果一个关系满足1NF，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式（2NF）第二范式要求每个表只描述一件事情Student字段例子学号姓名课程号课程名S001张三C01数据结构系号X01Student字段例子学号姓名S001张三Course字段例子课程号课程名C01数据结构……系号X01第三范式(3rdNF)如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）

StudentStudent字段例子学号姓名S001张三Course字段例子课程号课程名C01数据结构Depart字段例子系号…X01…字段例子学号姓名课程号课程名S001张三C01数据结构系号X01

……函数依赖函数依赖：当属性间存在X->Y,Y-\->X,Y->Z,称Z传递依赖与XX—>Y：称Y依赖X或X决定Y。如：学号->姓名,姓名依赖于学号数据规范化示例1：对学生关系模式进行分解，逐步分解为1NF、2NF、3NF

例如:

学生(学号，姓名，住址，电话，班名，系系名，课程号，课程名，学分，成绩)该关系模式的每一属性对应的域为简单域，符合第一范式数据规范化该关系模式满足函数依赖集为:

学号—>姓名,学号—>住址,学号—>电话,学号—>班名,

学号—>系名

（学号，课程号）—>成绩

课程号—>课程名，课程号—>学分可分解为以下三个2NF

学生1（学号，姓名，住址，电话，班名，系名）课程（课程号，课程名，学分）选课（学号，课程号，成绩）该三个关系的非主属性对码（主属性）完全依赖，均为2NF数据规范化分析关系模式“学生”

存在学号—>班名,班名->系名,系名－/－>班名系名对学号的传递依赖，所以不符合3NF,所以关系模式”学生1”不是第三范式关系模式”学生”分解为:

学生2（学号，姓名，住址，电话，班名）班级(班名，系名）规范化和性能的关系为满足某种商业目标，数据库性能比规范化数据库更重要通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间通过在给定的表中插入计算列（如成绩总分），以方便查询进行规范化的同时，还需要综合考虑数据库的性能。总结1-1在需求分析阶段，设计数据库的一般步骤为：收集信息标识对象标识每个对象的属性标识对象之间的关系在概要设计阶段和详细设计阶段，设计数据库的步骤为：绘制E-R图将E-R图转换为表格应用三大范式规范化表格总结1-2为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式。第一范式（1NF）的目标：确保每列的原子性。第二范式（2NF）的目标：确保表中的每列，都和主键相关。第三范式（3NF）的目标：确保每列都和主键列直接相关，而不是间接相关。第八章数据库的实现课程内容回顾需求分析阶段,数据库设计的一般步骤有哪些？如何将E-R图转换成数据表?简单描述3大范式本章目标使用SQL语句创建数据库和表使用SQL语句创建约束创建安全帐户的SQL语句数据库基础知识回顾创建数据库时:指定数据库名称主数据库文件和日志文件的名称和大小。

数据库文件的组成:主数据文件：*.mdf次要数据文件：*.ndf

日志文件：*.ldf数据库的其他主要属性:文件存放位置分配的初始空间，属于哪个文件组。文件的增长设置，可以按百分比或实际大小指定增长速度。文件容量设置，可以指定文件增长的最大值或不受限

创建数据库CREATEDATABASE数据库名[ON{PRIMARY(NAME=逻辑文件名,FILENAME=物理文件名

[,SIZE=大小][,MAXSIZE={最大容量|UNLIMITED}][,FILEGROWTH=增长量])}[,。。。n]][LOGON{(NAME=逻辑文件名,FILENAME=物理文件名

[,SIZE=大小][,MAXSIZE={最大容量|UNLIMITED}][,FILEGROWTH=增长量])}[,。。。n]]建库语法创建数据库示例1USEmasterGOCREATEDATABASEempDBONPRIMARY--主文件组，不能省略/*--数据文件的具体描述--*/(NAME=’empDB_data’,--主数据文件的逻辑名FILENAME=’D:\Data\empDB_data.mdf’,--主数据文件的物理名SIZE=5MB,--主数据文件的初始大小MAXSIZE=15MB,--主数据文件增长的最大值(UNLIMITED为增长不受限制)FILEGROWTH=20%)--文件的增长率LOGON/*--日志文件的具体描述，各参数含义同上--*/(NAME=’empDB_log’,FILENAME=’D:\Data\empDB_log.ldf’,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)GO演示创建数据库1：一个数据文件和一个日志文件创建数据库示例2USEmasterGOCREATEDATABASEArchiveONPRIMARY/*--主数据文件的具体描述--*/(NAME=Arch1,FILENAME=‘d:\data\archdat1.mdf',SIZE=10MB,MAXSIZE=15,FILEGROWTH=5),/*--次要数据文件1的具体描述--*/(NAME=Arch2,FILENAME=‘d:\data\archdat2.ndf',SIZE=10MB,MAXSIZE=15,FILEGROWTH=5),演示创建数据库2：多个数据文件和多个日志文件创建数据库示例2/*--次要数据文件2的具体描述--*/(NAME=Arch3,FILENAME=‘d:\data\archdat3.ndf',SIZE=10MB,MAXSIZE=15,FILEGROWTH=5),LOGON/*--日志文件1的具体描述--*/(NAME=Archlog1,FILENAME='d:\data\archlog1.ldf',SIZE=5MB,MAXSIZE=10,FILEGROWTH=5),/*--日志文件2的具体描述--*/(NAME=Archlog2,FILENAME='d:\data\archlog2.ldf',SIZE=5MB,MAXSIZE=10,FILEGROWTH=5)GO演示创建数据库2：多个数据文件和多个日志文件删除数据库DROPDATABASEdatabase_name[,.。。。n]USEmasterGODROPDATABASEpubs使用SQL语句创建表建表的步骤确定表中有哪些列确定每列的数据类型给表添加各种约束创建各表之间的关系整型:int,smallint小数:decimal,numeric字符:固定长度:char(6)可变长度:varchar(10)Unicode字符:nvarchar(10)日期:datetime货币:money布尔:Bit–取值为0或1创建表CREATETABLE表名

(

字段1数据类型列的特征，字段2数据类型列的特征，。。。

)语法列的特征包括该列是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键等。创建表示例一CREATETABLEDEPT(deptnoINTPRIMARYKEY,--主键（唯一，非空）dnameCHAR(15)NOTNULL,--部门名称，非空(必填)locVARCHAR(30)--部门位置，允许为空，即可选输入)演示创建部门表dept创建表示例二CREATETABLEEMP(empnoINTPRIMARYKEY,--雇员编号，主键

enameVARCHAR(10)NOTNULL,--雇员姓名

jobVARCHAR(9),--工种

salDECIMAL(7,2)NOTNULL,--工资deptnoINTNOTNULL,--雇员所在部门号，外键CONSTRAINTemp_deptno_fkFOREIGNKEY(deptno)REFERENCESdept(deptno))演示创建雇员表emp创建表示例三CREATETABLEstuInfo(stuNoINTIDENTITY(1,1)--学号，自动编号（标识列）stuNameVARCHAR(20)NOTNULL,--姓名stuAgeINTNOTNULL,--年龄stuAddressVARCHAR(30)--地址)演示创建学生信息表stuInfo创建表示例四

使用SELECTINTO生成表结构和数据的拷贝:SELECT*INTOnewdeptFROMdept

删除表USEempDBGO//检查数据库中是否存在该表,如果存在则删除IFEXISTS(SELECT*FROMsysobjectsWHEREname=’dept’)DROPTABLEdept语法:DROPTABLE表名系统表:存放表的清单演示删除表dept创建和删除约束回顾约束类型:主键约束（primarykeyconstraint）：要求主键列数据唯一，并且不允许为空。唯一约束（uniqueconstraint)：要求该列唯一，允许为空，但只能出现一个空值。检查约束（checkconstraint）：某列取值的限制、格式的限制等。如有关性别的检查。默认约束（defaultconstraint）：某列输入数据时的默认值，强制了域完整性。外键约束（foreignkeyconstraint）：用于在两表间建立关系，需要指定引用主表的哪列。

给表添加约束添加约束语法:约束的命名规则推荐采用：约束类型_约束字例如:给dept表的deptno字段添加主键约束，推荐起名为：PK_deptno给Emp表的ename字段添加唯一约束，可起名为：UQ_enameALTERTABLE表名ADDCONSTRAINT约束名约束类型具体的约束说明添加约束示例USEempDBGO----添加主键约束(deptno作为主键),约束名为pk_deptnoALTERTABLEdeptADDCONSTRAINTpk_deptnoPRIMARYKEY(deptno)――添加唯一约束,希望员工名字唯一ALTERTABLEempADDCONSTRAINTUQ_enameUNIQUE(ename)――添加检查约束,要求雇员工资少于3000ALTERTABLEempADDCONSTRAINTck_salCHECK(sal<=3000)――添加默认值约束,工资的默认值是８００ALTERTABLEEMPADDCONSTRAITNTdf_salDEFAULT800FORsal――添加外键约束(主表dept和从表emp建立关系，关联字段为deptno)ALTERTABLEempADDCONSTRAINTfk_deptnoFOREIGNKEY(deptno)REFERENCESdept(deptno)添加约束示例演示：查看约束结果删除约束删除约束语法:ALTERTABLE表名DROPCONSTRAINT约束名例如删除表emp中的工资默认值约束ALTERTABLEEMPDROPCONSTRAINTdf_sal回顾SQLServer的安全模型增删改查增删改查增删改查增删改查增删改查增删改查数据库用户数据库用户数据库用户登录帐号SQLserver三层安全管理数据表1数据表2DB1数据表1数据表2DB2数据表1数据表2DB3登录方式回顾登录验证有两种方式：SQL身份验证：适合于非windows平台的用户或Internet用户，需要提供帐户和密码Windows身份验证：适合于windows平台用户，不需要提供密码，和windows集成验证登录帐户相应有两种:：SQL帐户和Windows帐户

创建登录账户使用SQL语句创建两种模式下的登录帐户添加WINDOWS登录帐户添加SQL登录帐户EXECsp_grantlogin‘windows域名\域帐户’EXECEXECsp_addlogin‘帐户名’，‘密码’创建登录账户添加Windows登录帐户//windows用户为S01,SDXY表示域如是本机，SDXY为计算机名

EXECsp_grantlogin‘SDXY\S01’添加SQL登录帐户//登录名为zhangsan,密码为1234EXECsp_addlogin‘zhangsan’,’1234’GO演示：创建登录帐户创建数据库用户创建了登录帐户，只能登录到SQLServer系统，但还不能访问某个数据库。如果希望访问某个数据库，必须要成为该数据库的一个用户。创建数据库用户需要调用系统存储过程sp_grantdbaccess

在empDB中添加两个用户EXECsp_grantdbaccess‘登录帐户’，‘数据库用户’USEempDB--S01DBUser为数据库用户名EXECsp_grantdbaccess‘SDXY\S01’，‘S01DBUser’EXECsp_grantdbaccess‘zhangsan’，‘zhangsanDBUser’演示：创建数据库用户给数据库用户授权当成为数据库用户后,还不能访问表,需要DBA授予普通用户访问表的权限:insert,update,delete,select语法:GRANT权限

ON表名TO数据库用户USEempDBGO--为zhangsanDBUser分配对表emp的select，insert，update权限GRANTselect，insert，updateONempTOzhangsanDBUser--为用户s01DBUser分配建表的权限GRANTcreatetableTOS01DBUser

总结2-1数据库的物理实现一般包括：创建数据库创建表添加各种约束创建数据库的登录帐户并授权创建数据库或表时一般需要预先检测是否存在该对象数据库从master系统数据库的sysdatabases表中查询表从该数据库的系统表sysobjects表中查询总结2-2访问SQLServer某个数据库中的某个表，需要三层验证：是否是SQLServer的登录帐户是否是该数据库的用户是否有足够的权限访问该表第九章T-SQL编程课程内容回顾数据库文件包括哪些,扩展名都是什么?建立数据表和添加约束数据库的安全模型本章目标掌握如何定义变量并赋值掌握如何输出显示数据掌握IF、WHILE、CASE逻辑控制语句理解SQL中批处理的概念T-SQL中的数据类型T-SQL中的主要数据类型包括:数字类型:用于存储整数或小数整数如smallint,int;

小数如numeric,decimal字符类型:char,varchar,nchar,nvarchar日期类型:datetime使用变量变量分为局部变量和全局变量局部变量使用DECLARE定义,声明时必须在变量名前加@局部变量必须先声明后赋值如:变量名vname定义成@vname全局变量标识为两个@标记(@@)由SQLServer提供的，不能由用户创建。全局变量提供关于SQLServer的当前状态信息如:SQLServer当前版本号:@@version局部变量的声明和赋值局部变量的声明语法:例如:局部变量的赋值有两种方法语法:例如DECLARE@variable_nameDataTypeDECLARE@countint--声明一个局部变量@countDECLARE@enamevarchar(8)--声明一个局部变量ename,最多可存储８个字符SET@variable_name=value或SELECT@variable_name=valueSET@count=10或SELECT@count=10SET@ename=’SMITH’或SELECT@ename=’SMITH’局部变量的使用SET赋值语句一般用于赋给变量指定的数据常量，如本例‘SMITH’SELECT赋值语句一般用于从表中查询数据，然后再赋给变量。

需要注意的是:SELECT语句需要确保筛选的记录不多于一条。如果查询的记录多于一条，将把最后一条记录的值赋给变量。USEempDBDECLARE@venoint,@vnamechar(20)SET@vname=’SMITH’SELECT@veno=empnoFROMempWHEREename=@vnameSELECT*FROMempWHEREempno=@venoGO

局部变量的使用学生信息表问题：编写T-SQL找出名字是”董明”的学生学号的前后同学？分析：第一步，找出“董明“的学号；第二步，董明的学号加1或减1

参考语句:/*--找出董明的信息--*/DECLARE@namevarchar(8)--学员姓名SET@name='董明'--使用SET赋值SELECT*FROMstuInfoWHEREstuName=@name/*--找出董明的前后学号--*/DECLARE@Snoint--学号SELECT@Sno

=stuNoFROMstuInfo

--使用SELECT赋值

WHEREstuName=@nameSELECT*FROMstuInfoWHERE(stuNo=@sno+1)OR(stuNo=@sno-1)GO局部变量的使用全局变量全局变量标识为两个@标记(@@),它是由SQLServer提供的变量含义@@ERROR最后一个T-SQL错误的错误号@@IDENTITY最后一次插入的标识值@@LANGUAGE当前使用的语言的名称@@MAX_CONNECTIONS可以创建的同时连接的最大数目@@ROWCOUNT 受上一个SQL语句影响的行数@@SERVERNAME本地服务器的名称@@TRANSCOUNT 当前连接打开的事务数@@VERSION

SQLServer的版本信息全局变量的使用print'SQLServer的版本'+@@VERSIONprint'服务器的名称:'+