读书笔记——SQL学习指南（第2版）

1.dual表

mysql中可以使用select now();但是oracle中要求select语句必须有from 和表名，这时候就要使用dual表。mysql虽然不要求这样，但是为了兼容，mysql也提供了dual表。

dual表只包含一个名为dummy的列，并且只会有一个数据行。

 

2.字符型数据

char为定长，不足部分用空格补齐；varchar为可变长度。   但是使用length（） 函数对char列取长度的时候，会自动去掉自动补齐的空格，这样它的长度还是没有添加空格之前的长度。

插入字符串长度大于定义时，默认是strict模式，抛出异常。可以将其改为ansi模式，截断字符串并插入。

select @@session.sql_mode
结果： STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

修改模式方法：

set sql_mode='ansi';   

 

show character set;

显示所有字符集。 其中的maxlen代表一个字符最大需要几个字节存储。如果大于1，代表多字符集。

数据库、数据库表、数据库表的列可以有自己指定的字符集：

varchar(20) character set utf8
create database foreign_sales character set utf8;

 

varchar 可以容纳65536个字节  

对文本排序或分组时，只是用前1024字节，但是需要时可以放宽这个限制。

unsigned 代表无符号，即数字类型大于等于0.

 

对于日期格式：

只使用年月日的话要用 date  YYYY-MM-DD

跟踪订单发送时间的使用datetime   YYYY-MM-DD HH-MI-SS    1000-01-01 00:00:00  ~ 9999-12-31 00:00:00

记录用户何时修改表中数据可以使用timestamp  类型和datetime一样，但是mysql可以在向表中添加或者修改数据是自动为timestamp列产生当前的日期和时间。

year  YYYY                   time  HHH:MI:SS  保存时间段。

 

3. 如果限制一列只能添加固定的值

比如性别  只能填M或者F

gender char(1) check ( gender in('M','F')).    mysql虽然允许定义检查性约束，但并不强制使用它。

gender  enum('M','F') 使用enum枚举作为mysql的数据类型。

 

外键约束只能在InnoDB存储引擎创建数据库时才能起作用。

 

select version(), user(), database();

 

4. order by

order by 后可以使用函数：

order by  right(col2, 3);  按照col2列的后三个字母组成的字符串排序。

order by 后可以使用 数字占位符

order by 2, 4 表示按照select中的第二列和第四列排序。

 

5. null值

对于判断某列不等于什么的时候，先要看看有没有null值，因为null值无法判断相等或者不等，两个null值也不相等，判断null只能使用is null  。

 

sql是非过程化的语言，也就是说只是描述需要获取的数据库对象信息，而如何以最好的方式执行查询则由服务器负责。但是你可以通过控制sql让他按照你想要的方式去执行。

 

p88 5.4 相等连接和不等连接

比如某部门要举办象棋锦标赛，需要创建出来所有对弈者的列表。

select a.name,  'VS',  b.name from employee a inner join employee b on a.eid != b.eid  where a.dept=10 and b.dept=10

这样的话列出来的会有这种情况，比如：张三  VS 李四     还有一列是  李四 VS 张三，   为了避免这种情况，采用如下解决方法，每个人只和所有id比自己大的（或者小的）人比赛   on a.eid < b.eid    这样就可以了。

 

对union的结果集使用order by的时候，在最后一个集合后边使用order by ，并且要按照第一个集合的列名称来，如果使用第二个集合的列名称order by，将报错unknown column 

select a, b from table1 union select c, d from table2

order by a,b  

 

6.字符串中的单引号和特殊字符

update tablea set col2 = 'hello world li's a ';

如果字符串中含有单引号，需要对它进行处理才能正常执行。这就需要在单引号前边加上单引号进行转义，或者在单引号前边加上反斜杠进行转移。

quote()函数可以为单引号增加转义，   

select quote(col2) from tablea;   返回结果为  hello world li\'s a

当提取用于导出的数据时，可以使用quote()函数进行处理所有非系统产生的字符列。

 

包含国际化的特殊字符：  使用 char()函数。   select char(97, 98, 99)  返回  abc

select  ASCII('特殊字符')  可以返回该字符对应的ascii码。

使用concat()  和 char() 函数可以构建特殊字符串。

select concat('abc' , char(148), 'ef');  

 

 7. 内置函数

对应postion( 'character' in col2)  字符串中的第一个元素位置为1，如果找不到，才返回0。

insert(原始字符串, 字符串的开始位置, 需要替换的字符数， 替换字符串)

如果第三个参数是0，代表插入，  否则代表替换部分字符。

replace(原始字符串， 要替换的字符串，被替换的字符串)

 

select cast('2012-03-02 15:12:11' as DATATIME)

cast可以处理的包括 - , / 三种符号隔开的标准字符串,或者毫秒数。

如果是非标准的日期或者时间格式，需要用Str_to_date('字符串', '格式') 'September 17, 2008' 格式： '%M %d, %Y'

 

date_add(birthdate, interval '9-11' year_month) 加上9年11月

date_add(birthdate, interval 1 day)

datediff(day, '开始时间', '结束时间') 返回间隔时间；

 

8.分组

可以利用表达式分组 group by extract( year from start_date)

产生合计数 group by cola, colb with rollup 详见p144

oracle还支持 group by cude(col2,col3)

 

9. 单值与集合中的每个值比较

 

对于情况： id <> (select id from tablea where ...)

如果子查询返回多行的值，那么将会报错说子查询返回了more than 1 row。 要解决这种问题我们一般使用 not in 代替等于，不等于，大于，小于等于等方法。

不过还有另外一种方法，那就是在子查询集合前加上 all 或者 any

all 表示所有 ， any表示任一。 也就是all的话需要和子查询结果中的每一个比较之后都满足才可以，而any的话只要和其中任一个比较满足，就成功。

所以 in 和 =any 结果一样 not in 和 <>any 结果一样

 多列也可使用子查询 where (city, name ) in (select city, name from ...)

 

子查询分为关联子查询和非关联子查询

非关联子查询是指 子查询可以单独执行出结果， 然后可以使用结果替换子查询内容，外层查询可以在此结果上执行

关联子查询就是内部查询查找的时候要使用到外部查询的一列或多列的值才可以。 内部查询需要为外部查询的每一个候选行执行一次查询。

 

过滤条件中也可使用子查询

子查询可以出现在查询的 select from where having order by 中

子查询可以出现在 select update insert delete语句中

第九章很强大。 P149-P173

 P182 自内连接和自外连接的差距，也需要注意。

即查看所有员工及其主管。自内连接的话主管自己没有被包括在内，因为他的主管列是空，主管自己没有主管。

 

10. 条件逻辑 case表达式

查找型case表达式

case when 表达式1 then ..

when 表达式2 then ...

else ..

end

或者是

简单case表达式

case col1 when 10 then .. whern 20 then .. else .. end

 

行列变换可以使用case表达式方法。P199

case还可以使用存在性检查。