ORACLE数据库查询语句

CREATE TABLE test (

username VARCHAR(20),

starttime VARCHAR(14),

endtime VARCHAR(14)

);

INSERT INTO test

SELECT 'zhangsan', '20130305080000', '20130305100000' FROM dual UNION ALL

SELECT 'zhangsan', '20130305070000', '20130305090000' FROM dual UNION ALL

SELECT 'lisi', '20130305060000', '20130305110000' FROM dual UNION ALL

SELECT 'lisi', '20130305070000', '20130305090000' FROM dual UNION ALL

SELECT '王五1', '20130305070000', '20130305090000' FROM dual UNION ALL

SELECT '王五1', '20130305060000', '20130305080000' FROM dual UNION ALL

SELECT '王五2', '20130305070000', '20130305090000' FROM dual UNION ALL

SELECT '王五2', '20130305070000', '20130305080000' FROM dual UNION ALL

SELECT '王五3', '20130305070000', '20130305090000' FROM dual UNION ALL

SELECT '王五3', '20130305060000', '20130305100000' FROM dual UNION ALL

SELECT '王五4', '20130305070000', '20130305090000' FROM dual UNION ALL

SELECT '王五4', '20130305070000', '20130305100000' FROM dual UNION ALL

SELECT '赵六1', '20130305060000', '20130305100000' FROM dual UNION ALL

SELECT '赵六1', '20130305070000', '20130305080000' FROM dual UNION ALL

SELECT '赵六2', '20130305060000', '20130305100000' FROM dual UNION ALL

SELECT '赵六2', '20130305060000', '20130305080000' FROM dual UNION ALL

SELECT '赵六3', '20130305060000', '20130305100000' FROM dual UNION ALL

SELECT '赵六3', '20130305070000', '20130305110000' FROM dual UNION ALL

SELECT '赵六4', '20130305060000', '20130305100000' FROM dual UNION ALL

SELECT '赵六4', '20130305060000', '20130305110000' FROM dual;

COLUMN "USERNAME" FORMAT A8

COLUMN "STARTTIME" FORMAT A15

COLUMN "ENDTIME" FORMAT A15

SELECT

FROM

test Main

WHERE

EXISTS (

SELECT 1

FROM

test Sub

WHERE

Mainusername = Subusername

AND NOT ( MainSTARTTIME = SubSTARTTIME AND MainENDTIME = SubENDTIME)

AND (

-- 首先判断 第一种情况

-- 时间段1：----------+=====+-----

-- 时间段2：--------+====+--------

-- 或者

-- 时间段1：----------+=====+-----

-- 时间段2：----------+====+--------

-- 或者

-- 时间段1：----------+=====+-----

-- 时间段2：--------+==========+--

-- 或者

-- 时间段1：----------+=====+-----

-- 时间段2：----------+==========+--

(

SubStartTime <= MainStartTime

AND SubEndTime > MainStartTime

)

OR

-- 然后判断第二种情况

-- 时间段1：------+=========+-----

-- 时间段2：--------+====+--------

-- 或者

-- 时间段1：------+=========+-----

-- 时间段2：------+====+--------

-- 或者

-- 时间段1：------+=========+-----

-- 时间段2：--------+==========+--

-- 或者

-- 时间段1：------+=========+-----

-- 时间段2：------+==========+--

(SubStartTime >= MainStartTime

AND SubStartTime < MainEndTime

)

)

);

1create user username identified by password;//建用户名和密码oracle ,oracle

2grant connect,resource,dba to username;//授权 grant connect,resource,dba,sysdba to username;

3connect username/password//进入。

4select table_name,column_name from user_tab_columns where table_name='TABLE_NAME';//查询表中的表名，字段名等等。 最后的table_name要大写。

5 如何执行脚本SQL文件 SQL>@PATH/filenamesql;

7查询用户下的所有表 select distinct table_name from user_tab_columns; ===仅显示一列表名。

8如何搜索出前N条记录？

select from tablename where rownum<n；--足矣。（--是注释用的标记）

9查找用户下的所有表：select from tab; --查询该用户下的所有表及视图（显示表名tname， 类型tabname和clusterid）

2、显示当前连接用户

SQL> show user –不能用在sql窗口 只能用在command命令窗口。

3、查看系统拥有哪些用户

SQL> select from all_users;

4、新建用户并授权

SQL> create user a identified by a;(默认建在SYSTEM表空间下)

SQL> grant connect,resource to a;

5、连接到新用户

SQL> conn a/a –或者是connect a/a

6、查询当前用户下所有对象

SQL> select from tab; --table或是view

7、建立第一个表

SQL> create table a(a number);

8、查询表结构

SQL> desc a

9、插入新记录

SQL> insert into a values(1);

10、查询记录

SQL> select from a;

11、更改记录

SQL> update a set a=2;

12、删除记录

SQL> delete from a;

13、回滚

SQL> roll;

SQL> rollback;

14、提交

SQL> commit;

select from

(select t,dense_rank() over (order by cardkind) rank from cardkind t)

where rank = 2;

46 如何在字符串里加回车

select 'Welcome to visit'||chr(10)||'>

