初步学习Oracle数据库

初步学习Oracle数据库

数据库的最初目的：共享数据
行：数据
列：字段属性

四种基本语言

DDL ： 数据定义语言
create drop truncate rename alter
主要用来建表，对字段进行操作
DML： 数据操纵语言
Insert update delete select （DQL）
主要用来对数据的增删查改
DCL: 数据控制语言
（不介绍）
TCL： 事务控制语言
commit savepoint rollback
由于Oracle不是自动提交的，所以只有commit之前不会把数据提交到物理空间之中

基本数据类型

number（数的长度）整数
Number（4,2）99.99最大
Char（2） 定长为2
Varchar（） 不用
Varchar2（20）最大长度20
Date 时间 -年月日
Timestamp 时间戳 - 时分秒

DDL的使用

建表：
create table user(
id number（6）,
name varchar2（20）,
className varchar2（20）,
score number(5,2)
);
删除表：注意是是删除表这个文件
drop table user；
查看表结构
desc user；
增加字段
alter table user add（
birthday date
）；
删除字段：
alter table user drop（score）；
修改字段属性
alter table user_cxw modify(
className varchar(10) default ‘java1906’–设置默认值
);
修改表名：
rename user to user_cxk;
删除表中所有数据
truncate table user;

DML数据操纵语言：

插入数据：
insert into user values
(1,‘rose’,‘java1903’,92,sysdate);
参数的顺序，属性，个数要对的上
或者
insert into user_cxw （name,score,birthday) values
(‘moren’,92,sysdate);
添加部分数据，没添加的字段则会给出默认值

删除数据：
delete from user_cxw where id = 1;
修改数据
update user_cxw set classname = ‘java1907’，
score = 100;
查询数据（DQL）
select * from user;
DQL–数据查询语言包含在DML之中，详细的后面讲

TCL–事务控制语言

提交数据
commit
将数据保存到物理空间之中，用了commit之后数据无法撤回，由于Oracle的数据不是自动提交的，要手动提交，提交之前可以在本服务器中看到更改，但在别人的数据访问中看不到更改
开启节点
savepoint a
生成节点a便于回滚
回滚
rollback a
将数据回滚到节点a，如果没有a则回滚到commit之后。

组函数

Oracle自带的用于处理数据的函数
主要有
max min
avg sum
count
但要注意的是组函数通常会忽略null值

子句顺序以及作用

子句的顺序
select from where group by having order by

select
查找需要的数据，用于DQL之中
from
判断从哪个表中查找数据
where
筛选表数据
group by
规定数据的分组，如果有组函数则必须包含所有的非组函数
having
作用类似于where但是专门为组函数服务
order by
规定排序的依据与顺序
desc是降序 asc是默认排序 升序

DQL – 数据查询语言

DQL属于DML

基本查询就不列举了
like（模糊查询）
SELECT ename, job FROM emp WHERE ename LIKE ‘_A%’; --查询开头第二个字符为A的员工名字
如果是‘%A’则是结尾为A的
‘%A%’，包含A的员工

关联查询
如果需要同时用到两个表以上就用关联查询
关联查询有三种连接方式
1.等值连接
SELECT e.ename, e.job, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;
注意连接的值必须是两者的共同值
2.内连接
SELECT e.ename, d.dname
FROM emp e JOIN dept d
ON(e.deptno = d.deptno);
作用等同于等值连接
3.外连接
外连接又分为左连接，右连接，全连接
本质是选择哪一张表为驱动表
驱动表：就算有null值也会全部显示出来
SELECT table1.column, table2.column
FROM table1 [LEFT | RIGHT | FULL] JOIN table2
ON table1.column1 = table2.column2;

子查询
在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而 是来自于另外一个查询的结果。
根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查 询。

‐‐例如查找和SCOTT同职位的员工：
SELECT e.ename, e.job
FROM emp e
WHERE e.job =
(SELECT job FROM emp WHERE ename = ‘SCOTT’)；

