各数据库常用的命令、函数、数据类型总结

发表于 分类于

SQL标准 - 维基百科 ，以SQL为基础的其他延伸语言： Transact-SQL - 维基百科 、 PL-SQL(Oracle) - 维基百科

常用命令

MySQL

大全：https://www.runoob.com/mysql/mysql-command-manual.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
status

show [FULL] processlist; -- 活动的线程和进程（与正在使用的MySQL账户相关的线程），若不加full则只列出前100条。
KILL [CONNECTION | QUERY] processlist_id; -- 终止线程，默认选项 CONNECTION

show variables like '%max_connections%'; -- 最大连接数
show status like 'Max_used_connections'; -- 同时使用的最大连接数
show status like 'Threads%'; -- 具体如下
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Threads_cached    | 0      |
| Threads_connected | 41     | -- 当前打开的连接数，跟show processlist结果相同
| Threads_created   | 429082 | -- 表示创建过的线程数。该值不断增大是因为thread_cache_size使用默认值-1
| Threads_running   | 3      | -- 激活的连接数（不是sleep的），一般低于connected数值。准确来说，代表当前并发
+-------------------+--------+

show variables like '%table_size%';
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 536870912 | -- 内存表大小
| tmp_table_size      | 536870912 | -- 临时表大小
+---------------------+-----------+

show variables like 'max_execution_time'; -- SELECT语句的执行超时时间（以毫秒为单位）

表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
show indexes from t_base_table_template; -- 
show tables;

