蓁祎家进步中

# oracle

# 获取分区

select * from ALL_PART_TABLES where TABLE_NAME='AFRD_ABNMLACC_BATCH_ALARM';
select * from ALL_TAB_PARTITIONS where TABLE_NAME='AFRD_ABNMLACC_BATCH_ALARM';
select * from ALL_PART_INDEXES where TABLE_NAME='AFRD_ABNMLACC_BATCH_ALARM';
select * from USER_PART_INDEXES where TABLE_NAME='AFRD_ABNMLACC_BATCH_ALARM';
select * from ALL_PART_KEY_COLUMNS where TABLE_NAME='AFRD_ABNMLACC_BATCH_ALARM';
1
2
3
4
5

# 实现自增ID

create sequence ins_alarm_seq
    increment by 1
    start with 1
    maxvalue 99999999999999999
    nocycle
    nocache;
    
alter table afrd_ins_alarm add id number(20) default ins_alarm_seq.nextval;
1
2
3
4
5
6
7
8

# 约束、索引

alter table table_name add constraint my_constraint_name unique(cloumn1,column2)
alter table drop constraint my_constraint_name;

create index_name on table_name(column1,column2);
drop index index_name;
1
2
3
4
5

# 修改表及字段

-- 修改字段类型
alter table my_table modify my_column varchar(120);
-- 删除字段
alter table my_table drop column my_column;

-- 修改表名称
alter table my_table rename to my_new_table;
-- 或者，建新表并导入旧表数据，然后删除旧表
create table my_new_table as select * from my_table;
drop table my_table
1
2
3
4
5
6
7
8
9
10

# 备份恢复

# 单表备份恢复

# 备份旧表
create table table_bak as select * from table_origin;

# 恢复旧表，适用情况：同库且针对单表
truncate table table_origin;
insert into table_origin select * from table_bak;
1
2
3
4
5
6

# 多表或整库备份恢复

# oracle终端执行，进行备份
exp [user_name]/[user_password] tables=[table_1],[taleb_2] file=/home/ap/atfs/backup/table.dmp

# oracle终端执行，进行恢复
imp [user_name]/[user_password] grants=y commit=y full=y ignore=y file=/home/ap/atfs/backup/table.dmp
1
2
3
4
5

# 删除表

当一个表不再使用时，可以将其删除。删除表时，将产生以下结果：

  • 表的结构信息从数据字典中删除，表中的数据不可访问；
  • 表上的所有索引和触发器被一起清除；
  • 所有建立在该表上的同义词、视图和存储过程变为无效；
  • 所有分配给表的簇标记为空闲，可被分配给其他的数据库对象。

一般情况下，普通用户只能删除`自己模式`下的表。若要删除其他模式下的表，则必须`具有 DROP ANY TABLE 数据库权限`。 以下语句可删除 employee 表： 
DROP TABLE employee;
1

删除不存在的表会报错。若指定 IF EXISTS 关键字，删除不存在的表，不会报错，如：

DROP TABLE IF EXISTS employee;
1

如果要删除的表被其他表引用，即其他表的外键引用了表的任何主键或唯一键，则需要在 DROP TABLE 语句中包含 CASCADE 选项，如：

DROP TABLE employee CASCADE;
1

# 清空表

有些情况下，当表的数据不再使用时，需要删除表的所有行，即清空该表。DM8 支持以下方式来删除表中的所有的行：

  • 使用 DELETE 语句；
  • 使用 DROP 和 CREATE 语句；
  • 使用 TRUNCATE 语句。

# 3.1 使用delete

使用 DELETE 语句能删除表中的行。例如，下面的语句删除 employee 表中的所有行：

DELETE FROM employee;
1

但是，使用 DELETE 清空表，当表有很多行时，会消耗很多系统资源。因为，DELETE操作需要 CPU 时间，并且会产生大量的 REDO 日志和 UNDO 记录。另外，如果表上关联了元 组级触发器，每删除一行，就会启动一次触发器。这都需要大量的系统资源。

# 3.2 使用 DROP 和 CREATE

使用 DROP 删除一个表，然后创建一个同名的表，也可以达到清空表的效果。例如，下面的语句先删除 employe 表，之后再重新创建。

DROP TABLE EMPLOYEE;
CREATE TABLE EMPLOYEE(…);
1
2

当删除和重新创建表时，所有与之相关联的索引、完整性约束和触发器也被删除。同样，所有针对被删除表的授权也会被删除。

# 3.3 使用 TRUNCATE

使用 TRUNCATE 语句能删除表中的所有行。例如，下面的语句清空 employee 表。

TRUNCATE TABLE EMPLOYEE;
1

