2.多表查询
department表:
讯享网
employee表:
1.多表连接查询
不用建立外键,我们就可以将多个表连接起来进行查询 内连接(只获取匹配的数据) select * from employee inner join department on employee.dep_id = department.id; 左连接(只显示左表所有记录,没有的话用null代替) select * from employee left join department on emoloyee.dep_id = department.id; 右连接(只显示右表的所有记录,没有的话用null代替) select * from employee right join department on employee.dep_id = department.id; 全外连接(既显示左表又显示右表) 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; 讯享网
2.符合条件连接查询
讯享网select * from department,employee where department.dep_id = employee.id; 练习题 找出年龄大于25岁的员工和员工所在的部门 select name,department.name from employee inner join department on employee.dep_id = department.id where age > 25; 找出员工及所在部门以age升序 select employee.name,department.name from employee left join department on employee.dep_id = department.id order by age asc;
3.子查询
1：子查询是将一个查询语句嵌套在另一个查询语句中。 2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。 3：子查询中可以包含：in、not in、any、all、exists和not exists等关键字 4：还可以包含比较运算符：= 、 !=、> 、<等 ps:exists关键字,使用exists关键字后,如果子查询的结果会返回True或False,如果为True,外查询执行,否则外查询不执行 eg:select * from a where exists(select id from a where id =200); 如果id为200的记录存在,就会查询a中所有的记录,否则不会查询a中的记录 小练习 查询平均年龄在25岁以上的部门 select * 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 = (select id from department where name ="技术"); 查看不足1人的部门名 select name from department where id not in (select dep_id from employee group by dep_id); 查看部门中年龄大于平均年龄的员工的姓名,年龄 select name,age from employee inner join (select dep_id,avg(age) as av from employee group by dep_id) as a on employee.dep_id = a.dep_id where employee.age > a.av;
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请联系我们，一经查实，本站将立刻删除。
如需转载请保留出处：https://51itzy.com/kjqy/58394.html