SQL基础要点概述——基于Mysql

一、概论

1. 关系型数据库就是通过冗余字段，并且该冗余字段通常是外键，来关联数据的。
2. 逻辑主键是没有实际含义的，而自然主键有实际含义，但不管何种主键在赋值后都不允许修改。
3. SQL查询的结果完全视为一张二维表（或称中间表、结果集），并且这个也可以作为SQL查询的输入（如子查询）

二、数据类型

2.1、字符型数据

1. quote()：用单引号将字符串包含起来，并为字符串本身的单引号增加转义符
2. concat()：字符串连接，也可将数字和日期转为字符串
3. length()： 是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符
4. char_length()：不管汉字还是数字或者是字母都算是一个字符
5. position()：获取指定字符串的位置（从1开始，返回0表示找不到）
6. replace()：替换字符串中的指定部分
7. substring()：截取子串
8. 使用like和regexp来比较字符串：

-- 这些比较操作的结果为1或0，两者区别：like匹配整个列,而regexp在列值内进行匹配
select name like '%ns' from tableA;
select name regexp '%ns' from tableA；

2.2、时间数据

Mysql时间类型数据的存储格式可见官方文档：
Date and Time Data Type Representation

1. 可见，TIMESTAMP是依赖与Mysql所设置的时区的，存储的是其所在时区距离1970-01-01 00:00:00 UTC ± 时差 的秒数，而显示时又会调整为所在时区的时间；其他时间类型是与时区无关的。
2. 时间数据的基本精度都是秒，秒级以下精度存储在额外的字节中。

2.2.1、时间与字符串的转换

• 自动转换
  可以将字符型数据直接插到时间型的列中，Mysql会自动将字符串转换成对应的时间，但该字符串必须满足默认的格式—— ‘YYYY:MM:DD HH:MI:SS’

通过sql语句查询下 看看现在的值
show variables like '%date%';
默认的值是：
date_format= %Y-%m-%d
datetime_format=%Y-%m-%d %H:%i:%s

• 手动转换
  cast()：不仅可将字符串转为时间（此方法提供的待转成时间的字符串只能是该函数所接收的格式），还可以将字符串转为数值，属于SQL:2003标准。
  str_to_date()、date_to_str()：可以自定义字符串格式

2.2.2、常用函数

1. date_add()：返回时间，为指定日期增加任意一段时间间隔
2. last_day()：返回时间，得到指定日期的当月的最后一天
3. convert_tz()：返回时间，将某个时区的时间转换为另一个时区对应的时间
4. extract()：返回字符串，得到指定日期的年份/月份/天数/小时数/分钟数/秒数，属于SQL:2003标准。

更多函数参考官方文档：
Date and Time Functions

三、表连接

join分类：

• join、cross join、inner join 作用是一样的，因为默认情况下都是内连接。
• left outer join、right outer join、full outer join 这些外连接也不过是在内连接的基础上，再把不满足连接条件的记录附加在结果集的最后罢了。
• natural join这种东西为了可读性还是无视掉好了。

有关join的几个要点：

• 连接顺序不影响最终结果。通常情况下，SQL中写的连接顺序也并不是数据库在执行时会遵循的顺序，数据库优化器会根据自己的规则调整连接顺序，一般会将较小的表放在左边作为驱动表，不过也可以使用STRAIGHT_JOIN关键字人为强制设定驱动表。
• 驱动表是全扫描，被驱动表可利用索引。连接时先过滤where条件，再逐条扫描驱动表，根据驱动表的on条件去找被驱动表的对应记录。这也是为什么要将较小的表作为驱动表的原因。

另外几点：

• 子查询结果可以作为被连接的表
• 表连接时，on条件和where条件可以混淆，结果是一致的，不过为了可读性还是要区分开
• 不仅两个不同的表可以连接，一个表也可以和自身连接，称”自连接”。这种情况一般是因为表中含有指向自身的外键

四、分组与聚集

• 只有出现在group by子句中的列名，才可以出现在select后面——你问为什么要这样？因为分组查询相当于每个分组只查出一条记录，在这个分组中，出现在group by后面的列名的值肯定是唯一的，而其他的列名可就说不准了。
  例外： Mysql比较6，它可以不遵循这个规定。其实道理也简单，就是若一个分组的某个列下有多个不同值，它只会从中选取第一个值。

• select后面还可跟任意聚集函数——你又问为什么？因为聚集函数可以把一组值给整成一个值。

-- 查询记录数
select count(name) from tableA;
-- 查询记录中不同name的数目
select count(distinct name) from tableA; 

五、NULL关键字

--在数据库中没有boolean型的值，通常用tinyInt来表示布尔值，所以
select 6 = 6;        -- 结果为1
select 6 != 6;       -- 结果为0

-- 有一个特殊的值，NULL值既不满足=6，也不满足!=6，对于NULL的比较只能用is和not is，试图执行
select NULL = 6;     -- 结果为NULL
select NULL != 6;    -- 结果为NULL
select NULL = NULL;  -- 结果为NULL
select NULL IS NULL; -- 结果为1

-- 所以以下语句无法查出name为NULL的记录
select name from tableA where name !=6;
-- 正确的写法为
select name from tableA where name !=6 or name is NULL;

-- 同时聚集函数也会直接忽略值为NULL的列，就当它不存在一样。所以最好不要让数据库中存在NULL值，而去设置一个默认值来代替它，不然会使查询语句和聚集函数的结果不符合预期且无法轻易察觉。