TRUNCATE 语句为我们提供了一种快速、有效地删除表所有行的方法。并且 TRUNCATE 是一个 DDL 语句，不会产生任何回滚信息。执行 TRUNCATE 会立即提交，而且不能回滚。 TRUNCATE 语句并不影响与被删除的表相关联的任何结构、约束、触发器或者授权。 另外，DM 数据库 TRUNCATE 表后，原来分配给该表的空间会被释放，供其他数据库对象使用，大大提高空间的利用效率。
一般情况下，普通用户只能 TRUNCATE 自己模式下的表。若要 TRUNCATE 其他模式下的表，则必须具有 DROP ANY TABLE 数据库权限。 如果要清空的表被其他表引用，即其他表的外键引用了表的任何主键或唯一键，并且子表不为空或子表的外键约束未被禁用，则不能 TRUNCATE 该表。

# 删表总结

  • 1、在速度上，一般来说，drop> truncate > delete。
  • 2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
  • 3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

  • 如果想删除表，当然用drop；
  • 如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；
  • 如果和事务有关，或者想触发trigger，还是用delete；
  • 如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

# varchar2(20 char)与varchar2(20)

定义类型时加上cahr，就是强制按照字符类型存储

  • varchar2(20 char)——定义了20个字符长度的存储（相当于40个字节）
  • varchar2(20)——定义了20字节的存储

# 汉字占多少字节

与数据库编码有关

  • UTF-8：1汉字占3个字节，1英文字母占1个字节
  • GBK：1汉字占2个字节，1英文字母占1个字节
select lengthb('你好') from dual;
-- 6
select vsize('你好') from dual;
-- 6，查询中文字符存储需要的字节空间

select length('你好') from dual;
-- 2
1
2
3
4
5
6
7

# sqluldr2

由于oracle不支持大批量数据的导入导出，sqluldr2是一款Oracle的数据快速导出工具，能快速导出亿级数据为excel文件(txt/csv)

  • 1、把sqluldr2的执行文件（sqluldr2_linux64_10204.bin）传到Oracle的bin路径下
sqluldr2_linux64_10204.bin
# 报错依赖版本有差异的话，则建立软链接， lbn -s libclntsh.so.11.1 libclntsh.so.10.1

sqluldr2 test/test sql=tmp.sql fiedl=|@|
1
2
3
4
参数名称 含义
field 分隔符，指定字段分隔符，默认为逗号，建议使用不出现在数据库表字段信息的符号，比如|@|
record 分隔符，指定记录分隔符，默认为换行符，建议使用|^@^|
quote 引号符，非数字字段的前后引号
charset
ncharset 
# 常规导出
sqluldr2_linux64_10204.bin user=test/test@history_162 query="select * from temp_001" head="yes" file=/home/oracle/temp_001.txt

# 使用sql导出，tmp.sql内容为select * from temp_001
sqluldr2_linux64_10204.bin user=test/test@history_162 query=tmp.sql head='yes' file=/home/oracle/temp_001.txt

# 使用table参数，会在目录下生成temp_001_sqlldr.ctl文件
sqluldr2_linux64_10204.bin user=test/test@history_162 query=tmp.sql table=temp_001 head='yes' file=/home/oracle/temp_001.txt
1
2
3
4
5
6
7
8

# 字段与mysql、达梦类型对应关系

