练习及总结

1.查询员工姓名，年龄，职位，部门（隐式内连接）
select e.name,e.age,e.job,d.name from emp e ,dept d where e.deot_id=d.id;

2.查询年龄小于30的员工姓名，年龄，职位，部门（显式内连接）
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.deot_id=d.id where e.age<30;
-- on后面是连接条件 where后面是查询条件，隐士用where消除笛卡尔积，显示用inner join ... on

3.查询拥有员工的部门ID，部门名称
select distinct d.id,d.name from emp e ,dept d where e.deot_id=d.id;
-- distinct去除重复数据

4.查询所有年龄大于40的员工，以及归属部门名称，如果没有部门，也要展示出来
select e.*,d.name from emp e left join dept d on e.deot_id=d.id where e.age>40;
-- 左外连接，因为包含左外所有数据，所以可以显示出无部门的员工信息

5.查询所有员工的工资等级
-- 涉及到的表emp，salgrade
select e.*,s.grade from emp e ,salgrade s where e.salary>=s.losal and e.salary<=s.hisal;
select e.*,s.grade from emp e ,salgrade s where e.salary between s.losal and s.hisal;
-- between A and B 在A和B之间
select e.*,s.grade from emp e inner join salgrade s on e.salary>=s.losal and e.salary<=s.hisal;

6.查询销售部所有员工的信息及工资等级
-- 表：emp，salgrade，dept
-- 如果要联查n张表，至少要n-1个连接条件
select e.*,s.grade
from emp e,
     dept d,
     salgrade s
where (e.deot_id = d.id)
  and (e.salary >= s.losal and e.salary <= s.hisal)
  and (d.name = '销售部');

7.查询销售部的平均工资(avg)
select avg(e.salary) from emp e ,dept d where e.deot_id=d.id and d.name ='销售部';

8.查询工资比A高的员工信息(子查询)
select *from emp where salary>(select salary from emp where name='A');

9.查询工资比平均薪资高的员工信息
select *from emp where salary>(select avg(salary) from emp);

10.查询低于本部门平均薪资的员工信息(自连接）
select *from emp e2 where e2.salary<(select avg(e.salary) from emp e where e.deot_id=e2.deot_id);

11.查询所有的部门信息，并统计员工人数(count(*))  COUNT(*)函数返回由SELECT语句返回的结果集中的行数
select d.id,d.name,(select count(*) from  emp e where e.deot_id=d.id)'人数' from dept d;

12.查询所有学生的选课情况，展示出学生名称，学号，课程名称
-- 表：student,course,student_course
-- 连接条件student.id=student_course.studentid, course.id=student_cours.courseid
-- 利用中间表进行查询，利用中间表这个条件消除无效的笛卡尔积
select s.name, s.no, c.name
from student s,
     student_course sc,
     course c
where s.id = sc.steduntid
  and c.id = sc.courseid;

-----------------------------------------

总结
1.多表关系
    一对多：在多的一方设置外键，在一的一方设置主键
    多对多：通过中间表，中间表有N个外键，关联N个表的主键
    一对一：用于表结构的拆分，在其中任意一方设置外键（为了保证他们是一对一的关系，可以在外键上加上一个唯一约束），关联另一方的主键
2.多表查询（on条件的后面可再加where条件）
    内连接
    隐式：select...from 表A,表B where 条件...
    显式：select...from 表A inner join 表B on 条件...
    外连接
    左外：select...from 表A left join 表B on 条件...
    右外：select...from 表A right join 表B on 条件...
    自连接
    select...from表A 别名1，表A 别名2 where 条件...
    子查询（子查询的位置，可以出现在where之后，可以出现在from之后，也可以出现在select之后）
    标量子查询：子查询结果为单个值 （数字，字符串，日期等）
    列子查询：子查询返回的结果只有一列(常用操作符：IN,NOT IN, ANY, SOME,ALL)
    行子查询：子查询返回的结果为一行（返回的结果是一行，也可以是多列）
    表子查询：子查询返回的结果是多行多列的