[SQL-Mysql]学习整理(持续更新)

学习资料

好视频推荐

好书推荐

《MySQL技术内幕:SQL编程》

理论笔记

SQL的执行顺序

参考资料 ：SQL执行顺序
sql语句定义的顺序

(1) SELECT (2)DISTINCT<select_list>
(3) FROM <left_table>
(4) <join_type> JOIN <right_table>
(5)         ON <join_condition>
(6) WHERE <where_condition>
(7) GROUP BY <group_by_list>
(8) WITH {CUBE|ROLLUP}
(9) HAVING <having_condition>
(10) ORDER BY <order_by_condition>
(11) LIMIT <limit_number>

SQL语句执行顺序

(8) SELECT (9)DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2)         ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

执行顺序的先后可以解释为什么在select语句中为字段起了别名，但无法在where语句中使用。也可以解释为什么在select语句中为字段起了别名，但无法继续在select中使用。
参考：mysql select 字段别名是否可以用在 select中或者where中
注意：
在mysql中，group by、having中可以使用select中定义的别名；where中不能使用别名；order by中可以使用别名。其余像oracle，hive中别名的使用都是严格遵循sql执行顺序的，groupby后面不能用别名。mysql特殊是因为mysql中对查询做了加强。
参考：为什么group by后面不能使用列的别名

字符串截取函数

参考资料：Mysql截取字符串
1.left(str,index)：从最左边(第1个字符)截取到第index个字符(包括第index个字符)，截取字符长度为index。即最左边index个字符。
2.right(str,index)：从最右边(右边第1个字符)截取到右边倒数第index个字符(包括第index个字符)，截取字符长度为index。即最右边index个字符。
3.substring(str,index)：index>0时，表示从左边第index个字符开始(包括第index个字符)到最右边结束。
index=0，返回空。index<0时，表示从右边倒数第index个字符开始(包括第index个字符)到最右边结束。
4.substring(str,index,len)：index>0时，表示从左边第index个字符开始(包括第index个字符)，截取一个长为len的子字符串。index=0，返回空。index<0时，表示从右边倒数第index个字符开始(包括第index个字符)，截取一个长为len的子字符串。
5.substring_index(str,delim,count)：delim是被匹配的字符(串)。count>0时，表示截取str中从左边数第count个匹配上的delim左边的子字符串(不包括delim)。count=0时，返回空。count<0时，表示截取str中从右边数第count个匹配上的delim右边的子字符串(不包括delim)。如果str中没有被匹配的字符(串)，则返回原字符串。

函数

实践技巧

高效查看mysql帮助文档的方法

参考资料：高效查看MySQL帮助文档的方法
实际操作：在命令行中进入mysql，然后利用？+关键字或者help+关键字查询相关内容的帮助文档。

快速注释

Ctrl+?

Mysql实践疑难

需求：已知当前所在年份和周，想确定这一周的开始时间和结束时间(以周一为一周的开始)

表：t_table
year Week
2011 2
2011 3
2011 4
2011 5
参考资料：
mysql 某周的起始和结束日期
mysql时间相关函数
解答-SQL语句：

SELECT year, week, x.start, ADDDATE(x.start, 6) AS end
   FROM (
 SELECT ADDDATE(jan1, (t.week - WEEK(jan1, 5)) * 7 - WEEKDAY(jan1)) AS start, year, week
   FROM (SELECT MAKEDATE(`year`, 1) AS jan1, year, week FROM t_table) t
 ) x;

具体流程：
1.给t_table增加一个字段，表示当前年份的第一天，起别名jan1，查出的临时表起名t

(SELECT MAKEDATE(`year`, 1) AS jan1, year, week FROM t_table) AS t

注：MAKEDATE(year, dayofyear)：给定年份和一个数字dayofyear ，返回对应年份第dayofyear天的时间，在这里获得了对应年份的第一天，起名为jan1
2.将jan1转化成当前周的第一天，起别名start，查出的临时表起名x

(SELECT ADDDATE(jan1, (t.week - WEEK(jan1, 5)) * 7 - WEEKDAY(jan1)) AS start, year, week FROM t) AS x

注：
WEEK(date[,mode])：根据给定时间和模式，返回给定时间的所在周数，这里模式取5表示，以一年的第一个周一所在周为第一周(说明t.week也是在这个意义下得到的周数)
WEEKDAY(date)：返回给定时间所在的工作日，0表示周一，6表示周日
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)：ADDDATE函数有两种用法，这里采用了第二种。将周数得到的时间差加到了jan1上，得到了当周第一天的时间。
3.根据当周第一天start得到当周最后一天，起名end，得到最终表

SELECT year, week, x.start, ADDDATE(x.start, 6) AS end 
	FROM x

需求：想提取某个字段中的数字，去掉中文

产品表如下图(表来源：帆软社区-零基础快速自学SQL-任务1)：

想知道一个产品的最小单元对应的价格(如苹果汁数量为每箱24瓶，单价为18，最小单元对应价格为18/24=3/4元每瓶)

参考资料：
Mysql清除字段中的中文，只保留数字、字母等非中文符号
解答-SQL语句：

select replace(convert(`产品`.单位数量  using ascii),"?","")
from `产品`

具体流程：
1.使用concert函数，将字段类型转换成ASCII类型，此时中文会显示为’?’
2.配合repalce函数，将’?‘变成’'字符，则可以达到清除的效果

需求：数据按月、季度、日、时间段统计

参考资料：
MySQL统计函数记录——按月、按季度、按日、时间段统计
MySQL DATE_FORMAT() 函数：格式化显示时间

按年分类：

select date_format(col, '%Y'), count(*)
from mytable 
group by date_format(col, '%Y');
分析：col的时间格式化展示'年'
注意：year(col)也可以获年份时间，即等效于date_format(col, '%Y')

按月分类：

select date_format(col, '%Y-%m'), count(*) 
from mytable 
group by date_format(col, '%Y-%m');
分析：col的时间格式化展示'年-月'
注意：month(col)也可以获得月份时间，即等效于date_format(col, '%m')

按季度分类：

select concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3)) , count(*) 
from mytable 
group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3)); 
分析：按季度汇总事实上就是将（1，2，3）月对应季度1，(4，5，6)月对应季度2，以此类推，所以考虑(月份+2)/3之后取整
注意：quarter(col)也可以获得季度时间，即等效于FLOOR((date_format(col, '%m')+2)/3)

按小时分类：

date_format(col, '%Y-%m-%d %H'), count(*)
from mytable 
group by date_format(col, '%Y-%m-%d %H');

需求：查询一个float类型的字段

问题： MySql中float类型的字段，查询不出结果(数据来源：帆软社区-零基础快速自学SQL-任务1)：
现象： 在MYSQL中，字段类型为FLOAT的字段，如果不指定FLOAT的长度和小数点位数，要根据FLOAT字段的值精确查找，结果会是空

select b.`订单ID`, b.`折扣`
from `订单明细` AS b 
where b.`折扣` = 0.15

参考资料：
MySql中float类型的字段，查询不出结果

解决方案：

select b.`订单ID`, b.`折扣`
from `订单明细` AS b 
where b.`折扣` like '0.15'
注意：也可以改成 b.`折扣` like 0.15
或cast(b.`折扣` AS char) = 0.15
或concat(b.`折扣`, '') = '0.15'

注意：如果将字段类型改成float(10,2)，例如
ALTER TABLE book MODIFY COLUMN price FLOAT(10,2);
此时也可以使用where b.折扣= 0.15但不能使用where b.折扣= 0.150