一:基本命令
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 表名