数据库实验 sql语句

计算机科学与技术学院实验报告实验题目：交互模式下的DML学号：200800130089日期：2010-11-6班级：08计算机3班姓名：娄勇福Email：[e-mail]实验目的：熟练掌握查询分析器的使用方法，在交互模式下，反复练习各种DML语句，进行各种增删改和查询，进一步加深对SQL语言的理解。硬件环境：联想昭阳E43笔记本一台软件环境：MicrosoftSQLserver2000，查询分析器，企业管理器，QQ（截图工具）实验步骤：1、建立关系、表并填充数据：启动MicrosoftSQLserver2000服务管理器，打开企业管理器，新建数据库students，切换到查询分析器，在工具栏中间位置选择students数据库，使用SQL语句进行编辑，创建表与写入数据的语句如下：createtabledepart(numberchar(20),dnamechar(20),primarykey(number))createtablestudent(numberchar(20),snamechar(20),sexchar(20),ageint,departnamechar(20),departnochar(20),primarykey(number),foreignkey(departno)referencesdepart(number))createtablecourse(cnochar(20),cnamechar(20),xianxingint,valueint,primarykey(cno))createtableteacher(numberchar(20),tnamechar(20),cnamechar(20),cnochar(20),primarykey(number),foreignkey(cno)referencescourse(cno))createtablexuanke(snochar(20),cnochar(20),scoreint,primarykey(sno,cno),foreignkey(cno)referencescourse(cno))createtableshouke(tnochar(20),tnamechar(20),cnamechar(20),cnochar(20),primarykey(tno),foreignkey(tno)referencesteacher(number),foreignkey(cno)referencescourse(cno))以上代码创建了六个表，depart，student，teacher，shouke，xuanke，course，并利用主键和外键设置了各个表的约束关系。insertintoteachervalues('20090123','孟庆娟','大学英语','329')insertintoteachervalues('20098922','孙宇清','数据结构','328')insertintoteachervalues('20080145','王维华','电工','223')insertintoteachervalues('21458883','李新','数字逻辑','258')insertintoteachervalues('25896144','杨静','汇编语言','365')insertintoteachervalues('20014785','栾俊峰','离散数学','352')insertintoteachervalues('20585213','叶宏','离散数学','324')insertintoteachervalues('21458888','殷晓峰','计算机组成','333')insertintocoursevalues('223','电工','5',4)insertintocoursevalues('329','大学英语','5',4)insertintocoursevalues('328','数据结构','4',5)insertintocoursevalues('258','数字逻辑','3',3)insertintocoursevalues('365','汇编语言','4',4)insertintocoursevalues('352','离散数学','2',4)insertintocoursevalues('324','概率统计','5',3)insertintocoursevalues('333','计算机组成','8',2)insertintocoursevalues('224','数据库','5',1)insertintoxuankevalues('200800130089','223',98)insertintoxuankevalues('200800130089','329',99)insertintoxuankevalues('200800130146','324',97)insertintoxuankevalues('200800130085','365',60)insertintoxuankevalues('200800130043','333',59)insertintoxuankevalues('200800130043','224',10)insertintoxuankevalues('200800130055','328',89)insertintoxuankevalues('200800130066','352',69)insertintoxuankevalues('200800130088','258',93)insertintoxuankevalues('200800130047','324',77)insertintoxuankevalues('200800130089','224',99)insertintoxuankevalues('200800131111','224',50)insertintoxuankevalues('200800130555','224',69)insertintoxuankevalues('200800130555','324',58)insertintoxuankevalues('200800130555','258',90)insertintoxuankevalues('200800130089','328',96)insertintoxuankevalues('200800130089','258',95)insertintoxuankevalues('200800130089','365',99)insertintoxuankevalues('200800130089','352',100)insertintoxuankevalues('200800130089','324',100)insertintoxuankevalues('200800130089','333',100)insertintoxuankevalues('200800130555','223',85)insertintoxuankevalues('200800130555','329',78)insertintoxuankevalues('200800130555','328',47)insertintoxuankevalues('200800130555','365',88)insertintoxuankevalues('200800130555','352',84)insertintoxuankevalues('200800130555','333',87)insertintoxuankevalues('98001','324',95)insertintoxuankevalues('98002','328',88)insertintoxuankevalues('98002','365',68)insertintoxuankevalues('98002','224',97)insertintoxuankevalues('98003','224',55)insertintoxuankevalues('200800130555','223',77)insertintodepartvalues('24','计算机')insertintodepartvalues('1','经济')insertintodepartvalues('2','数学')insertintodepartvalues('5','管理')insertintodepartvalues('27','社会学')insertintodepartvalues('4','历史')insertintodepartvalues('9','软件')insertintodepartvalues('18','电子商务')insertintodepartvalues('14','临床')insertintodepartvalues('16','外语')insertintostudentvalues('200800130089','娄勇福','男',19,'计算机','24')insertintostudentvalues('200800130146','王静文','女',20,'计算机','24')insertintostudentvalues('200800130085','刘文','女',20,'计算机','24')insertintostudentvalues('200800130043','韩善文','女',20,'计算机','24')insertintostudentvalues('200800130088','老韩','女',30,'软件','9')insertintostudentvalues('200800130055','阿文','男',25,'经济','1')insertintostudentvalues('200800130066','小静','男',15,'管理','5')insertintostudentvalues('200800130047','南瓜粥','女',19,'数学','2')insertintostudentvalues('200800130099','小斯斯','男',18,'社会学','27')insertintostudentvalues('200800131111','小怡宁','女',22,'外语','16')insertintostudentvalues('98001','小越越','女',21,'临床','14')insertintostudentvalues('98002','小祥子','男',20,'计算机','24')insertintostudentvalues('98003','王欣','男',19,'计算机','24')insertintostudentvalues('98004','赵岩','男',19,'外语','16')insertintostudentvalues('200800130555','张平','男',21,'计算机','24')insertintoshoukevalues('20090123','孟庆娟','大学英语','329')insertintoshoukevalues('20098922','孙宇清','数据结构','328')insertintoshoukevalues('20080145','王维华','电工','223')insertintoshoukevalues('20585213','叶宏','概率统计','324')insertintoshoukevalues('25896144','杨静','汇编语言','365')insertintoshoukevalues('21458883','李新','数字逻