# 其他数据库

# Flag

图数据库 (opens new window)

# ORACLE

空字符串''同等NULL,字符串与数字类型会自动转换

系统表

  • USER_TABLES 当前用户拥有的表:TABLE_NAME,TABLESPACE_NAME,LAST_ANALYZED
  • DBA_TABLES 包括系统表:多了OWER
  • ALL_TABLES 所有用户的表:多了OWER
  • ALL_OBJECTS 当前用户有访问权限的所有对象:OWER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS
  • USER_TAB_COLUMNS 当前用户拥有的表字段
  • ALL_TAB_COLUMNS
  • DBA_TAB_COLUMNS
  • USER_TAB_COMMENTS 当前用户拥有的表注释 :TABLE_NAME,TABLE_TYPE,COMMENTS
  • DBA_TAB_COMMENTS :多了OWER
  • ALL_TAB_COMMENTS :多了OWER
  • USER_COL_COMMENTS 当前用户拥有的表字段注释 : TABLE_NAME,COLUMN_NAME,COMMENTS
  • DBA_COL_COMMENTS :多了OWER
  • ALL_COL_COMMENTS :多了OWER

SELECT * FROM USER_TAB_COMMENTS WHERE COMMENTS LIKE '%摘要%'

分组获取最新一条数据(查询各组最新的一条记录)

  • over partition by 分析函数(开窗函数)
SELECT * FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY 分组字段名 ORDER BY 排序字段名 DESC) rn,t.* FROM test1 t
    ) WHERE rn = 1;

SELECT * FROM (
    select eb_vipcode,eb_time,MAX(eb_time) over(partition by eb_vipcode) as "atime" from eb_daskexpdateinfo
    ) x where eb_time = "atime";

SELECT * FROM (
    select ID_,COMPANY_NAME,USAGE_RATE,CREATE_TIME
    ,MAX(CREATE_TIME) over(partition by COMPANY_NAME) as "atime" from SPEC_RATE_ORIGIN
    ) x where CREATE_TIME = "atime";
  • group by
SELECT eb_vipcode,MAX(eb_time) AS "atime" FROM eb_daskexpdateinfo group by eb_vipcode
  • inner join
SELECT A.* FROM SPEC_RATE_ORIGIN A INNER JOIN (
    SELECT COMPANY_NAME,MAX(CREATE_TIME) AS "atime" FROM SPEC_RATE_ORIGIN group by COMPANY_NAME
    ) B ON A.COMPANY_NAME = B.COMPANY_NAME AND A.CREATE_TIME = B."atime";

一次执行多条SQL

INSERT ALL
 INTO a表(字段) VALUES(各个值1)
 INTO a表(字段) VALUES(其它值2)
 INTO a表(字段) VALUES(其它值3)
SELECT 1 FROM DUAL;
  • 使用begin…end;
begin
insert into table_name (列名,列名) values (express,express);
insert into table_name (列名,列名) values (express,express);
insert into table_name (列名,列名) values (express,express);
insert into table_name (列名,列名) values (express,express);
end;

插入或更新 upsert

MERGE INTO table_name alias1   
USING (table|view|sub_query) alias2  
ON (join condition)   
WHEN MATCHED THEN   
    UPDATE table_name SET col1 = col_val1
WHEN NOT MATCHED THEN   
    INSERT (column_list) VALUES (column_values);

修改字段

-- 创建会话级临时表来存储原数据,并删除表数据
CREATE GLOBAL TEMPORARY TABLE TEST_BAK AS (SELECT * FROM TEST);

-- 查看临时表数据
SELECT * FROM TEST_BAK;

-- 清空数据
DELETE FROM TEST;
-- 将目标字段数据清空
--UPDATE TEST SET MEASURED = NULL;

-- 修改表字段
ALTER TABLE TEST MODIFY MEASURED NUMBER(18,6);

-- 还原表结构
INSERT INTO TEST SELECT * FROM TEST_BAK;

-- 删除临时表
DROP TABLE TEST_BAK;

# Postgre

-- 插入或更新 upsert
INSERT INTO table_name(column_list) 
VALUES(value_list)
ON CONFLICT target action;

# SQLite3

连接符

连接符 说明
- 算术减法
!= 关系不等于
% 算术模量
& 逻辑与
* 算术乘法
/ 算术除法
| 逻辑或
|| 字符串串联
+ 算术加法
< 关系小于
<< 按位右移
<= 关系式小于或等于
<> 关系不等于
= 关系等于
== 关系等于
> 关系大于
>= 关系大于或等于
>> 按位左移
AND 逻辑与
GLOB 关系文件名匹配
IN 逻辑输入
LIKE 关系字符串匹配
OR 逻辑或

# Redis

# Memcached

# MongoDB

# 免费数据库

MySQL

SQLServer

PostgresqlSQL

Redis