SQL*PLUS

1

• SQLPLUS

2
???SQLPLUS

• SQLPLUS?ORACLE?????????,????????????ORACLE???????
  ???,?????????????????????????????????SQL??????
• ???????SQLPLUS???????????,????,????????????????,?
  ??????????,???????SQLPLUS??????

3
SQLPLUS???

• ???SQLPLUS?,??????,????????????,????????????
• Sqlplus username/password
• ???????,SQLPLUS????????????? SQLgt

4
SQLPLUS????(?)

• L ??????????SQL??
• Ln ????????n????
• C/old/new ??????,?old??new
• A text ????????????
• delete ???????????
• del ?????????
• I ????????
• / or UN ????????

5
SQLPLUS????(?)
Save filename ?????????? filename.sql?? Get
filename ?filename.sql????? ?????? Start
filename ??filename? _at_filename ??filename?
6
Example CREATE TABLE
Create table department (dept_id number(2)
primary key, dept_name varchar2(15)) Create
table employee (emp_id number(4) primary key,
emp_name varchar2(15), emp_phone number(10),
emp_salary number(10), fk_dept number(2)
references department)
7
Example DESCRIBE
Display the structure of a table. SQLgtDescribe
department SQLgtDescribe employee
Name Null? Type -------------------
-------------- ----------------- dept_id NOT
NULL NUMBER(2) dept_name NULL
VARCHAR2(15)
Name Null? Type -------------------
-------------- ----------------- emp_id NOT
NULL NUMBER(4) emp_name NULL
VARCHAR2(15) emp_phone NULL
NUMBER(10) emp_salary NULL
NUMBER(10) fk_dept NOT NULL NUMBER(2)
8
Example INSERT
SQLgtInsert into department values(11,
Finance) SQLgtInsert into department valu
es(12, MIS) SQLgtInsert into
department values(13, Administration)
9
Example INSERT
SQLgtInsert into employee values(1111,
David, 25925252,10000, 11 ) SQLgtInsert into
employee values(2222, Peter, 12345678,11000,
12) SQLgtInsert into employee values(3333,
Jack, 28825252,12000, 13) SQLgtInsert into
employee values(4444, Peter,
28825252,11000, 13)
10
SQL- Select
SQLgt Select from employee 2
?????? SQLgtl 1 select from
employee SQLgtc/employee/department 1 select
from department SQLgtl 1 select from department
11
SQL-Select
Select columnname1,columnname2, from tablename1,
tablename2,.. Where condition_acondition_b group
by columnname1 having functionname(columnname1)gtco
ndition order by columnname1
12
SQL-Select
SELECT?? ?SELECT????????????SELECT,??????????????
???,?????(,)?? FROM?? ??????????????????????? WHER
E?? ????????????????,????????,select????????????
13
SQL-Select
GROUP BY?? ????????????? HAVING?? HAVING???WHERE?
?????,??????????????????????,GROUP
BY????????????,??????????????????????????,??WHERE?
??????? ORDER BY?? ???????????
14
Example
Select from department where dept_id11 Sele
ct from employee order by emp_name Select
from employee,department
15
Example
Select emp_id,emp_name,emp_phone, dept_name from
employee,department where dept_idfk_dept Selec
t emp_name from employee,department where
dept_idfk_dept group by emp_name Select
emp_name from employee,department where
dept_idfk_dept group by emp_name having
count()gt1
16
?????

• ??
• ! or lt gt ???
• gt ??
• lt ??
• gt ?????
• lt ?????
• in ??????????
• between ???????
• like ????????
• is null ??
• not ??????????

17
Set Operators

• UNION All rows selected by either query
• UNION ALL All rows selected by wither query,
  include all duplicates
• INTERSECT All distinct rows selected by both
  queries.
• MINUS All distinct rows selected by the first
  query but not in the second

18
SQL
Select from employee where emp_namePeter S
elect from employee where emp_name!Peter S
elect from employee where emp_namegtPeter Se
lect distinct emp_name from employee
19
SQL

• Select from employee where emp_nameltPeter
• Select from employee where emp_name
  in(Peter, Jack)
• Select from employee where emp_id between 1111
  and 3333

