时间戳

首页 > 技术文章 > Oracle简单应用

Oracle简单应用

Y-wee 2020-10-29 20:02 原文

Oracle简单应用

1、体系结构

2、表空间

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

2.1、创建表空间

create tablespace gec_name_space 
datafile 'E:\oracle_tablespace\gec.dbf' 
size 100m 
autoextend on 
next 10m
  • gec_name_space为表空间名称
  • datafile 指定表空间对应的数据文件
  • size 后定义的是表空间的初始大小
  • autoextend on 自动增长 ，当表空间存储都占满时，自动增长
  • next 后指定的是一次自动增长的大小

3、用户

3.1、创建用户

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

3.2、用户赋予权限

新创建的用户没有任何权限，登陆后会提示

Oracle 中已存在三个重要的角色

  • connect：是授予最终用户的典型权利，最基本的
  • resource：是授予开发人员的
  • dba：拥有全部特权，是系统最高权限，只有 DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且 DBA用户可以操作全体用户的任意基表，包括删除
grant connect to gecuser

4、数据类型

5、表

5.1、创建表

create table person( pid number(10), name varchar2(10), gender number(1) default 1, birthday date );

5.2、插入表数据

insert into person(pid, name, gender, birthday) values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));

5.3、删除表

DROP TABLE 表名

5.4、修改表

在 sql中使用 alter 可以修改表

  • 添加列

    ALTER TABLE 表名称 ADD(
    列名 1 类型 [DEFAULT默认值]，
    列名 2 类型[DEFAULT 默认值]...)
alter table person add(address varchar2(10));

  • 修改列类型

    ALTER TABLE 表名称 MODIFY(
    列名 1 类型[DEFAULT 默认值]，
    列名 2 类型[DEFAULT 默认值]...)
alter table person modify(address varchar2(20));

  • 修改列名

     ALTER TABLE 表名称 RENAME 列名 1 TO 列名 2

6、序列

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

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

    CREATE SEQUENCE seqpersonid; 
nextval :取得序列的下一个内容 
currval :取得序列的当前内容 
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual; 
insert into person (pid, pname) values (s_person.nextval, '小明');

7、单行函数

单行函数：作用于一行，返回一个值。

7.1、字符函数

接收字符输入返回字符或者数值

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

  • 把大写字符变成小写字符

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

7.2、数值函数

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

    select round(56.16, -2) from dual;---四舍五入，后面的参数表示保留的位数 
select trunc(56.16, -1) from dual;---直接截取，不在看后面位数的数字是否大于5.

7.3、日期函数

----查询出emp表中所有员工入职距离现在几天。 
select sysdate-e.hiredate from emp e; --sysdate：当前日期
----算出明天此刻 
select sysdate+1 from dual; 
----查询出emp表中所有员工入职距离现在几月。 
select months_between(sysdate,e.hiredate) from emp e;
----查询出emp表中所有员工入职距离现在几年。 
select months_between(sysdate,e.hiredate)/12 from emp e; 
----查询出emp表中所有员工入职距离现在几周。 
select round((sysdate-e.hiredate)/7) from emp e;

8、转换函数

---条件表达式 
---条件表达式的通用写法，mysql和oracle通用 
---给emp表中员工起中文名 
select e.ename, 
	case e.ename 
		when 'SMITH' then '曹贼' 
		when 'ALLEN' then '大耳贼' 
		when 'WARD' then '诸葛小儿' 
		else '无名' 
			end 
from emp e;

9、通用函数

9.1、空值处理nvl

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

9.2、Decode函数

----oracle专用条件表达式 
select e.ename, 
	decode(e.ename,
           'SMITH', '曹贼', 
           'ALLEN', '大耳贼',
           'WARD', '诸葛小儿',
           '无名') "中文名" 
from emp e;

10、多行函数（聚合函数）

10.1、统计记录总数count（）

select count(1) from emp;---查询总数量

10.2、最小值查询min（）

select min(sal) from emp;---最低工资

10.3、最大值查询 max（）

select max(sal) from emp;---最大工资

10.4、查询平均值avg（）

select avg(sal) from emp;---平均工资

10.5、求和函数sum（）

select sum(sal) from emp;---工资总和

11、分组查询

---查询出每个部门的平均工资 
---分组查询中，出现在group by后面的原始列，才能出现在select后面 
---没有出现在group by后面的列，想在select后面，必须加上聚合函数。
---聚合函数有一个特性，可以把多行记录变成一个值。 
select e.deptno, avg(e.sal), e.ename from emp e group by e.deptno; 
---查询出平均工资高于2000的部门信息 
select e.deptno, avg(e.sal) asal from emp e group by e.deptno having avg(e.sal)>2000; 
----where是过滤分组前的数据，having是过滤分组后的数据。 
---表现形式：where必须在group by之前，having是在group by之后。 
---查询出每个部门工资高于800的员工的平均工资
---然后再查询出平均工资高于2000的部门 
select e.deptno, avg(e.sal) asal from emp e where e.sal>800 group by e.deptno having avg(e.sal)>2000;

12、多表查询

语法： SELECT {DISTINCT} *|列名.. FROM 表名 别名，表名 1 别名{WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...}

范例：查询员工表和部门表

select * from emp e,dept d where e.deptno=d.deptno

12.1、外连接（左右连接）

使用(+)表示左连接或者右连接，当(+)在左边表的关联条件字段上时是右连接，如果是在右边表的关联条件字段上就是左连接

select e.empno,e.ename,d.deptno,d.dname
from empe,dept d 
where e.deptno(+) =d.deptno

参考资料：https://www.cnblogs.com/hehaiyang/p/4745897.html

12.2、子查询

select * from emp t1 
where t1.sal>(
    select t.sal from emp t where t.empno=7654)

13、分页查询

ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现

----emp表工资倒叙排列后，每页五条记录，查询第二页。
----rownum行号不能写大于一的正数。 
select * from( 
    select rownum rn, tt.* from( 
        select * from emp order by sal desc ) tt where rownum<11
    ) where rn>5

参考资料：https://www.cnblogs.com/wuxu/p/11198764.html

推荐阅读