网页编程网

建表语句：
create table student(
id number(6),
grade varchar2(5),--年级
class_number varchar2(5),--班级号
name varchar2(20),--姓名
sex char(4),
score number(4,2)--分数
);
建序列语句：
create sequence student_seq--序列名为：payInfo_seq
start with 1
increment by 1
minvalue 1
maxvalue 99999
nocache
nocycle;
插入数据：
insert into student values (student_seq.nextval,'2012','01','n1','男',25.66);
insert into student values (student_seq.nextval,'2012','02','ox1','男',95.66);

insert into student values (student_seq.nextval,'2013','01','n2','男',85.66);
insert into student values (student_seq.nextval,'2013','02','mn1','女',25.66);
insert into student values (student_seq.nextval,'2013','02','mn2','男',19.64);

insert into student values (student_seq.nextval,'2013','02','or1','男',25.66);
insert into student values (student_seq.nextval,'2013','02','op3','女',95.66);
insert into student values (student_seq.nextval,'2013','03','o4','女',45.10);
insert into student(id,grade,class_number) values (student_seq.nextval,'2013','04');

insert into student values (student_seq.nextval,'2014','02','pt2','男',19.64);

insert into student values (student_seq.nextval,'2014','03','p3','女',75.68);
insert into student values (student_seq.nextval,'2014','03','px1','女',36.60);

完成如下操作：

1。删除表格，后再重新建立，观察新生成的序列有何种特点。
drop table student;
2。去了重复班级号。
select distinct class_number from student;
select class_number from student group by class_number;
3。按分数降、升序（desc\asc）
select * from student order by score desc;
4.只显示2013\2014级的
select * from student where grade=2013 or grade=2014;
select * from student where grade in (2013,2014);
5.显示分数>60，且非2012级的
select * from student where grade !=2012 and grade>60;
6.显示姓名带1的
%:0 or more
_:only one
select * from student where name like '%1';
7.子查询：显示与n2有相同班级号的
select * from student where class_number = (select class_number from student where name='n2');
试试in
8.数据统计，求平均分，算最大值，算总数量
select count(*) from student;
select avg(score) from student;
查询每个年级有多少人
select grade,count(grade) AS 年级数量 from student group by grade;
显示name为空
select * from student where name is null;

9.联合查询
union 不包括重复行
union all包括重复行

查2013、2014年级有哪些班级编号
select class_number from student where grade=2013 union
select class_number from student where grade=2014;

10.视图
create view WC AS
select * from student where grade=2013 and sex='女' and score>60
select * from WC;