mysql 数据操作 单表查询 where约束 like 模糊匹配

 

 

 

mysql> select * from employee;
+----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
|  1 | alex       | male   |  78 | 2015-03-02 | teacher   | NULL         | 1000000.31 |    401 |         1 |
|  2 | yuanhao    | male   |  73 | 2014-07-01 | teacher   | NULL         |    3500.00 |    401 |         1 |
|  3 | liwenzhou  | male   |  28 | 2012-11-01 | teacher   | NULL         |    2100.00 |    401 |         1 |
|  4 | jingliyang | female |  18 | 2011-02-11 | teacher   | NULL         |    9000.00 |    401 |         1 |
|  5 | jinxin     | male   |  18 | 1900-03-01 | teacher   | NULL         |   30000.00 |    401 |         1 |
|  6 | 成龙       | male   |  48 | 2010-11-11 | teacher   | NULL         |   10000.00 |    401 |         1 |
|  7 | 歪歪       | female |  48 | 2015-03-11 | sale      | NULL         |    3000.13 |    402 |         2 |
|  8 | 丫丫       | female |  38 | 2010-11-01 | sale      | NULL         |    2000.35 |    402 |         2 |
|  9 | 丁丁       | female |  18 | 2011-03-12 | sale      | NULL         |    1000.37 |    402 |         2 |
| 10 | 星星       | female |  18 | 2016-05-13 | sale      | NULL         |    3000.29 |    402 |         2 |
| 11 | 格格       | female |  28 | 2017-01-27 | sale      | NULL         |    4000.33 |    402 |         2 |
| 12 | 张野       | male   |  28 | 2016-03-11 | operation | NULL         |   10000.13 |    403 |         3 |
| 13 | 程咬金     | male   |  18 | 1997-03-12 | operation | NULL         |   20000.00 |    403 |         3 |
| 14 | 程咬银     | female |  18 | 2013-03-11 | operation | NULL         |   19000.00 |    403 |         3 |
| 15 | 程咬铜     | male   |  18 | 2015-04-11 | operation | NULL         |   18000.00 |    403 |         3 |
| 16 | 程咬铁     | female |  18 | 2014-05-12 | operation | NULL         |   17000.00 |    403 |         3 |
+----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
16 rows in set (0.06 sec)

 

 

需求：找jin开头的员工记录 用到模糊匹配

 

% 和正则表达式的 * 一样     代表任意字符

_ 任意一个字符

 

只要jin开头就可以了

 

mysql> select * from employee where name like 'jin%';
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  4 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  5 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)

 

 

 

 

 

代表匹配6个字符  后面___ 的 都是任意字符   匹配jin开头 6个字符的

 

 

mysql> select * from employee where name like 'jin_';
Empty set (0.00 sec)

mysql> select * from employee where name like 'jin__';
Empty set (0.00 sec)

mysql> select * from employee where name like 'jin___';
+----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name   | sex  | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+
|  5 | jinxin | male |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
+----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+
1 row in set (0.00 sec)

 

 

 

#6:关键字LIKE模糊查询
    通配符’%’
    SELECT * FROM employee 
            WHERE name LIKE 'eg%';

    通配符’_’
    SELECT * FROM employee 
            WHERE name LIKE 'al__';