mysql心得笔记_MySQL开发心得笔记超详细及SQL语法考核

最新推荐文章于 2022-04-06 21:27:30 发布

weixin_31096417

最新推荐文章于 2022-04-06 21:27:30 发布

阅读量101

点赞数

文章标签： mysql心得笔记

本文链接：https://blog.csdn.net/weixin_31096417/article/details/113950852

版权

五十五、select执行的顺序

select...from...

join ...

on ...where...

group by ..

having ...

order by ...

1、先从表中取数据，访问innodb buffer pool

from ...

join ...

on ...

where

2、分组、聚合，数据已经进入用户工作空间

group by ...

having ...

3、select ....：取列数据

4、order by：排序输出

五十六、集合操作

union：结果集去重

union all：结果集不去重

五十七、insert增

1、insert values一条数据

表的名字后面最好加上列的名字

2、insert values多条数据

3、insert into select

select可以非常复杂，语法完全就是select

五十八、update改

基本格式：update 一个表set 列where 列条件;

1、一定要带上where条件

2、update分为下面的几个步骤操作

①找到需要update的数据，此操作取决于where条件

where条件可以是一个复杂的where条件，比如是一个子查询

示例：将平均成绩75分以上的学生的级别设置为优等生

update 学生信息表 aset grade=‘优等生’where a.stuno in (select b.stuno from 成绩表 b group by b.stuno having avg(成绩)>=75);

②set后面的列，也可以很复杂，比如是一个相对子查询

UPDATE players_data pd

SET number_mat=(

SELECT count(*)

FROM matches m

WHERE m.playerno=pd.playerno),

sum_penalties=(

SELECT sum(amount)

FROM penalties pen

WHERE pen.playerno= pd.playerno);

3、update可以改写成一个select语句

把1和2改写成一个select语句，不要对一个update在生产里面直接进行优化

4、update可以使用order by，数据按照顺序进行更新

5、update可以使用limit，限制每次更新的行数

五十九、replace替代已有的行

使用场合insert+update，两个表数据合并到一起

六十、delete删

1、绝大多数情况下需要加上where条件

2、where条件可以很复杂，例如是一个子查询

3、理解delete和truncate的区别

truncate：清空全部数据、速度快、释放空间(不删表)

delete：全部或者部分删除数据、速度慢、不释放空间

六十一、临时表

1、只是针对当前会话有效，临时表和数据都存储在用户工作空间

2、临时表的使用很消耗资源

①create、insert、drop，因此在非常频繁的查询环境下，不宜使用临时表；

②临时表需要使用用户工作空间，临时表中存在的数据不易过多，否则容易出现磁盘临时表；

3、临时表的使用场合

需要暂存结果集数据，后面的操作需要访问这些暂存结果集，主要是为了可读性。

4、有一种误区一定要注意，一定不要将普通表作为临时表来使用

原因：普通表当做临时表来使用，下面的操作需要手工去做

①create、insert、truncate或者drop

②对于普通表的所有操作都会产生redo(事务)，非常消耗资源

六十二、关于约束

1、非空

2、default约束

3、主键约束

4、外键约束

5、SET、ENUM约束

约束注意点：

①尽量选择列都为非空

②对于bool、时间列经常会出现default约束

③每一个表尽最大程度要有主键

④唯一键可以有多个，唯一键可以有空值

⑤外键列一般会有，但是外键约束不建议使用，在应用层面保证主表和外表的一致性

⑥合理使用set和enum约束，提升数据的质量

⑦外键约束中on delete、update，尽量不要设置级联删除操作(很危险！！！)

六十三、表的DDL

1、极其严肃的一个动作

2、使用help书写DDL语句

3、ddl动作的后遗症和危险性

①影响I、D、U、S

②长时间锁表、产生海量IO

4、测试DDL的影响范围---优化对象

①锁表时间

②IO情况

③具体测试要求

示例：产生一个500万行的表(写一个存储过程实现)，对表进行增加列、删除列、修改列的名字、将列的长度变长、将列的长度变短

MySQL>delimiter $$

mysql> create procedure do_big(x int)->begin-> 　　declare v int;-> 　　set v=x;-> 　　create table test(test_num int auto_increment not nullprimary key);-> 　　while v>0 do

-> 　　　　insert into test values(null);-> 　　　　set v=v-1;-> 　　end while;->end $$

mysql>delimiter ;

mysql> call do_big(5000000);

……

mysql> select count(*) fromtest;+----------+

| count(*) |

+----------+

| 5000000 |

+----------+

看一下上面的这些操作，哪些操作时间长、哪些操作时间短，并对其进行初步的原理分析

mysql> insert into test values(123456789);mysql> delete from test where test_num=123;mysql>alter table test CHANGE COLUMN->test_num-> test_id int(10) not nullauto_increment;mysql> alter table test modify test_id int(100);mysql> alter table test modify test_id int(20);

总结：对于一个大表而言，将列的长度变长时间是最长的，其他的操作处理时间都还挺短。

六十四、视图的最佳实践

1、视图就是select的一个名字

2、不建议使用复杂视图

select语句里面不要带有distinct、group by、聚合函数、union等操作

3、不建议在视图中嵌套视图

4、视图的主要使用场合

统一访问接口(select)---主要的好处

规范访问

隐藏底层表结构、ddl不影响应用访问

5、视图在安全方面的意义

六十五、存储过程(脚本)