查询顺序和多表查询
查询顺序和多表查询
SQL语句中的查询操作是最复杂的也是我们工作中接触最多的, 所以正确的掌握sql语句的查询就显得非常重要. 下面是拿这些记录来说明sql语句的各个关键字的执行顺序.
0. 表的创建和数据的准备
create table emp( id int primary key auto_increment, name varchar(50) not null, sex enum('male', 'female') default 'male' not null, 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 );insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), ('egon','male',78,'20150302','teacher',1000000.31,401,1), ('kevin','male',81,'20130305','teacher',8300,401,1), ('tank','male',73,'20140701','teacher',3500,401,1), ('owen','male',28,'20121101','teacher',2100,401,1), ('jerry','female',18,'20110211','teacher',9000,401,1), ('nick','male',18,'19000301','teacher',30000,401,1), ('sean','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);
1. 查询顺序
我们可以知道mysql语句的执行顺序是先执行后面筛选的语句, 最后才执行前面的select语句.
where:
mysql中的where语句是对from (table) 拿到的所有的数据做一个初步的筛选. 例如下面
查询id大于等于3小于等于6的数据, 下面两种写法是等价的
mysql:db_8_21> select * from emp where id >= 3 and id <= 6; select * from emp where id between 3 and 6; +----+-------+--------+-----+------------+---------+--------------+--------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+--------+-----+------------+---------+--------------+--------+--------+-----------+ | 3 | kevin | male | 81 | 2013-03-05 | teacher | <null> | 8300.0 | 401 | 1 | | 4 | tank | male | 73 | 2014-07-01 | teacher | <null> | 3500.0 | 401 | 1 | | 5 | owen | male | 28 | 2012-11-01 | teacher | <null> | 2100.0 | 401 | 1 | | 6 | jerry | female | 18 | 2011-02-11 | teacher | <null> | 9000.0 | 401 | 1 | +----+-------+--------+-----+------------+---------+--------------+--------+--------+-----------+ 4 rows in set Time: 0.014s +----+-------+--------+-----+------------+---------+--------------+--------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+--------+-----+------------+---------+--------------+--------+--------+-----------+ | 3 | kevin | male | 81 | 2013-03-05 | teacher | <null> | 8300.0 | 401 | 1 | | 4 | tank | male | 73 | 2014-07-01 | teacher | <null> | 3500.0 | 401 | 1 | | 5 | owen | male | 28 | 2012-11-01 | teacher | <null> | 2100.0 | 401 | 1 | | 6 | jerry | female | 18 | 2011-02-11 | teacher | <null> | 9000.0 | 401 | 1 | +----+-------+--------+-----+------------+---------+--------------+--------+--------+-----------+
利用where查询记录为空的时候, 需要使用is关键字, 而不能使用=.
group by:
group by 分组的意思是按照表所有记录一个相同的字段分成几组, 然后对这些整体进行操作, 例如省里的人口表可以以市为单位分组, 然后以组为单位进行人口数量统计, 薪资统计等等.
分组的注意点:
1. 当不含group by关键字, 默认分为一组.
2. 当需要使用聚合函数的时候, 必须经过分组, 或是按照默认一组使用.
当我们指定某个字段分组的时候, 在sql_mode为only_full_group_by模式下, 前面的select 必须由分组依据和聚合函数组成, 不能出现组内的其他
单个字段.
分组的执行顺序是 from where group by select
例子: # 2.获取每个部门的最高工资, 平均工资, 最低工资, 人数, 工资总和...
mysql:db_8_21> select post, max(salary) as max_salary from emp group by post; +--------------------+------------+ | post | max_salary | +--------------------+------------+ | operation | 20000.0 | | sale | 4000.33 | | teacher | 1000000.31 | | 张江第一帅形象代言 | 7300.33 | +--------------------+------------+
mysql:db_8_21> select post, count(salary) from emp group by post; select post, avg(salary) from emp group by post; select post, sum(salary) from emp group by post; select post, min(salary) from emp group by post; +--------------------+---------------+ | post | count(salary) | +--------------------+---------------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 张江第一帅形象代言 | 1 | +--------------------+---------------+ 4 rows in set Time: 0.020s +--------------------+---------------+ | post | avg(salary) | +--------------------+---------------+ | operation | 16800.026 | | sale | 2600.294 | | teacher | 151842.901429 | | 张江第一帅形象代言 | 7300.33 | +--------------------+---------------+ 4 rows in set Time: 0.009s +--------------------+-------------+ | post | sum(salary) | +--------------------+-------------+ | operation | 84000.13 | | sale | 13001.47 | | teacher | 1062900.31 | | 张江第一帅形象代言 | 7300.33 | +--------------------+-------------+ 4 rows in set Time: 0.009s +--------------------+-------------+ | post | min(salary) | +--------------------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.0 | | 张江第一帅形象代言 | 7300.33 | +--------------------+-------------+ 4 rows in set Time: 0.009s
having
having关键字是在前面分组的基础上再次进行一次筛选, 前面必须出现关键字group by having关键字才有效.
例子: 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
mysql:db_8_21> select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000; +---------+-------------+ | post | avg(salary) | +---------+-------------+ | teacher | 255450.0775 | +---------+-------------+ 1 row in set Time: 0.016s
distanct
distinct关键字是对最后要显示的数据进行一次去重筛选, 它的优先级还在select之后
例子: 查询部门的所有年龄段
mysql:db_8_21> select distinct age from emp; +-----+ | age | +-----+ | 18 | | 78 | | 81 | | 73 | | 28 | | 48 | | 38 | +-----+ 7 rows in set
order by
这个关键字是对查询后的结果进行一次排序
默认的排序是升序asc, 降序需要指定关键字desc
排序还可以指定多个字段, 并且这多个字段每个还可以分别指定顺序.
例子: #先按照age降序排，在年轻相同的情况下再按照薪资升序排
mysql:db_8_21> select * from emp order by age desc, salary asc; +----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+ | 3 | kevin | male | 81 | 2013-03-05 | teacher | <null> | 8300.0 | 401 | 1 | | 2 | egon | male | 78 | 2015-03-02 | teacher | <null> | 1000000.31 | 401 | 1 | | 4 | tank | male | 73 | 2014-07-01 | teacher | <null> | 3500.0 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | <null> | 3000.13 | 402 | 2 | | 8 | sean | male | 48 | 2010-11-11 | teacher | <null> | 10000.0 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | <null> | 2000.35 | 402 | 2 | | 5 | owen | male | 28 | 2012-11-01 | teacher | <null> | 2100.0 | 401 | 1 |
...
limit
这个关键字是对查询的数据进行最后一次限制, 对查询的结果显示多少条, 从哪里开始显示
例如有10条查询结果 limit 5 表示只显示前5条, limit 2, 7 表示从第二条开始显示, 显示7条结果.
例子: 查询工资最高的人的详细信息
mysql:db_8_21> select * from emp order by salary desc limit 1; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | egon | male | 78 | 2015-03-02 | teacher | <null> | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ 1 row in set Time: 0.010s
2 多表查询
2.0 数据准备
create table dep( id int, name varchar(20) ); create table emp( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into dep values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into emp(name,sex,age,dep_id) values ('jason','male',18,200), ('egon','female',48,201), ('kevin','male',38,201), ('nick','female',28,202), ('owen','male',18,200), ('jerry','female',18,204) ;
2.1 表连接查询
表连接查询一共分为4种:
内连接: inner join
将两张表按笛卡尔积的方式连接起来, 只取两张表有对应关系的记录.
现在两张表按照emp.dep_id=dep.id的关系连接
mysql:db_8_21> select * from emp; +----+-------+--------+-----+--------+ | id | name | sex | age | dep_id | +----+-------+--------+-----+--------+ | 1 | jason | male | 18 | 200 | | 2 | egon | female | 48 | 201 | | 3 | kevin | male | 38 | 201 | | 4 | nick | female | 28 | 202 | | 5 | owen | male | 18 | 200 | | 6 | jerry | female | 18 | 204 | +----+-------+--------+-----+--------+ 6 rows in set Time: 0.016s mysql:db_8_21> select * from dep; +-----+----------+ | id | name | +-----+----------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +-----+----------+ 4 rows in set Time: 0.009s mysql:db_8_21> select * from emp inner join dep on dep.id = emp.dep_id; +----+-------+--------+-----+--------+-----+----------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+-----+--------+-----+----------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 38 | 201 | 201 | 人力资源 | | 4 | nick | female | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 200 | 200 | 技术 | +----+-------+--------+-----+--------+-----+----------+ 5 rows in set Time: 0.015s
左连接: left join:
在内连接的基础上保留左表没有对应关系的记录
右连接: right join
在内连接的基础上保留右表没有对应关系的记录
全连接: union
在内连接的基础上保留两表没有对应关系的记录
mysql:db_8_21> select * from emp left join dep on dep.id = emp.dep_id; +----+-------+--------+-----+--------+--------+----------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+-----+--------+--------+----------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 5 | owen | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 38 | 201 | 201 | 人力资源 | | 4 | nick | female | 28 | 202 | 202 | 销售 | | 6 | jerry | female | 18 | 204 | <null> | <null> | +----+-------+--------+-----+--------+--------+----------+ 6 rows in set Time: 0.010s mysql:db_8_21> select * from emp right join dep on dep.id = emp.dep_id; +--------+--------+--------+--------+--------+-----+----------+ | id | name | sex | age | dep_id | id | name | +--------+--------+--------+--------+--------+-----+----------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 38 | 201 | 201 | 人力资源 | | 4 | nick | female | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 200 | 200 | 技术 | | <null> | <null> | <null> | <null> | <null> | 203 | 运营 | +--------+--------+--------+--------+--------+-----+----------+ 6 rows in set Time: 0.016s mysql:db_8_21> select * from emp left join dep on dep.id = emp.dep_id union select * from emp right join dep on dep.id = emp.dep_id; +--------+--------+--------+--------+--------+--------+----------+ | id | name | sex | age | dep_id | id | name | +--------+--------+--------+--------+--------+--------+----------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 5 | owen | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 38 | 201 | 201 | 人力资源 | | 4 | nick | female | 28 | 202 | 202 | 销售 | | 6 | jerry | female | 18 | 204 | <null> | <null> | | <null> | <null> | <null> | <null> | <null> | 203 | 运营 | +--------+--------+--------+--------+--------+--------+----------+ 7 rows in set Time: 0.019s
2.2 子查询
要理解子查询需要先明白我们所有的中间查询都可以被看做一张表. 子查询就是按照我们的正常逻辑进行的查询.
翻译过来就是将一个查询语句的结果当做另一个查询语句的条件.
例子: 查询部门是技术或者人力资源的员工信息
思路也很简单, 就是我们拆成两句来写.
select id from dep where name = '技术' or name = '人力资源'; select * from emp where dep_id in (201, 200); select * from emp where dep_id in (select id from dep where name = '技术' or name = '人力资源');
先查找技术和人力资源的结果, 然后按照emp表的id在201, 200的思路去找, 最后做一个替换就可以了.
浙公网安备 33010602011771号