20
SQL
Select from department where dept_name like
A Select from department where dept_name
like n Select from department where
dept_name not like n
21
??????
???????????,??????????????????????,?????AND ?
OR??? Select from employee where
emp_namePeter and fk_dept13 Select from
employee where emp_namePeter or fk_dept13
22
?????
?SELECT???????????,??SQLPLUS?????????,?????????
Select from employee where fk_deptdept
23
?????????
???SELECT???,?????????????,SQLPLUS???????????????
???? Select emp_name, emp_salary12 from
employee ???????()????????? Select
dept_name.emp_name from department,
employee where dept_idfk_dept
24
??Subqueries

• Subqueries ???where???,???????????
• ??????,????????????,???????,??????,????????
• ??????SQL???
• Select dept_name from department where
  dept_id(select fk_dept from employee where
  emp_nameJack)
• Select dept_name from department, employee where
  dept_idfk_dept and emp_namejack

25
Subqueries???
??????????????? ?????????????dept_id,?????????????
?? ????????,?????12???,??????????,???????????
26
????????

• TAB ????????????VIEW,????????????????
• Select from TAB
• Select from DICTIONARY
• DESCRIBE user_objects
• Select distinct object_type from user_objects
• Select object_name from user_objects where
  object_typeTABLE

27
Section Objectives
? ? ? ? ? ? ? ? ? ? ? ?
28
? ? ? ?
Oracle ????????????? Table ????? View ???????????,
??????????????? Sequence ?????? Index ??? ????
????????????
29
? ? ? ?

• ????
• ??(column)??
• ???????????
• ?????
• ??(references) -- ??(Null)????
• (unique)
• ??????

30
? ? ? ? ? ? ?

• CREATE TABLE schema. table_name
• (column data type DEFAULT expr
• column_constraint,
• . . .
• )

31
? ? ? ? ? ? ?

• ????(table_name)
• ???(DEFAULT expr)
• ???????,?????????????????
• ????(column)

32
? ? ? ? ? ? ?

• ????(data type)
• ????????
• ?????(column_constraint)
• ???????,??????????????

33
? ? ? ? ? ? ?

• ???????????????
• ?????????????? A-Z, a-z, _, , and
• ??????????????????????
• ??????????? Oracle8 Server ????

34
? ? ? ?
35
? ?

• ???Oracle server??????
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

36
? ? ? ? ? ? ?

• ? ? ? ? ?
• column CONSTRAINT constraint_name
  constraint_type

37
? ? ? ? NOT NULL

• ??????????
• Example
• CREATE TABLE friend
• phone VARCHAR2(15) NOT NULL,
• last_name VARCHAR2(25)
• CONSTRAINT friend_last_name_nn NOT
• NULL,
• .

38
? ? ? ? UNIQUE

• ????????????????
• ??????
• ??????

phone VARCHAR2(10) CONSTRAINT
s_emp_phone_uk UNIQUE,
39
? ? ? ? UNIQUE PRIMARY KEY

• ???????????????????????
• ?????????????????????
• ?????????

id NUMBER(7) CONSTRAINT s_emp_id_pk PRIMARY
KEY,
40
? ? ? ? FOREIGN KEY

• ?????????????????
• ????????????????????????????
• ????????????????

dept_id NUMBER(7) CONSTRAINT
s_emp_dept_id_fk REFERENCES s_dept(id)
41
? ? ? ? FOREIGN KEY

• FOREIGN KEY
• ????????????
• REFERENCES
• ???????????

42
? ?
SQLgt CREATE TABLE s_dept (
id NUMBER(7) CONSTRAINT s_dept_id_pk PRIMARY
KEY name VARCHAR(25) CONSTRAINT
s_dept_name_nn NOT NULL region_id NUMBER(7)
CONSTRAINT s_dept_region_id_fk REFERENCES )
43
?????(Subquery)????
CREATE TABLE table column(, column ...) AS
subquery
44
?????(Subquery)????

• ??S_EMP???????????????????41?????
• ???????????????????

CREATE TABLE emp_41 AS SELECT id, last_name,
userid, start_date FROM WHERE dept_id 41
45
? ? ? ? ? ? ?

• ???????
• ??, ??, ??, ?????
• ????

