实验一 使用对象资源管理器创建库和数据表

 一、实验目的和要求：                                                

1、要求学生掌握用对象资源管理器创建数据库的方法并建一些约束；

2、使用企业管理器输入数据。   

    

二、实验内容：                                                 

1．用对象资源管理器创建Student数据库；

2. 在该数据库中用对象资源管理器中创建四个表（student ,teacher,course,s_c ）：           

3.为各表设置表的主键 ；

4、并录入表中的数据。             

建议：在定义表中字段时，尽量使用意义的英文单词。

三、实验器材：

微机+操作系统+SQL SERVER2012。    

四、实验步骤：                                                

1.启动SQLSERVER 2012 ，利用对象资源管理器创建数据库student，并在该数据库中按要求创建如下四张表，具体要求如下：                                      

“教师表”表名：teacher，其结构如下：                      

字段名称	字段类型	字段大小/格式	是否可为空
教师号	varchar	8	否
教师名	varchar	8	是
职称	varchar	10	是
所属院系统	varchar	20	是
邮件地址	varchar	20	是
电话号码	varchar	15	是
出生年月	Datetiime		是
基本工资	decimal	小数位数2	是
奖金	decimal	小数位数2	是

“学生表”表名：student，其结构如下：                

字段名称	字段类型	字段大小/格式	是否可为空
学生号	varchar	8	否
学生名	varchar	8	是
院系	varchar	20	是
年龄	int		是
出生年月	datetime		是
邮箱	varchar	20	是
家庭地址	varchar	40	是

“课程表”表名：course，其结构如下：                      

字段名称	字段类型	字段大小/格式	是否可为空
课程号	varchar	8	否
课程名	varchar	30	是
先修课程号	varchar	8	是
学分	Decimal	小数位数1	是

“选课表” 表名：sc，其结构如下：                          

字段名称	字段类型	字段大小/格式	是否可为空
学生号	varchar	8	否
课程号	varchar	8	否
成绩	int		是
教师号	varchar	8	否

2. 打开对象资源管理器，右击相应的表，单击出现的快捷菜单选择打开有返回所有行，对相应的表录入数据。

 

五、实验结果

1.按要求用企业管理器建立了四张的结构，并分别设置了主键。

2.对四张表分别录入了数据。

 

六、实验小结：

 

实验二 使用SQL创建数据表及管理数据表

 

一、实验目的和要求：                                                

1.掌握在SQL SERVER 中使用SQL命令创建数据表，熟练掌握create table 命令的使用，
掌握 主键约束，外键约束，唯一约束，检查约束，默认约束，空、非空约束的使用

2.掌握在SQL SERVER 中使用SQL命令修改数据表 ，掌握alter table命令添加列，添加完成性约束，删除列，删除完整性约束，修改列命令的使用  

             

二、实验内容：                                                 

1.使用命令在教学数据库下，分别按以下要求建书上26页的五个关系，设置对应表的主键，外键和约束关系。                  

(1)建学生表S，要求对SNO建主键，性别SEX默认值为‘男’；

(2)建教师关系T，要求对TNO建主键，年龄的取值范围为20至65岁；

(3)建课程关系C，要求对CNO建主键，课程名CN建惟一约束且不允许空值。

(4)建选课关系SC，要求对SNO，CNO分别建外部键，并对SNO+CNO为主键；

(5)建授课关系TC，要求建TNO+CNO为主键，并对TNO，CNO分别为外部键。

2、使用SQL命令，通过ALTER TABLE方式完成以下表格的修改

（1）．给s表添加手机号列，家庭住址列，身份证号要求手机号只能是0-9之间的字符，且长度为11位，家庭住址不能为空，身份证号要求不能为空，且不能重复

（2）、给s表添加一唯一约束到姓名列，一默认约束到姓别列，要求默认值为“男”

（3）．删除t表中的prof列上的约束

（4）、删除s表与sc 表之间的关联，再通过命令重新建立其关联

 

三、实验器材：

微机+操作系统+SQL SERVER2012。

 

四、实验步骤                                                 

