欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL原生语句个人补漏

程序员文章站 2022-06-06 21:49:41
写了两年的php,一直依赖于phpstorm,其他三方工具和框架集成的高效语句,造成的后果就是基础愈发陌生,手写原生sql和php代码等卡壳严重。亡羊补牢!ps:原文是markdown,没修改格式直接传了 ......

# insert插入
insert into table_name (field1,field2...fieldn) **values** (value1,value2...valuen);所有列需添加可不写列名。
多条插入:insert into table_name (field1,field2...fieldn) **values** (value1,value2...valuen), (valuea,valueb...valuen), (valuea,valueb...valuen);
# where子句
select * from table_name where **binary** field = 'php / php';where子句字符串比较不区分大小写,要求区分时引入binary关键字。
# update更新
update table_name set field1 = new value1, field2 = new value2, ... (+ where);
update table_name set field1 = **replace**(field1, 'old_string', 'new_string')(+ where);替换字段中的字符。
# delete删除
delete from table_name where field = value;不加where,表数据全灭。
# union组合结果集
select value from table_name1(+ where) union all / distinct(default) select value from table_name2(+ where) orderby value;union组合两个以上的结果集输出新集,关键字默认distinct去重,为空不写,关键字all取全部数据包括重复。
# where,having
某种程度where与having作用相同,区别where筛选表字段;having对筛选字段进行再筛选,且常与聚合函数出没。
# join连接
inner join返回字段匹配的结果集(交集);left join返回左表所有记录和字段匹配的结果集;right join返回右表所有记录和字段匹配的结果集。
# sql执行顺序
写:select[distinct] - from - join - on - where - group by - having - union - order by - limit[offset]
执行:from - on - join - where - group by - having - select[distinct] - union - order by
# null
select * from table_name where field is null; is not null非null值。
# 索引
常用类型;primary key,unique,index,fulltext index,组合索引;
创建:create index indexname on table_name(field); alter table table_name add index indexname(field);
删除:drop index indexname on table_name; alter table table_name drop index indexname;
# and,or连接
and,or执行等级并列,多个or只执行首个,所以and出现在or之后一般不生效,二者同时应用时应写为“(or语句)”;
# explain执行分析
explain模拟执行sql语句,分析查询语句和性能瓶颈;
## 执行计划包含信息:
- id:查询序列号,执行顺序由上到下;如果是子查询,id会递增,id值与执行优先级成正比;相同id遵循由上到下原则;
- select_type:
- --simple简单子查询,不含子查询或union;
- --primary包含复杂的子部分,外部被标记为primary;
- --subquery,select或where列表包含了子查询;
- --derived,from列表中的子查询标记为derived(衍生),结果存放临时表;
- --union,通过union连接多个结果,第二个select后的为union;
- table:当前执行表
- **type**:查询类型
- --**最佳到最差**:system - const - eq_ref - ref - range - index - all;一般至少达到range,最好达到ref,避免all;
- --system,表只有一行记录(等同于系统表,const类型特例,忽略不计);
- --const,通过索引一次找到;
- --eq_ref,唯一性索引扫描,对于每个索引键,表只有一条记录匹配(主键或唯一键索引);
- --ref,非唯一性索引,所有匹配索引值的行全部读取;
- --range,检索给定范围的行,使用一个索引选择行
- --index,与all相同(读全表),但index只遍历索引树,索引文件比数据文件小;
- --all,数据文件查全表;
- possible_keys:可能应用的索引;其中会出现已有索引并按优劣顺序排列,开发者编写测试sql的时候要注意,若是有效的索引应让这些索引(按顺序)尽量生效;
- **key**:引用的索引,没选择索引为null;
- key_len:索引长度,越短越好;
- ref:显示使用哪个列或常数与key一起执行;
- **rows**:得到结果集需查询的行数,越少越好(多行相乘可以估算要处理的行数);
- extra:解决查询的详情;
- --**using filesort**,mysql使用外部索引排序,而不是按照表内索引顺序读取;
- --**using temporary**,使用了临时表保存中间结果,对查询结果排序时使用了临时表,常见order by,group by;
- --using index,相应select操作使用了覆盖索引,避免了访问表的数据行,效率很高。如果同时出现using where表示索引被用来执行索引键值的查找,没出现using where表示读取动作;
- --using where,使用where过滤;
- --using join buffer,使用了连接缓存;
- --impossible where,where子句值为false,不能用来获取任何元组;
- --select tables optimized away,在没有groupby子句的情况下,基于索引优化min/max操作或者对于myisam存储引擎优化count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化;
- --distinct,优化distinct操作,找到第一匹配的元组后即停止查找同样值的动作;