《MySQL必知必会》3-9章笔记

~~其实就是把书上的例子给敲了一遍~~

1.连接、选择与显示

①连接MySQL

方式一：在命令提示符中输入


mysql -u root -p

登录root用户并输入密码

方式二：在MySQL文件夹内打开MySQL命令客户端

方式三：用navicat或sqlyog之类的图形化工具连接数据库，连接时输入用户名和密码

②选择数据库

查看一个数据库中的数据，或是对这个数据库进行操作，必须要先对这个数据库进行选择操作


USE DB1;

以上语句选择了名为DB1的数据库

③显示数据库和表的详细信息

使用show命令显示所有可用的数据库


show databases;#用这句来显示所有可用数据库的列表

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

使用show显示指定数据库中所有的表


mysql> use mysql;

Database changed

mysql> show tables;#显示一个数据库中表的列表

+---------------------------+

| Tables_in_mysql          |

+---------------------------+

| columns_priv              |

| component                |

| db                        |

| default_roles            |

| engine_cost              |

| func                      |

| general_log              |

| global_grants            |

| gtid_executed            |

| help_category            |

| help_keyword              |

| help_relation            |

| help_topic                |

| innodb_index_stats        |

| innodb_table_stats        |

| password_history          |

| plugin                    |

| procs_priv                |

| proxies_priv              |

| role_edges                |

| server_cost              |

| servers                  |

| slave_master_info        |

| slave_relay_log_info      |

| slave_worker_info        |

| slow_log                  |

| tables_priv              |

| time_zone                |

| time_zone_leap_second    |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

使用show columns显示出一个表的所有字段的详细信息


mysql> show columns from db;

+-----------------------+---------------+------+-----+---------+-------+

| Field                | Type          | Null | Key | Default | Extra |

+-----------------------+---------------+------+-----+---------+-------+

| Host                  | char(255)    | NO  | PRI |        |      |

| Db                    | char(64)      | NO  | PRI |        |      |

| User                  | char(32)      | NO  | PRI |        |      |

| Select_priv          | enum('N','Y') | NO  |    | N      |      |

| Insert_priv          | enum('N','Y') | NO  |    | N      |      |

| Update_priv          | enum('N','Y') | NO  |    | N      |      |

| Delete_priv          | enum('N','Y') | NO  |    | N      |      |

| Create_priv          | enum('N','Y') | NO  |    | N      |      |

| Drop_priv            | enum('N','Y') | NO  |    | N      |      |

| Grant_priv            | enum('N','Y') | NO  |    | N      |      |

| References_priv      | enum('N','Y') | NO  |    | N      |      |

| Index_priv            | enum('N','Y') | NO  |    | N      |      |

| Alter_priv            | enum('N','Y') | NO  |    | N      |      |

| Create_tmp_table_priv | enum('N','Y') | NO  |    | N      |      |

| Lock_tables_priv      | enum('N','Y') | NO  |    | N      |      |

| Create_view_priv      | enum('N','Y') | NO  |    | N      |      |

| Show_view_priv        | enum('N','Y') | NO  |    | N      |      |

| Create_routine_priv  | enum('N','Y') | NO  |    | N      |      |

| Alter_routine_priv    | enum('N','Y') | NO  |    | N      |      |

| Execute_priv          | enum('N','Y') | NO  |    | N      |      |

| Event_priv            | enum('N','Y') | NO  |    | N      |      |

| Trigger_priv          | enum('N','Y') | NO  |    | N      |      |

+-----------------------+---------------+------+-----+---------+-------+

也可用describe达到同样效果


mysql> describe db;

+-----------------------+---------------+------+-----+---------+-------+

| Field                | Type          | Null | Key | Default | Extra |

+-----------------------+---------------+------+-----+---------+-------+

| Host                  | char(255)    | NO  | PRI |        |      |

| Db                    | char(64)      | NO  | PRI |        |      |

| User                  | char(32)      | NO  | PRI |        |      |

| Select_priv          | enum('N','Y') | NO  |    | N      |      |

| Insert_priv          | enum('N','Y') | NO  |    | N      |      |

| Update_priv          | enum('N','Y') | NO  |    | N      |      |

| Delete_priv          | enum('N','Y') | NO  |    | N      |      |

| Create_priv          | enum('N','Y') | NO  |    | N      |      |

