# 建表createtable department(
id int,
name varchar(20));createtable employee(
id intprimarykeyauto_increment,
name varchar(20),
sex enum('male','female')notnulldefault'male',
age int,
dep_id int);# 插入数据insertinto department values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');insertinto employee(name,sex,age,dep_id)values('nick','male',18,200),('jason','female',48,201),('sean','male',38,201),('tank','female',28,202),('oscar','male',18,200),('mac','female',18,204);
二.多表连接查询
SELECT 字段列表
FROM 表1INNER|LEFT|RIGHTJOIN 表2ON 表1.字段 = 表2.字段;
2.1 交叉连接
mysql>select*from employee,department;+----+------------+--------+------+--------+------+--------------+| id | name | sex | age | dep_id | id | name |+----+------------+--------+------+--------+------+--------------+|1| nick | male |18|200|200| 技术 ||1| nick | male |18|200|201| 人力资源 ||1| nick | male |18|200|202| 销售 ||1| nick | male |18|200|203| 运营 ||2| jason | female |48|201|200| 技术 ||2| jason | female |48|201|201| 人力资源 ||2| jason | female |48|201|202| 销售 ||2| jason | female |48|201|203| 运营 ||3| sean | male |38|201|200| 技术 ||3| sean | male |38|201|201| 人力资源 ||3| sean | male |38|201|202| 销售 ||3| sean | male |38|201|203| 运营 ||4| tank | female |28|202|200| 技术 ||4| tank | female |28|202|201| 人力资源 ||4| tank | female |28|202|202| 销售 ||4| tank | female |28|202|203| 运营 ||5| oscar | male |18|200|200| 技术 ||5| oscar | male |18|200|201| 人力资源 ||5| oscar | male |18|200|202| 销售 ||5| oscar | male |18|200|203| 运营 ||6| mac | female |18|204|200| 技术 ||6| mac | female |18|204|201| 人力资源 ||6| mac | female |18|204|202| 销售 ||6| mac | female |18|204|203| 运营 |+----+------------+--------+------+--------+------+--------------+
2.2 内连接
# 关键字:inner join on# 语法:from A表 inner join B表 on A表.关联字段=B表.关联字段# 找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果# department没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来
mysql>select employee.id,employee.name,employee.age,employee.sex,department.name from employee innerjoin department on employee.dep_id=department.id;+----+-----------+------+--------+--------------+| id | name | age | sex | name |+----+-----------+------+--------+--------------+|1| nick |18| male | 技术 ||2| jason |48| female | 人力资源 ||3| sean |38| male | 人力资源 ||4| tank |28| female | 销售 ||5| oscar |18| male | 技术 |+----+-----------+------+--------+--------------+# 上述sql等同于
mysql>select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
2.3 外连接之左连接
# 关键字:left join on# 语法:from 左表 left join 右表 on 左表.关联字段=右表.关联字段# 以左表为准,即找出所有员工信息,当然包括没有部门的员工# 本质就是:在内连接的基础上增加左边有右边没有的结果
mysql>select employee.id,employee.name,department.name as depart_name from employee leftjoin department on employee.dep_id=department.id;+----+------------+--------------+| id | name | depart_name |+----+------------+--------------+|1| nick | 技术 ||5| oscar | 技术 ||2| jason | 人力资源 ||3| sean | 人力资源 ||4| tank | 销售 ||6| mac |NULL|+----+------------+--------------+
2.4外连接之右连接
# 关键字:right join on# 语法:from A表 right join B表 on A表.关联字段=B表关联字段# 以右表为准,即找出所有部门信息,包括没有员工的部门# 本质就是:在内连接的基础上增加右边有左边没有的结果
mysql>select employee.id,employee.name,department.name as depart_name from employee rightjoin department on employee.dep_id=department.id;+------+-----------+--------------+| id | name | depart_name |+------+-----------+--------------+|1| nick | 技术 ||2| jason | 人力资源 ||3| sean | 人力资源 ||4| tank | 销售 ||5| oscar | 技术 ||NULL|NULL| 运营 |+------+-----------+--------------+
2.5 外连接之左右连接互换
# 左连接
mysql>select employee.id,employee.name,department.name as depart_name from employee leftjoin department on employee.dep_id=department.id;# 右连接
mysql>select employee.id,employee.name,department.name as depart_name from department rightjoin employee on employee.dep_id=department.id;
2.6 全连接
全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
# 注意:mysql不支持全外连接 full JOIN# 强调:mysql可以使用此种方式间接实现全外连接select*from employee leftjoin department on employee.dep_id = department.id
unionselect*from employee rightjoin department on employee.dep_id = department.id
;# 查看结果+------+------------+--------+------+--------+------+--------------+| id | name | sex | age | dep_id | id | name |+------+------------+--------+------+--------+------+--------------+|1| nick | male |18|200|200| 技术 ||5| oscar | male |18|200|200| 技术 ||2| jason | female |48|201|201| 人力资源 ||3| sean | male |38|201|201| 人力资源 ||4| tank | female |28|202|202| 销售 ||6| mac | female |18|204|NULL|NULL||NULL|NULL|NULL|NULL|NULL|203| 运营 |+------+------------+--------+------+--------+------+--------------+# 注意 union与union all的区别:union会去掉相同的纪录
2.7 符合条件连接查询
# 示例1:以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门select employee.name,department.name from employee innerjoin 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 >25orderby age asc;
三.子查询
3.1带IN关键字的子查询
# 查询平均年龄在25岁以上的部门名select id,name from department
where id in(select dep_id from employee groupby dep_id havingavg(age)>25);# 查看技术部员工姓名select name from employee
where dep_id in(select id from department where name='技术');# 查看不足1人的部门名(子查询得到的是有人的部门id)select name from department where id notin(selectdistinct dep_id from employee);
3.2 带比较运算符的子查询
# 比较运算符:=、!=、>、>=、<、<=、<># 查询大于所有人平均年龄的员工名与年龄
mysql>select name,age from emp where age >(selectavg(age)from emp);+---------+------+| name | age |+---------+------+| jason |48|| sean |38|+---------+------+rowsinset(0.00 sec)# 查询大于部门内平均年龄的员工名、年龄select t1.name,t1.age from emp t1
innerjoin(select dep_id,avg(age) avg_age from emp groupby dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
3.3 带EXISTS关键字的子查询
# department表中存在dept_id=203,Ture
mysql>select*from employee
->whereexists->(select id from department where id=200);+----+------------+--------+------+--------+| id | name | sex | age | dep_id |+----+------------+--------+------+--------+|1| nick | male |18|200||2| jason | female |48|201||3| sean | male |38|201||4| tank | female |28|202||5| oscar | male |18|200||6| mac | female |18|204|+----+------------+--------+------+--------+# department表中存在dept_id=205,False
mysql>select*from employee
->whereexists->(select id from department where id=204);
Empty set(0.00 sec)
3.4 all与any:区间修饰条件
# 语法规则# where id in (1, 2, 3) => id是1或2或3# where id not in (1, 2, 3) => id不是1,2,3# where salary < all(3, 6, 9) => salary必须小于所有情况(小于最小)# where salary > all(3, 6, 9) => salary必须大于所有情况(大于最大)# where salary < any(3, 6, 9) => salary只要小于一种情况(小于最大)# where salary > any(3, 6, 9) => salary只要大于一种情况(大于最小)in<>()# 案例select*from emp where salary <all(select salary from emp where id>11);