oracle测试实用SQL语句

• .查询当前数据库中所有表名

select * 
from user_tables;

• .查询指定表中的所有字段名

select column_name 
from user_tab_columns 
where table_name = 'table_name';--表名要全大写

• .查询指定表中的所有字段名和字段类型

select column_name, data_type 
from user_tab_columns 
where table_name = 'table_name';--表名要全大写

• .查询指定表的所有列名和相应的列注释

select * 
from user_col_comments 
where table_name = 'table_name';--表名要全大写

• 给表中列名添加注释

comment on column 表名.列名 is '注释';

• .查询当前数据库中所有表名和注释

select * 
from user_tab_comments;

• .对于指定的字段所选择的重复行，只返回一行

select distinct job 
from emp
where deptno = 20;

• .向数据库中按表中字段插入数据

insert into table_name(列名1，列名2，列名3,) 
values(值1,值2，值3);

• .向数据库中插入数据

insert into table_name 
values(值1，值2，值3),(值1，值2，值3),······,(值1，值2，值3);--值要和表中的字段顺序类型一致

• .按时间条件查询

select *
from 表名
where 时间字段名 > to_date('20210414 16:00:00','yyyy/mm/dd hh24:mi:ss');

• 按时间查询(时间是String型)

SELECT *
FROM 表名
WHERE  TO_DATE(字段名, 'YYYY-MM-DD') > TO_DATE('20210601', 'YYYY-MM-DD')

• .查询系统当前时间

select TO_Char(SysDate,'YYYY-MM-DD HH24:MI:SS') 
from Dual;

• .查系统日期

select sysdate from dual; --当前日期和时间
select trunc(sysdate) from dual;--今天日期
select trunc(sysdate ,'DD') from dual; --今天日期
select trunc(sysdate,'d')+7 from dual;--本周星期日的日期
select trunc(sysdate,'dy')+7 from dual;  --本周星期日的日期
select trunc(sysdate,'day')+7 from dual; --本周星期日的日期
select trunc(sysdate,'q') from dual; --本季开始日期
select trunc(sysdate,'month') from dual; --本月开始日期
select trunc(sysdate ,'mm') from dual; --本月开始日期
select trunc(sysdate,'year') from dual;  --本年开始日期
select trunc(sysdate ,'yyyy') from dual; --本年开始日期
select trunc(sysdate ,'HH24') from dual; --本小时开始时间
select trunc(sysdate ,'MI') from dual; --本分钟开始时间
select trunc(sysdate ,'CC') from dual; --本世纪开始日期
select trunc(LAST_DAY(sysdate),'dd') from dual; --本月最后一天

• .删除表中一行数据，删除操作作为事务记录在日志中保存

DELETE FROM 表名称 
WHERE 列名称 = 值;

• .清空表中所有数据，没有日志

TRUNCATE TABLE 表名称

• .删除表

DROP TABLE table_name

• .以cno升序、degree降序查询Score表的所有记录

select * 
from score 
order by cno,degree desc

• .查询表QTY中列名NAME下，LOVE条件，有几条数据

select count(NAME)
from QTY
where NAME = 'LOVE';

• .查询表QTY中，最高QTY_D的，QTY_D、SEQ_ID和NAME

select NAME,SEQ_ID,QTY_D
from QTY
where QTY_D in(select max(QTY_D) from QTY);

• .查询表QTY中，相同NAME下，QTY_D的平均值

select avg(QTY_D),NAME
from QTY
group by NAME;

• .查询QTY表中，至少有5个相同NAME下，并以“冰”开头的，QTY_D的平均值、NAME和相同NAME的数量

select avg(QTY_D),NAME,count(PRODUCT_CATEGORY_NAME)
from QTY
where NAME like '冰%'
group by NAME
having count(NAME)>5;

• .查询QTY表中，多个字段，和他们的平均值（字段必须是数字型）

select QTY_D,QTY_D_TQ,QTY_M,QTY_M_TQ,(QTY_D+QTY_D_TQ+QTY_M+QTY_M_TQ)/4 as average
from QTY

• .查询QTY表中，QTY_D的值，并对QTY_D的值做出评价

select QTY_D,(case when QTY_D<20 then '低' 
                   when QTY_D>=20 and QTY_D<=50 then '中' 
                   when QTY_D>50 then '高' 
                   else '低' end) as evaluation
from QTY

--模板
CASE WHEN condition THEN result
 
[WHEN...THEN...]
 
ELSE result
 
END

condition是一个返回布尔类型的表达式，
如果表达式返回true，则整个函数返回相应result的值，
如果表达式皆为false，则返回ElSE后result的值，
如果省略了ELSE子句，则返回NULL。

详细用法

• 快速检查有无数据

select max(字段名),min(字段名)
from 表名

• 查询百分比数

select _NAME,sum(_M),sum(_PBP),round(sum(_M)/sum(_PBP),3)*100||'%' pbp达成率
from 表名
group by _NAME;

SELECT decode(achieve_rate,0,'0.00', to_char(round(achieve_rate*100,2),'FM9999999999999999.00'))||'%'
FROM ACHIEVE_RATE -- 查询结果会保留小数点后的0

• 返回结果集中前10行

select * from tableName where rownum <= 10;

• 返回结果集中第10行到第100行

select * from (select rownum rn,t.* from tableName t) where rn<=100 and rn >=10; 

• 查询时返回序号

select t.id,t.score 数学分数,rownum
from
(select b.id,b.score
from grade b
where b.subject='数学'
order by score
desc) t,
student a
where a.id=t.id