说明 oracle mysql 达梦
变长字符串 varchar[0-65535]
定义长度默认按照字符长度计算
如果是gbk编码的汉字将占用2个字节		 varchar2[1-4000]
varchar是varchar2的同义词
定义默认按照字节长度计算		 varchar
整数 tinyint(-128-127)
smallint(-32768-32767)
mediumint(-8338608-8388607)
int(-21474838648-2147483647)
bigint(-9223372036854775808-9223372036854775807)		 无专用类型
tinyint可用number(3, 0)代替
smallint可用number(5, 0)代替
mediumint可用number(7, 0)代替
int可用number(10, 0)代替
bigint可用number(20, 0)代替
oracle中smallint、int、integer都是number(3, 0)的同义词		 numeric[精度, 标度]，存储0、正负定点数，精度范围1-38
number，同numeric
decimal/dec，跟numeric类似
bit，存储0,1,null
integer/int，存储有符号整数，精度为10
pls_integer，与integer类似
bigint，存储有符号整数，精度为19，标度为0
tinyint，存储有符号整数，精度为3，标度为0，范围：-128-127
byte，与tinyint类似
smallint，精度为5，标度为0
数值类型 decimal[1-65[, 0-30]]
numeric是decimal的同义词		 number([1-38[,-84-127]])
decimal、numeric、dec是number的同义词
浮点型 float(D, M) oracle 10g开始增加binary_float类型
10g以前无专用类型，可用number代替
oracle中有float和real类型，不过都是number的同义词
双精度浮点类型 double(D, M) oracle 10g开始增加binary_float类型
10g以前无专用类型，可用number代替
oracle中有float和real类型，不过都是number的同义词		 real，带二进制的浮点数
float，二进制精度的浮点数，精度最大53
double，精度最大53
double precision，双精度浮点数，二进制精度为53，十进制精度为15
位类型 bit(1-64) / bit，存储0,1,null
日期类型 date，3字节存储，只存储日期，支持范围1000-01-01至9999-12-31
time，3字节存储，只存储时间，支持范围-838:59:59至83859:59
datetime，8字节存储，可表示日期时间，支持范围1000-01-01 00:00:00至9999-12-31 23:59:59
timestamp，4字节存储，可表示日期时间，支持范围1970-01-01 00:00:00至2038-01-19 03:14:07		 date，7字节存储，可表示日期时间，支持范围-4712-01-01 00:00:00至9999-12-31 23:59:59
高精度日期 5.6.4以前不支持小数秒精度
5.6.4开始time、datetime、timestamp支持最多6位小数秒精度，即微秒		 timestamp[0-9]，占用7-11字节，当小数为0时，与date相同，最高精度可达9位，即纳秒 date，-4712-01-01至9999-12-31
time，时分秒
timestamp/datetime，年月日时分秒
time with time zone，带时区的time
timestamp with time zone，带时区
年份 year，1字节存储，支持范围1901-2155 无对应类型，可用number(3, 0)代替 /
定长字符串 char[0-255]，按字符长度计算 char[1-2000]，按字节长度计算 char/character，定长，最大由数据库页面决定
无符号说明 支持，用于数值类型 / /
大字符串 tinytext，最大支持255字节
text，最大支持65535字节
mediumtext，最大支持16M字节
longtext，最大支持4G字节
字段不支持默认值		 clob，oracle10g以前最大支持4GB，10g以后最大支持4GB个数据库，数据库大小为2KB-32KB
二进制对象，存文件或者图片 tinyblob，最大255字节
blob，最大65535z字节
mediumblob，最大16M字节
longblob，最大4G字节		 blob，说明同clob text/longvarchar，变长字符，最大2G-1
image/longvarbinary，多媒体信息
blob，变长字符串，最大2G-1字节
clob，变长字符串，最大2G-1字节
二进制信息 定长binary(0-255)
变长varbinary(0-65535)		 raw(1-2000) binary,定长二进制数据
varbinary，变长二进制数据
枚举类型 enum(v1,v2,v3,v4……)，最多65535个元素 / /
集合类型 set(v1,v2,v3……)，最多64个元素 / /
国际化字符集 / 支持nchar(1-2000)、nvarchar(1-4000)、nclob /
外部文件指针类型 / 支持，文件大小最大4GB，文件名称最长255个字符 bfile，指明存储在操作系统中的二进制文件
自增 支持 通过sequence来实现
innodb最大1000个字段，所有字段总长不能超过65535字节 最大1000个字段

# 分页

利用自带的rownum计算参数的开始序号(startNum)及结束序号(endNum)

由于rownum在表中不能使用>号，但是可以使用<号，所以会采用转换的方式来进行分页查询

-- n为除了0以外的任何值，查询出的结果都是空
select rownum, e.* from my_table e where rownum > n;

select * from (
    select rownum rw, b.* from (
        -- 中间业务
        select * from my_table
        ) a where rownum < endNum 
    ) b where b.rw > startNum;
    

-- agrs:[0, 20]
select * from (select a.*, rownum taiji_rownum from (select * from afrd_ins_list order by update_time asc, vrf_stcd asc) a) 
    where taiji_rownum > ? and rownum <= ?;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# oracle中的函数

# 排序中的null

back

# mysql排序

mysql中认为null在排序时为最小值。

  • 降序时，会把null排在最后面；
  • 升序时，会把null排在最前面

所以针对实际项目需求，可能需要对升序的进行处理，把null排在最后面，解决方法如下：

SELECT * FROM `ts_wb_signin` ORDER BY  UPDATE_USER IS NULL, UPDATE_USER ASC
1

加上UPDATE_USER IS NULL这一句

或者

SELECT * FROM `ts_wb_signin` ORDER BY  ISNULL(UPDATE_USER) ASC;
1

或者,排序字段前加上负号“-”，同时把“ASC”改为“DESC”，

SELECT * FROM `ts_wb_signin` ORDER BY  -UPDATE_USER desc;
1

以上都可实现升序排序时，null值排到最后面。

# oracle排序

back

oracle中认为null在排序时为最大值

  • 降序时，会把null排在最前面；
  • 升序时，会把null排在最后面；

oracle提供了函数 NULLS LAST或者NULLS FIRST

ORDER BY GATHERTIME DESC NULLS LAST
1

# 查看数据库连接情况

back

SELECT username, machine, program, status, COUNT (machine) AS machineCount
FROM v$session
GROUP BY username, machine, program, status
ORDER BY machine;
1
2
3
4

# 常用语句拾锦

back

# 获取前10等

back

SELECT * FROM (SELECT * FROM ALARM_INFO where STATUS = 0 OR STATUS = 1 ORDER BY CREATE_TIME DESC) WHERE ROWNUM &lt;= 10
1

如果直接如下，是得不到想要的最新10条记录的，ROWNUM先有的，再排序，就乱了：

SELECT * FROM ALARM_INFO where STATUS = 0 OR STATUS = 1 AND ROWNUM &lt;= 10 ORDER BY CREATE_TIME DESC
1