1.选择查询分析器组件，连接到服务器；                      

2.进入查询分析器界面后分别通过CREATE、ALTER、DROP命令创建数据表及管理表；

3.进入查询分析器分别用CREATE、DROP命令创建及删除索引。

 

五、实验结果

1、使用命令在教学数据库下，分别按以下要求建书上27页的五个关系，设置对应表的主键，外键和约束关系。  

(1)create table s

(sno char(6)  constraint s_prim primary key,

   sn varchar(10),

   sex char(2) default '男',

   age int,

   dept varchar(30))

(2)create table t

(tno char(6) primary key,

   tn varchar(10),

   sex char(2),

   age int  constraint t_ck  check(age>=20 and age<=65),

   prof char(8),

   sal smallmoney,

   comm smallmoney,

   dept varchar(20))

(3)create table c

(cno char(6) primary key,

 cn varchar(20) unique not null,

    ct int)

(4)create table sc

(sno char(6) foreign key references s(sno),

    cno char(6) foreign key references c(cno),

    score numeric(4,1)

    primary key(sno,cno))

(5)create table tc

(tno char(6) foreign key references t(tno),

 cno char(6) foreign key references c(cno),

   primary key(tno,cno)) 

2、

（1））alter table s

add tel char(11) check (tel like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

addr varchar(50) not null,

sfzh varchar(20) not null unique

（2）alter table s

add unique(sn),

default '男' for sex

（3）alter table t

drop constraint xx

（4）alter table sc

drop constraint 外键约束的名称

 

alter table sc

add foreign key (sno) references s(sno)

 

六、实验小结：                                               

实验三 简单查询

一、实验目的和要求：                                                

1. 要求掌握SQL查询的基本格式；

2．学会查询中条件运算符的使用。

               

二、实验内容：                                                  

1.查询教师T表中的所有记录信息。                             

2.查询全体教师的教师号、姓名、职称信息。                  

3.查询选修了课程的学生号。                                   

4.查询选修了课程号为’c1’的学生的学号和成绩。                   

5.查询教师表中工资高于1500分的教师的教师号、姓名、工资。

6.查询岗位津贴不在1500至2000之间的教师号、姓名、职称。                                                       

7.查询选修了C2和C3的学生的学号、课程号和成绩。         

8.查询所有姓赵的学生的全部信息。                         

9.查询缺考的学生的学号及相应的课程号。

10．查所所有学生的姓名及出生年份,并为对应列给别名

11．查询姓王或李的学生姓名和所在系

12．查询所有选了课的学生学号，要求选了多门课的学生学号只显示一次

13．查询分数为80,90分的学生选课记录

14．查询以'编'开头的,且倒数第2个为'原'的课程信息

 

三、实验器材：

微机+操作系统+SQL SERVER12。

 

四、实验步骤：                                                

 1.启动SQL  SERVER 中的单击新建查询。                

 2.输入对应的SQL 查询语句。                        

 五、实验结果：

1.Select *   From  t                                   

 2.Select  tno,tn,prof   From  t                           

 3.Select distinct  sno  From  sc                         

 4.Select sno,score   From sc   Where cno='c1'                 

 5.Select tno,tn,sal  From tb   Where sal>=1500                

 6.Select tno,tn,prof  From t  Where comm not Between 1500 and 2000                                                   

 7.Select sno,cno,score  From sc  Where cno in('c2','c3')         

 8.Select *  From s  Where sn like'赵%'                      

 9.Select sno,cno   From sc   Where score is null    

 10.select Sn,year(GETDATE()) 出生年份 from s

11.select Sn,dept from s where Sn like '[王李]%'

12.select distinct sno from Sc

13.select * from Sc where score in (80,90)

14.select * from C where cn like '编%原__%'       

 

六、实验小结：

               

实验四 复杂查询

                    

一、实验项目和要求：                                            

1．学会使用常用函数对查询进行汇总；

2．掌握分组查询和排序查询的SQL查询的格式的使用。

             

二、实验内容：                                                  

• 统计自动化系的学生的人数。                             
• 统计所有教师的平均工资。
• 求选修C1号课程的最高分、最低分及之间的相差的分数。
• 查询全体学生情况，查询结果按所在系升序排列，系相同按学生年龄降序排列。                                          
• 查询选修了3门以上课程的学生学号。                      
• 查询有3人以上选修的课程号。  
• 查询每位教师的教号及其任课门数
• 求有三门以上选课成绩及格的学生的学号及其总成绩同，查询结果按总成绩降序列出。

 

三、实验器材：

微机+操作系统+SQL SERVER。

 

四、实验步骤：                                                

1.启动SQL  SERVER 中的单击新建查询。                

 2.输入对应的SQL 查询语句。

                                 

五、实验结果：

• select count(*) as 自动系人数 From s  Where dept='自动化'
• select avg(sal) as 平均工资  From t
• select max(score) as 最高分,min(score) as 最低分，max(score)-min(score) as 差 from sc where cno=’c1’
• select *  from s  order by dept,age desc
• select sno  from sc  group by sno  having count(*)>3
• select cno   from sc   group by cno   having count(sno)>3)
• select tno,count(*) as 任课门数 from tc group by tno
• select sno,sum(score) as 总分from sc  where score>=60  group by sno  having count(*)>=3 order by sum(score) desc

 

