一、单行函数
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;
注意:(+)只能出现在连接条件的一侧