| Drop_priv            | enum('N','Y') | NO  |    | N      |      |

| Grant_priv            | enum('N','Y') | NO  |    | N      |      |

| References_priv      | enum('N','Y') | NO  |    | N      |      |

| Index_priv            | enum('N','Y') | NO  |    | N      |      |

| Alter_priv            | enum('N','Y') | NO  |    | N      |      |

| Create_tmp_table_priv | enum('N','Y') | NO  |    | N      |      |

| Lock_tables_priv      | enum('N','Y') | NO  |    | N      |      |

| Create_view_priv      | enum('N','Y') | NO  |    | N      |      |

| Show_view_priv        | enum('N','Y') | NO  |    | N      |      |

| Create_routine_priv  | enum('N','Y') | NO  |    | N      |      |

| Alter_routine_priv    | enum('N','Y') | NO  |    | N      |      |

| Execute_priv          | enum('N','Y') | NO  |    | N      |      |

| Event_priv            | enum('N','Y') | NO  |    | N      |      |

| Trigger_priv          | enum('N','Y') | NO  |    | N      |      |

+-----------------------+---------------+------+-----+---------+-------+

其它常用show语句


show status;#用于显示广泛的服务起状态信息

show create database;#显示创建特定数据库的SQL语句

show create table;#显示创建特定表的SQL语句

show grants;#显示授予用户的安装权限

show errors;

show warnings;#显示服务器错误或警告信息

2.检索数据

使用select语句检索数据

①检索单个列


mysql> select prod_name from products;#从product表中检索一个名为prod_name的列

+----------------+

| prod_name      |

+----------------+

| .5 ton anvil  |

| 1 ton anvil    |

| 2 ton anvil    |

| Detonator      |

| Bird seed      |

| Carrots        |

| Fuses          |

| JetPack 1000  |

| JetPack 2000  |

| Oil can        |

| Safe          |

| Sling          |

| TNT (1 stick)  |

| TNT (5 sticks) |

+----------------+

这将返回表中所有行

②检索多个列

在select之后给出多个列名


mysql> select prod_id,prod_name,prod_price from products;

+---------+----------------+------------+

| prod_id | prod_name      | prod_price |

+---------+----------------+------------+

| ANV01  | .5 ton anvil  |      5.99 |

| ANV02  | 1 ton anvil    |      9.99 |

| ANV03  | 2 ton anvil    |      14.99 |

| DTNTR  | Detonator      |      13.00 |

| FB      | Bird seed      |      10.00 |

| FC      | Carrots        |      2.50 |

| FU1    | Fuses          |      3.42 |

| JP1000  | JetPack 1000  |      35.00 |

| JP2000  | JetPack 2000  |      55.00 |

| OL1    | Oil can        |      8.99 |

| SAFE    | Safe          |      50.00 |

| SLING  | Sling          |      4.49 |

| TNT1    | TNT (1 stick)  |      2.50 |

| TNT2    | TNT (5 sticks) |      10.00 |

+---------+----------------+------------+

③检索所有列

用*通配符来检索所有列


mysql> select * from products;

+---------+---------+----------------+------------+----------------------------------------------------------------+

| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |

+---------+---------+----------------+------------+----------------------------------------------------------------+

| ANV01  |    1001 | .5 ton anvil  |      5.99 | .5 ton anvil, black, complete with handy hook                  |

| ANV02  |    1001 | 1 ton anvil    |      9.99 | 1 ton anvil, black, complete with handy hook and carrying case |

| ANV03  |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |

| DTNTR  |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |

| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |

| FC      |    1003 | Carrots        |      2.50 | Carrots (rabbit hunting season only)                          |

| FU1    |    1002 | Fuses          |      3.42 | 1 dozen, extra long                                            |

| JP1000  |    1005 | JetPack 1000  |      35.00 | JetPack 1000, intended for single use                          |

| JP2000  |    1005 | JetPack 2000  |      55.00 | JetPack 2000, multi-use                                        |

| OL1    |    1002 | Oil can        |      8.99 | Oil can, red                                                  |

| SAFE    |    1003 | Safe          |      50.00 | Safe with combination lock                                    |

| SLING  |    1003 | Sling          |      4.49 | Sling, one size fits all                                      |