六、子查询

6.1、按结果集的维度分类

单列单行（点）：
可直接用于 =、<>、!=、<、<=、>、>= 运算符

单列多行（线）：
可直接用于 in、not in 运算符
或用于 =、<>、!=、<、<=、>、>= 和 all()、any()的组合

多列多行（面）：
可直接用于 in、not in 运算符

6.2、按关联还是非关联分类

当子查询的where条件中包含表之间的连接条件，这时的子查询就成为了关联子查询，就不能像非关联子查询那样可以不依赖外层查询而去独立执行内层查询。

非关联子查询用起来最爽，因为它最符合人的正常思维——我们先取出一个结果，再使用这个结果，一步一走，思路清晰。

6.3、子查询等价转换

非关联子查询和使用in运算符、关联子查询和使用exists运算符，两者可以互相转换；在某些情况下，还可以与连接查询互相转换。

-- 非关联子查询和使用in运算符。
SELECT  * FROM  EMP  WHERE  EMPNO > 0  AND  DEPTNO IN (SELECT DEPTNO  FROM  DEPT  WHERE  LOC = 'MELB');

-- 关联子查询和使用exists运算符。
SELECT * FROM  EMP  WHERE  EMPNO > 0  AND  EXISTS (SELECT 'X'  FROM DEPT WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = 'MELB');

-- 连接查询（注意这里需要多加一个DISTINCT,仔细想想为什么）。
SELECT DISTINCT EMP.* FROM EMP JOIN DEPTNO ON DEPT.DEPTNO = EMP.DEPTNO WHERE DEPT.LOC = 'MELB';

再看一个稍微复杂点的例子：

下面的非关联子查询语句是为了查出datasourcekey相同的记录中version最大的记录：

SELECT 
    t.*
FROM
    datasource t
WHERE
    (t.datasourcekey , t.version) IN (SELECT 
            t2.datasourcekey, MAX(t2.version)
        FROM
            datasource t2
        GROUP BY t2.datasourcekey);

我们转换成关联子查询和使用exists运算符：

SELECT 
    t.*
FROM
    datasource t
WHERE
    EXISTS( SELECT 
            1
        FROM
            datasource t2
        WHERE
            t2.datasourcekey = t.datasourcekey
        GROUP BY t2.datasourcekey
        HAVING MAX(t2.version) = t.version);

利用Mysql对group by的特有支持，尝试转换成连接查询：

SELECT 
    t.*
FROM
    datasource t JOIN datasource t2 
    ON t.datasourcekey = t2.datasourcekey
GROUP BY t2.datasourcekey
HAVING MAX(t2.version) = t.version

然而失败了，因为：
（1）连接查询虽然连接了两张表，但产生的结果实质上被当作一张表。
（2）SQL语句按某种顺序执行，先执行完group by子句并产生了结果才会在这个结果上再执行having子句。

所以，在完成group by子句的时候，已经为每个分组筛选出一条记录了，根据Mysql对group by的特有特性，这条记录中，没有在group by后面出现的列名的列值只是随机选取出来的，该条记录的t.version列的列值很大可能是不等于max(t2.version)的，而满足该条件的记录很大可能被随机过滤掉了。

6.4、到底哪种形式效率更好

以上三种等价形式的优劣分析：

• in是子查询为驱动表，外面的表为被驱动表，故适用于子查询结果集小而外面的表结果集大的情况。in一般都是非关联子查询。
• exists是外面的表位驱动表，子查询里面的表为被驱动表，故适用于外面的表结果集小而子查询结果集大的情况。exists一般都是关联子查询。

（1）对于关联子查询，必须先执行外层查询，接着对所有通过过滤条件的记录，执行内层查询。外层查询和内层查询相互依赖，因为外层查询会把数据传递给内层查询。——(意味着内层查询会执行多次)

（2）对于非关联子查询，则必须先完成内层查询之后， 外层查询才能介入。——(意味着外层查询会执行多次)

（3）对于连接查询，其驱动表是优化器自己选择的，而关联子查询的外层表一定是作为驱动表，非关联子查询的内层表也一定是作为驱动表。不过要与子查询达到相同的效果，连接查询还需要再走一个DISTINCT逻辑。——(连接查询只会执行一次，不过其连接过程也相当于执行多次右层查询)

所以没有一位的哪者效率更高，而要根据具体情况具体分析。

人工优化子查询SQL是否必要？

某些情况下没必要，因为Mysql优化器会帮你优化的。比如你给一句非关联的使用in的子查询SQL，它会把它转换为关联的使用exists的子查询SQL，然后再去执行。

但有时候Mysql这种没头脑的优化也是蛮讨厌的，比如某个情况明明非关联查询效率更高，但它却偏偏帮你”优化”成关联查询去执行。

不过Mysql优化器倒是不会把一个子查询优化成连接查询，并且Mysql优化器对于连接操作更mature，所以人工优化成连接查询还是有点必要的。

要想彻底弄懂子查询的优化问题，还是建议看官方文档：
Optimizing Subqueries

参考资料

参考书籍：
《SQL学习指南》 —— Alan Beaulieu

参考链接：
mysql group by的特有特性
mysql in 子查询 效率慢 优化
exist-in和关联子查询-非关联子查询
oracle中关于in跟exists，not in 和 not exists、关联子查询、非关联子查询