MySQL开发技巧

最新推荐文章于 2025-04-30 22:21:22 发布

qq_34515002

最新推荐文章于 2025-04-30 22:21:22 发布

阅读量238

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_34515002/article/details/80734267

MySQL 专栏收录该内容

5 篇文章

订阅专栏

参考：

1、 MySQL开发技巧

2、 MySQL开发技巧2

3、MySQL开发技巧3

常见的SQL语句类型

DDL:数据定义语言

TPL:事务处理语言

DCL:数据控制语言

DML:数据操作语言（CRUD）

如何正确的使用Join从句

内连接	INNER
全外连接(不支持)	FULL OUTER
左外连接	LEFT OUTER
右外连接	RIGHT OUTER
交叉连接(又称笛卡尔积)	CROSS

全外连接(FULL OUTER)实现方式：

SELECT A.*,B.* FROM table A LEFT JOIN table B ON B.Key = A.Key

UNION ALL

SELECT A.*,B.* FROM table B RIGNT JOIN table A ON A.Key = B.Key

使用JOIN更新表

UPDATE table A

JOIN table B ON A.key = B.key

SET A.column=set_value;

使用JOIN优化子查询

准备表：

CREATE TABLE `users` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(32) NOT NULL,
`age` tinyint(3) unsigned NOT NULL DEFAULT '10',
`sex` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8

CREATE TABLE `province` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`pname` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

第1种子查询：

select A.id,A.username,(select pname from province B WHERE A.username=B.pname) as pname from users A；

show profiles;

第2种外连接查询；

select A.id,A.username,B.pname from users A left join province B on A.username=B.pname;

show profiles;

所以第2种比较好。

使用join优化聚合子查询

如何实现分组选择

查询每人打怪最多的两条记录；

方法1：

WITH tmp AS(

SELECT a.user_name,b.timestr,b.kills, ROW_NUMBER() over(partition by a.user_name order by b.kills) cnt

FROM user1 a

JOIN user_kills b ON a.id = b.user_id

)select * from tmp where cnt <=2;

方法2：

SELECT d.user_name,c.timestr,kills

FROM(

SELECT user_id,timestr,kills,( SELECT COUNT(*) FROM user_kills b WHERE b.user_id = a.user_id AND a.kills <=b.kills) AS cnt

FROM user kills a

GROUP BY user_id,timestr,kills

JOIN user1 d ON c.user_id = d.id

WHERE cnt <=2;

查看mysql语句运行时间

1. Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。

2.确定支持show profile 后，查看profile是否开启，数据库默认是不开启的。变量profiling是用户变量，每次都得重新启用。

查看方法： show variables like "%pro%";

设置开启方法： set profiling = 1;

再次查看show variables like "%pro%"; 已经是开启的状态了。

3.可以开始执行一些想要分析的sql语句了，执行完后，show profiles; 即可查看所有sql的总的执行时间。

show profile for query 1 即可查看第1个sql语句的执行的各个操作的耗时详情。

show profile cpu, block io, memory,swaps,context switches,source for query 6;可以查看出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等

show profile all for query 6 查看第6条语句的所有的执行信息。

测试完毕后，关闭参数：

mysql> set profiling=0

方法二： timestampdiff来查看执行时间。

这种方法有一点要注意，就是三条sql语句要尽量连一起执行，不然误差太大，根本不准

set @d=now();
select * from comment;
select timestampdiff(second,@d,now());

如果是用命令行来执行的话，有一点要注意，就是在select timestampdiff(second,@d,now());后面，一定要多copy一个空行，不然最后一个sql要你自己按回车执行，这样就不准了。

行转列

场景：

1、报表统计

2、汇总显示

set names 'gbk';

mysql> select a.user_name,sum(kills)
    -> from user1 a
    -> join user_kills b on a.id =b.user_id
    -> group by a.user_name;
+-----------+------------+
| user_name | sum(kills) |
+-----------+------------+
| 孙悟空          |         47 |
| 沙僧        |          9 |
| 猪八戒         |         24 |
+-----------+------------+
3 rows in set (0.01 sec)

--方法1：
select * 
from(
  select  sum(kills) as '沙僧' from user1 a join user_kills b on a.id =b.user_id and a.user_name='沙僧'
  ) a
cross join(
  select  sum(kills) as '猪八戒' from user1 a join user_kills b on a.id =b.user_id and a.user_name='猪八戒'
  ) b
cross join(
  select  sum(kills) as '孙悟空' from user1 a join user_kills b on a.id =b.user_id and a.user_name='孙悟空'
  ) c 
;
--方法2：
select sum(case when user_name='孙悟空' then kills end) as '孙悟空'
,sum(case when user_name='猪八戒' then kills end) as '猪八戒'
,sum(case when user_name='沙僧' then kills end) as '沙僧'
from user1 a
join user_kills b on a.id = b.user_id
;

mysql> select *
    -> from(
    ->   select  sum(kills) as '沙僧' from user1 a join user_kills b on a.id =b.user_id and a.user_n
ame='沙僧'
    ->   ) a
    -> cross join(
    ->   select  sum(kills) as '猪八戒' from user1 a join user_kills b on a.id =b.user_id and a.user
_name='猪八戒'
    ->   ) b
    -> cross join(
    ->   select  sum(kills) as '孙悟空' from user1 a join user_kills b on a.id =b.user_id and a.user
_name='孙悟空'
    ->   ) c
    -> ;
+------+--------+--------+
| 沙僧   | 猪八戒      | 孙悟空       |
+------+--------+--------+
|    9 |     24 |     47 |
+------+--------+--------+
1 row in set (0.01 sec) 


mysql> select sum(case when user_name='孙悟空' then kills end) as '孙悟空'
    -> ,sum(case when user_name='猪八戒' then kills end) as '猪八戒'
    -> ,sum(case when user_name='沙僧' then kills end) as '沙僧'
    -> from user1 a
    -> join user_kills b on a.id = b.user_id
    -> ;
+--------+--------+------+
| 孙悟空       | 猪八戒      | 沙僧   |
+--------+--------+------+
|     47 |     24 |    9 |
+--------+--------+------+
1 row in set (0.00 sec)