MySql几个查询更新例子

布满荆棘的人生 2022-06-11 06:09 184阅读 0赞

一、  
创建一个表：  
CREATE TABLE \`flower\` (  
  \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
  \`color\` varchar(255) DEFAULT NULL,  
  PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=6 DEFAULT CHARSET=latin1  
插入如下数据：  
1    blue  
2    red  
3    blue  
4    red  
5    blue  
那么现在要求使用一个sql语句统计出来所有的red和blue的个数，  
可以如下：  
select  
sum( case when color='red' then 1 else 0 end)as 'red',  
sum( case when color='blue' then 1 else 0 end)as 'blue'  
from flower  
select count(1) as cnt,color from flower group by color  
上面两个都可以。  
一般公司都会把数据库作为存储介质而非逻辑运算。在这个例子中分多次起初数据。其实数据库是可以做到一次取出这类数据的。  
二、  
在一个更新语句中使用查询语句  
创建表如下：  
CREATE TABLE \`counter\` (  
  \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
  \`counter\` int(11) DEFAULT NULL,  
  PRIMARY KEY (\`id\`)  
) ENGINE=MyISAM AUTO\_INCREMENT=1018 DEFAULT CHARSET=latin1  
录入如下数据：  
1    83  
3    4  
5    45  
6    32  
7      
8      
9    83  
10    4  
现在如果想把id为1的counter列设置为所有的counter之和。那么，对于其他数据库产品，可以如下：  
update counter c set counter=(select sum(counter) from counter)  
但是，要注意的是，mysql根本就不支持在update语句中使用select语句【对于同一个表操作的情况】。  
那么，在mysql下可以使用一个连接，这么写：  
update counter c inner join (select sum(counter)  as count\_ from counter )as t  set counter= t.count\_ where c.id=1  
三、  
查询id大于5的个数  
有一个表，id自增长，想查询id大于5的，那么很简单的可以写出来一个sql：  
select count(\*) from table where id > 5  
但是，这么写有什么问题呢。  
在innodb表引擎中，这是必定要扫描全表的。【勘误2012-12-17--\-这里不知道当时脑袋秀逗了？怎么会写出这么傻x的错误。innodb只要有索引就不会全表扫描】  
在myisam表引擎中，使用explain可以看到，这个sql语句访问了很多数据。而非仅仅小于5的那几个数据。可以优化如下：  
explain select (select count(\*) from counter )- count(\*) from counter where id<=5  
再次explain可以看到，Select tables optimized away，这次的sql使用了myisam的存储表数据个数的强项。