| TNT1    |    1003 | TNT (1 stick)  |      2.50 | TNT, red, single stick                                        |

| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |

+---------+---------+----------------+------------+----------------------------------------------------------------+

④检索不同的行

使用distinct关键字使得查询结果只能是不同的值

不使用distinct查询的结果


mysql> select vend_id from products;

+---------+

| vend_id |

+---------+

|    1001 |

|    1001 |

|    1001 |

|    1002 |

|    1002 |

|    1003 |

|    1003 |

|    1003 |

|    1003 |

|    1003 |

|    1003 |

|    1003 |

|    1005 |

|    1005 |

+---------+

用了distinct之后的查询结果


mysql> select distinct vend_id from products;

+---------+

| vend_id |

+---------+

|    1001 |

|    1002 |

|    1003 |

|    1005 |

+---------+

⑤限制结果

使用limit关键字限制查询结果的行数

显示前五行


mysql> select prod_name from products limit 5;

+--------------+

| prod_name    |

+--------------+

| .5 ton anvil |

| 1 ton anvil  |

| 2 ton anvil  |

| Detonator    |

| Bird seed    |

+--------------+

显示从第五行开始的往后五行（六到十行）


mysql> select prod_name from products limit 5,5;

+--------------+

| prod_name    |

+--------------+

| Carrots      |

| Fuses        |

| JetPack 1000 |

| JetPack 2000 |

| Oil can      |

+--------------+

3.排序检索数据

①排序数据

使用order by子句进行排序


mysql> select prod_name from products order by prod_name;#对结果按prod_name列按字母顺序排序

+----------------+

| prod_name      |

+----------------+

| .5 ton anvil  |

| 1 ton anvil    |

| 2 ton anvil    |

| Bird seed      |

| Carrots        |

| Detonator      |

| Fuses          |

| JetPack 1000  |

| JetPack 2000  |

| Oil can        |

| Safe          |

| Sling          |

| TNT (1 stick)  |

| TNT (5 sticks) |

+----------------+

②按多个列排序

order by子句中多个列名之间用逗号隔开，按照子句中指定的顺序进行排序


#先按照prod_price从小到大进行排序

#在prod_price相同的情况下再按照prod_name的字母顺序进行排序

mysql> select prod_id,prod_price,prod_name from products order by prod_price,prod_name;

+---------+------------+----------------+

| prod_id | prod_price | prod_name      |

+---------+------------+----------------+

| FC      |      2.50 | Carrots        |

| TNT1    |      2.50 | TNT (1 stick)  |

| FU1    |      3.42 | Fuses          |

| SLING  |      4.49 | Sling          |

| ANV01  |      5.99 | .5 ton anvil  |

| OL1    |      8.99 | Oil can        |

| ANV02  |      9.99 | 1 ton anvil    |

| FB      |      10.00 | Bird seed      |

| TNT2    |      10.00 | TNT (5 sticks) |

| DTNTR  |      13.00 | Detonator      |

| ANV03  |      14.99 | 2 ton anvil    |

| JP1000  |      35.00 | JetPack 1000  |

| SAFE    |      50.00 | Safe          |

| JP2000  |      55.00 | JetPack 2000  |

+---------+------------+----------------+

③指定排序方向

DESC:降序 ASC:升序


#对查询结果按照价格从高到底进行排序

mysql> select prod_id,prod_price,prod_name

    -> from products

    -> order by prod_price desc;

+---------+------------+----------------+

| prod_id | prod_price | prod_name      |

+---------+------------+----------------+

| JP2000  |      55.00 | JetPack 2000  |

| SAFE    |      50.00 | Safe          |

| JP1000  |      35.00 | JetPack 1000  |

| ANV03  |      14.99 | 2 ton anvil    |

| DTNTR  |      13.00 | Detonator      |

| FB      |      10.00 | Bird seed      |

| TNT2    |      10.00 | TNT (5 sticks) |

| ANV02  |      9.99 | 1 ton anvil    |

| OL1    |      8.99 | Oil can        |

| ANV01  |      5.99 | .5 ton anvil  |

| SLING  |      4.49 | Sling          |

| FU1    |      3.42 | Fuses          |

| FC      |      2.50 | Carrots        |

| TNT1    |      2.50 | TNT (1 stick)  |

+---------+------------+----------------+

