数据库原理实验报告
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
/*字符匹配*/ select Cno,Ccredit from Course where Cname like 'DB\_Desigen' escape '\' /*转码字符由自己定义*/
/*涉及空值的查询*/ select Sno,Cno from SC where Grade is null /*注意is不能用=替代*/
三、查询 select Sno,Sname from Student;
/*查询经过计算的值*/ select Sname,2014-Sage from Student
/*指定字符通过小写输出*/ select Sname,'year of birth',2014-Sage,lower(Sdept) from Student
insert into Course (Cno,Cname,Cpno,Ccredit) values('2','数学',NULL,2) insert into Course (Cno,Cname,Cpno,Ccredit) values('6','数据处理',NULL,2) insert into Course (Cno,Cname,Cpno,Ccredit) values('4','操作系统','6',3) insert into Course (Cno,Cname,Cpno,Ccredit) values('7','PASCAL语言','6',4) insert into Course (Cno,Cname,Cpno,Ccredit) values('5','数据结构','7',4) insert into Course (Cno,Cname,Cpno,Ccredit) values('1','数据库','5',4) insert into Course (Cno,Cname,Cpno,Ccredit) values('3','信息系统','1',4) insert into Course (Cno,Cname,Cpno,Ccredit) values('8','DB_Design','7',4)
/*嵌套查询*/ /*in谓词*/ select Sno,Sname,Sdept from Student
where Sdept in( select Sdept from Student where Sname='刘晨'
)/*此查询可以利用自身连接代替select S1.Sno,S1.Sname,S1.Sdept from Student S1,Student S2 where S1.Sdept=S2.Sdept and S2.name='刘晨'*/
select sum(Ccredit) from SC,Course where Sno='200215121' and SC.Cno=Course.Cno 的总学分*/
/*查询学号为的学生选修课程
/*group by子句*/ select Cno,COUNT(Sno) from SC group by Cno
select * from student where sno <> '200215121'
select * from student where sno != '200215121'/*不含有通配符的时候可以使用“=;<>;!=”代替like，not
like*/
select Sname,Sno,Ssex from Student where Sname like '刘%'
select Sname,Sdept,SBiblioteka Baiduge from Student where Sage not between 20 and 30
/*确定集合*/ select Sname,Ssex from Student where Sdept in ('CS','MA')
/*字符匹配*/ select * from student where sno like '200215121'
select Sno,Cno from SC where Grade is not null
/*多重条件查询*/ select Sname from Student
where Sdept='CS' and Sage<20 /*and优先级比or高*/
select Sname,Ssex from Student where Sdept='CS' or Sdept='IS' or Sdept='MA' */
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215123','王敏','女',18,'MA') insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215125','张立','男',19,'IS')
/*带有比较运算符的子查询*/ /*相关子查询*/ select Sno,Cno from SC x where Grade >= (
select avg(Grade) from SC y where y.Sno=x.Sno )
/*带有any(some)或all谓词的子查询*/
select Sname,Sage from Student where Sage<any(
select Sname from Student where Sname like '欧阳__'/*注意一个汉字两个字符*/
select Sname,Sno from Student where Sname like '__阳%'
select Sname,Sno from Student where Sname not like '刘%'
/*having 短语*/ select Sno from SC group by Sno having COUNT(*)>2
/*连接查询*/ select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno /*自然连接，两个表中不重复的属性名可以不加前缀*/ select Student.Sno,Sname,Ssex,Sdept,Cno,Grade from Student,SC where Student.Sno=SC.Sno /*自身连接，要为表取别名，此时必须都要有前缀*/ select first.Cno,second.Cpno from Course first,Course second where first.Cpno=second.Cno /*外连接*/ select Student.Sno,Sname,Ssex,Sdept,Cno,Grade from Student right outer join SC on(Student.Sno=Sc.Sno) /*复合条件连接*/ select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and Sc.Cno='2' and SC.Grade>80 /*复合条件连接*/ select Student.Sno,Sname,Cname,Grade from Student,Course,SC where Student.Sno=SC.Sno and Course.Cno=SC.Cno
select Sno,Sname from Student where Sno in(
select Sno from SC where Cno in (
select Cno from Course where Cname='信息系统' ) ) /*以上代码的连接方式*/ select Student.Sno,Sname from Student,Course,SC where Course.Cname='信息系统' and SC.Cno=Course.Cno and Student.Sno=Sc.Sno
select Sage from Student where Sdept='CS' )and Sdept <>'CS' select Sname,Sage from Student where Sage<all( select Sage from Student where Sdept='Cs' )and Sdept<>'CS'
/*有时in谓词可看做多个or的缩写
/*order by子句*/ select Sno,Grade from SC where Cno='3' order by Grade desc /*asc:升序；desc：降序，缺省升序*/
select * from Student order by Sdept,Sage desc
/*比较大小查询*/ select Sname,Sage from Student where Sage>19
select Sname from Student where Sdept='CS'
/*确定范围*/ select Sname,Sdept,Sage from Student where Sage between 20 and 30
题目： 数据库原理实验报告
课本内容练习
一、创建表 create table Student(
Sno char(9) primary key, Sname char(20) unique, Ssex char (2), Sage smallint, Sdept char(20) ) create table Course( Cno char(4) primary key, Cname char(40), Cpno char(4), Ccredit smallint, foreign key (Cpno) references Course(Cno) ); create table SC( Sno char(9), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno) ) create table Dept_age( Sdept char(15), Avg_age smallint, ) 二、插入 insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215121','李勇','男',20,'CS') insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215122','刘晨','女',19,'CS')
insert
into SC(Sno,Cno,Grade) values('200215121','1',92) insert into SC(Sno,Cno,Grade) values('200215121','2',85) insert into SC(Sno,Cno,Grade) values('200215121','3',88) insert into SC(Sno,Cno,Grade) values('200215122','2',90) insert into SC(Sno,Cno,Grade) values('200215122','3',80)
/*聚集函数*/ select count(*)/*貌似distinct和*是不能同时使用的*/
from SC
select count(distinct Sno) from Student
select avg(Grade) from SC where Cno=1 /*计算一号课程的平均成绩*/
select max(Grade) from SC where Cno=2 /*计算号课程的最高成绩*/
/*为查询结果指定列标题*/ select Sname name,'year of birth' birth,2014-Sage birthday,lower(Sdept) department from Student
/*消除取值重复的行*/ select distinct Sno from SC
/*涉及空值的查询*/ select Sno,Cno from SC where Grade is null /*注意is不能用=替代*/
三、查询 select Sno,Sname from Student;
/*查询经过计算的值*/ select Sname,2014-Sage from Student
/*指定字符通过小写输出*/ select Sname,'year of birth',2014-Sage,lower(Sdept) from Student
insert into Course (Cno,Cname,Cpno,Ccredit) values('2','数学',NULL,2) insert into Course (Cno,Cname,Cpno,Ccredit) values('6','数据处理',NULL,2) insert into Course (Cno,Cname,Cpno,Ccredit) values('4','操作系统','6',3) insert into Course (Cno,Cname,Cpno,Ccredit) values('7','PASCAL语言','6',4) insert into Course (Cno,Cname,Cpno,Ccredit) values('5','数据结构','7',4) insert into Course (Cno,Cname,Cpno,Ccredit) values('1','数据库','5',4) insert into Course (Cno,Cname,Cpno,Ccredit) values('3','信息系统','1',4) insert into Course (Cno,Cname,Cpno,Ccredit) values('8','DB_Design','7',4)
/*嵌套查询*/ /*in谓词*/ select Sno,Sname,Sdept from Student
where Sdept in( select Sdept from Student where Sname='刘晨'
)/*此查询可以利用自身连接代替select S1.Sno,S1.Sname,S1.Sdept from Student S1,Student S2 where S1.Sdept=S2.Sdept and S2.name='刘晨'*/
select sum(Ccredit) from SC,Course where Sno='200215121' and SC.Cno=Course.Cno 的总学分*/
/*查询学号为的学生选修课程
/*group by子句*/ select Cno,COUNT(Sno) from SC group by Cno
select * from student where sno <> '200215121'
select * from student where sno != '200215121'/*不含有通配符的时候可以使用“=;<>;!=”代替like，not
like*/
select Sname,Sno,Ssex from Student where Sname like '刘%'
select Sname,Sdept,SBiblioteka Baiduge from Student where Sage not between 20 and 30
/*确定集合*/ select Sname,Ssex from Student where Sdept in ('CS','MA')
/*字符匹配*/ select * from student where sno like '200215121'
select Sno,Cno from SC where Grade is not null
/*多重条件查询*/ select Sname from Student
where Sdept='CS' and Sage<20 /*and优先级比or高*/
select Sname,Ssex from Student where Sdept='CS' or Sdept='IS' or Sdept='MA' */
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215123','王敏','女',18,'MA') insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215125','张立','男',19,'IS')
/*带有比较运算符的子查询*/ /*相关子查询*/ select Sno,Cno from SC x where Grade >= (
select avg(Grade) from SC y where y.Sno=x.Sno )
/*带有any(some)或all谓词的子查询*/
select Sname,Sage from Student where Sage<any(
select Sname from Student where Sname like '欧阳__'/*注意一个汉字两个字符*/
select Sname,Sno from Student where Sname like '__阳%'
select Sname,Sno from Student where Sname not like '刘%'
/*having 短语*/ select Sno from SC group by Sno having COUNT(*)>2
/*连接查询*/ select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno /*自然连接，两个表中不重复的属性名可以不加前缀*/ select Student.Sno,Sname,Ssex,Sdept,Cno,Grade from Student,SC where Student.Sno=SC.Sno /*自身连接，要为表取别名，此时必须都要有前缀*/ select first.Cno,second.Cpno from Course first,Course second where first.Cpno=second.Cno /*外连接*/ select Student.Sno,Sname,Ssex,Sdept,Cno,Grade from Student right outer join SC on(Student.Sno=Sc.Sno) /*复合条件连接*/ select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and Sc.Cno='2' and SC.Grade>80 /*复合条件连接*/ select Student.Sno,Sname,Cname,Grade from Student,Course,SC where Student.Sno=SC.Sno and Course.Cno=SC.Cno
select Sno,Sname from Student where Sno in(
select Sno from SC where Cno in (
select Cno from Course where Cname='信息系统' ) ) /*以上代码的连接方式*/ select Student.Sno,Sname from Student,Course,SC where Course.Cname='信息系统' and SC.Cno=Course.Cno and Student.Sno=Sc.Sno
select Sage from Student where Sdept='CS' )and Sdept <>'CS' select Sname,Sage from Student where Sage<all( select Sage from Student where Sdept='Cs' )and Sdept<>'CS'
/*有时in谓词可看做多个or的缩写
/*order by子句*/ select Sno,Grade from SC where Cno='3' order by Grade desc /*asc:升序；desc：降序，缺省升序*/
select * from Student order by Sdept,Sage desc
/*比较大小查询*/ select Sname,Sage from Student where Sage>19
select Sname from Student where Sdept='CS'
/*确定范围*/ select Sname,Sdept,Sage from Student where Sage between 20 and 30
题目： 数据库原理实验报告
课本内容练习
一、创建表 create table Student(
Sno char(9) primary key, Sname char(20) unique, Ssex char (2), Sage smallint, Sdept char(20) ) create table Course( Cno char(4) primary key, Cname char(40), Cpno char(4), Ccredit smallint, foreign key (Cpno) references Course(Cno) ); create table SC( Sno char(9), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno) ) create table Dept_age( Sdept char(15), Avg_age smallint, ) 二、插入 insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215121','李勇','男',20,'CS') insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215122','刘晨','女',19,'CS')
insert
into SC(Sno,Cno,Grade) values('200215121','1',92) insert into SC(Sno,Cno,Grade) values('200215121','2',85) insert into SC(Sno,Cno,Grade) values('200215121','3',88) insert into SC(Sno,Cno,Grade) values('200215122','2',90) insert into SC(Sno,Cno,Grade) values('200215122','3',80)
/*聚集函数*/ select count(*)/*貌似distinct和*是不能同时使用的*/
from SC
select count(distinct Sno) from Student
select avg(Grade) from SC where Cno=1 /*计算一号课程的平均成绩*/
select max(Grade) from SC where Cno=2 /*计算号课程的最高成绩*/
/*为查询结果指定列标题*/ select Sname name,'year of birth' birth,2014-Sage birthday,lower(Sdept) department from Student
/*消除取值重复的行*/ select distinct Sno from SC