oracle学习——1

前端之家收集整理的这篇文章主要介绍了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/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用户syssystem

系统用户

syssystem

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,'new york');

已创建 1 行。

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

已创建 1 行。

sql> insert into dept values(30,'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,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,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,score) values('李四','数学',75);
insert into exam(name,score) values('赵柳','英语',1);
insert into exam(name,86);
insert into exam(name,score) values('张思',33);
insert into exam(name,score) values('起点',98);

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

select * from (select row_number() over(partition by subject order by score
 desc) paiming,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行。

猜你在找的Oracle相关文章