单表查询
单表查询的语法及关键字执行的优先级
单表查询语法
SELECT DISTINCT（去掉重复） 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
关键字执行的优先级
from where group by select distinct having order by limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.执行select（去重）
5.将分组的结果进行having过滤
6.将结果按条件排序：order by
7.限制结果的显示条数
简单查询
company.employee 员工id id int 姓名 emp_name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 职位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int #创建表 create table employee( id int not null unique auto_increment, emp_name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ) CHARSET=utf8 ENGINE=InnoDB; #查看表结构 mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | emp_name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ #插入记录 #三个部门：教学，销售，运营 insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龙','male',48,'20101111','teacher',10000,401,1), ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2), ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3) ;
#简单查询 mysql> select id,emp_name,sex,age,hire_date,post_comment,salary,office,depart_id from employee; # 查看指定列数据 mysql> mysql> select * from employee; #查看所有数据 mysql> select emp_name,salary from employee; # 避免重复 mysql> select distinct post from employee; #对查出的数据去重 +-----------------------------------------+ | post | +-----------------------------------------+ | 老男孩驻沙河办事处外交大使 | | teacher | | sale | | operation | +-----------------------------------------+ 4 rows in set (0.01 sec) #通过四则运算查询 mysql> select emp_name, salary*12 from employee; mysql> select emp_name, salary*12 as Anuual_salary from employee; # as 别名 mysql> select emp_name, salary*12 Anuual_salary from employee; # 直接设置别名 #定义显示格式 CONCAT() 函数用于连接字符串 mysql> select concat('姓名: ',emp_name,'年薪: ',salary*12) as Annual_salary from employee; +-------------------------------------+ | Annual_salary | +-------------------------------------+ | 姓名: egon年薪: 87603.96 | | 姓名: alex年薪: 12000003.72 | | 姓名: wupeiqi年薪: 99600.00 | | 姓名: yuanhao年薪: 42000.00 | | 姓名: liwenzhou年薪: 25200.00 | | 姓名: jingliyang年薪: 108000.00 | | 姓名: jinxin年薪: 360000.00 | | 姓名: 成龙年薪: 120000.00 | | 姓名: 歪歪年薪: 36001.56 | | 姓名: 丫丫年薪: 24004.20 | | 姓名: 丁丁年薪: 12004.44 | | 姓名: 星星年薪: 36003.48 | | 姓名: 格格年薪: 48003.96 | | 姓名: 张野年薪: 120001.56 | | 姓名: 程咬金年薪: 240000.00 | | 姓名: 程咬银年薪: 228000.00 | | 姓名: 程咬铜年薪: 216000.00 | | 姓名: 程咬铁年薪: 204000.00 | +-------------------------------------+ 18 rows in set (0.00 sec) 结合CASE语句：(和shell一样的判断语法) mysql> select -> ( -> case -> when emp_name = "jingliyang" then -> emp_name -> when emp_name = "alex" then -> concat(emp_name,"_bigsb") -> else -> concat(emp_name,"sb") -> end -> ) as new_name -> from employee; +-------------+ | new_name | +-------------+ | egonsb | | alex_bigsb | | wupeiqisb | | yuanhaosb | | liwenzhousb | | jingliyang | | jinxinsb | | 成龙sb | | 歪歪sb | | 丫丫sb | | 丁丁sb | | 星星sb | | 格格sb | | 张野sb | | 程咬金sb | | 程咬银sb | | 程咬铜sb | | 程咬铁sb | +-------------+ 18 rows in set (0.01 sec) 简单的select语句 select * from 表 select 字段 from 表 select 字段1，字段2 from 表 select 字段1，字段2 as 别名 from 表 select 字段1，字段2 别名 from 表 select 字段1，字段2*12 别名 from 表 select 字段1，concat('你想拼接的字符串',字段2*12) 别名 from 表
小练习：
1 查出所有员工的名字，薪资,格式为 <名字:egon> <薪资:3000> 2 查出所有的岗位（去掉重复） 3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
mysql> select concat("<名字:",emp_name,">") as 名字,concat("<薪资:",salary,">") as 薪资 from employee; +---------------------+---------------------+ | 名字 | 薪资 | +---------------------+---------------------+ | <名字:egon> | <薪资:7300.33> | | <名字:alex> | <薪资:1000000.31> | | <名字:wupeiqi> | <薪资:8300.00> | | <名字:yuanhao> | <薪资:3500.00> | | <名字:liwenzhou> | <薪资:2100.00> | | <名字:jingliyang> | <薪资:9000.00> | | <名字:jinxin> | <薪资:30000.00> | | <名字:成龙> | <薪资:10000.00> | | <名字:歪歪> | <薪资:3000.13> | | <名字:丫丫> | <薪资:2000.35> | | <名字:丁丁> | <薪资:1000.37> | | <名字:星星> | <薪资:3000.29> | | <名字:格格> | <薪资:4000.33> | | <名字:张野> | <薪资:10000.13> | | <名字:程咬金> | <薪资:20000.00> | | <名字:程咬银> | <薪资:19000.00> | | <名字:程咬铜> | <薪资:18000.00> | | <名字:程咬铁> | <薪资:17000.00> | +---------------------+---------------------+ 18 rows in set (0.00 sec) 2.查出所有岗位（去掉重复） mysql> select distinct post from employee; +-----------------------------------------+ | post | +-----------------------------------------+ | 老男孩驻沙河办事处外交大使 | | teacher | | sale | | operation | +-----------------------------------------+ 4 rows in set (0.00 sec) 3. 查出所有员工名字，以及年薪，年薪的字段名为annual_year mysql> select emp_name,salary*12 as annual_year from employee; +------------+-------------+ | emp_name | annual_year | +------------+-------------+ | egon | 87603.96 | | alex | 12000003.72 | | wupeiqi | 99600.00 | | yuanhao | 42000.00 | | liwenzhou | 25200.00 | | jingliyang | 108000.00 | | jinxin | 360000.00 | | 成龙 | 120000.00 | | 歪歪 | 36001.56 | | 丫丫 | 24004.20 | | 丁丁 | 12004.44 | | 星星 | 36003.48 | | 格格 | 48003.96 | | 张野 | 120001.56 | | 程咬金 | 240000.00 | | 程咬银 | 228000.00 | | 程咬铜 | 216000.00 | | 程咬铁 | 204000.00 | +------------+-------------+ 18 rows in set (0.00 sec)
where 条件
where 字句中可以使用：
1. 比较运算符：> < >= <= <> != 2. between 80 and 100 值在80到100之间 3. in(80,90,100) 值是80或90或100 4. like 'e%' 通配符可以是%或_， %表示任意多字符 _表示一个字符 5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询，查看销售部门所有人的名字（想查看的是名字，条件:部门=销售） mysql> select emp_name from employee where post="sale"; #2:多条件查询,查看讲师中工资大于10000的（条件一：讲师，条件二：大于10000） mysql> select emp_name,salary from employee -> where post="teacher" and salary>10000; #3:关键字BETWEEN AND, salary列中10000到20000之间的 mysql> select emp_name,salary from employee -> where salary between 10000 and 20000; #4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) mysql> select emp_name,post_comment from employee -> where post_comment is null; +------------+--------------+ | emp_name | post_comment | +------------+--------------+ | egon | NULL | | alex | NULL | | wupeiqi | NULL | | yuanhao | NULL | | liwenzhou | NULL | | jingliyang | NULL | | jinxin | NULL | | 成龙 | NULL | | 歪歪 | NULL | | 丫丫 | NULL | | 丁丁 | NULL | | 星星 | NULL | | 格格 | NULL | | 张野 | NULL | | 程咬金 | NULL | | 程咬银 | NULL | | 程咬铜 | NULL | | 程咬铁 | NULL | +------------+--------------+ 18 rows in set (0.00 sec) mysql> select emp_name,post_comment from employee -> where post_comment is not null; Empty set (0.00 sec) mysql> update employee set post_comment="" where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 ''是空字符串，不是null mysql> select emp_name,post_comment from employee where post_comment=""; +----------+--------------+ | emp_name | post_comment | +----------+--------------+ | alex | | +----------+--------------+ 1 row in set (0.00 sec) #5:关键字IN集合查询 mysql> SELECT emp_name,salary FROM employee -> WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; mysql> select emp_name,salary from employee -> where salary in (3000,3500,4000,9000); mysql> select emp_name,salary from employee -> where salary not in (3000,3500,4000,9000); #6:关键字LIKE模糊查询 mysql> #通配符'%' mysql> select * from employee -> where emp_name like 'eg%'; +----+----------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+----------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | +----+----------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ 1 row in set (0.00 sec) mysql> #通配符'_' mysql> select * from employee -> where emp_name like 'al__'; +----+----------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+----------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | +----+----------+------+-----+------------+---------+--------------+------------+--------+-----------+ 1 row in set (0.00 sec)
小练习：
1. 查看岗位是teacher的员工姓名、年龄 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资 4. 查看岗位描述不为NULL的员工信息 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
mysql> select emp_name,age from employee where post='teacher'; mysql> select emp_name,age from employee -> where post='teacher' and age > 30; mysql> select emp_name,age,salary from employee -> where post='teacher' and salary between 9000 and 10000; mysql> select * from employee -> where post_comment is not null; mysql> select emp_name,age,salary from employee -> where post='teacher' and salary in (9000,10000,30000); mysql> select emp_name,age,salary from employee -> where post='teacher' and salary not in (9000,10000,30000); mysql> select emp_name,salary*12 as '年薪' from employee -> where post='teacher' and emp_name like 'jin%';
group by
单独使用GROUP BY关键字分组 SELECT post FROM employee GROUP BY post; 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数 GROUP BY关键字和GROUP_CONCAT()函数一起使用 SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名 SELECT post,GROUP_CONCAT(emp_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. 查询岗位名以及岗位包含的所有员工名字 2. 查询岗位名以及各岗位内包含的员工个数 3. 查询公司内男员工和女员工的个数 4. 查询岗位名以及各岗位的平均薪资 5. 查询岗位名以及各岗位的最高薪资 6. 查询岗位名以及各岗位的最低薪资 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
#1. 对部门字段分组 mysql> select post,group_concat(emp_name) from employee group by post; +-----------------------------------------+---------------------------------------------------------+ | post | group_concat(emp_name) | +-----------------------------------------+---------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | | 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+---------------------------------------------------------+ 4 rows in set (0.00 sec) #2. mysql> select post,count(emp_name) from employee group by post; +-----------------------------------------+-----------------+ | post | count(emp_name) | +-----------------------------------------+-----------------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-----------------+ 4 rows in set (0.00 sec) 3. 对sex字段分组 mysql> select sex,count(sex) from employee group by sex; +--------+------------+ | sex | count(sex) | +--------+------------+ | male | 10 | | female | 8 | +--------+------------+ 2 rows in set (0.00 sec) 4. 根据部门分组, 使用聚合函数统计平均值 mysql> select post,avg(salary) from employee group by post; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 老男孩驻沙河办事处外交大使 | 7300.330000 | +-----------------------------------------+---------------+ 4 rows in set (0.00 sec) 5. 一样的操作, 使用 max()函数 mysql> select post,max(salary) from employee group by post; +-----------------------------------------+-------------+ | post | max(salary) | +-----------------------------------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ 4 rows in set (0.00 sec) 6.一样的操作,使用min()函数 mysql> select post,min(salary) from employee group by post; +-----------------------------------------+-------------+ | post | min(salary) | +-----------------------------------------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ 4 rows in set (0.00 sec) 7. 根据性别分组,是avg()函数统计 mysql> select sex,avg(salary) as '平均薪资' from employee group by sex; +--------+---------------+ | sex | 平均薪资 | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+ 2 rows in set (0.00 sec)
HAVING过滤
HAVING与WHERE不一样的地方在于!!!!!!
#！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
# having 和group by不能分开用。 先分组，再过滤
小练习：
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 3. 查询各岗位平均薪资大于10000的岗位名、平均工资 4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
#1. 先分组，在过滤。 根据部门分组,分组后过滤掉部门中人数大于2的。 mysql> select post,group_concat(emp_name) as '员工名字',count(emp_name) as '员工个数' from employee group by post having count(emp_name) < 2; +-----------------------------------------+--------------+--------------+ | post | 员工名字 | 员工个数 | +-----------------------------------------+--------------+--------------+ | 老男孩驻沙河办事处外交大使 | egon | 1 | +-----------------------------------------+--------------+--------------+ 1 row in set (0.00 sec) #2. 先分组，在过滤。一样的 mysql> select post,avg(salary) as "平均工资" from employee group by post having avg(salary) > 10000; +-----------+---------------+ | post | 平均工资 | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 2 rows in set (0.00 sec) #3.先分组,在过滤。多条件过滤 mysql> select post,avg(salary) as "平均工资" from employee group by post having avg(salary) > 10000 and avg(salary) < 20000; +-----------+--------------+ | post | 平均工资 | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ 1 row in set (0.00 sec) # 或者使用between关键字 mysql> select post,avg(salary) as "平均工资" from employee group by post having avg(salary) between 10000 and 20000; +-----------+--------------+ | post | 平均工资 | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ 1 row in set (0.00 sec)
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. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
mysql> select * from employee order by age,hire_date desc; #2. 默认升序 mysql> select post,avg(salary) from employee -> group by post # 按照部门分组 -> having avg(salary) > 10000 # 过滤掉工资小于10000的 -> order by avg(salary); # 根据工资字段排序 +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 2 rows in set (0.00 sec) #3. 降序 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+ 2 rows in set (0.00 sec)
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. 分页显示，每页5条
mysql> select * from employee order by salary desc limit 0,5; +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec) mysql> select * from employee order by salary desc limit 5,5; +----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec) mysql> select * from employee order by salary desc limit 10,5; +----+----------+--------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+----------+--------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | +----+----------+--------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ 5 rows in set (0.00 sec) mysql> select * from employee order by salary desc limit 15,5; +----+-----------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | +----+-----------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 3 rows in set (0.00 sec) mysql> select * from employee order by salary desc limit 20,5; Empty set (0.00 sec)
使用正则表达式查询
SELECT * FROM employee WHERE emp_name REGEXP '^ale'; SELECT * FROM employee WHERE emp_name REGEXP 'on$'; SELECT * FROM employee WHERE emp_name REGEXP 'm{2}'; 小结：对字符串匹配的方式 WHERE emp_name = 'egon'; WHERE emp_name LIKE 'yua%'; WHERE emp_name REGEXP 'on$';
小练习：
查看所有员工中名字是jin开头，n或者g结尾的员工信息
mysql> select * from employee where emp_name regexp '^jin.*[n|g]$';
浙公网安备 33010602011771号