数据库——数据操作——单表查询（9）

单表查询

  • 1.语法
  • SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

     

  • 2.关键字的执行优先级（重点）
  • 重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
    1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

     

  • 3.简单查询示例
  •  1 company.employee
 2     员工id      id                  int             
 3     姓名        emp_name            varchar
 4     性别        sex                 enum
 5     年龄        age                 int
 6     入职日期     hire_date           date
 7     岗位        post                varchar
 8     职位描述     post_comment        varchar
 9     薪水        salary              double
10     办公室       office              int
11     部门编号     depart_id           int
12 
13 
14 
15 #创建表
16 create table employee(
17 id int not null unique auto_increment,
18 name varchar(20) not null,
19 sex enum('male','female') not null default 'male', #大部分是男的
20 age int(3) unsigned not null default 28,
21 hire_date date not null,
22 post varchar(50),
23 post_comment varchar(100),
24 salary double(15,2),
25 office int, #一个部门一个屋子
26 depart_id int
27 );
28 
29 
30 #查看表结构
31 mysql> desc employee;
32 +--------------+-----------------------+------+-----+---------+----------------+
33 | Field        | Type                  | Null | Key | Default | Extra          |
34 +--------------+-----------------------+------+-----+---------+----------------+
35 | id           | int(11)               | NO   | PRI | NULL    | auto_increment |
36 | name         | varchar(20)           | NO   |     | NULL    |                |
37 | sex          | enum('male','female') | NO   |     | male    |                |
38 | age          | int(3) unsigned       | NO   |     | 28      |                |
39 | hire_date    | date                  | NO   |     | NULL    |                |
40 | post         | varchar(50)           | YES  |     | NULL    |                |
41 | post_comment | varchar(100)          | YES  |     | NULL    |                |
42 | salary       | double(15,2)          | YES  |     | NULL    |                |
43 | office       | int(11)               | YES  |     | NULL    |                |
44 | depart_id    | int(11)               | YES  |     | NULL    |                |
45 +--------------+-----------------------+------+-----+---------+----------------+
46 
47 #插入记录
48 #三个部门：教学，销售，运营
49 insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
50 ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
51 ('alex','male',78,'20150302','teacher',1000000.31,401,1),
52 ('wupeiqi','male',81,'20130305','teacher',8300,401,1),
53 ('yuanhao','male',73,'20140701','teacher',3500,401,1),
54 ('liwenzhou','male',28,'20121101','teacher',2100,401,1),
55 ('jingliyang','female',18,'20110211','teacher',9000,401,1),
56 ('jinxin','male',18,'19000301','teacher',30000,401,1),
57 ('成龙','male',48,'20101111','teacher',10000,401,1),
58 
59 ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
60 ('丫丫','female',38,'20101101','sale',2000.35,402,2),
61 ('丁丁','female',18,'20110312','sale',1000.37,402,2),
62 ('星星','female',18,'20160513','sale',3000.29,402,2),
63 ('格格','female',28,'20170127','sale',4000.33,402,2),
64 
65 ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
66 ('程咬金','male',18,'19970312','operation',20000,403,3),
67 ('程咬银','female',18,'20130311','operation',19000,403,3),
68 ('程咬铜','male',18,'20150411','operation',18000,403,3),
69 ('程咬铁','female',18,'20140512','operation',17000,403,3)
70 ;
71 
72 #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
73 
74 准备表和记录
    准备表和记录
  •  1 #简单查询
 2     SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
 3     FROM employee;
 4 
 5     SELECT * FROM employee;
 6 
 7     SELECT name,salary FROM employee;
 8 
 9 #避免重复DISTINCT
