create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into department values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into employee(name,sex,age,dep_id) values ('egon','male',18,200), ('alex','female',48,201), ('wupeiqi','male',38,201), ('yuanhao','female',28,202), ('liwenzhou','male',18,200), ('jingliyang','female',18,204) ;
一、多表连接查询
#重点：外链接语法 select 字段列表 from 表1 inner|left|right join 表2 on 表1.字段 = 表2.字段;
1、交叉连接：不使用任何匹配条件，生成笛卡尔积
积 表示乘积的意思 把两个表中的所有数据 全部建立关联关系 a 表 有二条 b表有三条 总数据量为2 * 3 = 6条 可以保证 肯定有一条关联关系是正确的,但是同时会产生大量错误数据, 我们需要加以过滤 来得到正确的数据 ------------------------------------------ mysql> select * from employee,department; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 1 | egon | male | 18 | 200 | 201 | 人力资源 | | 1 | egon | male | 18 | 200 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 203 | 运营 | | 2 | alex | female | 48 | 201 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 2 | alex | female | 48 | 201 | 202 | 销售 | | 2 | alex | female | 48 | 201 | 203 | 运营 | | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 | | 4 | yuanhao | female | 28 | 202 | 200 | 技术 | | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 4 | yuanhao | female | 28 | 202 | 203 | 运营 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 | | 5 | liwenzhou | male | 18 | 200 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 203 | 运营 | | 6 | jingliyang | female | 18 | 204 | 200 | 技术 | | 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 | | 6 | jingliyang | female | 18 | 204 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | 203 | 运营 | +----+------------+--------+------+--------+------+--------------+
2、内连接：只连接匹配的
两边数据完全匹配成功才会显示 inner（可以省略） join on（on只能与join一起使用，但是在join中 可以把on换成where） on == where 只能用于连接查询 如果用来筛选匹配关系 建议使用on 连接查询中必须使用on
mysql> select *from department,employee where dep_id = department.id and department.name = "技术"; +------+--------+----+-----------+------+------+--------+ | id | name | id | name | sex | age | dep_id | +------+--------+----+-----------+------+------+--------+ | 200 | 技术 | 1 | egon | male | 18 | 200 | | 200 | 技术 | 5 | liwenzhou | male | 18 | 200 | +------+--------+----+-----------+------+------+--------+ #这种方式与上面的方式类似，相当于，join连接两个表，这里的on相当于上边的where mysql> select *from department join employee on dep_id = department.id where department.name = "技术"; +------+--------+----+-----------+------+------+--------+ | id | name | id | name | sex | age | dep_id | +------+--------+----+-----------+------+------+--------+ | 200 | 技术 | 1 | egon | male | 18 | 200 | | 200 | 技术 | 5 | liwenzhou | male | 18 | 200 | +------+--------+----+-----------+------+------+--------+
#多对多： 1.先把三个表全都连在一起 select * from stu join tsr join tea 2.用on来筛选出正确关系 on stu.id = tsr.s_id and tea.id = tsr.t_id （把错误的丢掉） 3.然后通过where 添加额外的条件 where tea.name = "egon"; stu tsr join 为三个表
3、外连接之左右连接
#外连接查询结果=内连接查询结果+主表中有而从表中没有的 #外链接之左连接：（左边的是主表） 左边无论是否匹配，都全部显示，右边只显示匹配成功的 #语法： select * from 表1 left join 表2 on 条件 select * from employee left join department on dep_id = department.id;
4、全外连接
#注意：mysql不支持全外连接 full JOIN #强调：mysql可以使用此种方式间接实现全外连接 select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id ; ##注意 union与union all的区别：union会去掉相同的纪录 mysql> select * from employee left join department on employee.dep_id = department.id -> union all -> select * from employee right join department on employee.dep_id = department.id;
mysql> select * from employee left join department on employee.dep_id = department.id -> union -> select * from employee right join department on employee.dep_id = department.id -> -> ; +------+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+------------+--------+------+--------+------+--------------+ 7 rows in set (1.58 sec) mysql> select * from employee left join department on employee.dep_id = department.id -> union all -> select * from employee right join department on employee.dep_id = department.id; +------+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | 1 | egon | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+------------+--------+------+--------+------+--------------+ 12 rows in set (0.00 sec)
5、非等值连接查询
#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门 select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25; #示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示 select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;
----------------------------------------
1、内链接
2、左右连接
3、左右连接升级版
4、全连接，MySQL不支持full, 使用左右连接拼接
5、使用左右连接升级版拼接
二、子查询
create table emp (id int,name char(10),sex char,age int,dept_id int,job char(10),salary double); insert into emp values (1,"刘备","男",26,1,"总监",5800), (2,"张飞","男",24,1,"员工",3000), (3,"关羽","男",30,1,"员工",4000), (4,"孙权","男",25,2,"总监",6000), (5,"周瑜","男",22,2,"员工",5000), (6,"小乔","女",31,2,"员工",4000), (7,"曹操","男",19,3,"总监",10000), (8,"司马懿","男",24,3,"员工",6000); create table dept(id int primary key,name char(10)); insert into dept values(1,"市场"),(2,"行政"),(3,"财务");
#1：子查询是将一个查询语句嵌套在另一个查询语句中。 #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。 #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 #4：还可以包含比较运算符：= 、 !=、> 、<等
过程推导
#练习，查查财务部有哪些人， #连接查询： mysql> select *from dept join emp -> on dept_id = dept.id -> where dept.name = "财务"; #子查询： 先通过部门名称拿到部门id select name from emp where dept_id = 3; 在根据id 找对应的员工 select name from emp where dept_id = (select id from dept where name = "财务"); #连到一起 select name from emp where dept_id = (select id from dept where name = "财务");
1、带in关键字的子查询
#查询平均年龄在25岁以上的部门名 select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); #查看技术部员工姓名 select name from employee where dep_id in (select id from department where name='技术'); #查看不足1人的部门名(子查询得到的是有人的部门id) select name from department where id not in (select distinct dep_id from employee);
2、带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<> #查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from emp where age > (select avg(age) from emp); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec) #查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
3、带EXISTS关键字的子查询
#exists(相关子查询) #查询到返回1，查询不到返回0 select exists(select *id from emp where name='张三')