我的书签
添加书签
移除书签5.3 Oracle
Day01
一.数据库和数据库管理系统
oracle 数据库的一种
DBMS 数据库管理系统 负责对数据的管理
DB 数据库 数据的真正存放
RDBMS 关系型数据库管理系统
RDB 关系型数据库
基于二维表的数据库
表头
行
列
字段名
字段值
现在主流关系型数据库管理系统
oracle oracle(甲骨文)
db2 IBM
sqlserver mic(微软)
非商业型数据库
mysql sun——-oracle(操作系统 java)
二.sql (结构化查询语言)
2.1 select语句 查询语句
select
2.2 ddl语句 数据定义语句
create table
drop table
alter table
2.3 dml语句 数据操作语句
insert
delete
update
2.4 tcl 事务控制语句
commit; 提交 确认
rollback; 回滚 撤销
savepoint 保存点;
2.5 dcl 数据控制语句
grant 授权
revoke 回收权限
三.使用数据库
oracle9i oracle10g oracle11g
telnet IP
telnet 192.168.0.26
telnet 192.168.0.23
openlab
open123
使用sqlplus 进入数据库
sqlplus
openlab
open123
SQL>
SQL>!clear
SQL>exit
查询数据库中的表结构
SQL>desc 表名;
desc s_emp;
SQL> desc s_emp;
Name Null? Type
ID 员工编号 NOT NULL NUMBER(7)
LAST_NAME 姓 NOT NULL VARCHAR2(25)
FIRST_NAME 名 VARCHAR2(25)
USERID 用户编号 VARCHAR2(8)
START_DATE 入职日期 DATE
COMMENTS 备注 VARCHAR2(255)
MANAGER_ID 领导的员工编号 NUMBER(7)
TITLE 职位 VARCHAR2(25)
DEPT_ID 部门编号 NUMBER(7)
SALARY 月薪 NUMBER(11,2)
COMMISSION_PCT 提成 NUMBER(4,2)
表头中的每个字段的名字
这个字段上 能不能不填值
字段的类型 number 数字
char varchar2 字符串类型
date 日期类型
四.select语句
4.1 from 子句
4.1.1 如何数据库的表中查询一个字段的值
select 字段名 from 表名;
把s_emp 表中 salary的数据查询出来
select salary from s_emp;
4.1.2 如何从表中查询 多个字段的值
select 字段名1,字段名2 from 表名;
把s_emp 表中 first_name salary 列出
select first_name,salary from s_emp;
4.1.3 思考如何把s_emp 表中所有的数据查询
出来。
select id,first_name,last_name ….;
select * from 表名;
4.1.4 表中字段数据的数学运算
+ - * /
把每个员工的工资加100显示出来
select salary,salary+100 from s_emp;
计算每个员工的月薪 和 年薪
select salary,salary*12 from s_emp;
select salary,100+salary*12 from s_emp;
select salary,(100+salary)*12 from s_emp;
sql中的除法不取整
select salary,salary/22 from s_emp;
4.1.5 数据库中的别名
字段或者表达式的后面 加一个名字
select salary sal,
(100+salary)*12 yearsal from s_emp;
字段或者表达式的别名只能有一个
select salary sal,
(100+salary)*12 year sal from s_emp;
要处理别名的原样显示 “别名”
select salary sal,
(100+salary)*12 “year Sal” from s_emp;
4.1.6 sql 中字符串的表达
使用单引号 引起一串字符
‘’ ‘ ‘ ‘a’ ‘hello world’ ‘_‘
显示s_emp 表中first_name last_name
select first_name,last_name from s_emp;
字符串的拼接 ||(字符串拼接符)
select first_name||last_name from s_emp;
@ _
select first_name||’@’||last_name
from s_emp;
first_name 和 last_name 之间拼接一个 ‘
this is this’s
%d %lf %%
select first_name||’’’’||last_name
from s_emp;
first_name 和 last_name 之间拼接两个 ‘
select first_name||’’’’’’||last_name
from s_emp;
select first_name||’’’’||’’’’||last_name
from s_emp;
4.1.7 null值的处理
null 值和任何值做运算 结果都是null
换一种年薪的计算方式
12salary+12salary*(commission_pct/100)
12salary(1+commission_pct/100)
列出每个员工的年薪 和 新的计算方式的年薪
select salary*12 yearsal,
salary12(1+commission_pct/100) newys
from s_emp;
null值的处理 null值处理函数
nvl(par1,par2) par1,par2可以是任意类型
但要求类型保持一致。
当par1为null 则返回par2的值
当par1不为null 就返回值par1的值
select salary*12 yearsal,
nvl(salary12(1+commission_pct/100),0) newys
from s_emp;
null 值要尽早处理
select salary*12 yearsal,
salary12(1+nvl(commission_pct,100)/100) newys
from s_emp;
显示每个员工的 first_name manager_id
salary 如果manager_id 为null 则显示
成-1
select first_name,nvl(manager_id,-1),
salary from s_emp;
4.1.8 数据的排重显示 distinct
(sql语句只关心做什么 不管怎么做)
select salary from s_emp;
select distinct salary from s_emp;
如何初始化sql编程环境
1.上传初始化脚本
2.运行脚本
SQL>@路径名/脚本名
显示 s_emp 表中 title 和 salary
select title ,salary from s_emp;
联合排重
select distinct title,salary from s_emp;
/ error /
-- 这是单行注释
select title,distinct salary
from s_emp;
select distinct id,title,salary
from s_emp;
4.2 where子句
4.2.1 作用
限制表中的数据返回,符合where条件数据
就被选中,不符合where条件数据就被过滤掉。
4.2.2 两个极限条件
where 1=1; / 恒等 /
where 1=2; / 永假 /
select id,first_name from s_emp;
select id,first_name from s_emp
where 1=1;
select id,first_name from s_emp
where 1=2;
no rows selected
4.2.3 number 类型条件的书写
找出工资大于1400的员工
列出id first_name salary
select id,first_name,salary from s_emp
where salary>1400;
select id,first_name,salary from s_emp
where salary<1400;
select id,first_name,salary from s_emp
where salary=1400;
select id,first_name,salary from s_emp
where salary!=1400;
4.2.4 字符串类型条件的书写
找出first_name 叫Carmen的 员工的
id,first_name,salary
select id,first_name,salary
from s_emp
where first_name=’Carmen’;
字符串类型 要注意单引号
字符串类型的值要严格区分大小写
sql语句不区分大小写
SELECT ID,FirSt_Name,salary
from s_emp
where first_name=’Carmen’;
4.2.5 常见条件运算符
> < >= <= = !=
4.2.6 sql 语句提供的运算符
4.2.6.1 表达区间 [a,b]
where 字段 between a and b;
s_emp 表中 salary在[1400,2500]
的员工的id ,salary
select id,salary
from s_emp
where salary between 1400
and 2500;
4.2.6.2 表达一个字段在一个范围内取值
where 字段 in (a,b,c,d)
部门编号是 31 或者 32 或者是50 的
员工的信息。列出id,first_name,dept_id
select id,first_name,dept_id
from s_emp
where dept_id in(31,32,50);
select id,first_name,dept_id
from s_emp
where dept_id in(32,31,50);
最终数据没有差别 但执行效率 可能受到
极大的影响。
出现概率高的放前面。
把s_emp 表中 id 是 1或者是10或者是25
的员工查询出来 列出id first_name
select id,first_name from s_emp
where id in(1,10,25);
4.2.6.3 模糊查询 like
李嘉诚 李小龙 李刚 李
成龙 史泰龙 龙龙 小龙女
ls *.txt
ls *.c
ls .
sql 中的统配符
% 0-n个任意字符
_ 1个任意字符
找出姓李的人
where name like ‘李%’;
name 中带龙的
where name like ‘%龙%’;
倒数第二个是龙
where name like ‘%龙_‘;
找出s_emp 表中first_name 中带a的
select first_name from s_emp
where first_name like ‘%a%’;
找出s_emp 表中first_name 中第二个字符
是a的。
select first_name from s_emp
where first_name like ‘_a%’;
desc user_tables;
select table_name from user_tables;
s_emp 员工表
s_dept 部门表
从user_tables 表中 找出table_name
符合S_ 开头的表名.
select table_name from user_tables
where tablename like ‘S%’;
处理_ 或者 %的转义问题
select table_name from user_tables
where tablename like ‘S\%’
escape ‘\‘;
从user_tables 表中 找出table_name
符合S__ 开头的表名.
select table_name from user_tables
where tablename like ‘S\_%’
escape ‘\‘;
4.2.6.4 NULL 值的判断
where 字段 is null;
如果字段的值 是NULL 就选中数据
如果不是NULL 就不选中。
找出提成是10的员工的
id first_name commission_pct
select id,first_name,commission_pct
from s_emp where commission_pct=10;
select id,first_name,commission_pct
from s_emp where commission_pct!=10;
select id,first_name,commission_pct
from s_emp
where commission_pct is null;
4.2.7 逻辑条件 连接符号
and
[a,b]
where 字段名>=a and 字段名<=b;
[1400,2500]
select id,first_name,salary
from s_emp
where salary>=1400 and
salary<=2500;
or
员工的编号 是1 或者是10 或者是15
的。列出id first_name salary
select id,first_name,salary
from s_emp
where id=1 or id=10 or id=25;
not
= != ^= <>
> <=
< >=
between a and b not between a and b
in not in (注意NULL)
like not like
is null is not null
4.3 数据排序
4.3.1 order by 排序标准;
永远出现在sql语句最后
4.3.2 按照工资排序(升序 asc
降序 desc)
显示 id first_name salary
select id,first_name,salary
from s_emp;
select id,first_name,salary
from s_emp order by salary;
select id,first_name,salary
from s_emp order by salary desc;
4.3.3 排序的语法
order by 排序标准 排序方式;
排序方式:
升序 asc 默认的顺序
自然顺序 字典顺序
降序 desc
4.3.4 多字段排序
select first_name,salary from s_emp
order by salary;
如果salary的值相同 则启用名字降序排列
select first_name,salary from s_emp
order by salary,first_name desc;
4.3.5 NULL 值在排序中的处理
NULL在排序中作为最大值
select id,manager_id from s_emp
order by manager_id;
4.4 单行函数
4.4.1 概念
针对sql语句影响的每一行 都返回一个结果。
sql影响多少行 就返回多少个结果。
select first_name,upper(first_name)
from s_emp;
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;
组函数:针对sql语句影响的一组数据做
处理 最终返回一个结果。
无论sql影响多少行 都返回一个结果。
select count(id) from s_emp
where id=1;
select count(id) from s_emp
where id>1;
select count(id) from s_emp
where id<1;
4.4.2 处理字符串的单行函数
upper 字符串变大写
lower 字符串变小写
select first_name,lower(first_name)
from s_emp;
测试表 dual
select * from dual;
select lower(‘HELLO’) FROM DUAL;
concat(par1,par2) 连接两个字符串
用的很少 因为有 ||
select concat(‘hello ‘,’world’)
from dual;
select concat(concat(‘hello ‘,’world’)
,’ hello’) from dual;
length 求字符串长度
select length(‘hello’) from dual;
initcap 把每个单词的首字母变大写
select initcap(‘one world one dream’)
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;
select substr(‘hello’,-1,2) from dual;
列出 s_emp 表中 first_name 以及
first_name的后三个字符
select first_name,substr(first_name,
-3,3) from s_emp;
replace 替换
select replace(‘hello’,’lo’,’test’)
from dual;
nvl(par1,par2)
4.4.3 数字处理函数
round 四舍五入函数
select round(9.57) from dual;
select round(9.99) from dual;
/ 保留小数点后一位 /
select round(9.57,1) from dual;
select round(9.54,1) from dual;
select round(9.547,2) from dual;
/ 对小数点前1位进行四舍五入/
select round(12.88,-1) from dual;
select round(12.88,-2) from dual;
trunc 截取
/ 截掉小数部分 /
select trunc(9.57) from dual;
select trunc(9.99) from dual;
/ 截取 并保留几位小数 /
select trunc(9.57,1) from dual;
select trunc(9.54,1) from dual;
select trunc(9.547,2) from dual;
/ 截取 对小数前的第几位进行截取 /
select trunc(12.88,-1) from dual;
select trunc(12.88,-2) from dual;
4.4.4 格式显示函数
to_char(par1,par2)
par1要处理数字数据
par2 是格式 可以省略 代表把数字变成
字符串类型。如果有格式 则按照格式显示
数字类型的数据。
格式如下:
fm 代表格式开头 可以省略
9 小数点前代表0-9的任意数字
小数点后1-9的任意数字
0 小数点前 代表强制显示前导零
12345 012,345
1234 001,234
小数点后 代表0-9的任意数字
$ 美元符号
L 本地货币符号 ￥ RMB
. 小数点
, 分隔符
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.远程登录服务器
telnet IP
2.切换shell
bash
3.修改配置文件
vi .bash_profile
export NLS_LANG=’SIMPLIFIED CHINESE_CHINA.ZHS16GBK’
export NLS_LANG=’AMERICAN_AMERICA.ZHS16GBK’
4.保存退出
esc
shift+zz
5.source .bash_profile
6.重新进入sqlplus
4.4.5 函数的嵌套
把一个函数的返回值作为另一个函数的参数。
把s_emp 表中 first_name 和 first_name
的后三个字符显示出来
结合 substr 和 length
select first_name,substr(first_name,
-3,3) from s_emp;
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
select id,first_name,
nvl(to_char(manager_id),’BOSS’)
from s_emp;
4.5 多表查询
4.5.1 为什么?
需要的数据来自于多张表。
表的设计决定的。
4.5.2 表的介绍
s_emp 员工表
id 员工编号
first_name 员工名
dept_id 部门编号
s_dept 部门表
id 部门编号
name 部门名
列出每个员工的id first_name和部门编号
select id,first_name,dept_id
from s_emp;
列出每个员工的id first_name和部门编号
还要把部门名显示出来。
字段如果重名则加表名区分
/* 两张表所有的记录匹配的可能
笛卡尔积 */
select s_emp.id,first_name,dept_id,
name
from s_emp,s_dept;
/* 表达表关系的条件 称之为 表的连接条件
员工所在的部门编号 是一个部门的编号
dept_id=s_dept.id */
select s_emp.id,first_name,dept_id,
name
from s_emp,s_dept
where dept_id=s_dept.id;
Day02
4.3 数据排序
4.3.1 order by 排序标准;
永远出现在sql语句最后
4.3.2 按照工资排序(升序 asc
降序 desc)
显示 id first_name salary
select id,first_name,salary
from s_emp;
select id,first_name,salary
from s_emp order by salary;
select id,first_name,salary
from s_emp order by salary desc;
4.3.3 排序的语法
order by 排序标准 排序方式;
排序方式:
升序 asc 默认的顺序
自然顺序 字典顺序
降序 desc
4.3.4 多字段排序
select first_name,salary from s_emp
order by salary;
如果salary的值相同 则启用名字降序排列
select first_name,salary from s_emp
order by salary,first_name desc;
4.3.5 NULL 值在排序中的处理
NULL在排序中作为最大值
select id,manager_id from s_emp
order by manager_id;
4.4 单行函数
4.4.1 概念
针对sql语句影响的每一行 都返回一个结果。
sql影响多少行 就返回多少个结果。
select first_name,upper(first_name)
from s_emp;
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;
组函数:针对sql语句影响的一组数据做
处理 最终返回一个结果。
无论sql影响多少行 都返回一个结果。
select count(id) from s_emp
where id=1;
select count(id) from s_emp
where id>1;
select count(id) from s_emp
where id<1;
4.4.2 处理字符串的单行函数
upper 字符串变大写
lower 字符串变小写
select first_name,lower(first_name)
from s_emp;
测试表 dual
select * from dual;
select lower(‘HELLO’) FROM DUAL;
concat(par1,par2) 连接两个字符串
用的很少 因为有 ||
select concat(‘hello ‘,’world’)
from dual;
select concat(concat(‘hello ‘,’world’)
,’ hello’) from dual;
length 求字符串长度
select length(‘hello’) from dual;
initcap 把每个单词的首字母变大写
select initcap(‘one world one dream’)
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;
select substr(‘hello’,-1,2) from dual;
列出 s_emp 表中 first_name 以及
first_name的后三个字符
select first_name,substr(first_name,
-3,3) from s_emp;
replace 替换
select replace(‘hello’,’lo’,’test’)
from dual;
nvl(par1,par2)
4.4.3 数字处理函数
round 四舍五入函数
select round(9.57) from dual;
select round(9.99) from dual;
/ 保留小数点后一位 /
select round(9.57,1) from dual;
select round(9.54,1) from dual;
select round(9.547,2) from dual;
/ 对小数点前1位进行四舍五入/
select round(12.88,-1) from dual;
select round(12.88,-2) from dual;
trunc 截取
/ 截掉小数部分 /
select trunc(9.57) from dual;
select trunc(9.99) from dual;
/ 截取 并保留几位小数 /
select trunc(9.57,1) from dual;
select trunc(9.54,1) from dual;
select trunc(9.547,2) from dual;
/ 截取 对小数前的第几位进行截取 /
select trunc(12.88,-1) from dual;
select trunc(12.88,-2) from dual;
4.4.4 格式显示函数
to_char(par1,par2)
par1要处理数字数据
par2 是格式 可以省略 代表把数字变成
字符串类型。如果有格式 则按照格式显示
数字类型的数据。
格式如下:
fm 代表格式开头 可以省略
9 小数点前代表0-9的任意数字
小数点后1-9的任意数字
0 小数点前 代表强制显示前导零
12345 012,345
1234 001,234
小数点后 代表0-9的任意数字
$ 美元符号
L 本地货币符号 ￥ RMB
. 小数点
, 分隔符
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.远程登录服务器
telnet IP
2.切换shell
bash
3.修改配置文件
vi .bash_profile
export NLS_LANG=’SIMPLIFIED CHINESE_CHINA.ZHS16GBK’
export NLS_LANG=’AMERICAN_AMERICA.ZHS16GBK’
4.保存退出
esc
shift+zz
5.source .bash_profile
6.重新进入sqlplus
4.4.5 函数的嵌套
把一个函数的返回值作为另一个函数的参数。
把s_emp 表中 first_name 和 first_name
的后三个字符显示出来
结合 substr 和 length
select first_name,substr(first_name,
-3,3) from s_emp;
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
select id,first_name,
nvl(to_char(manager_id),’BOSS’)
from s_emp;
4.5 多表查询
4.5.1 为什么?
需要的数据来自于多张表。
表的设计决定的。
4.5.2 表的介绍
s_emp 员工表
id 员工编号
first_name 员工名
dept_id 部门编号
s_dept 部门表
id 部门编号
name 部门名
列出每个员工的id first_name和部门编号
select id,first_name,dept_id
from s_emp;
列出每个员工的id first_name和部门编号
还要把部门名显示出来。
字段如果重名则加表名区分
/* 两张表所有的记录匹配的可能
笛卡尔积 */
select s_emp.id,first_name,dept_id,
name
from s_emp,s_dept;
/* 表达表关系的条件 称之为 表的连接条件
员工所在的部门编号 是一个部门的编号
dept_id=s_dept.id */
select s_emp.id,first_name,dept_id,
name
from s_emp,s_dept
where dept_id=s_dept.id;
-———————————————————
1.下载安装压缩包 解压
2.找到安装文件位置 安装
sudo dpkg -i gftp*
3.使用gftp
gftp
code.tarena.com.cn
-———————————————————
4.5.3 笛卡尔积
如果两张表 没有任何关联条件 则会产生
笛卡尔积。
s_dept
id 部门编号
name 部门名
region_id 地区编号
s_region
id 地区编号
name 地区名
把每个部门的名字 和 对应的地区名列出
select s_dept.name,s_region.name
from s_dept,s_region
where region_id=s_region.id;
4.5.4 使用表的别名
select d.name,r.name
from s_dept d,s_region r
where region_id=r.id;
4.5.5 列出每个员工的first_name 以及部门
的名字 还有地区的名字。
s_emp s_dept s_region
select e.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;
col 字段名 for a宽度
col name for a15
4.5.6 连接两张表的条件 都使用的是等号
则这种连接称之为等值连接。
连接两张表的条件 使用的是非等号
则这种连接称之为非等值连接。
desc salgrade;
SQL> desc salgrade;
Name
——————————-
GRADE 工资级别
LOSAL 这个级别对应的低工资
HISAL 这个级别对应的高工资
把每个员工的工资 和 工资对应的工资级别
列出来。
select salary,grade
from s_emp,salgrade
where salary between
losal and hisal;
select salary,grade
from s_emp,salgrade
where salary>=losal
and salary<=hisal;
4.5.7 特殊的连接 (自连接)
s_emp 领导 普通员工
id fname manager_id
2 a 2
3 c 2
4 d 2
如果你的id 是另外一个员工的manager_id
则你是领导。
select id,first_name
from s_emp
where id=manager_id;
一张表中存储了 两种不同业务含义的数据。
找出那些人是领导？
select distinct m.id,m.first_name
from s_emp e,s_emp m
where e.manager_id=m.id;
25个员工 其中有8个领导 剩下17普通员工
4.5.8 等值连接
非等值连接
自连接
都是符合连接条件的数据被选中
不符合连接条件的被过滤掉。
这种连接 统称内连接。
select distinct m.id,m.first_name
from s_emp e,s_emp m
where e.manager_id!=m.id;
得到是过滤掉的数据
外连接: 外连接的结果集 等于 内连接的
结果集加上匹配不上的记录。
(一个也不能少)
oracle 使用(+) 来完成外连接
(+) 字段对面的表的数据 全部匹配出来。
select distinct m.id,m.first_name
from s_emp e,s_emp m
where e.manager_id(+)=m.id;
//被找回的数据 是通过NULL 记录匹配的
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;
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 not null;
4.5.9 练习
列出 部门的名字 和 对应的地区名
s_dept s_region
select d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id;
公司业务扩展 新增了部门
insert into s_dept values(100,
‘test’,NULL);
commit;
列出 每个部门的名字 和 对应的地区名
没有地区的部门名也要显示。
select d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id(+);
列出每个员工的first_name 和对应的部门名
s_emp s_dept
select e.first_name,d.name
from s_emp e,s_dept d
where dept_id=d.id;
把老板部门号 变成NULL
update s_emp set dept_id=NULL
where id=1;
commit;
列出每个员工的first_name 和对应的部门名
没有部门的员工也要显示
select e.first_name,d.name
from s_emp e,s_dept d
where dept_id=d.id(+);
4.5.10 给老板涨工资
update s_emp set salary=12500
where id=25;
commit;
列出每个员工的id salary 和工资级别
不在工资级别范围内的员工也要显示。
select id,salary,grade
from s_emp,salgrade
where salary between
losal(+) and hisal(+)
order by id;
xdjmq01
S6656666
vm\**.zip
Day03
一.SQL99 中的内外连接
1.1 sql99 中的内连接
列出每个员工的 first_name 和对应的部门名
select first_name,name
from s_emp e, s_dept d
where e.dept_id=d.id;
from a表 join b表 on 连接条件;
from a表 inner join b表 on 连接条件;
select first_name,name
from s_emp e join s_dept d
on e.dept_id=d.id;
select first_name,name
from s_emp e inner join s_dept d
on e.dept_id=d.id;
1.2 sql99 中的外连接
a表 left outer join b 表 on 连接条件;
a表 right outer join b 表 on 连接条件;
a表 full outer join b 表 on 连接条件;
/* (+) 字段对面的表的数据全部匹配出来
left outer join 就是左边的表发起
连接,左边的表的数据全部匹配出来*/
增加一个新部门
insert into s_dept values(100,’test’,NULL);
commit;
列出所有的部门名 和 对应的地区名
(+)
select d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id(+);
left outer join
select d.name,r.name
from s_dept d left outer
join s_region r
on d.region_id=r.id;
right outer join
select d.name,r.name
from s_region r right outer join s_dept d
on d.region_id=r.id;
/ 使用左外连接 完成谁是普通员工?/
先使用 (+) id first_name
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;
select distinct m.id,m.first_name
from s_emp m left outer join s_emp e
on e.manager_id=m.id
where e.manager_id is null;
全外连接的结果集 等于左外连接的结果集
加上右外连接的结果集 然后减去重复的记录。
oracle 如何实现全外连接?
oracle不是通过两端都加(+)
是通过关键字
union 合并两个结果集 并排重
union all 合并两个结果集 不排重
select id from s_emp union
select id from s_emp;
select id from s_emp union all
select id from s_emp;
多表查询:
内连接
等值连接
非等值连接
自连接
外连接
等值连接
非等值连接
自连接
SQL99 内外连接
内连接
a表 join b表 on 连接条件;
a表 inner join b表 on 连接条件;
外连接
a表 left outer join b 表 on 连接条件;
a表 right outer join b 表 on 连接条件;
a表 full outer join b 表 on 连接条件;
union
union all
二.组函数
2.1 特点
无论sql语句影响多少行 最终返回一个结果
2.2 常见的组函数
count 统计个数
max 统计一组数据的最大值
min 统计最小值
sum 统计和
avg 统计平均值
2.3 举例
统计 s_emp 表中的 员工人数 工资的
最大值 工资的最小值
select count(id),max(salary),
min(salary) from s_emp;
2.4 统计s_emp 中的工资的和 和
工资的平均值。
select sum(salary),avg(salary)
from s_emp;
select sum(distinct salary),
avg(distinct salary)
from s_emp;
2.5 测试组函数对NULL值的处理方式
统计s_emp提成的个数 提成的平均值
忽略
select count(commission_pct),
avg(commission_pct) from s_emp;
三.分组
3.1 按照一定的标准 把数据分成若干组。
3.2 where 条件 group by 分组标准.
3.3 按照部门编号分组 统计每个部门的人数
select dept_id,count(id)
from s_emp
where 1=1
group by dept_id;
3.4 按照部门编号分组 统计每个部门的
平均工资。
select dept_id,avg(salary)
from s_emp
where 1=1
group by dept_id;
按照部门编号分组 统计每个部门的
平均工资。列出平均工资大于1400的部门
和 平均工资。
select dept_id,avg(salary)
from s_emp
where 1=1
group by dept_id;
3.5 如何对组数据进行过滤
需要使用 having 数据过滤条件
select dept_id,avg(salary)
from s_emp
where 1=1
group by dept_id
having avg(salary)>1400;
3.6 按照部门号分组 统计每个部门的人数
显示人数大于2的部门编号 和 人数。
select dept_id,count(id) ec
from s_emp
group by dept_id
having count(id)>2
order by ec;
from
where
group by
having
select
order by
3.7 列出每个部门的平均工资
要求显示平均工资大于1400的部门
编号和 平均工资。还要求显示部门名。
s_emp s_dept
select dept_id,avg(salary),d.name
from s_emp e,s_dept d
where e.dept_id=d.id
group by dept_id,d.name
having avg(salary)>1400;
select dept_id,avg(salary),max(d.name)
from s_emp e,s_dept d
where e.dept_id=d.id
group by dept_id
having avg(salary)>1400;
结论:在分组语句中 select 后的字段 要么
是分组标准要么是经过合适的组函数处理过的。
四.子查询
4.1 概念
把一条sql 的执行结果 另一条sql的操作基础。
4.2 子查询出现的位置
where
having
from
4.3 where 之后
/ 查询所有的领导编号 /
select distinct manager_id from s_emp;
/* 如果一个员工的编号 出现在领导编号中
则这个员工是领导*/
in 等于其中的任何一个
select id,first_name from s_emp
where id in (select distinct
manager_id from s_emp);
/ 查询所有的非领导 列出 id ,first_name/
not in 不等于其中任何一个数据
id not in (1,2,3,NULL);
select id,first_name from s_emp
where id not in (select distinct
manager_id from s_emp);
select id,first_name from s_emp
where id not in (select distinct
manager_id from s_emp where
manager_id is not null);
4.4 子查询出现在having 后
按照部门号分组,列出每个部门的平均工资
要求显示平均工资大于42部门的平均工资
的部门，显示部门号 和平均工资.
select dept_id ,avg(salary)
from s_emp
group by dept_id
having avg(salary)>(
select avg(salary) from
s_emp where dept_id=42);
4.5 子查询出现在from之后
任何的查询语句 可以看成一张内存表
select id,first_name name ,salary
from s_emp;
select * from ( select id,first_name
name ,salary from s_emp )
where salary>1000;
select*from(select dept_id ,avg(salary)
asal from s_emp
group by dept_id) where asal>(
select avg(salary) from
s_emp where dept_id=42);
五.建表 和 删表
5.1 建立表
create table 表名(
字段名 类型,
字段名 类型,
字段名 类型
);
类型:
number
name char(20) 定长字符串
name varchar2(20) 变长字符串
date 日期类型
5.2 建立一张员工表
create table myemp9528(
id number,
fname char(10),
sname varchar2(10),
salary number
);
5.3 删除表
drop table myemp9528;
create table myemp9528(
id number,
fname char(10),
sname varchar2(10),
salary number
);
六.DML (数据操作语句)
6.1 insert 增加数据
insert into 表名 values(字段1值,
字段2值,字段3值);
insert into myemp9528 values(
9527,’xsy’,’xsy’,123456);
commit;
select length(fname),length(sname)
from myemp9528 where id=9527;
select * from myemp9528
where fname=’xsy ‘;
select * from myemp9528
where sname=’xsy’;
可以选择部分字段 插入值
没有选择的字段 以NULL值插入
必须包含所有的非空字段
insert into 表名(字段名1,字段名3)
values(字段1值,字段3值);
insert into myemp9528(id,sname)
values(2008,’bjtest’);
commit;
select * from myemp9528;
insert into s_dept values(100,
‘test’,NULL);
insert into myemp9528(id,sname)
values(1,’bdl’);
commit;
6.2 删除数据
delete from 表名 where 条件;
commit;
delete from myemp9528 where id=1;
commit;
6.3 更新数据
update 表名 set 字段1名=值
where 条件;
update 表名 set 字段1名=值1,
字段2名=值2
where 条件;
commit; | rollback;
update myemp9528 set salary=8888
where sname=’bdl’;
update s_emp set salary=12500
,first_name=’bdl’ where id=12;
commit;
七.事务 （交易）
commit; 提交
rollback; 回滚
savepoint 保存点名;
原子性:
事务中的操作 是一个不可分割的整体。
转账
update account
set salary=salary-20000 where ano=’1’;
a
update account
set salary=salary+20000 where ano=’2’;
b
if(a&&b){
commit;
}else{
rollback;
}
事务中的操作 要么一起成功 要么一起失败
隔离性:
一个事务中的操作 在没有提交以前 对
另一个事务而言 数据的变化是不可见的。
drop table testtcl;
create table testtcl(
id number primary key,
salary number
);
insert into testtcl values(1,2000);
insert into testtcl values(2,4000);
一致性:
-20000;
+20000
-50
持久性:
保存点:
让事务做到 部分成功 部分失败。
insert
savepoint a;
update
savepoint b;
insert
savepoint c;
if(c){
rollback to b;
commit;
}
drop table testtcl2;
create table testtcl2(
id number primary key,
salary number
);
insert into testtcl2 values(2,2000);
savepoint a;
insert into testtcl2 values(3,3000);
savepoint b;
insert into testtcl2 values(4,4000);
savepoint c;
insert into testtcl2 values(5,5000);
savepoint d;
rollback to b;
commit;
八.日期类型
date
8.1 日期类型的表达
‘dd-MON-yy’
select start_date from s_emp;
8.2 把s_emp 表中 id first_name start_date
要求按照start_date排序
select id,first_name,start_date
from s_emp order by start_date;
8.3 日期格式显示
to_char(日期数据,’日期格式’)
yyyy 四位年
mm 2位月
dd 两位天
hh 12小时制
hh24
mi 分钟
ss 秒
day 星期几
month 月的全写
mon 月的缩写
pm 上午am 下午pm
select id,first_name,
to_char(start_date,
‘yyyy-mm-dd hh24:mi:ss’)
from s_emp order by start_date;
select id,first_name,
to_char(start_date,
‘yyyy-mm-dd hh24:mi:ss day pm’)
from s_emp order by start_date;
8.4 如何插入日期
建立一张订单表
drop table myorder9527;
create table myorder9527(
ono varchar2(50) primary key,
oname varchar2(30),
omoney number,
odate date
);
insert into myorder9527 values(
‘bj002’,’test002’,168.75,’18-AUG-14’);
commit;
select ono,to_char(odate,
‘yyyy-mm-dd hh24:mi:ss’)
from myorder9527;
8.5 直接插入当前系统时间
insert into myorder9527 values(
‘bj003’,’test003’,168.75,sysdate);
commit;
to_date(‘日期字符串’,’日期格式’)
根据日期格式 把日期字符串 转换成日期
2008-08-08 20:08:08
2012-12-21 23:59:59
2020-01-01 00:48:15
Day04
八.日期类型
date
8.1 日期类型的表达
‘dd-MON-yy’
select start_date from s_emp;
8.2 把s_emp 表中 id first_name start_date
要求按照start_date排序
select id,first_name,start_date
from s_emp order by start_date;
8.3 日期格式显示
to_char(日期数据,’日期格式’)
yyyy 四位年
mm 2位月
dd 两位天
hh 12小时制
hh24
mi 分钟
ss 秒
day 星期几
month 月的全写
mon 月的缩写
pm 上午am 下午pm
select id,first_name,
to_char(start_date,
‘yyyy-mm-dd hh24:mi:ss’)
from s_emp order by start_date;
select id,first_name,
to_char(start_date,
‘yyyy-mm-dd hh24:mi:ss day pm’)
from s_emp order by start_date;
8.4 如何插入日期
建立一张订单表
drop table myorder9527;
create table myorder9527(
ono varchar2(50) primary key,
oname varchar2(30),
omoney number,
odate date
);
insert into myorder9527 values(
‘bj002’,’test002’,168.75,’18-AUG-14’);
commit;
select ono,to_char(odate,
‘yyyy-mm-dd hh24:mi:ss’)
from myorder9527;
8.5 直接插入当前系统时间
insert into myorder9527 values(
‘bj003’,’test003’,168.75,sysdate);
commit;
to_date(‘日期字符串’,’日期格式’)
根据日期格式 把日期字符串 转换成日期
2008-08-08 20:08:08
2012-12-21 23:59:59
2020-01-01 00:48:15
8.6 to_date(‘日期字符串’,’日期格式’)
insert into 表名 values(to_date(
‘2008-08-08 20:08:08’,’yyyy-mm-dd hh24:mi:ss’));
insert into myorder9527(ono,odate)
values(‘testbj003’,
to_date(‘2008-08-08 20:08:08’,
‘yyyy-mm-dd hh24:mi:ss’));
insert into myorder9527(odate) values(
to_date(‘2008-08-08 20:08:08’,
‘yyyy-mm-dd hh24:mi:ss’));
8.7 日期的调整
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’) from dual;
/ 1代表一天 /
select to_char(sysdate+1,
‘yyyy-mm-dd hh24:mi:ss’) from dual;
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(sysdate+1/24,
‘yyyy-mm-dd hh24:mi:ss’) from dual;
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(sysdate+1/(24*60),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
1 1/24 1/(2460) 1/(2460*60)
8.8 特殊调整
按照月为单位进行调整
add_months(日期,月数)
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(add_months(sysdate,3),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
select months_between(sysdate,sysdate+5)
from dual;
last_day(日期) 一个月份对应的这个月的最后一天
的时间点
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(last_day(sysdate),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
next_day(日期,’星期几’)
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(next_day(sysdate,’monday’),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(next_day(
next_day(sysdate,’friday’),’friday’),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
round(日期) 默认以天为单位对日期进行四舍五入
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(round(sysdate),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
round(日期,’日期单位格式’)
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(round(sysdate,’mm’),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(round(sysdate,’hh’),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
trunc(日期) 默认以天为单位对日期进行截取
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(trunc(sysdate),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
round(日期,’日期单位格式’)
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(trunc(sysdate,’mm’),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
select to_char(sysdate,
‘yyyy-mm-dd hh24:mi:ss’),
to_char(trunc(sysdate,’hh’),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
8.9 把一个日期字符串 ‘2008-08-08 20:08:08’
经过转换 得到一个对应的日期。然后得到
这个日期对应的月的最后一天的最后一秒
对应的时间点。使用to_char 验证是否正确。
2008-08-31 23:59:59
2012-12-21 10:05:04
2012-12-31 23:59:59
select to_char(
trunc(last_day(to_date(‘2008-08-08 20:08:08’,
‘yyyy-mm-dd hh24:mi:ss’))+1)-1/(246060),
‘yyyy-mm-dd hh24:mi:ss’) from dual;
select to_char(
trunc(add_months(to_date(‘2008-08-08 20:08:08’,
‘yyyy-mm-dd hh24:mi:ss’),1),’mm’)
-1/(246060),’yyyy-mm-dd hh24:mi:ss’)
from dual;
九.数据库中的约束
9.1 约束
对数据库中的字段 对应的数据可以加限制。
9.2 种类
主键 primary key
字段的值 非空 并且 唯一
一个表只能有一个主键
唯一 unique
字段的值 必须保持不能重复
非空 not null
字段的值 不能是NULL值
检查 check
字段的值 必须符合检查条件
外键约束 reference (关联 引用)
foreign key
on delete cascade
on delete set null
9.3 约束的具体实现
列级约束:在定义表的某一列时 直接对这一列加
约束限制。
表级约束:在定义完表的所有列之后 再选择某些
列加约束限制。
9.4 主键的列级约束
drop table testcolumn_cons;
create table testcolumn_cons(
id number primary key,
name varchar2(20)
);
insert into testcolumn_cons
values(1,’test1’);
ERROR at line 1:
ORA-00001: unique constraint
(OPENLAB.SYS_C00444679) violated
如果加约束时 不给约束起名字 则系统会自动
分配一个约束名。
可以给约束命名
drop table testcolumn_constest1;
create table testcolumn_constest1(
id number constraint
testcolumn_constest1_id_pk primary key,
name varchar2(20)
);
insert into testcolumn_constest1
values(1,’test1’);
ERROR at line 1:
ORA-00001: unique constraint
(OPENLAB.TESTCOLUMN_CONSTEST1_ID_PK) violated
9.5 建立一张表
id number 设置成主键
fname varchar2(30) 设置成非空
sname varchar2(30) 设置成唯一
salary number 设置检查 必须大于3500
drop table testemp1986abc;
create table testemp1986abc(
id number constraint
testemp1986abc_id_pk primary key,
fname varchar2(20) constraint
testemp1986abc_fname_nn not null,
sname varchar2(20) constraint
testemp1986abc_sname_uk unique,
salary number constraint
testemp1986abc_salary_ck check(
salary>3500 )
);
9.6 表级约束 主键
表级约束 可以完成联合约束
create table testtable_cons(
id number,
name varchar2(30),
salary number ,
constraint testtable_cons_id_pk
primary key(id,name)
);
create table testtable_cons(
id number,
name varchar2(30),
salary number ,
constraint testtable_cons_id_pk
primary key(id),
constraint testtable_cons_name_uk
unique (name),
constraint testtable_cons_salary_ck
check(salary>3500)
);
十.外键约束
10.1 涉及到两张表 一张叫父表(主表)
一张叫子表(从表)。子表中的外键字段的取值
受限于父表中字段的取值。
外键的取值 要么取父表中字段的值
要么取NULL值。
10.2 外键关系的实现
10.2.1 建立表
一般先建立父表 后建立子表
drop table parent1234a;
create table parent1234a(
id number primary key,
name varchar2(30)
);
drop table child1234a;
create table child1234a(
id number primary key,
age number,
fid number constraint
child1234a_fid_fk references
parent1234a(id)
);
10.2.2 插入数据
一般先插入父表数据 后插入子表数据
否则子表的外键取值要取NULL
insert into child1234a values(
9527,50,1);
ERROR at line 1:
ORA-02291: integrity constraint
(OPENLAB.CHILD1234A_FID_FK) violated -
parent key not found
insert into child1234a values(
9527,50,NULL);
insert into parent1234a values(
1,’parent1’);
insert into child1234a values(
9529,55,1);
10.2.3 删除数据?
先删子表中和父表关联的数据
再删父表数据
级联 （on delete cascade ）
10.2.4 删除表
先删子表 后删父表
/ 先解除主外键关系 后删表 /
drop table 表名 cascade constraints;
10.3 级联删除 和 级联置空
on delete cascade
员工表 m (子表)
id
ename
eage
dept_id
部门表 1 （主表）
id
name
drop table mydept1402 cascade constraints;
create table mydept1402(
id number primary key,
name varchar2(30)
);
insert into mydept1402 values(1,’app’);
insert into mydept1402 values(2,’test’);
commit;
drop table myemp1402 cascade constraints;
create table myemp1402(
id number primary key,
ename varchar2(30),
eage number,
dept_id number constraint
myemp1402_dept_id_fk references
mydept1402(id)
);
insert into myemp1402 values(1,’ea’,
24,1);
insert into myemp1402 values(2,’eb’,
25,1);
insert into myemp1402 values(3,’ec’,
26,2);
insert into myemp1402 values(4,’ed’,
24,2);
insert into myemp1402 values(5,’ee’,
27,2);
commit;
on delete cascade 删除主表数据时 会把
和主表关联的子表数据删除。
on delete set null 删除主表数据时 会把
和主表关联的子表数据的外键置成null。
10.4 修改脚本 把列级约束的外键实现 写成
表级约束的实现。
ALTER TABLE s_emp
ADD CONSTRAINT s_emp_dept_id_fk
FOREIGN KEY (dept_id)
REFERENCES s_dept (id);
先建立两张表 然后通过修改表结构 增加
外键约束，但这样对数据要求非常严格。
十一.数据库中其它对象
11.1 序列 sequence
用来产生主键的值。
如何创建序列
create sequence 序列名;
如何使用
在需要主键值的地方 写 序列名.nextval
create sequence testmyseqbdl_id;
create table testmyseqbdl(
id number primary key,
name varchar2(30)
);
insert into testmyseqbdl values(
testmyseqbdl_id.nextval,
‘test’||testmyseqbdl_id.currval);
11.2 索引
目的:
加速查询。
3亿 8*60 全表扫描
0.01 索引查找
原理:
通过树状结构组织数据 通过消耗
大量的时间 和 空间 来加速查询。
语法:
具有唯一性字段的数据 会自动建立索引
叫唯一性索引。
create index 索引名
on 表名(字段名);
set timing on;
create table testemp1402
as select id,first_name name,
salary from s_emp;
create index testemp1402_name_ind
on testemp1402(name);
删除索引
drop index 序列名;
11.3 视图 view
本质:
视图本质上 就是一条sql语句。
如何创建视图
create or replace view 视图名 as
select 语句;
可以对同一份物理数据 做不同的表现
可以简化查询
create or replace view myview
as select id ,first_name name,
salary from s_emp;
select * from (select id ,first_name
name, salary from s_emp);
select * from myview;
十二.分页技术
oracle rownum
sqlserver top
mysql limit m,n
rownum 行号 伪列
select rownum, id,first_name from s_emp;
一页显示 5 行 取第一页数据
select rownum, id,first_name from s_emp
where rownum<6;
select * from(select rownum r, id,
first_name from s_emp where rownum<11)
where r>5;
按照salary 排序 一页显示 5 条数据
显示第二页数据。
first_name salary
select first_name ,salary from s_emp
order by salary;
先排序 还是先编号?
select rownum, first_name ,salary
from s_emp
order by salary;
先排序 后编号
select * from(
select rownum r,first_name,salary from
(select first_name ,salary
from s_emp
order by salary
)where rownum<11
)where r>5;
按照某个字段排序 要第n页数据 至少三层查询
最内层 负责排序
中间层 负责编号 并去rownum特性
最外层 在去除rownum特性的基础上
过滤掉 第n-1页数据
按照salary 排序 一页显示 5 条数据
显示第三页数据。
select * from(
select rownum r,first_name,salary from
(select first_name ,salary
from s_emp
order by salary
)where rownum<n*pagesize+1
)where r>(n-1)*pagesize;
select * from(
select rownum r,first_name,salary from
(select first_name ,salary
from s_emp
order by salary
)where rownum<3*5+1
)where r>(3-1)*5;
select * from(
select rownum r,first_name,salary from
(select first_name ,salary
from s_emp
order by salary
)where rownum<3*11+1
)where r>(3-1)*11;
