SQL

查询当前用户有多少张数据表：

select count('select * from'||table_name||';') from user_tables

创建用户SALARYUSER并分配dba权限。

create user SALARYUSER identified by 123456;grant dba to SALARYUSER;

创建表空间SALARY（数据表空间）

create tablespace SALARY   logging  datafile 'D:\SALARY.dbf' size 50m  autoextend on  next 50m maxsize 20480m  extent management local;

把表空间SALARY赋给对应的用户

alter user SALARYUSER default tablespace SALARY;

导入数据命令

imp salaryuser/123456@ORCL file=C:\Users\Administrator\Desktop\all.dmp full=y

删除用户并删除数据（加了cascade就可以把用户连带的数据全部删掉）

drop user SALARYUSER cascade;

删除表空间，同时删除数据文件

drop tablespace SALARY including contents and datafiles;

重置scott用户密码并解锁

alter user scott identified by 123456 account unlock;

建立序列

CREATE SEQUENCE emp_sequence  --序列名

查询oracle版本

select * from v$instance

查看当前用户的所有序列

select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner='用户名';

查询指定序列的下一个值（T_CUST_INFO_ID_SQE为序列名）

select T_CUST_INFO_ID_SQE.nextval from dual

oracle使用instr优化like并且替换or查询

SELECT        L.APPLY_LOAN_KEY,L.AUDIT_STATUS,        to_char(L.INSERT_DATE, 'yyyy-mm-dd hh24:mm:ss') as INSERT_DATE,        NVL(L.CUST_NAME,' ') as CUST_NAME,        NVL(L.CUST_NO,' ') as CUST_NO,        NVL(L.LOAN_TERM,' ') as LOAN_TERM,        NVL(L.LOAN_AMOUNT,' ') as LOAN_AMOUNT,        NVL(L.LOAN_TYPE,' ') as LOAN_TYPE,        NVL(L.AUDIT_TERM,' ') as AUDIT_TERM,        NVL(L.AUDIT_AMOUNT,' ') as        AUDIT_AMOUNT,        NVL(L.AUDIT_TYPE,' ') as AUDIT_TYPE,        NVL(L.MOBILE,' ') as        MOBILE        FROM        XD_APPLY_LOAN L        WHERE        L.AUDIT_STATUS IN         <foreach item="item" index="index" collection="status" open="("            separator="," close=")">            #{item}        </foreach>         AND L.SALER_NO = #{saler_no}        <if test="other_param !=null and other_param != '' ">            AND INSTR(L.CUST_NAME||','||L.APPLY_LOAN_KEY||','||L.MOBILE,#{other_param}) > 0        </if>

截取指定字符串（例如T.LOAN_TYPE=501,精英方案 截取后501）

SUBSTR(T.LOAN_TYPE,1,INSTR(T.LOAN_TYPE,',',1,1)-1)

oracle常用函数

1. INSTR(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
   C1 被搜索的字符串
   C2 希望搜索的字符串
   I 搜索的开始位置,默认为1
   J 出现的位置,默认为1

   instr(title,’手册’)>0 相当于 title like ‘%手册%’
   instr(title,’手册’)=1 相当于 title like ‘手册%’
   instr(title,’手册’)=0 相当于 title not like ‘%手册%’

2. CONCAT 连接两个字符串;

3. REPLACE(‘string’,’s1’,’s2’)
   string 希望被替换的字符或变量
   s1 被替换的字符串
   s2 要替换的字符串

4. SUBSTR(string,start,count)取子字符串,从start开始,取count个

5. SYSDATE获取系统时间

6. sys_guid()获取UUID

查询格式化日期

to_char(L.INSERT_DATE, 'yyyy-mm-dd hh24:mi:ss') as INSERT_DATE

插入格式化日期

to_date(#{upload_time,jdbcType=DATE},'yyyy-mm-dd hh24:mi:ss')

数据库分页

SELECT B.*    FROM    (SELECT    A.*,    rownum rnum    FROM    (SELECT        APPLY_LOAN_KEY,        nvl(LOAN_TYPE,' ') LOAN_TYPE,            nvl(LOAN_AMOUNT,' ') LOAN_AMOUNT,            nvl(AUDIT_STATUS,' ') AUDIT_STATUS,            nvl(LOAN_TERM,' ') LOAN_TERM    FROM XD_APPLY_LOAN WHERE    CUST_NO = #{cust_no} ORDER BY INSERT_DATE DESC ) A    WHERE rownum <=#{pageIndex}*#{pageSize}    ) B    WHERE rnum > (#{pageIndex} - 1) * #{pageSize}

查询某张表的字段和字段长度

select distinct t.COLUMN_NAME,t.DATA_TYPE || '(' || t.data_length || ')' from sys.all_tab_columns t where t.TABLE_NAME = 'XD_COMPANY_INFO'

修改字段长度

alter table XD_COMPANY_INFO modify(LANDLORD_MOBILE varchar(23));

新增字段

ALTER TABLE XD_APPLY_LOAN ADD BLACKBOX VARCHAR2(4000);COMMENT ON COLUMN XD_APPLY_LOAN.BLACKBOX IS '设备指纹';

删除字段

ALTER TABLE XD_AD DROP COLUMN APP_TYPE;

获取数据库随机数
oracle

SELECT * FROM (SELECT * FROM XD_SALER ORDER BY DBMS_RANDOM.RANDOM()) WHERE ROWNUM = 1

mysql

SELECT * FROM XD_SALER ORDER BY  RAND() LIMIT 1

sql server

SELECT TOP 1 * FROM XD_SALER ORDER BY NEWID()

查询数据库编码集

SELECT USERENV('LANGUAGE') FROM DUAL;

查询所占字节数

SELECT LENGTHB('汉字') FROM DUAL;

查询所占字符数

SELECT LENGTH('汉字') FROM DUAL;

注：length(‘string’)=lengthb(‘string’)判断字符串是否含有中文

UTF-8字符集，一个汉字占三个字节，gbk字符集，一个汉字占两个字节，比如varchar（10）类型的字段，UTF-8的汉字，只能存3个，gbk字符集的汉字却能存5个

删除表中多余的重复记录，重复记录是根据单个字段来判断，只留有rowid最小的记录

DELETE FROM XD_SALER WHERE SALER_NO IN (SELECT SALER_NO FROM XD_SALER GROUP BY SALER_NO HAVING COUNT(SALER_NO) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM XD_SALER GROUP BY SALER_NO HAVING COUNT(SALER_NO) > 1);

批量更新

update b set (name) = (SELECT name FROM a WHERE b.id = a.id)

给表的字段赋一个默认值X

select 字段1,'X' as 字段2 from tableName;