先队产品价格进行降序排序，再对产品名排序：


mysql> select prod_id,prod_price,prod_name

    -> from products

    -> order by prod_price desc,prod_name;

+---------+------------+----------------+

| prod_id | prod_price | prod_name      |

+---------+------------+----------------+

| JP2000  |      55.00 | JetPack 2000  |

| SAFE    |      50.00 | Safe          |

| JP1000  |      35.00 | JetPack 1000  |

| ANV03  |      14.99 | 2 ton anvil    |

| DTNTR  |      13.00 | Detonator      |

| FB      |      10.00 | Bird seed      |

| TNT2    |      10.00 | TNT (5 sticks) |

| ANV02  |      9.99 | 1 ton anvil    |

| OL1    |      8.99 | Oil can        |

| ANV01  |      5.99 | .5 ton anvil  |

| SLING  |      4.49 | Sling          |

| FU1    |      3.42 | Fuses          |

| FC      |      2.50 | Carrots        |

| TNT1    |      2.50 | TNT (1 stick)  |

+---------+------------+----------------+

与limit配合查出最昂贵物品的价格


mysql> select prod_price from products order by prod_price desc limit 1;

+------------+

| prod_price |

+------------+

|      55.00 |

+------------+

4.过滤数据

where子句在order by子句之前

①where子句


#查询价格为2.5的商品

mysql> select prod_name,prod_price from products where prod_price = 2.50;

+---------------+------------+

| prod_name    | prod_price |

+---------------+------------+

| Carrots      |      2.50 |

| TNT (1 stick) |      2.50 |

+---------------+------------+

②条件操作符

操作符说明

=：等于

<>：不等于

!=：不等于

<：小于

<=：小于等于

>：大于

>=：大于等于

BETWEEN...AND...：在两个值之间

③检查单个值

MySQL执行匹配时默认不区分大小写


mysql> select prod_name,prod_price from products where prod_name = 'fuses';

+-----------+------------+

| prod_name | prod_price |

+-----------+------------+

| Fuses    |      3.42 |

+-----------+------------+

④不匹配检查


#查询不是由1003供应商供应的商品，也可以用<>符号

mysql> select vend_id,prod_name from products where vend_id != 1003;

+---------+--------------+

| vend_id | prod_name    |

+---------+--------------+

|    1001 | .5 ton anvil |

|    1001 | 1 ton anvil  |

|    1001 | 2 ton anvil  |

|    1002 | Fuses        |

|    1002 | Oil can      |

|    1005 | JetPack 1000 |

|    1005 | JetPack 2000 |

+---------+--------------+

字符串要用单引号' '括起来

⑤范围值检查


#查询价格5到10之间的商品

mysql> select prod_name,prod_price from products where prod_price between 5 and 10;

+----------------+------------+

| prod_name      | prod_price |

+----------------+------------+

| .5 ton anvil  |      5.99 |

| 1 ton anvil    |      9.99 |

| Bird seed      |      10.00 |

| Oil can        |      8.99 |

| TNT (5 sticks) |      10.00 |

+----------------+------------+

⑥空值检查

使用is null来检查空值


#查询没有邮箱的顾客id

mysql> select cust_id from customers where cust_email is null;

+---------+

| cust_id |

+---------+

|  10002 |

|  10005 |

+---------+

5.where子句的进阶用法

①and操作符

用and对多个列进行过滤


mysql> select prod_id,prod_name,prod_price from products where vend_id = 1003 and prod_price <= 10;

+---------+----------------+------------+

| prod_id | prod_name      | prod_price |

+---------+----------------+------------+

| FB      | Bird seed      |      10.00 |

| FC      | Carrots        |      2.50 |

| SLING  | Sling          |      4.49 |

| TNT1    | TNT (1 stick)  |      2.50 |

| TNT2    | TNT (5 sticks) |      10.00 |

+---------+----------------+------------+

②or操作符


mysql> select prod_name,prod_price from products where vend_id = 1002 or vend_id = 1003;

+----------------+------------+

| prod_name      | prod_price |

+----------------+------------+

| Fuses          |      3.42 |

| Oil can        |      8.99 |

| Detonator      |      13.00 |

| Bird seed      |      10.00 |

| Carrots        |      2.50 |

| Safe          |      50.00 |

| Sling          |      4.49 |

