一、 oracle基本的sql语言
a) DDL（Data Definition Language，数据定义语言）用于定义数据的结构，如创建，修改或者删除数据库对象。
1） 创建
A） 创建表
| CREATE TABLE <table_name>( column1 DATATYPE [NOT NULL] [PRIMARY KEY], column2 DATATYPE [NOT NULL], ... [constraint <约束名> 约束类型 (要约束的字段) ... ] ） 说明： DATATYPE --是Oracle的数据类型,可以查看附录。 NUT NULL --可不可以允许资料有空的（尚未有资料填入）。 PRIMARY KEY --是本表的主键。 constraint --是对表里的字段添加约束.(约束类型有 Check,Unique,Primary key,not null,Foreign key)。 示例: create table stu( s_id number(8) PRIMARY KEY, s_name varchar2(20) not null, s_sex varchar2(8), clsid number(8), constraint u_1 unique(s_name), constraint c_1 check (s_sex in ('MALE','FEMALE')) ); |
复制表
| CREATE TABLE <table_name> as <SELECT 语句> (需注意的是复制表不能复制表的约束); 示例: create table test as select * from emp; 如果只复制表的结构不复制表的数据则: create table test as select * from emp where 1=2; |
B） 创建索引
| Create [unique] index <index_name> on <table_name> (字段 [asc|desc]); Unique----确保所有的索引列中的值都是可以区分的。 [asc|desc] ------在列指定排序创建索引。 (创建索引的准则： 1.如果表里有几百行记录则可以对其创建索引(表里的记录行数越多索引的效果就越明显)。 2.不要试图对表创建两个或三个以上的索引。 3.为频繁使用的行创建索引。 ) 实例： Create index i_1 on emp(empno asc); |
C） 创建同义词
同义词
Dual是一张虚拟的表，该表是在哪里定的？ 测试该表是否属于system；
在sys用户存在该表。但是在scott用户下可以通过表名称直接访问此表。正常情况下，如果要访问不同用户下的表，必须使用："用户名.表名称"。 此时，实际上就是同义词的作用。同义词，可以让其他用户通过一个名称方便的访问"用户名.表名称"。 必须在管理员用户下创建 创建同义词：
删除同义词： DROP SYNONYM userinfo; |
2） 修改（修改表）
1、 向表中添加字段
Alter table <table_name> add (字段1 类型 [not null], 字段2 类型 [not null] ……….);
2.修改表中字段
ALTER TABLE <table_name> modify(字段1 类型,
字段2 类型
.... );
3 .删除表中字段
ALTER TABLE <table_name> drop(字段1,
字段2
.... );
4 .修改表的名称
RENAME <table_name> to <new table_name>;
5 .对已经存在的表添加约束
添加内容（详解约束）
| 目的是为了保证表的完整性 1、主键约束:PRIMARY KEY 2、唯一约束:UNIQUE 3、检查约束:CHECK 4、非空约束:NOT NULL 5、外键约束: 指定约束名称： CONSTRAINT persion_pid_pk PRIMARY KEY(pid) CONSTRAINT persion_pname_uk UNIQUE(pname) CONSTRAINT persion_page_ck CHECK(age BETWEEN 0 AND 150) CONSTEAINT persion_book_pid_fk FOREIGN KEY(pid) REFERENCES persion(pid) 级联删除： DROP TABLE table_name CASCADE CONSTRAINT; 删除主表中数据的时候同时删除字表对应的数据： CONSTEAINT persion_book_pid_fk FOREING KEY(pid) REFERENCES persion(pid) ON DELETE CASCADE 增加约束：
例： ALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid); 删除约束
|
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> 约束类型 (针对的字段名);
示例：
Alter table emp add constraint S_F Foreign key (deptno) references dept(deptno);
6 .对表里的约束禁用;
ALTER TABLE <table_name> DISABLE CONSTRAINT <constraint_name>;
7 .对表里的约束重新启用;
ALTER TABLE <table_name> ENABLE CONSTRAINT <constraint_name>;
8 .删除表中约束
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
示例:
ALTER TABLE emp drop CONSTRAINT <Primary key>;
3） 删除
A) 删除表
DROP TABLE <table_name>;
示例
drop table emp;
B) 删除索引
DROP INDEX <index_name>;
示例
drop index i_1;
C) 删除同义词
DROP SYNONYM <synonym_name>;
示例
drop synonym mm;
b) DML（Data Manipulation Language，数据操作语言）用于检索或者修改数据
1、 插入记录
INSERT INTO table_name (column1,column2,...)
values ( value1,value2, ...);
示例
insert into emp (empno,ename) values(9500,'AA');
把 一个表中的数据插入另一个表中
INSERT INTO <table_name> <SELECT 语句>
示例
create table a as select * from emp where 1=2;
insert into a select * from emp where sal>2000;
2、 查询记录
一般查询
SELECT [DISTINCT] <column1 [as new name] ,columns2,...>
FROM <table1>
[WHERE <条件>]
[GROUP BY <column_list>]
[HAVING <条件>]
[ORDER BY <column_list> [ASC|DESC]]
DISTINCT --表示隐藏重复的行
WHERE --按照一定的条件查找记录
GROUP BY --分组查找(需要汇总时使用)
HAVING --分组的条件
ORDER BY --对查询结果排序
要显示全部的列可以用*表示
示例：
select * from emp;
WHERE 语句的运算符
where <条件１>AND<条件２> --两个条件都满足
示例：
select * from emp where deptno=10 and sal>1000;
where <条件１>OR<条件２> --两个条件中有一个满足即可
示例：
select * from emp where deptno=10 OR sal>2000;
where NOT <条件> --不满足条件的
示例：
select * from emp where not deptno=10;
where IN(条件列表) --所有满足在条件列表中的记录
示例：
select * from emp where empno in(7788,7369,7499);
where BETWEEN .. AND .. --按范围查找
示例：
select * from emp where sal between 1000 and 3000;
where 字段 LIKE --主要用与字符类型的字段
示例1：
select * from emp where ename like '_C%'; --查询姓名中第二个字母是'C'的人
'-' 表示任意字符;
'%' 表示多字符的序列；
where 字段 IS [NOT] NULL --查找该字段是[不是]空的记录
汇总数据是用的函数
SUM --求和
示例：
select deptno,sum(sal) as sumsal from emp GROUP BY deptno;
AVG --求平均值
MAX --求最大值
MIN --求最小值
COUNT --求个数
子查询
SELECT <字段列表> from <table_name> where 字段运算符(<SELECT 语句>);
示例：
select * from emp where sal=(select max(sal) from emp);
运算符
Any
示例：
select * from emp where sal>ANY(select sal from emp where deptno=30) and deptno<>30;
--找出比deptno=30的员工最低工资高的其他部门的员工
ALL
select * from emp where sal>ALL(select sal from emp where deptno=30) and deptno<>30;
--找出比deptno=30的员工最高工资高的其他部门的员工
连接查询
SELECT <字段列表> from <table1,table2> WHERE table1.字段[(+)]=table2.字段[(+)]
示例
select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;
查询指定行数的数据
SELECT <字段列表> from <table_name> WHERE ROWNUM<行数;
示例:
select * from emp where rownum<=10;--查询前10行记录
注意ROWNUM只能为1 因此不能写 select * from emp where rownum between 20 and 30;
要查第几行的数据可以使用以下方法:
select * from emp where rownum<=3 and empno not in (select empno from emp where rownum<=3);
结果可以返回整个数据的3-6行;
把工资等级用如下方式表示：
1：第一等工资
2：第二等工资
3：第三等工资
4：第四等工资
5：第五等工资
| select e.ename 雇员姓名,e.sal 工资,d.dname 部门名称,decode(s.grade,1,'第一等级',2,'第二等级',3,'第三等级',4,'第四等级') 员工工资等级, m.ename 直接上机领导,m.sal 领导工资,ms.grade 领导工资等级 from emp e,emp m,dept d,salgrade s,salgrade ms where e.mgr=m.empno and e.deptno=d.deptno and e.sal between s.losal and s.hisal and m.sal between ms.losal and ms.hisal; |
补充内容
| 1、ROWNUM(行号) SELECT ROWNUM,empno,ename,job FROM emp; ROWNUM采用自动编号的形式。 显示查询的前5条件记录 SELECT ROWNUM,empno,ename,job FROM emp WHERE ROWNUM <= 5; 如果想查询中间部分的内容，则必须使用子查询，不能使用BETWEEN AND
|
3、 更新记录
UPDATE table_name set column1=new value,column2=new value,...
WHERE <条件>
示例
update emp set sal=1000,empno=8888 where ename='SCOTT'
4、 删除数据
DELETE FROM <table_name>
WHERE <条件>
示例
delete from emp where empno='7788'
c) DCL(Data Control Language,数据控制语言)用于定义数据库用户的权限。
1.授权
GRANT <权限列表> to <user_name>;
| DBA为用户授予权限 注意：只有sysdba才有授予权限的权利，必须在sysbda下操作 1）给用户授予连接表空间权限 例：给manager授予连接表空间pengdata的权限 SQL> grant connect to manager; 授权成功。 2）给用户授予使用资源（如创建对象)的权限 SQL> grant resource to manager; 授权成功。 3）给用户授予DBA管理员权限 SQL> grant dba to manager; 授权成功。 4）收回权限 SQL> revoke resource from manager; 撤销成功。 6. 其他用户给新用户授予操作表的权限 注意：要让拥有表的用户去给新用户授予权限 例：让用户scott给新用户manager授予对表emp的操作权限 SQL> conn scott/tiger 1）授予查询表的权限 已连接。 SQL> grant select on emp to manager; 授权成功。 注意：新用户查询另一个用户的表时必须指明表的所属用户 SQL> conn manager/peng 已连接。 SQL> select * from scott.emp; ………… 已选择14行。 2）授予对表的删除、修改、插入等权限 SQL> grant delete,update,insert on emp to manager; 授权成功。 3）一次性授予所有权限 SQL> grant all on emp to manager; 授权成功。 | ||||||||
| 1、用户管理 创建用户：创建用户必须用管理员用户登录。
注意：用户的密码不能以数字开头。 给用户授权（登录权限）
在ORACLE中，一个新的用户所有的权限都必须分别授予。 把多个权限一次性赋予一个用户，则可以将多个权限定义为一个角色。 在ORACLE中提供了两个主要的角色：CONNECT、RESOURCE，可以直接将这两个角色赋予用户test。 如果普通管理员密码丢失，可以使用超级管理员修改普通管理员密码.
如果要访问其他用户的表，则要授予此张表的访问权限 将scott用户下的emp表的查询、删除权限赋给test
|
2.收回权限
REVOKE <权限列表> from <user_name>
Oracle 的权限列表
connect 连接
resource 资源
unlimited tablespace 无限表空间
dba 管理员
session 会话
D)tcl(事务控制语言)
1.COMMIT 提交；
2.ROLLBACK [TO savepoint] 回滚;
3.SAVEPOINT <savepoint> 保存位置。
二、ORCL 其他对象
1、 视图
| 1、视图的作用 一个视图实际上就是封装了一条复杂的查询语句。 2、创建视图的语法
视图创建完成后，可以像查询表一样查询。 查看所有的视图
视图和表的区别： 原表的数据发生改变，则对应的视图数据跟随改变。 删除视图:
视图中是不应该包含真实数据的。 修改视图中的7369的部门编号为30。
发现在emp表中的7369部门编号发生修改，此操作时不合适的，因为创建视图是有条件的，一旦修改之后，此条件被破坏，所以在创建视图时，ORACLE提供了两个重要的参数： 1、WITH CHECK OPTION 不能更新视图的创建条件
创建条件不能更新，而其他字段呢？ 2、WITH READ ONLY 创建的视图只读 视图本身还是用来做查询的，所以不应该允许更改，所以此时可以使用该参数。 |
2、 序列
| 在数据库中，存在自动增长的列，如果想在ORACLE中完成自动增长，则只能依靠序列完成，所有的自动增长操作，需要用户手工处理。 例：创建一个myseq的序列，验证自动增长的操作
序列创建完成后，所有的自动增长由用户自己处理，所以在序列中提供以下的两种操作： 6、nextVal:取得序列的下一个内容 7、currVal:取得序列的当前内容 例：创建一张表，验证序列的操作
Nextval的内容始终在进行自动增长的操作，而curr使用取出当前操作的序列的结果。 修改序列的增长幅度： INCREMENT BY n
默认情况序列从1开始，指定序列的起始： START WITH m
其他参数： Maxvalue n | nomaxvalue 是否有最大值 重新创建序列，让其取值在1、3、5、7之间循环。
|
3、 用户
创建用户
CREATE USER <user_name> [profile "DEFAULT"]
identified by "<password>" [default tablespace "USERS"]
删除用户
DROP USER <user_name> CASCADE
三、PL/SQL
1、 函数
| 日期函数： 在日期中进行加或者减的规律： 日期-数字=日期 日期+数字=日期 日期-日期=数字（天数） 当前日期： Select sysdate from dual; 查询10部门员工进入公司的星期数 Select empno,ename,ROUND((sysdate-hiredate)/7) from emp; Moths_between();求出给定日期范围内的月数 Add_months():在指定的日期上加上指定的月数，求出之后的日期 Next_day();下一个的今天是哪一个日期 Last_day();求出给定日期的最后天的日期 Select empno,ename,months_between(sysdate,hiredate) from emp; Select Add_months(sysdate,4) from dual; Select next_day(sysdate,'星期二') from dual; Select last_day(sysdate) from dual; 转换函数： TO_CHAR()：转换为字符串 TO_NUMBER():转换成数字 TO_DATE():转换成日期 将年、月、日分开 年：y，四位数字用yyyy表示 月：m，二位数字用mm表示 日：d，二位数字用dd表示 Selct * from empno,ename,TO_CHAR(hiredate,'yyyy') year, TO_CHAR(hiredate,'mm') months,TO_CHAR(hiredate,'dd') day From emp; 使用TO_CHAR进行日期显示的转换功能，转换为中国人喜欢的格式。 ORAClE默认的格式‘2-1月-10’ Select empno,TO_CHAR(hiredate,'yyyy-mm-dd') from emp; TO_CHAR可以用在数字上 可以在数字上加入一些符号，以分割太长的数字，比如用‘，’ Select empno,ename,TO_CHAR(sal,'99,999') from emp; 9表示一位数字 希望数字可以表示出明确的区域，可以使用一下符号 $表示美元 L：表示LOCAL的缩写，以本地语言进行金额显示 Select empno,ename,TO_CHAR(sal,'$99,999') form emp; TO_NUMBER是将字符串转变为数字 Select TO_NUMBER('1243') +TO_NUMBER('123') from DUAL; TO_DATE()将一个字符串变为DATE类型 Select TO_DATE('2010-11-02','yyyy-mm-dd') from dual; 通用函数 求出每个员工的年薪 Select empno,ename,(sal+comm)*12 from emp; 有的员工奖金为null，null值计算之后结果还是null。使用NVL函数，将一个指定的null值变成指定的内容。 Select empno,ename,NVL(comm,0),(sal+NVL(comm,0))*12 income from emp; DECODE()函数，该函数类似于IF……ELSE……ELSE语句。 DECODE(col/expression,search1,result1[,search2,result2,……][,default]) Serach1：为用于比较的条件 Result：为返回值 如果col/expression和seatchi相比较，结果相同则返回resulti，否则返回默认值。 Select DECODE(1,1,'内容是1','2','内容是2','3','内容是3') from dual; 要求查询员工的编号，姓名，雇佣日期以及工作，将工作信息进行替换。 Select empno 员工编号,ename 员工姓名,hiredate 雇佣日期， DECODE（job,'CLERK','业务员','SALESMAN'，‘销售人员’，'MANAGER','经理' 'ANALYST','分析员','PRESIDENT''，'总裁'） 职位 from emp; |
2、 嵌套表（自定义对象）
| 一个表中包含了另外一个字表 如：一个部门可能承接多个项目 通常情况下，定义两张表department表，project表。
这种操作是最通用，最正确的操作。但ORACLE中引入嵌套表，可以将项目表的类型作为一个department表的字段类型，达到嵌套的功能。 如果想完成嵌套表的制作，首先保证一点：数据库在创建表的时候要指定字段类型，所以嵌套表本身要同样指定类型，这种类型需要单独定义。
注意：该定义后的“/”一定不能省略。 如果出现错误，则可以通过show errors 语句查看错误。 类型创建成功以后，并不能直接使用，因为此类型是一个完整的类型，所以还需要为该类型指定一个名称。
|
3、 数据库备份与回复
| 数据库备份：exp 数据库的恢复：imp 在D盘建立一个oracle_data的文件夹，在此文件夹下面备份ORACLE数据。该操作需要通过命令行进入到该文件目录下，在该目录下执行：exp即可。 测试数据库备份的作用，删除数据库下的所有表. ORACLE中无批量删除表的功能 恢复备份文件（需要到备份的文件目录下） 数据库备份步骤： 第一步： 第二部： 回复备份文件 |
4、 同义词
Dual是一张虚拟的表，该表是在哪里定的？ 测试该表是否属于system；
在sys用户存在该表。但是在scott用户下可以通过表名称直接访问此表。正常情况下，如果要访问不同用户下的表，必须使用："用户名.表名称"。 此时，实际上就是同义词的作用。同义词，可以让其他用户通过一个名称方便的访问"用户名.表名称"。 必须在管理员用户下创建 创建同义词：
删除同义词： DROP SYNONYM userinfo; |
5、 用户管理
创建用户：创建用户必须用管理员用户登录。
注意：用户的密码不能以数字开头。 给用户授权（登录权限）
在ORACLE中，一个新的用户所有的权限都必须分别授予。 把多个权限一次性赋予一个用户，则可以将多个权限定义为一个角色。 在ORACLE中提供了两个主要的角色：CONNECT、RESOURCE，可以直接将这两个角色赋予用户test。 如果普通管理员密码丢失，可以使用超级管理员修改普通管理员密码.
如果要访问其他用户的表，则要授予此张表的访问权限 将scott用户下的emp表的查询、删除权限赋给test
|
6、
四、ORAL JDBC连接
与Java的JDBC连接
1、 加驱动
Class.forName(“Oracle.jdbc.driver.OracleDriver”);
2、 创建连接
url:
thin:”jdbc:oracle:[email protected]:1521:ORCL,scott,tiger”
oci:”jdbc:orcal:[email protected]:1521:ORCL”必须配置服务器
区别：thin是瘦客户端，不需要安装Oracle的客户端，但是oci必须使用客户端
Oci的速度比thin要快，thin方式是纯的Java连接方式
五、
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)