0、SQL基础Note
wehre：行过滤
group by：分组
having：分组过滤
exists：相关子查询
where和having：针对行过滤 与 针对分组结果过滤的区别
where子查询和exists子查询：where包含子查询时先查得where后的子查询的结果再根据结果进行where前的主查询；exists先执行exists前的主查询得到结果再对每个结果进行子查询，可见后者在数据量大时会很耗时，慎用。
视图：视图通常用于查询而很少用于更新，并非所有的视图都可以进行更新操作，如视图中存在分组(group by)、联结、子查询、并(unoin)、聚合函数(sum/count等)、计算字段、DISTINCT等都不能对视图进行更新操作。
索引：单列索引、复合索引、唯一索引、主键索引 等。索引的设计：
where子句中的列可能最适合做为索引
不要尝试为性别或者有无这类字段等建立索引(因为类似性别的列，一般只含有“0”和“1”，无论搜索结果如何都会大约得出一半的数据)
如果创建复合索引，要遵守最左前缀法则。即查询从索引的最左前列开始，并且不跳过索引中的列
不要过度使用索引。每一次的更新，删除，插入都会维护该表的索引，更多的索引意味着占用更多的空间
使用InnoDB存储引擎时，记录(行)默认会按照一定的顺序存储，如果已定义主键，则按照主键顺序存储，由于普通索引都会保存主键的键值，因此主键应尽可能的选择较短的数据类型，以便节省存储空间
不要尝试在索引列上使用函数。
子查询与join查询：一般来说前者效率通常比后者低。
假设 用户订单表user_order有10w条数据，商品表goods有1000条数据，其间通过商品id关联，且g.type=1的goods有100条数据。前者子查询后需要100个子查询数据每个用户10w数据集进行查询、后者则不需要。
select uo.* from user_order uo where uo.gid in ( select g.id from goods g where g.type=1 );select uo.* from user_order uo inner join goods g on uo.gid=g.id and g.type=1;
逗号分隔多表的多表查询其实就是inner join查询：from a, b where a.id=b.aid 与 from a inner join b on a.id=b.aid 等价
count(1)、count(*)：不会忽略值为null的字段
count(列名)：会忽略值为null的字段
1、 针对返回结果的case语句
示例
select id, name, case user_rolewhen 0 then "管理员"when 1 then "未注册用户"when 2 then "注册用户"else concat("未知值:", user_role)end “角色”from user 2、动态添加where条件
SELECT id, name, school_number AS schoolNumber, gender, grade FROM student where (?4 is NULL or name like %?4% or school_number like %?4%) and (?5 is NULL or gender= ?5) and (?6 is NULL or grade like %?6%)
该句应用背景为支持用户搜索返回模糊匹配结果，特点在于用户可以动态选择搜索条件的个数，?4、?5、?6 三个参数用户传不传均可。即不用因为用户传送参数个数的不同进行写多条SQL语句或在业务层面进行if判断，而只用一条SQL。
也可以使用if语句，示例：
update course c set c.is_enable= if (c.license_expire_time > now() , true, false), c.disable_time= if (c.is_enable =true, null, now());
3、SQL事件
可以让事件只发生一次或多次或周期性发生。
详见：
示例：
-- '以下为course表中is_enable, disable_time自动更新的事件';SET GLOBAL event_scheduler = ON;drop event if exists `autoDisableCourse`;create event `autoDisableCourse`on schedule every 1 minuteDO update course c set c.is_enable= if (c.license_expire_time > now() , true, false), c.disable_time= if (c.is_enable =true, null, now());
4、SQL触发器
示例：
-- '以下为course表中exp_num字段自动更新有关的两个触发器';drop trigger if exists `updateExpNumInCourseTableAfterInsertExp` ;DELIMITER ;;CREATE trigger `updateExpNumInCourseTableAfterInsertExp` after insert on experiment for each row begin -- '插入一个实验时更新课程表中的实验数字段';update course c set c.exp_num =(select count(e.id) from experiment e where e.course_id=NEW.course_id) where c.id = NEW.course_id;end ;;DELIMITER ;drop trigger if exists `updateExpNumInCourseTableAfterDeleteExp` ;DELIMITER ;;CREATE trigger `updateExpNumInCourseTableAfterDeleteExp` after delete on experiment for each row begin -- '删除一个实验时更新课程表中的实验数字段';update course c set c.exp_num =(select count(e.id) from experiment e where e.course_id=OLD.course_id) where c.id = OLD.course_id;end ;;DELIMITER ;
5、关联表组织和命名
一对一、一对多关系通过外键来关联表，外键存储在主动关联的表即reference table（而非被管理表即referenced table）中；命名采用user、user_role
多对多关系通过主键来关联表，将reference table和referenced table的主键单独存在第三张表中来维护关联关系；命名采用user、region、user_region
可以看到，两者都会有下划线，此时不好快速区分到底 带下划线的表是 一张详细表（如user_role）还是一张 用来表示两张表关联关系的表（如user_region）。解决方法：一种是前者去掉 "user_" 前缀；另一种是后者统一加上 可区分的前缀如 "re_user_region" 。