oracle 数据库的基本命令

最新推荐文章于 2024-09-27 13:27:26 发布

polestar.wang

最新推荐文章于 2024-09-27 13:27:26 发布

阅读量582

点赞数 1

分类专栏： Sql 文章标签： oracle

本文链接：https://blog.csdn.net/wangdingfeng5141/article/details/52325605

版权

Sql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、Oracle的基本命令DDL：

数据库模式定义语言，关键字：create
DML：数据操纵语言，关键字：Insert、delete、update
DCL：数据库控制语言，关键字：grant、remove
DQL：数据库查询语言，关键字：select

//在cmd命令提示符下启动sqlplus登录Oracle

sqlplus 用户名/密码 //例如 sqlplus scott/tiger

//解锁用户，需要管理员权限的用户才可以，进入system或sys

SQL>alter user scott account unlock;

//从一个用户跳到scott用户下

conn scott/tiger

//当前oracle用户下所有表

select * from user_tables;

//当前oracle用户下所有表名

 select table_name from user_tables;

//或进入system用户下执行

select TABLE_NAME from dba_tables where owner='用户名大写'

//查看表结构
describe tablename;

//若在window窗口下sqlplus 中清屏命令：
host cls 或是clear screen 或只是4位clea scre

//查看当前登陆的用户名：
select user from dual;
//或
show user

--查询所有用户：
select * from all_users;
//sys或system下查询
select * from dba_users

--查看当前用户权限：
select * from session_privs;

select * from dept

对数据库的操作：增、删、改、查（select）

where 子句

select * from dept where deptno>20
select * from dept where deptno in(30,40);
select * from dept where deptno<>20;

实现思路：将主键设置为序列，自后每次按照固定规则增加相应的数字即可。
1、首先要有create sequence或者create any sequence权限，
create sequence emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10;

2、插入到表中，
INSERT INTO emp VALUES
(emp_sequence .nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);

SQL> create table sms_activity(
2 id number,
3 activity_name varchar2(50),
4 begin_time varchar2(30),
5 end_time varchar2(30),
6 content varchar2(600)
7 );

表已创建。

SQL> commit;

提交完成。

SQL> alter table sms_activity add primary key (id);

表已更改。

SQL> commit;

提交完成。

SQL> create sequence sms_activity_seq
2 minvalue 1
3 maxvalue 9999999999
4 increment by 1
5 cache 20
6 ;

序列已创建。

SQL> create or replace trigger bi_activity
2 before insert on sms_activity
3 for each row
4 begin
5 select sms_activity_seq.nextval into :NEW.ID from dual;
6 end;
7 /

触发器已创建

SQL> desc sms_activity
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER
ACTIVITY_NAME                                      VARCHAR2(50)
BEGIN_TIME                                         VARCHAR2(30)
END_TIME                                           VARCHAR2(30)
CONTENT                                            VARCHAR2(600)

SQL> alter table sms_activity add (create_time varchar2(30),createby varchar2(20),modify_time varchar2(30),modifyby varchar2(20));

表已更改。

SQL> commit;

二、oracle创建视图

在PL/SQL Developer数据库管理工具中，使用SCOTT用户帐号进行登录，新建SQL查询窗口，输入一条创建视图的SQL语句，随后弹出一个错误的提示信息：ORA-01031:权限不足。所以要先授权给用户

//授权给用户
用数据库的system用户给Scott赋权限

登陆system用户，执行grant create view to scott;

此时授权成功就可以用Scott用户创建视图了

********************************************用户的管理****************************************
//创建用户
CREATE USER jason IDENTIFIED BY price;
//指定默认表空间和临时表空间
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
//给用户jason授予CREATE SESSION的权利
GRANT CREATE SESSION TO jason;
//修改用户密码:修改用户jason密码为marcus
ALTER USER jason IDENTIFIED BY marcus;
//可以通过PASSWORD命令修改当前登录用户的密码
CONN jason/marcus
PASSWORD
********************************************权限和角色*********************
拥有相关权限可以运行用户在数据库中完成相关操作，如执行DDL语句。
权限可以组合在一起形成相关不同的角色。两个比较有用的角色是CONNECT和RESOURCE角色

************************************************************
CREATE OR REPLACE VIEW dept_sum_vw
(name,minsal,maxsal,avgsal)
AS
SELECT d.dname,min(e.sal),max(e.sal),avg(e.sal)
FROM    emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;

