like 操作符：
通配符： %可以代表0或多个任意字符
_可以代表1个任意字符
select prod_name from products where prod_name like '%o%'; 名字中包含字母o
select prod_name from products where prod_name like 'o%'; 名字以o开头
select prod_name from products where prod_name like '%o'; 名字以o结尾
名字中包含字符o和n且不知道位置和先后顺序
select prod_name from products where prod_name like '%o%n%' or prod_name like '%n%o%';
select prod_name from products where prod_name like '%o%' AND prod_name like '%n%';
名字的第二个字符为e
select prod_name from products where prod_name like '_e%';
名字的第三个字符为r
select prod_name from products where prod_name like '__r%';
注意NULL： 虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作为产品名的行。
regexp操作符：
select prod_name from products where prod_name regexp 'o'; 名字中包含字母o
select prod_name from products where prod_name regexp 'ing';名字中包含字母ing
通配符： . （点）可以代表1个任意字符
select prod_name from products where prod_name regexp 'a'; 名字中包含字母a
select prod_name from products where prod_name regexp '.a'; 名字中包含'任意至少一个字符+a',即a不能在第一个位置。
select prod_name from products where prod_name regexp '..a'; 名字中包含'至少2个任意字符+a'。
select prod_name from products where prod_name regexp '\.5'; 名字中包含'.5'。 \:表示转义，将特殊字符转化成普通字符
select prod_name from products where prod_name regexp '.5'; 名字中包含'至少一个任意字符+5'。
匹配符 ：
|:表示或者 select prod_name from products where prod_name regexp 'a5|b5';
[ ]:占一位，取值为[ ]内任意字符
select prod_name from products where prod_name regexp '[a|b]5';
select prod_name from products where prod_name regexp '[ab]5';
[0123456789] 、[0|1|2|3|4|5|6|7|8|9]、[0-9]: 表示0-9中一个数字
[a-z]:表示a-z中任意一个小写字母
^ :表示以 开头
select prod_name from products where prod_name regexp '^a';
[^ ]:[ ]的取反，表示不在[ ]范围之内;
$ :表示结尾
select prod_name from products where prod_name regexp 'l$';
select prod_name from products where prod_name regexp '^l$';以l开头l结尾，就含有l一个字符的名字
select prod_name from products where prod_name regexp '^....$';只含有4个字符的产品名字
* : 表示匹配前面字符任意次 0~n（至少0次，至多n次）
+ : 表示匹配前面字符至少一次 1~n（至少1次，至多n次）
? :表示匹配前面字符只能0或1次
{n} :表示匹配前面字符n次
{n, } :表示匹配前面字符至少n次
{n,m}:表示匹配前面字符至少n次至多m次
例如：
dual内置空表
匹配判断——显示1为真 0为假
mysql> select prod_name from products where prod_name regexp '^l$';以l开头l结尾，就含有l一个字符的名字（产品名为l的产品）
Empty set (0.00 sec)
mysql> select prod_name from products where prod_name regexp '^....$';只含有4个字符的产品名字
+-----------+
| prod_name |
+-----------+
| Safe |
+-----------+
1 row in set (0.00 sec)
mysql> select 1 from dual where 'a2.213211' regexp '^[a-z]'; 'a2.213211'中满足以a-z中任意字符开头
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where 'a2.213211' regexp '[a-z]'; 'a2.213211'中不存在以a-z中之外任意字符开头
Empty set (0.00 sec)
mysql> select 1 from dual where '2.213211' regexp '[a-z]'; ''2.213211' '中存在以a-z中之外任意字符开头
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '2' regexp '[0-9]$'; '2'中不存在以0-9之外的字符开头及结尾
Empty set (0.00 sec)
mysql> select 1 from dual where '2' regexp '^[0-9]$';2属于，存在0-9中任意一个字符
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '2222' regexp '^[0-9]*$';
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where ' ' regexp '^[0-9]*$'; 以0-9开头至少0个字符
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '3526234666' regexp '^[0-9]*$';
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '3526234666' regexp '^[0-9]+$'; 以0-9开头至少1个字符
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '6' regexp '^[0-9]+$';
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where ' ' regexp '^[0-9]+$'; 以0-9开头至少1个字符
Empty set (0.00 sec)
mysql> select 1 from dual where ' ' regexp '^[0-9]?$'; 以0-9开头只有0个字符或者1个字符
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '2' regexp '^[0-9]?$'; 以0-9开头只有0个字符或者1个字
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '23' regexp '^[0-9]?$'; 以0-9开头只有0个字符或者1个字符
Empty set (0.00 sec)
mysql> select 1 from dual where '23' regexp '^[0-9]{2}$'; 以0-9开头2个字符
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '23' regexp '^[0-9]{1}$'; 以0-9开头1个字符
Empty set (0.00 sec)
mysql> select 1 from dual where '23' regexp '^[0-9]{3}$'; 以0-9开头3个字符
Empty set (0.00 sec)
mysql> select 1 from dual where '23' regexp '^[0-9]{1,}$'; 以0-9开头至少1个字符
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '23' regexp '^[0-9]{2,}$'; 以0-9开头至少2个字符
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '23' regexp '^[0-9]{3,}$'; 以0-9开头至少3个字符
Empty set (0.00 sec)
mysql> select 1 from dual where '233245' regexp '^[0-9]{3,5}$'; 以0-9开头至少3个字符至多5个字符
Empty set (0.00 sec)
mysql> select 1 from dual where '233245' regexp '^[0-9]{3,6}$'; 以0-9开头至少3个字符至多6个字符
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select 1 from dual where '233245' regexp '^[0-9]{7,8}$'; 以0-9开头至少3个字符至多8个字符
Empty set (0.00 sec)
mysql> select 1 from dual where '233245' regexp '^[0-9]{6,8}$'; 以0-9开头至少6个字符至多8个字符
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
手机号判断（1为真 0为假）
select 1 from dual where '13776042496' regexp '^1[0-9]{10}$'；
邮箱判断（1为真 0为假）
Select 1 from dual where 'weiyan0930@163.com' regexp '^[0-9a-zA-Z]+@[0-9a-zA-Z]{2,4}\.[a-zA-Z]{2,4}$';
取别名： as
select prod_id as 产品编号,prod_name as name ,prod_price 价格 from products; as可以省略
select p.prod_id as 产品编号,p.prod_name ,prod_price 价格 from products as p;
select prod_id as '产品编号',products.prod_name ,prod_price '价格' from products;
网友评论