mysql的sql语句基本操作

@[TOC]

mysql基本的增删改查（select,update,delete,insert）

mysql的左连接，右连接，内连接，全连接的区别(left join,right join,ininer join)

mysql的聚合函数(max,min,avg,sum)

mysql的分页及group by

mysql的子查询

一条sql的执行顺序是什么

sql优化

定位慢查询

 

联合查询效率较高，举例子来说明联合查询：内联inner join 、左联left outer join 、右联right outer join 、全联full outer join 的好处及用法。

　　联合查询效率较高，以下例子来说明联合查询(内联、左联、右联、全联)的好处：

T1表结构 （用户名,密码）	userid （int）	username varchar（20）	password  varchar（20）
	1	jack	jackpwd
	2	owen	owenpwd
T2表结构 （用户名,密码）	userid （int）	jifen varchar（20）	dengji  varchar（20）
	1	20	3
	3	50	6

　　第一：内联(inner join)。

　　如果想把用户信息、积分、等级都列出来，那么一般会这样写：select * from T1 ,T3 where T1.userid = T3.userid(其实这样的结果等同于select * from T1 inner join T3 on T1.userid=T3.userid )。

　　把两个表中都存在userid的行拼成一行(即内联)，但后者的效率会比前者高很多，建议用后者(内联)的写法。

　　SQL语句：select * from T1 inner join T2 on T1.userid=T2.userid

运行结果	T1.userid	username	password	T2.userid	jifen	dengji
	1	jack	jackpwd	1	20	3

　　第二：左联(left outer join)。

　　显示左表T1中的所有行，并把右表T2中符合条件加到左表T1中;右表T2中不符合条件，就不用加入结果表中，并且NULL表示。

　　SQL语句：select * from T1 left outer join T2 on T1.userid=T2.userid

运行结果	T1.userid	username	password	T2.userid	jifen	dengji
	1	jack	jackpwd	1	20	3
	2	owen	owenpwd	NULL	NULL	NULL

　　第三：右联(right outer join)。

　　显示右表T2中的所有行，并把左表T1中符合条件加到右表T2中;左表T1中不符合条件，就不用加入结果表中，并且NULL表示。

　　SQL语句：select * from T1 right outer join T2 on T1.userid=T2.userid

运行结果	T1.userid	username	password	T2.userid	jifen	dengji
	1	jack	jackpwd	1	20	3
	NULL	NULL	NULL	3	50	6

　　第四：全联(full outer join)。

　　显示左表T1、右表T2两边中的所有行，即把左联结果表+右联结果表组合在一起，然后过滤掉重复的。

　　SQL语句：select * from T1 full outer join T2 on T1.userid=T2.userid

运行结果	T1.userid	username	password	T2.userid	jifen	dengji
	1	jack	jackpwd	1	20	3
	2	owen	owenpwd	NULL	NULL	NULL
	NULL	NULL	NULL	3	50	6

　　总结，关于联合查询，效率的确比较高，4种联合方式如果可以灵活使用，基本上复杂的语句结构也会简单起来。这4种方式是：1)Inner join 2)left outer join 3)right outer join 4)full outer join

①常用的聚合函数
count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值

②求总行数
–返回非NULL数据的总行数
select count(height) from students;
–返回总行数,包含null值记录
select count(*) from students;

③求最大值
–查询女生的编号最大值
select max(id) from students where gender = 2;

④求最小值
–查询未删除的学生最小编号
select min(id) from students where is_delete = 0;

⑤求和
–查询男生的总身高
select sum(height) from students where gender = 1;
–平均身高
select sum(height) / count(*) from students where gender = 1;

⑤求平均值
– 求男生的平均身高, 聚合函数不统计null值，平均身高有误
select avg(height) from students where gender = 1;
– 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;
说明:ifnull函数:表示判断指定字段的值是否为null,如果为空使用自己提供的值
-----聚合函数的特点
聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算,必须使用ifnull函数对null值做替换

