Oracle
oracle是美国甲骨文公司开发的数据库产生
oracle的版本主要分为:
oracle 8i --------Internet
oracle 9i
oracle 10G--------Grid(网格)
Oracle 11G
Oracle 12C
安装oracle时，安装目录，不能包含任何中文字符
第一步：先启动oracle的服务
OracleServiceORCL ------------oracle实例
-----该服务启动完成以后，oracle数据库可以单独使用，但是不能用程序连接
OracleXXXXXXListener
-------该服务启动完成以后，oracle的数据库就可以使用程序连接
oracle数据库服务器包含两个部分的内容:
1、oracle数据库 (用于存储数据信息)
2、oracle实例 (相当于是一个仓库管理员,启动它以后，可以操作oracle数据库中存放的信息)
oracle数据库服务器 = oracle数据库 + oracle实例
oracle数据库中，主要存放三种文件:
1、数据文件 (存放数据、约束、主外键、序列、同义词、存储过程、触发器、游标....)
2、日志文件 (存放的是系统日志信息)
3、操作文件 (存放数据文件与日志文件的物理位置)
oracle的几个用户权限
sys--------------------(SYSDBA) 数据库管理员(拥有数据库的最高权限)
system----------------(SYSOPER) 数据库操作员(它的权限仅次于管理员)
-----------------------(NORMAL) 普通用户
1、当前的操作系统的登录用户的级别必须是:administrator
2、采用匿名登录的方式登录系统 : sqlplus /nolog
3、切换到sysdba权限 conn /as sysdba;
4、创建表空间:
##语法
create tablespace 表空间的名称 datafile '路径' size 10m autoextend on next 5m maxsize 100m;
例：
create tablespace vincentSpace datafile 'f:/oracle/vincentspace.dbf' size 20m autoextend on next 10m maxsize 100m;5、创建临时表空间
##语法:
create temporary tablespace vincentTempSpace tempfile 'f:/oracle/vincentTemp.dbf'size 10m autoextend on next 5m maxsize 50m;6、创建用户名以及密码，并且指定该用户的信息存到哪一个表空间，以及它的临时表空间
##语法：
create user 用户名 identified by 密码 default tablespace vincentSpace temporary tablespace vincentTempSpace ;
例子:
create user xzc identified by xzc default tablespace vincentSpace temporary tablespace vincentTempSpace ;7、授予vicent登录数据库的权限
grant connect to 用户名;
grant connect to xzc;8、授予进行数据库操作的权限
grant resource to 用户名;
grant resource to xzc;步骤：
-- 匿名登录
sqlplus /nolog
-- 切换到dba权限
conn /as sysdba
-- 创建表空间
create tablespace myspace datafile 'd:/my.dbf' size 10m autoextend on next 5m maxsize 50m;
-- 创建临时表空间
create temporary tablespace mytempspace tempfile 'd:/mytemp.dbf' size 10m;
-- 查看有哪些表空间
select tablespace_name from dba_tablespaces;
-- 删除表空间
drop tablespace 表空间名称;
-- 创建用户
create user hello identified by hello default tablespace 表空间名称 temporary tablespace 临时表空间名称;
-- 查看用户
select username from dba_users;
-- 删除用户
drop user xzc;
-- 修改密码
alter user 用户名 identified by 密码;
-- 授权
grant connect to 用户名;
grant resource to xzc;
-- 撤销权限
revoke connect from 用户名;
revoke resource from xzc;
-- 切换身份
conn 用户名/密码
-- 查看表的结构
desc 表名;
-- 导出数据、导入数据
expdp dumpfile=xx logfile=xxx directory=xxx tables=user.table
impdp dumpfile=xx logfile=xxx directory=xxx tables=user.table
-- 查询所有用户表
select table_name from user_tables;
-- 把表bank 的权限给 scott用户
grant all on bank to scott;
-- 把dba权限给某个用户
grant dba to test1;
-- 锁定用户
alter user 用户名 account lock;
-- 解锁用户
alter user 用户名 account unlock;--scott用户下面包含的表,主要是一些测试数据,tiger
*****************注意：导入或导出的时候，不需要登录到oracle,在控制台下操作即可
*****************注意： oracle有两个端口，用程序连接的端口号是:1521
它还占用了8080端口
oracle的连接字符串:
mysql----url: jdbc:mysql://localhost:3306/数据库名称
oracle---url: jdbc:oracle:thin:@localhost:1521:实例名称
mysql---驱动字符串: com.mysql.jdbc.Driver
oracle---- oracle.jdbc.driver.OracleDriver
创建一张表inf
create table inf (
Id int primary key,
name varchar(20),
age int
);查看表空间名称
select tablespace_name from dba_tablespaces;查看有哪些用户
select username from dba_users;修改用户密码
alter user 用户名 identified by 新密码;创建新用户
create user 用户名 identified by 新密码 default 表空间 临时表空间
授权用户
grant connect to 用户;
grant resource to 用户;
撤销权限
revoke connect from 用户;
conn 用户名/密码
默认情况下,哪一个用户创建的数据,只能自己使用,但经过授权以后,别的用户也可以使用
授权的方式有两种:
1、以dba的身份直接授权
2、哪一个用户创建的数据，该用户也可以授权给其他用户
以dba的身份授权:
grant insert on vincent.test to jor;
grant delete on vincent.test to jor;
grant update on vincent.test to jor;
grant select on vincent.test to jor;
grant all on vincent.test to jor;
以dba的身份撤销权限:
revoke insert on vincent.test from jor;
revoke all on vincent.test from jor;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
以创建者的身份来授权:
grant insert on test to jor;
查看删除的表：
show recyclebin;恢复删除的表
flashback table info3 to before drop删除数据，不进回收站
drop table 表名 purge查看当前表空间中，有哪些表
select table_name from user_tables;
1、数值类型 number
2、字符类型 char varchar2
3、日期类型 date
4、大对象类型 clob blob bfile
5、特殊类型
@@@@@@@@@@@数值类型:
int表示整数
float表示小数
推荐的数值类型是: number
id number: 表示定义一个数值类型的字段id，默认长度为38位长度
id number(11) 表示id是一个数字类型，最大长度为11位
id number(10,2) 表示id是一个数值类型，可以保留小数，小数点后最多保留两位有效位数
@@@@@@@@@@@@@@@@@@
字符类型: char varchar,nchar,nvarchar
oracle推荐的字符类型是: varchar2 与 char
name varchar(20) ---定义一个字段name为字符类型，最大长度为20个字符，如果实际存的长度不到20字符，系统将会按照实际存储的大小收缩空间
-----------优点：节约存储空间
-----------缺点: 由于每一次存储以后，系统都需要重新判断存的数据是否占满空间用于决定是否释放多于空间，所以，相对会消耗更的资源
gender char(2) ---定义一个字段gender为字符类型，占用两个字符空间,不管空间是否占满，空间大小都不再改变
---------缺点:相对浪费空间,空间不会根据实际存入的内容进入缩放
--------优点：存了数据以后，它不会检查实际占用空间，所以，速度相对较快
如果可以确定存储的数据长度，应该使用char
如果不确定存储的数据长度，应该用varchar
char与varchar都需要两个字节长度才可以存储一个汉字
nchar与nvarchar这两种类型，一个长度即可存储一个汉字
varchar 与 varchar2的区别:
都表示字符类型，varchar它是所有数据库必须要有的一种类型
varchar2才是oracle自己的字符数据类型，它的兼容性会更好
@@@@@@@@@@@@@@@@
日期类型 date
bir date
insert into inf values(1,'bruce',date'2014-10-10');
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
大对象类型: 最大容量可以存储4GB容量
CLOB 文本大对象
BLOB 二进制大对象
BFILE 文件大对象
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
特殊类型
rownum与rowid (伪列)
rowid-----它用于显示当前这一行数据在oracle数据库中的物理位置
rownum----它用于给数据表中的每一行数据，生成一组连续的编号
数值类型：number
字符类型: varchar2 char
日期类型: date
大对象类型: clob,blob,bfile
特殊类型: rowid rownum
create table inf
(
id number(11) primary key,
name varchar2(20) not null,
age number(11)
)
drop table inf; 删掉表格
truncate table inf; 清空表格
--oracle中,没有自动增长列,但是通过序列,可以实现同样的效果 sequence
create sequence inf_seq start with 1 increment by 1;
--创建一个序列,名称是inf_seq,初始从1开始,每次递增1个数
drop sequence inf_seq;--删除序列
insert into inf values(inf_seq.nextval,'chris',23);
insert into inf values(inf_seq.nextval,'bruce',24);
insert into inf values(inf_seq.nextval,'jack',25);
insert into inf values(inf_seq.nextval,'tom',26);
select * from inf;
drop table inf;
create table province
(
pid number(11) primary key,
pname varchar2(20)
)
insert into province values(1,'湖北省');
insert into province values(2,'广东省');
/*
id 主键
name 非空
age 必须在1-120岁之间
gender 必须是'男'或'女'
address 默认在武汉市
idCard 必须是18位,前17位是数字,最后一位可以是x,y,或数字
tel 满足电话号码的基本格式
*/
insert into myuser3 values(1,'wesley',18,'男','济南市',123456789012345678,18621624530,1);
create table inf
(
id number(11) primary key,
name varchar2(20) not null,
age number check(age between 1 and 120),
gender char(2) check(gender='男' or gender='女'),
address varchar2(20) default 济南市
idCard char(18) check(regexp_like(idcard,'^[0-9]{18}$|^[0-9]{17}(x|y)$')),
Tel varchar2(20) check (regexp_like(tel,'^1(3,5,8)[0-9]{9}$|^[0-9]{3}-[0-9]{8}$|^[0-9]{4}-[0-9]{7,8}$')),
pid number(11) references province(pid)
)
pid number(11) references province(pid)
insert into inf values(3,'jack',23,'男',default,'4444','028-12345678',4);
--正则表达式的用法:
字段 varchar2(20) check(regexp_like(字段名,正则表达式的规则))
在oracle中的正则表达式的每一个规则,要以 ^ 开始,以$结束
^1(3,5,8)[0-9]{9}$|^[0-9]{3}-[0-9]{8}$|^[0-9]{4}-[0-9]{7,8}$
1、连接运算符 ||
2、关系运算符 > >= < <= == != <> between in not in
3、算术运算符 + - * /
4、逻辑运算符 and or
5、集合运算符 union union all intersect
语句的分类:
ddl 数据定义语言 create drop alter
dml 数据操作语言 insert delete update select
dcl 数据控制语言 grant revoke
自连接(natural join): 两张表必须有相同的字段
内连接(inner join):
---------------------
select * from stu;
select * from score;
select * from subject;
--内连接第一种用法:
select a.stuid,a.name,a.age,c.sname,b.score from stu a,score b,subject c where
a.stuid=b.stuid and b.subjectid=c.id;
--内连接第二种用法,连接三张表
select a.stuid,a.name,c.sname,b.score from (stu a inner join score b on a.stuid=b.stuid)
inner join subject c
on(b.subjectid=c.id);
--要求:
1、如果考试的科目是英语必须80以上或者考试的科目是物理70分以上
2、按成绩排序
3、名称 科目名称 分数
select a.stuid,a.name,c.sname,b.score from stu a,score b,subject c
where a.stuid=b.stuid and b.subjectid=c.id and
((c.sname='英语' and b.score>=80)or(c.sname='物理' and b.score>=70));
--内连接所连接的两张表是平级关系
--@@外连接
#### 它所连接的两张表是主表与次表的关系,主表的数据,必须全部显示,次表的数据,只在与主表中的数据有关联的才能显示
create table stu
(
id number(11) primary key,
name varchar2(20)
)
insert into stu values(1,'张三');
insert into stu values(2,'李四');
insert into stu values(3,'王五');
insert into stu values(4,'孙六');
drop table score;
create table score
(
sid number(11) primary key,
score number(11),
stuId number(11)
)
insert into score values(1,99,1);
insert into score values(2,78,2);
--外连接的语法:
--左外连接
select 字段..from 表1 left outer join 表2 on (表1.字段=表2.字段);
左外连接,左边的表是主表,必须全部显示,右边的表是次表,对应上才可以显示
select a.id,a.name,b.score from stu a left outer join score b on (a.id=b.stuid);
--右外连接
select 字段..... from 表1 right outer join 表2 on (表1.字段=表2.字段)
右外连接,右边的表是主表,必须全部显示,左边的表是次表,对应上才可以显示
select a.id,a.name,b.score from stu a right outer join score b on (a.id=b.stuid);
--完全外连接:(两张表都是主表,不管是否对应上,都要显示)
select a.id,a.name,b.score from stu a full outer join score b on (a.id=b.stuid);
--外连接的第二种用法: 有+ 为次表
select a.id,a.name,b.score from stu a,score b where a.id(+)=b.stuid;
--查所有参加考试的学生信息
select a.name from stu a,score b where a.id = b.stuid;
--查询所有没有参加考试的学生信息
1、先找出参加了考试的学生编号
select stuid from score;
2、找出编写不在这个结果的学员信息
select * from stu where id not in (select stuid from score)
在一条查询语句中,包含多个select子句
create table inf
(
id number(11) primary key,
name varchar2(20),
regTime date
)
insert into inf values(1,'jack',date'2002-10-02');
insert into inf values(2,'chris',date'2001-12-22');
insert into inf values(3,'andy',date'1992-09-12');
insert into inf values(4,'cindy',date'2012-01-14');
insert into inf values(5,'jor',date'1998-03-22');
select * from inf where regTime > date'2000-01-01';
select * from inf where regTime between date'1995-01-01' and date'2005-01-01';
create table info2
(
name varchar2(20) not null,
gender varchar2(20) not null,
class varchar2(20),
score number,
bir date
)
insert into info2 values('余胜军','男','s1t80',92,date'1992-01-20');
insert into info2 values('黄辉','男','s1t80',91,date'1986-09-01');
select rownum,name,gender,class,score,bir from info2;
select rownum,p.* from info2 p where rownum <=3; //查询前三条
--注意:如果在查询语句中有where与order by ,where一定要在order by 之前
select p.* from info2 p where rownum<=3 order by score desc; X
select rownum,t.* from (select p.* from info2 p where p.gender='男' order by score desc) t where rownum<=3; //查询男性的前三名
--查询第四到第六条数据
--假设: 每一页显示3条数据,当前第5页
第一页 rownum分别是: 1,2,3
第二页 rownum分别是: 4,5,6
7,8,9
--查询第二页的数据
需要知道的值:
1、第1页+第2页总共应该显示的rownum有哪些 当前页 * 每一页显示的条数 6
2、当前页之前,已经显示过哪些rownum (当前页-1)*每一页显示的条数 3
select k.* from (select rownum r,f.* from info2 f where rownum <=6) k where k.r>3
分页查询，每页显示三条数据
第一页：select aa.* from (select rownum r,s.* from score s) aa where aa.r between 1 and 3;
第二页：select aa.* from (select rownum r,s.* from score s) aa where aa.r between 4 and 6;
第三页：select aa.* from (select rownum r,s.* from score s) aa where aa.r between 7 and 9;
dual：它是oracle中非常特殊的一张表,它本身并不是真实存在的,它只是一张虚拟表
--一般在使用函数查询的时候,就可以使用虚拟,如果写查询的时候,不确定查询哪一张表,就可以写虚表名称。
sysdate: 获得当前系统时间
--语法：
select sysdate from dual;
add_months（）:指定时间上面加上月份,形成新的时间
select add_months(时间,加上几个月) from dual;
select add_months(date'2011-10-1',2) from dual;
select add_months(sysdate,-3) from dual;
select p.*,add_months(bir,12) 出生日期 from info2 p;
--months_between:用于计算两个时间相差的月份
语法: select months_between(第一个时间,第二个时间) from 表名;
select floor(months_between(sysdate,date'2011-3-14')/12) 相差年份 from dual;
floor 取整
select name,gender,score,floor(months_between(sysdate,bir)/12)||'岁' 年龄 from info2;
select name,gender,score,floor(months_between(sysdate,bir)/12)||'岁' 年龄
from info2 where floor(months_between(sysdate,bir)/12) between 20 and 25
order by bir; //查询年龄在20--25岁之间的人
--next_day( ,) 获得下一个星期几是哪一天,
注意: 星期日 1 星期一 2 星期六 7
select next_day(date'2011-02-12',2) from dual;
--last_day 用于得到本月的最后一天
select last_day(date'2000-02-22') from dual;
--truncate: 用于截断日期格式,只保留年-月-日
select sysdate from dual;
select trunc(sysdate) from dual;
--extract :用于获得日期中,指定部份的值 year,month,day
select extract(year from sysdate) from dual;
length()---计算字符的长度
select length('abc') from dual;
查询名字长度为2 且为男生的
select * from info2 where length(name)=2 and gender='男';
select * from info2 where name like '__'
--upper(),lower()
upper:将字符转换大写
lower:将字符转换成小写
select upper('abc233FFs经在') from dual;
select upper(class) from info2;
--ltrim 去掉左侧的空格
--rtrim 去掉右侧的空格
select ltrim(' 123') from dual;
--substr:截取字符串的指定内容
substr(字段串,开始的位置,要截取的长度) 开始下标为1
select substr('abcd123kkk',5,3) from dual;
--replace():替换函数
select replace('abcKKK123','KKK','yyy') from dual;
insert into info3 values(1,'jack','13986181999');
select substr(tel,4,5) from info3;
select replace(tel,substr(tel,4,5),'*****') from info3;
输出 13*****81999
--concat(第一个字符,第二个字符) 将第二个字符追加到第一个字符的尾部
select concat('hello','world') from dual;
abs()----求绝对值
select abs(23) from dual;
select power(m,n);---计算m的n次方
select power(2,16) from dual;
select mod(10,3) from dual;--取模
select round(1234.56789,2) from dual;--四舍五入,保留小数字之后的几位
select sqrt(2) from dual;--开根
select sign(0) from dual;--用于判断是正数,负数,还是零 1,-1,0
select decode(gender,'男','男同学','女同学') from info2;
create table info4
(
id number(11) primary key,
name varchar2(20),
age number(20)
);
insert into info4 values(1,'jack',16);
insert into info4 values(2,'chris',19);
insert into info4 values(3,'bruce',18);
insert into info4 values(4,'andy',22);
insert into info4 values(5,'mariah',15);
decode(表达式,值,结果1,结果2) --如果表达式与值相等,显示结果1,否则显示结果2
decode(表达式,值1,结果1,值2,结果2,值3,结果3......)
select k.*,decode(sign(age-18),-1,'未成年人','成年人') 是否成年 from info4 k;
select k.*,decode(sign(age-18),-1,'未成年',0,'刚刚成年',1,'已成年') 是否成年 from info4 k;
to_date() --将字符类型,转换成日期类型
to_char() --将数据转换成字符类型
create table info5
(
name varchar2(20),
bir date
)
--默认日期格式: 日-月-年 日/月/年 '12-2月-2014'
insert into info5 values('aa','19/3月/2014');
insert into info5 values('bb','19-4月-2014');
insert into info5 values('cc',to_date('2013-12-23','yyyy-MM-dd'));
insert into info5 values('dd',to_date('2011/10/11','yyyy/MM/dd'));
insert into info5 values('kk',date'2011-11-11');
--to_char()
select to_char(12345) from dual;
select to_char(date'2011-02-02','yyyy-MM-dd') from dual;
select p.*,rownum 收入名次 from emp p order by p.salary desc;
----oracle先查询数据生成rownum,然后再对生成的结果进行排序
select p.*,rownum 名次 from (select * from emp order by salary desc) p;
--分析函数主要有四个:
row_number() over(order by 字段)--先排序,再生成连续的序号
row_number() over(partition by 字段 order by 字段)
--先按照某一字段分组,然后再对每一组里面的某一个字段进行排序生成序号
rank() over(partition by 字段 order by 字段)
--先按照某一字段分组,然后再对每一组里面的某一个字段进行排序生成序号,
--如果有并列值,就产生一组相同的序号
dense_rank() over(partition by 字段 order by 字段)
--先按照某一字段分组,然后再对每一组里面的某一个字段进行排序生成序号,
--如果有并列值,就产生一组相同的序号
--即使有并列值,序号依然是连续的
row_number() over()--该分析函数可以排序以后再生成一个连续的序号
select p.*,row_number() over(order by salary desc) 名次 from emp p;
--查看工资最高的三个人员信息
select * from (select p.empname,p.emplocation,p.salary,d.deptname,
row_number() over(order by salary desc) 名次
from emp p,dept d where p.deptid=d.deptid) where rownum<=3;
--查看每一个部门工资收入高低情况,并且在部门内按照工资收入高低序排序
select e.empname,e.emplocation,d.deptname,e.salary,
dense_rank() over(partition by d.deptid order by salary desc) 名次
from emp e,dept d where e.deptid=d.deptid ;
--3、查看每个部门工资总额,按总额排序
select d.deptname,sum(e.salary)
from emp e,dept d
where e.deptid=d.deptid
group by d.deptname order by sum(e.salary) desc;
--4、查看部门总工资高于18000的部门信息，降序
select d.deptname,sum(e.salary)
from emp e,dept d
where e.deptid=d.deptid
group by d.deptname having sum(e.salary) >18000 order by sum(e.salary) desc;
--5、查看每个部门工资最高的人员信息
select * from (select e.empname,e.emplocation,d.deptname,e.salary ,
row_number() over(partition by d.deptid order by e.salary desc) 名次
from emp e,dept d where e.deptid=d.deptid) k where k.名次=1;
--6、查看每个部门工资最高的人员前三名信息
select * from (select e.empname,e.emplocation,d.deptname,e.salary ,
row_number() over(partition by d.deptid order by e.salary desc) 名次
from emp e,dept d where e.deptid=d.deptid) k where k.名次 in (1,2,3);
----------------------------------------------
info2表
--7、查询info2表，查询生日是本月的学生信息
select * from info2 where extract(month from bir)=extract(month from sysdate);
9、查询:所有1990年以后出生学生信息
Select * from info2 where bir > date’1990-01-01’;
--注意:如果在查询语句中,使用到了分组函数group by 那么就只能查询 用于分组的字段或者聚合函数
select class,count(name) from info2 group by class;
--查询班级总人数大于5个人的班级信息
--having是用于在分组以后设置查询条件的关键字,分组以后设置条件必须用到having
--having 不能单独出现,它只能做为group by 的子句出现
where > group by > having >order by
select class,count(name) from info2 group by class having count(name)>5 order by count(name);
1、同义词 synonym
2、视图 view
3、序列 sequence
4、索引 index
同义词: 它是一个数据库对象,一般用于关联到某一张表,操作同义词其实就在操作这张表
--使用同义词的好处
1、可以隐藏用户信息
2、可以隐藏真实的表名称
--同义词的分类:
1、私有同义词:只能自己用,如果其他用户要使用,需要经过授权
2、公有同义词:大家都可以用
--查看当前用户有哪些私有同义词
select synonym_name from user_synonyms;
--创建私有同义词的语法:
create synonym 私有同义词名称 for 表;
create synonym syn_info for info2;
select * from syn_info;
delete from syn_info where name='余胜军';--对同义词的操作,其实就是在对表操作
--删除同义词
drop synonym syn_info;
select * from scott.syn_dept;
--用户可以授权其他用户访问该私有同义词的权限
grant insert on 私有同义词名称 to 用户;
grant all on 私有同义词名称 to 用户;
-- 撤销访问私有同义词的权限
revoke all on 同义词 from 用户;
--两种身份都可以授权
dba 或者是同义词的创建者
---授予创建私有同义词的权限---只有dba才有权限执行该操作
grant create synonym to 用户名;
grant create public synonym to 用户名;
----------------------------------------------------------------
创建公共同义词
create public synonym syn_info_pb for info5;
--查看有哪些公共同义词
select synonym_name from all_synonyms where owner='PUBLIC';
--如果要访问公共同义词,用户必须要拥有对公共同义词所关联表有访问权限
grant all on info5 to scott;
视图它是一数据库对象,一般用于关联到一张或者多张表的查询结果,操作视图就在操作表中的数据
--查看有哪些视图
select view_name from user_views;
--授权创建视图 dba
grant create view to 用户名;
--创建视图 (基于单表创建)
create or replace view 视图名称 as 查询语句;
create or replace view info_vw as select * from info2;
create or replace view info_vw as select rownum id,name,gender,class from info2;
create or replace view info_vw as select name,gender from info2 where gender='女';
create or replace view info_vw as select * from info2 with read only;
--授权scott访问该视图的权限
grant all on info_vw to scott
insert into info_vw values('张飞','男','s1t82',90,date'2012-01-22');
--基于多表的视图 (如果视图是基于多表的,一般不允许做增删改)
create or replace view my_vw as
select * from (select e.empname,e.emplocation,d.deptname,e.salary ,
row_number() over(partition by d.deptid order by e.salary desc) 名次
from emp e,dept d where e.deptid=d.deptid) k where k.名次=1;
select * from my_vw;
grant all on my_vw to vincent;
drop view 视图名称
--查看序列
select sequence_name sequence
from user_sequences;
--删除序列
drop sequence inf_seq;
--创建序列
create sequence info_seq
start with 1---初值
increment by 1--递增量
minvalue 1 --最小值
maxvalue 10 --最大值--默认没有最大值
nocycle --到达最大值,重新循环生在新的序列 nocycle
nocache --不缓存 --cache 10
--查看当前序列的值
select inf_seq.currval from dual;
--初始化序列,产生一个序列号
select inf_seq.nextval from dual;
--简化的方式创建序列
create sequence inf_seq;
--默认初值为1,递增量为1,最小值为1,没有最大值,不循环,不缓存
create table inf
(
id number(11) primary key,
name varchar(20),
age number(11)
)
begin
for a in 1..2000000
loop
insert into inf values(inf_seq.nextval,'username'||a,23);
end loop;
end;
--查询姓名为 username198
select * from inf where name='username198'; --没有索引 2.297秒
--有索引 0.375
--查看当前表有哪些索引
select index_name from user_indexes where table_name ='INF';
--删除索引
drop index 索引名;
--创建索引
create index inf_index on inf(name);--对inf表中的name字段创建索引
索引可以提高检索效率,但是,并不是所有情况下,都适合用索引,在有情况下,建立索引反而会降低速度
1、索引主要用于海量数据查询,如果数据量很少就不适合建立索引
2、如果会经常对数据表做增、删、改的操作也不适合建立索引,因为内容一改变,索引会重新排列
--注意:对表的某一个字段建立索引,在查询的时候,只有把建立了索引的字段作为查询条件,才能提高查询效率
select * from inf where name=’’
grant create session to test;--赋予create session的权限
grant create table,create view,create trigger, create sequence,create procedure to test;--分配创建表，视图，触发器，序列，过程 权限
grant unlimited tablespace to test; --授权使用表空间
;
create table bank
(
id number(11) primary key,--编号
name varchar2(20) not null,--帐号名称
salary number(11)--帐户余额
)
insert into bank values(1,'jack',1000);
insert into bank values(2,'chris',5000);
grant all on bank to scott;
---多个用户,如果同时去访问同一数据,就会产生并发问题
通过锁的机制,就可以解决这一类问题
oracle的锁有两种分类方式:
第一类:按照锁的范围大小,可以分为: 行级锁 与 表级锁
第二类:按照锁的力度可以分为: 共享锁 与 排它锁
--------------------------------------------------------------
commit ---提交
rollback ---回滚(撤销之前的操作,但是已提交的数据无法撤销)
表级锁:如果对表加了表级锁,在解锁之前,其他不能对该表进行任何修改操作(可以查询)
lock table 表名 in exclusive mode; ---表级锁的语法
lock table 表名 in row exclusive mode; ---行级锁的语法
如果对表进行修改操作,系统会对表自动加上一个行级锁
什么情况会自动解锁:
1、执行 commit;
2、执行 rollback;
3、执行 DDL语句或 DCL语句
DDL语句(数据定义语言) create drop
DCL语句(数据控制语言) grant revoke
行级锁:如果对表加了行级锁,在解锁之前,其他用户不能对当前用户正在编辑的行进行修改操作,但其他的行可以
按照锁的力度分为: 共享锁 与 排它锁
对一张表加了排它锁,其他用户在该表锁解锁之前 不能对表加任何锁
对一张表加了共享锁,其他用户还可以对表加锁,但只能加共享锁
--排它锁: lock table 表名 in exclusive mode;
--共享锁: lock table 表名 in share mode;
如果两边都把表锁住,如果做同时做修改操作,就会造成“死锁”,系统侦测到"死锁",就会自动解锁
ALTER USER user1 ACCOUNT LOCK --给用户加锁
ALTER USER user1 ACCOUNT UNLOCK --解锁用户
Procedure Language / Structured Query Language
过程化语言 / 结构化查询语言
PL/SQL语句可以用于编写: 函数\存储过程\触发器\游标
--PL/SQL语句的基本语法:
declare
--此处用于声明,定义变量
begin
--此处写代码块
end;
例:
declare
name varchar2(20):='jack';--声明变量
age number(11):=23;
begin
dbms_output.put_line('姓名是:'||name);
dbms_output.put_line('年龄是:'||age);
end;
------------------------------------------
declare
name varchar2(20);
age number(11);
begin
name:='&请输入你的姓名'; //动态输入，字符串要加’’,数字不用
age:=&请输入你的年龄;
dbms_output.put_line('姓名是'||name);
dbms_output.put_line('姓名是'||age);
end;
-------------------------------------------
declare
i number(11):=1;
begin
while(i<100)
loop
dbms_output.put_line('i的值为:'||i);
i:=i+1;
end loop;
end;
-------------------------------
declare
name constant varchar2(20):='bruce'; //是常量，不能变
begin
name:='lee';
dbms_output.put_line('姓名是:'||name);
end;
----@@ if 与 end if需要成对出现 if...then....end if
declare
age number(11):=18;
begin
if(age>17)
then
dbms_output.put_line('成年人');
end if;
end;
----@@ if...then else end if
declare
age number(11):=11;
begin
if age>17
then
dbms_output.put_line('已是成年人');
else
dbms_output.put_line('未成年人');
end if;
end;
例：--判断是否可以参加决赛（嵌套语句）
declare
speed number(11):=17;
gender char(2):='女';
begin
if(speed<=15)
then
if(gender='男')
then
dbms_output.put_line('你可以参加男子决赛');
else
dbms_output.put_line('你可以参加女子决赛');
end if;
else
dbms_output.put_line('你不能参加决赛');
end if;
end;
---------------------------------------------------
接收一个成绩,如果在90以上,显示优秀，80以上显示良好,60以上显示及格,否则,显示不及格
--需要一种类似于switch来完成
case
when 条件1 then 代码1;
when 条件2 then 代码2;
end case;
--如果有任一条件匹配成功,后续代码就不再运行
declare
score number(11);
begin
score:=&请输入你的成绩;
case
when score>=90 then dbms_output.put_line('优秀');
when score>=80 then dbms_output.put_line('良好');
when score>=60 then dbms_output.put_line('及格');
when score<60 then dbms_output.put_line('不及格');
end case;
end;
while循环
declare
i number(11):=1;
k number(11);
begin
k:=&请输入循环次数;
while(i<=k)
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
------------------------------
for循环
begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;
--------------三角形-----------------
循环结构的嵌套:
declare
i number(11);
j number(11);
begin
for i in 1..10 --控制行
loop
for j in 1..i
loop
dbms_output.put('*');
end loop;
dbms_output.put_line('');
end loop;
end;
--------------等边三角形-----------------
declare
i number(11);
j number(11);
k number(11);
begin
for i in 1..10 --控制行
loop
for j in 1..11-i
loop
dbms_output.put(' ');
end loop;
for k in 1..2*i-1
loop
dbms_output.put('*');
end loop;
dbms_output.put_line('');
end loop;
end;
create table inf
(
id number primary key,
name varchar2(20),
age number
)
select * from inf;
create sequence inf_seq;
添加语句
begin
for i in 1..10
loop
insert into inf values(inf_seq.nextval,'name'||i,23);
end loop;
commit;
end;
修改语句
begin
update inf set name='bruceLee' where id=1;
commit;
end;
删除语句
begin
delete from inf where id=1;
commit;
end;
动态输入编号,根据输入的编号查询数据（select 必须要 into）
declare
myname varchar2(20);
myage number(11);
myid number(11);
begin
select id,name,age into myid,myname,myage from inf where id=2;
dbms_output.put_line('编号:'||myid);
dbms_output.put_line('姓名:'||myname);
dbms_output.put_line('年龄:'||myage);
end;
--------------------------------
declare
myname inf.name%type; //inf表name字段的类型
myage inf.age%type;
myid inf.id%type;
begin
select id,name,age into myid,myname,myage from inf where id=3;
dbms_output.put_line('编号:'||myid);
dbms_output.put_line('姓名:'||myname);
dbms_output.put_line('年龄:'||myage);
end;
---------------------------------------------------------------------------
存储过程是提前创建并且编译好的sql语句集,它存储在数据库中,如果要执行这些代码,只需要调用存储过程即可
传统方式:
1、先在程序中编写好SQL语句
2、把语句传输到数据库系统中
3、数据库系统编译这些sql语句
4、数据库系统执行这些SQL语句
创建存储过程:
1、编写语句
2、编译这些sql语句
3、存储起来
使用这些存储过程:
1、调用已经编译好的存储过程
--优点
1 速度更快
2 安全性更好
什么是存储过程?
答:提前编写并且编译好的存储在数据库中的语句块就称为 "存储过程"
-- procedure 过程
基本语法:
--不带参数的存储过程
create or replace procedure 存储过程名称
as
begin
语句块;
end;
--带参数的存储过程
create or replace procedure 存储过程名称(参数....)
as
begin
语句块;
end;
-------------------------------
存储过程参数的分类:
输入参数: 只能把外面的数据带入到存储过程里面 in
输出参数: 只能把存储过程得到数据,带到存储过程之外(专用于返回结果的参数) out
输入输出参数: 既能将参数带入到存储过程,也能将返回值带出去 in out
--带参数的存储过程
create or replace procedure my_pc(in_id in number,in_name in varchar2)
as
begin
dbms_output.put_line('编号是'||in_id);
dbms_output.put_line('姓名是'||in_name);
end;
begin
my_pc(in_name =>'aa',in_id => 12); ？
end;
create or replace procedure my_pc(out_name out varchar2,out_age out number)
as
begin
out_name:='bruceLee';
out_age:=12345;
end;
调用:
declare
myname varchar2(20);
myage number(11);
begin
--my_pc(out_name => myname,out_age => myage);
my_pc(myname,myage);
dbms_output.put_line('返回的结果值是:'||myname);
dbms_output.put_line('返回的结果值是:'||myage);
end;
create or replace procedure my_pc(in_out_id in out number,out_name out varchar2,out_tel out varchar2)
as
begin
select id,name,tel into in_out_id,out_name,out_tel from info3 where id=in_out_id;
end;
调用：
declare
id number(11);
name varchar2(20);
tel varchar2(20);
begin
id:=&请输入你要查询的编号;
my_pc(id,name,tel);
dbms_output.put_line('编号:'||id);
dbms_output.put_line('姓名:'||name);
dbms_output.put_line('电话:'||tel);
end;
call my_pc();
--
begin
my_pc()
end;
truncate table inf; 清空表格
select * from inf;
Oracle中的游标:它是用于指数据集中的某一行记录的一个标识符
Oracle中的游标分类:
1、静态游标 (声明游标时,必须指定,该游标指向的是哪一张表,指定以后就不允许再改变)
A、隐式游标 (由系统提供)
B、显示游标 (由用户创建)
2、动态游标 (声明游标时,不必指定指向的是哪一张表,在打开游开游标的时候再决定)
1 sql%found ---执行成功返回true,执行失败返回false
2 sql%notfound ---执行成功返回false,执行失败返回true
3 sql%rowcount ---执行语句以后,有几行语句操作成功
begin
update inf set name='abc' where id>2;
dbms_output.put_line('修改的行数是:'||sql%rowcount);
end;
begin
update inf set name='bruceLee' where id=5678;
if(sql%found)
then dbms_output.put_line('修改数据成功!');
else
dbms_output.put_line('修改数据成失败!');
end if;
end;
使用显示游标基本步骤:
--1 声明游标
--2 打开游标
--3 利用游标提取数据
--4 关闭游标
declare
cursor my_cur is select id,name,age from inf where id=3;
mid number;
mname varchar2(20);
mage number;
begin
open my_cur;
fetch my_cur into mid,mname,mage;
dbms_output.put_line('编号'||mid);
dbms_output.put_line('姓名'||mname);
dbms_output.put_line('年龄'||mage);
close my_cur;
end;
------------------------------------------------------------------
declare //读出一行的数据
cursor my_cur is select * from inf where id=5;
r inf%rowtype; --声明变量r,它的类型为inf表中的行的类型
begin
open my_cur;
fetch my_cur into r;
dbms_output.put_line('编号'||r.id);
dbms_output.put_line('姓名'||r.name);
dbms_output.put_line('年龄'||r.age);
close my_cur;
end;
----------------------------------------------------------------
declare //取得整个表的数据
cursor my_cur is select * from inf ;
r inf%rowtype; --声明变量r,它的类型为inf表中的行的类型
begin
open my_cur;
fetch my_cur into r;
while(my_cur%found)
loop
dbms_output.put_line(r.id||' '||r.name||' '||r.age);
fetch my_cur into r;
end loop;
close my_cur;
end;
--------------------------------------------------------------------
在声明的时候不需要关联到表,在打开的时候才指定,与哪一张表建立关联
---动态游标的语法:
declare
type my_cur is ref cursor; --声明一种动态游标类型,名称为 : my_cur
abc my_cur; --声明一个动态游标类型的变量,名称为: abc
r inf%rowtype; --声明一个变量r,它代表inf表中的一行数据
begin
open abc for select * from inf;
fetch abc into r;
while(abc%found)
loop
dbms_output.put_line(r.id||' '||r.name||' '||r.age);
fetch abc into r;
end loop;
close abc;
end;
--------------找出年龄大于指定值的行，放在储存过程里面---------------
create or replace procedure my_pc(in_age in number)
as
begin
declare
type my_cur is ref cursor; --声明一种动态游标类型,名称为 : my_cur
abc my_cur; --声明一个动态游标类型的变量,名称为: abc
r inf%rowtype; --声明一个变量r,它代表inf表中的一行数据
begin
open abc for select * from inf where age >in_age ;
fetch abc into r;
while(abc%found)
loop
dbms_output.put_line(r.id||' '||r.name||' '||r.age);
fetch abc into r;
end loop;
close abc;
end;
end;
---------
begin
my_pc(in_age => 29); 调用存储过程
end;
作用:创建程序包以后,可以把定义好的游标类型,放到程序包中,要使用这种游标的时候,只需要从程序包中取得该游标
类型即可使用
-----------------------------------
创建程序包的语法:
--创建一个程序包,程序包中包含一个动态游标
create or replace package my_pk
as
type r1 is ref cursor;
end;
----------------------------------------------------------------------
--创建一个存储过程,返回游标
create or replace procedure my_pc(out_cur out my_pk.r1)
as
begin
open out_cur for select * from info2;
end;
-----------------------------------------------------------
String sql = "{call my_pc(?)}";
cst = getConn().prepareCall(sql);
cst.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
cst.execute();
rs = (ResultSet) cst.getObject(1);
while(rs.next()){
String name = rs.getString("name");
什么是触发器？
答：触发器是数据库中的一个代码块，当满足一定条件时，触发器中的代码将会自动运行
触发器只能被系统调用，用户不能调用
触发器的类型：
1、行级触发器
2、表级触发器
3、视图触发器（替换触发器）
4、模式触发器
创建触发器的语法:
create or replace trigger 触发器名称
在什么时候做什么事情的时候
begin
要触发的代码;
end;
--如果有for each row就表示当前是一个行级触发器
-- :new :old
create or replace trigger tr_info3_insert
before insert on info3 for each row
begin
dbms_output.put_line('operation info3......');
end;
--------------------------
表级触发器 ,在表级触发器中 :new 与 :old 这两属性都不可用
create or replace trigger tr_info3_insert
before insert on info3
begin
dbms_output.put_line('operation info3......');
end;
--create or replace trigger tr_info3_insert 创建触发器
-- before insert on info3 对info3表进行添加数据的时候触发
在把数据添加到info3表之前，先触发，然后再向表中添加数据
------------部门表，新员工工作年限永远是0-----
create table empInfo
(
eid number(11) primary key,
ename varchar2(20),
workYears number(11) --如果是新员工，工作年限应该为0
);
create or replace trigger tr_insert_empInfo
before insert on empInfo for each row
begin
:new.workYears:=0; --自动将要赋给工作年限的值改为0
end;
insert into empInfo values(1,'chris',999);
update empInfo set workYears=10 where id=1
select * from empInfo;
------------主外键关联时，删除主键的值------------------
create table myprovince
(
pid number(11) primary key,
pname varchar2(20)
)
insert into myprovince values(1,'湖北省');
create table mycity
(
cid number(11) primary key,
cname varchar2(20),
pid number(11) references myprovince(pid)
)
insert into mycity values(1,'武汉市',1);
insert into mycity values(2,'襄阳市',1);
insert into mycity values(3,'宜昌市',1);
-----
两张表如果有主外键约束，如果外键表有数据，
主键表与之有关联的主键值不允许不删除
delete from myprovince where pid=1;
--如果要删除主键表的数据，就必须先把外键表与之有关联的数据先删除
--触发器可以在删除主键表中的数据之前，先删除外键表中有关联的数据
create or replace trigger tr_del_province
before delete on myprovince for each row
begin
delete from mycity where pid = :old.pid;
end;
--------------------------------
create or replace trigger tr_op_empInfo
before insert or update or delete on empInfo
begin
if(user!='FJ')
then
raise_application_error(-20001,'该用户无限执行这些操作');
end if;
end;
/*
错误编号必须在 -20001 ---------- -20999
*/
insert into empInfo values(1,'tomcat',23);
update empInfo set ename='abc' where eid=1;
delete from empInfo where eid=1;
--------------通过表级触发器，实现日志记录------------------------
create table empLog
(
eid number(11) primary key,--日志编号
ename varchar2(20),--操作者名称
opDate date,--操作时间
opDetails varchar2(200)--做了什么操作
)
create sequence emplog_seq;
select * from emplog;
---------
create or replace trigger tr_op_empInfo
before insert or update or delete on empinfo
begin
case
when inserting
then
insert into empLog values(emplog_seq.nextval,user,sysdate,'向empInfo表中添加了数据')
when updating
then
insert into empLog values(emplog_seq.nextval,user,sysdate,'向empInfo表中修改了数据');
when deleting
then
insert into empLog values(emplog_seq.nextval,user,sysdate,'向empInfo表中删除了数据');
end case;
end;
对schema这张表操作的时候触发
--创建对象，或者，删除对象的时候会触发
create table syslog
(
sid number(11) primary key,--系统日志编号
uname varchar2(20) ,--用户名
opTime date,--操作时间
objName varchar2(20),--创建或删除的对象名称
objType varchar2(20),--创建或删除的对象类型
opType varchar2(20)--做了什么操作：删除或创建
)
select * from syslog;
create sequence syslog_seq;
create or replace trigger tr_create_schema
after create on schema
begin
insert into syslog values(syslog_seq.nextval,user,sysdate,ora_dict_obj_name,ora_dict_obj_type,'创建');
end;
-----------
create or replace trigger tr_drop
after drop on schema
begin
insert into syslog values(syslog_seq.nextval,user,sysdate,ora_dict_obj_name,ora_dict_obj_type,'删除');
end;
-- ora_dict_obj_name 创建或删除的对象名称
-- ora_dict_obj_type 创建或删除的对象的类型
扫一扫
283
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
