sql简单查询

SELECT ‘DROP TABLE ‘||TABLE_NAME||’;’ FROM USER_TABLES WHERE TABLE_NAME LIKE ‘NEDW%’; ----sql拼接

FLASHBACK TABLE a TO BEFORE DROP; --闪回删除表

select * from 表名 as of timestamp(sysdate-5/1440); —闪回到5分钟前

select * from DBA_OBJECTS T where T.OBJECT_TYPE LIKE ‘%PACKGE’ —查看数据库的包和包体

select * from sys.v_$version —查看oracle版本

SELECT * from v$locke{d}_{o}bjecti,Al{l}_{O}bjectso,v$session s WHERE i.OBJECT_ID=o.OBJECT_ID AND i.SESSION_ID=s.SID;

alter system kill session ‘1293,12637’ immediate;

SELECT spid,osuser,s.PROgram from v$sessions,v$process p WHERE s.PADDR=p.ADDR AND s.SID=‘37’;

–表空间
CREATE TABLESPACE temptablespace_lxx datafile ‘D:\temptablespace_lxx.ora’ SIZE 1000m;
SELECT * FROM User_Tablespaces;
SELECT * from user_users;
SELECT A.TABLESPACE_NAME, (1 - (A.TOTAL) / B.TOTAL) * 100 USED_PERCENT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;–查看表空间的增张情况
SELECT tablespace_name,segment_space_management from dba_tablespaces; --查看表空间是否手动管理
SELECT * from dba_ts_quotas; --查询配额

–用户
CREATE USER bbsp6 IDENTIFIED BY lxx1 DEFAULT TABLESPACE tablespace_lxx QUOTA 500m ON USERS TEMPORARY TABLESPACE temptablespace_lxx;
DROP USER lxxx CASCADE;–删除用户(如果用户下有对象，用cascade，可删除用户所有的对象，然后再删除用户)
ALTER USER lxx IDENTIFIED BY lxxx;–修改用户密码
ALTER USER lxx ACCOUNT LOCK; --用户锁定
ALTER USER lxx ACCOUNT UNLOCK;–用户解锁
SELECT * from dba_sys_privs;–查看用户系统权限
SELECT * from user_sys_privs;–查看当前用户系统权限
SELECT * from Session_Privs;–查看当前会话系统权限
SELECT * from USER_TABLES;–查看用户下的所有表
SELECT * from User_Objects WHERE instr(object_name,‘DE’)>0; --查看用户下的所有表
SELECT SUM(BYTES) / (1024 * 1024) AS “size(M)”
FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER(‘&table_name’);–查看某表的大小
DROP USER APEX_030200 CASCADE; --删除用户
ALTER TABLE test_lxx RENAME TO aa_lxx;–修改表名
ALTER TABLE aa_lxx ADD age VARCHAR2(3);–增加字段
ALTER TABLE TEST_lxx Modify age VARCHAR2(4);–修改字段属性
ALTER TABLE test_lxx DROP COLUMN age;–删除字段
ALTER TABLE TEST_lxx RENAME COLUMN age TO lxx_age;–修改字段名
CONNECT:ALTER SESSION、CREATE CLUSTER,CREATE databaselink,CREATE sequence,CREATE SESSION,CREATE synonym,CREATE TABLE,CREATE triggr
resource:CREATE cluster,CREATE PROCEDURE,CREATE sequence,CREATE TABLE,CREATE triggr
SELECT * from dba_USERS; --用户状态