CREATE OR REPLACE VIEW dept_sum_vw
(name,minsal,maxsal,avgsal)
AS
SELECT d.dname,min(e.sal),max(e.sal),avg(e.sal)
FROM    emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;

视图的优点：
1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
2.用户通过简单的查询可以从复杂查询中得到结果。
3.维护数据的独立性，试图可从多个表检索数据。
4.对于相同的数据可产生不同的视图。

视图的分类：
视图分为简单视图和复杂视图。

两者区别如下：
1.简单视图只从单表里获取数据，复杂视图从多表获取数据；
2.简单视图不包含函数和数据组，复杂视图包含；
3.简单视图可以实现DML操作，复杂视图不可以。

************************************************************
//存储过程

create or replace procedure helloworld
as
begin
dbms_output.put_line('helloworld');
end;
/

set serveroutput on;
execute helloworld;

Oracle存储过程基本语法存储过程
　　1 CREATE OR REPLACE PROCEDURE 存储过程名
　　2 IS
　　3 BEGIN
　　4 NULL;
　　5 END;

行1:
　　CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个存储过程, 如果存在就覆盖它;
行2:
　　IS关键词表明后面将跟随一个PL/SQL体。关键字IS和AS均可，
行3:
　　BEGIN关键词表明PL/SQL体的开始。
行4:
　　NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句; 们本身没有区别。IS后面是一个完整的

PL/SQL块，可以定义局部变量，但不能以DECLARE开始。局部变量在过程内部存放值。
行5:
　　END关键词表明PL/SQL体的结束

带参存储过程（输入参数）
create or replace procedure helloTom (pname in varchar2)
as
begin
dbms_output.put_line('hello '|| pname ||'!');
end;
/
set serveroutput on;
exec helloTom('jerry');

/
带参存储过程（输出参数）
create or replace procedure writeTom(pname out varchar2)
as
begin
select loc into pname from dept where rownum=1;
end;
/

variable pname varchar2(40);
exec writeTom(:pname);

declare
cname varchar2(40);
begin
writeTom(cname);
dbms_output.put_line(cname);
end;
/

//java代码
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection(url, "scott", "tiger");
//创建存储过程的对象
CallableStatement c=conn.prepareCall("{call writeTom(?)}");

//给存储过程的第一个参数设置值
c.registerOutParameter(1, Types.VARCHAR);

//执行存储过程
c.execute();

//得到存储过程的输出参数值
System.out.println (c.getString(1));

/
带参存储过程（in out 两个参数）
//创建存储过程InoutTom向查询dno数据的dname
create or replace procedure inoutTom(dno in number,info out varchar2)
as
begin
select dname || '_' || loc into info from dept where deptno=dno;
end;
/

Java代码

Class.forName("oracle.jdbc.driver.OracleDriver");
			conn=DriverManager.getConnection(url, "scott", "tiger");
			c=conn.prepareCall("{call OUTINFO(?,?)}");
			c.setInt(1, 20);
			c.registerOutParameter(2, Types.VARCHAR);
			c.execute();
			System.out.println(c.getString(2));

形式参数可以有三种模式：IN、OUT、INOUT。如果没有为形式参数指定模式，那么默认的模式是IN。
　　IN表示输入参数
　　OUT表示输出参数
模式描述IN参数（默认模式）（输入参数）用来从调用环境中向存储过程传递值，不能给IN参数赋值，给此参数传递的值可以是常

量、有值的变量、表达式等。
　　OUT参数（输出参数）用来从过程中返回值给调用者，不能将此参数的值赋给另一个变量，不能是常量或表达式。在过程体内，

必须给OUT参数赋值。INOUT参数（输入输出参数）既可以从调用者向过程中传递值，执行过程后还可返回可能改变了的值给调用者

。

IN参数（默认模式）（输入参数）:用来从调用环境中向存储过程传递值，不能给IN参数赋值，给此参数传递的值可以是常量、有值

的变量、表达式等。
　　
OUT参数（输出参数）:用来从过程中返回值给调用者，不能将此参数的值赋给另一个变量，不能是常量或表达式。在过程体内，必

须给OUT参数赋值。

INOUT参数（输入输出参数）:既可以从调用者向过程中传递值，执行过程后还可返回可能改变了的值给调用者。

、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、另外的例子
CREATE OR REPLACE PROCEDURE stu_proc(v_name OUT VARCHAR2) AS
BEGIN
SELECT o.dname INTO v_name FROM dept o where o.deptno = 20;
END;