Oracle sql基本语句大全

练习Oracle数据库的相关语句，把所敲的代码整理到博客。以后用到可以快输查询

说明：一下只是简单的sql语句

表管理

创建表：

create table Num

(

id varchar2(2),

name varchar2(5),

psd char(5)

);

修改表结构：

alter table Num add age number(2);    --添加

alter table Num modify age number(5);   --修改

alter table Num drop column age;      --删除项

修改表的名字：

rename Num to Numb

保留表结构将表中的数据删除

truncate table Numb;

创建表并复制另一个表的结构以及数据：

create table Num as select * from Numb;

删除表：

drop table Numb;

查询表内数据：

 

select 命令

select * from Num;        --查询所有

select name from Num     --查询某个数据项

select id,name from Num   --查询多个数据项

 

运算符

select 3+4*2 from dual;

 

select sage*30 from Student   --获取数据项处理后的数据

 

连接运算符

select sname ||'今年'||sage||'岁' from Student

robin今年23岁

zgp今年35岁

zgpd今年64岁

 

条件运算符

select * from Student where sname='robin',

select * from Student where quxi>1

 

嵌套查询

select * from Student where sage >

(

select sage from Student where sname='zgp'

);

 

逻辑运算符

select * from Student where quxi!=3

select * from Student where not quxi=3     --结果相同

 

成员运算符

select * from Student where quxi not in(1,2);

 

范围运算符：

select * from Student where quxi between 2 and 3

select * from Student where quxi>1 and quxi<=3

 

like运算符

select * from Student where sname like '%bin'

select * from Student where sname is not null

 

聚函数

select floor(sage) from Student   --大

select ceil(sage) from Student    --小

select round(sage) from Student

 

order by 字句

select * from Student order by sage asc –升

select * from Student order by sage desc  --降

 

组函数

select * from Student where sage>

(

select avg(sage) from Student

)

select count(*) from Student

select count(rowid) from Student

 

group by 字句

select quxi,count(*) from Student group by quxi

QUXI	COUNT(*)
1	1
4	1
3	1

select quxi from Student group by quxi order by avg(sage)   --只能出现组函数

 

insert字句

insert into Student values(5,'zg','13',34,'nan',2)

insert into Student values('&1','&2','&3','&4','&5',&6)

 

update字句

update Student set sname='rob' where quxi=2

 

 

delete 字句

delete from Student where quxi=2

insert into Course values(2,96)

 

 

视图：

select * from Student

SNO		SNAME		SPSD		SAGE	SSEX	QUXI
4		robins		123		23.2	nan	4
1		robin		123		23	nan	3
3		zgpd		213		64	nan	1
CNO		CNAME
1		99
2		96

 

select s.sno,s.sname,c.cno,c.cname from Student s,Course c

SNO	SNAME	CNO	CNAME
4	robins	1	99
1	robin	1	99
3	zgpd	1	99
4	robins	2	96
1	robin	2	96
3	zgpd	2	96

 

create view stu

as

select s.sno,s.sname,c.cno,c.cname

from Student s,Course c

 

create view stu

as

select Student.sno,Student.sname,Course.cno,Course.cname

from Student,Course

where Student.sno=Course.cno;

 

索引：

create index snoindex on Student(sno)

drop index snoindex