《SQL基础教程》笔记（三）

第5章操作符与函数

5.1创建派生列

SLEECT 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使用||连接串

||是2个连续的管道字符

可将多个串连接合并为一个串，如’a’||’b’||’c’||’d’等于’abcd’

列出作者的姓，名（连接为一列并且按姓，名排序）

SELECT au_fname|| ‘ ‘ || ‘au_lname

AS “Author name”

FROM authors

ORDER BY au_lname ASC,au_fname ASC;

 

5.5使用SUBSTRING提取子串

子串是源串的连续字符序列，包含空串或整个源串本身

将出版社ID分割为字母和数字部分

SELECT pub_id

SUBSTRING(pub_id FROM 1,FOR 1)

AS “Alpha part”

SUBSTRIING(pub_id FROM 2)

AS “Num part”

FROM publisher;

 

5.6使用UPPER和LOWER更改串大小

SLEECT title.name

FROM titles

WHERE UPPER title.name() LIKE ‘‘%mo%’;

5.7使用TRIM()修正大小

TRIM ([LEADING|TRAILING|BOTH]

FROM string）

 删除‘AAA’的前导，尾随，前导和尾随，’<’,”>’是表示删除空格后串的范围

 

SELECT

‘<’ || AAA ‘||’>’

AS “Untrimmed”,

‘<’||TRIM(LEADING FROM ‘AAA’)||’>’

AS”leading”,

‘<’||TRIM(TRAILING FROM ‘AAA’)||’>”

AS “trailing”

‘<’||TRIM(‘AAA’)||’>’

AS “Both”

 

删除以H开头的姓中的前导的H

SELECT au_lname,

TRIM (LEADING ’H’ FROM au_lname)

AS “Trimmed name”

FROM authors;

 

5.8使用CHARACTER_LENGTH()得到串长度

列出作者名的长度

SELECT au_fname,

CHARACTER_LENGTH(au_fname) AS “LEN”

FROM authors;

 

 

5.9使用POSITION()查找子串

列出e在名中，ma在姓中的位置

SELECT

Au_fname,

POSITION(‘e’ IN au_fname) AS “Pos e”,

Au_lname,

POSITIONS(‘ma’ IN au_lname) AS “Pos ma”,

FROM authors;

 

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

列出2001，2002上半年出版的书，并列出出版日期排序

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”,

CURRENT_TEMP  AS “timestamp”

 

 

5.12获得用户信息

SELECT CURRENT_USER AS “User”

 

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

将书价从DECIMAL转换为INTEGER和CHAR(8),<>表示类的范围

 

SELECT

Price

AS “price(DECIMAL)”,

Cast (price(INTERGER),

AS “price(INterger)”,

‘<’||CAST(price AS CHAR(8)) || ‘>’

AS “price((CHAR)(8))”

FROM tiles;

 

5.14s使用CASE计算条件值

将历史书价*1.1，将心理学书架*1.2，其他不变

SELECT

Title_id,

Type,

Price,

CASE type

WHEN ‘history’

THEN PRICE*1.10

WHEN ‘psychology’

THEN priuce*1.20

ELSE price

END

AS “New price”

FROM tites;

ORDER BY type ASC,title_id ASC;

 

5.15使用COALESCE()检查空值

COALESCE (expr1,expr2,expr3)等价于

CASE

WHEN expr1 IS NOT NULL THEN expr1

WHEN expr2 IS NOT NULL THEN expr2

ELSE expr3

END

列出位置，若州为空，显示N/A

SELECT

Pub_id,

City,

Coalesce(state,’N/A’,)  AS “state”,

Country

FROM publishers；

 

5.16使用NULLIF表示式

 

NULLIF (expr1,expr2)等价于 CASE

WHEN expr1=expr2 THEN NULL

ELSE expr1

END

如果没有合同，contact为0

SELECT

Title_id,

Cotract,

NULLIF(contract,0) AS “NULL contract”

FROM titles;