Oracle简单应用
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
记得快乐