DROP TABLE IF EXISTS `t_base_table_template`;
CREATE TABLE `t_base_table_template` (
  -- 最大值比较：无符号=有符号*2
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  -- 对于数字类型，括号中的数字称为字段的显示宽度，不影响类型的最大最小值。使用unsigned zerofill时，显示宽度才有意义。
  `balance` bigint(20) unsigned zerofill NOT NULL COMMENT '余额',
  -- 50个字符。UTF-8下，50个英文字母占用50Byte，50个汉字占用150Byte。length函数用于计算字节数。
  `account` varchar(50) NOT NULL COMMENT '账号',
  `password` varchar(50) NOT NULL COMMENT '密码',
  -- 允许为null且数值不分散的列，不要建立索引
  `sex` decimal(1,0) DEFAULT NULL COMMENT '性别  1：男   2：女',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `source` varchar(10000) NOT NULL COMMENT '数据来源',
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_idx` (`account`) USING BTREE,
  KEY `account_password_idx` (`account`, `password`),
  -- 指定索引长度
  KEY `source_idx` (`source`(128))
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4;

-- 普通索引：CREATE        INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
-- 唯一索引：CREATE UNIQUE INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
CREATE INDEX create_time ON t_base_table_template (`create_time`);
DROP INDEX create_time ON t_base_table_template;

常用函数

通用

COALESCE：返回参数中的第一个非null的值，它要求参数中至少有一个是非null的，如果参数都是null会报错。

1
SELECT COALESCE	( NULL, NULL, NULL, '巴顿' ) AS NAME

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
select
  CONVERT ( '-1', signed ), -- 有符号
  CONVERT ( '-1', unsigned ), -- 无符号
  
  IF ( dt.operator IS NULL, 0, 1 ) isExistData,
  IFNULL ( SUM( t.spart_cnt ), 0 ) AS quantity,
  
  CASE '1' 
      WHEN '1' THEN '正常' 
      WHEN '2' THEN '受控' 
      WHEN '3' THEN '禁止'
    ELSE NULL
  END AS sale_state,
  CASE 
    WHEN t.name is null THEN '正常' 
    ELSE '异常'
  END AS name_state,
  
  concat ( t.region_name_cn, '-', t.country_name_en, '-', t.customer_name_en ) AS customer_name_en,
  group_concat ( item.product_cn ORDER BY product_cn DESC SEPARATOR '$' ) AS product_name 
  LEFT ( opp.`month`, 4 ) AS 'year',
  TRUNCATE( ROUND ( T.sum_quantity :: NUMERIC / t2.total_quantity :: NUMERIC, 4 ) * 100, 2 ) quantity_percent,
  CAST ( '2022-01-31 13:28:56' AS TIMESTAMP ),
  DATE_FORMAT ( '2022-01-06 13:56:35', '%Y-%m-%d %H:%i:%S' ),
  DATE_FORMAT ( now(), '%Y-%m-%d %H:%i:%S' ),
  CHARSET ( 'ABC' ),
  CHARSET ( CONVERT( 'ABC' USING gbk ) )
from table_name
where
  <!-- 历史6个月 -->
  <![CDATA[
      t.`month` <= DATE_FORMAT( CURDATE(), '%Y%m' )
  AND t.`month` >= DATE_FORMAT(DATE_SUB( NOW(), INTERVAL 5 MONTH ), '%Y%m')
  ]]>
  <!-- 未来6个月 -->
  <![CDATA[
      t.`MONTH` >= DATE_FORMAT( NOW(), '%Y%m' )
  AND t.`MONTH` <= DATE_FORMAT( DATE_ADD( NOW(), INTERVAL 5 MONTH ), '%Y%m' )
  ]]>
  
  AND binary asd.name like concat( '%', 'petty','%') -- binary区分大小写，mysql默认不区分大小写

Oracle

PostgreSQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SELECT
	-- 数学函数
    ABS ( - 17.4 ) AS "绝对值",-- 17.4
    sign( - 8.5 ) AS "参数的符号(-1,0,+1)",-- -1
    SQRT ( 4.0 ) AS "	平方根",-- 2
    cbrt( 27.0 ) AS "立方根",-- 3
    CEIL ( - 42.8 ) AS "向上取整",-- -42
    FLOOR ( - 42.8 ) AS "向下取整",-- -43
    MOD ( 9, 4 ) AS "取余",-- 1
    trunc( 42.438 ) AS "截断小数位",-- 42
    trunc( 42.438, 2 ) AS "截断小数，保留2位小数",-- 42.43
    round( 42.4 ) AS "四舍五入",-- 42
    round( 42.438, 2 ) AS "四舍五入，保留2位小数",-- 42.44
    TRUNC( ROUND( '10' :: NUMERIC / '100' :: NUMERIC, 4 ) * 100, 2 ) AS percent,-- 10.00
    POWER ( 2.0, 3.0 ) AS "2的3次幂",-- 8.0
    log( 2.0, 8.0 ) AS "以2为底8的对数",-- 3.0
    log( 100.0 ) AS "以10为底100的对数",-- 2.0
    EXP ( 1.0 ) AS "自然指数",-- 2.7182818284590452
    LN ( 2.7182818284590452 ) AS "自然对数",-- 1.0
    degrees( 1 ) AS "弧度转角度",-- 57.29577951308232
    radians( 57.29577951308232 ) AS "角度转弧度",-- 1
    pi( ) AS "π常量",-- 3.141592653589793
    random( ) AS "0.0到1.0之间的随机数值"
    
    -- 三角函数列表
    
    
    
    -- 其他
    asd.proj_cn_name ILIKE concat ( '%', #{requestParam.likeCondition,jdbcType=VARCHAR}, '%' ),
    to_number( RIGHT ( tt.mon_asd, 2 ), '99' ) AS name_cn,
    CAST ( offer.pc_id AS VARCHAR ( 50 ) ) AS pc_id,
  from table_name
  where
    <![CDATA[
        asd.mon_asd <= to_char( CURRENT_TIMESTAMP, 'YYYYMM' )
    AND asd.mon_asd >= to_char( CURRENT_TIMESTAMP + '-5 month', 'YYYYMM' )
    ]]>
    
    AND asd.name ilike concat( '%', 'petty','%')

数据类型

数值

Oracle

PL-SQL#数值类型 - 维基百科

number 类型： number[([precision], [scale])]

precision 表示十进制有效数字的个数，最多不能超过38个有效数字 number(38,0) （实际支持39-40位十进制数字）。

Scale的范围为[-84，127]。Scale为正数时，表示从小数点到最不重要的十进制有效数字的个数（小数点后多余舍弃）；为负数时，其绝对值表示从最不重要的十进制有效数字到小数点的位数（小数点前四舍五入变为0）。

如果指定了精度，没有指定scale，scale默认为0。

如果没有指定精度，precision与scale默认为最大的取值区间（维基百科是这样说的），实际测试如下（Oracle 11g）：

插入数据 查询结果
0.123456789123456789123456789123456789 0.123456789123457
1.123456789123456789123456789123456789 1.12345678912346
12.123456789123456789123456789123456789 12.1234567891235
123456789123456789.123456789123456789123456789123456789 123456789.123457
123456789123456789.123456789123456789123456789123456789 123456789123457000

结论：从左到右，最多保留十进制有效数字15个。其余部分，小数点前四舍五入变为0，小数点后舍弃。

浮点类型：binary_float、binary_double

MySQL

https://www.runoob.com/mysql/mysql-data-types.html

日期和时间类型

Oracle

秒、毫秒、微秒、纳秒

date 类型：精度到秒，占用7个字节的存储空间。

timestamp 类型：最高精度可以到ns(纳秒)，小数位数可以指定为0-9，默认为6位。如果精度为0，则用7字节存储，与date类型功能相同；如果精度大于0，则用11字节存储。

timestamp with time zone 类型：timestamp 类型的变种，它包含了时区偏移量的值，保持 client 的 timezone。

timestamp with local time zone 类型：把 client 的 timezone 转换成 DB 的 timezone 对应的时间。

带时区的类型需要 Java 也是用带时区的？

MySQL

https://www.runoob.com/mysql/mysql-data-types.html

字符串类型

Oracle

  • 固定长度类型：char/nchar，自动补足空格，最多可以存储2000字节。
  • 可变长度类型：varchar2/nvarchar2，最大字节数都是4000，自动删除首尾的空格.

格式：char(size [byte| char]) 、varchar2(size [byte| char]) 、nvarchar2(size)

例：

char(8 byte) ：默认是byte

varchar2(4000) / varchar2(4000 char) ：可以存入4000个字母，不能存入4000个汉字。如果数据库字符集是GBK，那么varchar2最多存放2000个汉字；如果字符集是UTF-8，那么最多存放1333个汉字了。存放汉字建议使用nvarchar2。

nvarchar2(2000) ：每个字符长度都是2个字节，所以size最大值为4000/2=2000，最多存放2000个汉字或字母，不受数据库字符集的影响。

MySQL

https://www.runoob.com/mysql/mysql-data-types.html

存储过程和函数的区别

存储过程 函数
程序头部声明用procedure 程序头部声明用function
程序头部声明时不需要描述返回类型 程序头部声明要描述返回类型，而且PL/SQL块中至少要包括一个有效的return语句
可以使用in/out/in out三种模式的参数 可以使用in/out/in out三种模式的参数
可以通过out/in out返回零个或者多个值 通过return语句返回一个值，且该值要与申明部分一致，也可以是通过out类型的参数带出的变量
可以作为一个独立的PL/SQL语句来执行 不能独立执行，必须作为表达式的一部分调用
用于在数据库中完成特定的操作或者任务(如插入、删除) 用于特定的数据(如选择)
SQL语句(DML或SELECT)中不可调用存储过程 SQL语句(DML或SELECT)中可以调用函数