| TNT (1 stick)  |      2.50 |

| TNT (5 sticks) |      10.00 |

+----------------+------------+

③计算次序

列出价格大于等于10美元且由1002或1003供应商制造的所有商品


mysql> select prod_name,prod_price from products where vend_id=1002 or vend_id=1003 and prod_price >= 10;

+----------------+------------+

| prod_name      | prod_price |

+----------------+------------+

| Fuses          |      3.42 |

| Oil can        |      8.99 |

| Detonator      |      13.00 |

| Bird seed      |      10.00 |

| Safe          |      50.00 |

| TNT (5 sticks) |      10.00 |

+----------------+------------+

这样的查询结果显然不符合要求（结果中由价格小于10的商品），原因是and的运算优先度比or高，MySQL把这段条件解释为“供应商1003制造的价格大于等于10的商品或1002制造的所有商品”了

为了避免这种情况应该使用括号


mysql> select prod_name,prod_price from products where (vend_id=1002 or vend_id=1003) and prod_price >= 10;

+----------------+------------+

| prod_name      | prod_price |

+----------------+------------+

| Detonator      |      13.00 |

| Bird seed      |      10.00 |

| Safe          |      50.00 |

| TNT (5 sticks) |      10.00 |

+----------------+------------+

④in操作符

用in操作符和括号配合指定条件范围，范围中的每个条件都可以进行匹配，条件之间用逗号分隔


#和下面的or达到了一样的效果

mysql> select prod_name,prod_price from products where vend_id in (1002,1003) order by prod_name;

+----------------+------------+

| prod_name      | prod_price |

+----------------+------------+

| Bird seed      |      10.00 |

| Carrots        |      2.50 |

| Detonator      |      13.00 |

| Fuses          |      3.42 |

| Oil can        |      8.99 |

| Safe          |      50.00 |

| Sling          |      4.49 |

| TNT (1 stick)  |      2.50 |

| TNT (5 sticks) |      10.00 |

+----------------+------------+


mysql> select prod_name,prod_price from products where vend_id = 1002 or vend_id = 1003 order by prod_name;

+----------------+------------+

| prod_name      | prod_price |

+----------------+------------+

| Bird seed      |      10.00 |

| Carrots        |      2.50 |

| Detonator      |      13.00 |

| Fuses          |      3.42 |

| Oil can        |      8.99 |

| Safe          |      50.00 |

| Sling          |      4.49 |

| TNT (1 stick)  |      2.50 |

| TNT (5 sticks) |      10.00 |

+----------------+------------+

推荐使用in操作符来代替or操作符，原因如下：

清楚直观
计算次序容易管理
比or执行速度更快
in的最大优点是可以包含其它where子句

⑤not操作符

用not否定后面跟的条件


#列出1002，1003以外的供应商制造的所有商品

mysql> select prod_name,prod_price from products where vend_id not in (1002,1003) order by prod_name;

+--------------+------------+

| prod_name    | prod_price |

+--------------+------------+

| .5 ton anvil |      5.99 |

| 1 ton anvil  |      9.99 |

| 2 ton anvil  |      14.99 |

| JetPack 1000 |      35.00 |

| JetPack 2000 |      55.00 |

+--------------+------------+

6.用通配符进行过滤

①like操作符

通配符：用来匹配值的一部分的特殊字符
搜索模式：由字面值、通配符或两者组合构成的搜索条件

为了使用通配符，必须使用like操作符

②百分号通配符

%表示任何字符出现任意次数


#列出以jet开头的所有商品

mysql> select prod_id,prod_name from products where prod_name like 'jet%';

+---------+--------------+

| prod_id | prod_name    |

+---------+--------------+

| JP1000  | JetPack 1000 |

| JP2000  | JetPack 2000 |

+---------+--------------+

可以使用多个通配符


mysql> select prod_id,prod_name from products where prod_name like '%anvil%';

+---------+--------------+

| prod_id | prod_name    |

+---------+--------------+

| ANV01  | .5 ton anvil |

| ANV02  | 1 ton anvil  |

| ANV03  | 2 ton anvil  |

+---------+--------------+

③下划线通配符

用于匹配单个字符


mysql> select prod_id,prod_name from products where prod_name like '_ ton anvil';

+---------+-------------+

| prod_id | prod_name  |

