MySQL query and update examples

I,
Create a table:
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
Insert the following data:
1 blue
2 red
3 blue
4 red
5 blue
Now we need to use an SQL statement to calculate the number of all red and blue colors,
It can be as follows:
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
Both of the above can be used.
Generally, companies use databases as storage media rather than logic operations. In this example, we split the initial data multiple times. In fact, databases can retrieve this type of data at a time.
II,
Use a query statement in an update statement
Create a table as follows:
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
Enter the following data:
1 83
3 4
5 45
6 32
7
8
9 83
10 4
Now, if you want to set the counter column with ID 1 to the sum of all counters. For other database products, you can:
Update Counter C set counter = (select sum (Counter) from Counter)
However, it should be noted that MySQL does not support the SELECT statement in the update statement [for operations on the same table ].
Therefore, you can use a connection in MySQL:
Update Counter C inner join (select sum (Counter) as Count _ from Counter) as t set counter = T. Count _ Where C. ID = 1
III,
Number of query IDs greater than 5
If you want to query tables with IDs greater than 5, you can write an SQL statement:
Select count (*) from table where ID> 5
But what is the problem with this writing.
In the InnoDB table engine, this must scan the entire table. [2012-12-17 --- I don't know if my head was cute at the time? How can I write such a silly error. InnoDB does not scan the entire table as long as there is an index]
In the MyISAM Table engine, you can see that this SQL statement accesses a lot of data by using explain. Instead of the Data smaller than 5. The optimization is as follows:
Explain select (select count (*) from Counter)-count (*) from counter where ID <= 5
Again, you can see that select tables optimized away, this SQL uses MyISAM to store the strengths of the number of table data.