oracle_day02

最新推荐文章于 2024-04-30 22:34:40 发布

玉露枫林

最新推荐文章于 2024-04-30 22:34:40 发布

阅读量248

点赞数

分类专栏：数据库

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

七.数据的排序
7.1 概念
可以按照一定的标准对数据进行排序。
7.2 语法
order by 排序标准排序方式;
(只能出现在sql语句最后 )
排序方式可以省略省略之后就是升序
排列方式:
升序自然顺序字典顺序 asc
降序 desc
select id,salary from s_emp;
select id,salary from s_emp
order by salary;
select id,salary from s_emp
order by salary desc;

7.3 举例
按照部门编号进行排序显示员工的
id first_name dept_id

select id,first_name,dept_id
from s_emp
order by dept_id;
7.4 NULL 值在排序中如何处理？
按照manager_id 进行降序排序显示
id first_name manager_id
select id,first_name,manager_id
from s_emp
order by manager_id desc;
7.5 多字段排序
当一个排序字段的值相同时可以启用第二
排序字段。
按照manager_id 进行降序排序显示
id first_name manager_id
如果manager_id 相同则按照 id排序
select id,first_name,manager_id
from s_emp
order by manager_id desc,
id asc;
八.单行函数
8.1 概念
单行函数:在sql语句这个函数和sql语句
影响的行数有关。sql语句影响多少行数据
这个函数就返回多个少结果。
(针对sql语句影响的每一行都返回一个结果)
组函数:对sql语句影响的所有数据返回
一个结果。
8.2 举例
单行函数: upper
select first_name,upper(first_name)
from s_emp
where id=1;
select first_name,upper(first_name)
from s_emp
where id>1;
select first_name,upper(first_name)
from s_emp
where id<1;
组函数: count
select count(first_name)
from s_emp
where id=1;
select count(first_name)
from s_emp
where id>1;
select count(first_name)
from s_emp
where id<1;
8.3 针对字符串处理的单行函数
upper(par)
lower(par)
select first_name,lower(first_name)
from s_emp;
select lower('HELLO') FROM DUAL;
length(par)
select length('hello') from dual;
select first_name,length(first_name)
from s_emp;
concat(par1,par2) 连接两个字符串的
select concat('hello ','world')
from dual;
select 'hello ' || 'world'
from dual;
select 'ab ' || 'cd ' || 'ef'
from dual;
select concat(concat('ab ','cd '),
'ef') from dual;
substr(par1,par2,par3)
par1是要处理的字符串
par2 从什么位置开始截取从1开始
也可以是负数 -1 代表最后一个字符
par3 截取多长

select substr('hello',0,2) from dual;
select substr('hello',1,2) from dual;

显示s_emp 表的 first_name 和
first_name的前三个字符
select first_name,
substr(first_name,1,3)
from s_emp;
显示s_emp 表的 first_name 和
first_name的后三个字符
select first_name,
substr(first_name,-3,3)
from s_emp;
nvl(par1,par2)

8.4 处理数字的函数
round 四舍五入
select round(9.57) from dual;
select round(9.57,0) from dual;
select round(9.57,1) from dual;
select round(9.54,1) from dual;
select round(9.54,-1) from dual;
select round(12.88,-1) from dual;
select round(12.88,-2) from dual;

trunc 截取
select trunc(9.57) from dual;
select trunc(9.57,0) from dual;
select trunc(9.57,1) from dual;
select trunc(9.54,1) from dual;
select trunc(9.54,-1) from dual;
select trunc(12.88,-1) from dual;
select trunc(12.88,-2) from dual;
8.5 字符串转换函数
to_number(par)
'123' 'abcxyz'
select id,first_name
from s_emp where id=11;
select id,first_name
from s_emp where
id=to_number('11');
select id,first_name
from s_emp where
id='11';
select to_number('abc') from dual;

8.6 格式化显示函数
to_char(par1,par2)
par1是要处理数字或者数字字段
par2 是格式可以省略代表把一个
类型转换成字符串类型。不省略则
数据会按照指定的格式显示。
格式有:
fm 代表格式的开始可以省略
$ 美元符号
L 本地货币符号
￥ RMB
9 小数点前代表 0-9 任意数字
小数点后代表 1-9 任意数字
0 小数点前代表强制显示前导零
12345 012,345.00
7895.35 007,895.35
小数点后代表 0-9的任意数字
, 分割符号
. 小数点

select salary,to_char(salary,
'fm$099,999.99') from s_emp;

select salary,to_char(salary,
'fm$099,999.00') from s_emp;

select salary,to_char(salary,
'fmL099,999.00') from s_emp;

如何修改本地的语言
1.切换shell
bash
2.修改配置文件
vi .bash_profile
3.写入环境变量 NLS_LANG
export NLS_LANG=
'SIMPLIFIED CHINESE.CHINA.ZHS16GBK'
export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'
4.保存退出让配置文件升序
source .bash_profile
ehco $NLS_LANG
5.重新进入sqlplus

8.7 函数的嵌套
把一个函数的返回值作为另一个函数
的参数。

select 'ab ' || 'cd ' || 'ef'
from dual;
select concat(concat('ab ','cd '),
'ef') from dual;
显示 s_emp 表中 first_name 和
first_name 后三个字符
substr length
select first_name,
substr(first_name,
length(first_name)-2,3)
from s_emp;
显示s_emp 表中 id ,first_name ,
manager_id 如果manager_id 为NULL
则显示成 BOSS
nvl to_char
select id,first_name,
nvl(to_char(manager_id),'BOSS')
from s_emp;

