本文为《SQL学习指南》一书的学习笔记,供自己记录查找使用。如有侵权可私信删除。
今日文章包括:chap 6 使用集合
chap 7 数据生成、转换和操作
目录
使用集合
集合的基本操作(符):交(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()。