GROUP BY 语法

SELECT statements... GROUP BY column_name1[,column_name2,...] [HAVING condition];

实例

mysql> SELECT gender FROM members;+--------+| gender |+--------+| Female || Female || Male   || Female || Male   || Male   || Male   || Male   || Male   || Male   || Male   || Male   || Male   || Male   |+--------+14 rows in set (0.00 sec)mysql> SELECT gender FROM members GROUP BY gender;+--------+| gender |+--------+| Female || Male   |+--------+2 rows in set (0.00 sec)mysql> SELECT category_id,year_released FROM movies GROUP BY category_id,year_released;+-------------+---------------+| category_id | year_released |+-------------+---------------+|        NULL |          2008 ||        NULL |          2010 ||        NULL |          2012 ||           1 |          2011 ||           2 |          2008 ||           6 |          2007 ||           7 |          1920 ||           8 |          1920 ||           8 |          2005 ||           8 |          2007 |+-------------+---------------+10 rows in set (0.00 sec)mysql> SELECT gender,COUNT(membership_number)  FROM members GROUP BY gender;+--------+--------------------------+| gender | COUNT(membership_number) |+--------+--------------------------+| Female |                        3 || Male   |                       11 |+--------+--------------------------+2 rows in set (0.00 sec)mysql> SELECT * FROM movies GROUP BY category_id,year_released HAVING category_id = 8;ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myflixdb.movies.movie_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_bymysql> SELECT category_id,year_released FROM movies GROUP BY category_id,year_released HAVING category_id = 8;+-------------+---------------+| category_id | year_released |+-------------+---------------+|           8 |          1920 ||           8 |          2005 ||           8 |          2007 |+-------------+---------------+3 rows in set (0.00 sec)

转义与模糊匹配

• 语法

SELECT statements... WHERE fieldname LIKE 'xxx%';

• 实例

