SQL学习笔记（四）集合简介与数据的生成、转换与操作

本文为《SQL学习指南》一书的学习笔记，供自己记录查找使用。如有侵权可私信删除。

今日文章包括：chap 6 使用集合

chap 7 数据生成、转换和操作

目录

使用集合

union操作符

数据生成，转换和操作

使用字符串数据

操作字符串的内建函数

返回数字的字符串函数：

返回字符串的字符串函数：

数值数据使用

时间数据使用

产生日期时间的函数

 操作时间数据

转换函数CAST()

本节命令小结

---

使用集合

集合的基本操作（符）：交（intersect），并（union），差（except）

使用简单的集合操作符可以实现表的纵向连接。

这种连接类似SAS中的SET语句连接，之前的JOIN类似SAS中的MERGE.

例如，在两条select语句中可以使用集合操作符执行集合操作，形成复合查询：

SELECT 1 num,"abc" str
    -> UNION
    -> SELECT 9 num,"xyz" str;

讲老实话，我觉得这个UNION很生硬啊，但是确实就把两条数据放进了同一个表。感觉更像给表insert 了一下....

集合操作符可以有两个修饰符，一个表示包含重复项，另一个表示去除重复项（但不一定是所有的重复项）。以上是一个简单的例子，接下来对几个集合操作符进行进一步的介绍。

union操作符

union与union all操作符可以连接多个数据集（区别在于是否去除重复项）。以下演示区别：

SELECT emp_id
    -> FROM employee
    -> WHERE assigned_branch_id = 2
    -> AND (title = "Teller" OR title = "Head Teller")
    -> UNION
    -> SELECT DISTINCT open_emp_id
    -> FROM account
    -> WHERE open_branch_id = 2;

遗憾的是，MySQL对于集合的操作比较初级，只有并运算能够 INTERSECT操作符和EXCEPT操作符MySQL都是不支持的。好在我们还可以使用横向连接的JOIN来破局。事实上，考虑作交和作差的实际含义，INTERSECT实际上就是相等连接（几个集合里都有：ON a = b ），EXCEPT则可能需要一些不等连接，具体情况可通过画韦恩图分析。

数据生成，转换和操作

使用字符串数据

首先生成接下来示例会用到的表。

CREATE TABLE string_tb1
    -> (char_fld CHAR(30),
    -> vchar_fld VARCHAR(30),
    -> text_fld TEXT
    -> );

转义字符：如果在字符串中出现单引号，前面加\即可。

update string_tbl
    -> set text_fld = "this string didn\'t work but it does now";

对于需要为字符串加上引号以及转义字符的情况，MySQL中有函数quote()可以达到这一效果。

select text_fld,quote(text_fld) from string_tbl;

利用char()函数可以根据ASCII码对应的数字返回特殊字符。（但是我失败了，不知道为什么。）

操作字符串的内建函数

返回数字的字符串函数：

函数	用途	注与例
length()	返回字符数	char列存储虽为定长（尾端用空格补齐），但MySQL服务器 在获取数据时会自动删去尾端空格，所以显示出的就是实际字符数。
position()	查找字符串中子字符串的位置	SELECT POSITION("characters" IN vchar_fld)，若找不到，返回0.
locate()	从任意位置开始搜索子字符串	SELECT LOCATE("is",vchar_fld,5)
strcmp()	字符串比较函数	第一个字符串在第二个之前，返回-1；相同，返回0； 在第二个之后，返回1.注意strcmp()函数对大小写不敏感。
		STRCMP("abc,xyz")的结果是-1.

返回字符串的字符串函数：

函数	用途	注
concat()	拼接	可以使用连接操作符||代替函数调用
insert()	插入或替换	SELECT INSERT("goodbye world",9,0,"cruel ")    结果返回“goodbye cruel world" 。第三个参数是0代表插入到第9个字符
		SELECT INSERT("goodbye world",1,7,"hello")，结果返回“hello world" 。  第三个参数非0代表将从第2个参数开始的这些个字符替换为第四个参数
substring()	提取子字符串	SELECT SUBSTRING("goodbye cruel world",9,5) ，结果返回cruel。

数值数据使用

函数	用途	注
truncate()	截取需要的小数位数	第二个参数是截取长度。Round也可以：四舍五入保留几位小数。 这两个函数第二个参数均可指定为负数，可看作以整十整百为单位看待数据

时间数据使用

产生日期时间的函数

如果列位还有记忆，在系列笔记（二）中，我们曾介绍一个将字符串转换为日期的简单函数str_to_date:

SELECT STR_TO_DATE(‘Dec-21-1980’,’%b-$d-%Y’);

在字符串无需人工识别日期部件的情况下，我们可以直接使用cast()函数将字符串转化为日期:

SELECT CAST('2018-09-17 15:30:00' AS DATETIME);

 AS后可选：DATETIME or DATE or TIME.

可以使用内建函数直接获取系统时钟，返回当前日期或字符串：

CURRENT_DATE()    CURRENT_TIME()    CURRENT_TIMESTAMP()

SELECT CURRENT_DATE(),CURRENT_TIME() ,CURRENT_TIMESTAMP();

 操作时间数据

 返回日期的时间函数

接受一个日期型值作为参数，再返回另一个日期。

• DATE_ADD() 函数

第二个参数包含了三个元素：interval关键字、所需要增加的数量以及时间间隔类型。比如我很好奇我在这世界的第10000天是哪一天：

 SELECT '2000-5-11' BIRTHDAY ,DATE_ADD('2000-5-11',INTERVAL 10000 DAY) 10000_DAYS_LATER;

        原来是27岁那年的9月27号，希望那一天我能开开心心，不知道那时候我会在哪里工作and有没有男朋友……

 看个有趣的

上面的2-11忘了用“”包裹，结果人家直接算成了-9...也没啥毛病吧哈哈哈哈

常用时间类型有（偷懒一下以后再写，在p129）

• LAST_DAY() 函数：返回当月月底的最后一天。返回的是一个类型为date的值。
• CONVERT_TZ() 函数：时区转换。有印象就行，要是用的话现查好咯

返回字符串的时间函数

大多用于提取日期或时间的某一部分。

可以用DAYNAME() 函数提取某一日期是星期几，更建议使用EXTRACT()函数来提取日期值中的信息，毕竟记忆一种函数的变体总是比记忆几种函数容易些.

SELECT EXTRACT(YEAR FROM '2008-09-28 22:09:05');

 提取的信息类型和date_add() 里的间隔类型是一样滴，直接去那里找就好啦。

返回数字的时间函数

• DATEDIFF()  求两个日期之间的间隔天数。比如我很好奇我出生到现在过了多少天： 

转换函数CAST()

用于转换数据类型的函数有不少，之所以介绍CAST()，一个重要原因是它在几种服务器中都适用。使用CAST()时必须提供一个作为关键字的值或者表达式，以及需要转换的类型。

SELECT CAST('1234567' AS SIGNED INTEGER);
SELECT CAST('1999-9-11' AS DATE);

 转换日期的前提是它的格式能被转换。如果不能，还是要先用str_to_date()。

本节命令小结