Oracle数据库学习 day01

最新推荐文章于 2024-06-13 10:04:21 发布

weixin_46539792

最新推荐文章于 2024-06-13 10:04:21 发布

阅读量155

点赞数

分类专栏： Oracle学习文章标签： oracle

本文链接：https://blog.csdn.net/weixin_46539792/article/details/105498683

版权

Oracle学习专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1. 创建表空间

表空间： ORACLE数据库的逻辑单元。数据库—表空间一个表空间可以与多个数据文件（物理结构）关联
一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。

-- 创建表空间
create tablespace tom
datafile 'c:\tom.dbf'
size 100m
autoextend on
next 10m;

--删除表空间
drop tablespace tom;

itcast ：为表空间名称
datafile： 指定表空间对应的数据文件
size： 后定义的是表空间的初始大小
autoextend on ： 自动增长，当表空间存储都占满时，自动增长
next： 后指定的是一次自动增长的大小。

2. 用户

2.1 创建用户

-- 创建用户
create user tom
identified by tom
default tablespace tom;

identified by 后边是用户的密码
default tablespace 后边是表空间名称
oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户
下的。

2.2 用户赋权限

--给用户授权
--oracle数据库中常用角色
connect--连接角色，基本劫色
resource--开发者角色
dba--超级管理员角色
--给tom用户授予dba角色
grant dba to tom;

3. Oracle数据类型

No	数据类型	描述
1	varchar, varchar2	表示一个字符串
2	NUMBER	NUMBER(n)表示一个整数，长度是n ，NUMBER(m,n):表示一个小数，总长度是 m，小数是 n，整数是 m-n
3	DATA	表示日期类型
4	CLOB	大对象，表示大文本数据类型，可存 4G
5	BLOB	大对象，表示二进制数据，可存 4G

4. 表的管理

4.1 建表

Create table 表名（
字段 1 数据类型 [default 默认值],
字段 2 数据类型 [default 默认值],
…
字段 n 数据类型 [default 默认值]
）;
范例：创建 person表

--创建一张表
create table person(
       pid number(20),
       pname varchar2(10)
);

4.2 表删除

--三个删除
--删除表中全部记录
delete from person;
--删除表结构
drop table person;
--先删除表，再次创建表。效果等同于删除表中全部记录。
--在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。
--索引可以提供查询效率，但是会影响增删改效率
truncate table person;

4.3 表的修改

在 sql中使用 alter 可以修改表
添加语法：ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]…)
修改语法：ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]…)
修改列名: ALTER TABLE 表名称 RENAME 列名 1 TO 列名 2

--修改表结构
--添加一列
alter table person add (gender number(1)); 
--修改列数据类型
alter table person modify gender char(1);
--修改列名
alter table person rename column gender to sex;
--删除列
alter table person drop column sex;

4.4 数据库表数据的更新

1. INSERT（增加）

--增加一条数据
insert into person(pid,pname) values(2,'lisi');
commit;

2.UPDATE（修改）

--修改数据
update person set pname = 'wangwu' where pid = 1;
commit;

3. DELETE（删除）

delete from person where name = 'wangwu';
commit;

4.5 序列

在很多数据库中都存在一个自动增长的列,如果现在要想在 oracle 中完成自动增长的功能, 则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。

范例:创建一个 seqpersonid的序列,验证自动增长的操作

--序列不真的属于任何一张表，但是可以逻辑和表做绑定。
--序列：默认从1开始，依次递增，主要用来给主键赋值使用
--dual：虚表，只是为了补全语法，没有任何意义。
create sequence s_person;
select s_person.currval from dual;
-- 添加一条记录
insert into person(pid,pname) values(s_person.nextval,'zhaoliu');
commit;

序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：

nextval ：取得序列的下一个内容
currval ：取得序列的当前内容

在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。
序列的管理一般使用工具来管理。

5. Scott用户

--scott用户，密码tiger
--解锁scott用户
alter user scott account unlock;
--解锁scott用户的密码【此句也可以用来重置密码】
alter user scott identified by tiger;

6. 单行函数

6.1. 字符函数

