工作中总结的相关sql积累(Oracle)

select /*+ parallel(a,4)*/
       *
      FROM 
     all_col_comments a
     where a.OWNER='用户名'


select /*+ parallel(a,4)*/
       *
      FROM 
     all_col_comments a
     where a.TABLE_NAME = '表名';

2、当前用户下所有表名

select k.TABLE_NAME  from user_tables；

3、sql语句导库

exp szcrt/szcrt@orcl owner=szcrt file=d:\crt1.dmp

4、union连接

 select max(id) from (
SELECT id FROM user_test
union
SELECT id FROM ORDER_test
);

5、查询一张表的所有字段名

select column_name from user_tab_cols where table_name='表名'

6、日期类型时分秒sql

to_date('2017-08-02 15:33:09', 'YYYY-MM-DD HH24:MI:SS'),

to_char(sysdate,'Day, HH12:MI:SS');

select to_char(sysdate,'Month,Day, HH12:MI:SS') from dual;

结果:

当天日期:

to_date(to_char(sysdate,'YYYY-MM-DD'), 'YYYY-MM-DD')

7、SQL查询拼接

select 'select * from '|| t.TABLE_NAME ||';'  from User_Tables t

8、字段长度不等于18

length(字段名) <> 18

9、字段值只允许小写字母

regexp_like(字段名,'[a-z]+')

10、测试数据库能否正常使用

select 1 from dual ;

11、系统当前日期

sysdate

12、随机数

sys_guid()

13、查询当前表的所有字段

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  COMMENTS
FROM
  USER_COL_COMMENTS
WHERE
  TABLE_NAME = 'BS_CRT_CRD';

select * from user_tab_cols
where table_name='EAUSER'; --表名要用大写

14、exists与in的区别(比较重要)

select * from user_test for update;

select * from order_test for update;

--in IN时不对NULL进行处理

select * from user_test a where a.id in (select y.user_id from order_test y);

--exists 指定一个子函数，检测行的存在，遍历循环外表，

然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

select * from user_test a where exists (select y.user_id from order_test y where a.id = y.user_id);

--in与exists 的区别

--执行顺序不一样 exists先执行主查询的表select * from user_test，

--然后根据表的每一条记录，执行以下语句，依次去判断where后面的条件是否成立

select y.user_id from order_test y where a.id = y.user_id

区别及应用场景

　　　　in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，

注意:not exists都比not in要快。

15、建立索引大全

--标准建索引:

CREATE INDEX indexId ON order_test (user_id)      TABLESPACE 表空间名;

--建立唯一索引:

CREATE unique INDEX indexId ON order_test (user_id)      TABLESPACE 表空间名;

--建立组合索引:

CREATE INDEX 索引名 ON 表名 (列名1,列名2)      TABLESPACE 表空间名;

--创建反向键索引:

CREATE INDEX 索引名 ON 表名 (列名) reverse     TABLESPACE 表空间名;

--删除索引

DROP INDEX indexId

--重建指定索引

ALTER INDEX indexId  REBUILD;

--索引虚高出现的原因:频繁update和delete导致索引块中有碎片

注意：一个基表不能建太多的索引；

空值不能被索引

只有唯一索引才真正提高速度,一般的索引只能提高30%左右。

16、查询字段不为空

select * from GK_GF_ADSL_USER u where  u.mark is not null;

17、查询两个表的一些字段(非联查)

select t.fj_old,t.oldflag_kx,t.gfjrfs ,t1.newfj,t.kzlx from GK_GF_IOM_INFO t,gk_info_his t1

18、同义词相当于Java后台的public 公共方法，可以让其他库的可以访问本库的表

--同义词

create public synonym YJSJ_ZFXYJ_TJ（改） for admin.YJSJ_ZFXYJ_TJ（改）;

--赋权限给某个用户该表必须是同义词表

grant select, insert, update, delete on YJSJ_ZFXYJ_TJ（改） to zhjk（改）;

19、关于sql分组的应用

--按照FGS分组，查询不同的组的总cost的数量

select sum(cost),fgs from GK_KHPG_WB_COSTGUANGGAIUSER group by fgs;

--按照FGS分组，查询不同的组有多少条数据

select count(*),fgs from GK_KHPG_WB_COSTGUANGGAIUSER group by fgs;

--按照FGS,FJ分组，查询不同组有多少条数据

select count(*) ,fgs,fj from GK_KHPG_WB_COSTGUANGGAIUSER group by fgs,fj;

--having是分组（group by）后的筛选条件，分组后的数据组内再筛选

----按照FGS,FJ分组，查询不同组大于100条的有多少条数据

