搜索
实验三：数据库数据的定义与简单查询操作一、实验目的熟练掌握建立数据库和表，向数据库输入数据、修改数据和删除数据的操作及简单的查询操作。二、实验内容和要求建立数据库并设计各表，输入多条实际数据，并实现数据的增、删、改操作。查询数据库中的表和表中各数据的定义。设计各种单表查询SQL语句。三、实验步骤创建学生管理数据库，数据库名为test，包含学生的基本信息，课程信息和选课信息。数据库xsgl包含下列3个表：(l）student：学生基本信息。(2）course：课程信息表。(3）sc：学生选课表。各表的结构分别如实验二的表1-1、表1-2和表1-3所示。1、数据库的建立：(1)用可视化界面建立：（a）在KingbaseES中用企业管理器-新建数据库；(2)命令方式建立：（a）在KingbaseES中，在查询分析器中使用SQL语句：创建“test”数据库：CreatedatabasetestwithEncoding=’GBK’;创建”xsgl”模式：Createschemaxsgl;2.表的建立：(1)用可视化界面建立：在KingbaseES中用企业管理器—数据库—test—表—右键—新建表;(2)命令方式建立：在KingbaseES中查询分析器的编辑窗口中用下列SQL语句:在“xsgl”模式中建立表格“student”、“course”、“sc”:SETSearch_pathToxsgl,public;Createtablestudent(snoCHAR(10)notnull,snameCHAR(10)notnull,ssexCHAR(2)notnull,sageintnotnull,sdeptCHAR(4)notnull,primarykey(sno));Createtablecourse(cnoCHAR(3),cnameCHAR(30),creditINT,pcnoCHAR(3)NULL,primarykey(cno));…….或者：Createtablexsgl.student(snoCHAR(10)notnull,snameCHAR(10)notnull,ssexCHAR(2)notnull,sageintnotnull,sdeptCHAR(4)notnull,primarykey(sno));Createtablexsgl.course(cnoCHAR(3),cnameCHAR(30),creditINT,pcnoCHAR(3)NULL,primarykey(cno));Createtablexsgl.sc(snoCHAR(10),cnoCHAR(3),gradeINTNULL,primarykey(sno,cno));3.表数据的添加:(1).用可视化方法:I:在KingbaseES中用企业管理器—数据库—test—表－表名—右键－打开表－返回所有行；输入下列数据：snosnamessexsagesdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS95005刘云女18CScnocnamecreditpcno1数据库452数学63信息系统31snocnograde9500119295001285950013889500229095002380950032859500415895004285(2).在KingbaseES查询分析器的编辑窗口中使用下列SQL语句插入数据:insertintoxsgl.student(sno,sname,ssex,sage,sdept)values('95001','李勇','男',20,'CS');insertintoxsgl.student(sno,sname,ssex,sage,sdept)values('95002','刘晨','女',19,'IS');insertintoxsgl.student(sno,sname,ssex,sage,sdept)values('95003','王敏','女',18,'MA');insertintoxsgl.student(sno,sname,ssex,sage,sdept)values('95004','张立','男',19,'IS');insertintoxsgl.student(sno,sname,ssex,sage,sdept)values('95005','刘云','女',18,'CS');insertintoxsgl.course(cno,cname,credit,pcno)values('1','数据库',4,'5');insertintoxsgl.course(cno,cname,credit,pcno)values('2','数学',6,null);insertintoxsgl.course(cno,cname,credit,pcno)values('3','信息系统',3,'1');insertintoxsgl.course(cno,cname,credit,pcno)values('4','操作系统',4,'6');insertintoxsgl.course(cno,cname,credit,pcno)values('5','数据结构',4,'7');insertintoxsgl.course(cno,cname,credit,pcno)values('6','数据处理',3,null);insertintoxsgl.course(cno,cname,credit,pcno)values('7','PASCAL语言',4,'6');insertintoxsgl.sc(sno,cno,grade)values('95001','1',92);insertintoxsgl.sc(sno,cno,grade)values('95001','2',85);insertintoxsgl.sc(sno,cno,grade)values('95001','3',88);insertintoxsgl.sc(sno,cno,grade)values('95002','2',90);insertintoxsgl.sc(sno,cno,grade)values('95002','3',80);insertintoxsgl.sc(sno,cno,grade)values('95003','2',85);insertintoxsgl.sc(sno,cno,grade)values('95004','1',58);insertintoxsgl.sc(sno,cno,grade)values('95004','2',85);4操作系统465数据结构476数据处理37PASCAL语言464.表数据的修改:(1).用可视化方法:在KingbaseES中用企业管理器—数据库—test—表－表名—右键－打开表－返回所有行；在表格中将相应的数据修改即可。(2)．命令方法：将所有学生的年龄增加一岁：updatexsgl.studentsetsage=sage+1将4号课程的学分改为4:updatexsgl.coursesetcredit=4wherecno=4设置7号课程没有先行课:updatexsgl.coursesetpcno=nullwherecno=7将95001号学生的1号课程的成绩增加3分:updatexsgl.scsetgrade=grade+3wheresno=95001andcno=15.表数据的删除:(1).用可视化方法:在KingbaseES中用企业管理器—数据库—test—表－表名—右键－打开表－返回所有行；单击左边的行标记,选定某一行,或单击后拖动选择相邻的多行,再右击鼠标选择弹出式菜单中的删除。(2)．命令方法：删除学号为95005的学生的记录：deletefromxsgl.studentwheresno=95005删除所有的课程记录：deletefromxsgl.course删除成绩为不及格（少于60分）的学生的选课记录：deletefromxsgl.scwheregrade606.简单查询在对象搜索器中查询数据库中的关系表及表中的各数据的属性、值。单表查询1.查询全体学生的学号和姓名:selectsno,snamefromstudent2.查询全体学生的所有信息:select*fromstudent或者selectsno,sname,ssex,sage,sdeptfromstudent3.查询全体学生的姓名,出生年份,和所在系,并用小写字母表示所有系名:selectsname,'出生年份为:',year(getdate())-sage,lower(sdept)fromstudent4.给上例的结果集指定列名:selectsname,'出生年份为:'出生,year(getdate())-sage年份,lower(sdept)系名fromstudent5.查询选修了课程的学生的学号:selectdistinctsnofromsc比较:selectsnofromsc6.查询年龄在20岁以下的学生的姓名及其年龄:selectsname,sagefromstudentwheresage207.查询考试成绩有不及格的学生的学号:selectdistinctsnofromscwheregrade60比较:selectsnofromscwheregrade608.查询年龄在20-30岁直接的学生的姓名,姓名,所在系:selectsname,ssex,sdeptfromstudentwheresagebetween20and309.查询IS,CS,MA系的所有学生的姓名和性别:selectsname,ssexfromstudentwheresdeptin('IS','MA','CS')10.查找所有姓’李’的学生的姓名,学号和性别:selectsname,sno,ssexfromstudentwheresnamelike'李%'比较:将学生表中的’95001’号学生的姓名’李勇’改为’李勇勇’,再执行:selectsname,sno,ssexfromstudentwheresnamelike'李_'11.查询没有先行课的课程的课程号cno和课程名cname:selectcno,cnamefromcoursewherepcnoisnull查询结果排序12.查询选修了3号课程的学生的学号和成绩,并按分数降序排列:selectsno,gradefromscwherecno='3'orderbygradeDESC13.查询全体学生的情况,查询结果按所在系号升序排列,同一系中的学生按年龄降序排列:select*fromstudentorderbysdeptASC,sageDESC聚集函数1.查询学生总人数：SelectCount(*)as学生总数fromstudent2.查询选修了课程的学生总数：selectcount(distinctsno)as选课学生总数fromsc3.查询所有课程的总学分数和平均学分数,以及最高学分和最低学分：selectsum(credit)as总credit,avg(credit)as课程平均学分,max(credit)as最高学分,min(credit)as最低学分fromcourse4.计算1号课程的学生的平均成绩,最高分和最低分:selectavg(grade)as平均成绩,max(grade)as最高分,min(grade)as最低分fromscwherecno='1'5.查询’信息系’(IS)学生”数据结构”课程的平均成绩:selectavg(grade)fromstudent,course,scwherestudent.sno=sc.snoandcourse.cno=sc.cnoandsdept='IS'andcname='数据结构'；分组查询1.查询各系的学生的人数并按人数从多到少排序:selectsdept,Count(*)as人数f