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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
//输入数据库的用户名&密码
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2）查询表
SQL> select * from a_name;
ID NAME
---------- --------------------
CITY
--------------------------------------------------------------------------------
11 laoyang
beijing
1
2
3
4
5
6
22 zhang
yantai
3）插入&提交操作
//注意插入字符的表示，是单引号。
SQL> insert into a_name("ID", "NAME", "CITY") values(1, 'liu', 'dalian');
1 row created.
SQL> commit;
Commit complete.
1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
1
2
3
4
5
6
7
8
9
10
6）order by 排序
desc：降序
asc：升序
SQL> select * from a_name order by "ID" desc;
ID NAME
---------- --------------------
CITY
--------------------------------------------------------------------------------
22 zhang
yantai
11 laoyang
beijing
1
2
3
4
5
6
7
8
9
10
11
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
要求：
-计算每个班级的最高分数
排序语句：
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
1
2
3
4
5
6
7
8
9
10
11
12
13
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
参考:
http://www.live-in.org/archives/2135.html
