oracle学习——1

最新推荐文章于 2024-03-06 10:00:00 发布

开发转测试

最新推荐文章于 2024-03-06 10:00:00 发布

阅读量597

点赞数 2

分类专栏： javaSE

本文链接：https://blog.csdn.net/fulq1234/article/details/79760698

版权

javaSE 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

Oracle 下载地址

oracle 11.2.0.1.0 下载

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

hibernate-distribution-3.6.10.Final-dist.zip下载

Oracle安装:

https://jingyan.baidu.com/article/363872eccfb9266e4aa16f5d.html

oracle安装出错后,解决方案

https://blog.csdn.net/cleversihijun/article/details/51538303

如果是命令，末尾不用有分号，

如果是sql，末尾一定要有分号,

视频课程地址：https://www.imooc.com/learn/337

Oracle11g常用数据字典

Oracle数据字典的名称由前缀和后缀组成，使用_连接，含义说明如下：

　　dba_:包含数据库实例的所有对象信息

　　v$_:当前实例的动态视图，包含系统管理和系统优化等所使用的视图

　 user_:记录用户的对象信息

　　gv_：分布式环境下所有实例的动态视图，包括系统管理和系统优化使用的视图

　　all_:记录用户的对象信息机被授权访问的对象信息

基本数据字典

　　描述逻辑存储结构和物理存储结构的数据表，还包括描述其他数据对象信息的表：

数据字典名称	说明
dba_tablespaces	关于表空间的信息
dba_ts_quotas	所有用户表空间限额
dba_free_space	所有表空间中的自由分区
dba_segments	描述数据库中所有段的存储空间
dba_extents	数据库中所有分区的信息
dba_tables	数据库中所有数据表的描述
dba_tab_columns	所有表、视图以及簇的列
dba_views	数据库中所有视图的信息
dba_synonyms	关于同义词的信息
dba_sequences	所有用户序列信息
dba_constraints	所有用户表的约束信息
dba_indexs	数据表中所有索引的描述
dba_ind_columns	所有表及簇上压缩索引的列
dba_triggers	所有用户的触发器信息
dba_source	所有用户存储过程信息
dba_data_files	查询关于数据库文件的信息
dba_tab_grants/privs	查询关于对象授权的信息
dba_objects	数据库所有对象
dba_users	关于数据库中所有用户的信息

all_users

all_tables

常用动态性能视图

　　提供了关于内存和磁盘的运行情况，用户只能进行只读而不能修改它们

数据字典名称	说明
v$database	描述关于数据库的相关信息
v$datafile	数据库使用的数据文件信息
v$log	从控制文件中提取有关重做日志组的信息
v$logfile	有关实例重置日志组文件名及其位置的信息
v$archived_log	记录归档日志文件的基本信息
v$archived_dest	记录归档日志文件的路径信息
v$controlfile	描述控制文件的相关信息
v$instance	记录实例的基本信息
v$system_parameter	显示实例当前有效的参数信息
v$sga	显示实例的SGA区大小
v$sgastat	统计SGA使用情况的信息
v$parameter	记录初始化参数文件中所有项的值
v$lock	通过访问数据库会话，设置对象锁的所有信息
v$session	有个会话的信息
v$sql	记录SQL语句的详细信息
v$sqltext	记录SQL语句的语句信息
v$bgprocess	显示后台进程信息
v$process	当前进程的信息

第2章用户和表空间

2-1 使用系统用户登录Oracle

Oracle的用户有sys，system

系统用户

sys，system

sysman

scott 的默认密码是tiger

使用system用户登录

[username/password] [@server] [as sysdba|sysoper]

system/root@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 3月 30 14:54:12 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


#用户名/密码
请输入用户名:  system/123456

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> connect sys/123456 as sysdb
SP2-0306: 选项无效。
用法: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
其中 <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL> connect sys/123456 as sysdba;
已连接。

启用scott用户

alter user username account unlock

使用scott用户登录SQL PLUS

2-2 Oracle用户和表空间之查看登录用户

查看登录用户

show user命令

dba_users数据字典

