注意，两边都加上+号会发生语法错误
差集：使用crossjoin子句使连接的表产生差集//crossjion
差集和笛卡尔集是相同的
selectlast_name,department_namefromemployeescrossjiondepartments;//不常用
自然连接
1.naturaljoin子句，会以两个表中具有相同名字的列为条件创建等值连接。
2.在表中查询满足等值条件的数据
3.如果只是列名相同而数据类型不同，会产生错误。select*fromempnaturaljoindept//不常用
使用using子句创建连接
1.在naturaljion子句创建等值连接时，可以使用using子句指定等值连接中需要用到的列。
2.使用using可以在有多个列满足条件是进行选择。
3.不要给选中的列中加上表名前缀或别名。
4.naturaljion和using子句经常同时使用
SQL99链接employee表和department表
select*fromemployeesjoindepartmentsusing(department_id)//缺点：要求有同样的列名
使用on子句创建连接
1.自然连接中是以具有相同名字的列为连接条件的
2.可以使用on子句指定额外的连接条件。
3.这个连接条件是与其他条件分开的
select*fromempejoindeptdone.deptno=d.deptno//
使用on子句创建多表连接
selecte.last_name,d.department_name,l.cityfromemployeesejoindepartmentsdone.department_id=d.department_id
joinlocationslond.location_id=l.location_id
where子句能做的joinon子句差不多都可以做了
内连接和外连接(2)
1。在SQL：1999中，内连接只返回满足连接条件的数据
2.两个表在连接过程式中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行，这中连接称为左(或右)外连接
3.两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接称为满外联接
SQL99的左外连接，右外连接，满外连接
1.selectlast_name,department_namefromemployeeefull/left/rightjoindepartmentsdone.department_id=d.department_id
查询各部门的平均工资
selectavg(sal)fromempgroupbydeptno
2.(有员工的城市)各个城市的平均工资
selectavg(salary),cityfromemployeee,departmentsd,locationl//如何体现有员工，因为avg(salary)忽略空值
wheree.department_id=d.department_idandd.location_id=l.location_idgroupbyl.city
//注意，on是指连接条件，where是指from的条件，
*****************************************************************************]
veryimportant
3.在select列表中所有未包含在组函数中的列都应该包含在GROUPBY子句中
selectavg(salary),department_idfromemployees//这句是错误的，avg()是组函数，他返回公司的平均工资，只有一个值，但是departmetn_id,有多个值，所以发生了歧义，
selectavg(sal),deptnofromempgroupbydeptno;//deptno在select语句中，但是他没有包含在组函数中，所以他必须包含在GROUPBY子句中
包含在groupby子句中的列不必包含在select列表中
selectavg(salary)fromemployeesgroupbydepartment_id
selectdepartment_id,job_idfromemployeesgroupbydepartment_id,job_id;多列分组
非法使用组函数
1.不能在where子句中使用组函数
2.可以在having子句中使用组函数。
**************************************************************************************
查询平均工资大于8000的部门名称和它的平均工资
selectdepartment_name,avg(salary)fromemployeesejoindepartmentsdone.department_id=d.department_id
groupbydepartment_namehavingavg(salary)>8000
****************************************************************************************
公司员工工资，最大，最小，平均，总和
selectmin(sal),max(sal),avg(sal),sum(sal)fromemp
各job_id的员工工资，最大，最小，平均，总和
selectjob,min(sal),max(sal),avg(sal),sum(sal)fromempgroupbyjob
各job的员工总和
selectjob,count(*)fromempgroupbyjob
最高工资，最低工资的差距别名differnece
selectmax(sal),min(sal),max(sal)-min(sal)DIFFERENCEfromemp
查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者员工不计算在内
selectmgr,min(sal)fromemp
wheremgrisnotnull
groupbymgr
havingmin(sal)>=6000
*********************************************************]
查询所有部门的名字，location_id,员工数量和平均工资
selectdepartment_name,location_id,count(employee_id),avg(salary)
fromemployeese,departmentsd
wheree.department_id=d.department_id
groupbydepartment_name,location_id
**********************************************
1995年雇佣的人数
selectcount(*)fromemployeeswhereto_char(hire_date,'YYYY')='1995'
***********************************************
查询公司在1995-1995年间，每年雇佣的人数，类似下面的格式
-----------------------------------------------
total|1995|1996|1997|1998|
------------------------------------------------
20|3|4|6|7|
selectcount(*),
sum(decode(to_char(hire_date,'YYYY'),'1995',1,0))"1995",
sum(decode(to_char(hire_date,'YYYY'),'1996',1,0))"1996",
sum(decode(to_char(hire_date,'YYYY'),'1997',1,0))"1997",
sum(decode(to_char(hire_date,'YYYY'),'1998',1,0))"1998"
fromemployees
whereto_char(hire_date,'YYYY')in('1995','1996','1997','1998')
最后一句也可以这样
whereto_char(hire_date,'YYYY')between1995and1998
--------------------------------------------------
子查询语法
1.子查询(内查询)在主查询之前一次执行完成
2.子查询结果被主查询使用(外查询)
注意事项
1.子查询要包含在括号内
2.将子查询放在比较条件的右侧
3.单行操作符对应单行子查询，多行操作符对应多行子查询
**********************
单行子查询：只返回一个值,多行子查询，放回多个值
selectlast_name,salaryfromemployeeswheresalary>(selectsalaryfromemployeeswherelast_name='Abel')
***********************************
查询平均工资最高的部门信息
select*fromdepartments
wheredepartment_id=(
selectdepartment_idfromemployeesgroupbydepartment_id
havingavg(salary)=(selectmax(avg(salary))
fromemployeesgroupbydepartment_id
)
)
******************************
查询平均工资最高的job信息
----查询平均工资最高的job信息
select*fromjobswherejob_id=(
---平均工资最高的job_id
selectjob_id
fromemployees
groupbyjob_id
havingavg(salary)=(
-----按job_id分组，平均工资最高的平均工资是多少
selectmax(avg(salary))
fromemployeesgroupbyjob_id
)
)
4.查询平均工资高于公司平均工资的部门有哪些?
//注意这里的等号应该用in才合理
//有用到分组长函数，可定就要groupby了，having条件一定要放在groupby后面
这中情况下，要一层的剥离关系，
**************************************************
多行子查询in,anyall
1.查询出公司中所有的manager的详细信息
1).公司中有多少个manager
selectdistinctmanager_idfromemployees
select*fromemployeeswhereemployee_idin(selectdistinctmanager_idfromemployees)
ALL的使用,
查询公司中平均工资最高的department_id
selectdepartment_idfromemployeesgroupbydepartment_idhavingavg(salary)>=all(
-----所有部门的平均工资//这个部门的平均工资大于替他所有部门//
selectavg(salary)fromemployeesgroupbydepartment_id
)
****************************************
in等于列表中的任何一个//这个常用
any和子查询放回的任意一个值比较，//这个是太绕的，他返回所有值
all和子查询返回的所有值比较//这个是比较绕的，还好理解，返回最的个值
******************************************
1).比各个部门平均工资高的员工的员工信息
selectemployee_id,last_name,salary,department_id
fromemployeese
wheresalary>(
selectavg(salary)fromemployeeswheredepartment_id=e.department_id
)
orderbydepartment_id,salary
//要想在条件中用分组函数就要用having
