sql教程6分组、转义与模糊匹配、正则表达式

作者: python测试开发 | 来源:发表于2020-01-17 06:36 被阅读0次

sql教程6分组、转义与模糊匹配、正则表达式
常用的正则表达式
正则表达式学习
5: 正则表达式 + 三剑客之grep
正则表达式_2
2019-05-07python学习记录-搜文本
JavaScript正则表达式——转义字符
正则表达式括号的作用
《javaScript正则表达式迷你书》（一）
Python基础022--正则表达式

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_by
mysql> 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)

sql教程6分组、转义与模糊匹配、正则表达式
GROUP BY 语法实例转义与模糊匹配语法实例正则表达式语法实例
常用的正则表达式
微软-正则表达式语法菜鸟教程-正则特殊字符若要匹配这些特殊字符之一，必须首先转义字符，即，在字符前面加反斜杠字...
正则表达式学习
参考：正则表达式30分钟入门教程元字符字符转义分枝条件分组向后引用零宽断言负向零宽断言贪婪和懒惰 ...
5: 正则表达式 + 三剑客之grep
3 正则表达式正则表达式元字符分类: 字符匹配次数匹配位置锚定分组基本正则表达式: vim, grep,...
正则表达式_2
一、元字符二、反义字符三、转义字符四、重复匹配五、分组/捕获六、贪婪与惰性七、修饰符
2019-05-07python学习记录-搜文本
正则表达式添加括号可以在正则表达式中加分组groups() 复数可以一次获得所有分组 | 用管道匹配多个分组? ...
JavaScript正则表达式——转义字符
1、转义：JavaScript正则表达式也支持非字母的字符匹配，这些字符需要通过反斜杠（\）进行转义总结1、\n：...
正则表达式括号的作用
本文摘抄自javascript正则表达式迷你书正则表达式是匹配模式，要么匹配字符，要么匹配位置 1. 分组和分...
《javaScript正则表达式迷你书》（一）
正则表达式字符匹配攻略正则表达式是匹配模式，要么匹配字符，要么匹配位置。两种模糊匹配如果正则只有精确匹配是没...
Python基础022--正则表达式
正则表达式模块、贪婪匹配和非贪婪匹配、正则分组、match和search的使用常用的正则表达式元字符正则表达式...