38 09网络数据库电子教案(实践)

38 09网络数据库电子教案(实践) 放心减肥网.jianfei1595.提供课次章节课题教学目的和要求第3次实践课实验三企业管理器创建数据表（设置表的主键、外键和建立表间的关系；为表增加约束）掌握使用企业管理器创建数据库表（建立主外表关系、为表添加约束）及添加数据重点难点设置表的主键、外键和建立表间的关系；为表增加约束课时讲授方式教学过程作业2课时上机操作附于表后附于表后课堂总结用企业管理器创建数据库表及向数据库表中录入数据试验三创建数据库操作步骤“SQL Server组”，右击“新建SQL Server注册”（注意服务器选择(local)）“数据库”，右击“新建数据库”“设置数据库属性”（常规、数据文件、事务日志，此三项设置可参照教材P81）常规输入“名称”数据文件主要数据文件(文件名，一般会默认生成、位置、初始大小、文件自动增长、最大文件大小)，扩展名.MDF；次要数据文件(文件名，一般会默认生成、位置、初始大小、文件自动增长、最大文件大小)，扩展名.NDF；事务日志文件名，一般会默认生成、位置、初始大小、文件自动增长、最大文件大小。 .LDF第一部分创建数据库Students（学生数据库）数据库包括一个主文件Students_Data.MDF（初始大小为2MB；文件自动增长按百分比20；将文件增长限制为500M）一个次要文件Students_Data.NDF（初始大小为1MB；文件自动增长按兆字节1MB；将文件增长限制为500M）一个日志文件Students_Log.LDF（初始大小为1MB；文件自动增长按百分比20；将文件增长限制为500M）并且要把这些三个文件文件保存到F盘上的文件夹StudentsData里创建数据库表步骤点击已创建的数据库前的“+”，右击“表”“新建表”设计要创建的新数据库表（列名、数据类型、长度、允许空；设置主键、建立约束、关系等等）点击“保存”按钮输入表名点击“确定”第二部分在数据库中创建表放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供StudentInfo(学生信息表)表结构如下要求Scode“标识”设置放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供Saddress默认值Semail检查约束Sphone检查约束放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供SGrade默认值SSex检查约束StuCourse（科目表）CourseID为主键放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供StuScore（成绩表）设置StudentID和CourseID做主键方法按Shift键选中StudentID和CourseID，右击，选择“设置主键”Score约束放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供第三部分建立关系放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供关系图放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供关系图命名为Student第四部插入数据1往学生表中插入数据如下放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供2往科目表中插入数据如下3往成绩表中插入数据如下放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供课次第4次实践课章节课题实验四数据管理（使用T-SQL进行增、改、删数据）教学目的和要求理解T-SQL及其作用;使用SQL Server中的逻辑表达式和运算符;掌握使用T-SQL插入、更新、删除数据重点难点T-SQL插入、更新、删除数据课时讲授方式教学过程作业课堂总结掌握T-SQL增、改、删数据2课时上机操作附于表后附于表后本次试验的素材数据库students；数据库表studentInfo(列名Sname、Scode、Saddress、Semail、Sphone、SGrade、SSex)；数据库表StuCourse（列名CourseId、CourseName、BookName）；数据库表StuScore（列名StudentId、CourseId、Score）实验四内容第一部分插入数据插入语句语法格式INSERTINTO列名VALUES?INTO是可选的，可以省略?表名是必须的，表的列名是可选的，如果省略，将一次插入?多个列名和多个值列表用逗号分隔练习 1、往StudentIfo表中插入数据如下T-SQL操作代码如下放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供练习 2、往StuCourse表中插入数据如下T-SQL操作代码如下练习 3、往StuScore表中插入数据如下T-SQL操作代码如下放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供第二部分修改更新数据语法格式UPDATESETWHERE练习 4、把刘明的地址改为湖南常德，把刘明的性别改为男update studentinfoset ssex=男,saddress=湖南常德where scode=刘明练习 5、把所有学生的CourseID为3的课程成绩提高5%update stuscoreset score=score*1.05where courseid=3练习 6、把Email中含有sohu的学生年级改为3，地址改为“湖南长沙”update studentinfoset saddress=湖南长沙where semaillike%sohu%练习 7、把学号为3，科目号为4的学生成绩改为70分update stuscoreset score=70where studentid=3and courseid=4第三部分删除DELETE FROMWHERE注意TRUNCATE TABLE与DELETE FROM等价练习 8、李永因为违反学校纪律，被开除(删除学生名为李永的记录，如果有错误，找出问题，并删除之)注意使用DELETE删除数据时，不能删除被外键值所引用的数据行提示先删外表delete fromstuscore wherestudentid=2后删主表delete from studentinfo wherescode=2放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供课次第5次实践课章节课题实验五数据查询（条件查询；返回限制行，查询排序）教学目的和要求1.会使用SELECT语句进行条件查询2.掌握在查询中返回限制行3.会进行查询排序重点难点条件查询、返回限制行查询、查询排序课时讲授方式教学过程作业课堂总结2课时上机操作附于表后实验内容附于表后的练习掌握这三种查询实验 五一、实验素材（存放在文件夹“sy5素材”）数据库Students(三个数据库表StudentInfo、StuCourse、StuScore)数据库表StudentInfo的结构如下检查约束(SClasslike0-90-90-90-90-9)(SEmaillike%)(SPhonelike0-90-90-90-9-0-90-90-90-90-90-90-90-9)(SSex=男orSSex=女)数据库表StuCourse的结构如下放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供数据库表StuScore的结构如下检查约束(Score=0andScore=100)数据库表StudentInfo与数据库表StuScore关系如下数据库表StuCourse与数据库表StuScore关系如下放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供StudentInfo、StuCourse、StuScore三表的数据分别如下图放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供 二、实验五操作内容首先，启动sql server服务器；其次，打开企业管理，附加文件夹“sy5素材”下的数据库Students；再次，打开查询分析器，连接好sql server服务器。 查询语句通用格式SELECTFROMWHEREORDER BYASC或DESC例 1、查询StudentInfo的所有数据操作代码select*fromstudentinfo练习1分别查询另两个表的所有数据例 2、查询男学员信息操作代码select*FROM Studentinfowhere SSEX=男练习2查询女学员信息（有两种方法）例 3、查询08114班学员信息操作代码SELECT*FROM StudentInfo WHERE SClass=08114练习3查询湖南长沙的学员信息例 4、查询女学员信息，只包括学号、姓名、地址；并且按学号的升序显示操作代码SELECT SCode,SName,Saddress FROM Studentinfo WHERESSEX=女ORDER BYScode练习4查询男学员信息，只包括姓名、学号、地址；并且按学号的降序显示例 5、查询不是湖南长沙的学员信息，显示学员学号、学员姓名、学员地址；操作代码SELECT SCodeAS学员编号,SName AS学员姓名,SAddress AS学员地址FROM StudentInfo WHERE SAddress湖南长沙练习5查询不是湖南株洲的学员信息，显示学员学号、学员姓名、学员地址；放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供例 4、查询邮箱不为空的学员姓名、学员邮箱操作代码SELECT Sname,Semail FROM StudentInfo WHERESEmail ISnot NULL练习5查询邮箱为空的学员姓名、学员邮箱例 6、查询表StudentInfo,显示姓名、地址和常量列学校名称，该常量列的值为益阳职院操作代码SELECT姓名=SName,地址=SAddress,益阳职院AS学校名称FROMStudentInfo练习 6、查询表StudentInfo,显示姓名、性别、地址和常量列学校名称，该常量列的值为湖南师大例 7、查询女学员的姓名、地址信息，且返回前两行记录SELECT TOP2SName,SAddress FROMStudentInfoWHERESSex=女练习7查询男学员的姓名、电话，且返回前三行记录例 8、查询女学员的姓名、地址信息，且返回前60%记录SELECT TOP60PERCENT SName,SAddress FROMStudentInfoWHERESSex=女练习8查询男学员的姓名、地址信息，且返回前50%记录例 9、查询综合成绩及格的学员成绩信息，显示学员编号、课程编号、综合成绩（原成绩的90%+5）SELECT StudentIDAs学员编号,courseID As课程编号,(Score*0.9+5)As综合成绩FROM stuScoreWHERE(Score*0.9+5)=60ORDER BYScore asc练习9查询综合成绩及格的学员成绩信息，显示学号、课程编号、综合成绩（原成绩的85%+7）例 10、查询成绩及格的学员成绩信息，显示学员编号、课程编号、成绩；且依次按成绩降序，课程编号升序显示SELECT StudentIDAs学员编号,courseID As课程编号,Score As成绩FROM stuScoreWHERE Score=60ORDER BYScore desc,CourseID asc练习10查询成绩高于70的学员成绩信息，显示学员编号、课程编号、成绩；且依次按成绩升序，课程编号降序显示附加练习use northwindselect*from employeesSELECT FirstName+.+LastName AS姓名FROM EmployeesSELECT姓名=FirstName+.+LastName FROMEmployees思考此两条查询语句的作用是否相同，作用是什么？放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供课次章节课题教学目的和要求第6次实践课实验六数据查询（SQL Server2000中的函数用法）、三种多行插入技术掌握字符串函数、日期函数、数学函数、系统函数会应用多行插入技术重点难点字符串函数日期函数三种多行插入技术课时讲授方式教学过程作业2课时上机操作附于表后附于表后课堂总结掌握SQL Server2000中的函数用法；三种多行插入技术 一、字符串函数CHARINDEX(指定字符串参数,字符串参数,开始查找位置参数)用来寻找一个指定的字符串在另一个字符串中的起始位置。 练习1查找C#在你们的C#课程的起始位置，要求从第2个位置开始查找。 LEN(字符串参数)返回传递给它的字符串长度练习2查询返回My SQL课程的字符串长度LOWER(待转换字符串参数)把传递给它的字符串转换为小写练习3把MY SQL课程转换成小写，查询显示UPPER(待转换字符串参数)把传递给它的字符串转换为大写练习4把sql server课程转换成大写，查询显示LTRIM(字符串参数)清除字符左边的空格练习5把sql server课程字符左边的空格清除，查询显示RTRIM(字符串参数)清除字符右边的空格练习6把sql server课程字符右边的空格清除，查询显示RIGHT(指定字符串参数，返回字符个数参数)从字符串右边返回指定数目的字符练习7返回阿拉法.司马吉右边的3个字符，查询显示LEFT(指定字符串参数，返回字符个数参数)从字符串左边返回指定数目的字符练习8返回阿拉法.司马吉左边的3个字符，查询显示放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供REPLACE(指定字符串参数，被替换字符串参数，替换字符串参数)替换一个字符串中的字符练习9把莫乐可切.杨可中的可替换成米，查询显示STUFF(指定字符串参数，起始位置参数，长度参数，替换字符串参数)在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串练习10把ABCDEFG中的第2位置开始，4个字符长度的字符串替换为我的专业，查询显示 二、日期函数GETDATE()取得当前的系统日期练习11获取当前系统日期，查询显示DATEADD(日期部分,数值,日期)将指定的数值添加到指定的日期部分后的日期SELECT DATEADD(mm,4,99/01/02)练习12给01/02/1999的年份增加5，查询显示DATEDIFF(日期部分,日期1,日期2)两个日期之间的指定日期部分的区别练习13返回99/01/02与99/05/02的月份区别，查询显示DATENAME(日期部分,日期)日期中指定日期部分的字符串形式练习14返回01/01/2000的月份，查询显示DATEPART(日期部分,日期)日期中指定日期部分的整数形式练习15返回01/01/2000的月份，查询显示 三、数学函数ABS(数值表达式参数)取数值表达式的绝对值练习16求-43.5的绝对值，查询显示CEILING(数值表达式参数)返回大于或等于所给数字表达式的最小整数练习17求43.5的最小整数，查询显示；练习18求-43.5的最小整数，查询显示；FLOOR(数值表达式参数)取小于或等于指定表达式的最大整数练习19求43.5的最大整数，查询显示；练习20求-43.5的最大整数，查询显示；POWER(底数数值表达式，指数数值表达式)取数值表达式的幂值练习21求3的2次幂值，查询显示SIGN(数值表达式参数)取数值表达式的符号，对于正数返回+1，对于负数返回-1，对于0则返回0练习22求-43.5的符号，查询显示SQRT(数值表达式参数)取浮点表达式的平方根练习23求16的平方根，查询显示放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供 四、系统函数CONVERT(目标数据类型（长度），待转换数据)用来转变数据类型例如SELECT CONVERT(V ARCHAR (3),123)思考执行结果SELECT CONVERT(float,123)SELECT CONVERT(int,123.55)练习24将123456转换为可变长度字符数据类型且该数据类型的长度为6，查询显示练习25CURRENT_USER返回当前用户的名字例如SELECT CURRENT_USER DATALENGTH(字符串参数)返回用于指定字符串表达式的字节数练习26查询返回My SQL课程的字符串字节数*/练习27HOST_NAME()返回当前用户所登录的计算机名字例如SELECT HOST_NAME()练习28返回当前所登录的用户名称SELECT SYSTEM_USER练习29从给定的用户ID返回用户名SELECT USER_NAME (0)SELECT USER_NAME (1)SELECT USER_NAME (2)案例1某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”；请编写SQL语句实现以上要求；数据库表名Card；密码字段名PassWord；*/use bankdelete fromcard insert card values(01,黎明,0oI1)insertcardvalues(02,张明,0o23I1)SELECT*FROM CARD解决方法一Update CardSet PassWord=Replace(password,O,0)Update CardSet PassWord=Replace(password,I,1)解决方法二Update CardSet PassWord=Replace(Replace(password,O,0),I,1)案例2在数据库表中有以下字符数据，如13- 1、13- 2、13- 3、13- 10、13- 100、13- 108、13- 18、13- 11、13- 15、14- 1、14-2现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排需，输出要排成这样放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供13- 1、13- 2、13- 3、13- 10、13- 11、13- 15、13- 18、13- 100、13- 108、14- 1、14-2数据库表名SellRecord；字段名ListNumber；SELECT*FROM sellrecordinsert SellRecordvalues(13-1)insert SellRecordvalues(13-2)insert SellRecordvalues(13-3)insert SellRecordvalues(13-10)insert SellRecordvalues(13-100)insert SellRecordvalues(13-108)insert SellRecordvalues(13-18)insert SellRecordvalues(13-11)insert SellRecordvalues(13-15)insert SellRecordvalues(14-1)insert SellRecordvalues(14-2)案例2实现操作代码SELECT ListNumberFROM SellRecordORDER BYConvert(int,Left(ListNumber,CharIndex(-,ListNumber)-1),Convert(int,Stuff(ListNumber,1,Charindex(-,ListNumber),)多行插入技术 1、当新表中的表结构与旧表的表结构相同时，可以用下面语句把旧表中的所需数据一次性插入到新表中INSERT INTO(新表列名)SELECTFROM当新表TongXunLu中的表结构与旧表的表Studentinfo结构相同时，可以用下面语句把旧表中的所需数据一次性插入到新表中示例演示操作代码INSERT INTOTongXunLu(姓名,地址,电子邮件)SELECT SName,SAddress,SEmail FROMStudentinfo多行插入技术 2、/*我要对一张表作测试工作，但又不想破坏表的数据，怎么办？-把这张表复制出来SELECT源表列名INTOFROM示例演示操作代码SELECT Studentinfo.SName,Studentinfo.SAddress,Studentinfo.SEmail INTOTxl FROMStudentinfo放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供/*紧接上面的问题，当我复制了源表相应的列出来后，发现没有适合做主键的列，怎么？-插入新的标识列SELECT列名1，列名2，IDENTITY(数据类型,标识种子,标识增长量)AS列名INTO新表FROM原始表示例演示操作代码SELECT Studentinfo.SName,Studentinfo.SAddress,Studentinfo.SEmail,IDENTITY(int,1,1)As StudentIDINTO TongxlFROMStudentinfo多行插入技术 3、前面讲的多行插入都是有源数据存在，那么我想自己输入新的数据进去，但只想用一条Insert语句，能成功吗？INSERT INTO(列名)SELECTUNION SELECTUNION INSERTSTUDENTinfo2(SName,SGrade,SSex)SELECT测试女生1,7,女UNION SELECT测试女生2,7,女UNION SELECT测试男生4,7,男UNION SELECT测试男生5,7,男放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供课次章节课题教学目的和要求实验七数据查询 (2)第7次实践课模糊查询、聚合函数、分组查询及HAVING子句掌握使用LIKE、BETWEEN、IN进行模糊查询掌握在查询中使用聚合函数掌握使用GROUP BY进行分组查询及使用HAVING子句筛选重点难点模糊查询、聚合函数、分组查询课时讲授方式教学过程作业2课时上机操作附于表后附于表后课堂总结查询通用格式；模糊查询、聚合函数、分组查询本次试验的素材数据库students；数据库表studentInfo数据库表StuCourse数据库表StuScore放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供实验七操作实验练习 一、使用LIKE关键字进行模糊查询格式SELECT列名列表FROM表名WHERE列名LIKE模糊列名值练习1查询所有非08级且学号尾数为3或者4的学生基本信息练习2查询所有湖南的学生基本信息练习3查询姓李的学生，显示姓名、性别、联系方式练习4查询所有非06级的学生基本信息 二、使用NOT LIKE关键字进行模糊查询练习5查询所有非07级的学生基本信息练习6查询所有非湖北的学生基本信息，并按学号升序排列练习7查询没使用sina邮箱的学生信息，显示姓名、学号、邮箱 三、使用is null关键字进行模糊查询格式SELECT列名列表FROM表名WHERE列名IS NULL练习8查询地址为空的学生信息，显示姓名、地址 四、使用is notnull关键字进行模糊查询练习9查询地址不为空的学生信息且按年级升序排列，显示姓名、地址、年级 五、使用BETWEENAND关键字进行模糊查询格式SELECT列名列表FROM表名WHERE列名BETWEENAND练习10查询成绩在60与80分之间的学生成绩信息，显示学号、分数放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供 六、使用NOT BETWEENAND关键字进行模糊查询练习11查询成绩不在60与80分之间的学生成绩信息，显示学号、分数 七、使用IN关键字进行模糊查询格式SELECT列名列表FROM表名WHERE列名IN(列值列表)练习12查询学生地址在湖北武汉,湖南长沙,湖南株洲的学生信息，显示学生姓名、地址练习13查询班级为 08113、07113的学生信息，显示学生姓名、地址，显示学生姓名、班级 八、使用NOT IN关键字进行模糊查询练习14查询学生地址不在甘肃天水,湖北黄岗的学生信息，显示学生姓名、地址练习15查询不在 06113、 07113、08113的学生信息，显示学生姓名、班级 九、使用聚合函数查询MAX(列名)MIN(列名)AVG(列名)SUM(列名)COUNT(列名或*)练习16查询学号为0711302的学生成绩总和,显示总成绩练习17查询所有80分以上学生成绩的平均成绩,显示平均成绩练习18查询学生成绩表中的最高分、最低分,显示最高分，最低分练习19查询学生成绩表中的最高分、最低分,显示最高分，最低分练习20查询课程编号为C002的及格人数注意查询语句通用格式总结SELECT列名FROM表名WHERE条件GROUP BY列名HAVING分组后的筛选条件order by排序字段 十、使用GROUP BY分组查询练习21查询各门课程的平均成绩，显示课程编号、课程平均成绩练习22查询各门课程的平均成绩且按课程平均成绩降序排列，显示课程编号、课程平均成绩SELECT CourseID,AVG(Score)AS课程平均成绩FROM stuScoreGROUP BYCourseID order by AVG(Score)desc 十、使用HAVING子句筛选（分组后）练习23查询各门课程的平均成绩不低于70的课程平均成绩且按课程平均成绩降序排列，显示课程编号、课程平均成绩练习23操作代码SELECT CourseID,AVG(Score)AS课程平均成绩FROM stuScoreGROUP BYCourseID havingAVG(Score)70order byAVG(Score)desc放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供练习24查询各学生的成绩不低于70的科目数且科目数大于 1、按科目数降序排列，显示课程编号、课程平均成绩示例代码SELECT studentid,COUNT(*)FROM stuscoreWHERE score=70GROUP BYstudentid HAVINGCOUNT(*)1orderbyCOUNT(*)desc放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供课次章节课题教学目的和要求重点难点课时讲授方式教学过程作业第8次实践课实验八数据查询（多表联结查询）熟练掌握内联结(INNER JOIN)掌握外联结左外联结(LEFT JOIN)、右外联结(RIGHT JOIN)、完整外联结(FULL JOIN)了解交叉联结(CROSS JOIN)内联结查询、外联结查询2课时上机操作附于表后附于表后课堂总结总结内联结查询、外联结查询、交叉联结查询基本通用格式本次试验的素材数据库students；数据库表studentInfo(列名Sname、Scode、Sclass、Saddress、Semail、Sphone、SGrade、SSex)；数据库表StuCourse（列名CourseId、CourseName、BookName）；数据库表StuScore（列名ScoreId、StudentId、CourseId、Score）实验实训八上机内容 一、内联接多表联结查询内联结-1内联接一般语法格式一Select列名1,列名2,列名3?From表名1as别名1INNER JOIN表名2as别名1ON表名1.相同列1=表名2.相同列2练习1从学生信息表和学生成绩表中查询学生有关信息，显示姓名、学号、科目编号、成绩练习2从学生课程表和学生成绩表中查询学生有关信息，显示学号、科目编号、科目名称、成绩练习3执行分析以下两个语句，结果是否有区别语句 一、SELECT S.SName,c.studentid,C.CourseID,C.Score FromstuScore AS C INNER JOIN Studentinfo AS S放心减肥网.jianfei1595.提供放心减肥网.jianfei1595.提供ON C.StudentID=S.SCode语句 二、SELECT S.SName,c.studentid,C.CourseID,C.Score FromStudentinfo AS S INNER JOIN stuScore AS CON C.Stu