大理大学实验报告课程名称：医学数据库管理技术学院：工程学院专业：生物医学工程年级： 2013 班级： 1 姓名：王绍帆学号： 2013166132指导教师：赵春文2015年12月20 日大理大学教务处制实验报告要求实验报告内容原则上应包括以下几个内容：1.实验课程名称2.实验项目名称3.实验目的和要求4.主要仪器设备5.实验原理和内容6.主要的操作方法与实验步骤7.实验数据记录和处理8.实验结果与分析9.思考题注：不同类型的实验课程对实验报告可有不同的要求，各个学科的实验报告可以根据自己的学科特点对以上内容作适当调整。

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J4','700');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P2','J2','100');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J1','400');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J2','200');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J4','500');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J5','400');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J1','400');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P15','J2','100');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P1','J1','200');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P3','J1','200');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P5','J1','100');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J3','300');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J4','200');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P2','J4','100');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P3','J1','200');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J2','200');INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J4','500');2.显示建立好的表(1)查询命令：SELECT*FROM S;查询结果：(2)查询命令：SELECT*FROM P;查询结果：(3)查询命令：SELECT*FROM J;查询结果：(4)查询命令：SELECT*FROM SPJ;查询结果：3.执行习题6所要求的操作(1)求供应工程J1零件的供应商号码查询命令：SELECT DISTINCT SNOFROM SPJWHERE JNO='J1'查询结果：(2)求供应工程J1零件P1的供应商号码SNO查询命令：SELECT SNOFROM SPJWHERE JNO='J1' AND PNO='P1';查询结果：(3)求供应工程J1零件为红色的供应商号码SNO查询命令：SELECT SPJ.SNOFROM SPJ,PWHERE SPJ.PNO=P.PNO AND SPJ.JNO='J1' AND P.COLOR='红';查询结果：(4)求没有使用天津供应商生产的红色零件的工程号JNO查询命令：SELECT SPJ.JNOFROM SPJ,S,PWHERE S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND S.CITY!='天津' ANDP.COLOR='红';查询结果：(5)求至少用了供应商S1所供应的全部零件的工程号JNO。

1.先找出供应商S1供应的零件查询命令：SELECT DISTINCT PNOFROM SPJWHERE SNO='S1'查询结果：2.再找既用零件P1又用零件P2的工程号JNO。

查询命令：SELECT DISTINCT JNOFROM SPJWHERE PNO='P1' INTERSECT SELECT DISTINCT JNO FROM SPJ WHEREPNO='P2'查询结果：5 实验结果及总结1.因为重复执行了生成表格的语句，所以出现了错误，经过百度，了解到用DROP TABLE S CASCADE语句删除S表。

2.SELECT* FROM S 这是输出s表的全内容，检验输入数据的准确。

3.输入数据的时，字符串常数要用单引号（英文符号）括起来。

值与属性列要一一对应。

4.kingbase数据库对命令的格式有较为严格的要求，输入时要具有较高的准确性，不然容易报错。

