Oracle简单应用

Oracle简单应用

1、体系结构

2、表空间

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

2.1、创建表空间

create tablespace gec_name_space 
datafile 'E:oracle_tablespacegec.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