模糊查询

• 创建下面的表：
  
  create table employee ( id int primary key auto_increment, name varchar(10), sales int, hiredate datetime);
  
  datetime 是日期时间数据类型。为新表添加记录:
  
  insert into employee ( name, sales, hiredate ) values ("张飞", 800, NOW() );
  
  insert into employee( name, sales, hiredate ) values ("王兵", 600, NOW() );
  
  insert into employee( name, sales, hiredate ) values ("刘玲", 700, NOW() );
  
  insert into employee( name, sales, hiredate ) values ("李梅花",900, NOW() );
  
  insert into employee( name, sales, hiredate ) values ("杨梅丽",300, NOW() );
  
  insert into employee( name, sales, hiredate ) values ("赵小梅",900, NOW() );
  
  NOW() 是返回日期时间的函数。显示表的记录：

  
    mysql>  select * from employee;
  +----+--------+-------+---------------------+
  | id | name   | sales | hiredate            |
  +----+--------+-------+---------------------+
  |  1 | 张飞   |   800 | 2026-05-01 22:09:44 |
  |  2 | 王兵   |   600 | 2026-05-01 22:11:33 |
  |  3 | 刘玲   |   700 | 2026-05-01 22:12:16 |
  |  4 | 李梅花 |   900 | 2026-05-01 22:14:51 |
  |  5 | 杨梅丽 |   300 | 2026-05-01 22:15:51 |
  |  6 | 赵小梅 |   900 | 2026-05-01 22:17:18 |
  +----+--------+-------+---------------------+
  6 rows in set (0.020 sec)
  
  mysql>
                                                 

  
  
  
• 提取表中 name 含有梅的记录：
  

  
      select * from employee  where name like '%梅%';
                                                 

  
     mysql> select * from employee  where name like '%梅%';
  +----+--------+-------+---------------------+
  | id | name   | sales | hiredate            |
  +----+--------+-------+---------------------+
  |  4 | 李梅花 |   900 | 2026-05-01 22:14:51 |
  |  5 | 杨梅丽 |   300 | 2026-05-01 22:15:51 |
  |  6 | 赵小梅 |   900 | 2026-05-01 22:17:18 |
  +----+--------+-------+---------------------+
  3 rows in set (0.006 sec)
  
  mysql>
                                                 

  上面的通配符 % 表示任意字符串，当然包括空字符串。



• 提取表中 name 中间含有梅的记录：
  

  
      select * from employee  where name like '_梅_';
                                                 

     mysql> select * from employee  where name like '_梅_';
  +----+--------+-------+---------------------+
  | id | name   | sales | hiredate            |
  +----+--------+-------+---------------------+
  |  4 | 李梅花 |   900 | 2026-05-01 22:14:51 |
  |  5 | 杨梅丽 |   300 | 2026-05-01 22:15:51 |
  +----+--------+-------+---------------------+
  2 rows in set (0.006 sec)
  
  mysql>
                                                 

  上面的通配符 _ 表示任意一个字符。

按升序排列并显示

• 按升序显示记录的格式：
  
  select * from 表名 order by 列名 ;
  
  下面的命令按 sales 升序排列，提取表 employee 的记录：
  
  

  
       select * from employee order by sales;
  
                                  

  
     mysql> select * from employee order by sales;
  +----+--------+-------+---------------------+
  | id | name   | sales | hiredate            |
  +----+--------+-------+---------------------+
  |  5 | 杨梅丽 |   300 | 2026-05-01 22:15:51 |
  |  2 | 王兵   |   600 | 2026-05-01 22:11:33 |
  |  3 | 刘玲   |   700 | 2026-05-01 22:12:16 |
  |  1 | 张飞   |   800 | 2026-05-01 22:09:44 |
  |  4 | 李梅花 |   900 | 2026-05-01 22:14:51 |
  |  6 | 赵小梅 |   900 | 2026-05-01 22:17:18 |
  +----+--------+-------+---------------------+
  6 rows in set (1.128 sec)
  
  mysql>
                                                 

  
  
  

按降序排列且指定记录的范围提取记录

• 按降序排列且指定记录的显示范围的格式：
  
  select * from 表名 order by 列名 desc limit 数 offset 数 ;
  
  下面的命令按 sales 降序排列后，从第3条记录开始，提取表 employee 的 3 条记录：：
  
  

  
       select * from employee order by sales desc limit 3 offset 2;
  
                                  

  
     mysql> select * from employee order by sales desc limit 3 offset 2;
  +----+------+-------+---------------------+
  | id | name | sales | hiredate            |
  +----+------+-------+---------------------+
  |  1 | 张飞 |   800 | 2026-05-01 22:09:44 |
  |  3 | 刘玲 |   700 | 2026-05-01 22:12:16 |
  |  2 | 王兵 |   600 | 2026-05-01 22:11:33 |
  +----+------+-------+---------------------+
  3 rows in set (0.007 sec)
  
  mysql>