学院数据教案.docx
《学院数据教案.docx》由会员分享，可在线阅读，更多相关《学院数据教案.docx（27页珍藏版）》请在冰点文库上搜索。
学院数据教案
P14表间记录的对应关系
1一对一:
例：
A表为职工基本信息表
编号姓名性别职称电话
01张三男高工44554
02李四女副高54443
03王二男中级34343
….
注：
此表中默认编号为主码
B表为一月工资表
编号基本工资奖金总工资
01450500
02550300
03800260
总结:
A表中任何一个记录在B表中最多存在1次，反之一样.
2一对多:
A表中一个记录可以在B中存放N个记录对应，N>1
例：
A表为职工基本信息表
编号姓名性别职称电话部门
01张三男高工44554人事部
02李四女副高54443生产部
03王二男中级34343销售部
….
B表为全年工资表
编号月份基本工资奖金总工资
011450500
021550300
031800260
012450500
022550300
032800260
….
C员工销售情况表
编号品名数量单价总金额日间Tel买家
01A50
01B20
02A30
02C10
03B15
01C60
01C10
3多对多
学生选课实例
例：
教师表A（张明，李明，王三）
教师编号姓名职称专业所带学生
T01张明教授计科A
T01张明教授计科B
T01张明教授计科C
T02李明副教授信科A
T02李明副教授信科B
T03王三副教授计科A
T03王三副教授计科B
T03王三副教授计科C
学生表B（S01，S02，S03，S04）
学号课程课程编号教师编号
S01大学英语D01T01
S01大学物理D02T02
S01高数D03T03
S02大学英语D01T01
S03大学英语D01T01
S04大学英语D01T01
拓展：
一个学生可以选多门课程，一个教师可以上多门课，一门课可以不同的多个老师上
作业：
制作多个表，完成表的设计.（特别注意各表的码的设定）
要求各表有多个记录数据
安装之前：
对自己的计算机的C盘做镜像GHOST，一键还原
BAIDU：
VS2008SQL2008安装
安装之后，另外再镜像一份
以上情况都是二元联系（两个表操作）
下面看多元联系（多个表操作）
实例：
A表
学号姓名…
01aaaa
02bbb
03cccc
04dddd
05eeeee
B表
学号语文数学英语
01566565
02784455
03899977
04566566
05566565必修课，每个学生的课程一样
C表
学号籍贯
01四川成都
02四川双流
03四川南江
04江西南昌
05四川成都
实例2：
A表
学号姓名…
01aaa
02Bbb
03cccc
04dddd
B：
学号课程号成绩
01D0178
01D0288
02D0160
02D030
03D1070
02D0290适合选修（销售情况）
…
E-R图
作业2:
以P18的图1-15为准，设计出该系统的各数据表.要求各表有若干记录，并体现出表之间的联系。
最后再在表前附上自己的E-R图。
上机时间:
双周的星期二上午34节
地点:
1213
准备优盘:
上机实验后,上交实验内容
P202.2建立数据库（第2周星期二上机实验内容相关）
SQL中提供两种操作方法:
一是鼠标方式SSMS,二是命令方式（T_SQL）
补:
启动SQLSERVER:
\
在用户名和密码都确保正确的情况下,无法打开软件,可能是sqlserver服务器没打开。
解决方法：
打开上图的“配置工具”---配置管理器;把里面的两个sqlserver服务器打开（右击它选“启动”）
2.2.2数据类型
1整数型:
数学中整数.
一般使用intsmallint
2浮点数（小数）：
float
3货币:
也是数值型,money
4日期时间型:
datetime
2012-2-2814:
41:
35
小日期型：
smalldatetime:
只有日期，没有时间（最适合生日，进货日期。
。
。
。
）
5字符型：
用于各个国家的文字，编号类的数字等
Char（n）:
定长串（常用于姓名，籍贯等这种文字内容少的列）
Varchar（n）:
变长串（常用于备注，论坛的贴子内容）
TEXT：
存放天文数字的文字内容。
6二进制型：
一般用于照片
Binary（n）
7图像型:
image:
8位型bit:
存放二值状态，1真true,0假false
如何创建一个表？
xs
学号姓名性别身高籍贯团员否照片备注
步骤：
1建立一个数据库：
在SQL工作窗口，选中左上边的“数据库”右击选“新建数据库”.给数据库取名（如学生系统）
2在数据库建立表:
xs
打开刚才的：
学生系统数据库，用鼠标右击“表”选“新建表”
注意：
完成创建表后，记住保存
注意：
如何把作业数据库复制出来上交或保存：
要停止服务器：
在SQL的配置工具中，把刚才启动的服务关闭，就可以把数据库复制出去。
（如何找到自己的数据库文件存放在哪里？
：
在SQL窗口中右击本数据，选“属性”—“文件”就可以看到文件存放位置的文件）
如何向表中添加新记录内容？
右击表选“编辑前200行”，根据建表时的类型，录入数据。
一行记录完成后要执行操作：
点“！
”或按F5.此时就把录入数据保存到数据库。
数据查询
查询是SQL的核心内容
基本格式:
Select数据项from表[where条件]
数据项可以是常量,变量,表达式,表的字段名,可以是多个
表可是一个或多个（如果为多表,则是表间操作）
1数据项:
select5+3
declare@xint//定义一个整型变量x
set@x=100//给x赋值
select@x+2//查询时使用变量x
selectGETDATE（）+100//求今天后100是哪一天
selectyear（GETDATE（））//取出今天的年份
select姓名,year（GETDATE（））-YEAR（出生日期）from学生
//求出学生表中所有人的姓名和年龄
update学生set年龄=YEAR（GETDATE（））-YEAR（出生日期）
//计算学生表中所有人的年龄并填充到年龄字段中
设学生表中的出生日期字段,选出生日是今天的人的姓名
Select姓名
select姓名from学生whereMONTH（出生日期）=month（getdate（））andDAY（出生日期）=day（getdate（））
例:
求张立比李勇大多少岁?
declare@xint
declare@yint
select@x=year（getdate（））-year（出生日期）from学生where姓名='张立'
select@y=year（getdate（））-year（出生日期）from学生where姓名='李勇'
select@x-@y
例:
求学生全班人的平均年龄是多大?
declare@pjfloat
select@pj=avg（year（getdate（））-year（出生日期））from学生
select'平均年龄是:
',@pj
select'欢迎您的到来,今天是:
',getdate（）
P41第四章检索数据（数据查询）
Select数据项from表[where条件]
Where条件:
结果是逻辑值都可以作为条件,如果为真,表示该记录
参与操作,为假则不操作.
AANDB:
逻辑与运算,表示只有,A,B同时成立时,结果才为真
Aorb:
逻辑或运算,表示只要a,b中至少有一个成立时,结果就成立
Nota:
逻辑非运算,表示求A的反条件
Anorb:
逻辑异或.相异则成立
Select数据项from表[where条件][orderby列名][asc/desc]
说明:
orderby列名:
表示查询的结果按指定的列排序,默认为ASC升序,如果要降序则加上desc.
select姓名from学生orderby姓名desc
排序依据:
（1）字符串:
按拼音顺序（新华字典顺序）
（2）数值:
按数学大小顺序排
select姓名,身高from学生orderby身高desc
（3）日期:
是今天比昨天大.（跟年龄相反）
select姓名,身高,出生日期from学生orderby出生日期desc//按出生日期降序排（年龄越大则在上面）
selecttop3姓名,身高,出生日期from学生orderby出生日期desc
//取年龄最大的前三名
selecttop50percent姓名,身高,出生日期from学生orderby出生日期desc
求年龄较大的一半人的情况,50%
（4）逻辑值:
真比假大
select姓名,身高,出生日期,党员否from学生orderby党员否//按党员否逻辑值排序
注意事项:
1*可代表所有字段列:
select*from学生orderby党员否
附:
select姓名+性别from学生//字段名是以拼凑的
Select姓名,语文+数学+英语from成绩//求每个人的总成绩
张三215
李四276
.........
2as关键字:
给查询值或表另取一个名字（别名）.
select姓名,年龄+10as十年后年龄from学生
select姓名,年龄+10十年后年龄from学生
//给年龄+10的计算结果另取名字”十年后年龄”.注:
as是可以省掉的.
Select姓名,语文+数学+英语as总分from成绩
补:
用select查询的结果去向表中填充数据.
insertinto学生（学号,姓名,性别）select学号,姓名,性别from学生where性别='男'
13新建两个表”学生1”,学生2,,把学生表中的所有男生复制到学生1表中;
把所有女生团员复制到学生2表中
答案:
Createtable学生1（学号nchar（5）,姓名nchar（6））
Createtable学生2（学号nchar（5）,姓名nchar（6））
Insertinto学生1（学号,姓名）select学号,姓名from学生where性别=’男’
Insertinto学生2（学号,姓名）select学号,姓名from学生;
where性别=’女’and团员否=1
如何把学生1,学生2两个表合并成学生3?
Createtable学生3（学号nchar（5）,姓名nchar（6））
Insertinto学生3select*from学生1
Insertinto学生3select*from学生2
一:
查询时的一些控制杂项
1distinct可以控制查询结果没有重复值
例:
查询出学生表中性别有几类
selectdistinct性别from学生
例:
问学生表中有哪几种姓氏?
selectdistinct（LEFT（姓名,1））as姓氏from学生
selectdistinctLEFT（姓名,1）as姓氏from学生
例:
问学生表中有几种姓氏?
selectCOUNT（distinct（LEFT（姓名,1）））as姓氏数from学生
补:
groupby字段列名
此子句功能是分组计算,统计数据.
例:
统计学生表中的男女生的各自人数
Select姓名from学生‘显示出全班每个人的性别8个男女
selectcount（性别）from学生’性别数就是人数
select性别,count（性别）as人数from学生groupby性别
按性别分组groupby性别,每一组内要分别实现count（）运算.
例:
统计学生表中党员和非党员各自人数
Select党员否,count（党员否）from学生groupby党员否
例:
问学生表中各种姓氏有几人?
createtablecnt（姓氏nchar
（2）,人数int）
insertintocnt（姓氏）selectLEFT（姓名,1）as姓氏from学生
select姓氏,COUNT（姓氏）as人数fromcntgroupby姓氏
droptablecnt
为什么要利用中间表cnt?
因为groupby表内的字段名,而姓氏不是字段名（
姓名才是字段名）,故把每个人的姓氏选出来存放到CNT临时表中,再对CNT临时
表来groupby姓氏
二.查询条件的常用运算符
关系比较:
=><<>!
=>=<=
范围运算:
[not]betweenAandB
判断指定的值是否在A,B之间
例:
查出身高在[1.70,1.80）的学生情况
select*from学生where身高between1.70and1.80
select*from学生where身高>=1.70and身高<=1.80
例:
查出所以介于’王’和’张’之间的姓的人（汉字是以拼音顺序为准的）
select*from学生where姓名between'王'and'张'
select*from学生where姓名notbetween'王'and'张'
集合运算:
[not]in（集合）:
等效于or逻辑运算
例:
查出学生表中所有姓王,张,杨的人
select*from学生whereleft（姓名,1）='王'orLEFT（姓名,1）='张';
orLEFT（姓名,1）='杨'
select*from学生whereleft（姓名,1）in（'王','张','杨'）
例:
查出成绩表中所有选了c01,c03,c05课程的学生成绩情况
select*from成绩where课程号in（'c01','c03','c05'）
例:
查出成绩表中所有选了c01,c03,c05课程的各自人数
select课程号,COUNT（学号）as人数from成绩where;
课程号in（'c01','c03','c05'）groupby课程号
例:
求成绩表中每个学生选了几门课?
select学号,COUNT（*）as门数from成绩groupby学号
例:
查出成绩表中选课门数在3门以上的学生姓名有哪能些?
select学号,COUNT（*）as门数from成绩groupby学号;
havingCOUNT（*）>=3
select姓名from学生表where学号in（select学号as;
门数from成绩表groupby学号havingCOUNT（*）>=3）
此为SQL语句的select查询嵌套:
把子查询的查询结果作为一个集合
这个集合再作为主查询的条件
例:
求计算机网络基础和大学英语两门课程的成绩情况
select*from成绩表where课程号in（select课程号from课程表where名称in（'计算机网络基础','大学英语'））
select*from成绩表where学号in
（select学号from学生表where姓名
in（'李大方','李四方','杨'））
统计:
count（）,avg（）,max（）,min（）,sum（）
（1）只要有了分组groupby,而且使用查询统计函数（COUNT,AVG,SUM…）
则每一个小组要分别统计计算.常用于分类汇总.
select课程号,MAX（成绩）as最高,MIN（成绩）最低,COUNT（*）as人数,
AVG（成绩）from成绩表groupby课程号
例:
求学生表中男女生的各自平均成绩
select性别,avg（语文）from学生表groupby性别
例:
求男生在全部的百分比是多大?
declare@zrsint,@nfloat
select@zrs=COUNT（*）from学生表
select@n=COUNT（*）from学生表where性别='男'
selectconvert（nchar（3）,@n/@zrs*100）+'%'
注意:
用一个变量是无法保存多个值,只是保存最后一个有效值,如:
declare@xmnchar（10）
select@xm=姓名from学生表where性别='男'
select@xm
看到的结果是最后一个男生的姓名
注:
单值可以保存在变量,多值一般建立临时表
例:
成绩表中每科的平均成绩.
select课程号,AVG（成绩）as平均from成绩表groupby课程号
select学号,SUM（成绩）from成绩表groupby学号
求每个学生生所有选课的总分
select性别,max（身高）as最高from学生表groupby性别
分别求男女生的最高
select性别,max（身高）as最高,MIN（身高）as最低,AVG（身高）as平均from学生表groupby性别
一次性可多项统计.
例:
求李小芳同学的所有选修课程的平均成绩
方法1
declare@xhnchar（11）
select@xh=学号from学生表where姓名='李四方'
select学号,AVG（成绩）from成绩表where学号=@xhgroupby学号
注意:
如果不分组groupby则无法查出非统计项”学号”
Where和groupby同时使用，where在前,groupby在后:
选用Where在表中挑选出数据,再整合排列分组,再统计汇总
定义的变量类型要和实际查询结果保存数据的宽度符合,如果是字符串型,长度>=实际长度.
方法2
declare@xhnchar（11）
declare@msint
declare@sint
select@xh=学号from学生表where姓名='李四方'//求出此人的学号
select@ms=COUNT（*）from成绩表where学号=@xh//求出此人选课门数
select@s=SUM（成绩）from成绩表where学号=@xh//求此人的所有课程总分
select@s/@ms//总分/门数
方法3:
select学号,AVG（成绩）from成绩表where学号=
（select学号from学生表where姓名='李四方'）groupby学号
例:
select学号,convert（numeric（4,1）,AVG（成绩））from成绩表where学号in
（select学号from学生表where姓名='李四方'or姓名='李小芳'）groupby学号
说明:
子查询是一个单值时,前面匹配用=,in;如果多值集合,要用in,=some,=any
例:
求出”计算机网络基础”课程的最高分
declare@kchnchar（3）
select@kch=课程号from课程表where名称='计算机网络基础'
select课程号,MAX（成绩）from成绩表where;
课程号=@kchgroupby课程号
例:
求李四方的大学英语成绩
declare@kchnchar（3）
declare@xhnchar（11）
select@kch=课程号from课程表where名称='大学英语'
select@xh=学号from学生表where姓名='李四方'
select学号+'李四方'+课程号+':
大学英语:
'+;
CONVERT（nchar（5）,成绩）from成绩表where课程号=@kchand学号=@xh
说明:
having统计结果条件:
对分组统计后的结果进行条件限制.
（having不能对表中原始字段进行条件限制,因为表中字段的限制使用
Where字段条件）
select课程号,COUNT（*）from成绩表groupby课程号;
havingCOUNT（*）>6
例:
求出成绩表中平均成绩在85以上的有哪些课程号?
select课程号,AVG（成绩）from成绩表groupby课程号;
havingAVG（成绩）>=85
例:
求学生表中姓氏在2人以内的有哪些姓氏?
createtablecnt（姓氏nchar
（2））
insertintocntselectLEFT（姓名,1）from学生
select姓氏,COUNT（姓氏）fromcntgroupby姓氏;
havingcount（姓氏）<=2
droptablecnt
第8周内容:
1表的复制补充:
一般有两种办法
（1）建立一新表结构,再insertinto新表selectfrom原表
（2）在高版本的sql中支持直接复制功能,如下
select学号,姓名,性别intomy1from学生表where性别='男'
into新表名:
子句只能放在select的字段名后面.
2约束特例:
如控制区号是三位或四位数字的方法:
createtabletest2（区号varchar（5）,constraint区号CHECK（区号LIKE'[0-9][0-9][0-9]'or区号LIKE'[0-9][0-9][0-9][0-9]'）,地区名char（10））
or
createtabletest4（区号varchar（5）,地区名char（10）notnullunique,
CHECK（区号LIKE'[0-9][0-9][0-9]'
or区号LIKE'[0,1,2,3,4,5,6,7,8,9][0-9][0-9][0-9]'andlen（地区名）>=3））
%_
升级会员 