DB2 教程

DB2 教程

December 5, 2020
,
DB2

DB2 教程

排序

ORDER BY

SELECT
    select_list
FROM
    table_name
ORDER BY
    expression1 [ASC | DESC],
    expression2 [ASC | DESC],
    ...

过滤

DISTINCT

SELECT DISTINCT column_name1, column_name2, ...
FROM table_name;

IN

WHERE publisher_id IN (100, 103, 105)

LIMIT 行

LIMIT

LIMIT 10 OFFSET 5;

也可以写成：

LIMIT 5, 10

FETCH

SELECT title, rating
FROm books
ORDER BY rating DESC
FETCH FIRST 10 ROWS ONLY;

下一页：

SELECT title, rating
FROm books
ORDER BY rating DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

DB2 类型

Integers

支持三种：SMALLINT (-32768 ~ 32767)、INT (31 bits)、BIGINT (63 bits)。示例：

CREATE TABLE db2_integers(
	smallint_col SMALLINT,
	int_col INT,
	bigint_col BIGINT
);

Decimal

NUMERIC(p, s)
DECIMAL(p, s)
DEC(p, s)

12.345 的 p 就是 5 ，s 就是 3，所以存储这个至少定义为 DEC(5, 3)

CHAR

存储固定长度的字符串：

CHAR(n)
CHARACTER(n)

n 就是最多可以存储多少字节，范围 1 ~ 255

VARCHAR

存储变长的字符串：

column_name VARCHAR(n)

n 表示最大字节数，最大为 32740，如果还想更大，请使用 VARBINARY(n) 类型

DATE

范围 0001-01-01 到 9999-12-31，内部采用 4 字节存储，Date 常量：

'2019-05-06'

创建含有 Date 的列：

CREATE TABLE reading_lists (
	...
	added_on DATE DEFAULT CURRENT_DATE,
	...
);

常用的 DATE 函数:

  • 获取当前日期
SELECT 
    CURRENT_DATE 
FROM 
    sysibm.sysdummy1;

-- OR

SELECT
    CURRENT DATE    
FROM
    sysibm.sysdummy1;
  • 提取年月日
SELECT 
    YEAR(CURRENT_DATE) current_year,
    MONTH(CURRENT_DATE) current_month,
    DAY(CURRENT_DATE) current_day
FROM
    sysibm.sysdummy1;
  • 从 TIMESTAMP 提取 DATE
SELECT 
    DATE (CURRENT_TIMESTAMP) 
FROM
    sysibm.sysdummy1;
  • 格式化
SELECT
    title,
    CHAR(published_date,ISO) published_date_iso,
    CHAR(published_date,USA) published_date_usa,
    CHAR(published_date,EUR) published_date_eur,
    CHAR(published_date,JIS) published_date_jis
FROM
    books;
ORDER BY title;

TIME

范围：00.00.00 - 24.00.00，内部采用 3 个字节存储。

插入语句示例：

INSERT INTO daily_routines(start_at)
VALUES ('06:00', '06:05', '17:00');

使用 CHAR() 来格式化 TIME：

SELECT CHAR(start_at, USA) start_time, routine 
FROM daily_routines 
ORDER BY start_at;

DB2 常用的 TIME 函数：

  • 获取当前时间
SELECT CURRENT_TIME "Current time" FROM sysibm.sysdummy1;
SELECT CURRENT TIME "Current time" FROM sysibm.sysdummy1;
  • 提前小时、分钟、秒
SELECT HOUR(CURRENT_TIME) "Current hour",
MINUTE(CURRENT_TIME) "Current minute",
SECOND(CURRENT_TIME) "Current second"
FROM sysibm.sysdummy1;
  • 从 TIMESTAMP 提取 TIME
SELECT TIME(CURRENT_TIMESTAMP) "Current time"
FROM sysibm.sysdummy1;
  • 格式化 TIME
SELECT 
	CHAR(TIME, '17:07:08', ISO) ISO_format,
	CHAR(TIME, '17:07:08', USA) USA_format,
	CHAR(TIME, '17:07:08', EUR) EUR_format,
	CHAR(TIME, '17:07:08', JIS) JIS_format
FROM sysibm.sysdummy1;

TIMESTAMP

可以表达比秒更小的时间，还有也可以包含 TIME ZONE：

TIMESTAMP WITHOUT TIME ZONE
-- 默认的 TIMESTAMP 是不带时间戳的
TIMESTAMP

TIMESTAMP WITH TIME ZONE
TIMESTAMP(p) WITHOUT TIME ZONE

示例：

CREATE TABLE logs(
	...
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	...
);

TIMESTAMP 默认的秒精度是 6。

DB2 函数

Date 函数

String 函数

Function Description
CONCAT(s1, s2) 连接两个字符串
INITCAP Convert a string to proper case or title case format
INSERT(source_str, position, length, insert-string) Insert a substring into a string starting from a position and also deletes a substring specified by a length from the string.
INSTR Return the position of the nth occurrence of a substring within a string.
LEFT Extract a substring that consists of the number of leftmost characters from a string.
LOCATE Find the position of the first occurrence of a string within another string
LOWER Convert a string to lowercase
LPAD Return a string that is padded on the left with the specified character, or with blanks.
LTRIM Remove specified string from the beginning of a string.
REPEAT Repeat a string a number of times
REPLACE Replace all occurrence of a substring in a string with a new substring.
RIGHT Extract a substring that consists of the number of rightmost characters from a string.
RPAD Return a string that is padded on the right with the specified character, string, or with blanks.
RTRIM Remove specified characters from the end of a string.
SPACE Return a character string that consists of a specified number of blanks.
SUBSTRING Extract a substring with a specified length from a string.
TRANSLATE Return a string in which one or more characters in a string are converted to other characters.
TRIM Remove blanks or another specified character from the end, the beginning, or both ends of a string expression.
UPPER Convert all characters of a string to uppercase.