子查询可以出现在from select having where字段后面

–子查询小结：
–如果是单记录-一行一列，where直接比较
–一列，多行集合 集合 any in all
–多行多列 - 作为一张临时表使用

分页查询
Oracle的分页查询比较复杂，而mysql的分页查询则有专门的函数，比较简单

查询前4条
select sal,ename,rownum
from emp
where rownum<=4;

查询5-8
select
sal,ename,e.n
from
(select sal,ename,rownum n from emp)e
where
e.n between 5 and 8;

查询排序后的第四条

select ename,sal,rownum n
from (
select ename,sal
from emp
order by sal desc)
where rownum <= 5;

查询工资由高到低 5-8名
select sal,ename,e.n
from(
select sal,ename,rownum n
from(
select sal,ename
from emp
order by sal desc
)
)e
where e.n between 5 and 8;

Decode函数

用来实现if-else功能

select ename,job,decode(job,
‘ANALYST’,sal1.2,
‘CLERK’,sal,
‘MANAGER’,sal2,
sal*4)–没有匹配到的yearcomm
from emp;

视图，序列，索引

视图

视图是基于select生成的一张虚拟的表，主要用来简化复杂查询，同时对其他的不在select中的数据起到保护作用，视图主要用来查询，因为视图和基表共用空间，所以最好不要修改视图
只有简单视图才能用insert插入数据

创建视图
创建一个简单视图V_EMP_10，来显示部门10中的员工的编码、姓名和薪水：
CREATE VIEW v_emp_10
AS
SELECT empno, ename, sal, deptno
FROM emp
WHERE deptno = 10;

序列

序列(SEQUENCE)是一种用来生成唯一数字值的数据库对象。序列的值由 Oracle程序按递增或递减顺序自动生成，通常用来自动产生表的主键值，是一种 高效率获得唯一键值的途径。

create sequence user_seq–默认从1开始,每次+1
start with 10
maxvalue 100;

使用序列
insert into user_cxw (id) values(
user_cxw_seq.nextVal);
currVal是获得当前序列

索引

1、索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效 率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE） 中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的 I/O操作比对表进行操作要少很多。
2、索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个 索引，是一种提高查询效率的机制。

创建索引
CREATE INDEX idx_emp_ename ON emp(ename);

为提升查询效率，创建和使用索引的原则：
1、为经常出现在WHERE子句中的列创建索引
2、为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复 合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
3、为经常作为表的连接条件的列上创建索引
4、不要在经常做DML操作的表上建立索引
5、不要在小表上建立索引
6、限制表上的索引数目，索引并不是越多越好
7、删除很少被使用的、不合理的索引

约束

约束的类型
1、非空约束(Not Null)，简称NN
2、唯一性约束(Unique)，简称UK
3、主键约束(Primary Key)，简称PK
4、外键约束(Foreign Key)，简称FK
5、检查约束(Check)，简称CK

主键(Primary Key)约束条件从功能上看相当于非空（NOT NULL）且唯一 （UNIQUE）的组合。
一个表只能有一个主键
主键选取的原则
1、主键应是对系统无意义的数据
2、永远也不要更新主键，让主键除了唯一标识一行之外，再无其他的用途
3、主键不应包含动态变化的数据，如时间戳
4、主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意 义
5、主键尽量建立在单列上

加入主键：
ALTER TABLE employees3
ADD CONSTRAINT
employees3_eid_pk PRIMARY KEY (eid);

外键约束
外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两 个字段的关系。

添加外键
ALTER TABLE employees4
ADD CONSTRAINT employees4_deptno_fk
FOREIGN KEY (deptno) REFERENCES dept(deptno);

外键约束对性能的降低
1、如果在一个频繁DML操作的表上建立外键，每次DML操作，都将导致数据库 自动对外键所关联的对应表做检查，产生开销，如果已在程序中控制逻辑，这些 判断将增加额外负担，可以省去。
2、另外外键确定了主从表的先后生成关系，有时会影响业务逻辑。