Oracle数据库

一、Oracle基本语句

--查询所有表
SELECT * FROM USER_TABLES;
--按表名查找
SELECT table_name FROM USER_TABLES;
--创建表
CREATE TABLE student(
id integer PRIMARY KEY,
name varchar2(30),
birthday date
)
--修改列
ALTER TABLE student ADD sex INTEGER;
SELECT * FROM student;
--重命名列
ALTER TABLE student RENAME COLUMN sex TO sexs;
--删除列
ALTER TABLE student DROP COLUMN sexs; 

--创建自增序列
CREATE SEQUENCE st_id
INCREMENT BY 1 --设置步长
START WITH 1 --设置初始值
nomaxvalue --设置最大值
nocache --设置缓存,默认20
nocycle --设置循环;
--查看序列
SELECT st_id.nextval FROM dual;--dual

--插入数据
INSERT INTO student(id,name,birthday) VALUES(st_id.nextval,'张三',SYSDATE);
--修改数据
UPDATE student SET name='' WHERE id = 3;
--删除数据
DELETE FROM student WHERE id = 4;
--查询
SELECT * FROM student;


--创建表
CREATE TABLE clas(
id integer PRIMARY KEY,
cname varchar2(30)
)
--创建表关联
ALTER TABLE student ADD c_id INTEGER;
--添加外键
ALTER TABLE student ADD CONSTRAINT sk_st_cl FOREIGN KEY (c_id) REFERENCES clas(id)



SELECT * FROM student;
SELECT * FROM clas;

--创建视图
CREATE VIEW st_view 
AS
SELECT * FROM student;
--查询视图
SELECT * FROM st_view;
--修改视图数据
UPDATE st_view SET name='Kid' WHERE id = 3;
SELECT * FROM st_view;
SELECT * FROM student;

--Oracle数据库的伪列,分页,去重,比较数据的更新时间
SELECT rownum FROM student;--该列存在于表中,隐藏列
SELECT rowid FROM student;--非真实存在于表中

INSERT INTO clas(id,cname) VALUES(1,'爬树');
INSERT INTO clas(id,cname) VALUES(2,'拆家');
INSERT INTO clas(id,cname) VALUES(3,'睡觉');

--连接查询
--左外连接
SELECT * FROM student s LEFT JOIN clas c on c.id = s.c_id
SELECT * FROM STUDENT s,CLAS c WHERE s.C_ID=c.id(+)
--右外连接
select * from student s right join clas c on s.c_id = c.id
select * from student s,clas c where s.c_id(+) = c.id
--内连接
select * from student s inner join clas c on s.c_id = c.id
select * from student s,clas c where s.c_id = c.id

二、Oracle数据库视图

视图不属于真正的数据库表,对应数据库中的表的一部分或全部内容

在操作时可能会遇到用户权限不足的问题

grant create  view to scott;
grant select any table to scott;
grant select any dictionary to scott;
commit;

--视图
--创建一个视图
create view s_c_view 
as
select s.id sid,s.name,c.id cid,c.cname  from student s left join clas c on s.c_id = c.id
--查询
select * from st_view
select * from s_c_view
--修改视图数据
update st_view set c_id = 6 where id = 10
update s_c_view set name = '赵六' where sid = 12
--Oracle数据库的伪列,分页,去重,比较数据的更新时间
SELECT rownum FROM student;--该列存在于表中,隐藏列
SELECT rowid FROM student;--非真实存在于表中

三、Oracle数据库的常用函数

--字符串
--转小写
select lower('abcABC') from dual
--转大写
select upper('abcABC') from dual
--首字母大写 区域小写
select initcap('abcABC') from dual

--字符串连接
select concat('a','b') from dual

--截取字符串 三个参数 1、要截取的字符串 2、开始的下标 3、截取的位数
select substr('abcdegf',2) from dual

--查看字符串的长度 个数
select length('你好') from dual

--追加 将对应的字符串变为规定的长度 如果不足 左边添加 如果大于规定 截取
select lpad('ads',10,'') from dual select lpad('ads',10,'') from dual

--替换 将对应所有的查的的字符替换成对应的字符
select replace('adsads','d','') from dual

--数字的函数
--四舍五入 1、数据 2、小数点保留的位数
select round(34.89,-2) from dual
--截断
select trunc(34.892,1) from dual
--求余
select mod(-89776) from dual
--绝对值
select abs(-89776) from dual

--日期的函数 sysdate 表示当前时间
--在当前时间基础上添加一个月
select add_months(sysdate,-7) from dual
--查看当前日期的下个星期几是哪天
select next_day(sysdate,'星期一') from dual
--当前月的最后一天
select last_day(add_months(sysdate,-7)) from dual
--对日期进行四舍五入 yyyy 年 mm 月 dd天 hh 小时 mi秒
select round(sysdate,'hh') from dual
--直接+ 加对应的天
select sysdate+12323 from dual

