oracle常用sql语句

创建表空间

CREATE TABLESPACE mydb DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\mydb ' SIZE 200000K AUTOEXTEND ON NEXT 10000K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
--新增记录
insert into tbl_person(id,name,sex,birthday,salary,classid)
values([color=blue]S_PERSON.Nextval[/color],'huqitao9','m',[color=blue]to_date[/color]('1987-1-2','yyyy-mm-dd'),2600,1)--使用sequences可以实现序列自增长

--批量插入数据
insert into tbl_person(id,name,sex,birthday,salary,classid)
select [color=blue]s_person.nextval[/color],name ,sex,birthday,salary,classid from tbl_person where classid=1

--复制表及其数据
create table tbl_person1
as
select * from tbl_person where[color=blue] 1<>1 -- 1=1复制数据及其结构 1<>1复制结构但不复制数据[/color]
--删除数据
delete from tbl_person where id =14

--更新数据
update tbl_person set
sex = 'm',
birthday = to_date('1999-5-8','yyyy-mm-dd')
where id = 10
select name,sex,salary from tbl_person
select * from tbl_person
select distinct sex, classid from tbl_person --[color=blue]distinct 查询记录并消除重复行[/color]select * from tbl_person where sex = 'f'
select * from tbl_person where salary >= 3000
select * from tbl_person where sex <> 'f'-- != ,<>都可以表示不等于
select * from tbl_person where salary between 2500 and 3500 --包含了边界值
select * from tbl_person where classid is null --classid = ''表示classid等于一个''字符串，它与null是不一样的
select * from tbl_person where classid = 1 and sex='f' --and 表示逻辑与
select * from tbl_person where classid = 1 or sex='f' --or表示逻辑或

select * from tbl_person where name like 'hu%'--%表示任意的，任意个字符
select * from tbl_person where name like 'ljj_'-- _表示一个字符
select * from tbl_person where id in (1,3,5,7,9,11,13,15,17,19)

--聚合函数
select count(*) from tbl_person where sex = 'm' --[color=blue]count计算查询结果的记录数[/color]select max(salary) from tbl_person --取最大值
select min(salary) from tbl_person --取最小值
select sum(salary) from tbl_person --求和
select avg(salary) from tbl_person --求平均值

--分组查询
select classid,count(*) from tbl_person--分组查询中， 查询的字段只能是分组的条件或者是聚合函数
group by classid

select classid from tbl_person
group by classid
having count(*)>=5 --[color=blue]having限定每组组内的条件[/color]

select classid from tbl_person
where sex='m' --[color=blue]where来限制所有数据的条件[/color]
group by classid
having count(*) >=4
select * from tbl_person order by classid asc, salary desc
[img]http://dl.iteye.com/upload/attachment/0073/2428/1cda39c9-3399-3556-a12a-4b6818357d24.jpg[/img]
select * from tbl_person where salary > any(select salary from tbl_person where classid=1)--any表示任意一条记录，[color=blue]该例子中表示大于它结果中的最小值[/color]select * from tbl_person where salary > all(select salary from tbl_person where classid=1)--all表示所有记录，[color=blue]该例子中表示大于结果中的最大值[/color]
select sysdate from dual--from dual 表示在结果视图中显示值 ,dual不代表一个表
[img]http://dl.iteye.com/upload/attachment/0073/2428/1cda39c9-3399-3556-a12a-4b6818357d24.jpg[/img]


取最新的一条数据
select * from ( select * from issuelogs t where issueid=81 order by dealtime desc ) where rownum = 1