oracle基础实战笔记-阿里云开发者社区

本文包含以下内容：

1）Centos oracle 11g express安装与使用；
2）oracle最基本的增insert into、删delete、改update set、查select、Merge、distinct、drop操作等。
3）视图作用；
4）Shell脚本批量执行.sql文件；

1.Linux oracle 安装——CentOS6.5安装Oracle Express Edition

Oracle Express Edition是给个人学习、熟悉Oracle的简化版。http://www.live-in.org/archives/2088.html

PS：也有windows平台的Oracle Express Edition。

可以结合：sqldeveloper.exe 可视化工具查看表。

2.注意事项：

1）访问地址举例如：http://100.1.1.31:8081/apex/f?p=4950:6:1519924674005718::NO

Linux查看oracle版本：

[root@WEB-W031 xe]# pwd
/u01/app/oracle/product/11.2.0/xe

3.centos 下操作：

0）连接

//切换到oracle用户

[root@WEB-W031 dbs]# su oracle

//切换到sqlplus 操作

bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 14 15:23:37 2016

//输入数据库的用户名&密码
SQL> conn system/123456
Connected. （至此，连接成功）

此外使用：
sqlplus /nolog;
conn / as sysdba;
也有和sqlplus / as sysdba同样的效果。

1）查询dba用户名：

SQL> select username from dba_users;

USERNAME
------------------------------
SYS
SYSTEM
ANONYMOUS
APEX_PUBLIC_USER
APEX_040000
XS$NULL
OUTLN
FLOWS_FILES
MDSYS
CTXSYS
XDB

USERNAME
------------------------------
HR

2）查询表

SQL> select * from a_name;

ID NAME
---------- --------------------
CITY
--------------------------------------------------------------------------------
11 laoyang
beijing

22 zhang
yantai

3）插入&提交操作

//注意插入字符的表示，是单引号。

SQL> insert into a_name("ID", "NAME", "CITY") values(1, 'liu', 'dalian');



1 row created.
SQL> commit;


Commit complete.

4）更新表内容

SQL> update a_name set “CITY”=’shenzhen3’ where “ID”=1;

1 row updated.

SQL> select * from a_name;

ID NAME
---------- --------------------
CITY
--------------------------------------------------------------------------------
11 laoyang
beijing

22 zhang
yantai

1 liu
shenzhen3

5）删除表

SQL> delete from a_name where “ID”=1;
1 row deleted.

SQL> select * from a_name;
ID NAME
---------- --------------------
CITY
--------------------------------------------------------------------------------
11 laoyang
beijing

22 zhang
yantai

6）order by 排序

desc：降序
asc：升序

SQL> select * from a_name order by "ID" desc;

ID NAME
---------- --------------------
CITY
--------------------------------------------------------------------------------
22 zhang
yantai

11 laoyang
beijing

7）shell脚本执行oracle指令

应用场景：批量oracle操作，单个执行很容易敲错。
方法一：通过oracle SQL developer工具可视化执行。
方法二：通过shell脚本批量执行（如下）：

#!/bin/bash
#--确保只运行oracle用户运行
if [ `whoami` != 'oracle' ]
then
echo "Error: You must be oracle to execute."
exit 99
fi
# 获取ORACLE_SID
ORACLE_SID=$ORACLE_SID

# ORACLE_SID=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f1 -d':'`
export ORACLE_SID

# 获取ORACLE_HOME
# ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus system/123456 as sysdba

#连接
conn system/123456

#--spool 写入文件
#--spool /tmp/sql.txt;
#--执行sql语句
select * from a_name;

#--执行sql文件
@/home/laoyang/oracle_test/test.sql

#--spool off
exit

#创建表sql
CREATE TABLE
TEST_T
(
ID VARCHAR2(50),
NO VARCHAR2(50),
T_NAME VARCHAR2(50),
S_NAME VARCHAR2(50),
SCORE VARCHAR2(50)
);

insert into TEST_T VALUES(1, 1, 'Peter', '张三', 60);
insert into TEST_T VALUES(2, 2, 'Mary', '李四', 99);
insert into TEST_T VALUES(3, 3, 'Tom', '王五', 76);
insert into TEST_T VALUES(4, 1, 'Peter', '小A', 59);
insert into TEST_T VALUES(5, 2, 'Mary', '小B', 60);
insert into TEST_T VALUES(6, 3, 'Tom', '小C', 99);
insert into TEST_T VALUES(7, 1, 'Peter', 'Zhang', 70);
insert into TEST_T VALUES(8, 2, 'Mary', 'Wang', 88);
insert into TEST_T VALUES(9, 3, 'Tom', 'Li', 90);

要求：
-计算每个班级的最高分数
排序语句：
select no 班级， max(score) 分数 from A_TEST_T1 group by no;

排序结果：
1 70
3 99
2 99

8）关联子查询 in 示例

要求：查找表中分数最高的学生的名字和班级等全部信息。
查询语句：
select * from a_test_t1 where score in (select max(score) from a_test_t1);

9）关联子查询 exist 示例

要求：查找表中分数小于60的学生信息。
查询语句：
select * from a_test_t1 t1 where exists (select 1 from a_test_t1 t2 where t2.score < 60 and t1.id = t2.id);
与下面的指令等价：
select * from a_test_t1 where score in (select score from a_test_t1 where score < 60);

10) 查看表名（有哪些表）

select table_name from user_tables;

11）视图的作用：

