Mysql单表查询
单表查询
语法：
一、单表查询的语法 SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
创建一张员工表
create table t1( id int primary key 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), salary double(15,2), depart_id int ); [root@db1 11:38:48]:>desc t1; +-----------+-----------------------+------+-----+---------+----------------+ | 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 | | | salary | double(15,2) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +-----------+-----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) #插入记录 #三个部门：运维，开发，前端 insert into t1(name ,sex,age,hire_date,post,salary,depart_id) values ('张三','male',18,'20180301','exploit',7300.33,1), ('李四','male',78,'20160302','exploit',1000000.31,1), ('王五','male',81,'20130305','exploit',8300,1), ('二麻子','male',73,'20140701','exploit',13500,1), ('东哥','male',28,'20121101','exploit',12100,1), ('李静','female',18,'20110211','exploit',9000,1), ('李显','male',18,'19900301','exploit',30000,1), ('峰哥','male',48,'20111111','OPS',10000,1), ('马登','female',48,'20150311','OPS',6000.13,2), ('冯浩','female',38,'20101101','OPS',7000.35,2), ('李享','female',18,'20110312','OPS',8000.37,2), ('彭峰','female',18,'20160513','OPS',9000.29,2), ('王伟','female',28,'20170127','OPS',11000.33,2), ('姬野','male',28,'20160311','front-end',10000.13,3), ('胡三','male',18,'19970312','front-end',20000,3), ('李东雨','female',18,'20130311','front-end',19000,3), ('夏雨','male',18,'20150411','front-end',18000,3), ('夏峰','female',18,'20140512','front-end',17000,3);
where 约束
where子句中可以使用 1.比较运算符：>、<、>=、<=、<>、!= 2.between 80 and 100 ：值在80到100之间 3.in(80,90,100)值是10或20或30 4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符，_表示一个字符 5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
- 单条件查询
[root@db1 12:04:08]:>select id,name from t1 where id>10; +----+-----------+ | id | name | +----+-----------+ | 11 | 李享 | | 12 | 彭峰 | | 13 | 王伟 | | 14 | 姬野 | | 15 | 胡三 | | 16 | 李东雨 | | 17 | 夏雨 | | 18 | 夏峰 | +----+-----------+ 8 rows in set (0.00 sec)
- 多条件查询
[root@db1 12:06:17]:>select name from t1 where post='OPS' and salary>8000; +--------+ | name | +--------+ | 峰哥 | | 李享 | | 彭峰 | | 王伟 | +--------+ 4 rows in set (0.00 sec)
关键字IN集合查询
[root@db1 12:11:23]:>select name,salary from t1 where salary=8000 or salary=9000 or salary=10000; +--------+----------+ | name | salary | +--------+----------+ | 李静 | 9000.00 | | 峰哥 | 10000.00 | +--------+----------+ 2 rows in set (0.00 sec) [root@db1 12:11:38]:>select name,salary from t1 where salary in (8000,9000,10000); +--------+----------+ | name | salary | +--------+----------+ | 李静 | 9000.00 | | 峰哥 | 10000.00 | +--------+----------+ 2 rows in set (0.00 sec) [root@db1 12:13:13]:>select name,salary from t1 where salary not in (8000,9000,10000); +-----------+------------+ | name | salary | +-----------+------------+ | 张三 | 7300.33 | | 李四 | 1000000.31 | | 王五 | 8300.00 | | 二麻子 | 13500.00 | | 东哥 | 12100.00 | | 李显 | 30000.00 | | 马登 | 6000.13 | | 冯浩 | 7000.35 | | 李享 | 8000.37 | | 彭峰 | 9000.29 | | 王伟 | 11000.33 | | 姬野 | 10000.13 | | 胡三 | 20000.00 | | 李东雨 | 19000.00 | | 夏雨 | 18000.00 | | 夏峰 | 17000.00 | +-----------+------------+ 16 rows in set (0.00 sec)
关键字LIKE模糊查询
[root@db1 12:13:53]:>select * from t1 where name like '李%'; +----+-----------+--------+-----+------------+-----------+------------+-----------+ | id | name | sex | age | hire_date | post | salary | depart_id | +----+-----------+--------+-----+------------+-----------+------------+-----------+ | 2 | 李四 | male | 78 | 2016-03-02 | exploit | 1000000.31 | 1 | | 6 | 李静 | female | 18 | 2011-02-11 | exploit | 9000.00 | 1 | | 7 | 李显 | male | 18 | 1990-03-01 | exploit | 30000.00 | 1 | | 11 | 李享 | female | 18 | 2011-03-12 | OPS | 8000.37 | 2 | | 16 | 李东雨 | female | 18 | 2013-03-11 | front-end | 19000.00 | 3 | +----+-----------+--------+-----+------------+-----------+------------+-----------+
group by 分组查询
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 #2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等 #3、为何要分组呢？取每个部门的最高工资、取每个部门的员工数、取男人数和女人数 #小窍门：‘每’这个字后面的字段，就是我们分组的依据 #4、大前提： #可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
当执行以下sql语句的时候，是以post字段查询了组中的第一条数据，没有任何意义，因为我们现在想查出当前组的多条记录。
[root@db1 12:14:58]:>select * from t1 group by post; +----+--------+------+-----+------------+-----------+----------+-----------+ | id | name | sex | age | hire_date | post | salary | depart_id | +----+--------+------+-----+------------+-----------+----------+-----------+ | 1 | 张三 | male | 18 | 2018-03-01 | exploit | 7300.33 | 1 | | 14 | 姬野 | male | 28 | 2016-03-11 | front-end | 10000.13 | 3 | | 8 | 峰哥 | male | 48 | 2011-11-11 | OPS | 10000.00 | 1 | +----+--------+------+-----+------------+-----------+----------+-----------+
由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的。如果想分组，则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY
mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) #查看MySQL 5.7默认的sql_mode如下： mysql> select @@global.sql_mode; +--------------------+ | @@global.sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ 1 row in set (0.00 sec) mysql> exit;#设置成功后，一定要退出，然后重新登录方可生效 Bye
继续验证通过group by分组之后，只能查看当前字段,如果想查看组内信息，需要借助于聚合函数
[root@db1 12:22:27]:>select * from t1 group by post; ERROR 1055 (42000): 'db01.t1.id' isn't in GROUP BY [root@db1 12:22:34]:>select post from t1 group by post; +-----------+ | post | +-----------+ | exploit | | front-end | | OPS | +-----------+ 3 rows in set (0.00 sec)
聚合函数
max()求最大值 min()求最小值 avg()求平均值 sum() 求和 count() 求总个数 #强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 # 每个部门有多少个员工 select post,count(id) from t1 group by post; # 每个部门的最高薪水 select post,max(salary) from t1 group by post; # 每个部门的最低薪水 select post,min(salary) from t1 group by post; # 每个部门的平均薪水 select post,avg(salary) from t1 group by post; # 每个部门的所有薪水 select post,sum(age) from t1 group by post;
HAVING过滤
#HAVING与WHERE不一样的地方在于 #执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数 [root@db1 12:36:06]:>select * from t1 having salary>30000; ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause [root@db1 12:36:49]:>select post,group_concat(name) from t1 group by post having salary > 30000; ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
查询各岗位内包含的员工个数小于6的岗位名、岗位内包含员工名字、个数
[root@db1 01:02:04]:>select post,group_concat(name),count(id) from t1 group by post; +-----------+-----------------------------------------------------+-----------+ | post | group_concat(name) | count(id) | +-----------+-----------------------------------------------------+-----------+ | exploit | 张三,李显,李静,东哥,二麻子,王五,李四 | 7 | | front-end | 夏雨,李东雨,胡三,姬野,夏峰 | 5 | | OPS | 彭峰,王伟,李享,马登,峰哥,冯浩 | 6 | +-----------+-----------------------------------------------------+-----------+ 3 rows in set (0.00 sec) [root@db1 01:01:59]:>select post,group_concat(name),count(id) from t1 group by post having count(id)<6; +-----------+---------------------------------------+-----------+ | post | group_concat(name) | count(id) | +-----------+---------------------------------------+-----------+ | front-end | 夏雨,李东雨,胡三,姬野,夏峰 | 5 | +-----------+---------------------------------------+-----------+ 1 row in set (0.00 sec)
查询各岗位平均薪资大于10000的岗位名、平均工资
[root@db1 01:03:03]:>select post,avg(salary) from t1 group by post having avg(salary) > 10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | exploit | 154314.377143 | | front-end | 16800.026000 | +-----------+---------------+ 2 rows in set (0.00 sec)
查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
[root@db1 01:04:01]:>select post,avg(salary) from t1 group by post having avg(salary) > 10000 and avg(salary) <20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | front-end | 16800.026000 | +-----------+--------------+ 1 row in set (0.00 sec)
order by 查询排序
按单列排序
select * from t1 order by age; select * from t1 order by age ASC; //升序 select * from t1 order by age DESC; //降序
按多列排序:先按照age升序排序，如果年纪相同，则按照id降序
select * from t1 order by age ASC,id DESC; +----+-----------+--------+-----+------------+-----------+------------+-----------+ | id | name | sex | age | hire_date | post | salary | depart_id | +----+-----------+--------+-----+------------+-----------+------------+-----------+ | 18 | 夏峰 | female | 18 | 2014-05-12 | front-end | 17000.00 | 3 | | 17 | 夏雨 | male | 18 | 2015-04-11 | front-end | 18000.00 | 3 | | 16 | 李东雨 | female | 18 | 2013-03-11 | front-end | 19000.00 | 3 | | 15 | 胡三 | male | 18 | 1997-03-12 | front-end | 20000.00 | 3 | | 12 | 彭峰 | female | 18 | 2016-05-13 | OPS | 9000.29 | 2 | | 11 | 李享 | female | 18 | 2011-03-12 | OPS | 8000.37 | 2 | | 7 | 李显 | male | 18 | 1990-03-01 | exploit | 30000.00 | 1 | | 6 | 李静 | female | 18 | 2011-02-11 | exploit | 9000.00 | 1 | | 1 | 张三 | male | 18 | 2018-03-01 | exploit | 7300.33 | 1 | | 14 | 姬野 | male | 28 | 2016-03-11 | front-end | 10000.13 | 3 | | 13 | 王伟 | female | 28 | 2017-01-27 | OPS | 11000.33 | 2 | | 5 | 东哥 | male | 28 | 2012-11-01 | exploit | 12100.00 | 1 | | 10 | 冯浩 | female | 38 | 2010-11-01 | OPS | 7000.35 | 2 | | 9 | 马登 | female | 48 | 2015-03-11 | OPS | 6000.13 | 2 | | 8 | 峰哥 | male | 48 | 2011-11-11 | OPS | 10000.00 | 1 | | 4 | 二麻子 | male | 73 | 2014-07-01 | exploit | 13500.00 | 1 | | 2 | 李四 | male | 78 | 2016-03-02 | exploit | 1000000.31 | 1 | | 3 | 王五 | male | 81 | 2013-03-05 | exploit | 8300.00 | 1 | +----+-----------+--------+-----+------------+-----------+------------+-----------+
查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
[root@db1 01:22:18]:>select post,avg(salary) from t1 group by post having avg(salary) > 10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | exploit | 154314.377143 | | front-end | 16800.026000 | +-----------+---------------+ 2 rows in set (0.00 sec)
查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
[root@db1 01:22:26]:>select post,avg(salary) from t1 group by post having avg(salary) > 10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | exploit | 154314.377143 | | front-end | 16800.026000 | +-----------+---------------+
limit 限制查询的记录数
[root@db1 01:25:18]:>select * from t1 limit 3; //查询前三条 +----+--------+------+-----+------------+---------+------------+-----------+ | id | name | sex | age | hire_date | post | salary | depart_id | +----+--------+------+-----+------------+---------+------------+-----------+ | 1 | 张三 | male | 18 | 2018-03-01 | exploit | 7300.33 | 1 | | 2 | 李四 | male | 78 | 2016-03-02 | exploit | 1000000.31 | 1 | | 3 | 王五 | male | 81 | 2013-03-05 | exploit | 8300.00 | 1 | +----+--------+------+-----+------------+---------+------------+-----------+ 3 rows in set (0.00 sec)
分页显示，每页5条
[root@db1 01:27:58]:>select * from t1 limit 0,5; +----+-----------+------+-----+------------+---------+------------+-----------+ | id | name | sex | age | hire_date | post | salary | depart_id | +----+-----------+------+-----+------------+---------+------------+-----------+ | 1 | 张三 | male | 18 | 2018-03-01 | exploit | 7300.33 | 1 | | 2 | 李四 | male | 78 | 2016-03-02 | exploit | 1000000.31 | 1 | | 3 | 王五 | male | 81 | 2013-03-05 | exploit | 8300.00 | 1 | | 4 | 二麻子 | male | 73 | 2014-07-01 | exploit | 13500.00 | 1 | | 5 | 东哥 | male | 28 | 2012-11-01 | exploit | 12100.00 | 1 | +----+-----------+------+-----+------------+---------+------------+-----------+ 5 rows in set (0.00 sec) [root@db1 01:28:05]:>select * from t1 limit 5,5; +----+--------+--------+-----+------------+---------+----------+-----------+ | id | name | sex | age | hire_date | post | salary | depart_id | +----+--------+--------+-----+------------+---------+----------+-----------+ | 6 | 李静 | female | 18 | 2011-02-11 | exploit | 9000.00 | 1 | | 7 | 李显 | male | 18 | 1990-03-01 | exploit | 30000.00 | 1 | | 8 | 峰哥 | male | 48 | 2011-11-11 | OPS | 10000.00 | 1 | | 9 | 马登 | female | 48 | 2015-03-11 | OPS | 6000.13 | 2 | | 10 | 冯浩 | female | 38 | 2010-11-01 | OPS | 7000.35 | 2 | +----+--------+--------+-----+------------+---------+----------+-----------+ 5 rows in set (0.00 sec) [root@db1 01:28:17]:>select * from t1 limit 10,5; +----+--------+--------+-----+------------+-----------+----------+-----------+ | id | name | sex | age | hire_date | post | salary | depart_id | +----+--------+--------+-----+------------+-----------+----------+-----------+ | 11 | 李享 | female | 18 | 2011-03-12 | OPS | 8000.37 | 2 | | 12 | 彭峰 | female | 18 | 2016-05-13 | OPS | 9000.29 | 2 | | 13 | 王伟 | female | 28 | 2017-01-27 | OPS | 11000.33 | 2 | | 14 | 姬野 | male | 28 | 2016-03-11 | front-end | 10000.13 | 3 | | 15 | 胡三 | male | 18 | 1997-03-12 | front-end | 20000.00 | 3 | +----+--------+--------+-----+------------+-----------+----------+-----------+ 5 rows in set (0.00 sec)
浙公网安备 33010602011771号