了解oracle基本语句,oracle必会的基本语句

建表语句：

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;