Oracle常用命令

一：基本命令

1.进入oracle

sqlplus/nolog

2.创建用户名或密码

create user username identified by password

3.授权命令

grant connect, resource to username

4.切换用户

connect username/password

5.查询当前用户下的所有表

select * from user_tables

二：增删改查

1.创建表格

create table tablename(id number(6,0) not null primary key,name varchar2(20),pwd varchar(20),regdate date);

2.删除表格

drop table tabname;

3.查看是否成功表格

desc tablename

4.插入数据

insert into stu(id) values (1);

5.删除数据

delete from stu where id=1;

6.修改数据

update stu set id =5 where id=1;

7..查看数据

select * from stu

三：高级查询

1.去处重复

select distinct sore from student

2.模糊查询

select *from emp where ename like '_M%‘

3.升序

select *from emp order by sal asc

4.降序

select *from emp order by sal desc

5.左连接

select * from student left join message on student.sore=message.sore

6.右连接

select * from student right join message on student.sore=message.sore

7.内连接

select * from student inner join message on student.sore=message.sore

8.分组

select count(*) from student group by sore

9.创建视图 (授权grant create view to scott)

create view stu_message as select s.id,s.name,s.age,s.class,s.sex,s.sore,m.grade,m.company,i.pay,i.object from student s inner join message m on s.sore=m.sore inner join stulive i on m.grade=i.grade

10.取前三条

select * from(select sore from student order by sore asc) where rownum < 4

11.子查询

select * from(select sore from student order by sore asc)

12.and与or

select * from(select sore from student order by sore asc);select sore from student where sore =450 or sore=420;

13.between语句用于取范围内的值

select * from 表名 where  列名 between ? and ?

select * from 表名 where  列名  no between ? and ?

14.avg()语句用于返回平均值

select avg (列名) from 表名

15.count()语句返回列值的多少，null不算

select count (列名) from 表名

select count (distinct 列名) from 表名

16.sum()用于返回列值得总和

select sum (列名) from 表名