10     SELECT DISTINCT post FROM employee;    
11 
12 #通过四则运算查询
13     SELECT name, salary*12 FROM employee;
14     SELECT name, salary*12 AS Annual_salary FROM employee;
15     SELECT name, salary*12 Annual_salary FROM employee;
16 
17 #定义显示格式
18    CONCAT() 函数用于连接字符串
19    SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary 
20    FROM employee;
21 
22    CONCAT_WS() 第一个参数为分隔符
23    SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary 
24    FROM employee;
25 
26 1
27 查出所有员工的名字，薪资,格式为
28 
29 <
30 名字:egon
31 >
32 <
33 薪资:3000
34 >
35 
36 2
37  查出所有的岗位（去掉重复）
38 
39 3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
40 select concat('<名字:',name,'>    ','<薪资:',salary,'>') from employee;
41 select distinct depart_id from employee;
42 select name,salary*12 annual_salary from employee;
    简单查询

     

  • 4.where约束
    • 比较运算符：>,<,>=,<=,<>,!=
    • between 80 and 100  （值在80到100之间）
    • in(80,90,100) 值是80或90或100
    • like ‘egon%’
      • pattern可以是%或_，%表示任意多字符，_表示一个字符
    • 逻辑运算符：在多个条件直接可以使用逻辑运算符 and  or not
    •  1 #1:单条件查询
 2     SELECT name FROM employee
 3         WHERE post='sale';
 4 
 5 #2:多条件查询
 6     SELECT name,salary FROM employee
 7         WHERE post='teacher' AND salary>10000;
 8 
 9 #3:关键字BETWEEN AND
10     SELECT name,salary FROM employee 
11         WHERE salary BETWEEN 10000 AND 20000;
12 
13     SELECT name,salary FROM employee 
14         WHERE salary NOT BETWEEN 10000 AND 20000;
15 
16 #4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
17     SELECT name,post_comment FROM employee 
18         WHERE post_comment IS NULL;
19 
20     SELECT name,post_comment FROM employee 
21         WHERE post_comment IS NOT NULL;
22 
23     SELECT name,post_comment FROM employee 
24         WHERE post_comment=''; 注意''是空字符串，不是null
25     ps：
26         执行
27         update employee set post_comment='' where id=2;
28         再用上条查看，就会有结果了
29 
30 #5:关键字IN集合查询
31     SELECT name,salary FROM employee 
32         WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
33 
34     SELECT name,salary FROM employee 
35         WHERE salary IN (3000,3500,4000,9000) ;
36 
37     SELECT name,salary FROM employee 
38         WHERE salary NOT IN (3000,3500,4000,9000) ;
39 
40 #6:关键字LIKE模糊查询
41     通配符’%’
42     SELECT * FROM employee 
43             WHERE name LIKE 'eg%';
44 
45     通配符’_’
46     SELECT * FROM employee 
47             WHERE name LIKE 'al__';
      where约束示例
       1 1. 查看岗位是teacher的员工姓名、年龄
 2 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
 3 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
 4 4. 查看岗位描述不为NULL的员工信息
 5 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
 6 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
 7 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
 8 
 9 
10 select name,age from employee where post = 'teacher';
11 select name,age from employee where post='teacher' and age > 30; 
12 select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
13 select * from employee where post_comment is not null;
14 select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
15 select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
16 select name,salary*12 from employee where post='teacher' and name like 'jin%';
      小练习

       

  • 5.分组查询
    • 什么是分组？为什么要分组
    • #1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

#3、为何要分组呢？
    取每个部门的最高工资
    取每个部门的员工数
    取男人数和女人数

小窍门：‘每’这个字后面的字段，就是我们分组的依据


#4、大前提：
    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

       

    • only_full_group_by
    •  1 #查看MySQL 5.7默认的sql_mode如下：
 2 mysql> select @@global.sql_mode;
 3 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 4 
 5 #！！！注意
 6 ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。
 7 
 8 
 9 #设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
10 mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
11 
12 ！！！SQL_MODE设置！！！
      View Code
       1 mysql> select @@global.sql_mode;
 2 +-------------------+
 3 | @@global.sql_mode |
 4 +-------------------+
 5 |                   |
 6 +-------------------+
 7 row in set (0.00 sec)
 8 
 9 mysql> select * from employee group by post; 
