单表查询的语法
SELECT distinct 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
# distinct表示结果 去重
# mysql ｓｑｌ语句不区分大小写。
关键字的执行优先级(重点)
重点中的重点：关键字的执行优先级 from 找到表:from where 拿着where指定的约束条件，去文件/表中取出一条条记录 group by 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 having 将分组的结果进行having过滤 select 执行select distinct 去重 order by 将结果按条件排序：order by limit 限制结果的显示条数
简单查询
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, 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;
操作过程
mysql> create table employee( -> id int not null unique auto_increment, -> 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;; Query OK, 0 rows affected (0.02 sec) mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | 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 | | +--------------+-----------------------+------+-----+---------+----------------+ 10 rows in set (0.00 sec)
#插入记录 #三个部门：教学，销售，运营 insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('leco','male',18,'20170301','teacher',7300.33,401,1), #以下是教学部 ('loocha','male',78,'20150302','teacher',1000000.31,401,1), ('odes','male',81,'20130305','teacher',8300,401,1), ('yunyun','male',73,'20140701','teacher',3500,401,1), ('cmz','male',28,'20121101','teacher',2100,401,1), ('zhangsan','female',18,'20110211','teacher',9000,401,1), ('zhaoliu','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> insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values -> ('leco','male',18,'20170301','teacher',7300.33,401,1), #以下是教学部 -> ('loocha','male',78,'20150302','teacher',1000000.31,401,1), -> ('odes','male',81,'20130305','teacher',8300,401,1), -> ('yunyun','male',73,'20140701','teacher',3500,401,1), -> ('cmz','male',28,'20121101','teacher',2100,401,1), -> ('zhangsan','female',18,'20110211','teacher',9000,401,1), -> ('zhaoliu','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) -> ; Query OK, 18 rows affected (0.01 sec) Records: 18 Duplicates: 0 Warnings: 0
#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk简单查询
#简单查询 SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee; SELECT * FROM employee; SELECT name,salary FROM employee; #避免重复DISTINCT SELECT DISTINCT post FROM employee; #通过四则运算查询 SELECT name, salary*12 FROM employee; SELECT name, salary*12 AS Annual_salary FROM employee; SELECT name, salary*12 Annual_salary FROM employee; #定义显示格式 CONCAT() 函数用于连接字符串 SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee; CONCAT_WS() 第一个参数为分隔符 SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee;
操作过程
１. SELECT id,post_comment,salary,office,depart_id FROM employee; # 只查询部分字段
mysql> SELECT id,post_comment,salary,office,depart_id FROM employee; +----+--------------+------------+--------+-----------+ | id | post_comment | salary | office | depart_id | +----+--------------+------------+--------+-----------+ | 1 | NULL | 7300.33 | 401 | 1 | | 2 | NULL | 1000000.31 | 401 | 1 | | 3 | NULL | 8300.00 | 401 | 1 | | 4 | NULL | 3500.00 | 401 | 1 | | 5 | NULL | 2100.00 | 401 | 1 | | 6 | NULL | 9000.00 | 401 | 1 | | 7 | NULL | 30000.00 | 401 | 1 | | 8 | NULL | 10000.00 | 401 | 1 | | 9 | NULL | 3000.13 | 402 | 2 | | 10 | NULL | 2000.35 | 402 | 2 | | 11 | NULL | 1000.37 | 402 | 2 | | 12 | NULL | 3000.29 | 402 | 2 | | 13 | NULL | 4000.33 | 402 | 2 | | 14 | NULL | 10000.13 | 403 | 3 | | 15 | NULL | 20000.00 | 403 | 3 | | 16 | NULL | 19000.00 | 403 | 3 | | 17 | NULL | 18000.00 | 403 | 3 | | 18 | NULL | 17000.00 | 403 | 3 | +----+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec)
２. SELECT * FROM employee; # 查询所有，效率低，测试的时候，可以使用。
mysql> SELECT * FROM employee; +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 1 | leco | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 | | 2 | loocha | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | odes | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yunyun | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | cmz | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | zhangsan | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | zhaoliu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 南京 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 贝宁 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 田径 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 东京 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 普京 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张三 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 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 | | 18 | 赵刘 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec)
３. SELECT DISTINCT post FROM employee; #去重复
mysql> SELECT DISTINCT post FROM employee; +-----------+ | post | +-----------+ | teacher | | sale | | operation | +-----------+ 3 rows in set (0.00 sec) ＃没有去重 mysql> SELECT post FROM employee; +-----------+ | post | +-----------+ | teacher | | teacher | | teacher | | teacher | | teacher | | teacher | | teacher | | teacher | | sale | | sale | | sale | | sale | | sale | | operation | | operation | | operation | | operation | | operation | +-----------+ 18 rows in set (0.00 sec)
４.指定四则运算 SELECT name, salary*12 FROM employee;
mysql> SELECT name, salary*12 FROM employee; +----------+-------------+ | name | salary*12 | +----------+-------------+ | leco | 87603.96 | | loocha | 12000003.72 | | odes | 99600.00 | | yunyun | 42000.00 | | cmz | 25200.00 | | zhangsan | 108000.00 | | zhaoliu | 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)
５.重命名 SELECT name, salary*12 AS Annual_salary FROM employee;
或者(不加as)SELECT name, salary*12 Annual_salary FROM employee;
mysql> SELECT name, salary*12 AS Annual_salary FROM employee; +----------+---------------+ | name | Annual_salary | +----------+---------------+ | leco | 87603.96 | | loocha | 12000003.72 | | odes | 99600.00 | | yunyun | 42000.00 | | cmz | 25200.00 | | zhangsan | 108000.00 | | zhaoliu | 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)
6. 定义显示格式 SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee;
CONCAT() 函数用于连接字符串mysql> SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary -> FROM employee; +-------------------------------------+ | Annual_salary | +-------------------------------------+ | 姓名: leco 年薪: 87603.96 | | 姓名: loocha 年薪: 12000003.72 | | 姓名: odes 年薪: 99600.00 | | 姓名: yunyun 年薪: 42000.00 | | 姓名: cmz 年薪: 25200.00 | | 姓名: zhangsan 年薪: 108000.00 | | 姓名: zhaoliu 年薪: 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.01 sec)
CONCAT_WS() 第一个参数为分隔符SELECT CONCAT_WS(':',name,salary*12) AS Annual_salaryFROM employee;
mysql> SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee; +--------------------+ | Annual_salary | +--------------------+ | leco:87603.96 | | loocha:12000003.72 | | odes:99600.00 | | yunyun:42000.00 | | cmz:25200.00 | | zhangsan:108000.00 | | zhaoliu: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)
mysql> select concat(name,':',age) from employee; +----------------------+ | concat(name,':',age) | +----------------------+ | leco:18 | | loocha:78 | | odes:81 | | yunyun:73 | | cmz:28 | | zhangsan:18 | | zhaoliu:18 | | 成龙:48 | | 南京:48 | | 贝宁:38 | | 田径:18 | | 东京:18 | | 普京:28 | | 张三:28 | | 李四:18 | | 赵钱:18 | | 网五:18 | | 赵刘:18 | +----------------------+ 18 rows in set (0.00 sec)
查詢select concat("<名字: ",name,">"), concat("<薪資: ",salary,">") from employee;
mysql> select concat("<名字: ",name,">"), concat("<薪資: ",salary,">") from employee; +------------------------------+--------------------------------+ | concat("<名字: ",name,">") | concat("<薪資: ",salary,">") | +------------------------------+--------------------------------+ | <名字: leco> | <薪資: 7300.33> | | <名字: loocha> | <薪資: 1000000.31> | | <名字: odes> | <薪資: 8300.00> | | <名字: yunyun> | <薪資: 3500.00> | | <名字: cmz> | <薪資: 2100.00> | | <名字: zhangsan> | <薪資: 9000.00> | | <名字: zhaoliu> | <薪資: 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)
WHERE约束
where字句中可以使用：
比较运算符：><>= <= <> != between 80 and 100 值在10到20之间 in(80,90,100) 值是10或20或30 like 'egon%' pattern可以是%或_， %表示任意多字符 _表示一个字符 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
select * from employee where id>7;
mysql> select * from employee where id>7; +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 南京 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 贝宁 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 田径 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 东京 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 普京 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张三 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 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 | | 18 | 赵刘 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 11 rows in set (0.01 sec)
mysql> select id,name,age from employee where id>7; +----+--------+-----+ | id | name | age | +----+--------+-----+ | 8 | 成龙 | 48 | | 9 | 南京 | 48 | | 10 | 贝宁 | 38 | | 11 | 田径 | 18 | | 12 | 东京 | 18 | | 13 | 普京 | 28 | | 14 | 张三 | 28 | | 15 | 李四 | 18 | | 16 | 赵钱 | 18 | | 17 | 网五 | 18 | | 18 | 赵刘 | 18 | +----+--------+-----+ 11 rows in set (0.00 sec)
mysql> select name,age,salary from employee where post='teacher' and salary> 8000; +----------+-----+------------+ | name | age | salary | +----------+-----+------------+ | loocha | 78 | 1000000.31 | | odes | 81 | 8300.00 | | zhangsan | 18 | 9000.00 | | zhaoliu | 18 | 30000.00 | | 成龙 | 48 | 10000.00 | +----------+-----+------------+ 5 rows in set (0.00 sec)
mysql> select name,salary from employee where post='teacher' and salary >=10000 and salary <20000; +--------+----------+ | name | salary | +--------+----------+ | 成龙 | 10000.00 | +--------+----------+ 1 row in set (0.00 sec)
mysql> select name,salary from employee where post='teacher' and salary between 10000 and 20000; +--------+----------+ | name | salary | +--------+----------+ | 成龙 | 10000.00 | +--------+----------+ 1 row in set (0.00 sec)
mysql> select name,salary from employee where post='teacher' and salary >=10000 or salary <=20000; +----------+------------+ | name | salary | +----------+------------+ | leco | 7300.33 | | loocha | 1000000.31 | | odes | 8300.00 | | yunyun | 3500.00 | | cmz | 2100.00 | | zhangsan | 9000.00 | | zhaoliu | 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)
mysql> select name,salary from employee where post='teacher' and salary not between 10000 and 20000; +----------+------------+ | name | salary | +----------+------------+ | leco | 7300.33 | | loocha | 1000000.31 | | odes | 8300.00 | | yunyun | 3500.00 | | cmz | 2100.00 | | zhangsan | 9000.00 | | zhaoliu | 30000.00 | +----------+------------+ 7 rows in set (0.00 sec)
单独某一个，或者某几个
mysql> select * from employee where age=73 or age=28 or age=81; +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 3 | odes | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yunyun | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | cmz | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 13 | 普京 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张三 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec) mysql> select * from employee where age in(73,28,81); +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 3 | odes | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yunyun | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | cmz | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 13 | 普京 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张三 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec)
判断某个字段是否为空 isnull 不能是等于
mysql> select * from employee where post_comment is null; +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 1 | leco | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 | | 2 | loocha | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | odes | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yunyun | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | cmz | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | zhangsan | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | zhaoliu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 南京 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 贝宁 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 田径 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 东京 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 普京 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张三 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 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 | | 18 | 赵刘 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec)
like 模糊匹配
select * from employee where name like 'l%'; # 表示所有不限制个数，类似shell的×
select * from employee where name like 'l_'; # _表示一个字符
mysql> select * from employee where name like 'l%'; +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 1 | leco | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 | | 2 | loocha | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ 2 rows in set (0.00 sec)
mysql> select * from employee where name like 'le__'; +----+------+------+-----+------------+---------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+---------+--------+-----------+ | 1 | leco | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+---------+--------+-----------+ 1 row in set (0.00 sec)
groupby 分组，where之后运行的
一 什么是分组？为什么要分组？
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 #2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等 #3、为何要分组呢？ 取每个部门的最高工资 取每个部门的员工数 取男人数和女人数 小窍门：‘每’这个字后面的字段，就是我们分组的依据 #4、大前提： 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
使用group by 报错
mysql> select * from employee group by post; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db1.employee.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决办法
在mysql 工具 搜索或者插入数据时报下面错误： ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_tl.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 原因： 看一下group by的语法： select 选取分组中的列+聚合函数 from 表名称 group by 分组的列 从语法格式来看，是先有分组，再确定检索的列，检索的列只能在参加分组的列中选。 我当前Mysql版本5.7.17， 再看一下ONLY_FULL_GROUP_BY的意思是：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。 查看mysql版本命令：select version(); 查看sql_model参数命令： SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode; 发现： 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 第一项默认开启ONLY_FULL_GROUP_BY， 解决方法： 1.只选择出现在group by后面的列，或者给列增加聚合函数；（不推荐） 2.命令行输入： set @@GLOBAL.sql_mode=''; set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 默认关掉ONLY_FULL_GROUP_BY！ 这个时候 在用工具select 一下 SELECT @@sql_mode; SELECT @@GLOBAL.sql_mode; 发现已经不存在ONLY_FULL_GROUP_BY ，感觉已经OK。但是如果你重启Mysql服务的话，发现ONLY_FULL_GROUP_BY还是会存在的 想要彻底解决这个问题 就得去改my.ini 配置（如果你们mysql 没有这个文件，就把my-default.ini 改成my.ini，我这个版本就是没有my.ini配置问题） 在 [mysqld]和[mysql]下添加 SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> select * from employee group by post; +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 14 | 张三 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 9 | 南京 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 1 | leco | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 | +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 3 rows in set (0.00 sec)
但是此时还是不是严格分组模式，只是显示每组中的第一个元素。
由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义
mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected, 1 warning (0.00 sec)
分组之后，只能取分组的字段，以及每个组合聚合结果
mysql> quit #设置成功后，一定要退出，然后重新登录方可生效 Bye mysql> use db1; mysql> select post from employee group by post; +-----------+ | post | +-----------+ | operation | | sale | | teacher | +-----------+ 3 rows in set (0.00 sec)
#只能查看分组依据和使用聚合函数
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的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
# 查看组内有多少个人和组内成员 mysql> select count(id),group_concat(post) from employee group by post; +-----------+---------------------------------------------------------+ | count(id) | group_concat(post) | +-----------+---------------------------------------------------------+ | 5 | operation,operation,operation,operation,operation | | 5 | sale,sale,sale,sale,sale | | 7 | teacher,teacher,teacher,teacher,teacher,teacher,teacher | | 1 | 老男孩驻沙河办事处外交大使 | +-----------+---------------------------------------------------------+ 4 rows in set (0.00 sec)
聚合函数
max
min
avg
sum
count
mysq
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 示例： 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;
分组
l> select post,count(id) from employee group by post; +-----------+-----------+ | post | count(id) | +-----------+-----------+ | operation | 5 | | sale | 5 | | teacher | 8 | +-----------+-----------+ 3 rows in set (0.00 sec) mysql> select post,count(id) emp_count from employee group by post; +-----------+-----------+ | post | emp_count | +-----------+-----------+ | operation | 5 | | sale | 5 | | teacher | 8 | +-----------+-----------+ 3 rows in set (0.00 sec)
#没有groupby则默认整体算作一组
取出员工所有的最高工资
mysql> select max(salary) from employee; +-------------+ | max(salary) | +-------------+ | 1000000.31 | +-------------+ 1 row in set (0.00 sec)
查看部门的所有员工信息 group_concat
#各个部门的所有 员工成员名字group_concat后面加什么就显示什么
mysql> select post,group_concat(name) from employee group by post; +-----------+-----------------------------------------------------+ | post | group_concat(name) | +-----------+-----------------------------------------------------+ | operation | 赵刘,网五,赵钱,李四,张三 | | sale | 贝宁,普京,东京,田径,南京 | | teacher | leco,成龙,zhaoliu,zhangsan,cmz,yunyun,odes,loocha | +-----------+-----------------------------------------------------+ 3 rows in set (0.00 sec)
1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资1. 查询岗位名以及岗位包含的所有员工名字 select post,group_concat(name) from employee group by post; mysql> select post,group_concat(name) from employee group by post; +-----------+-----------------------------------------------------+ | post | group_concat(name) | +-----------+-----------------------------------------------------+ | operation | 赵刘,网五,赵钱,李四,张三 | | sale | 贝宁,普京,东京,田径,南京 | | teacher | leco,成龙,zhaoliu,zhangsan,cmz,yunyun,odes,loocha | +-----------+-----------------------------------------------------+ 3 rows in set (0.00 sec) 2. 查询岗位名以及各岗位内包含的员工个数，且年龄大于20岁 select post,count(id) from employee where age >20 group by post; mysql> select post,count(id) from employee where age >20 group by post; +-----------+-----------+ | post | count(id) | +-----------+-----------+ | operation | 1 | | sale | 3 | | teacher | 5 | +-----------+-----------+ 3 rows in set (0.36 sec) 3. 查询公司内男员工和女员工的个数 select sex,count(id) from employee group by sex; mysql> select sex,count(id) from employee group by sex; +--------+-----------+ | sex | count(id) | +--------+-----------+ | male | 10 | | female | 8 | +--------+-----------+ 2 rows in set (0.00 sec) 4. 查询岗位名以及各岗位的平均薪资 select post,avg(salary) from employee group by post; mysql> select post,avg(salary) from employee group by post; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 133775.080000 | +-----------+---------------+ 3 rows in set (0.00 sec) s 5. 查询岗位名以及各岗位的最高薪资 select post,max(salary) from employee group by post; mysql> select post,max(salary) from employee group by post; +-----------+-------------+ | post | max(salary) | +-----------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | +-----------+-------------+ 3 rows in set (0.00 sec) 6. 查询岗位名以及各岗位的最低薪资 select post,min(salary) from employee group by post; mysql> select post,min(salary) from employee group by post; +-----------+-------------+ | post | min(salary) | +-----------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | +-----------+-------------+ 3 rows in set (0.00 sec) 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 select sex,avg(salary) from employee group by sex; mysql> select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+ 2 rows in set (0.01 sec)
HAVING过滤
HAVING与WHERE区别
#！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
1. 查询各岗位内包含的员工个数小于7的岗位名、岗位内包含员工名字、个数 select post,group_concat(name),count(id) from employee group by post; select post,group_concat(name),count(id) from employee group by post having count(id) < 7; mysql> select post,group_concat(name),count(id) from employee group by post; +-----------+-----------------------------------------------------+-----------+ | post | group_concat(name) | count(id) | +-----------+-----------------------------------------------------+-----------+ | operation | 赵刘,网五,赵钱,李四,张三 | 5 | | sale | 贝宁,普京,东京,田径,南京 | 5 | | teacher | leco,成龙,zhaoliu,zhangsan,cmz,yunyun,odes,loocha | 8 | +-----------+-----------------------------------------------------+-----------+ 3 rows in set (0.00 sec) mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 7; +-----------+------------------------------------+-----------+ | post | group_concat(name) | count(id) | +-----------+------------------------------------+-----------+ | operation | 赵刘,网五,赵钱,李四,张三 | 5 | | sale | 贝宁,普京,东京,田径,南京 | 5 | +-----------+------------------------------------+-----------+ 2 rows in set (0.00 sec) 3. 查询各岗位平均薪资大于10000的岗位名、平均工资 select post,avg(salary) from employee group by post having avg(salary) > 10000; mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 133775.080000 | +-----------+---------------+ 2 rows in set (0.00 sec) 4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary)<20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ 1 row in set (0.00 sec) mysql> select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | 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降序排序 select * from employee order by age asc; #升序 select * from employee order by age; #默认是升序 mysql> select * from employee order by age asc; +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 18 | 赵刘 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | 网五 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 赵钱 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 6 | zhangsan | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | zhaoliu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 15 | 李四 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 1 | leco | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 | | 11 | 田径 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 东京 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 5 | cmz | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 14 | 张三 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 13 | 普京 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 10 | 贝宁 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 南京 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 4 | yunyun | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 2 | loocha | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | odes | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) mysql> select * from employee order by age; +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 18 | 赵刘 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | 网五 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 赵钱 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 6 | zhangsan | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | zhaoliu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 15 | 李四 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 1 | leco | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 | | 11 | 田径 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 东京 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 5 | cmz | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 14 | 张三 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 13 | 普京 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 10 | 贝宁 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 南京 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 4 | yunyun | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 2 | loocha | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | odes | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) mysql> select * from employee order by age asc, id desc; # 是按照age升序排，如果age相同按照id降序排 +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 18 | 赵刘 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | 网五 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 赵钱 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 李四 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 12 | 东京 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 11 | 田径 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 7 | zhaoliu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 6 | zhangsan | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 1 | leco | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 | | 14 | 张三 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 13 | 普京 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 5 | cmz | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 贝宁 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 南京 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 4 | yunyun | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 2 | loocha | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | odes | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc; mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 133775.080000 | +-----------+---------------+ 2 rows in set (0.00 sec) 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc; mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | teacher | 133775.080000 | | 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条
# 工资最高的那一位 select * from employee order by salary desc limit 1; mysql> select * from employee order by salary desc limit 1; +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | loocha | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ 1 row in set (0.00 sec) # 工资最高的 select * from employee order by salary desc limit 1; mysql> select * from employee order by salary desc limit 1; +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | loocha | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ 1 row in set (0.00 sec) select * from employee limit 0,5; # 取1到5条信息 mysql> select * from employee limit 0,5; +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 1 | leco | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 | | 2 | loocha | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | odes | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yunyun | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | cmz | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+--------+------+-----+------------+---------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec)
正则表达式
# 正则表达式
select * from employee where name regexp "^zh"; # 查找zh开头的 select * from employee where name regexp '^cmz'; #cmz开头 select * from employee where name regexp 'es$'; #es结尾 select * from employee where name regexp 'm{2}'; select * from employee where name regexp "^zh.*(san|u)$"; #zh开头，san或者u结尾 查看所有员工中名字是jin开头，n或者g结果的员工信息 select * from employee where name regexp '^jin.*[gn]$';
mysql> select * from employee where name regexp "^zh"; +----+----------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+----------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | zhangsan | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | zhaoliu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | +----+----------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 2 rows in set (0.00 sec) mysql> select * from employee where name regexp "^zh.*(san|u)$"; #zh开头，san或者u结尾 +----+----------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+----------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | zhangsan | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | zhaoliu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | +----+----------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 2 rows in set (0.00 sec)