接收字符输入返回字符或者数值，dual 是伪表

把小写的字符转换成大小的字符

--字符函数
select upper('yes') from dual; --YES

把大写字符变成小写字符

select lower('YES') from dual; --yes

6.2 数值函数

四舍五入函数：ROUND() 默认情况下 ROUND 四舍五入取整，可以自己指定保留的位数。
数值截取函数
取余函数


select round(26.17,-2) from dual; --四舍五入，后面的参数表示保留的位数

select trunc(26.17,1) from dual;--截位值取

select mod(10,3) from dual;--取余

6.3 日期函数

--日期函数
--查询emp表员工入职到现在多少天
select sysdate - e.hiredate from emp e;
--算民天的此刻
select sysdate + 1 from dual;
--查询emp表员工入职到现在多少周
select round((sysdate - e.hiredate)/7) from emp e;
--查询emp表员工入职到现在多少个月
select round(months_between(sysdate , e.hiredate)) from emp e;
--查询emp表员工入职到现在多少年
select round(months_between(sysdate,e.hiredate)/12) from emp e;

6.4 转换函数

--转换函数
--日期转字符串
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;
--字符串转日期
select to_date('2020-1-1 13:20:20','fm yyyy-mm-dd hh24:mi:ss') from dual;

6.5 通用函数

1.空值处理 nvl

--查询emp表所有员工的年薪
--奖金里面有null值，如果null值和任意数字做算术运算，结果都是null
select e.sal*12 + nvl(e.comm,0) from emp e;

2.Decode 函数

--orcale中除了起别名，都用单引号
--orcale专用
select e.ename,
       decode(e.ename,
       'SMITH', '张三',
           'ALLEN',  '李四',
            'xx')
from emp e;

3.case when

--条件表达式
--条件表达式的通用写法，mysql和oracle通用
--给emp表中员工起中文名
select e.ename,
       case e.ename
         when 'SMITH' then '张三'
           when 'ALLEN' then '李四'
             when 'WARD' then '王五'
               --else 'xx' else可以省略
                 end
 from emp e; 
 -- 判断emp表员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入
 --其余显示低收入
 select e.sal,
        case
          when e.sal > 3000 then '高收入'
            when e.sal >1500 then '中等收入'
              else '低收入'
                end "中文名"
from emp e;
--orcale中除了起别名，都用单引号

7. 多行函数（聚合函数）

select count(1) from emp;	--查询员工总数
select sum(emp.sal) from emp;	--查询工资总额
select max(emp.sal) from emp;	--查询最高工资
select min(emp.sal) from emp;	--查询最少工资
select avg(emp.sal) from emp;	--查询平均工资

不建议使用 count(*)，可以使用一个具体的列以免影响性能。

8. 分组统计

分组统计需要使用 GROUP BY来分组
语法：SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列名 1 ASC|DESC，列名 2…ASC|DESC

--分组查询
--查询每个部门的人数
select e.deptno,count(e.ename) from emp e group by e.deptno;
--查询出每个部门的平均工资 
--分组查询中，出现在group by 后面的原始列，才能出现在select后面
--没有出现在group by后面的列，想在select后面，必须加上聚合函数
--聚合函数有一个特性，可以把多行记录变成一个值
select e.deptno, avg(e.sal) from emp e group by e.deptno;
--按部门分组，查询出部门名称和部门的员工数量 
select e.ename from emp e group by e.deptno;
--查询平均工资高于2000的部门信息
select e.deptno,avg(e.sal) from emp e group by e.deptno having avg(e.sal) > 2000;
--所有条件都不能使用别名来判断
--比如下面的条件语句也不能使用别名当条件
select ename, sal s from emp where sal>1500;
--查询出每个部门工资高于800的员工的平均工资
select e.deptno , avg(e.sal) from emp e where e.sal > 800 group by e.deptno;
--where是过滤分组前的数据，having是过滤分组后的数据
--表现形式：where必须在group by 之前， having 是在 group by 之后
--查询出每个部门工资高于800的员工的平均工资
--然后再查询出平均工资高于2000的部门
select e.deptno, avg(e.sal) from emp e
where e.sal > 800
group by e.deptno
having avg(e.sal) > 2000;