--转换函数
--将日期转化为字符串
--将类型转化为日期to_date() 格式要写正确
select to_date('1996-08-23 22:34:22','yyyy-mm-dd hh24:mi:ss') from dual
--将类型转化为字符串to_char()
select substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),6) from dual
--将类型转化为数字to_number()
select to_number(substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),0,4))*2 from dual

四、Oracle数据库的自定义函数

--自定义函数

--定义函数
CREATE OR REPLACE FUNCTION sayhelo
--返回值类型
RETURN varchar2
IS
BEGIN
	RETURN 'hello word';
END
								--入参
CREATE OR REPLACE FUNCTION addid(vid integer)
--返回值
RETURN varchar2 
IS
BEGIN
	--字符串拼接使用||
	RETURN  'hello word'||vid;
end

--自定义带有参数返回的函数
--给定一个1-12数字  判断那个季节
create or replace function season
(moth in integer , sea out varchar2)
return varchar2
is
begin
  if moth>=1 and moth <=3 then
    return '冬季';
  elsif moth>=4 and moth <=6 then
    return '春季';
  else
    return '输入错误';
  end if;
end;
--带有返回值的函数的调用
declare
  name varchar(20);
begin
  dbms_output.put_line(season(7,name));
end; 

--出参和入参是同一个参数
create or replace function tonum
(v_a in out integer)
return integer
is
begin
  v_a := v_a*20;
  return v_a;
end;

--调用
declare
  v_v integer:=2;
begin
  dbms_output.put_line(tonum(v_v));
end; 

五、 Oracle数据库的存储过程

自定义函数和储存过程的区别:
1) 自定义函数必须有返回值,存储过程不需要
2) 自定义函数不可以写对数据库表决心操作,存储过程可以在里面定义CRUD的各种操作
3) 存储过程不需要在select 里面调用 都是直接调用的
4) 存储过程在java里面可以直接调用

优点:可以将复杂的sql 或者批量操作都放入到存储过程中 调用就立即执行,执行效率很高
缺点:不易维护 (修改、改变逻辑) 数据库迁移 移植性 没有

	--创建存储过程
create or replace procedure updateEmpById
(s_id in integer)
is
begin
  update emp set sal = sal + 100 where empno = s_id;
  commit;
end;
--根据部门deptno 来查询该部门的平均工资
--oracle 的存储过程 不能直接调用select  做查询    mysql的存储过程可以直接展示查询结果
create or replace procedure getAvgSalByDeptNo
(s_id in integer,name out varchar2)
is
begin
  select ename into name from emp where empno = s_id;
  dbms_output.put_line(name);
end;
--调用存储过程
begin
  updateEmpById(7369);
end;
declare
 e_name varchar2(20);
begin
     getAvgSalByDeptNo(7369,e_name);
end;
--for 的用法
create or replace procedure sayEmpEnameByDeptno
(deptNo in integer)
is
begin
  for emp in ( select * from emp where deptno = deptNo ) loop
    dbms_output.put_line(emp.ename);
    end loop;
end;

五、 Oracle数据库的触发器

触发器可以在数据库发生改变时,自动运行,不能被调用,不能接受参数

检测表数据   增删改  
--创建一个记录表  用来记录相关的操作
create table st_log(
id integer primary key,
user_name varchar2(20),
CRUD_day date,
emmage varchar2(40)
)

alter table st_log add name varchar2(20)
create sequence log_id 
increment by 1   --步长
start with 1     --初始值
nomaxvalue       --最大值10 27
nocache          --默认有20个放入缓存
nocycle          --是否循环

--创建一个触发器 记录添加
create or replace trigger stadd
after insert on student
for each row
begin
   insert into st_log(id,crud_day,user_name,emmage,name) values(log_id.nextval,sysdate,user,'添加了一条数据',:new.name);
   commit;
end;

--创建一个触发器  修改记录
create or replace trigger stupdate
after update on student
for each row
begin
   insert into st_log(id,crud_day,user_name,emmage,name) values(log_id.nextval,sysdate,user,'修改了一条数据',:old.name);
   commit;
end;
--创建一个触发器  删除记录
create or replace trigger stadelete
after delete on student
for each row
begin
   insert into st_log(id,crud_day,user_name,emmage,name) values(log_id.nextval,sysdate,user,'删除了一条数据',:old.name);
   commit;
end;

--查询语句
select  * from student
select * from st_log

insert into student (id,name,birthday) values(st_id.nextval,'张三1',sysdate);
insert into student (id,name,birthday) values(st_id.nextval,'李四1',sysdate);

update student set name = '赵七' where id = 4;

delete from student where id = 6;

jingsongchan

发表评论

电子邮件地址不会被公开。 必填项已用*标注