数据库实验和语句练习

sql语句知识
基于教材：数据库系统概论第六版
重点
计算日期

to_char(sysdate,'yyyy')-to_char(sbirth,'yyyy')

输出保留两位小数

round(y,2)--y为数据，2为位数，不指定时保留整数

declare
   x student.sno%type:='101388';
   y sc.grade%type;
begin 
  test_student(x,y);
  dbms_output.put_line('avg_grade is '||round(y,2));
end;

数据定义

表

create table spj(
sno varchar(30),
pno varchar(30),
jno varchar(30),
qty number(5),
primary key (sno,pno,jno),
foreign key (sno) references s(sno),
foreign key (pno) references p(pno),
foreign key (jno) references j(jno));

输入数据

alter table s
 add sphone varchar(30);

alter table s
 add smanager varchar(30);
 
alter table j
 drop constraint SYS_C007992;--删除约束名
 
alter table spj 
 modify qty number(12,2);
 --注意，要先将qty列的数据删除再修改数据类型。
 
alter table s
drop column smanager;
 

索引

create unique index in_j on j(jname);
create index in_p on p(pname);

导出表

导入表


忽略创建错误和导出整个文件选yes,其余回车

数据查询

--1
select pname,color
from p
where weight between 10 and 20;
--2
select avg(weight)
from p;
--3
select *
from p,spj
where p.pno = spj.pno and spj.sno='s3';
--4查询各个供应商号及其供应了多少类零件。
select sno,count(distinct pno)--------!!!!!!distinct
from spj
group by sno;
--5
select sno,count(distinct pno)
from spj
group by sno
having count(distinct pno)>2;
--6
select *
from p
where pname like '螺%';
--7
select jno,count(distinct sno)
from spj
group by jno;
--8查询供应数量在1000—2000之间（包括1000和2000）的零件名称。!!!!!!!!!!
select pname,sum(qty)
from p,spj
where p.pno = spj.pno
group by pname
having sum(qty) between 1000 and 2000;

--1--命名操作
select cname 课程名,cproperty 课程性质,credit 学分,chour 学时
from course;
--2
select sno,sname,smajor,scollege
from student
where smajor like '%大数据%';
--3----计算出生日期
select sname,to_char(sysdate,'yyyy')-to_char(sbirth,'yyyy')
from student
where smajor='计算机科学与技术';
--4
select * 
from student 
where smajor='软件工程' and to_char(sbirth,'yyyy')='1995'
order by sbirth asc;---不会排序 升序ascending order
                 -- 降序descending order
--5
select cno,count(sno),max(grade)
from sc
group by cno;
--6----复杂一些
select cname,chour,cterm
from course
where cproperty='选修' and cno in(
 select cno
 from sc
 group by cno
 having count(sno)>60
);

select cname,chour,cterm
from course
where cno in
(select sc.cno
from course,sc
where course.cno=sc.cno and cproperty='选修'
group by sc.cno
having count(sno) > 60);
--7
select scollege,count(sno)
from student 
group by scollege ;

难题

--8----(只有成绩在60分以上，才能获得课程学分） 
select student.sno,sname,sum(credit)
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and scollege ='信息工程学院' and grade>=60
group by (student.sno,student.sname);

--9
--错误例子，原因，班级不同，保证授课表和学生表的班级相同
select cname,sname,grade，sclass,tno
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and course.cno in(
select tc.cno 
from tc,teacher
where tc.tno = teacher.tno and tname='吴春燕')
order by grade asc;
--正确
select  cname,sname,grade
from sc,student,course,teacher,tc
where sc.sno = student.sno and sc.cno = course.cno and teacher.tno = tc.tno and sc.cno=tc.cno and tname = '吴春燕' 
and student.sclass=tc.sclass---!!!!!!!!!班级不同
order by grade asc;

---测试部分
select *
from sc,tc,teacher,student
where sc.cno=tc.cno and tc.tno=teacher.tno and student.sno=sc.sno and student.sclass=tc.sclass and tname='吴春燕';

select * 
from teacher natural join tc
where tname='吴春燕'; 

班级不同

--10
select student.sno
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and cname='中间件技术'
intersect--集合
select student.sno
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and cname='Java EE技术';


select student.sno
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and cname='中间件技术' and student.sno in(
select student.sno--嵌套
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and cname='Java EE技术');

--11
select sname,smajor,sbirth
from student
where sbirth<to_date('1994/1/1','yyyy/mm/dd');

--12
select student.sno,sname,count(cno)
from student,sc
where student.sno=sc.sno
group by (student.sno,sname)
having count(distinct cno)>5;

select sno,sname
from student
where sno in( 
select sno
from sc
group by sno
having count(cno) > 5);
--13-----!!!!!出错
select sname,to_char(sysdate,'yyyy')-to_char(sbirth,'yyyy') age
from student s1--一行
where to_char(sysdate,'yyyy')-to_char(sbirth,'yyyy')<
(select avg(to_char(sysdate,'yyyy')-to_char(sbirth,'yyyy'))--求平均
from student s2
where s1.scollege=s2.scollege);--少写了个s;

--14---授课表没有它的信息
select tname
from teacher 
where  tno not in(---出错！！！！！！！！写的not exists
select tno 
from tc);
--15
select *
from student 
where to_char(sysdate,'yyyy')-to_char(sbirth,'yyyy')>(
select max(to_char(sysdate,'yyyy')-to_char(sbirth,'yyyy'))
from student
where smajor='计算机科学与技术');

数据更新与视图

数据库安全性

包括
用户（创建，修改，删除）
权限（授予，回收）
角色（创建）（授予，回收）
角色是权限的集合，dba代表所有权限

存储过程触发器

问题

创建完成以后要运行一下，不然不会被保存

输出的结果在上面