SQL> show user;
USER 为 "SYS"

SQL> desc dba_users;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------------------

 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)
 PASSWORD_VERSIONS                                  VARCHAR2(8)
 EDITIONS_ENABLED                                   VARCHAR2(1)
 AUTHENTICATION_TYPE                                VARCHAR2(8)

#修改用户的默认表空间
SQL> select default_tablespace,temporary_tablespace from dba_users where usernam
e='SYSTEM';

DEFAULT_TABLESPACE
------------------------------------------------------------
TEMPORARY_TABLESPACE
------------------------------------------------------------
USERS
TEMP


SQL> ALTER USER system DEFAULT TABLESPACE system;

用户已更改。

SQL> select default_tablespace,temporary_tablespace from dba_users where usernam
e='SYSTEM';

DEFAULT_TABLESPACE
------------------------------------------------------------
TEMPORARY_TABLESPACE
------------------------------------------------------------
SYSTEM
TEMP



创建用户默认和临时表空间

#创建永久表空间
SQL> create tablespace test1_tablespace datafile 'test1file.dbf' size 10m;

表空间已创建。

#创建临时表空间
SQL> create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' s
ize 10m;

表空间已创建。

2-3 Oracle用户和表空间之启用scott用户

alter user username account unlock

SQL> alter user scott account unlock;

用户已更改。

如果忘记密码，修改密码

SQL> connect sys/123456 as sysdba;
已连接。
SQL> alter user scott identified by 123456 ;

用户已更改。

2-5 Oracle用户和表空间之表空间概述

表空间的分类

永久表空间

临时表空间

UNDO表空间

2-6 Oracle用户和表空间之查看用户表空间

dba_tablespaces user_tablespaces数据字典

注意scott 不能操作dba_tablespaces，权限不够

dba_users,user_users数据字典

设置用户的默认和临时表空间

ALTER USER username DEFAULT|TEMPORARY TABLESPACE tablespace_name

创建表空间

CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE xx.dbf SIZE xx

修改表空间的状态

设置联机或脱机状态

ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;

ALTER TABLESPACE tablespace_name READ ONLY|READ WRITE

设置用户的默认或者临时表空间

ALTER USER username DEFAULT|TEMPORARY TABLESPACE tablespace_name

2-8 Oracle用户和表空间之创建表空间

2-9 Oracle用户和表空间之修改表空间

添加数据文件

ALTER

SQL> desc dba_data_files;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPAC
E';

FILE_NAME
--------------------------------------------------------------------------------

D:\PROGRAM\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF



#修改表空间的状态
SQL> alter tablespace test1_tablespace offline;

表空间已更改。

SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLES
;

STATUS
------------------
OFFLINE

SQL> alter tablespace test1_tablespace online;

表空间已更改。

SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLES
;

STATUS
------------------
ONLINE



SQL> ALTER USER system DEFAULT TABLESPACE system;

用户已更改。

2-10 Oracle用户和表空间之修改数据文件

2-12 Oracle用户和表空间之删除表空间

第3章管理表

3-1 Oracle管理表之认识表

3-2 Oracle管理表之数据类型

3-4 Oracle管理表

3-5 Oracle管理表之修改表

3-7 Oracle管理表之删除表

使用CTL管理事务

(1) COMMIT: 提交事务，即永久保存事务中数据库的修改。

(2) ROLLBACK:回滚事务，即取消对数据库所做的任何修改。

(3) SAVEPOINT:在事务中创建存储点

(4) ROLLBACK TO <SavePoint_Name>:将事务回滚到存储点

问题1：何时开启事务

解答：在oracle中，事务在上一次事务结束以后，数据“第一次“被修改时开启。

问题2：何时结束事务

解答：有以下两种情况。

（1）数据被提交

发送COMMIT命令

执行DDL或DCL语句后，当前事务自动被提交

与Oracle分离，如退出PL/SQL Developer

（2）数据被撤销

发送ROLLBACK命令

服务器进程异常结束

DBA停止会话

SQL> show user
USER 为 "SYSTEM"
SQL> create table dept
  2  (
  3  deptno number(2) primary key,
  4  dname varchar2(14),
  5  ioc varchar2(13)
  6  );