10 +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
11 | id | name | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
12 +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
13 | 14 | 张野 | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |
14 |  9 | 歪歪 | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |
15 |  2 | alex | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
16 |  1 | egon | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
17 +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
19 
20 
21 #由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的
22 
23 mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
24 Query OK, 0 rows affected (0.00 sec)
25 
26 mysql> quit #设置成功后，一定要退出，然后重新登录方可生效
27 Bye
28 
29 mysql> use db1;
30 Database changed
31 mysql> select * from employee group by post; #报错
32 ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY
33 mysql> select post,count(id) from employee group by post; #只能查看分组依据和使用聚合函数
34 +----------------------------+-----------+
35 | post                       | count(id) |
36 +----------------------------+-----------+
37 | operation                  |         5 |
38 | sale                       |         5 |
39 | teacher                    |         7 |
40 | 老男孩驻沙河办事处外交大使 |         1 |
41 +----------------------------+-----------+
42 rows in set (0.00 sec)
      View Code

       

    • group  by
    • 单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;

GROUP BY与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
      强调！！！

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

       

    • 聚合函数
    • #强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例：
    SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;
    • 小练习
    •  1 1. 查询岗位名以及岗位包含的所有员工名字
 2 2. 查询岗位名以及各岗位内包含的员工个数
 3 3. 查询公司内男员工和女员工的个数
 4 4. 查询岗位名以及各岗位的平均薪资
 5 5. 查询岗位名以及各岗位的最高薪资
 6 6. 查询岗位名以及各岗位的最低薪资
 7 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
 8 
 9 #题1：分组
10 mysql> select post,group_concat(name) from employee group by post;
11 +-----------------------------------------+---------------------------------------------------------+
12 | post                                    | group_concat(name)                                      |
13 +-----------------------------------------+---------------------------------------------------------+
14 | operation                               | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
15 | sale                                    | 歪歪,丫丫,丁丁,星星,格格                                |
16 | teacher                                 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
17 | 老男孩驻沙河办事处外交大使              | egon                                                    |
18 +-----------------------------------------+---------------------------------------------------------+
19 
20 
21 #题目2：
22 mysql> select post,count(id) from employee group by post;
23 +-----------------------------------------+-----------+
24 | post                                    | count(id) |
25 +-----------------------------------------+-----------+
26 | operation                               |         5 |
27 | sale                                    |         5 |
28 | teacher                                 |         7 |
29 | 老男孩驻沙河办事处外交大使              |         1 |
30 +-----------------------------------------+-----------+
31 
32 
33 #题目3：
34 mysql> select sex,count(id) from employee group by sex;
35 +--------+-----------+
36 | sex    | count(id) |
37 +--------+-----------+
38 | male   |        10 |
39 | female |         8 |
40 +--------+-----------+
41 
42 #题目4：
43 mysql> select post,avg(salary) from employee group by post;
44 +-----------------------------------------+---------------+
45 | post                                    | avg(salary)   |
46 +-----------------------------------------+---------------+
47 | operation                               |  16800.026000 |
48 | sale                                    |   2600.294000 |
49 | teacher                                 | 151842.901429 |
50 | 老男孩驻沙河办事处外交大使              |   7300.330000 |
51 +-----------------------------------------+---------------+
52 
53 #题目5
54 mysql> select post,max(salary) from employee group by post;
55 +-----------------------------------------+-------------+
56 | post                                    | max(salary) |
57 +-----------------------------------------+-------------+
58 | operation                               |    20000.00 |
59 | sale                                    |     4000.33 |
60 | teacher                                 |  1000000.31 |
61 | 老男孩驻沙河办事处外交大使              |     7300.33 |
62 +-----------------------------------------+-------------+
63 
64 #题目6
65 mysql> select post,min(salary) from employee group by post;
66 +-----------------------------------------+-------------+
67 | post                                    | min(salary) |
68 +-----------------------------------------+-------------+
69 | operation                               |    10000.13 |
70 | sale                                    |     1000.37 |
71 | teacher                                 |     2100.00 |
72 | 老男孩驻沙河办事处外交大使              |     7300.33 |
73 +-----------------------------------------+-------------+
74 
75 #题目七
76 mysql> select sex,avg(salary) from employee group by sex;
77 +--------+---------------+
78 | sex    | avg(salary)   |
79 +--------+---------------+
80 | male   | 110920.077000 |
81 | female |   7250.183750 |
82 +--------+---------------+
      View Code

       

  • 6.having 过滤
    • HAVING与WHERE不一样的地方在于!!!!!!
    • #！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
       1 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
 2 2. 查询各岗位平均薪资大于10000的岗位名、平均工资
 3 3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
 4 
 5 #题1：
 6 mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
 7 +-----------------------------------------+--------------------+-----------+
 8 | post                                    | group_concat(name) | count(id) |
 9 +-----------------------------------------+--------------------+-----------+
