本文共 2053 字，大约阅读时间需要 6 分钟。

第5章操作符与函数

5.1创建派生列

查询语句：

SELECT title_id,         Price,         0.10 AS “Discount”，         Price * (1-0.10) AS “New price”  FROM titles;

5.2执行算术运算

查询语句：

SELECT title_id,         Price * sales AS “Revenue”  FROM titles  WHERE type = 'biography'  ORDER BY price * sales DESC;

5.3确定计算的顺序

算术操作符的顺序高于比较操作符高于逻辑操作符。

5.4使用||连接串

查询语句：

SELECT au_fname || ' ' || au_lname AS “Author name”  FROM authors  ORDER BY au_lname ASC, au_fname ASC;

5.5使用SUBSTRING提取子串

查询语句：

SELECT    SUBSTRING(pub_id FROM 1, FOR 1) AS “Alpha part”，     SUBSTRING(pub_id FROM 2) AS “Num part”  FROM publishers;

5.6使用UPPER和LOWER更改串大小

查询语句：

SELECT title.name  FROM titles  WHERE UPPER(title.name) LIKE '%mo%';

5.7使用TRIM()修正大小

修剪字符串，删除前导、尾随或两者。

查询语句：

SELECT     '<' || TRIM(LEADING FROM 'AAA') || '>' AS “Untrimmed”，     '<' || TRIM(TRAILING FROM 'AAA') || '>' AS “Trailing”，     '<' || TRIM('AAA') || '>' AS “Both”  FROM dual;

5.8使用CHARACTER_LENGTH()得到串长度

查询语句：

SELECT au_fname,         CHARACTER_LENGTH(au_fname) AS “LEN”  FROM authors;

5.9使用POSITION()查找子串

查询语句：

SELECT     au_fname,     POSITION('e' IN au_fname) AS “Pos e”，     au_lname,     POSITION('ma' IN au_lname) AS “Pos ma”  FROM authors;

5.10执行日期及时间间隔运算

查询语句：

SELECT title_id,         pudate  FROM titles  WHERE EXTRACT(YEAR FROM pudate) BETWEEN 2001 AND 2002    AND EXTRACT(MONTH FROM pudate) BETWEEN 1 AND 6  ORDER BY pudate DESC;

5.11获取当前日期和时间

查询语句：

SELECT     CURRENT_DATE AS “Date”，     CURRENT_TIME AS “Time”  FROM dual;

5.12获得用户信息

查询语句：

SELECT CURRENT_USER AS “User”  FROM dual;

5.13使用CAST()转换数据类型

查询语句：

SELECT     Price AS “price(DECIMAL)”，     CAST(Price AS INTEGER) AS “price(INterger)”，     '<' || CAST(Price AS CHAR(8)) || '>' AS “price((CHAR)(8))”  FROM titles;

5.14使用CASE计算条件值

查询语句：

SELECT     type,     CASE        WHEN type = 'history' THEN Price * 1.10        WHEN type = 'psychology' THEN Price * 1.20        ELSE Price     END AS “New price”  FROM titles  ORDER BY type ASC, title_id ASC;

5.15使用COALESCE()检查空值

查询语句：

SELECT     Pub_id,     City,     COALESCE(state, 'N/A', Country) AS “state”  FROM publishers;