六、实验小结：

实验五 子查询及连接查询

                              

一、实验目的和要求：                                                

• 要求学会使用嵌套查询；
• 要求掌握内连接查询的格式；
• 理解外连接查询。  

                 

二、实验内容：                                                

1．查询每个学生的选课情况。（列出学号，姓名，课程号，课程名）                                                    

2．查询既选修了1号课又选修了3号课的学生学号              

3．查询至少有两人选修的课程号（用自身连接）。              

4．查询选修2号课且成绩在90分以上的学生学号和姓名。        

5．查询选修了“数据库”课且成绩不及格的学生姓名。          

6．查询所有选课学生的学号、姓名、选课名称及成绩。         

7．查询所有学生姓名，年龄和选课名称。

8．查询所有选修了课的及一门课也没选的学生学号，姓名，课程号。（用左外连接s表为左表，SC为右表）。      

9.查询与“赵亦”同学在一个系学习的学生学号、姓名及所在系。

10.查询选修了“微机原理”课程的学生学号和姓名。           

11.查询其他系中比信息系某一学生年龄小的学生姓名和年龄。   

12.查询其他系中比信息系所有学生年龄都小的学生姓名和年龄。  

13.查询没有选修1号课的学生姓名。                         

14.查询选修了全部课程的学生姓名。                         

15.查询至少选修了学生s1选修的全部课程的学生学号,并存储到表s1_tablek中。   

 

三、实验器材：

微机+操作系统+SQL SERVER2012。

 

四、实验步骤：                                                

1.启动SQL  SERVER 中的单击新建查询。                

 2.输入对应的SQL 查询语句。

 

五、实验结果：

1.select s.sno,sn,c.cno,cn  from s,c,sc  where s.sno=sc.sno and c.cno=sc.cno                        

2.select x.sno  from sc as x,sc as y  where x.sno=y.sno and x.cno='c1' and y.cno='c3'                

3.select cno,count(sno) as 选课人数 from sc  group by cno   having count(sno)>=2                      

4.select s.sno,sn    from  s,sc  where s.sno=sc.sno and cno='c2' and score>=90                   

5.select sn  from s,sc,c  where s.sno=sc.sno and c.cno=sc.cno and score<60 and cn='数据库'                   

6.select s.sno,sn,cn,score   from s,sc,c where s.sno=sc.sno and c.cno=sc.cno                         

7.select sn,age,cn   from s,sc,c  where s.sno=sc.sno and c.cno=sc.cno                                                

8.select s.sno,sn,sc.cno  From s left join sc on s.sno=sc.sno    

9.select sno,sn,dept  From s  Where dept=(select dept  From s  Where sn='赵亦')                             

10.select sno,sn  From s   Where  sno in (select sno  From sc  Where cno = (Select cno  From c Where cn='微机原理'))  