10 | 老男孩驻沙河办事处外交大使              | egon               |         1 |
11 +-----------------------------------------+--------------------+-----------+
12 
13 #题目2：
14 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
15 +-----------+---------------+
16 | post      | avg(salary)   |
17 +-----------+---------------+
18 | operation |  16800.026000 |
19 | teacher   | 151842.901429 |
20 +-----------+---------------+
21 
22 #题目3：
23 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;
24 +-----------+--------------+
25 | post      | avg(salary)  |
26 +-----------+--------------+
27 | operation | 16800.026000 |
28 +-----------+--------------+
      小练习

       

  • 7.查询排序：order by 
    • 按单列排序
    SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
    SELECT * from employee
        ORDER BY age,
        salary DESC;
       1 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
 2 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
 3 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
 4 #题目1
 5 mysql> select * from employee ORDER BY age asc,hire_date desc;
 6 
 7 #题目2
 8 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
 9 +-----------+---------------+
10 | post      | avg(salary)   |
11 +-----------+---------------+
12 | operation |  16800.026000 |
13 | teacher   | 151842.901429 |
14 +-----------+---------------+
15 
16 #题目3
17 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
18 +-----------+---------------+
19 | post      | avg(salary)   |
20 +-----------+---------------+
21 | teacher   | 151842.901429 |
22 | operation |  16800.026000 |
23 +-----------+---------------+
      小练习

       

  • 8.限制查询的记录数：limit 
    • 示例：
    SELECT * FROM employee ORDER BY salary DESC 
        LIMIT 3;                    #默认初始位置为0 

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
       1 1. 分页显示，每页5条
 2 
 3 mysql> select * from  employee limit 0,5;
 4 +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
 5 | id | name      | sex  | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
 6 +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
 7 |  1 | egon      | male |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
 8 |  2 | alex      | male |  78 | 2015-03-02 | teacher                                 |              | 1000000.31 |    401 |         1 |
 9 |  3 | wupeiqi   | male |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
10 |  4 | yuanhao   | male |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
11 |  5 | liwenzhou | male |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
12 +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
13 rows in set (0.00 sec)
14 
15 mysql> select * from  employee limit 5,5;
16 +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
17 | id | name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
18 +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
19 |  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
20 |  7 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
21 |  8 | 成龙       | male   |  48 | 2010-11-11 | teacher | NULL         | 10000.00 |    401 |         1 |
22 |  9 | 歪歪       | female |  48 | 2015-03-11 | sale    | NULL         |  3000.13 |    402 |         2 |
23 | 10 | 丫丫       | female |  38 | 2010-11-01 | sale    | NULL         |  2000.35 |    402 |         2 |
24 +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
25 rows in set (0.00 sec)
26 
27 mysql> select * from  employee limit 10,5;
28 +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
29 | id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
30 +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
31 | 11 | 丁丁      | female |  18 | 2011-03-12 | sale      | NULL         |  1000.37 |    402 |         2 |
32 | 12 | 星星      | female |  18 | 2016-05-13 | sale      | NULL         |  3000.29 |    402 |         2 |
33 | 13 | 格格      | female |  28 | 2017-01-27 | sale      | NULL         |  4000.33 |    402 |         2 |
34 | 14 | 张野      | male   |  28 | 2016-03-11 | operation | NULL         | 10000.13 |    403 |         3 |
35 | 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         | 20000.00 |    403 |         3 |
36 +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
37 rows in set (0.00 sec)
      小练习
  • 9.使用正则表达式查询 
    • SELECT * FROM employee WHERE name REGEXP '^ale';

SELECT * FROM employee WHERE name REGEXP 'on$';

SELECT * FROM employee WHERE name REGEXP 'm{2}';


小结：对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';
      1 查看所有员工中名字是jin开头，n或者g结果的员工信息
2 
3 select * from employee where name regexp '^jin.*[gn]$';
      小练习

       

posted @ 2018-03-12 12:54  GraceZen  阅读(297)  评论(0)  编辑  收藏  举报