九.多表查询
9.1 为什么?
因为查询数据不在一张表中而是分布
在多张表中。
9.2 举例
列出每个员工的 id first_name 部门编号
select id,first_name,dept_id
from s_emp;
列出每个员工的 id first_name 部门名
desc s_dept;
SQL> desc s_dept;
Name
-------------------
ID 部门编号
NAME 部门名
REGION_ID 地区编号
如果两张表中有同名字段需要加表名区分
select s_emp.id,first_name,
s_dept.id,name
from s_emp,s_dept;
连接两张表如果没有连接条件则会产生
笛卡尔积。
避免产生笛卡尔积有表连接条件即可
select s_emp.id,first_name,
s_dept.id,name
from s_emp,s_dept
where dept_id=s_dept.id;

9.3 把每个部门的id 和部门名以及
部门所在的地区名显示出来
SQL> desc s_dept; SQL> desc s_region;
Name Name
------------------- ----------------
ID 部门编号 ID 地区编号
NAME 部门名 NAME 地区名
REGION_ID 地区编号
select s_dept.id,s_dept.name,s_region.name
from s_dept,s_region
where region_id=s_region.id;
/* 设定叫name的这一列一行最多显示
15个字符 */
col name for a15;
9.4 可以给表名起别名
select d.id,d.name,r.name
from s_dept d,s_region r
where region_id=r.id;
9.5 列出每个员工的 first_name
和这个员工所在的部门名以及部门
所在的地区名。
select first_name,d.name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and
d.region_id=r.id;
9.6 连接部门表和员工表使用等号连接
连接部门表和地区表也是用等号
这种连接叫等值连接。
如果不使用等号连接两张表则叫
非等值连接。
SQL> desc salgrade;
Name
------------------
GRADE 工资级别
LOSAL 本级别对应的低工资
HISAL 本级别对应的高工资
drop table salgrade;
create table salgrade(
grade number primary key,
losal number,
hisal number
);
insert into salgrade
values(1,700,1200);
insert into salgrade
values(2,1201,1400);
insert into salgrade
values(3,1401,2000);
insert into salgrade
values(4,2001,3000);
insert into salgrade
values(5,3001,9999);
commit;
列出每个人的工资和工资对应
的工资级别。
id salary grade
1 2500 4
2 1450 3
select id,salary,grade
from s_emp e,salgrade s
where e.salary between
losal and hisal;
9.7 特殊的连接
有些人是领导有些人不是领导
找出所有的领导?
id first_name manager_id
100 a 100
101 b 100
102 c 100

如果一个人的领导id 是你的id 则你就
是领导。
select id,first_name
from s_emp
where manager_id=id;
select m.id,m.first_name
from s_emp e,s_emp m
where e.manager_id=m.id;

9.8 表连接
内连接:符合条件的数据被选中不
符合条件的被过滤掉。
等值连接
员工和部门
部门和地区
员工部门地区
非等值
员工工资和工资级别
自连接
领导和非领导

外连接:
外连接的结果集等于内连接的
结果集加上匹配不上的记录。
( 一个也不能少 )
十.外连接
10.1 概念
外连接的结果集等于内连接的
结果集加上匹配不上的记录。
( 一个也不能少 )
10.2 举例
能找出谁是普通员工?
25-8=17
select distinct m.id,m.first_name
from s_emp e,s_emp m
where e.manager_id!=m.id;
// logic error
10.3 外连接的语法
(+) (+)字段对面的表的数据全部匹配
出来。
select distinct m.id,m.first_name
from s_emp e,s_emp m
where e.manager_id(+)=m.id
and e.manager_id is null;
10.4 列出每个部门的名字和部门对应
的地区名。
select d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id;
公司业务扩展增加了新的部门
insert into s_dept values(9527,
'Test100',NULL);
commit;
列出每个部门的名字和部门对应
的地区名。没有地区的部门也要显示。
select d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id(+);

select d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id(+)
and r.id is null;
10.5 列出每个员工的工资和工资对应
的工资级别。没有工资级别的员工也要显示。
select id, salary,grade
from s_emp e,salgrade s
where e.salary between
losal(+) and hisal(+)
order by id;
给老板涨工资
update s_emp set salary=12500
where id=1;
commit;
10.6 外连接:
等值连接
新增部门和地区
非等值连接
涨工资和工资级别
自连接
谁是普通 ?
10.7 sql99中的内外连接
sql99内连接:
from a join b on 连接条件;
from a inner join b on 连接条件;

列出每个部门的名字和对应的地区名
select d.name,r.name
from s_dept d ,s_region r
where d.region_id=r.id;
select d.name,r.name
from s_dept d join s_region r
on d.region_id=r.id;

sql99外连接:
from a表 left outer join b表
on 连接条件 where 条件;
from a表 right outer join b表
on 连接条件 where 条件;
from a表 full outer join b表
on 连接条件 where 条件;
所谓的左外连接就是左边的表发起连接
,谁发起连接就谁中的所有的数据匹配
出来。
select d.name,r.name
from s_dept d ,s_region r
where d.region_id=r.id(+);
select d.name,r.name
from s_dept d left outer join
s_region r
on d.region_id=r.id;

使用sql99 的左外连接把谁是普通
员工查找出来？


where name='abc' and passwd='123'
or 1=1;
select id,first_name
from s_emp where id=-1 or 1=1;