11.select sn,age  From s Where age < any(Select age  From s  Where dept='信息') and dept<>'信息'  

12.select sn,age  From s Where age < all(Select age  From s  Where dept='信息') and dept<>'信息'                            

13.select sn From s  Where sno not in  (select sno  From sc   Where cno='c1')                              

或 select sn  From s   Where not exists  (select *   From sc   Where sno=s.sno and cno='c1')             

14.select sn from  s where (not exists  ( select *   from  c  where not exitsts( select *   from sc  where SNo = S.SNo AND CNo = C.CNo)))          

15.select sno into s1_table   from S   where not exists    (select cno   from SC  where sno='s1' and cno not in  (select cno   from SC  where S.sno=sno)) 

                                 

六、实验小结：

                  

                                                          

 

实验六  视图和数据操纵

一、实验项目和要求：                                            

• 熟练掌握SQL语句对数据的插入、删除、修改操作；
• 会使用SQL语句创建视图、删除视图。

              

二、实验内容：                                                

• 建立选修了1号课程的学生视图stu_01312_1（学号，姓名，选修课，成绩）;                                              
• 建立计算机系选修了1号课程并且成绩不及格的学生视图stu_01312_2（学号，姓名，选修课，成绩）；                       
• 建立视图stu_year,由学生学号、姓名、出生年份组成；            
• 删除视图stu_year                                          
• 设计一张学生补考缴费视图，包括学生的学号及应缴纳费用（假设每人每门课不及格需缴纳补考费30元）。                         
• 建立课程汇总视图，包含课程号、选课人数、平均分、最高分和最低分。                                               
• 建立学生情况视图，包含学号，选课门数，最高分和最低分。
• s3同学的c5,c6,c7 三门课程的成绩分别是75，80，67 ，如何一次性将这些信息添加到sc表中 ？
• 将“计算机”系全体学生的成绩置零；
• 删除“计算机系”系全体学生的选课记录；
• 删除学号为“S1”的相关信息；并注意跟这个学生相关信息的变化
• 将学号为“S2”的学生的学号修改为“S002”；并注意跟这个学生相关信息的变化

13.把选修了课程名为“数据结构”的学生的成绩提高10%；

14.把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高5%；

15.把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉；

三、实验器材：

微机+操作系统+SQL SERVER。

四、实验步骤：                                                

   1.启动SQL  SERVER 中的单击新建查询。                

 2.输入对应的SQL 查询语句。                         

五、实验结果：                                                        

• create view stu_01312_1  as select s.sno,sn,cno,score  from s,sc   where s.sno=sc.sno and cno='c1'                      
• create view stu_01312_2 as select s.sno,sn,cno,score from s,sc where s.sno=sc.sno and cno='c1' and score<60 and dept=’计算机’     
• create view stu_year(sno,sname,birthyear)   as select sno,sn,2011-age    from s                                  
• drop view stu_year                                       
• create view jh1(学号,补考费用)  as select sno,count(*)*30 from sc  where score<60   group by sno                         
• create view xk1(课程号,选课人数,平均分,最高分,最低分) as   select cno,count(*),avg(score),max(score),min(score)  from sc  group by  cno                                    
• create view xk2(学号,选课门数,最高分,最低分) As select sno,count(cno),max(score),min(score)   from sc  group by sno     
• insert into SC (SNO,CNO,score)

select 's3','c5','75'

union

select 's3','c6','80'

union

select 's3','c7','67'

 

 

• update SC

set SCORE=0

where SNO in(select SNO from s where dept='计算机')

• delete from SC where SNO in (select SNO from s where DEPT='计算机')
• delete from s where SNO='s1'
• update s

set SNO='s002'

where SNO='s2'

13.update SC

set SCORE=1.1*score  where cno in (select cno from c where CN='数据结构' )

14.update SC

set SCORE=SCORE*1.05

where CNO='c2' and SCORE<(select AVG(SCORE) from SC where CNO='c2')

15.delete from sc

where CNO='c2' and SCORE<(select AVG(SCORE) from SC where CNO='c2')

六、 实验小结：