# 其他数据库

# 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是dml操作;truncate是ddl操作,ddl隐式提交不能回滚,会回收表空间
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;

死锁

-- 查询死锁会话
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, l.os_user_name
, s.machine, s.terminal, o.object_name, s.logon_time, p.SPID
 FROM v$locked_object l, all_objects o, v$session s,v$process p
 WHERE l.object_id = o.object_id AND l.session_id = s.sid AND s.paddr = p.addr
 ORDER BY sid, s.serial#;

-- 结束
alter system kill session 'sid,serial#';
orakill SID spid

查看所有表结构

SELECT t1.Table_Name || chr(13) || t3.comments       AS "表名称及说明",
       --t3.comments                                 AS "表说明",
       t1.COLUMN_ID                                  AS "序号",
       t1.Column_Name                                AS "字段名称",
       t1.DATA_TYPE || '(' || t1.DATA_LENGTH || ')'  AS "数据类型",
       t1.NullAble                                   AS "是否为空",
       t2.Comments                                   AS "字段说明",
       t1.Data_Default                               AS "默认值"
       --t4.created                                  AS "建表时间"
  FROM cols t1
  LEFT JOIN user_col_comments t2 
         ON t1.Table_name = t2.Table_name
        AND t1.Column_Name = t2.Column_Name
  LEFT JOIN user_tab_comments t3 
         ON t1.Table_name = t3.Table_name
  LEFT JOIN user_objects t4 
         ON t1.table_name = t4.OBJECT_NAME
  WHERE NOT EXISTS (SELECT t4.Object_Name
          FROM User_objects t4
         WHERE t4.Object_Type = 'TABLE'
           AND t4.Temporary = 'Y'
           AND t4.Object_Name = t1.Table_Name)
  ORDER BY t1.Table_Name, t1.Column_ID;

# Postgre

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

查询Postgres数据库中的所有表信息(表名、备注)

SELECT
	relname AS tabname,
	cast( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS COMMENT
FROM
	pg_class c
WHERE
	relkind = 'r'
	AND relname NOT LIKE 'pg_%'
	AND relname NOT LIKE 'sql_%'
    -- AND relchecks=0 -- 过滤掉分表
ORDER BY
	relname

查询Postgres数据库中的表字段名、类型、注释、注释是否为空

SELECT
	a.attname AS NAME,
	col_description ( a.attrelid, a.attnum ) AS COMMENT,
	format_type ( a.atttypid, a.atttypmod ) AS type,
	a.attnotnull AS notnull
FROM
	pg_class AS c,
	pg_attribute AS a
WHERE
	c.relname = '表名'
	AND a.attrelid = c.oid
	AND a.attnum >0

UUID

-- 不建议在高并发下使用,以下三个语句都可以生成
SELECT uuid_in(md5(random()::text || now()::text)::cstring);
SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring);
SELECT md5(random()::text || clock_timestamp()::text)::uuid;
-- 去掉-,32位
SELECT REPLACE(md5(random()::text || clock_timestamp()::text),'-','');

日期时间

select to_timestamp('2022-08-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
select to_date('2022-08-02 23:59:59', 'yyyy-mm-dd HH24:mi:ss');
select to_char(now(), 'yyyy-mm-dd HH24:mi:ss');
select date_trunc('day', now());
select date_trunc('day', now()) -interval '1d' + interval '6 hours';

# SQLite3

连接符

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

# Redis

Java客户端

存储方式

  • 适合用 String 存储的情况:
    • 每次需要访问大量的字段
    • 存储的结构具有多层嵌套的时候
  • 适合用 Hash 存储的情况:
    • 在大多数情况中只需要访问少量字段
    • 自己始终知道哪些字段可用,防止使用 mget 时获取不到想要的数据

安装

wget https://download.redis.io/redis-stable.tar.gz
tar -zvf redis-stable.tar.gz
cd redis-stable
make
make install
cp redis.conf /etc/redis.conf
vi /etc/redis.conf

输入/daemonAidaemonize no改成daemonize yes 允许任意IP访问:输入/NETWORK搜索,替换bind 127.0.0.1bind 0.0.0.0 设置访问密码:输入/SECURITY搜索,取消注释requirepass foobared 修改protected-mode yes(保护模式,需配置设置访问密码) 为 protected-mode no

# 启动
redis-server /etc/redis.conf
ps -ef | grep redis
# 连接redis
redis-cli -p 端口 -a 密码
# 配置设置密码,不会修改配置文件
config set requirepass 密码
# 单实例关闭redis
redis-cli shutdown
# 多实例关闭,指定端口关闭redis
redis-cli -p 6379 shutdown

# MongoDB