表已创建。

SQL> insert into dept values(10,'accounting','new york');

已创建 1 行。

SQL> select * from dept;

    DEPTNO DNAME                        IOC
---------- ---------------------------- --------------------------
        10 accounting                   new york

SQL> rollback;

回退已完成。

SQL> select * from dept;

未选定行

SQL> insert into dept values(10,'accounting','new york');

已创建 1 行。

SQL> insert into dept values(20,'accounting','new york');

已创建 1 行。

SQL> insert into dept values(30,'accounting','new york');

已创建 1 行。

SQL> insert into dept values(40,'operations','new york');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from dept;

    DEPTNO DNAME                        IOC
---------- ---------------------------- --------------------------
        10 accounting                   new york
        20 accounting                   new york
        30 accounting                   new york
        40 operations                   new york

SQL> rollback;

回退已完成。

SQL> select * from dept;

    DEPTNO DNAME                        IOC
---------- ---------------------------- --------------------------
        10 accounting                   new york
        20 accounting                   new york
        30 accounting                   new york
        40 operations                   new york

SQL> insert into dept values(50,'a','new york');

已创建 1 行。

SQL> savepoint a;

保存点已创建。

SQL> insert into dept values(60,'b','new york');

已创建 1 行。

SQL> rollback to savepoint a;

回退已完成。

SQL> select * from dept;

    DEPTNO DNAME                        IOC
---------- ---------------------------- --------------------------
        10 accounting                   new york
        20 accounting                   new york
        30 accounting                   new york
        40 operations                   new york
        50 a                            new york

使用DCL控制权限

INSERT , SELECT, UPDATE

SQL> alter table dept add constraint fk_dept foreign key(deptno) references dept
(deptno);

表已更改。

SQL> desc dept;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 IOC                                                VARCHAR2(13)

SQL> alter table dept add(tel_no varchar2(12),address varchar2(20));

表已更改。

使用SQL函数操作数据表

连接操作符（||）用于将两个多多个字符串合并成为一个字符串。

1. TO_CHAR()

转换成字符串类型

2. TO_DATE()

转换成日期类型

3.TO_NUMBER()

转换成数值类型

SQL> select to_char(1234.5,'$99999.9') from dual;

