当前位置:主页 >> Oracle >> 正文
oracle必会的基本语句
阅读:1150 输入:2014-11-15 12:48:55

建表语句:
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;