--------小结-------------
count(col):表示求指定列的总行数
max(col):表示求指定列的最大值
min(col):表示求指定列的最小值
sum(col):表示求指定列的和
avg(col):表示求指定列的平均值
————————————————
limit分页：

select * from  t  (pageNumber-1)*pageSize,总之，前端只需要告诉我们需要第几页，每页显示多少条数据就行。 

例如：select * from t  limit 0,10,即表示：查询第一页的数据，每页显示10条

group by（具有去重功能）：

给张这样的表：

用这样的sql查：

SELECT name FROM test GROUP BY name

结果展示是这样：

子查询：

IN子查询

 比较运算符子查询

EXIST子查询

from型子查询(把内层的查询结果当成临时表，供外层sql再次查询。查询结果集可以当成表看待。临时表要使用一个别名。)

 

where型子查询（把内层查询结果当作外层查询的比较条件）

 sql优化：

1.使用子查询优化

select * from orders_history where type=8 limit 100000,1;
select id from orders_history where type=8 limit 100000,1;
 
select * from orders_history where type=8 and 
id>=(select id from orders_history where type=8 limit 100000,1) 
limit 100;
 
select * from orders_history where type=8 limit 100000,100;

 

4条语句的查询时间如下：

第1条语句：3674ms
第2条语句：1315ms
第3条语句：1327ms
第4条语句：3710ms
针对上面的查询需要注意：

比较第1条语句和第2条语句：使用 select id 代替 select * 速度增加了3倍
比较第2条语句和第3条语句：速度相差几十毫秒
比较第3条语句和第4条语句：得益于 select id 速度增加，第3条语句查询速度增加了3倍
这种方式相较于原始一般的查询方法，将会增快数倍。

① SQL语句及索引的优化
SQL语句的优化：
1、尽量避免使用子查询

2、避免函数索引

3、用IN来替换OR

    另外，MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from table_name where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。

4、LIKE前缀%号、双百分号、_下划线查询非索引列或*无法使用到索引，如果查询的是索引列则可以

5、读取适当的记录LIMIT M,N，而不要读多余的记录
select id,name 
from table_name limit 866613, 20
使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：

select id,name from table_name 
where id> 866612 limit 20
 

6、避免数据类型不一致
7、分组统计可以禁止排序sort，总和查询可以禁止排重用union all

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。

另外，如果排序字段没有用到索引，就尽量少排序；

 

8、避免随机取记录

9、禁止不必要的ORDER BY排序

10、批量INSERT插入

11、不要使用NOT等负向查询条件
你可以想象一下，对于一棵B+树，根节点是40，如果你的条件是等于20，就去左面查，你的条件等于50，就去右面查，但是你的条件是不等于66，索引应该咋办？还不是遍历一遍才知道。

 

12、尽量不用select *
SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前者也需要经常更新。所以要求直接在select后面接上字段名。

 

13、区分in和exists
select * from 表A 
where id in (select id from 表B)
上面sql语句相当于

select * from 表A 
where exists(select * from 表B where 表B.id=表A.id)
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

 

14、优化Group By语句
如果对group by语句的结果没有排序要求，要在语句后面加 order by null（group 默认会去排序）；

尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；

如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；

如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by的结果。
 

慢查询定位

一、简介

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

二、参数说明

slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录

三、设置步骤

1.查看慢查询相关参数

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

2.设置方法
方法一：全局变量设置
将 slow_query_log 全局变量设置为“ON”状态

mysql> set global slow_query_log='ON'; 

设置慢查询日志存放的位置

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

查询超过1秒就记录

mysql> set global long_query_time=1;

方法二：配置文件设置
修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

3.重启MySQL服务

service mysqld restart

4.查看设置后的参数

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

四、测试

1.执行一条慢查询SQL语句

mysql> select sleep(2);

2.查看是否生成慢查询日志

ls /usr/local/mysql/data/slow.log

如果日志存在，MySQL开启慢查询设置成功！