----------------MySQL常用SQL--------------------------------------
----常用函数(待补全)
date_format(date,'%Y-%m-%d') -------------->oracle中的to_char();
str_to_date(str_date,'%Y-%m-%d %H:%i:%s') -------------->oracle中的to_date();
----常用查询(待总结)
-----insert
1、insert into 最普遍的插入,如果表中存在主键相同的数据,执行会报错。
2、replace into 如果表中存在与主键相同的数据,则替换掉该主键的记录,反之则插入(存在就替换,反之插入)
3、insert ignore 如果表中存在主键相同的数据不在插入该条数据,反之则插入(存在则忽略,反之插入)
4、insert into *** on duplicate key update 如果表中存在该记录,则按新的数据进行修改,不存在则插入。(与replace into的区别在于:replace是完全替换成新的数据记录,此处是修改不同的地方,新纪录中没有的部分依然采用老记录中的数据。)
----批量update\insert
INSERT INTO user_info_backup ( employee_name, age, sex)
SELECT
employee_name,
age,
sex
FROM
employee
-----批量修改
UPDATE sheet1,employee set sheet1.card_id=employee.card_id where sheet1.employee_code =employee.employee_code
----MySQ执行计划(Explain)解析
id: 查询的唯一标识
执行计划-ID:
三种情况:
(1)id相同,执行顺序由上至下
(2)id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
(3)id有相同也有不同,同时存在
select_type: 查询的类型
SIMPLE: 简单查询,不包含子查询和union
PRIMRARY: 包含子查询时的最外层查询; 使用union时的第一个查询
UNION: 包含union的查询中非第一个查询
UNION RESULT 临时结果
DEPENDENT UNION: 与 UNION 相同,但依赖外层查询的结果
SUBQUERY: 子查询
DEPENDENT SUBQUERY: 依赖外层查询的子查询
DERIVED: 用于 FROM 中的子查询(中间表)
table: 查询的表, 可能是数据库中的表/视图,也可能是 FROM 中的子查询
type: 搜索数据的方法
1.null: 不需要访问索引和表即可完成 例: select now()
2.const: 表中仅有一行匹配,在分解查询计划时直接将其读出作为常量使用。例:select * from employee where employee_id='10001'
3.eq_ref: 使用 PRIMARY KEY 或 UNIQUE KEY 进行关联查询 例:select * from employee join employee_auth on employee_auth.employee_id=employee.employee_id where employee_auth.id='113726'
4.ref: 使用允许重复的索引进行查询 例:select * from employee where employee.employee_code='Y1394'
5.range: 使用索引进行范围查询 例:select * from employee where employee.employee_id <10026
6.index: 在索引上进行顺序扫描。常见于在多列索引中未使用最左列进行查询
7.all: 扫描全表,最坏的情况
possible_keys: 可能使用的索引
实际使用的索引。
如果为NULL,则没有使用索引,查询中若使用了覆盖索引,则该索引和查询的select字段重叠
key: 最终决定要使用的key
key_len: 查询索引使用的字节数。通常越少越好
索引使用的字节数,相当于长度
char和varchar跟字符编码也有密切的联系,
latin1占用1个字节,gbk占用2个字节,utf8占用3个字节。(不同字符编码占用的存储空间不同)
ref: 查询的列或常量
rows: 需要扫描的行数,估计值。通常越少越好
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
extra: 额外的信息
using filesort: 查询时执行了排序操作而无法使用索引排序。虽然名称为’file’但操作可能是在内存中执行的,取决是否有足够的内存进行排序。
应尽量避免这种filesort出现。
using temporary: 使用临时表存储中间结果,常见于ORDER BY和GROUP BY语句中。临时表可能在内存中也可能在硬盘中,应尽量避免这种操作出现。
using index: 索引中包含查询的所有列不需要查询数据表(回表)。可以加快查询速度。
using where: 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给客户端
using index condition: 索引条件推送(MySQL 5.6 新特性),服务器层将不能直接使用索引的查询条件推送给存储引擎,从而避免在服务器层进行过滤。
distinct: 优化distinct操作,查询到匹配的数据后停止继续搜索
-------MySQL查看索引(SHOW INDEX)
SHOW INDEX FROM <表名> [ FROM <数据库名>]
例:show index from employee
Table 表示创建索引的数据表名,这里是 tb_stu_info2 数据表。
Non_unique 表示该索引是否是唯一索引。若不是唯一索引,则该列的值为 1;若是唯一索引,则该列的值为 0。
Key_name 表示索引的名称。
Seq_in_index 表示该列在索引中的位置,如果索引是单列的,则该列的值为 1;如果索引是组合索引,则该列的值为每列在索引定义中的顺序。
Column_name 表示定义索引的列字段。
Collation 表示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类。
Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大。
Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引,则该列的值为被编入索引的字符的数目;若整列被编入索引,则该列的值为 NULL。
Packed 指示关键字如何被压缩。若没有被压缩,值为 NULL。
Null 用于显示索引列中是否包含 NULL。若列含有 NULL,该列的值为 YES。若没有,则该列的值为 NO。
Index_type 显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE)。
Comment 显示评注。