+---------+-------------+

| ANV02  | 1 ton anvil |

| ANV03  | 2 ton anvil |

+---------+-------------+

④使用通配符的技巧

如果其它操作符能达到相同目的就不要用通配符
尽量不要把通配符放在搜索模式的开始处，这样搜索速度最慢
注意通配符的位置

7.用正则表达式进行搜索

正则表达式是用来匹配文本的特殊的串

①基本字符匹配


mysql> select prod_name from products where prod_name regexp '1000' order by prod_name;

+--------------+

| prod_name    |

+--------------+

| JetPack 1000 |

+--------------+

regexp后所跟的是正则表达式

用.去匹配任意一个字符


mysql> select prod_name from products where prod_name regexp '.000' order by prod_name;

+--------------+

| prod_name    |

+--------------+

| JetPack 1000 |

| JetPack 2000 |

+--------------+

②进行or匹配


mysql> select prod_name from products where prod_name regexp '1000|2000' order by prod_name;

+--------------+

| prod_name    |

+--------------+

| JetPack 1000 |

| JetPack 2000 |

+--------------+

③匹配几个字符之一


#[123]匹配1or2or3

mysql> select prod_name from products where prod_name regexp '[123] Ton' order by prod_name;

+-------------+

| prod_name  |

+-------------+

| 1 ton anvil |

| 2 ton anvil |

+-------------+

④匹配范围

用[0-9]表示[0123456789]


mysql> select prod_name from products where prod_name regexp '[1-5] Ton' order by prod_name;

+--------------+

| prod_name    |

+--------------+

| .5 ton anvil |

| 1 ton anvil  |

| 2 ton anvil  |

+--------------+

⑤匹配特殊字符

用转移符号\匹配特殊字符


#匹配.

mysql> select vend_name from vendors where vend_name regexp '\\.' order by vend_name;

+--------------+

| vend_name    |

+--------------+

| Furball Inc. |

+--------------+


\\-匹配-,\\.匹配.,\\\匹配\

⑥匹配字符类

预定义的字符集称为字符类

类的说明

[:alnum:]            任意字母和数字          

 [:alpha:]              任意字符            

 [:blank:]              空格和制表            

 [:cntrl:]            ascⅡ控制字符          

 [:digit:]              任意数字            

 [:graph:]    与[:print:]相同但不包含空格    

 [:lower:]            任意小写字符          

 [:print:]            任意可打印字符          

 [:punct:]   不在[:alnum:]和[:cntrl:]中的字符 

| [:space:]      包括空格在内的任意空白字符    

| [:upper:]            任意大写字母          

| [:xdigit:]         任意十六进制数字

⑦匹配多个实例

重复元字符

元字符的说明

  *          0个或多个匹配        

  +      一个或多个匹配，等于{1,}  

  ?            0个或1个匹配        

  {n}        指定数目的匹配      

  {n,}      不少于指定数目的匹配    

 {n,m}   匹配数目的范围(m不超过255)

例子一：


#\\(和\\)分别匹配了括号的两边,s后的?使得s可有可无

mysql> select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;

+----------------+

| prod_name      |

+----------------+

| TNT (1 stick)  |

| TNT (5 sticks) |

+----------------+

例子二：


#[:digit:]表示任意数字,{4}表示任意数字连续出现四次

mysql> select prod_name from products where prod_name regexp '[[:digit:]]{4}' order by prod_name;

+--------------+

| prod_name    |

+--------------+

| JetPack 1000 |

| JetPack 2000 |

+--------------+

#也可以这样写

mysql> select prod_name from products where prod_name regexp '[0-9][0-9][0-9][0-9]' order by prod_name;

+--------------+

| prod_name    |

+--------------+

| JetPack 1000 |

| JetPack 2000 |

+--------------+

⑧定位符

定位元字符

 元字符      说明    

    ^     文本的开始 

    $     文本的结尾 

 [[:<:]]   词的开始  

 [[:>:]]   词的结尾


#搜索一个数(包括以小数点开始的数)开始的所有产品

mysql> select prod_name from products where prod_name regexp '^[0-9\\.]' order by prod_name;

+--------------+

| prod_name    |

+--------------+

| .5 ton anvil |

| 1 ton anvil  |

| 2 ton anvil  |

+--------------+