–角色
CREATE ROLE student;–创建角色
GRANT SELECT ON CLASS TO student;–给角色赋权
DROP ROLE student;–删除角色s
GRANT ALL PRIVILEGES TO lxx;–赋权
GRANT CONNECT ,resource,DBA TO lxx;–赋权
GRANT SELECT ANY TABLE TO TEST;–赋权给test表select权限
GRANT UPDATE(job) ON emp1 TO TEST;–把表的某一列操作权限赋权给其他用户
SELECT * from User_Tab_Privs_Made;–查看都把哪些表什么权限赋予了其他用户
GRANT ALL TO USER;–赋权
GRANT CREATE SESSION,SELECT ANY TABLE,DBA TO USER;–赋权
GRANT CREATE SESSION,CREATE TABLE,SELECT ANY TABLE,DBA TO ceshi WITH ADMIN OPTION;–(admin option 使该用户具有转授系统权限的权限)
REVOKE CREATE TABLE FROM lxx;–收回系统权限
REVOKE CREATE SESSION FROM lxx;–收回系统权限
SELECT * from Session_Roles;–查询当前用户的所有有效角色
SELECT * from User_Role_Privs;–查询当前用户的所有有效角色
SELECT * from User_Sys_Privs;–查看当前用户的系统权限/表级权限
SELECT * from User_Tab_Privs;–查看当前用户的系统权限/表级权限
SELECT * from dba_sys_privs;–查看当前用户的系统权限/表级权限

–数据库连接db_link(需要dba权限)
drop PUBLIC database link LINK_BBSP;
SELECT * from dba_objects WHERE object_type=‘DATABASE LINK’;
SELECT * from DBA_DB_LINKS;
create database link LINK_abc
connect to bems IDENTIFIED by bems
using ‘(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.200.44.86)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bems)
)
)’;
SELECT * from Dba_Db_Links; --查看所有的db_link

–同义词 Create the synonym
create or replace synonym EMP3 for SCOTT.EMP3;
create or replace synonym EMP3 FOR bank@link_bbsp;

–存储过程
SELECT * from user_objects WHERE object_type = ‘PROCEDURE’;
SELECT TASK_NAME,
ROUND((MAX(END_TIME) - MIN(START_TIME)) * 24 * 60, 4) AS ff
FROM SYS_LOGRECORD
GROUP BY TASK_NAME
ORDER BY ff DESC;–查看存储过程跑批时间

–数据字典
SELECT * from user_segments;–查看表和索引的段，区，…数据字典
SELECT * from System_Privilege_Map;–查看所有系统权限
SELECT * from Dba_Ts_Quotas;–查看数据库用户使用情况
select userenv(‘language’) from dual; --查看数据库表字符集
SELECT * from Nls_Database_Parameters;–查看数据库表字符集
SELECT * FROM v$nls_parameters; --查看数据库表字符集

–索引
SELECT * from user_indexes;–查看索引
create INDEX index_emp_deptno ON emp (deptno) TABLESPACE temptablespace_lxx;–创建索引
DROP INDEX index_emp_deptno;–删除索引
SELECT * from User_Ind_Columns;–查看索引被索引的字段
SELECT SUM(BYTES) / (1024 * 1024) AS “size(M)”
FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER(‘&index_name’);–查看某索引的大小

–序列
SELECT * FROM user_sequences;–查看序列号

–视图
SELECT * from USER_VIEWS; --查看视图的名称

–约束
SELECT * FROM user_constraints; --查看约束条件
ALTER TABLE test_lxx ADD CONSTRAINT pk_test PRIMARY KEY (NAME);–建主键
alter table EMP add constraint FK_TEST foreign key (DEPTNO) references DEPT (DEPTNO) on delete cascade;–建外键
ALTER TABLE test_lxx DROP CONSTRAINT pk_test;–删主键

–数据库
SELECT * FROM product_component_version;–查看数据库版本/信息
SELECT * FROM v$version;--查看数据库版本/信息SELECTNAME,created,lo{g}_{m}odefromv$database;–查看数据库版本/信息
select * from 表名 as of timestamp(sysdate-5/1440);–还原到五分钟之前
SELECT USERENV(‘language’) FROM dual; --查看数据库编码
select * from sys.v_$version ; --查看oracle版本

–删除重复记录
DELETE FROM EMP_LXX LX
WHERE ROWID < (SELECT MAX(ROWID)
FROM EMP_LXX LXX
WHERE LXX.EMPNO = LX.EMPNO
GROUP BY ENAME
HAVING COUNT(*) > 0);

