北京理工大学-数据库-实验1-建立数据库.doc

(封皮页)北京理工大学数据库实验1主讲老师：赵晓林学生：李经2012/11/206(目录页)实验 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 使用所有学习的查询语句对表格的进行查询（自拟题目）51.4 实验结论71.5 实验体会7实验 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 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,jsbh char(10) not null PRIMARY KEY,zc char(6),xdh char(2) FOREIGN KEY REFERENCES xb(xdh),)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 null 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.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 values(王三,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 values(张二,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 values(李一,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,男);insert into xs values(李四,1120100016,02,08111003,1990-3-1,男);insert into xs values(李五,1120100017,02,08111003,1990-3-1,男);insert into xs values(李六,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 values(陈五,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 values(孙四,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 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 sk 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 values(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 into xk values(1120100004,002,9920100002,65);insert into xk values(1120100005,002,9920100002,55);insert 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,9920100003,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,005,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 Sql代码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=0where 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 * 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=数据库原理与设计结果：-2. 没有授课的教师insert into js values(段誉,9920100006,一级,05); -输入新老师数据select js.* from jswhere js.jsbh in ( (select js.jsbh from js)except(select sk.js