一、 oracle基本的sql语言
a) DDL(Data Definition Language,数据定义语言)用于定义数据的结构,如创建,修改或者删除数据库对象。
1) 创建
A) 创建表
CREATE TABLE <table_name>( |
复制表
CREATE TABLE <table_name> as <SELECT 语句> 示例:
|
B) 创建索引
Create [unique] index <index_name> on <table_name> (字段 [asc|desc]); Unique----确保所有的索引列中的值都是可以区分的。 [asc|desc] ------在列指定排序创建索引。
(创建索引的准则:
实例: Create index i_1 on emp(empno asc); |
C) 创建同义词
同义词
Dual是一张虚拟的表,该表是在哪里定的? 测试该表是否属于system;
测试该表是否属于sys:
在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 <条件1>AND<条件2> --两个条件都满足
示例:
select * from emp where deptno=10 and sal>1000;
where <条件1>OR<条件2> --两个条件中有一个满足即可
示例:
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为用户授予权限 | ||||||||
1、用户管理 创建用户:创建用户必须用管理员用户登录。
注意:用户的密码不能以数字开头。 给用户授权(登录权限)
在ORACLE中,一个新的用户所有的权限都必须分别授予。 把多个权限一次性赋予一个用户,则可以将多个权限定义为一个角色。 在ORACLE中提供了两个主要的角色:CONNECT、RESOURCE,可以直接将这两个角色赋予用户test。 如果普通管理员密码丢失,可以使用超级管理员修改普通管理员密码.
在一般系统中,用户第一次登录可以修改密码,即让密码失效。
默认情况下,scott用户是被锁住的,可以使用如下命令锁住用户
解锁被锁定的用户
如果要访问其他用户的表,则要授予此张表的访问权限 将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、创建视图的语法
例:建立一个视图,此视图包含了全部的20部门的雇员信息
注意:如果提示权限不足的问题,则需要重新授予权限。
视图创建完成后,可以像查询表一样查询。 查看所有的视图
视图和表的区别: 原表的数据发生改变,则对应的视图数据跟随改变。 删除视图:
修改视图:ORACLE提供一个替换的命令
视图中是不应该包含真实数据的。 修改视图中的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之间循环。
如果指定CACHE值,oracle就可以预先在内存里面放置一些sequence,这样存取的快些。cache里面的取完后,oracle自动再取一组到cache。使用cache或许会跳号, 比如数据库突然不正常down掉(shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。
|
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 语句查看错误。 类型创建成功以后,并不能直接使用,因为此类型是一个完整的类型,所以还需要为该类型指定一个名称。
此时可以直接使用pro_nt表示pro_ty类型,类似于varchar(2)表示字符串一样。
插入数据时,需要指定一个pro_ty的类型。
如要查询一个部门的多个项目,则要查询嵌套表。
更新项目1001的项目名称
|
3、 数据库备份与回复
数据库备份:exp 数据库的恢复:imp 在D盘建立一个oracle_data的文件夹,在此文件夹下面备份ORACLE数据。该操作需要通过命令行进入到该文件目录下,在该目录下执行:exp即可。 测试数据库备份的作用,删除数据库下的所有表. ORACLE中无批量删除表的功能 恢复备份文件(需要到备份的文件目录下)
数据库备份步骤: 第一步:
第二部:
回复备份文件
|
4、 同义词
Dual是一张虚拟的表,该表是在哪里定的? 测试该表是否属于system;
测试该表是否属于sys:
在sys用户存在该表。但是在scott用户下可以通过表名称直接访问此表。正常情况下,如果要访问不同用户下的表,必须使用:"用户名.表名称"。 此时,实际上就是同义词的作用。同义词,可以让其他用户通过一个名称方便的访问"用户名.表名称"。 必须在管理员用户下创建 创建同义词:
删除同义词: DROP SYNONYM userinfo;
|
5、 用户管理
创建用户:创建用户必须用管理员用户登录。
注意:用户的密码不能以数字开头。 给用户授权(登录权限)
在ORACLE中,一个新的用户所有的权限都必须分别授予。 把多个权限一次性赋予一个用户,则可以将多个权限定义为一个角色。 在ORACLE中提供了两个主要的角色:CONNECT、RESOURCE,可以直接将这两个角色赋予用户test。 如果普通管理员密码丢失,可以使用超级管理员修改普通管理员密码.
在一般系统中,用户第一次登录可以修改密码,即让密码失效。
默认情况下,scott用户是被锁住的,可以使用如下命令锁住用户
解锁被锁定的用户
如果要访问其他用户的表,则要授予此张表的访问权限 将scott用户下的emp表的查询、删除权限赋给test
回收权限
|
6、
四、ORAL JDBC连接
与Java的JDBC连接
1、 加驱动
Class.forName(“Oracle.jdbc.driver.OracleDriver”);
2、 创建连接
url:
thin:”jdbc:oracle:thin@hostip:1521:ORCL,scott,tiger”
oci:”jdbc:orcal:oci8@host:1521:ORCL”必须配置服务器
区别:thin是瘦客户端,不需要安装Oracle的客户端,但是oci必须使用客户端
Oci的速度比thin要快,thin方式是纯的Java连接方式
五、