TO_CHAR(1234.5,'$9
------------------
  $1234.5

SQL> select SYSDATE,to_char(SYSDATE,'YYYY"年"fmMM"月"fmDD"日"HH24:MI:SS') fro
ual;

SYSDATE        TO_CHAR(SYSDATE,'YYYY"年"FMMM"月"FMDD"日"HH24:MI:S
-------------- --------------------------------------------------
01-4月 -18     2018年4月01日10:33:30

SQL> select to_date('1980-01-01','yyyy-mm-dd') from dual;

TO_DATE('1980-
--------------
01-1月 -80

SQL> select to_number('100'),sqrt(to_number('100')) from dual;

TO_NUMBER('100') SQRT(TO_NUMBER('100'))
---------------- ----------------------
             100                     10

滤空函数

NVL(exp1,exp2)

NVL2(exp1,exp2,exp3)

SQL> select deptno,ioc,nvl(ioc,'空值') from dept where deptno > 30;

    DEPTNO IOC                        NVL(IOC,'空值')
---------- -------------------------- --------------------------
        40 new york                   new york
        50 new york                   new york
        51                            空值

SQL> select deptno,ioc,nvl2(ioc,'不为空','空值') from dept where deptno > 30;

    DEPTNO IOC                        NVL2(IOC,'不为空',
---------- -------------------------- ------------------
        40 new york                   不为空
        50 new york                   不为空
        51                            空值

DECODE(value,if1,then1,if2,then2,...else)

SQL> select deptno,ioc,decode(to_char(sysdate,'MM'),'01','一月'，'04','四月','其
它') from dept where deptno > 30;

    DEPTNO IOC                        DECODE(TO_CH
---------- -------------------------- ------------
        40 new york                   四月
        50 new york                   四月
        51                            四月

使用分析函数

ROW_NUMBER

row_number() over (partition by a order by b)是将表安装a字段进行分组，之后按照b字段进行组内排序，并给出排序的编号。

https://blog.csdn.net/u013125075/article/details/52303412

DENSE_RANK

RANK

https://blog.csdn.net/yangshangwei/article/details/53038325

同义词

synonym

https://blog.csdn.net/qq_34137397/article/details/55106760

当前身份是system

select username from sys.dba_users  --可以查出所有的schema
--scott用户下面有个表emp
create synonym m_emp for scott.emp;
select * from scott.emp;

scott.emp(scott:方案，emp:表)

删除同义词

SQL> drop synonym m_emp;

同义词已删除。

索引

索引提高搜索速度

参考视频网站： http://www.iqiyi.com/w_19rr790nsd.html

单列索引
create index name_index on customer(name); --给表customer的字段name创建索引

复合索引
create index emp_idx1 on emp(ename,job); --先搜索ename,再搜索job
create index emp_idx1 on emp(job,ename); --先搜索job,再搜索ename

select * from customer where name='sp' and catqart='aaa'; -- sql语句从后往前扫描，我们一下能把数据筛选成很少的，放到后面。

使用原则
1.在大表上面建立索引
2.在where子句或是连接条件上经常引用的列上建立索引
3.索引的层次不要超过4层

缺点
索引有一些先天不足：
1：建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引
2：更新数据的时候，系统必须要有额外的时间来同时对索引进行更新。以维持数据和索引的一致性。

B树索引
唯一索引和非唯一索引
反向键索引
位图索引
其它索引

B树索引建立在重复值很少的列上，而位图索引则建立在重复值很多，不同值相对固定的列上。

显示表的所有索引
select index_name,index_type from user_indexes where table_name='表名';
显示索引列
select table_name,column_name from user_ind_columns where index_name='IND_ENAME';

查看所有角色
select * from dba_roles;

授权系统权限
一般情况，授予系统权限是由dba完成，如果用其它用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限在授予系统权限时.

grant create session,create table to ken with admin option;
grant create view to ken

这里with admin option 是允许用户把权限分发，可以传递给别人

create session就是可以登录
回收系统权限
一般情况下，回收系统权限时dba来完成的，如果其他的用户来回收系统权限，要求该用户必须具有相应的系统权限及转授系统权限的选项（with admin option），回收系统权限使用revoke来完成。

从Oracle数据库中导入导出数据

1.使用exp导出数据
exp是Oracle提供的一个导出工具，它是操作系统下的一个可执行文件，存放目录为\ORACLE_HOME\BIN,ORACLE_HOME是Oracle的主目录。此处是D:\Program\Oracle\product\11.2.0\dbhome_1\BIN
2.使用imp导入数据

imp是Oracle提供的一个导入工具，

笔试题

1.表结构如下

create table exam(name varchar(32),subject varchar(32),score int);
insert into exam(name,subject,score) values('张三','语文',81);
insert into exam(name,subject,score) values('李四','数学',75);
insert into exam(name,subject,score) values('李四','语文',75);
insert into exam(name,subject,score) values('赵柳','英语',1);
insert into exam(name,subject,score) values('李四','英语',86);
insert into exam(name,subject,score) values('张思','英语',33);
insert into exam(name,subject,score) values('起点','英语',98);

问题：请用一条sql语句查询各科前三名

select * from (select row_number() over(partition by subject order by score
 desc) paiming,subject,name,score  from exam) t where paiming<=3 order by subjec
t asc,score desc;

结果

PAIMING SUBJECT   NAME   SCORE
---------- ----------------------------------------------------------------
1 数学 李四 75
1 英语 起点 98
2 英语 李四 86
3 英语 张思33
1 语文 张三 81
2 语文 李四 75
已选择6行。

开发转测试

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
oracle学习——1

Oracle 下载地址oracle 11.2.0.1.0 下载 http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html hibernate-distribution-3.6.10.Final-dist.zip下载 Oracle安装:https://jingyan.baidu.com/art...
复制链接

扫一扫