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(S_PERSON.Nextval,'huqitao9','m',to_date('1987-1-2','yyyy-mm-dd'),2600,1)--使用sequences可以实现序列自增长

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

--复制表及其数据
create table tbl_person1
as
select * from tbl_person where 1<>1     --  1=1复制数据及其结构   1<>1复制结构但不复制数据
--删除数据
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  --distinct 查询记录并消除重复行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'  --count计算查询结果的记录数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 --having限定每组组内的条件

select classid from tbl_person
where sex='m' --where来限制所有数据的条件
group by classid
having count(*) >=4
select * from tbl_person order by classid asc, salary desc

select * from tbl_person where salary > any(select salary from tbl_person where classid=1)--any表示任意一条记录，该例子中表示大于它结果中的最小值select * from tbl_person where salary > all(select salary from tbl_person where classid=1)--all表示所有记录，该例子中表示大于结果中的最大值
select sysdate from dual--from dual 表示在结果视图中显示值 ,dual不代表一个表



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