SQL课程大纲
•如何进入Sqlplus
•建立/修改Table
•INSERT 指令介紹
• UPDATE 指令介紹
• DELETE 指令介紹
• SELECT指令介紹
• WHERE Function 介绍
• Group Function介绍
• Oracle Function 介绍
• LOAD,UNLOAD 指令介紹
• Sqlplus内的一些命令介绍
进入ISQL
• sqlplus
1.常规登陆 sqlplus
SQL*Plus: Release - Production on 星期三 9月 17 09:57:02 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
2.快捷登陆 sqlplus username/passwd
username 登陆用户名
passwd 登陆用户密码
SQL*Plus: Release - Production on 星期三 9月 17 09:59:18 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production
建立/修改Table
• 数据类型:
VARCHAR2(size) 字符类型 (变长) 例 VARCHAR2(10)
CHAR[(size)] 字符类型 (定长) 例 CHAR (10)
NUMBER[(p,s)] 数值类型
例: NUMBER(5) 表示5位整数
例: NUMBER(15,3) 表11位整数, 3位小数
DATE 日期时间类型
LONG 变长字符类型,最大长度2G
CLOB 字符类型，最大长度4G
BLOB 二进制类型，最大长度4G
ROWID 16进制字符串，代表在表中的一个行的唯一地址
建立/更改Table
1.写好create table 的sql再执行
vi
/*
=========================================================
档案代号:dpe_file
档案名称:体检项目代号资料档
==========.===================.===========================
*/
create table dpe_file
(
dpe01 varchar2(6), /*体检项目代号 */
dpe02 varchar2(30), /*说明 */
dpe03 varchar2(01), /*No use */
dpeacti varchar2(01), /*资料有效码 */
dpeuser varchar2(10), /*资料所有者 */
dpegrup varchar2(06), /*资料所有部门 */
dpemodu varchar2(10), /*资料修改者 */
dpedate date /*最近修改日 */
);
create unique index dpe_01 on dpe_file (dpe01);
建立/更改Table
2.在 unix 环境下 sqlplus ds/ds < 即可create table 了
</u1/topo/apy/sql> sqlplus ds/ds <
SQL*Plus: Release - Production on 星期三 9月 17 10:37:23 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production
DOC>============================================================
DOC>档案代号:dpe_file
DOC>档案名称:体检项目代号资料档
DOC>==========.===================.==============================
DOC>*/
Table created.
Index created.
SQL>
建立/更改Table
3.在 sqlplus环境下建立 table
SQL> create table dpe_file
2 (
3 dpe01 varchar2(6), /*体检项目代号 */
4 dpe02 varchar2(30), /*说明 */
5 dpe03 varchar2(01), /*No use */
6 dpeacti varchar2(01), /*资料有效码 */
7 dpeuser varchar2(10), /*资料所有者 */
8 dpegrup varchar2(06), /*资料所有部门 */
9 dpemodu varchar2(10), /*资料修改者 */
10 dpedate date /*最近修改日 */
11 );
Table created.
SQL> create unique index dpe_01 on dpe_file (dpe01);
Index created.
SQL>
建立/更改Table
SQL> desc dpe_file;
Name Null? Type
----------------------------------------- -------- ----------------------------
DPE01 VARCHAR2(6)
DPE02 VARCHAR2(30)
DPE03 VARCHAR2(1)
DPEACTI VARCHAR2(1)
DPEUSER VARCHAR2(10)
DPEGRUP VARCHAR2(6)
DPEMODU VARCHAR2(10)
DPEDATE DATE
SQL>
建立/修改Table
• 修改table
1.写成sql 更改
SQL> ed
alter table dpe_file modify (dpe01 varchar2(10)); /*修改dpe01*/
alter table dpe_file add (dpe00 varchar2(10)); /*增加dep00*/
alter table dpe_file drop (dpe03); /*删除dpe03*/
drop index dpe_01; /*删除索引*/
SQL> @
Table altered.
Table altered.
Table altered.
Index dropped.
建立/修改Table
2.进入sqlplus 更改
SQL> alter table dpe_file modify (dpe01 varchar2(10));
Table altered.
SQL> alter table dpe_file add (dpe00 varchar2(10));
Table altered.
SQL> alter table dpe_file drop (dpe03);
Table altered.
SQL> drop index dpe_01;
Index dropped.
SQL>
建立/修改Table
SQL> desc dpe_file;
Name Null? Type
----------------------------------------- -------- ----------------------------
DPE01 VARCHAR2(10)
DPE02 VARCHAR2(30)
DPEACTI VARCHAR2(1)
DPEUSER VARCHAR2(10)
DPEGRUP VARCHAR2(6)
DPEMODU VARCHAR2(10)
DPEDATE DATE
DPE00 VARCHAR2(10)
INSERT 指令介绍
• INSERT INTO table_name[(column-list)]
VALUES(value-list)
范例:
1. INSERT INTO dpe_file VALUES('A00003','test','','Y','carrier','1400','','03/09/17
‘)
2. INSERT INTO cus_file(cus01,cus02)
VALUES(‘C00002’, ‘ test01’);
UPDATE 指令介绍
• Syntax
UPDATE table_name
SET col=expr,col=expr
[WHERE clause ]
范列說明:
dpe_file SET dpe02=‘test02’
WHERE dpe01 LIKE ‘A%1’
2. UPDATE dpe_file SET dpe02='test02',
dpeacti='N‘
WHERE dpe01 like 'A%1'
3. UPDATE dpe_file SET dpeuser='michael‘,
dpegrup='2100‘
WHERE (dpe01 = 'A00002' or dpe01 like 'C_000_');
DELETE 指令介绍
• Syntax:
DELETE FROM table_name
[WHERE clause]
范例说明:
FROM dpe_file
[注意]:没有where 条件会将所有资料删除
且无法将资料还原
FROM dpe_file
WHERE dpe01 LIKE ‘C%’
SELECT指令介绍
• Syntax:
• SELECT column,group_function(column)
FROM table_name
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column]
范例说明:
* FROM cus_file order by cus01 ASC
cus01,cus02 FROM cus_file order by cus01 DESC,cus02
last_name,salary,12*salary+100 FROM employee
last_name As name,commission comm FROM employee
last_name||job_id as “Employee Info” FROM employee
last_name || ‘ is a ’||job_id as “Employee Detail” FROM employee
distinct dpe02 FROM dpe_file
8. SELECT last_name,age FROM employee WHERE age<=45
SELECT指令介绍
• 范例说明:
* FROM employee WHERE age between 20 and 65
SELECT * FROM dpe_file
WHERE dpe02 is null and (dpeacti=‘Y’ or dpeuser like ‘carr%’)
employee_id,UPPER(last_name) FROM employee
WHERE INITCAP(last_name) = ‘Higgins’
SELECT employee_id,CONCAT(first_name,last_name) NAME ,
job_id,LENGTH(last_name), INSTR(last-name,’a’) “Contain ‘a’?”
FROM employee WHERE SUBSTR(job_id,4)=“REP”
SELECT last_name,salary,MOD(salary,5000) FROM employee
WHERE job_id=‘SA_REP’
Join table 範例:
oea01,oeb02,oeb04,oeb12 FROM oea_file,oeb_file
WHERE oea01=oeb01
_id,_name,_id,_id,
_id
FROM employee e ,department d
WHERE _id=_id
SELECT指令介紹
• 范例說明:
13. SELECT _name,_id,_name
FROM employee e ,department d
WHERE _id(+)=_id
SELECT _name,_id,_name
FROM employee e ,department d
WHERE _id=_id(+)
INTO cus1_file SELECT * FROM cus_file
INTO cua_file(cua01,cua02)
SELECT cus01,cus02 FROM cus_file
WHERE cus01 MATCHES ‘C*’
COUNT(*) FROM oea_file
WHERE oea02 BETWEEN ’01/01/01’ AND ’01/12/31’
COUNT(DISTINCT oea03) FROM oea_file
WHERE oea02 BETWEEN ’01/01/01’ AND ’01/12/31’
MAX(oea02),MIN(oea02) FROM oea_file
WHERE oea02 BETWEEN ’01/01/01’ AND ’01/12/31’
SELECT指令介紹
• 范例說明:
19. SELECT AVG(oeb12) FROM oea_file,oeb_file
WHERE oea02 BETWEEN '010101' AND '011231'
AND oea01=oeb01 AND oeb04='11-03-SDD16‘ and oeb12<>0
* FROM oea_file
WHERE oea02 = (SELECT MAX(oea02) FROM oea_file
WHERE oea02 > = ’01/01/01’)
department_id,AVG(salary) FROM employee
GROUP BY department_id
department_id,MAX(salary) FROM employee
GROUP BY department_id
HAVING MAX(salary)>10000
SELECT指令介紹----where
• 比较符号
‘=’ ‘ >’ ‘ >= ’ ‘< ’ ‘<=’ ‘<>’
• 其它比较符号
BETWEEN .. AND .. WHERE age between 20 and 30
IN (..) WHERE age in (20,21,22,23,24,25,26,27,28,29,30)
LIKE WHERE dpe01 like ‘A_0%’
IS NULL WHERE age is null
SELECT指令介紹----where
• 逻辑符号
‘AND’ ‘OR’ ‘NOT’
where dpe01=‘A00001’ AND dpe02=‘test01’
where dpe01=‘A00001’ OR dpe01=‘C00001’
where age not in (20,21,22,23,24,25,26,27,28,29,30)
Group Function
• AVG
• COUNT
• MAX
• MIN
• STDDEV
• SUM
Oracle Function---1
• Case-manipulation
functions
lower,upper,initcap
• Character-manipulation
functions
concat,substr,length,
instr,lpad,rpad,trim
Function Result
LOWER(‘SQL Course’) sql course
UPPER(‘SQL Course’) SQL COURSE
INITCAP(‘SQL Course’) Sql Course
CONCAT(‘Hello’,’World’) HelloWorld
SUBSTR(‘Helloworld’,1,5) Hello
LENGTH(‘HelloWorld’) 10
INSTR(‘HelloWorld’,W) 6
LPAD(salary,10,’*’) *****24000
RPAD(salary,10,’*’) 24000*****
TRIM(‘H’ FROM
‘HelloWorld)
elloWorld
FUNCTION---2
• Number Function
ROUND
round(,2)
TRUNC
trunc(,2)
MOD
mod(1600,300) 100
LOAD/UNLOAD指令介绍
• 因为oracle没有提供类似INFORMIX的load,unload语句，所以在
tiptop环境下有一些工具
1.使用load shell,load shell会去呼叫$TOP/ora/ 程序
使用方式
Usage: load database tablename txtfile
Ex : load ds ze_file
2.使用loaddb shell,loaddb shell会去呼叫external tools SQL*Loader
使用方式
Usage: /u1/topo/ora/bin/loaddb dbname [tablename] [txtfile]
Ex1 : /u1/topo/ora/bin/loaddb ds1
Ex2 : /u1/topo/ora/bin/loaddb ds1 ima_file []
Ex3 : /u1/topo/ora/bin/loaddb ds1 i%_file
3.直接使用Oracle提供的SQL*Loader,但必须自己编辑control file,
用法
sqlldr ds/ds control= log=
LOAD/UNLOAD指令介绍
control file格式如下
load data
infile '/u1/tiptopo/ '
append into table azb_file
fields terminated by '^A'
(
azb01,
azb02,
azb06,
azbacti,
azbuser,
azbgrup,
azbmodu,
azbdate
)
4.另外可以在BDL程序中使用channel的方式来产生SQL*Loader 所需用的control file,
再透过RUN command的作法来激活SQL*Loader,channel的写法可参考BDL文件
Sqlplus 一些命令介绍
• Help index 查询相关命令
• @ 运行.sql的文件
• Ed 类似于vi
• Get 将一句sql语句放入sql 缓冲器
• R –run
• / 重复上一个在memory中的command
• Desc 显示表结构
• Spool 记录执行过程
• Start get+r
• Save 将memory中的命令保存在一个文件中
• Set 设置一些oracle设定
Sqlplus 一些命令介绍
示范
1. help accept 找寻accept的帮助信息
2. @ 执行中的内容
3. ed 编辑一个文件
4. get 将该文件的内容放入memoery 中
5. r 执行memory中的命令
6. / 同r
7. desc dpe_file 查看dpe_file的表结构
8. spool 将在这期间执行的命令及结果放入 文件
9. sqlplus 命令
10. spool off
9. Start 执行start后面文件中的内容
10. save
11. set wrap on 设置可以折行显示
12. Set heading off
新增 表格
• 先寫sql
• 然後進入路徑下sqlplus avydg/avydg @表名
• 更新sechma 進入路徑sqlplus ds/ds @表名
• 查詢索引@q_index
