第一天：Oracle数据库学习笔记
1. 数据库：
oracle 堵塞启动顺序： 先开启监听器，再开服务
-
三范式
1NF（第一范式） 原子性：每列不可再拆分，一个列只放一个属性 2NF（第二范式） 唯一性，主键约束，每行数据都有一个唯一标识 3NF（第三范式） 一张表不能出现其他表的非主键字段 反三范式（不遵循）
2. 数据库管理系统
软件
3. 存储结构
物理存储（文件）、逻辑存储
逻辑存储：数据库->表空间->段->区->Oracle数据块
- 用户的创建
connect 连接权限
resource创建对象权限，增删改查，普通用户不能创建用户
create user zhg identified by 123 account unlock;
grant connect , resource to zhg
4. 表的约束
- 主键约束 primary key
- 唯一约束 unique
- 非空约束 not null
- 外键约束 foreign key
- 检查约束 check
习题：
create table tb_user(
userid number(5) primary key,
username varchar2(20) check(length(username)between 4 and 20) not null,
userpwd varchar2(20) not null check(length(userpwd) between 4 and 18),
age number(3) default(18) check(age>=18),
gender char(3) default('男') check(gender in ('男','女'))，
email varchar2(30) unique,
regtime date default(sysdate)
)
create table tb_txt (
txtid number(5) primary key,
title varchar2(32) not null check(length(title)>=4 and length(title)<=24),
txt varchar(1024) ,
pubtime date default(sysdate),
userid number(5) references tb_user(userid) on delete set null -- 设置外键
)
1.添加主键
2.添加检查约束，设定性别只能为“男”或“女”
3.添加检查约束，设定年龄范围在 0~100 之间
4.添加唯一约束，设定姓名必须唯一
5. 去重distinct
select distinct ename from emp;
6. order by 升序和降序
不要再纠结了，升序：最小的在前面，然后一致增加
降序：最大的在前面然后一直降低
多字段排序：
select ename , sal,deptno from emp order by deptno, sal desc;
- 伪列的概念，就是原本数据库中不存在该表，字符串进行拼接之后改列就属于伪列
7.null处理
-- null处理，计算员工的一个月工资 , 工资=基本工资+奖金
select ename,sal,sal+nvl(comm,0) 月工资 from emp-- 处理排序为null的字段
first : null在最前面
last:null在最后面
select ename,sal,comm from emp order by comm desc nulls last select ename,sal,comm from emp order by comm desc nulls first
8.字符串的拼接
-- 字符串的拼接,使用符号 || ， 如果拼接的是null，也会进行拼接，拼接的结果是伪列 select ename ,sal|| 'zhg' from emp
9.虚表
简单的使用：计算表达式的值，表名：dual
select 999*888 from dual
10. is null, is not null , nvl()的操作
select * from emp where comm is not null
select * from emp where not comm is not null
-- nvl()当某个值为null时进行赋值操作,判断第一个参数，不为null输出本身，否则输出第二个参数 select nvl(null,100) from dual
11.模糊查询
模糊查询一定要带引号
查询有%的字符
-- 查询包含% 的员工 select * from emp where ename like '%a%%' escape('a') -- 查询包含a% 的员工,第一个a是一个标识，第二个a是本身，第三个a是特殊字符 select * from emp where ename like '%aaa%%' escape('a') -- 查询 名字有% 并且后面有_的员工 select * from emp where ename like '%a%%a_%' escape('a')
12.子查询
1.where子查询
-- where 子查询，查询销售部的所有员工信息
select * from emp where deptno = (select deptno from dept where dname='SALES')
-- 查询工资等级为2的员工信息
select * from emp where sal between (select losal from salgrade where grade =2) and (select hisal from salgrade where grade =2)
2.from子查询
-- 查询员工的工资和薪水等级
select ename ,sal ,grade from emp e, salgrade s where e.asl between s.losal and s.hisal
3.select 子查询
13.常用的单行函数
- 要思考的点，这么去使用改函数，使用改函数能达到这样的效果
1.字符函数
-
concat函数（合并）
将两个字段合并成一个字段
select concat(ename,sal)薪资 from emp -
instr函数（查找）
-- 找字串 -- 假设有一个字符串‘hellword’ 找出o的位置，如果找不到返回0 select instr('hellword','o') from dual -- 指定位置查找，序号是从1开始，如果找不到则输出0， select instr('hellworld','l',1,3) from dual -- 找l ， 从第一个数开始找，找第三次出现l的位置 -
length函数(实用性很高)
-- 查询10部门的员工，姓名长度，安排员工姓名字符长度进行升序 select ename, deptno, length(ename) from emp where deptno=10 order by length(ename) -
大小写转换 lower、upper
-- 查询20部门员工的姓名和工资，将姓名全部小写 select ename,deptno , sal,lower(ename) lowerName from emp where deptno=20 -
去除字符串两边的空格 ，trim
-- 去除左右两边的字符串 select ltrim (' aaa bbb ') from dual select rtrim (' aaa bbb ') from dual -- 去除左右两边的字符串，并且拼接字符串 select ltrim (rtrim(' aaa bbb '))||'A' from dual select concat(ltrim (rtrim(' aaa bbb ')),'A') 去空格 from dual -- 去除两边空格 select trim (' aaa bbb ') from dual -
替换replace
-- repalce 会替换全部符合要求的字符 select replace('asifjasf','a','-') from dual -
截取substr
select substr('asjfdof',1,4) from dual;
2.数学函数
-- 绝对值 abs()
select abs(-1) from dual
-- 向上取整ceil()
select ceil(1.2) from dual
-- 向下取整floor()
select floor(1.2) from dual
-- 取余:5%3 = 2 mod()
select mod(5,3) from dual
-- x的y次幂 125,power()函数
select power(5,3) from dual
-- 平方根,就是开根号的意思 sqrt()
select sqrt(2) from dual
3.日期函数
- sysdate、current_date 系统当前时间
习题1 ：查询所有员工转正日期(add_moths()函数的使用)
转正日期=入职日期+ 试用期三个月
select substr('asjfdof',1,4) from dual;
习题2：查看当前月的最后一天:last_day()函数
-- 查询当前月的最后一天
select last_day(sysdate) from dual
习题三：入职多少个月 months_between() 函数
-- 月份差，入职多少个月了
select sysdate , hiredate,months_between (sysdate,hiredate) from emp
4.转换函数
-- 转换函数
-- to_char (date,charFormat) 日期->字符,如果转换格式中有中文需要用双引号包裹起来
select to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp
-- to_date 字符->日期
select to_date('1900-12-12','yyyy/mm/dd')+1 from dual
5.组函数
-- 组函数同时对多条纪律进行操作，并返回一个结果
-- 常见的组函数
avg() -- 平均值
sum() -- 求和
min() -- 最小值
max() -- 最大值
count() -- 总条数
14.简单插入与多条数据插入
-- 单条数据
insert into 表名 values(字段名，...)
insert into 表名 values(全部属性)
-- 多条数据使用union,多条数据拼接union即可
insert into infos
select 's003'，'小李','男',22,to_date('2022-1-1','yyyy-mm-dd'),null,'10' from dual
union
select 's004' ,'小王','女',28,null,null,'10' from dual
15.得到系统时间
select sysdate from dual
select sysdate, to_char(sysdate,'day yyyy-mm-dd')
select sysdate , to_char(sysdate,'day yyyy-mm-dd hh24:mi:ss') 可以设置24小时或者12小时制
16.case的使用（if 或 switch）
-- if
select empno , ename,sal ,job,case
when sal between 0 and 1000 then ' 萧贼派'
when sal between 1001 and 2000 then '划水派'
when sal between 2001 and 3000 then '摸鱼派'
else '打工的' end
from emp
-- switch
select ename,job ,case job
when 'SALESMAN' then '社会底层'
when 'CLERK' then '下水道老鼠'
when 'PRESIDENT' then '大boos'
else '摸鱼的'end 职位别称
from emp
17.序列 -- 索引的创建
-- 创建序列
create sequence s1
-- 查询序列
select s1.nextval from dual -- 查看序列并+1
select s1.currval from dual -- 查看当前序列
-- 创建一个初始值为1000，每次点击或者查看序列进行加10
create sequence s2
start with 1000
increament 10
-- 索引创建
create index inx_name zjj_song(name) -- 将zjj_song表中的name字段为索引
18.伪列
oracle中的伪列有rowid，相当于 打开了悲观锁 for update ,用的少
rownum使用频率多，用于分页
-- rowid 物理地址，添加数据时就确定好了 -- 打开悲观锁的两种方式 rowid 、 for update
-
习题1
-- 小于可以，大于不行 -- 查询最高工资的前三人 select * from ( select e.* from emp e where sal is not null order by sal desc ) where rownum<=3 -
习题2
-- 查询员工工资大于本部门的平均工资的员工 select * from emp e inner join ( select deptno ,avg(sal) pj from emp group by deptno ) p on e.deptno = p.deptno where sal > pj -
习题3
-- 查询员工已经对应的领导编号 select e1.empno 员工编号, e1.ename 员工姓名,e2.ename 领导编号 from emp e1,emp e2 where e2.empno=e1.mgr
