一、单行函数
1、 单行函数和组函数的概念
单行函数：针对sql语句影响的数据，每行都做处理，每行产生一个结果
select id,first_name,upper(first_name) froms_emp; 小写转大写
组函数：针对sql语句影响的数据，每组做处理，每组产生一个结果
select count(id) from s_emp; 查看多少行，汇总统计
2、 测试表
dual
desc dual；
3、 字符串函数
upper(str)：把str转换成全大写
SQL> select upper('hello world') fromdual;
HELLO WORLD
lower(str)：把str转换成全小写
SQL> select upper('TANG ZI HAO') fromdual;
tangzihao
initcap（str）：把str中每个单词都转换成首字母大写
SQL> select upper('hello world') fromdual;
Hello World
concat(s1,s2) 连接s1和s2 （用得少，一般用 || ）
select concat(‘hello’,’world’) from dual;
等价于 select ‘hello’||’world’from dual;
substr(s,start,[len])：截取子串
从start的位置开始，截取len个字符
start取值从1开始，如果写成0按1处理
start取正数表示从左往右开始数
start取负数，表示从右往左开始数，都是往后截
len省略，表示截取到字符串的结尾
select substr(‘hello world!’,1,3);
select substr(‘hello world!’,0,3);
select substr(‘hello world!’,-4,3);
select substr(‘hello world!’,-4,6);
length(s)：返回字符串的长度
select length(‘hello world’) from fual;
练习：用两种方法获取s_emp表中first_name字段的后三位
SQL> selectfirst_name,substr(first_name,-3,3) from s_emp;
SQL> select first_name,substr(first_name,length(first_name)-2,3)from s_emp;
4、 数学函数
round(x[,y])：四舍五入
y可省略，默认为0 round(4.56) 5
y为正整数时，就是四舍五入到小数点后y位
y为负整数时，就是四舍五入到小数点左边|y|位
selectround(5678.1234,-2) from dual; --5700
trunc(x[,y])：截取
y可省略，默认为0 trunc(4.56) 4 小数点后不要
y为正整数时，就是截取到小数点后y位
y为负整数时，就是截取到小数点左边|y|位
select trunk(5678.567,-2) from dual； -- 5600
5、 日期类型和常用的日期函数
1） 日期类型
类型：data
默认显示格式：
英文：dd—MON—yy 26-10-16
中文：dd—n月—yy 26-10月-16
日期各部分的表示方式：
yyyy ---- 四位数字年 2016
mm ---- 二位数字月 10
dd ---- 两位数字日 26
hh24 ---- 24小时制小时
hh ---- 12小时制小时
mi ---- 分钟
ss ---- 秒
year ---- 英文全拼的年份 twenty sisteen
mon ---- 月份的前三个字母 oct
month ---- 月份的全拼 octorber
day ---- 星期几全拼 Wednesday
dy ---- 星期几的前三个字母 wed
am ---- 上午
pm ---- 下午
系统日期：sysdata
select sysdatefrom dual;
2） 常用的日期函数( + - )
日期类型的算术运算
selectsysdate+10 from dual;
selectsysdate+10 from dual;
SQL> selectsysdate-to_date('1992-9-14','yyyy-mm-dd') from dual;
ADD_MONTHS(d,n)：在日期d的基础上加n个月
SQL> selectadd_months(sysdate,10) from dual;
MONTHS_BETWEEN（d1,d2）：计算日期d1和d2相差的月数
Selectmonths_between(sysdate,to_date(‘1992-09-14’,’yyyy-mm-dd’))from dual;
NEXT_DAY(d,wd)：日期d的下一个wd（星期几）
Selectnext_day(sysdate,’friday’) from dual;
LAST_DAY（d）：日期d所在月的最后一天
selectlast_day(sysdate) from dual;
6、 转换函数
1） to_char(d|n[,fmt])
a) 数字转换成字符串
selectto_char(1234) from dual;
格式字符串：以fm开头
9 小数点前表示0—9 ，小数点后表示1—9
0 小数点前表示强制加前导0，小数点后表示0—9
$ 美元符号
L 本地货币符号
。 小数点
， 分隔符
selectto_char(1234.56,’fm$099,999.99’) from dual;
selectid,to_char(salary,fm$099,999.99) from s_emp;
b) 日期转换成字符串
Selectto_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;
2016-10-26 11:57:45 Selectto_char(sysdate,’yyyy-mm-dd hh24:mi:ss year month day am’) from dual;
2）to_date(s[,fmt])：字符串转换日期
to_date(‘2008-08-0820:08:08’,’yyyy—mm-dd hh24:mi:ss’);
3) to _number(s[,fmt])：字符串转换成数字
隐式转换：
selectid,first_name,salary from s_emp where id=1;
selectid,first_name,salary from s_emp where id=’1’;
显示转换：
Selectid,first_name,salary from s_emp where id=to_num(‘1’);
Selectto_number(‘$01234.56’,’fm$09999.99’) from dual;
7、 函数的嵌套
一个函数的返回值作为另一个函数的参数
Selectconcat(concat(‘a’,’b’),’c’) from dual;
Select‘a’||’b’||’c’ from dual;
练习：列出员工的id，first_name,manager_id,如果marager_id为Null,显示成’BOSS’
selectid,first_name,nvl(to_char(manager_id),’BOSS’) from s_emp;
二、多表查询（表连接）
1、 需求：列出每个员工的id,first_name和所在部门的名称
1） 列出每个员工的id，first_name,dept_id
selectid,first_name,dept_id from s_emp;
2) 部门名称在部门表中（s_dept）
descs_dept；
SQL> desc s_dept;
Name Null? Type
------------------------------------------------- ----------------------------
ID 部门编号 NOT NULL NUMBER(7)
NAME 部门名称 NOT NULL VARCHAR2(25)
REGION_ID 地区编号 NUMBER(7)
Select * froms_dept；
语法： Select 字段 from 表1，表2 where 连接条件；
SQL> selects_emp.id,s_emp.first_name,s_dept.name from s_emp,s_dept wheres_emp.dept_id=s_dept.id;
如果没有连接条件，结果是什么？
selects_emp.id,s_emp.first_name,s_dept.name from s_emp,s_dept； 300条
笛卡尔积：多表查询时，没有连接条件时得到的结果就是笛卡尔积。
2、 表的别名
表名 别名
selecte.id,e.first_name,d.name from s_emp e,s_dept d where e.dept_id=d.id;
表一旦命名别名，则在当前语句中原表名失效
selecte.id,e.first_name,d.name from s_emp e,s_dept d wheres_emp.dept_id=s_dept.id; 错误
连接的多个表中没有重名的字段可以省略前面的别名或表名
selecte.id,first_name,name from s_emp e,s_dept d where dept_id=d.id;
3、 多表连接的类型
内连接
等值连接
非等值连接
自连接
外连接
等值连接
非等值连接
自连接
4、 内连接
连接条件成立的出现在结果集中，不成立的被过滤掉 (两表中同时有的)
更改id=1的员工的部门编号为Null；
update s_emp setdept_id=null where id=1;
commit;
selecte.id,first_name,name from s_emp e,s_dept d where dept_id=d.id;
1） 等值连接
连接条件用=
练习：列出每个部门的信息，包括编号、名称和所在地区的名称
S_dept：id name
S_region： name
desc s_region;
SQL> desc s_region;
Name Null? Type
------------------------------------------------- ----------------------------
ID 地区编号 NOTNULL NUMBER(7)
NAME 地区名称 NOT NULL VARCHAR2(50)
select * froms_region;
SQL> select * from s_region;
IDNAME
------------------------------------------------------------
1 North America
2 South America
3 Africa / Middle East
4 Asia
5 Europe
select d.id,d.name,r.name from s_deptd,s_region r where d.region_id=r.id；
2） 非等值连接
连接条件使用非等号
添加一张表：salgrade 工资级别表
creat tablesalgrade(
grade number(3),
losal number(7,2),
hisal number(7,2)
);
insert intosalgrade values(1,700,1200);
insert intosalgrade values(2,1201,1400);
insert intosalgrade values(3,1401,2000);
insert intosalgrade values(4,2001,3000);
insert intosalgrade values(5,3001,9999);
commit;
/*列出每个员工的信息，包括id,first_name,salary和工资级别 */
s_emp:idfirst_name salary (salary)
salgrade:grade(losal,hisal)
selecte.id,e.first_name,e.salary,g.grade from s_emp e,salgrade g
where e.salary>=g.losal and e.salary<=g.hisal;
或 where e.salary between g.losal and g.hisal;
/*更改编号为1的工资*/
Update s_emp setsalary=10000 where id=1;
Commit;
select e.id,e.first_name,e.salary,g.gradefrom s_emp e,salgrade g
where e.salary>=g.losal and e.salary<=g.hisal;
(老板的工资太高了 不在范围内)
3） 自连接
在逻辑上把一张表当成两张表使用
select 字段
from 表1 ，表2
where 条件；
需求：列出s_emp中所有的领导的名字
第一步：列出员工的id,first_name,manager_id;
selectid,first_name,.manager_id from s_emp;
第二步：列出所有员工及其领导的名字
员工表：s_emp e first_name
领导表：s_emp m first_name
Selecte.id,e.first_name,m.id,m.first_name
from s_emp e,s_emp m
where e.manager_id=m.id;
第三步：列出所有领导的名字
Selectdistinct m.first_name from s_emp e,s_emp m where e.manager_id=m.id;
注意：自连接，表一定要命名别名
5、 外连接
外连接的结果集=内连接的结果集+匹配不上的记录
（+） --- oracle特有的用法
对面表中的数据全部显示
表1.字段（+）=表2.字段 在表1中显示没有匹配的表2的字段，表2中的数据全部显示
表1.字段=表2.字段（+） 在表2中显示没有匹配的表1的字段，表1中的数据全部显示
1） 自连接
需求：列出员工表（s_emp）中所有的普通员工的first_name
第一步： 显示员工和其他领导的信息 包括id=1 的员工（没有领导）
select e.id,e.first_name,m.id,m.first_name
froms_emp e,s_emp m
wheree.manager_id(+)=m.id
第二步： 找出普通员工
select e.id,e.first_name,m.id,m.first_name
froms_emp e,s_emp m
where e.manager_id(+)=m.id and e.id is null;
第三步： 用外连接列出领导
select m.id,m.first_name
froms_emp e,s_emp m
where e.manager_id(+)=m.id and e.id is null;
练习：
.列出s_emp表中的id,first_name和start_date,其中start_date，按照‘yyyy-mm-dd’格式显示
显示员工的id,first_name和部门信息
显示员工的工资及工资级别的信息
显示所有的领导的信息
显示所有的普通员工的信息
2） 等值连接
更改id=1的员工的部门编号为Null；
update s_emp set dept_id=null where id=1;
commit;
/*列出员工的部门信息，包括没有部门的员工*/
select e.id,first_name,name from s_emp e,s_dept d wheredept_id=d.id;
在部门表中添加一个新部门
insert int s_dept values(100,’Infor’,null);
commit;
/*列出员工的木本信息 包括没有员工的部门*/
select e.id,first_name,name from s_emp e,s_dept d where e.dept_id（+）=d.id;
3）非等值连接
/*列出员工及其工资级别的信息*/
Select e.id,e.first_name,e.salary,g.grade
from s_emp e,salgrade g
where e.salary>=g.losal and e.salary<=g.hisal;
/*列出员工及其工资级别的信息,显示所有员工*/
Selecte.id,e.first_name,e.salary,g.grade
from s_emp e,salgrade g
where e.salary>=g.losal(+) and e.salary<=g.hisal(+);
/*列出员工及其工资级别的信息,显示所有级别*/
Selecte.id,e.first_name,e.salary,g.grade
from s_emp e,salgrade g
where e.salary(+)>=g.losal and e.salary(+)<=g.hisal;
注意：（+）只能出现在连接条件的一侧
645
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