例3.17查询命令：SELECT Sname,Sno,SdeptFROM "S-C"."STUDENT";查询结果：例3.18查询命令：SELECT*FROM "S-C"."STUDENT";查询结果：例3.19查询命令：SELECT Sname,2014-SageFROM "S-C"."STUDENT";查询结果：例3.201.查询命令：SELECT Sname,'Year of Birth:',2014-Sage,LOWER(Sdept)FROM "S-C"."STUDENT"查询结果：2.查询命令：SELECT Sname NAME,'Year of Birth:'BIRTH,2014-Sage BIRTHDAY, LOWER(Sdept)DEPARTMENTFROM "S-C"."STUDENT";查询结果：例3.21查询命令：SELECT SnoFROM "S-C"."SC";查询结果：例3.22查询命令：SELECT SnameFROM "S-C"."STUDENT"WHERE Sdept='CS';查询结果：例3.23查询命令：SELECT Sname,sageFROM "S-C"."STUDENT"WHERE Sage<20;查询结果：例3.24查询命令：SELECT DISTINCT SnoFROM "S-C"."SC"WHERE Grade<60;查询结果：例3.25查询命令：SELECT Sname,Sdept,SageFROM "S-C"."STUDENT"WHERE Sage BETWEEN 20 AND 23;查询结果：例3.26查询命令：SELECT Sname,Sdept,SageFROM "S-C"."STUDENT"WHERE Sage NOT BETWEEN 20 AND 23;查询结果：例3.27查询命令：SELECT Sname,SsexWHERE FROM "S-C"."STUDENT"Sdept IN('CS','MA','IS');查询结果：例3.28查询命令：SELECT Sname,SsexFROM "S-C"."STUDENT"WHERE Sdept NOT IN('CS','MA','IS');查询结果：例3.29查询命令：SELECT*FROM "S-C"."STUDENT";WHERE Sno LIKE '201215121';查询结果：例3.30查询命令：SELECT Sname,Sno,SsexFROM "S-C"."STUDENT";WHERE Sname LIKE '刘%';查询结果：例3.31查询命令：SELECT SnameFROM "S-C"."STUDENT";WHERE Sname LIKE '欧阳_';查询结果：例3.32查询命令：SELECT Sname,SnoFROM "S-C"."STUDENT";WHERE Sname LIKE '_阳%';查询结果：例3.33查询命令：SELECT Sname,Sno,SsexFROM "S-C"."STUDENT";WHERE Sname NOT LIKE '刘%';查询结果：例3.34查询命令：SELECT Cno,CcreditFROM "S-C"."COURSE";WHERE Cname LIKE 'DB\_Design' ESCAPE '\';查询结果：例3.35查询命令：SELECT*FROM "S-C"."COURSE";WHERE Cname LIKE 'DB\_%i_ _' ESCAPE '\';例3.36查询命令：SELECT Sno,CnoFROM "S-C"."SC"WHERE Grade IS NULL;查询结果：例3.37查询命令：SELECT Sno,CnoFROM "S-C"."SC"WHERE Grade IS NOT NULL;查询结果：例3.38查询命令：SELECT SnameFROM "S-C"."STUDENT";WHERE Sdept='CS' AND Sage<20;查询结果：例3.39查询命令：SELECT Sno,GradeFROM "S-C"."SC"WHERE Cno='3'ORDER BY Grade DESC;查询结果：例3.40查询命令：SELECT*FROM "S-C". "STUDENT";ORDER BY Sdept,Sage DESC;查询结果：例3.41查询命令：SELECT COUNT(*)FROM "S-C"."STUDENT";查询结果：例3.42查询命令：SELECT COUNT(DISTINCT Sno)FROM "S-C"."SC";查询结果：例3.43查询命令：SELECT AVG(Grade)FROM "S-C"."SC";WHERE Cno='1';查询结果：例3.44查询命令：SELECT MAX(Grade)FROM "S-C"."SC";WHERE Cno='1';查询结果：例3.45查询命令：SELECT SUM(Ccredit)FROM "S-C"."COURSE";WHERE Sno='201215012'AND o=o;查询结果：例3.46SELECT Cno,COUNT(Sno)FROM "S-C".SCGROUP BY Cno;查询结果：例3.47查询命令：SELECT SnoFROM "S-C".SC;GROUP BY SnoHAVING COUNT(*)>3;;查询结果：例3.48查询命令：SELECT Sno,AVG(Grade)FROM "S-C".SCGROUP BY SnoHAVING AVG(Grade)>=90;查询结果：例3.49查询命令：SELECT Student.*,SC.*FROM "S-C".Student,"S-C".SC;WHERE Student.Sno=SC.Sno;查询结果：例3.50查询命令：SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM "S-C".Student,"S-C".SC;WHERE Student.Sno=SC.Sno;查询结果：例3.51SELECT Student.Sno,SnameFROM "S-C".Student,"S-C".SC;WHERE Student.Sno=SC.Sno AND o='2'AND SC.Grade>90;查询结果：例3.52查询命令：SELECT o,SECOND.CpnoFROM "S-C".Course FIRST,"S-C".Course SECONDWHERE FIRST.Cpno=o;查询结果：例3.53查询命令：SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROM "S-C".Student LEFT OUTER JOIN "S-C".SC ON(Student.Sno=SC.Sno);查询结果：例3.54查询命令：SELECT Student.Sno,Sname,Cname,GradeFROM "S-C".Student,"S-C".SC,"S-C".CourseWHERE Student.Sno=SC.Sno AND o=o;查询结果：例3.551.查询命令：SELECT SdeptFROM "S-C".StudentWHERE Sname='刘晨';查询结果：2.查询命令：SELECT Sno,Sname,SdeptFROM "S-C".StudentWHERE Sdept='CS';查询结果：例3.56查询命令：SELECT Sno,SnameFROM "S-C".StudentWHERE Sno INFROM "S-C".SCWHERE Cno IN(SELECT CnoFROM "S-C".CourseWHERE Cname='信息系统' ));查询结果：例3.57查询命令：SELECT Sno ,CnoFROM "S-C".SC xWHERE Grade>=(SELECT AVG(Grade)FROM "S-C".SC yWHERE y.Sno=X.Sno) ;查询结果：例3.581.查询命令：SELECT Sname,SageFROM "S-C".StudentWHERE Sage <ANY(SELECT SageFROM "S-C".StudentWHERE Sdept ='CS') AND Sdept<>'CS';查询结果：2.查询命令：SELECT Sname,SageFROM "S-C".StudentWHERE Sage <(SELECT MAX(Sage)FROM "S-C".StudentWHERE Sdept ='CS') AND Sdept<>'CS';查询结果：例3.591.查询命令：FROM "S-C".StudentWHERE Sage <ALL(SELECT SageFROM "S-C".StudentWHERE Sdept ='CS')AND Sdept<>'CS';查询结果：2.查询命令：SELECT Sname,SageFROM "S-C".StudentWHERE Sage <(SELECT MIN(Sage)FROM "S-C".StudentWHERE Sdept ='CS')AND Sdept<>'CS';查询结果：例3.60查询命令：SELECT SnameFROM "S-C".StudentWHERE EXISTS(SELECT*FROM "S-C".SCWHERE Sno=Student.Sno AND Cno='1');查询结果：5 实验结果及总结1.执行查询语句时，要清楚查询的表的范围，通过条件语句来筛选出想要的结果；2.条件语句可以通过相同元组把表连接，以便查询多表的内容。