mysql> SELECT * FROM movies WHERE title LIKE '%code%';+----------+-----------------+------------+---------------+-------------+| movie_id | title           | director   | year_released | category_id |+----------+-----------------+------------+---------------+-------------+|        4 | Code Name Black | Edgar Jimz |          2010 |        NULL ||        7 | Davinci Code    | NULL       |          2007 |           6 |+----------+-----------------+------------+---------------+-------------+2 rows in set (0.00 sec)mysql> SELECT * FROM movies WHERE title LIKE '%code';+----------+--------------+----------+---------------+-------------+| movie_id | title        | director | year_released | category_id |+----------+--------------+----------+---------------+-------------+|        7 | Davinci Code | NULL     |          2007 |           6 |+----------+--------------+----------+---------------+-------------+1 row in set (0.00 sec)mysql> SELECT * FROM movies WHERE title LIKE 'code%';+----------+-----------------+------------+---------------+-------------+| movie_id | title           | director   | year_released | category_id |+----------+-----------------+------------+---------------+-------------+|        4 | Code Name Black | Edgar Jimz |          2010 |        NULL |+----------+-----------------+------------+---------------+-------------+1 row in set (0.02 sec)mysql> SELECT * FROM movies WHERE year_released LIKE '200_';+----------+--------------------------+------------------+---------------+-------------+| movie_id | title                    | director         | year_released | category_id |+----------+--------------------------+------------------+---------------+-------------+|        2 | Forgetting Sarah Marshal | Nicholas Stoller |          2008 |           2 ||        3 | X-Men                    | NULL             |          2008 |        NULL ||        5 | Daddy's Little Girls     | NULL             |          2007 |           8 ||        6 | Angels and Demons        | NULL             |          2007 |           6 ||        7 | Davinci Code             | NULL             |          2007 |           6 ||        9 | Honey mooners            | John Schultz     |          2005 |           8 |+----------+--------------------------+------------------+---------------+-------------+6 rows in set (0.00 sec)mysql> SELECT * FROM movies WHERE title LIKE 'cod_';Empty set (0.00 sec)mysql> SELECT * FROM movies WHERE title LIKE 'cod_%';+----------+-----------------+------------+---------------+-------------+| movie_id | title           | director   | year_released | category_id |+----------+-----------------+------------+---------------+-------------+|        4 | Code Name Black | Edgar Jimz |          2010 |        NULL |+----------+-----------------+------------+---------------+-------------+1 row in set (0.00 sec)mysql> SELECT * FROM movies WHERE year_released NOT LIKE '200_';+----------+---------------------------+----------------+---------------+-------------+| movie_id | title                     | director       | year_released | category_id |+----------+---------------------------+----------------+---------------+-------------+|        1 | Pirates of the Caribean 4 |  Rob Marshall  |          2011 |           1 ||        4 | Code Name Black           | Edgar Jimz     |          2010 |        NULL ||       16 | 67% Guilty                | NULL           |          2012 |        NULL ||       17 | The Great Dictator        | Chalie Chaplie |          1920 |           7 ||       19 | movie 3                   | John Brown     |          1920 |           8 ||       22 | movie 3                   | John Brown     |          1920 |           8 ||       23 | movie 3                   | John Brown     |          1920 |           8 |+----------+---------------------------+----------------+---------------+-------------+7 rows in set (0.01 sec)mysql> SELECT * FROM movies WHERE title LIKE '67#%%' ESCAPE '#';+----------+------------+----------+---------------+-------------+| movie_id | title      | director | year_released | category_id |+----------+------------+----------+---------------+-------------+|       16 | 67% Guilty | NULL     |          2012 |        NULL |+----------+------------+----------+---------------+-------------+1 row in set (0.00 sec)mysql> SELECT * FROM movies WHERE title LIKE '67=%%' ESCAPE '=';+----------+------------+----------+---------------+-------------+| movie_id | title      | director | year_released | category_id |+----------+------------+----------+---------------+-------------+|       16 | 67% Guilty | NULL     |          2012 |        NULL |+----------+------------+----------+---------------+-------------+1 row in set (0.00 sec)

正则表达式

• 语法

SELECT * FROM `movies` WHERE `title` REGEXP 'code';

• 实例

mysql> SELECT * FROM movies WHERE title REGEXP '^[abcd]';+----------+----------------------+------------+---------------+-------------+| movie_id | title                | director   | year_released | category_id |+----------+----------------------+------------+---------------+-------------+|        4 | Code Name Black      | Edgar Jimz |          2010 |        NULL ||        5 | Daddy's Little Girls | NULL       |          2007 |           8 ||        6 | Angels and Demons    | NULL       |          2007 |           6 ||        7 | Davinci Code         | NULL       |          2007 |           6 |+----------+----------------------+------------+---------------+-------------+4 rows in set (0.00 sec)mysql> SELECT * FROM movies WHERE title REGEXP '^[^abcd]';+----------+---------------------------+------------------+---------------+-------------+| movie_id | title                     | director         | year_released | category_id |+----------+---------------------------+------------------+---------------+-------------+|        1 | Pirates of the Caribean 4 |  Rob Marshall    |          2011 |           1 ||        2 | Forgetting Sarah Marshal  | Nicholas Stoller |          2008 |           2 ||        3 | X-Men                     | NULL             |          2008 |        NULL ||        9 | Honey mooners             | John Schultz     |          2005 |           8 ||       16 | 67% Guilty                | NULL             |          2012 |        NULL ||       17 | The Great Dictator        | Chalie Chaplie   |          1920 |           7 ||       19 | movie 3                   | John Brown       |          1920 |           8 ||       22 | movie 3                   | John Brown       |          1920 |           8 ||       23 | movie 3                   | John Brown       |          1920 |           8 |+----------+---------------------------+------------------+---------------+-------------+9 rows in set (0.00 sec)

原著是一个有趣的人,若有侵权,请通知删除