1）简化查询的结构，把子查询定义成视图，在另一个查询里用
2）限制数据访问，屏蔽部分不愿意给客户看到的字段
3）视图建多了，如果原表的表结构改了，会增加维护工作
创建视图
create view view_test_t as select id AAAA, no BBBB, t_name CCCC, s_name DDDD, score EEEE from a_test_t1 where score > 60;

SQL> create or replace view view_test_t(序号, 学号, 教师名字, 学生名字, 分数) as select id, no, t_name, s_name, score from a_test_t1 where score > 60;
View created.

//修改视图，为视图添加列名。

SQL> select * from view_test_t;
序号
--------------------------------------------------
学号
--------------------------------------------------
教师名字
--------------------------------------------------
学生名字
--------------------------------------------------
分数
--------------------------------------------------
2
2

12）minus 去除交集后剩余部分。

如集合1{1,2,3}
集合2{2,3,4}
集合1 minus 集合2 = {1}；
结合2 minus 集合1 = {4}；

create table t111
(
id varchar2(2)
);

create table t222
(
id varchar2(2)
);

insert into t111 values ('1');
insert into t111 values ('2');
insert into t111 values ('3');

insert into t222 values ('2');
insert into t222 values ('3');
insert into t222 values ('4');

//返回1
(select * from t111)
MINUS
(select * from t222);

//返回4
(select * from t222)
MINUS
(select * from t111);

13）Merge合并操作

merge命令根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行update，无法匹配的执行insert。
重点：这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于insert+update。

//创建tb_emp1表
SQL> create table tb_emp1
2 (
3 empno number not null,
4 ename varchar2(100),
5 job varchar2(100)
6 );

Table created.

//创建tb_emp2表
SQL> create table tb_emp2
2 (
3 empno number not null,
4 ename varchar2(100),
5 job varchar2(100)
6 );
Table created.

//向tb_emp1 表中插入数据
SQL> insert into tb_emp1 values(11, 'yang', 'engineer');
1 row created.

//向tb_emp2 表中插入数据, empno 与表1刚插入的相同。
SQL> insert into tb_emp2 values(11, 'zhang', 'perfoessor');
1 row created.

//合并操作
SQL> merge into tb_emp1 t1
2 using tb_emp2 t2
3 on (t1.empno = t2.empno)
4 when matched then
5 update set t1.ename=t2.ename
6 when not matched then
7 insert(empno, ename, job) values (t2.empno, t2.ename, t2.job);
1 row merged.

//查询合并后结果，已经修改！


SQL> select * from tb_emp1;
EMPNO
----------
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
11
zhang
engineer

//原tb_emp2不变
SQL> select * from tb_emp2;
EMPNO
----------
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
11
zhang
perfoessor


##14）distinct使用
distinct作用：是去掉重复值，并将结果返回。


SQL> select distinct name from fruits;
NAME
----------

Oranges
Apple
Peach

//查询个数
SQL> select count(*) from fruits;

COUNT(*)
----------
10

//查询不重复的name的个数
SQL> select count(distinct(name)) from fruits;

COUNT(DISTINCT(NAME))
---------------------
3

15) 清除表内容&删除表

清除整个表的内容
delete from 表名;
truncate table 表名;

注意：delete和truncate的区别：
【1】delete删除的数据可以恢复，truncate不能恢复
【2】delete高水位线不会下降，truncate高水位线会下降（释放表空间）。
PS：高水位：详见：

删除表
drop table 表名;
oracle 10g开始，表删除后不是直接删除而是去了回收站。
删除回收站中指定表：purge table 表名;

清空回收站：purge recyclebin;

直接删除（不会进回收站直接删除）：drop table 表名 purge;

举例：
//清除表内容（注意：常犯错的语句写成：delete from fruits; 没有“”，属于select固定思维导致出错）。

SQL> delete from fruits;
10 rows deleted.

//查询表（表依然存在）
SQL> select * from fruits;
no rows selected

//删除表
SQL> drop table fruits;
Table dropped.

//表已不存在，会报错。
SQL> select * from fruits;
select * from fruits
*
ERROR at line 1:
ORA-00942: table or view does not exist

参考:

http://www.live-in.org/archives/2135.html

作者：铭毅天下
转载请标明出处，原文地址：http://blog.csdn.net/laoyang360/article/details/51189284

oracle基础实战笔记

本文包含以下内容：

1.Linux oracle 安装——CentOS6.5安装Oracle Express Edition

2.注意事项：

3.centos 下操作：

0）连接

1）查询dba用户名：

2）查询表

3）插入&提交操作

4）更新表内容

5）删除表

6）order by 排序

7）shell脚本执行oracle指令

8）关联子查询 in 示例

9）关联子查询 exist 示例

10) 查看表名（有哪些表）

11）视图的作用：

12）minus 去除交集后剩余部分。

13）Merge合并操作

15) 清除表内容&删除表

参考:

热门文章

最新文章

相关课程

相关电子书

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

oracle基础实战笔记

本文包含以下内容：

1.Linux oracle 安装——CentOS6.5安装Oracle Express Edition

2.注意事项：

3.centos 下操作：

0）连接

1）查询dba用户名：

2）查询表

3）插入&提交操作

4）更新表内容

5）删除表

6）order by 排序

7）shell脚本执行oracle指令

8）关联子查询 in 示例

9）关联子查询 exist 示例

10) 查看表名（有哪些表）

11）视图的作用：

12）minus 去除交集后剩余部分。

13）Merge合并操作

15) 清除表内容&删除表

参考:

热门文章

最新文章

相关课程

相关电子书

推荐镜像