1、(封皮页)北京理工大学数据库实验 1主讲老师：赵晓林学生：李经2012/11/20(目录页)实验 1： 建立数据库 11.1 作业声明 11.2 实验目的 11.2.1 熟悉数据库环境、建立表、插入数据、删除数据 11.3 实验过程 11.3.1 建立“学籍与成绩管理系统”表格 11.3.2 使用 “INSERT ”语句向主表插入至少 5 行数据，向子表插入至少 30 行数据； 21.3.3 在子表中输入含有不存在外键值的数据； 41.3.4 使用 SELECT * FROM 观察数据； 41.3.5 更新部分学生的学籍情况、成绩 41.3.6 删除部分同学的学籍信息 51.3.7 使用所有学
2、习的查询语句对表格的进行查询（自拟题目） 51.4 实验结论 71.5 实验体会 70实验 1： 建立数据库1.1作业声明本次作业全部由本人完成，若存在抄袭或雷同现象，本人愿意接受老师相应的处理1.2实验目的1.2.1 熟悉数据库环境、建立表、插入数据、删除数据1.3实验过程1.3.1 建立“学籍与成绩管理系统”表格 建立表之间的参照关系 建立适当的索引1.3.1.1 sql 代码：create database xuejicreate table xb -系表(xdh char(2) not null PRIMARY KEY,xmc varchar(30) not null,)create
3、table xs -学生表(xm varchar(8) not null,xh char(10) not null PRIMARY KEY,xdh char(2) FOREIGN KEY REFERENCES xb(xdh),bj char(8),chrq datetime,xb char(2),)create table kc -课程表(kcbh char(3) not null PRIMARY KEY,kcmc char(20) not null,lx char(10),xf decimal(5,1),)create table js -教师表(xm varchar(8) not null
4、,jsbh char(10) not null PRIMARY KEY,zc char(6),xdh char(2) FOREIGN KEY REFERENCES xb(xdh),1)create table sk -授课表(kcbh char(3) not null FOREIGN KEY REFERENCES kc(kcbh),jsbh char(10) not null FOREIGN KEY REFERENCES js(jsbh),constraint kj_pk PRIMARY KEY (kcbh,jsbh)create table xk -选课表(xh char(10) not n
5、ull FOREIGN KEY REFERENCES xs(xh),kcbh char(3) not null FOREIGN KEY REFERENCES kc(kcbh),jsbh char(10) not null FOREIGN KEY REFERENCES js(jsbh),cj decimal(5,1),constraint xkj_pk PRIMARY KEY (xh,kcbh,jsbh)create index s_xs on xs(xh); -建立索引建立索引原因：提高查询速度1.3.2 使用“INSERT ”语句向主表插入至少 5 行数据，向子表插入至少 30 行数据；1.
6、3.2.1 Sql 代码insert into xb values(01,计算机 );insert into xb values(02,数学 );insert into xb values(03,经济 );insert into xb values(04,法律 );insert into xb values(05,中文 );insert into xs values(王一,1120100001,01,08111001,1990-1-1,男);insert into xs values(王二,1120100002,01,08111001,1990-1-2,男);insert into xs va
7、lues(王三,1120100003,01,08111001,1990-1-3,男);insert into xs values(王四,1120100004,01,08111001,1990-1-4,男);insert into xs values(王五,1120100005,01,08111001,1990-1-5,男);insert into xs values(王六,1120100006,01,08111001,1990-1-6,男);insert into xs values(张一,1120100007,02,08111002,1990-2-1,男);insert into xs va
8、lues(张二,1120100008,02,08111002,1990-2-2,男);insert into xs values(张三,1120100009,02,08111002,1990-2-3,男);insert into xs values(张四,1120100010,02,08111002,1990-2-4,男);insert into xs values(张五,1120100011,02,08111002,1990-2-5,男);insert into xs values(张六,1120100012,02,08111002,1990-2-6,男);insert into xs va
9、lues(李一,1120100013,02,08111003,1990-3-1,男);insert into xs values(李二,1120100014,02,08111003,1990-3-1,男);insert into xs values(李三,1120100015,02,08111003,1990-3-1,男);2insert into xs values(李四,1120100016,02,08111003,1990-3-1,男);insert into xs values(李五,1120100017,02,08111003,1990-3-1,男);insert into xs v
10、alues(李六,1120100018,02,08111003,1990-3-1,男);insert into xs values(陈一,1120100019,04,08111004,1990-4-1,女);insert into xs values(陈二,1120100020,04,08111004,1990-4-1,女);insert into xs values(陈三,1120100021,04,08111004,1990-4-1,女);insert into xs values(陈四,1120100022,04,08111004,1990-4-1,女);insert into xs v
11、alues(陈五,1120100023,04,08111004,1990-4-1,女);insert into xs values(陈六,1120100024,04,08111004,1990-4-1,女);insert into xs values(孙一,1120100025,05,08111005,1990-5-1,男);insert into xs values(孙二,1120100026,05,08111005,1990-5-1,男);insert into xs values(孙三,1120100027,05,08111005,1990-5-1,男);insert into xs v
12、alues(孙四,1120100028,05,08111005,1990-5-1,男);insert into xs values(孙五,1120100029,05,08111005,1990-5-1,男);insert into xs values(孙六,1120100030,05,08111005,1990-5-1,男);insert into kc values(001,操作系统, 必修,3);insert into kc values(002,模拟电子技术基础, 必修,3.5);insert into kc values(003,数据库原理与设计, 必修,3);insert into
13、kc values(004,数字图象处理, 专业选修,3);insert into kc values(005,游戏设计概论, 专业选修,2);insert into js values(郭靖,9920100001,高级,01);insert into js values(黄蓉,9920100002,一级,02);insert into js values(郭襄,9920100003,二级,03);insert into js values(杨过,9920100004,高级,04);insert into js values(杨康,9920100005,一级,05);insert into s
14、k values(001,9920100001);insert into sk values(002,9920100002);insert into sk values(003,9920100003);insert into sk values(004,9920100004);insert into sk values(005,9920100005);insert into xk values(1120100001,001,9920100001,90);insert into xk values(1120100002,001,9920100001,80);insert into xk valu
15、es(1120100003,001,9920100001,70);insert into xk values(1120100004,001,9920100001,60);insert into xk values(1120100005,001,9920100001,50);insert into xk values(1120100001,002,9920100002,95);insert into xk values(1120100002,002,9920100002,85);insert into xk values(1120100003,002,9920100002,75);insert
16、into xk values(1120100004,002,9920100002,65);insert into xk values(1120100005,002,9920100002,55);3insert into xk values(1120100001,003,9920100003,75);insert into xk values(1120100002,003,9920100003,80);insert into xk values(1120100003,003,9920100003,80);insert into xk values(1120100004,003,992010000
17、3,90);insert into xk values(1120100005,003,9920100003,70);insert into xk values(1120100001,004,9920100004,74);insert into xk values(1120100002,004,9920100004,88);insert into xk values(1120100003,005,9920100005,68);insert into xk values(1120100004,005,9920100005,67);insert into xk values(1120100005,0
18、05,9920100005,80);1.3.3 在子表中输入含有不存在外键值的数据； 观察数据库系统的反应1.3.3.1 Sql 代码insert into xs values(钱一,1120100090,06,08111005,1990-5-1,男);1.3.3.2 执行结果系统显示：消息 547，级别 16，状态 0，第 1 行INSERT 语句与 FOREIGN KEY 约束“FK_xs_xdh_54968AE5“ 冲突。该冲突发生于数据库“master“，表“dbo.xb“, column xdh。语句已终止。1.3.4 使用 SELECT * FROM 观察数据；1.3.4.1 Sq
19、l 代码select * from xs1.3.4.2 执行结果（部分，全部共 30 行）1.3.5 更新部分学生的学籍情况、成绩1.3.5.1 Sql 代码update xs set bj=18111001where xh=1120100004;update xk set cj=04where xh=11201000041.3.5.2 结果执行前：执行后1.3.6 删除部分同学的学籍信息1.3.6.1 Sql 代码delete from xkwhere xh=1120100004delete from xswhere xh=11201000041.3.6.2 结果执行以下查找时：select
20、 * from xkwhere xh=1120100004select * from xswhere xh=1120100004找不到数据，表示删除成功1.3.7 使用所有学习的查询语句对表格的进行查询（自拟题目）-1. 学数据库的学生select xs.xm from xs,xk,kcwhere xs.xh=xk.xhand xk.kcbh=kc.kcbhand kc.kcmc=数据库原理与设计 结果：5-2. 没有授课的教师insert into js values(段誉,9920100006,一级,05); -输入新老师数据select js.* from jswhere js.jsbh
21、 in ( (select js.jsbh from js)except(select sk.jsbh from sk)结果：-3. 学生王三所选课程的总学分select kc.* from kc,xk,xswhere kc.kcbh=xk.kcbh and xk.xh=xs.xhand xs.xm=王三;select sum(kc.xf) as 总学分 from kc,xk,xs where kc.kcbh=xk.kcbh and xk.xh=xs.xhand xs.xm=王三;执行结果：王三所选所有课程以及所求总学分-4. 教授学生王三必修课程的老师情况select kc.* from k
22、c,xk,xswhere kc.kcbh=xk.kcbhand xk.xh=xs.xhand xs.xm=王三;select js.* from js,xk,xs,sk,kcwhere js.jsbh=sk.jsbhand sk.kcbh=xk.kcbhand kc.kcbh=xk.kcbhand kc.lx=必修and xk.xh=xs.xh6and xs.xm=王三;执行结果：王三所选所有课程以及所求老师情况-5. 计算机系学生所学的所有课程select distinct kc.* from kc,xs,xb,xkwhere kc.kcbh=xk.kcbhand xk.xh=xs.xh and xs.xdh=xb.xdhand xb.xmc=计算机执行结果：1.4实验结论本次实验的目的均已经达到1.5实验体会通过实验，熟悉了数据库环境、建立表、插入数据、删除数据；巩固了相关 sql 语句的使用，练习了很多查询语句。