9. 多表查询

9.1 多表连接基本查询

使用一张以上的表做查询就是多表查询
语法： SELECT {DISTINCT} *|列名… FROM 表名别名，表名 1 别名 {WHERE 限制条件 ORDER BY 排序字段 ASC|DESC…}

--多表查询中的一些概念
--迪卡尔积
select * from emp,dept;
--等值连接
select * from emp,dept where emp.deptno = dept.deptno;
--内连接
select * from emp inner join dept on emp.deptno = dept.deptno;

9.2 自连接

--自连接：子链接其实就是站在不同的角度把一张表看成多张表
--查询出员工姓名，员工领导姓名
select e1.ename, e2.ename
from emp e1 , emp e2
where e1.mgr = e2.empno;
--查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
--自连接：自连接其实就是站在不同的角度把一张表看成多张表
select e1.ename,d1.dname, e2.ename , d2.dname
from emp e1, emp e2 , dept d1 , dept d2
where e1.mgr = e2.empno
and e1.deptno = d1.deptno
and e2.deptno = d2.deptno;
--查询出每个员工编号，姓名，部门名称，工资等级
--和他的上级领导的姓名，工资等级 
select e1.empno, e1.ename, d1.dname, s1.grade, e2.ename, s2.grade
from emp e1, emp e2, dept d1, salgrade s1, salgrade s2
where e1.mgr = e2.empno
and e1.deptno = d1.deptno
and e1.sal between s1.losal and s1.hisal
and e2.sal between s2.losal and s2.hisal;

9.3 外连接（左右连接）

--查询出所有部门，以及部门下的员工信息。【外连接】
select * 
from emp right join dept 
on emp.deptno = dept.deptno;
--查询所有员工信息，以及员工所属部门
select * 
from emp left join dept
on emp.deptno = dept.deptno;
--oracle中专用外连接
select * 
from emp e, dept d 
where e.deptno(+) = d.deptno; -- （+）表示显示右边全部

10. 子查询

子查询：在一个查询的内部还包括另一个查询，则此查询称为子查询。
Sql的任何位置都可以加入子查询。

--子查询
--子查询返回一个值
--查询出工资和SCOTT一样的员工信息
select sal from emp where ename = 'SCOTT';
select * 
from emp 
where sal in (select sal from emp where ename = 'SCOTT');
--子查询返回一个集合
--查询出工资和10号部门任意员工一样的员工信息
select sal from emp where deptno = 10;
select * 
from emp
where sal in (select sal from emp where deptno = 10); 
--子查询返回一张表
--查询出每个部门最低工资和最低工资员工姓名，和该员工所在部门名称
--1.查询出每个部门最低工资
select deptno, min(sal)
from emp
group by deptno;
--2.三表联查，得到最终结果
select t.deptno,t.msal, e.sal, e.ename, d.dname
from (select deptno, min(sal) msal
      from emp
      group by deptno
      ) t, emp e, dept d 
where t.msal = e.sal
and e.deptno = d.deptno;

11. Rownum与分页查询

**ROWNUM：**表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
我们可以根据 rownum 来取结果集的前几行，比如前 5 行
但是我们不能取到中间几行，因为rownum不支持大于号，只支持小于号，如果想实现我们的需求

-- 分页查询
select * from (
       select rownum rn , tt.* from(
              select * from emp order by sal desc
       ) tt where rownum < 11
) where rn > 5;

weixin_46539792

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle数据库学习 day01

Oracle学习 day01 1. 创建表空间2. 用户2.1 创建用户2.2 用户赋权限3. Oracle数据类型4. 表的管理4.1 建表4.2 表删除4.3 表的修改4.4 数据库表数据的更新4.5 序列5. Scott用户6. 单行函数6.1. 字符函数6.2 数值函数6.3 日期函数6.4 转换函数6.5 通用函数7. 多行函数（聚合函数）8. 分组统计9. 多表查询9.1 多表连接基本...
复制链接

扫一扫