–函数
SELECT ABS(COMM) from EMP;–取绝对值
SELECT POWER(3,2) FROM dual;–乘方
SELECT SQRT(4) from DUAL;–平方根
SELECT CEIL(4.4) from DUAL;–大于或等于取整数
SELECT FLOOR(4.4) from DUAL;–小于或等于取整数
SELECT MOD(4,3) from DUAL;–取余数
SELECT ROUND(4.3) from DUAL;–按m的位数取四舍五入值如果round(日期): 中午12以后将是明天的日期. round(sysdate,‘Y’)是年的第一天
SELECT ROUND(SYSDATE) from DUAL;–按m的位数取四舍五入值如果round(日期): 中午12以后将是明天的日期. round(sysdate,‘Y’)是年的第一天
SELECT ROUND(SYSDATE,‘Y’) from DUAL;–按m的位数取四舍五入值如果round(日期): 中午12以后将是明天的日期. round(sysdate,‘Y’)是年的第一天
SELECT CHR(4) from DUAL;–CHR 按数据库的字符集由数字返回字符
SELECT concat(4,3) from DUAL;–CONCAT(c1,c2) 把两个字符c1,c2组合成一个字符, 和 || 相同
SELECT replace(234345345,4,1) from DUAL;–REPLACE(c,s,r) 把字符c里出现s的字符替换成r, 返回新字符
SELECT SUBSTR(134345,3,5) from DUAL;–SUBSTR(c,m,n) m大于0,字符c从前面m处开始取n位字符,m等于0和1一样, m小与0,字符c从后面m处开始取n位字符
SELECT SUBSTR(134345,-3,5) from DUAL;–SUBSTR(c,m,n) m大于0,字符c从前面m处开始取n位字符,m等于0和1一样, m小与0,字符c从后面m处开始取n位字符
SELECT TRANSLATE(134345,3,5) from DUAL;–TRANSLATE(c,f1,t1) 字符c按f1到t1的规则转换成新的字符串
SELECT INITCAP(‘sdfvfvsdfv’) from DUAL;–INITCAP 字符首字母大写,其它字符小写
SELECT LOWER(‘DFGDFGR’) from DUAL;–LOWER 字符全部小写
SELECT UPPER(‘DSdfdfv’) from DUAL;–UPPER 字符全部大写
SELECT LTRIM(‘ssssdcsdc’,‘s’) from dual;–LTRIM(c1,c2) 去掉字符c1左边出现的字符c2
SELECT RTRIM(‘ssssdcsdcsss’,‘s’) from dual;–RTRIM(c1,c2) 去掉字符c1右边出现的字符c2
SELECT TRIM(BOTH ‘’ FROM ‘llllllll’) from dual;
SELECT TRIM(LEADING '’ FROM ‘llllllll’) from dual;
SELECT TRIM(TRAILING ‘*’ FROM ‘llllllll’) from dual;
SELECT length(TRIM(’ 23v ')) from dual;—TRIM(c1,c2) 去左右空格
SELECT LPAD(‘sdvsd’,‘9’,‘c’) from dual;–LPAD(c1,n,c2) 字符c1按指定的位数n显示，不足的位数用c2字符串替换左边的空位
SELECT rPAD(‘sdvsd’,‘9’,‘c’) from dual;–RPAD(c1,n,c2) 字符c1按指定的位数n显示，不足的位数用c2字符串替换右边的空位
SELECT add_months(to_date(‘2012-12-12’,‘yyyy-mm-dd’) ,‘3’) from dual;–ADD_MONTHS(d,n) 日期值加n月
SELECT last_day(to_date(‘2012-12-12’,‘yyyy-mm-dd’) ) FROM dual;–LAST_DAY(d) 返回当月的最后一天的日期
SELECT MONTHS_BETWEEN(TO_DATE(‘2012-12-2’, ‘yyyy-mm-dd’), SYSDATE) FROM DUAL; --MONTHS_BETWEEN(d1,d2) 两个日期值间的月份
SELECT NEXT_DAY(SYSDATE,2) from dual;–NEXT_DAY(d) 返回日期值下一天的日期
SELECT NVL(comm,‘444444’) from emp;–NVL(EXPR1, EXPR2)当EXPR1为空用EXPR2替代
SELECT DECODE(deptno,‘20’,‘22’,‘30’,‘33’) from emp;–DECODE(EXPR﹐V1﹐R1﹐V2﹐R2…)EXPR后条件和返回值成对出现，最后一个单值是不在条件中的返回值，如果没有单值，不满足条件的返回NULL。
SELECT to_CHAR(SYSDATE, ‘MM’) FROM dual;–TO_CHAR(HIREDATE, ‘MM’)取时间月份
SELECT * from EMP_BONUS WHERE COalesce(DE,0)<‘12’;–把de列的空值也参与比较（返回其参数中第一个非空表达式）

–0.排序求5-10条数据
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY SAL) AS RN, A.*
FROM EMP A
WHERE SAL IS NOT NULL)
WHERE RN BETWEEN 6 AND 10;

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ROWID) ROW_NUMBER, A.*
FROM AA_LXX A)
WHERE ROW_NUMBER <= 10; --前十条数据