select count(*) ,fgs,fj from GK_KHPG_WB_COSTGUANGGAIUSER group by fgs,fj having count(*)>100;

20、sql条件控制

1、select case when t.reserved_field_two = '0' then '男'

when t.reserved_field_two = '1' then '女'

else '其他' end from PERSON_PUNCH_CARD_OA t

2、select case when t.reserved_field_two = '0' then '男'

when t.reserved_field_two = '1' then '女'

else t.sex end as 性别,t.reserved_field_two from PERSON_PUNCH_CARD_OA t;

21、sql替换函数replace

--把table_value字段中的'替换成空

select replace(table_value,'''','') from SUC_INSERT_LOG

22、sql树遍历查询

列如:

select * from sys_organ start with organ_id = '120'  connect by prior orgin_id = parent_id

23、Oracle用户管理

1)、创建新用户：
create user 用户名 identified by 用户名;
2)、改密码：
alter user 用户名 identified by 新密码;
3)、删除用户名：
drop user 用户名【cascade】如果这个用户已经有表，那么删除时要带参数 cascade
4)、给用户授予权限：
grant resource,connect,dba to 用户名;
5)、收回权限：
revoke 权限 from 用户名;
revoke 权限 on 表名 from 用户名;
6)、切换用户：
conn 用户名;
7)、显示当前用户：
show user;
8)、创建表空间
create tablespace 用户名 datafile '绝对路径..\用户名.dbf' size 200mb autoextend on next 10mb maxsize unlimited;
-------------
datafile :表空间数据文件存放路径
size：设置初始大小为200m
autoextend on/off ：表示启动/停止自动扩展表空间
next 10m maxsize :表空间数据文件大小每次扩展10M
unlimited：无限制扩展
用户名----英文
.ldf 日志文件
.dbf 主数据文件
--------------
9)、创建临时表空间
create temporary tablespace 用户名 tempfile '绝对路径..\用户名.dbf' size 200mb autoextend on next 10mb maxsize unlimited;
-------------
临时表空间主要用途是在数据库进行排序运算，等操作时提供临时的运算空间，当运算完成之后系统会自动清理，
当临时表空间不足时，表现为运算速度异常的慢，如果临时表空间没有设置为自动扩展会出问题。
-------------
10)、查看表空间是否存在
select * from dba_data_files where tablespace_name = '';
11)、创建用户并为用户指定表空间
create user 用户名 identified by 密码 default tablespace 表空间;
12)、查看用户是否创建成功
select * from dba_users where username = '用户名';

24、操作表相关字段

创建表
create table 表名(字段名数据类型)
修改表结构
(1)、添加字段
alter table 表名 add(字段名数据类型(长度))
(2)、修改字段长度
alter table 表名 modify(字段名数据类型(更改后的长度))
(3)、修改字段类型(必须无数据)
alter table 表名 modify(字段名数据类型(更改后的类型))
(4)、修改字段的名字
alter table 表名 rename column 旧字段名 to 新字段名
(5)、删除表字段
alter table 表名 drop column 表字段
(6)、修改表的名字
rename 旧表名 to 新表名
(7)、删除表
drop table 表名
(8)、DELETE 语句用于删除表中的行。

DELETE FROM 表名称 WHERE 列名称 = 值

25、添加数据

insert into 表名 values(......);

插入日期，格式必须为日月年年是两位
添加部分字段

insert into 表名() values();

添加空数据：null
查询空数据

select * from 表名 where 字段名 is null;

26、字段修改操作

update 表名 set 字段名 = 值 where 条件;
删除数据表中的数据
delect from 表名 where 条件
删除表中所有记录
truncate table 表名;
删表
drop table 表名;

---------------------------------

主键(不允许重复) 外键 ------数据完整性约束
主键：标志事物唯一属性
外键：首先是一张表中的主键，又不是另外一张表中的主键
主键定义
primary key 约束来创建主键
外键定义
foreign key约束来创建外键
为现有的表中添加主键
alter table 表名 add primary key(字段名);
在两张表中创建外键
alter table 表一 add foreign key(字段1) references 表二(字段2);(表二的主键作为表一和表二的外键)

27、查询奇数

--奇数
select * from (select AAA.*, rownum rn from AAA) e where mod(数字, 2)<>0 and e.ID = 3;

28、分组查询

函数进行查询
ceil(n), 取大于等于数值n的最小整数 ceil(10.1) ---> 11
floor(n), 取小于等于数值n的最大整数 floor(10.1) --->10
mod(m,n) 取m整数除n后的余数
power(m,n) 取m的n次方
round(m，n) 四舍五入，保留位
avg(字段名) 求平均值，要求字段为数值型
count(字段名) 统计总数
min(字段名) 计算数值型字段最小数
max(字段名) 计算数值型字段最大数
sum(字段名) 计算数值型字段总和
lower(字段名) 转换为小写
upper(字段名) 转换为大写
abs(字段名) 取绝对值
----------------------------
分组
group by
having语句只能配合group by 语句使用

29、escape转义符的用法

例如：

select * from table where col like 'ABC/%/_%' escape'/';

模糊查询的语句虽然不会发生SQL错误，但是不进行回避的话，则无法得到要检索的值。
回避方法较单引号复杂。需要使用转义符。
将【%】转为【/%】，【_】转为【/_】，然后在加上【escape'/' 】就可以了。

30、多表联查

内连接查询：

select * from table1 inner join table2   on    table1.XX = table2.XX;

左外连接查询(左边为基准)：

select s.name,c.age from table1 s left join table2 c on s.xx  =  c.xx;

右外连接查询(右边为基准)：

 select s.name,c.age from table1 s right  join table2 c on s.xx  =  c.xx;

31、复制

insert into table1 select * from table2;

当表1的字段比表2的字段多的时候

insert into table1(字段名1,字段名2) select * from table2

32、升降序

order by 字段名 |desc 降序

|asc 升序

33、模糊查询

(1)like | % ------任意多个字符
| _ ------一个字节
(2)between xx and xx
(3)in()
not in()
(4)is not
null

34、子查询

select 字段名1 from 表1

where 字段名2|=(select 字段名2 from 表2 where 字段3 = '##')

|in

|>any大运其中任意一个

|...

35、树查询

前提:sys_dept 树结构表

dept_id 当前节点

parent_id 父节点

select dept_id from sys_dept start with dept_id =? connect by prior dept_id = parent_id

36、获取当天一整天的时间

取得当天0时0分0秒

select TRUNC(SYSDATE) FROM dual;

取得当天23时59分59秒(在当天0时0分0秒的基础上加1天后再减1秒)

SELECT TRUNC(SYSDATE)+1-1/86400 FROM dual;

或者:select to_char(trunc(sysdate),'yyyy-mm-dd hh24:mi:ss') ,trunc(sysdate)+1-1/86400 from dual

37.ROW_NUMBER() OVER函数

列子:

select y.* from (

select text.*,row_number() over(partition by yzy order by updateTime desc) rn from text

) y where rn = 1

意思是:

查询 text表数据，根据yzy分组，并在分组内部根据 updateTime 排列并取第一条数据

38、查询约束

例子：select * from User_Constraints T where T.CONSTRAINT_NAME = 'SYS_C0024298';

39、删除约束

例子：alter table table_name drop constraint SYS_C0024298;

40、oracl 查询数据库操作记录

select * from v$session t ;

select * from user_objects order by last_ddl_time desc;

select t.sql_text,t.first_load_time from v$sqlarea t where t.first_load_time like '2018-10-25%' order by t.first_load_time desc;

41、去重关键字

distinct

42、分页

示例:查询第一页数据，每页显示三条数据

SELECT * FROM (SELECT ROWNUM rn,EMPNO,ENAME,JOB,SAL,HIREDATE,MGR,COMM,DEPTNO
FROM EMP WHERE ROWNUM<=3) temp
WHERE temp.rn>=1;

示例:查询第二页数据，每页显示三条数据

SELECT * FROM (SELECT ROWNUM rn,EMPNO,ENAME,JOB,SAL,HIREDATE,MGR,COMM,DEPTNO
FROM EMP WHERE ROWNUM<=6) temp
WHERE temp.rn>=4;

43、创建序列

CREATE SEQUENCE SEQ_T_INVOICE_INFO MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 ORDER NOCYCLE;

44、查看序列

select * from user_sequences

45、序列的使用

select 序列名称.CURRVAL from dual;

取当前的值

select 序列名称.NEXTVAL from dual;

取下一个的值

46、删除序列

DROP SEQUENCE 序列名称;

47、oracle 数据库查询执行计划查看

EXPLAIN PLAN FOR SQL语句;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

48、oracle 查看创建表的SQL语句

select DBMS_METADATA.GET_DDL('TABLE','表名') from dual;

栗子~~

关注

3
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
工作中总结的相关sql积累(Oracle)

SQL常用语句1、查注释select /*+ parallel(a,4)*/ * FROM all_col_comments a where a.OWNER='用户名'select /*+ parallel(a,4)*/ * FROM all_col_comments a wher...
复制链接

扫一扫