回滚段查看 select rownum sys dba_rollback_segs segment_name Name v$rollstat extentsExtents v$rollstat rssize Size_in_Bytes v$rollstat xacts XActs v$rollstat gets Gets v$rollstat waits Waits v$rollstat writes Writes sys dba_rollback_segs status status from v$rollstat sys dba_rollback_segs v$rollname where v$rollname name(+) = sys dba_rollback_segs segment_name andv$rollstat usn (+) = v$rollname usn order by rownum 耗资源的进程(top session) select s schemaname schema_name decode(sign( mand) to_char(mand) Action Code # || to_char(mand) ) action statussession_status s osuser os_user_name s sid p spid s serial# serial_num nvl(s username [Oracle process] ) user_name s terminal terminal s program program st value criteria_value from v$sesstat st v$session s v$process pwhere st sid = s sid and st statistic# = to_number( ) and ( ALL = ALL or s status = ALL ) and p addr = s paddr order by st value desc p spid asc s username asc s osuser asc 查看锁(lock)情况 select /+ RULE / ls osuser os_user_name ls username user_name decode(ls type RW Row wait enqueue lock TM DML enqueue lock TX Transaction enqueue lock UL User supplied lock ) lock_type o object_name object decode(ls lmode null Row Share Row Exclusive Share Share Row Exclusive Exclusive null)lock_mode o owner ls sid ls serial# serial_num ls id ls id from sys dba_objects o ( select s osuser s username l type l lmode s sid s serial# l id l id from v$session s v$lock l where s sid = l sid ) ls where o object_id = ls id and o owner<> SYS order by o owner o object_name 查看等待(wait)情况 SELECT v$waitstat class v$unt count SUM(v$sysstat value) sum_valueFROM v$waitstat v$sysstat WHERE v$sysstat name IN ( db block gets consistent gets ) group by v$waitstat class v$unt 查看sga情况 SELECT NAME BYTES FROM SYS V_$SGASTAT ORDER BY NAME ASC 查看catched object SELECT owner name db_link namespace type sharable_mem loads executions locks pins kept FROM v$db_object_cache 查看V$SQLAREA SELECT SQL_TEXT SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS ROWS_PROCESSED FROM V$SQLAREA 查看object分类数量 select decode (o type# INDEX TABLE CLUSTER VIEW SYNONYM SEQUENCE OTHER ) object_type count() quantity fromsys obj$ o where o type# > group by decode (o type# INDEX TABLE CLUSTER VIEW SYNONYM SEQUENCE OTHER ) union select COLUMN count() from l$ union select DB LINK count() from 按用户查看object种类 select u name schema sum(decode(o type# NULL)) indexes sum(decode(o type# NULL)) tables sum(decode(o type# NULL))clusters sum(decode(o type# NULL)) views sum(decode(o type# NULL)) synonyms sum(decode(o type# NULL)) sequences sum(decode(o type# NULL NULL NULL NULL NULL NULL ))others from sys obj$ o sys user$ u where o type# >= and u user# =o owner# and u name <> PUBLIC group by u name order bysys link$ union select CONSTRAINT count() from n$ 有关connection的相关信息 )查看有哪些用户连接select s osuser os_user_name decode(sign( mand) to_char(mand) Action Code # || to_char(mand) ) action p program oracle_process status session_status s terminal terminal s program program s username user_name s fixed_table_sequence activity_meter query memory max_memory cpu_usage s sid s serial# serial_numfrom v$session s v$process p where s paddr=p addr and s type = USER order by s username s osuser )根据v sid查看对应连接的资源占用等情况select n name v value n class n statistic#from v$statname n v$sesstat vwhere v sid = andv statistic# = n statistic#order by n class n statistic# )根据sid查看对应连接正在运行的sqlselect /+ PUSH_SUBQ /mand_type sql_text sharable_mem persistent_mem runtime_mem sorts version_count loaded_versions open_versions users_opening executions users_executing loads first_load_time invalidations parse_calls disk_reads buffer_gets rows_processed sysdate start_time sysdate finish_time > || address sql_address N statusfrom v$sqlareawhere address = (select sql_address from v$session where sid = ) 查询表空间使用情况select a tablespace_name 表空间名称 round((nvl(b bytes_free )/a bytes_alloc) ) 占用率(%) round(a bytes_alloc/ / ) 容量(M) round(nvl(b bytes_free )/ / ) 空闲(M) round((a bytes_alloc nvl(b bytes_free ))/ / ) 使用(M) Largest 最大扩展段(M) to_char(sysdate yyyy mm dd hh :mi:ss ) 采样时间 from (select f tablespace_name sum(f bytes) bytes_alloc sum(decode(f autoextensible YES f maxbytes NO f bytes)) maxbytesfrom dba_data_files fgroup by tablespace_name) a (select f tablespace_name sum(f bytes) bytes_freefrom dba_free_space fgroup by tablespace_name) b (select round(max(ff length) / ) Largest ts name tablespace_namefrom sys fet$ ff sys file$ tf sys ts$ tswhere ts ts#=ff ts# and ff file#=tf relfile# and ts ts#=tf ts#group by ts name tf blocks) cwhere a tablespace_name = b tablespace_name and a tablespace_name = c tablespace_name 查询表空间的碎片程度 select tablespace_name count(tablespace_name) from dba_free_space group by tablespace_namehaving count(tablespace_name)> ;alter tablespace name coalesce;alter table name deallocate unused;create or replace view ts_blocks_v asselect tablespace_name block_id bytes blocks free space segment_name from dba_free_spaceunion allselect tablespace_name block_id bytes blocks s lishixinzhi/Article/program/Oracle/201311/18531

以上就是关于ORACLE数据库查询语句全部的内容，包括:ORACLE数据库查询语句、Oracle的查询语句怎么写、数据库手册：Oracle维护常用SQL语句二等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！