本文介绍了SQL查询的执行顺序，强调了`from`、`where`、`group by`、`having`、`order by`和`limit`的顺序。讨论了连接查询的性能优化，如选择小表作为驱动表，以及使用临时表的好处。还提醒了在`order by`中使用中文字段可能遇到的问题。此外，讲解了日期格式处理、数值格式化以及数据库设计的范式理论，如1NF、2NF和3NF。最后提到了`pymsql`库的执行限制和索引创建的建议，并探讨了窗口函数的应用场景。
序 执行顺序
from > where > group by > having > order by >limit
执行顺序高的语句要写在执行顺序低的语句前，否则会报错。
比如先写group by再写where就会报错
having的作用是选择符合条件的组，也就是单位为组。
这种写法group by score having score = max/min(score)是错误的，因为having中
的score不会遍历该分组，而是与select scroe from XX group by scroe类似。
1. 连接查询
-- 选择小表放右侧，作为驱动表，以提高性能
-- tb_college是小表，tb_student是大表
select tb_student.stu_name, tb_college.col_name from tb_student, tb_college
where tb_student.stu_id = tb_college.stu_id;
2. 中文字段引号
-- mysql 8.0中, order by后面的字段如果加了引号，会导致order by失效
select stu_height as '身高' form tb_student order by '身高' desc; -- order by 失效
select stu_height as '身高' form tb_student order by 身高 desc; -- order by 有效
3. 临时表(待验证)
-- (待验证) 连接查询时，构建临时表会节省资源？
-- recommended
select tb_college.name, ifnull(total, 0)
from tb_college left join
(select col_id, count(*) as total from tb_student group by col_id) tb_temp
on tb_college.col_id = tb_temp.col_id;
-- not recommended
select tb_college.name, count(tb_student.stu_id)
from tb_college left join tb_student on tb_college.col_id = tb_student.col_id;
4. null
-- 如果offset越界，整个select语句返回值是null， 而不是一张表
select stu_id from tb_student limit 100000, 1;
-- 而再对null进行select, 返回一张表，表中字段是null, 等同于 select null;
select (select stu_id from tb_student limit 100000, 1);
-- 如果指定一个字段，对null进行select，结果还是null, 而不是一张表
select sal from (select stu_id from tb_student limit 100000, 1);
5. 日期
-- 日期格式，加减天数不要使用+1 或-1，当日期为每月第一天和最后一天时，将不会进行跨月操作
-- 使用date_add(XXX, interval 1 day) 或 date_sub(XXX, interval 1 day)来进行日期的加减.
select t2.date from t1, t2
where t2.date = date_add(t1.date, interval 1 day);
-- 使用datediff(XXX, YYY)来进行XXX - YYY的运算.
select datediff(t2.date, t1.date) from t1, t2;
-- 使用date_format(字段名，'%Y-%m-%D')来获得date类型的年、月、日
select date_format(date, '%Y-%m') as month from tb group by(date_format(date, '%Y-%m'));
5. 格式处理
-- 小数位数处理
-- round(XXX, 小数位数) 四舍五入保留指定位数，当小数位数为负数，则开始保留整数位数
select round(t1.temperature, 2) from t1;
-- truncate(XXX, 小数位数) 在指定位数截断小数，舍去后面所有的位数
select truncate(t1.temperature, 2) from t1;
-- format(XXX, 小数位数) 四舍五入保留指定位数，位数不足用0补全，返回格式为字符串
select format(t1.temperature, 2) from t1;
6. 性能
-- in/not in， distinct运算效率较低。可改为存在性判断替代集合运算和去重。
-- 查询所有主管的姓名和职位
select t1.ename, t1.job from tb_emp t1 where exists
(select 'x' from tb_emp t2 where t1.eno = t2.mgr);
-- --------------------------------------------------------------------
-- limit offset： 当offset 较大时，查询多个字段性能较差。可改为先查主键（带索引性能好），
-- 再根据主键去查多列
select tb_emp.ename, tb_emp.job, tb_emp.sal from tb_emp
where tb_emp.eno = (select tb_emp.eno from tb_emp limit 1 offset 1000000000)
7. 数据库设计
7.1 1NF：每个字段不能再进行差分。
例：表中，电话字段又分为固定电话和手机，则不满足1NF
7.2 2NF：非主键字段由全部主键才能决定。
例1：主键（学号，学院号），字段 学院名称，则不满足2NF
（因为学院号就能决定学院名称）
例2：单主键，一般都满足2NF
7.3 3NF：非主键字段不能由其他非主键字段决定。
例：主键（学号），字段 学院号，学院名称，则不满足3NF
（因为学号能决定学院号，而学院号能决定学院名称）
7.4 BNF：有多种主键组合时，所有组合中的每个字段，都必须由其他所有字段才能决定。
例：主键（仓库号，货物号），属性 货物数量 仓库名，则不满足BNF
（因为（仓库名， 货物号）也可以作为主键，而仓库名和仓库号可以相互决定。）
7.5 不满足BNF及以下时，可能导致的问题：
7.5.1 冗余性 减慢速度
7.5.2 更新异常 每次更新属性，需要更新所有记录，否则记录之间产生冲突
7.5.3 插入异常 因为冗余的字段不能为空
7.5.4 删除异常 会连带删除冗余的字段
8. pymsql
cursor.execute(),括号中执行的sql语句只能有一个分号，否则执行失败。
原因是mysql服务器防止堆叠注入。
9. 索引
-- 创建复合索引
-- 此复合索引能发挥索引性能的情况是：1. 按ename查询 2. 按ename，job查询
-- 只查job时，此索引无法发挥作用，走index扫描。因为job在右侧
create index idx_emp on tb_emp (ename, job)
10. sum、avg、count、max、min窗口函数
如果over()中不写order by，那么窗口函数的对象是partition by生效后的所有记录。
如果over()中什么也不写，那么会返回所有行，每行都有相同的计算结果字段。（而如果不写over，只返回带有计算结果字段的一行）
count()可以附加条件，但一定要加另一情况。比如count(num = 2 or null), count(if(num=2, 1, null))
扫一扫
966
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
