数据库知识点一(Oracle)
数据库发展的三个阶段：
- 人工管理阶段
- 文件系统管理阶段
- 数据库系统管理阶段
关系型数据库：即用表来表示实体间的关系，关系即表的意思
SQL语句
结构化查询语句的分类
- DQL（数据查询语言）：select 、from
- DML（数据操作语言）：insert、update、delete
- DCL（数据控制语言）：grant（授权）、revoke（撤销）
- DDL（数据定义语言）：create、drop（删除）、alter（修改）
- TPL（事务处理语言）：commit（提交）、rollback（回滚）
关键字：sql语言保留的字符串
语句：一条你完整的SQL语句
子句：部分sql语句
sql语句书写规则：
- 关键字、表名、列名不分大小写，但行记录区分大小写
- 建议多行书写，增强代码可读性
- 关键字不可以缩写、分开及跨行
数据类型：
| 数据 | 类型 | 精度 |
|---|---|---|
| number | float型和int型 | |
| number(4) | 整型 | -104~10(4-1) |
| number(7,2) | 浮点型 | 整数5位，小数2位，总共7位 |
| varchar2(255) | 长度可变的字符类型 | 如varchar2(4) 存一个字占两个字节 |
| char(255) | 长度不可变的字符类型 | 如char(4) 存一个字占满四个字节，不足用空格填充 |
| date | 日期类型 | 精确到年月日 |
| timestamp | 时间戳 | 精确到年月日时分秒 |
示例表：emp、dept、salgrade
| emp | 员工表 | dept | 部门表 | salgrade | 薪资等级表 |
|---|---|---|---|---|---|
| 列名 | 描述 | ||||
| empno | 员工编号 | deptno | 部门编号 | grade | 薪资等级 |
| ename | 员工姓名 | dname | 部门名称 | losal | 薪资下线 |
| job | 工作岗位 | loc | 部门地点 | hisal | 薪资上线 |
| mgr | 经理编号 | ||||
| hiredate | 日志日期 | ||||
| sal | 工资 | ||||
| comm | 年终奖金 | ||||
| deptno | 部门编号 |
简单的sql语句
select子句：限定查询的结果列
from子句：限定查询数据的来源
where子句：返回限定数据行
order by子句：对结果集数据排序：asc：升序 desc:j降序
group by 子句：对数据行分组，select列表中除分组函数项，所有列都要在group by中
having子句：对分组结果进行筛选
语句执行顺序：
from–>where–>group by–>having–>select–>order by
-
任何控制运算结果都为空
-
排除null对运算的影响：
nvl(a,b)：若a为null值则用b来替换
select sal*12+nvl(comm,0),ename from emp; -
连接操作符：||
select ename || sal *yoursal* from emp; <!--yoursal--列别名--> -
原义字符串：所有原义字符在结果集中显示；日期和字符用单引号
select ename || '的工资是' || sal yoursal from emp; -
消除重复行：distinct
select distinct dedptno from emp; -
显示表结构（在命令行窗口执行）：desc 表名；
–>打开command windows–>edit–>
限制数据，对数据排序
-
数值类型直接比较，字符类型用单引号，区分大小写
-
日期类型比较：
select * from emp where hiredate>'31-12月-1981';- Oracle中默认DD-MON-RR格式：
- 作为比较值用单引号引起来
-
特殊比较运算符： between 下限 and 上限 -->判断是否在范围内
select * from emp where sal between 1200 and 3000; -
IN运算符：判断是否在集合中
select * from emp where dept in (10,30); -
Like运算符：模糊查询： %：匹配0个或多个字符 _:匹配一个字符
select * from emp where ename like 'SM@_%' escape'@'; --结果：SM_?? select * from emp where ename like '_M%'; -- 结果：_SM? -
Is NULL：判断是否为空值
select * from emp where comm is null;运算符：
算数运算符：* 、/、+、-
连接运算符：||
比较运算符：<、>、=
特殊比较运算符：between…and…
逻辑运算符：not、and、or
运算优先级：
括号 > 算数运算符 > 连接运算符 || > 比较运算符 > 特殊比较运算符 > 逻辑运算符
（） ((* 、/）>(+、-)) ((±)=||) (<、>、=） (between…and…) (not>and>or)
-
order by 子句：对结果集数据排序：asc：升序 desc:j降序
- 默认升序
- 空值最大
- 最后执行，放在最后
- 可根据列名/序号/别名排序
- 先写先排序，多列用逗号隔开
select * from emp order by job desc,sal asc; **字符按英文字母排序，工作相同则工资升序**
字符函数
select 1,2 from dual; --dual--虚表，不存在
-
lower：字符转小写
-
upper：字符转大写
-
Initcap:首字母大写
select initcap('liang xIN') from dual; --Liang Xin
字符处理函数
concat(a,b)：连接字符串，相当于||（只能接收两个字符，但可以嵌套）
select concat(concat(ename,'的工资为'),sal) 工资 from emp;
substr(a,b[,c])：截取字符串 :从a的b位置开始往右截取c个长度的字符串
a:目标字符串
b:开始位置 默认从1开始 +b:从左到右b位 -b:从右到左b位
c:截取长度(包含开始，可省略，默认向右取所有)
select substr('liangxinru',3,3) from dual; --ang--
length:字符串长度
Instr(a,b[,c] [,d]): 查找字符串 ：从c位置开始在a中寻找b第d次出现的位置
a:目标字符串 b:查找字符串 c:开始引索（从1开始，默认为1，包含开始） d:第几次出现
-c时，索引位置为从右往左第|c|位，从左往右查找
select instr('liangxinru','u',-2,1) from dual; --从倒数第二个字符开始从左往右查找'u'第二次出现的位置 10
lpad(s1,n1,s2):左填充 返回s1被s2从左填充到n1长度后的字符串，结果长度位n1
rpad(s1,n1,s2):右填充 返回s1被s2从右填充到n1长度后的字符串，结果长度位n1
select lpad(ename,10,'*')from dual;
replace(s1,s2,s3):字符替换函数 把s1中的s2用s3替换
select replace('今天星期三','三','四')from dual; --今天星期四
trim:去除字符串头尾：select trim (leading|trailing|both ‘trim_character’ from ‘trim_source’)
--select trim (leading|trailing|both 'trim_character' from 'trim_source')from dual;
select trim (both 'H' from 'Hello world') from dual; --ello world
数值函数
round(a[,b]) ：四舍五入 b:b=0\b省略：整数位 b:小数点后b位 -b：小数点前|b|+1位
select round(-1234.5) from dual; --1235
trunc(a[,b]) ：截取（取舍不取，截取整数部分）
select round(-1234.5) from dual; --1234
mod(m,n)：取m除以n后得到的余数（符号和被除数一致）
select mod(-9,2) from dual; --1
日期处理
Oracle默认日期格式：DD-MON-RR
fmt：日期的格式码： CC:世纪 YY:年 MM:月 DD:日（默认） HH24:小时 MI:分 SS:秒
sysdate ：当前系统日期(可运算)
select sysdate from dual;
months_between:间隙月
select months_between(sysdate,hiredate) from emp;
add_months:返回给定日期加上月数后的日期
select add_months('1-1月-95',3) from emp; --1995年4月1日
next_day:返回某日期的下一个指定日期
select next_day(sysdate,'星期四’) from dual; --返回下一个星期四的日期
last_day:返回指定日期对应月份的最后一天
select last_day('1-1月-95') from emp; --1995-1-31
round(date[,‘fmt’]) ：将date按fmt指定格式四舍五入，默认为DD
select round(sysdate,'mm')from dual;
trunc(date[,fmt])：date按fmt格式按指定格式截取
select trunc(hiredate,'yy')from emp;
extract（fmt from date）：返回日期类型中的年、月或日 fmt:year day month
select extract(year from hiredate) from emp;
转换函数
隐式转换（数值与字符类型）
select '1'+2 from dual; --字符转数值，需字符内容为数值含义
select 'zhang'+2 from dual; --无效
显示转换（强制转换）
to_char(date,‘fmt’)：用于日期型 date转varchar
fmt格式码： YYYY:年份 year:英文年份 MM:月份 month:英文月份 Day:周几 DY：英文周几
dd:日 hh:小时 mi:分 ss：秒
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day month')from dual; --xxxx年xx月xx日 xx:xx:xx xx xx
to_char(number,‘fmt’)：用于数值类型 number转varchar
fmt: L:当前国家货币 9：一位数字 ，(逗号)：千位符 .：小数点 0：前导零 $：美元符 格式码要超过数值长度否则出现###
select to_char(1234.5678,'L0999,999,999.99')from dual;
to_number(char)：varchar 转number，须由数字组成
select to_number('12')+4 from dual; --16
to_date(char[,fmt]):varchar 转date
select to_date('1-2月-2021','DD-MONTH-RR')from dual; 格式码fmt格式须与char格式匹配
select to_date('1-2月-2021')+2 from dual;
通用函数
null相关：
nvl(a,b)：若a为null用b替换，数据类型要一致
select nvl(comm,0) from emp;
nvl2(a,b,c)：若a不为null返回b，否则返回c，b,c类型要一致
select nvl2(comm,'有'，’无)from emp;
nullif(a,b)：如果a,b相等返回null,否则返回a，a,b数据类型要一致
select nullif(1,1) from dual; --null
select nullif(1,2) from dual; --1
coalesce(表达式1，表达式2，表达式3…表达式n)：返回第一个不为空的参数
select coalesce(null,'','zhang') from dual; --zhang
case [列名] --多条件查询
when 表达式A then 表达式B
when 表达式A1 then 表达式B1
else 表达式C
end
select ename,deptno,job,
case deptno when 10 then 'Accounting'
when 20 then 'RESEARCH'
when 30 then 'SALES'
when 40 then 'OPERATIONS'
else '无部门'
end 部门名称(别名)
from emp;
decode(字段|表达式，条件1，结果1[，条件2，结果2，…] [，缺省值]) ：多条件查询
select ename,deptno,decode(deptno,10,'Accounting',20,'RESEARCH',30,'SALES',40,'OPERATIONS','无部门') from emp;
分组函数
min\max：最小值\最大值
select min(sal),max(sal) from emp;
sum\avg：求和\求均值
select sun(sal),avg(nvl(sal,0)) from emp;
count：计数（忽略空值）
select count(*) from emp;
distinct：去重
select count(distinct deptno) from emp;
group by ：select列表中除分组函数项，所有列都要在group by中
select avg(nvl(sal,0)) from emp where deptno is not null group by deptno;
having：对分组结果进行筛选
select max(sal),deptno from emp
where deptno is not null
group by deptno
having max(sal)>2900;
嵌套子查询
单行子查询：
存在when,having,from子句
select * from emp where sal>(select sal from emp where ename='JONES');
多行子查询
多行运算符（IN ANY ALL）
select ename,sal from emp where empno in (select distinct mgr from emp);
多列子查询
通常用IN、
成对比较:
select ename,deptno,job,hiredate from emp
where (deptno,job) in (select deptno,job from emp
where extract (year from hiredate)=1981)
and extract (year from hiredate)!=1981;
不成对比较：
select ename,deptno,job,hiredate from emp
where (job in (select job from emp where extract (year from hiredate)=1981 )
or deptno in (select deptno from emp where extract (year grom hiredate)=1981)
and extract (year from hiredate)!=1981;
rownum：伪列：输出结果集时自增生成
top-n查询：
select s.* from (select * from emp order by sal desc) s where rownum<4;
分页查询：利用rownum特性
union(联合运算)：去重排序
union(完全联合运算)：不去重，不排序
intersect(相交运算)：返回共有行
minus(相减运算)： A minus B 即A减去B
(NOT) exists ：判断是否存在，返回布尔型（true/false）,找到记录不再执行，没有匹配记录返回false
①
select job,sal from emp where empno=7839
union (all)
select job,sal from emp_jobhistory where empno=7839
order by job desc;
②
select empno from emp
intersect
select empno from from emp_jobhistory;
③
select empno from emp
minus
select empno from emp_jobhistory;
相关子查询
（即高级子查询）：子查询依赖于父查询的结果
select ename,sal from emp e
where sal>(select avg(nvl(sal,0)) from emp s where e.deptno=s.deptno);
DML
数据操作语言：
insert (增)：insert into 表名 values (列值1，列值2，…) ;
delete(删)：delect 数据行；
update(改)：update 表名 set where 更新条件；
复制表结构：
create table emp_back as select * from emp where 1=0; --复制表结构
①
insert into emp_back select *from emp where deptno=10; --复制数据行
②
insert into emp_back(ename,empno,hiredate) select ename,empno,hiredate from emp where deptno=20; --复制指定数据
嵌入update：
update emp
set deptno=20,sal=sal+(select avg(nvl(sal,0)) from emp)
where deptno=10;
相关update
update emp e set dname=(select dname from dept where e.deptno=deptno)
嵌套delete
delete emp where deptno=(select deptno dept where dname='SALES');
相关delete
delete emp e where exists (select 3 from emp_jobhistory ej where e.empno=ej.empno);
1400
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
