ddl.ppt
TRANSCRIPT
-
7/25/2019 DDL.ppt
1/17
Creating and Managing
Tables
-
7/25/2019 DDL.ppt
2/17
Objectives
After completing this lesson, you should
be able to do the following:
Describe the main database objectsCreate tables
Describe the data types that can be used
when specifying column definition
Alter table definitions
Drop, rename, and truncate tables
-
7/25/2019 DDL.ppt
3/17
Database Objects
Object Description
Table Basic unit of storage; composed of rows
and columns
View Logically represents subsets of data fromone or more tables
Sequence Generates primary ey !alues
"nde# "mpro!es t$e performance of some queries
Synonym Gi!es alternati!e names to objects
-
7/25/2019 DDL.ppt
4/17
Naming Conventions
Must begin with a letter
Can be 1! characters long
Must contain only A", a#, !$, %, &, and 'Must not duplicate the name of another
object owned by the same user
Must not be an (racle )er*er reser*ed word
-
7/25/2019 DDL.ppt
5/17
The CREATE TABLE Statement
+ou must ha*e : C-A.- .A/0- pri*ilege
A storage area
+ou specify:
.able name
Column name, column datatype, and column si#e
CREATE TABLE [schema.]table
(columndatatype[DEFAULT expr][, ...]);
-
7/25/2019 DDL.ppt
6/17
Creating Tables
SQL> CREATE TABLE dept
2 (deptno NUMBER(2),
3 dn!e "ARC#AR2($%),
% &o' "ARC#AR2($3));
T&e 'eted.T&e 'eted.
Create the table
%onfirm table creation&SQL> DESCR*BE dept
N!e N+&& T-pe
DE/TN0 NUMBER(2)
DNAME "ARC#AR2($%)
L0C "ARC#AR2($3)
-
7/25/2019 DDL.ppt
7/17
Data tyesDatatype Description
V'(%)'(*+size, Variable-lengt$ c$aracter data
%)'(+size, .i#ed-lengt$ c$aracter data
/01B2(+p3s) Variable-lengt$ numeric data
D'T2 Date and time !alues
-
7/25/2019 DDL.ppt
8/17
The ALTER TABLE Statement
2se the A0.- .A/0- statement to:Add a new column
Modify an e3isting column
Define a default *alue for the new column
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE table
M0D*F1 (column datatype [DEFAULT expr]
[, column datatype]...);
-
7/25/2019 DDL.ppt
9/17
Adding a Col!mn
D24T56D24T56
EM/N0 ENAME ANNSAL #*REDATE
45 BLA6E 3%277 7$MA15$
8% MART*N $8777 25SE/5$
%44 ALLEN $4277 27FEB5$ 5%% TURNER $5777 75SE/5$
...
7878add aadd a
newnewcolumncolumnintointoD24T56D24T56table89table89
D24T56D24T56
EM/N0 ENAME ANNSAL #*REDATE
45 BLA6E 3%277 7$MA15$
8% MART*N $8777 25SE/5$
%44 ALLEN $4277 27FEB5$
5%% TURNER $5777 75SE/5$
...
90B
90B
/ew column/ew column
-
7/25/2019 DDL.ppt
10/17
Adding a Col!mn
+ou use the ADD clause to add columns
EM/N0 ENAME ANNSAL #*REDATE 90B
45 BLA6E 3%277 7$MA15$
8% MART*N $8777 25SE/5$ %44 ALLEN $4277 27FEB5$
5%% TURNER $5777 75SE/5$
...
o: e&e'ted.
SQL> ALTER TABLE dept37
2 ADD (
-
7/25/2019 DDL.ppt
11/17
Modi"ying a Col!mn
+ou can change a column4s data type, si#e,
and default *alue
A change to the default *alue affects only
subse5uent insertions to the table
ALTER TABLE dept37
M0D*F1 (en!e "ARC#AR2($8));
T&e &teed.T&e &teed.
-
7/25/2019 DDL.ppt
12/17
Droing a Table
All data and structure in the table is deleted
Any pending transactions are committed
All inde3es are dropped+ou cannotroll bac6 this statement
SQL> DR0/ TABLE dept37;
T&e dopped.T&e dopped.
-
7/25/2019 DDL.ppt
13/17
Changing the Name o" an
Object.o change the name of a table, *iew,
se5uence, or synonym, you e3ecute the
-7AM- statement
+ou must be the owner of the object
SQL> RENAME dept T0 dept!ent;
T&e en!ed.T&e en!ed.
-
7/25/2019 DDL.ppt
14/17
Tr!ncating a Table
.he .27CA.- .A/0- statement: emo*es all rows from a table
eleases the storage space used by that table
+ou cannot roll bac6 row remo*al when
using .27CA.-Alternati*ely, you can remo*e rows by using
the D-0-.- statement
SQL> TRUNCATE TABLE dept!ent;
T&e t+n'ted.T&e t+n'ted.
-
7/25/2019 DDL.ppt
15/17
S!mmary
Statement Description
%(2'T2 T'BL2 %reates a table
'LT2( T'BL2 1odifies table structures
D(O4 T'BL2 (emo!es t$e rows and table structure
(2/'12 %$anges t$e name of a table3 !iew3sequence3 or synonym
T(0/%'T2 (emo!es all rows from a table andreleases t$e storage space
-
7/25/2019 DDL.ppt
16/17
#ractice Overvie$
Creating new tables
Creating a new table by using the C-A.- .A/0- A) synta3
Modifying column definitions
8erifying that the tables e3ist
Dropping tables
Altering tables
-
7/25/2019 DDL.ppt
17/17