MySQL基础常见问题集锦

2021/11/23 2:10:40

编程Tag： mysql class 索引 id 常见问题 11 主键 Student 集锦

本文主要是介绍MySQL基础常见问题集锦，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL基础常见问题集锦

sql设置主键中一般规则?
1. 不更新主键列的值 ( 要设置主键的列不可被更新)
2. 不重用主键列的值 (主键值必须在整个主键列中唯一)
3. 不在主键列中使用可能会更改的值 (主键列不可与其它列存在绑定更新关系)

不能部分使用 distinct 关键字 ?

distinct关键字应用于所有列而不仅是前置它的列.

(distinct关键字区分粒度为的查询结果的行 , 如果一行数据显示不同则显示 , 相同则只显示一行数据)

mysql> SELECT DISTINCT student_id ,student_name,student_birthday FROM student;
+------------+--------------+------------------+
| student_id | student_name | student_birthday |
+------------+--------------+------------------+
|          1 | java         | 2021-11-01       |
|          2 | python       | 2021-11-01       |
|          3 | c            | 2021-11-01       |
|          4 | java         | 2021-11-02       |
|          5 | java         | 2021-11-02       |
+------------+--------------+------------------+
5 rows in set (0.00 sec)

mysql> SELECT DISTINCT student_name,student_birthday FROM student;
+--------------+------------------+
| student_name | student_birthday |
+--------------+------------------+
| java         | 2021-11-01       |
| python       | 2021-11-01       |
| c            | 2021-11-01       |
| java         | 2021-11-02       |
+--------------+------------------+
4 rows in set (0.00 sec)

limit 1,1 实际表示?
1. limit 1,1 表示从第 2 行开始,显示 1 行 , limit 检索第一行为行 0 .
2. Mysql5.0以后 limit 4 offset 3 表示 : 表示从第 3 行开始,显示 4 行 .
exist 和 not exist 效率高于 in 和 not in
like '1000' 和 regexp '1000'
1. like '1000' 匹配时匹配整列 , 即使列中包含 1000 也不会返回结果
2. regexp '1000' 匹配时匹配整列 , 列中包含 1000 即返回结果
多数DBMS拼接使用 + 或 || , MySQL使用Concat(A,B,C...) 函数实现
DATEDIFF(NOW(),'1995-12-01') 函数可计算指定日期与现在相差天数
STR_TO_DATE('1995,12,01','%Y,%m,%d') 函数可将指定字符串转成日期格式
count( 1 )表示在查询结果的基础上为每行新增一列 1 的填充列 , 计算增加1的个数达到统计效果
group by 使用时除聚合计算以外, select语句中每个列都必须在group by子句中给出
where 用于分组后组外过滤 , having用于分组时组内过滤
union 用于组合相同的返回列, 并去除组合后重复值 , union all 组合后不去除重复值

MyISAM引擎支持全文搜索(仅作了解)

代码

-- 建表
CREATE TABLE class(
    class_id INT,
    class_name VARCHAR(20),
    PRIMARY KEY (class_id),
    FULLTEXT(class_name)	-- 指定全文搜索需要建立的索引列
)ENGINE=MYISAM;	

--使用全文搜索
SELECT class_name FROM class WHERE MATCH(class_name) AGAINST('p1');

insert into中间可添加关键字LOW_PRIORITY以降低插入数据优先级, 保证select优先级
insert select 中语句不要求返回列名一致 , 但要求返回查询结果返回顺序与插入一致
update语句更新多行时, 如果一行出现错误可导致整体更新失败 , update ignore tablename 中ignore可跳过失败继续更新

alter table 的常用用法

给表添加一列

ALTER TABLE student ADD remark VARCHAR(20);

给表修改列名

ALTER TABLE student CHANGE remark intro varchar(30);

删除列
```
ALTER TABLE student DROP intro;
```

修改列类型

ALTER TABLE student MODIFY remark VARCHAR(100);

SQL客户端写存储过程时, 需要用 DELIMITER 命令设置结束符

DELIMITER // --设置操作符
CREATE PROCEDURE query_student_info_arg(
	OUT student_id INT,
	OUT class_id INT,
	OUT school_id INT
)
BEGIN 
	SELECT MAX(student.`student_id`) INTO student_id FROM student ;
	SELECT MAX(class.`class_id`) INTO class_id FROM class ;
	SELECT MAX(school.`school_id`) INTO school_id FROM school ;
END//
DELIMITER ; -- 在将操作符设置回 ;

使用EXPLAIN命令查看执行计划时 , type 列执行效率依次从最优到最差分别为:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

使用EXPLAIN命令查看执行计划时 , Extra列重要值及优化方案分别为:

distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了
Using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。
Using where：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

阿里编码规约中关于MySQL数据库部分

建表规约

【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint
（ 1 表示是， 0 表示否）。
【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只
出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
【强制】表名不使用复数名词。
【强制】禁用保留字，如 desc、 range、 match、 delayed 等，请参考 MySQL 官方保留字。
【强制】主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。
【强制】小数类型为 decimal，禁止使用 float 和 double。
【强制】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
【强制】 varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度
大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效
率。
【强制】表必备三字段： id, gmt_create, gmt_modified。
【推荐】表的命名最好是遵循“业务名称_表的作用” 。
【推荐】库名与应用名称尽量一致。
【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
1）不是频繁修改的字段。
2）不是唯一索引的字段。
3）不是 varchar 超长字段，更不能是 text 字段。
【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索
速度。

索引规约

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，
保证被关联的字段需要有索引
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据
实际文本区分度决定索引长度。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
【推荐】如果有 order by 的场景，请注意利用索引的有序性。 order by 最后的字段是组合索
引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
【推荐】利用覆盖索引来进行查询操作，避免回表。
【推荐】利用延迟关联或者子查询优化超多分页场景。
【推荐】 SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。
【推荐】建组合索引的时候，区分度最高的在最左边。
【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。

文章部分笔记来源 : 《MySQL必知必会》by Ben Forta 、《Java开发手册(泰山版)》by Alibaba

文章博客其他地址 :
[STR_TO_DATE 函数转换日期格式说明]: https://www.cnblogs.com/feiwenstyle/p/9531571.html "STR_TO_DATE 函数转换日期格式说明"
[mysql explain命令说明及优化方案]: https://cloud.tencent.com/developer/article/1093229 "mysql explain详解"

这篇关于MySQL基础常见问题集锦的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySQL基础常见问题集锦

MySQL基础常见问题集锦

阿里编码规约中关于MySQL数据库部分

建表规约

索引规约

相关编程文章