MySql常用语句及优化

感觉自己总是忘，还是做个笔记吧，(～￣▽￣)～

常见的优化

1，尽量不要使用*，会导致全表扫描，不用索引。
例如select * ;
在统计记录总数的时候，count(主键) 比count(*) 消耗的时间更长，测试了108条数据，count(主键) 0.031是cout(*)时间 0.016的两倍。
但是看了这篇：https://www.cnblogs.com/alexguoyihao/p/9100979.html
一百万条 count(id) 时间要短 0.028ms 感觉两者差不多，都可以吧

2，在经常需要进行查询的字段上添加索引，例如where后面的字段

比如在登录的时候
select * from t_user where username='flower' and password='flower';
 可以创建索引create index name_pass on t_user(username,password);
 速度真的会快很多，数据越多越明显。
 
3，分页limit优化
select * from t_book limit 10,4;
使用覆盖索引进行优化using(index_coulumn)
select * from t_book inner join (select id from t_book limit 10,4) as t2 using(id);
可能我数据太少了不够明显吧

4,索引是最左匹配的，尽量按从左到右的顺序指定搜索的字段

5，多用join少用子查询

-- 分页查询的limit begin,end;
(begin,end]是左开右闭的。
select id,name from t_book limit 0,5;
-- 查询出来的数据是从第一条到第五条

-- 添加列
alter table t_type add column parent_id INT(11) DEFAULT NULL COMMENT '参考主键id';
-- 添加外键关系
alter table t_type add constraint id_parent foreign key(parent_id) REFERENCES t_type(id) on delete cascade;
-- 修改字段名
alter table t_blog change  blogSchema blogschema varchar(500);
-- 重命名表
ALTER TABLE tbl1 RENAME tbl2;
-- 删去字段
ALTER TABLE tbl DROP COLUMN filed1, DROP COLUMN field2;
-- 删除列c：
ALTER TABLE t2 DROP COLUMN c;
-- 重命名列名
ALTER TABLE tbl MODIFY field1 BIGINT NOT NULL;

-- 在列d和列a中添加索引
ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);

-- 使用explain查看sql语句的执行情况
explain select * from t_book inner join (select id from t_book limit 10,4) as t2 using(id);

-- 左连接
select sname,age,name tname from t_student left join t_teacher on t_student.tid=t_teacher.id;
-- 内连接
select t.name from t_type as t inner join t_booktype as b on t.id =b.typeid  where b.bookid=2;

集函数
having是分组（group by）后的筛选条件，分组后的数据组内再筛选
where则是在分组前筛选
SELECT region, SUM(population), SUM(area) FROM bbc
GROUP BY region HAVING SUM(area)>1000000 

round (avg(列名)，2)返回组中的平均值，可以选择保留一位或者两位。
过滤掉最低分小于60的学生，并平均分保留两位数值
select snum,sum(score)  ,round (avg(score)，2),max(score) from Tbl_Grade
Group by snum having min(score)>=60;

按条件分组并统计组内的数量
这里有个高级查询的例子
需求：查询购物车中商品的种数

mysql> select bid,num,status,update_time,create_time from t_shopcar where uid=1;
+------+------+--------+---------------------+---------------------+
| bid  | num  | status | update_time         | create_time         |
+------+------+--------+---------------------+---------------------+
|    1 |    1 |      1 | 0000-00-00 00:00:00 | 2019-02-11 16:35:02 |
|    2 |    1 |      1 | 0000-00-00 00:00:00 | 2019-02-11 16:35:02 |
|    1 |    1 |      1 | 0000-00-00 00:00:00 | 2019-02-12 10:03:27 |
+------+------+--------+---------------------+---------------------+
3 rows in set (0.01 sec)

嵌套查询

mysql> select T.bid,count(T.num) as bid_count,T.status,T.update_time,T.create_time from (select bid,num,status,update_time,create_time from t_shopcar where uid=1) as T group by bid order by update_time;
+------+-----------+--------+---------------------+---------------------+
| bid  | bid_count | status | update_time         | create_time         |
+------+-----------+--------+---------------------+---------------------+
|    1 |         2 |      1 | 0000-00-00 00:00:00 | 2019-02-11 16:35:02 |
|    2 |         1 |      1 | 0000-00-00 00:00:00 | 2019-02-11 16:35:02 |
+------+-----------+--------+---------------------+---------------------+
2 rows in set (0.01 sec)