SELECT * FROM EMP WHERE ROWNUM <= 2 ORDER BY SAL
SELECT *
FROM (SELECT E.*, ROWNUM RN FROM EMP E WHERE ROWNUM <= 8 ORDER BY SAL)
WHERE RN > 2 --排序求2-8条数据

SELECT *
FROM EMP
WHERE ROWNUM < 10
MINUS
SELECT * FROM EMP WHERE ROWNUM < 5; --返回第5-9条记录

–1.查询课程1比课程2成绩高的所有学生的学号
SELECT A.S#, A.SCORE, B.SCORE
FROM (SELECT * FROM SC WHERE C# = ‘2’) A,
(SELECT * FROM SC WHERE C# = ‘1’) B
WHERE A.SCORE < B.SCORE
AND A.S# = B.S#;

–2.查询平均成绩大于60分的同学的学号和平均成绩
SELECT S#, AVG(SCORE) FROM SC GROUP BY S# HAVING AVG(SCORE) > ‘60’;

–3.查询所有同学的学号、姓名、选课数、总成绩
SELECT S.S#, S.SNAME, COUNT(SC.C#), SUM(SC.SCORE)
FROM SC SC, STUDENT S
WHERE SC.S# = S.S#
GROUP BY S.S#, S.SNAME;

–4.查询姓江的老师的个数
SELECT COUNT(DISTINCT(T#)) FROM TEACHER WHERE TNAME LIKE ‘江%’;

–5.查询没学过江老师课的同学的学号、姓名
SELECT S#, SNAME
FROM STUDENT
WHERE S# NOT IN (SELECT SC.S#
FROM TEACHER T, SC SC, COURSE C
WHERE SC.C# = C.C#
AND C.T# = T.T#
AND TNAME = ‘江老师’);

–6.查询学过1课并且也学过2课的同学的学号、姓名
SELECT S.S#, SNAME
FROM STUDENT S, SC SC
WHERE SC.S# = S.S#
AND SC.C# = ‘1’
AND EXISTS (SELECT *
FROM SC SC_2
WHERE SC_2.S# = SC.S#
AND SC_2.C# = ‘2’);

–7.查询学过江老师所交的所有课的同学的学号、姓名
SELECT S#, SNAME
FROM STUDENT WHERE S# IN (SELECT SC.S#
FROM SC SC, COURSE C, TEACHER T
WHERE T.T# = C.T#
AND SC.C# = C.C#
AND TNAME = ‘江老师’
GROUP BY S#
HAVING COUNT(c.c#) =(SELECT COUNT(c#)
FROM COURSE C, TEACHER T
WHERE T.T# = C.T#
AND TNAME = ‘江老师’));

–8、查询所有课程成绩小于60分的同学的学号、姓名
SELECT S#, SNAME
FROM STUDENT
WHERE S# NOT IN (SELECT S.S#
FROM STUDENT S, SC SC
WHERE SC.S# = S.S#
AND SCORE > 60);

–9.查询没有学全所有课的同学的学号、姓名
SELECT S.S#, SNAME
FROM SC SC, STUDENT S
WHERE SC.S# = S.S#
GROUP BY S.S#, S.SNAME
HAVING COUNT(C#) < (SELECT COUNT(C#) FROM COURSE);

–10、查询至少有一门课与学号为012512的同学所学相同的同学的学号和姓名
SELECT S.S#, SNAME
FROM SC SC, STUDENT S
WHERE SC.S# = S.S#
AND C# IN (SELECT C# FROM SC WHERE S# = ‘012512’)
GROUP BY S.S#, SNAME;

–11、删除学习江老师课的sc表记录
DELETE sc WHERE c# IN (SELECT DISTINCT SC.C#
FROM SC SC, TEACHER T, COURSE C
WHERE T.T# = C.T#
AND C.C# = SC.C#
AND TNAME = ‘江老师’);

–12.查询各科成绩最高和最低的分；以如下形式显示:课程id，最高分，最低分；
SELECT C#, MAX(SCORE), MIN(SCORE) FROM SC GROUP BY C#;

–13、查询学生平均成绩及其名次
SELECT 1 + (SELECT COUNT(DISTINCT 平均成绩)
FROM (SELECT S#, AVG(SCORE) 平均成绩 FROM SC GROUP BY S#) T1
WHERE 平均成绩 > T2.平均成绩) 名次,
S# 学生学号,平均成绩
FROM (SELECT S#, AVG(SCORE) 平均成绩 FROM SC GROUP BY S#) T2
ORDER BY 平均成绩 DESC;

SELECT SS.* ,ROWNUM from (SELECT SC.s#, AVG(SCORE) AS AVG
FROM SC SC, STUDENT S
WHERE SC.S# = S.S#
GROUP BY SC.s# ORDER BY AVG DESC ) SS;

–14.查询各科成绩前三名的记录；（不考虑成绩并列的情况）
SELECT *
FROM (SELECT C#,
ROW_NUMBER() OVER(PARTITION BY C# ORDER BY SCORE DESC) RN,
S.S#,
S.SNAME,
SC.SCORE
FROM SC SC, STUDENT S
WHERE SC.S# = S.S#) A
WHERE RN < 4;

–15.查询每门功课成绩最好的第2.3名
SELECT *
FROM (SELECT C#,
ROW_NUMBER() OVER(PARTITION BY C# ORDER BY SCORE DESC) RN,
S.S#,
S.SNAME,
SC.SCORE
FROM SC SC, STUDENT S
WHERE SC.S# = S.S#) A
WHERE RN < 4 AND rn>1;

–16.表中有abc三列，用sql语句实现：当A列大于B列时选A列否则选择B列，当B列大于C列时选择B列否则选择C列
SELECT A,
B,
C,
(CASE
WHEN TO_NUMBER(A) > TO_NUMBER(B) THEN
A
ELSE
B
END),
(CASE
WHEN TO_NUMBER(B) > TO_NUMBER© THEN
B
ELSE
C
END)
FROM SC;

–17.有一张表有abc三列分别语文数学英语，70,80,58分，写：大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格，
/显示格式：语文 数学 英语
及格 优秀 不及格/
SELECT A,
B,
C,
(CASE
WHEN A >= 80 THEN
‘优秀’
WHEN A < 60 THEN
‘不及格’
ELSE
‘及格’
END) AS 语文,
(CASE
WHEN B >= 80 THEN
‘优秀’
WHEN B < 60 THEN
‘不及格’
ELSE
‘及格’
END) AS 数学,
(CASE
WHEN C >= 80 THEN
‘优秀’
WHEN C < 60 THEN
‘不及格’
ELSE
‘及格’
END) AS 英语
FROM SC;

–18.查询出dept表中哪个部门下没有员工
SELECT DEPTNO FROM DEPT
MINUS
SELECT DISTINCT DEPTNO FROM EMP;

–19.查询出Emp表中比任意一个销售员(“SALESMAN”)工资低的员工姓名、工作、工资。
SELECT * from emp WHERE sal< ANY(SELECT sal from emp WHERE job=‘SALESMAN’);

–20.查询出比所有销售员的工资都高的员工姓名，工作，工资。
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB = ‘SALESMAN’);

–21.查询出工资最高的前5名员工的姓名、工资和工资。
SELECT ROWNUM ,sc.c# from (SELECT * from sc ORDER BY score) sc WHERE ROWNUM<6;

–22.对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元。
SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) “comm” FROM EMP WHERE SAL<2000;

–23.用一条sql语句查询出每门课都大于80分的学生姓名
SELECT * FROM SC SC WHERE S# NOT IN (SELECT S# FROM SC WHERE SCORE < 80);
SELECT S# FROM SC GROUP BY S# HAVING MIN(SCORE) > 80;

–24.最高效的去除重复记录方法（因为使用了rowid）去重
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMPNO = E.EMPNO);

–25.一个叫team的表，里面只有一个字段name,一共有四条记录，abcd四个球队，四个球队比赛，有多少种组合
SELECT A.NAME, B.NAME FROM TEAM A, TEAM B WHERE A.NAME < B.NAME;

–26.有两个表A,B 均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value
UPDATE B
SET B.VALUE =
(SELECT A.VALUE FROM A WHERE A.KEY = B.KEY)
WHERE B.ID IN (SELECT B.ID FROM B, A WHERE B.KEY = A.KEY)

–27.有一个表a有两个字段key value，字段都非空，写一个sql列出该表中一个key对应多个不同的value的记录
SELECT A.KEY || ‘a’ || A.VALUE
FROM A, A B
WHERE A.KEY = B.KEY
AND A.VALUE <> B.VALUE;

SELECT *
FROM A
WHERE KEY IN
(SELECT KEY FROM A GROUP BY KEY HAVING(COUNT(DISTINCT(VALUE)) >= 2));

–28.计算工资>999年龄<35的人数，工资>999年龄>35的人数，工资<999年龄<35的人数，工资<999年龄>35的人数
SELECT SUM(CASE WHEN (sal>999 AND age<35) THEN 1 ELSE 0 END ) ,
SUM(CASE WHEN (sal>999 AND age<35) THEN 1 ELSE 0 END ) ,
SUM(CASE WHEN (sal<999 AND age>35) THEN 1 ELSE 0 END ) ,
SUM(CASE WHEN (sal<999 AND age>35) THEN 1 ELSE 0 END )
from emp1 ;

–29.查询月份，当月收入，上月收入，下月收入
SELECT MONTHS AS “月份”,
MAX(INCOMES) AS “收入”,
MAX(PREV_MONTHS) AS “上月收入”,
MAX(NEXT_MONTHS) AS “下月收入”
FROM (SELECT MONTHS,
INCOMES,
DECODE(LAG(MONTHS) OVER(ORDER BY MONTHS),
TO_CHAR(ADD_MONTHS(TO_DATE(MONTHS, ‘yyyymm’), -1),
‘yyyymm’),
LAG(INCOMES) OVER(ORDER BY MONTHS),
0) AS PREV_MONTHS,
DECODE(LEAD(MONTHS) OVER(ORDER BY MONTHS),
TO_CHAR(ADD_MONTHS(TO_DATE(MONTHS, ‘yyyymm’), 1),
‘yyyymm’),
LEAD(INCOMES) OVER(ORDER BY MONTHS),
0) AS NEXT_MONTHS
FROM (SELECT MONTHS, SUM(SAL) AS INCOMES FROM EMP1 GROUP BY MONTHS) AA) AAA
GROUP BY MONTHS;

–30.表b 要求如下格式展示
/c1 c2 * 2005-01-01 4
2005-01-01 1 * 2005-01-02 5
2005-01-01 3 * 合计 9
2005-01-02 5/

SELECT NVL(TO_CHAR(C1, ‘yyyy-mm-dd’), ‘合计’), SUM(C2)
FROM B
GROUP BY ROLLUP(C1);

–31.

–32.查询数据库有多少张表有多少条数
SELECT table_name,num_rows FROM user_tables;

–33.把表和index都收集一下
ANALYZE TABLE emp COMPUTE STATISTICS;
ANALYZE index xxx COMPUTE STATISTICS;
–34.有两个表A,B 均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value
UPDATE B
SET B.VALUE =
(SELECT A.VALUE FROM A WHERE A.KEY = B.KEY)
WHERE B.ID IN (SELECT B.ID FROM B, A WHERE B.KEY = A.KEY)

–35.随机取数
SELECT * from (SELECT * from EMP ORDER BY dbms_random.value()) WHERE ROWNUM <=3;