46
? ?

• ALTER TABLE
• ???????
• ???????
• DROP TABLE
• ??????
• RENAME, TRUNCATE, COMMENT
• Alter table emp add(plan number(7,2)
• Alter table emp modify(plan number(9,2))

47
? ? ? ? ? ? ?

• ????????
• ????????

ALTER TABLE table ADD (column datatype DEFAULT
exprNOT NULL , column datatype . . . )
48
? ? ? ? ? ? ?

• ???? COMMENTS??? S_REGION ??

SQLgt ALTER TABLE s_region ADD
(comments VARCHAR2(255)) 1 Table altered
49
? ? ? ? ? ? ?

• ?????????(data type), ??(size), ???(default
  value)????(NOT NULL)????

ALTER TABLE table MODIFY(column data type
DEFAULT expr NOT NULL,
column data type . . .)
50
? ? ? ?

• ? ?
• ???S_EMP????TITLE?????50???????

SQLgt ALTER TABLE s_emp MODIFY (title
VARCHAR2(50)) 1 Table altered.
51
? ? ? ? ? ? ? ? ?

• ??(add)???(drop)(??????)????
• ??(Enable)???(disable)????
• ??MODIFY ??????????

SQLgt ALTER TABLE table ADD CONSTRAINT
constraint type (column) 1 Table altered.
52
? ? ? ? ? ? ? ? ?

• ???S_EMP??????????(??????manager?????????)

SQLgt ALTER TABLE s_emp ADD CONSTRAINT
s_emp_manager_id_fk FOREIGN KEY
(manager_id) 1 Table altered.
53
Constraint

• NOT NULL
• Alter table emp MODIFY(sal number constraint
  nnsal NOT NULL)
• UNIQUE, PRIMARY KEY
• Create table dept (deptno number(2) constraint
  pk_dept PRIMARY KEY, dname varchar2(9)
  constraint unq_name UNIQUE, loc varchar2(10))

54
Integrity Constraint

• FOREIGN KEY REFERENCE
• Create table emp (emp_no number(4), e_name
  varchar2(10), ..deptno constraint fk_deptno
  foreign key(deptno) references dept(deptno))
• ON DELETE CASCADE
• delete of referenced key values in the parent
  table and automatically deletes dependent rows in
  the child table to maintain referential
  integrity.
• CHECK
• Create table dept(deptno number constraint
  check_deptno check(deptno between 10 and 99)
  dept_name varchar2(9))

55
? ? ? ? ? ? ?

• ???S_EMP????manager?????

SQLgt ALTER TABLE s_emp DROP CONSTRAINT
s_emp_manager_id_fk Table altered.
56
? ? ? ? ? ? ?

• ????S_DEPT??????

SQLgt ALTER TABLE s_dept DROP PRIMARY
KEY CASCADE Table altered.
57
? ? ? ? ?

• ?ALTER TABLE ???DISABLE ??????????????
• ?? CASCADE ???????????????????????????

SQLgt ALTER TABLE s_emp DISABLE
CONSTRAINT s_emp_id_pk CASCADE Table altered.
58
? ? ? ?

• ??????????, ???ENABLE ???????????
• UNIQUE ? PRIMARY KEY ???????????????

SQLgt ALTER TABLE s_emp ENABLE
CONSTRAINT s_emp_id_pk Table altered.
59
? ? ? ? ? ? ?

• ?????????
• ????????????
• ????????????
• CASCADE CONSTRAINTS ????????????????
• ???????? roll back ?????

DROP TABLE table CASCADE CONSTRAINT
60
? ? ? ? ? ?
?? RENAME ????????????, ????
SQLgt RENAME s_ord TO s_order Table renamed.
61
? ? ? ? ? ? ?
TRUNCATE ?? ?????????? ???????????? ??TRUNCATE
???????????? roll back ????? ??????DELETE??????
SQLgt TRUNCATE s_item Table truncated.
62
Alter table

• Alter table dept drop primary key cascade
• If you omit cascade, oracle does not drop the
  unique or primary key constraint if any foreign
  key references it.
• Alter table dept drop constraint pk_dept cascade
• Alter table dept drop unique(dname)

63
Alter table

• You can rename a column using the CREATE TABLE
  command with the As clause.
• Create table temp(newname, col2, col3) AS select
  oldname, col2, col3 from static
• Drop table static
• Rename temp to static

64
? ? ? ? ?

• ????????
• ????????
• ????????
• ? ? ? ? ? ?

65
? ? ? ? ?

• ???????? - INSERT.
• ????????- UPDATE.
• ???????? - DELETE

66
? ? ? ? ? ? ? ? ? ? ?

• ??INSERT???????????

INSERT INTO table(column , column
) VALUES (value , value . . .)
67
? ? ? ? ? ? ? ? ? ? ?
SQLgt INSERT INTO s_dept VALUES
(11, Finance, 2) 1 row created.
68
? ? ? ? ? ? ? ?

• ???????????
• ???????? NULL ???????()

SQLgtINSERT INTO s_dept (id, name)
VALUES (12, MIS) 1 row created.
SQLgtINSERT INTO s_dept (id, name)
VALUES (13, Administration, NULL) 1 row
created.
69
? ? ? ? ? ? ?

• USER ?????????????
• SYSDATE???????????????

SQLgtINSERT INTO s_emp (id, first_name,
last_name,
userid, salary, start_date)
VALUES (26, Donna, Smith, USER, NULL,
SYSDATE) 1 row
created.
70
???????????

• ? SQLPlus????????????????????

SQLgtINSERT INTO s_dept (id, name, region_id)
VALUES (department_id,
department_name,
region_id) Enter value for department_id
61 Enter value for department_name
Accounting Enter value for region_id 2 1 row
created.
71
? ? ? ? ? ? ? ? ?

• ?? INSERT ???????
• ????? VALUES ???

SQLgtINSERT INTO history(id, last_name, salary,
title, start_date) SELECT
id, last_name, salary, title, start_date
FROM s_emp WHERE
start_date lt 01-JAN-94 10 rows created.
72
? ? ? ? ? ? ?

• ??UPDATE??????????

UPDATE table SET column value
, column value WHERE condition
73
? ? ? ? ? ? ?
SQLgt UPDATE s_emp SET dept_id
10 WHERE ID 2 1 row updated.
SQLgt UPDATE s_emp SET dept_id
32, salary 2550 WHERE ID 1 1
row updated.
74
? ? ? ? ? ?

• ????????WHERE????, ?????????????

SQLgt UPDATE s_emp SET
commission_pct 10 25 row updated.
75
? ? ? ? ? ? ?

• ??DELETE???????????
• ? ?

DELETE FROM table WHERE
condition
SQLgt DELETE FROM s_emp WHERE
start_date gt
TO_DATE(01.01.1996, DD.MM.YYYY) 1 row
deleted.
76
? ? ? ? ? ? ?

• ??????

SQLgt DELETE FROM test 25,000 rows deleted.
77
COMMIT ROLLBACK

• ????????
• ??COMMIT???????????????????

78
COMMIT ? ROLLBACK ?????

• ????COMMIT ? ROLLBACK ??, ????????????????????
• ????COMMIT ? ROLLBACK ??, ??????????SELECT
  ?????????????????????????????
• ????COMMIT ? ROLLBACK ?, ????????,
  ???????????????????

79
COMMIT ?????

• ???????????????????????????
• ????????????????????????????

80
? ?

• ???????????
• ??????

SQLgt INSERET INTO s_dept(id, name, region_id)
VALUES (54,
Education, 1) 1 row created.
SQLgt UPDATE s_emp SET
dept_id -54 WHERE id 2 1 row
created.
81
Committing ??

• ???????

SQLgt COMMIT Commit complete.
82
ROLLBACK?????

• ??????????
• ???????????????????
• ????????????????

SQLgt DELETE FROM test 25,000 rows
deleted. SQLgt ROLLBACK Rollback complete.
83
Create trigger

• Create trigger audit_trigger before insert or
  delete or update on classified_table for each row
  begin if INSERTING then insert to
  audit_table values(insert 1 row) elsif
  DELETING then insert to audit_table
  values(delete 1 row) elsif UPDATING then
  insert to audit_table values(update 1
  row) endif end