and e.last_name = 'Whalen';
4-4 join on联结
SQL1999语法规定了更加规范全面的联结语法，这些联结语法用在from子句里（注意不是where了），其中on子句使用最多，例如：
select e.last_name, d.department_name
from hr.employees e join hr.departments d
on (e.department_id = d.department_id);
或者自联结的例子：
select e.last_name, m.last_name manager from hr.employees e join hr.employees m on e.manager_id = m.employee_id;
演示-join on自联结
用join on查找员工的姓氏和经理的姓氏。
select e.last_name, m.last_name manager from hr.employees e join hr.employees m
on e.manager_id = m.employee_id;
练习-查询同一个老大
（10分钟）查询与 Whalen 同一个经理的所有员工的信息，显示部门编码、Whalen员工、同事姓氏（colleague）、经理编码。
4-5三向联结
三向联结是三个表之间的联结，如下：
select employee_id, city,department_name from hr.employees e
join hr.departments d
on e.department_id = d.department_id join hr.locations l
on d.location_id = l.location_id;
在SQL1999语法中，联结从左到右执行，第一个联结是员工表联结部门表，可以引用这两个表中的列，但是不能引用场所表的列，而第二个联结可以引用所有三个表中的列。 演示-三向联结
用join on对员工表、部门表、场所表进行三表联结，查询出员工编码、城市、部门名称。
select employee_id, city,department_name from hr.employees e
join hr.departments d
on e.department_id = d.department_id
join hr.locations l
on d.location_id = l.location_id;
练习-三向联结附加条件
（10分钟）查询在Toronto城市工作的所有员工的姓氏、职务、部门编号、部门名称。 提醒：城市是locations表的city字段。
4-6 join on 附加条件
在join on语句之后可以在where里面增加附加条件，附件条件的比较两端可以继续使用关联表的列，比如
1、查询Whalen的经理姓氏：
select e.last_name, e.manager_id, m.last_name from hr.employees e join hr.employees m
on e.manager_id = m.employee_id where e.last_name = 'Whalen';
2、查询比员工Whalen工资低的所有员工的姓氏和工资，按工资倒序
select c.last_name, c.salary
from hr.employees e join hr.employees c
on e.last_name = 'Whalen' where c.salary <= e.salary order by c.salary desc;
演示-查询比Whalen工资的员工
查询所有工资比Whalen低的员工的姓氏、工资，以工资倒序排列。
select c.last_name, c.salary
from hr.employees e join hr.employees c
on e.last_name = 'Whalen' where c.salary <= e.salary order by c.salary desc;
练习-查询老资格员工
（10分钟）查询在Whalen进公司之前被聘用的所有员工的姓氏和聘用日期，按日期倒序排列。提醒：聘用日期是hire_date 练习-比经理资格老的员工
（10分钟）查询出所有比自己经理资格更老的员工，显示姓氏（emp）、聘用日期（emp hired）、经理姓名（mgr）、经理聘用日期（mgr hired）。
4-7左右连接和全连接
在员工表和部门表连接时，出现了两个问题：
1）、有一个员工（Grant）的部门编码是空值，联结后看不到这个员工。 2）、部门表有一些部门，在员工表里是没有的，联结后也看不到这些部门。
1、左连接：当我们使用员工表去join 部门表时，员工表为左表，使用左连接（left outer join）就可以看到所有员工表的数据，即使部门表中没有，结果中其部门编码和名称以null显示。
select e.last_name, e.department_id, d.department_name from hr.employees e
left outer join hr.departments d on e.department_id = d.department_id;
注：很多时候可以省略outer，只需要写 left join，下同。
2、右连接：使用右连接（right outer join）可以看到所有部门表的数据，即使在员工表中没有这个部门。
select e.last_name, e.department_id, d.department_name from hr.employees e
right outer join hr.departments d on e.department_id = d.department_id;
3、全连接：使用全连接（full outer join）可以看到所有员工表和部门表的数据，即使在另外关联表中没有数据。
select e.last_name, e.department_id, d.department_name from hr.employees e
full outer join hr.departments d on e.department_id = d.department_id;
注：本示例系统不支持full join 演示-左右连接
1、演示左连接（看结果里的Grant用户）：
select e.last_name, e.department_id, d.department_name from hr.employees e
left outer join hr.departments d on e.department_id = d.department_id;
2、演示右连接（看结果里后面的Treasury等部门）
select e.last_name, e.department_id, d.department_name from hr.employees e
right outer join hr.departments d on e.department_id = d.department_id;
练习-带经理姓名的员工信息
（5分钟）查询所有员工的员工编码、姓氏、经理编码、经理姓名。注意有一个员工King，它是没有经理的，也要被查询出来。
5-1函数的概念
1、函数是SQL中的一项强大功能，可以用于执行数据计算、修改单个数据项、重组输出、转换数据类型、按指定格式显示等各种任务。 2、函数分为两种类型：单行函数和多行函数。 3、单行函数只处理单个行，为每行返回一个结果，单行函数又分为：字符函数、数字函数、日期函数、转换函数等几种类型。
4、多行函数又被称之为分组函数，它们可以处理成组的行，为一组行返回一个结果。 5、函数可以多层嵌套。
6、函数可以用在select、where、order by等字句当中。 注：每个厂商的数据库系统对于函数的实现都会有所不同，本课程根据案例数据库（h2db）进行演示，在以后工作过程中，还需要根据数据库类型自己调整。
5-2字符函数
字符函数将字符数据作为参数传入，进行大小写处理或字符串处理，常用的字符函数有（只是一部分）：
lower -- 转换为小写 lower('Sql') -- sql upper -- 转换为大写 upper('Sql') -- SQL concat -- 连接字符串 cancat('ab','12') -- ab12 substring --返回子串 substring('hello',1,3)--hel length -- 返回长度 length('hello') -- 5 instr -- 子串位置 instr('hello','l') -- 3 lpad -- 左边补齐 lpad('ab',5,'*') --***ab rpad -- 右边补齐 rpad('ab',5,'*') --ab*** trim -- 截取头尾字符 trim('H' from 'Hello')-- ello replace -- 替换 replace('Hello','e','a') -- Hallo
演示-大小写转换
显示员工Higgings的员工编码、姓名和部门编码。
select employee_id,last_name,department_id from hr.employees
where lower(last_name) = 'higgins';
演示-字符函数组合
显示职务从第四个字母起包含'REP'的所有员工的全名（名字和姓氏联结起来）、姓氏的长度、字母'a'在姓氏中的位置等信息。
select employee_id,concat(first_name,last_name) name, job_id, length(last_name),
instr(last_name,'a') \
from hr.employees
where substring(job_id,4) = 'REP';
练习-尾字母查询
（10分钟）显示姓氏以n、r或m结尾的所有员工的全名、姓氏的长度，按全名排序。
提示：substring(str, start, end)中，start为负数，表示其相对结尾位置反过来算。例如:substring(last_name , -1, 1)表示最后一位
演示-工资左补$
查询所有员工的姓氏和工资，工资按15个字符显示，左边不足的补上'$'，标记为salary。
select last_name, lpad(salary, 15, '$') salary from hr.employees;
练习-￥代表工资
（10分钟）查询所有员工的姓氏、工资、工资等级(grade)，要求工资等级每1000元用1个￥代替，例如:￥￥￥￥￥表示6000元到7000元之间，另外结果要按照工资倒序排列。 提醒：用工资/1000+1表示工资的等级数字，用rpad空格，补齐用￥。
5-3数字函数
数字函数接受数字输入，并返回数字值。例如：
round -- 四舍五入 round(45.926, 2) --> 45.93 trunc -- 截断 trunc(45.926, 2) --> 45.92
mod -- 求余 mod(16, 3) --> 1
练习-加工资
（5分钟）公司准备加薪15%，请您：
1、显示每位员工的员工编号、姓氏、工资、加薪后的工资（New Salary）以四舍五入整数显示。
2、增加一个字段显示加薪的增量（Increase）。
5-4日期格式
1、日期以内部数字格式存储：世纪、年、月、日、小时、分钟、秒、毫秒，这是所有数据库系统、甚至开发语言都支持的。
2、日期在显示的时候，是按照一定的格式进行显示，比如格式为
yyyy-MMM-dd, 2014-八月-11
常用的格式有（注意大小写）： yy -- 两位的年，如 14
yyyy -- 四位的年，如2014 M -- 不带前导0的数字月，如8 MM -- 两位数字月，如08
MMM -- 缩写的字符月，如八月（中文）、Aug(英文) MMMM -- 完整月份名，中文和缩写一样，英文是全称 EEE -- 星期几缩写 EEEE -- 星期几全称 w -- 年的第几周 W -- 月的第几周