oracle10g Training Log ☆ 4

Aug 22 posted in Oracle (RSS), 266 views.

--------------- Create table
create table table name
(
Column names of data types,
Column names of data types,
Column names of data types,
....
);

rn

char () fixed length character type (query efficiency)
varchar2 () variable-length character type
number
number (5,2) on behalf of five significant digits, decimals accounted for two
date

rn

--------------- Constraint
primary key primary key constraint
foreign key constraint foreign key
not null non-null constraints (can only be written in the column level)
The only constraint unique
check check constraint

rn

rn
create table student
(
SNO varchar2 (10),
SNAME varchar2 (20) not null,
SSEX varchar2 (2),
SAGE number,
SDEPT varchar2 (40),
email varchar2 (40),
primary key (sno),
unique (email),
check (ssex in ( 'M', 'F'))
);

rn

create table course
(
cno varchar2 (10),
cname varchar2 (20) not null,
primary key (cno)
);

rn
create table sc
(
sno varchar2 (10),
cno varchar2 (10),
grade number,
primary key (sno, cno),
check (grade between 0 and 100),
foreign key (sno) references student (sno),
foreign key (cno) references course (cno)
);

rn
------------------ Maintenance Table
---------- Add a column
alter table student
add (bir date);
---------- Modify the column
------ Listing
alter table student
rename column bir to birthday; --- to change the birthday column bir

rn

------ Data Types
alter table student
modify (birthday varchar2 (40));

rn
SQL> alter table student
2 modify (birthday date);

rn

---------- Delete a column
alter table student
drop (birthday);
Equivalent
alter table student
drop column birthday;

rn

------------------ Maintenance Constraints
--------- Add Constraint
--------- Add a non-null constraint
alter table student
modify (sname not null);

rn
alter table course
modify (cname not null);
-------- Add a unique constraint
alter table student
add unique (email);

rn
-------- Add check constraint
alter table student
add check (ssex in ( 'M', 'F'));

rn
alter table sc
add check (grade between 0 and 100);
-------- Add the primary key
alter table student
add primary key (sno);

rn
alter table course
add primary key (cno);

rn
alter table sc
add primary key (sno, cno);

rn
-------- Add a foreign key constraint
alter table sc
add foreign key (sno) references student (sno);

rn
alter table sc
add foreign key (cno) references course (cno);

rn

--------- Remove the constraint
alter table table name
drop constraint constraint name;

rn
alter table table name
drop constraint constraint name cascade; --- forced to delete constraints

rn
select * from user_constraints; --- Look, all bound by the current user

rn

The user_constraints table
P primary key constraint
R foreign key constraint
The only constraint U
C Check constraints or non-null constraint

rn

rn
------------------ Drop Table
SQL> drop table table name to delete the table you can flash back to ;------

rn
SQL> drop table table name to delete the table can not purge ;---- Flashback

rn

rn
---------------- Flashback
SQL> drop table sc;

rn
The table has been deleted.

rn
SQL> drop table student;

rn
The table has been deleted.

rn
SQL> select * from tab;

rn
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COURSE TABLE
BIN $ Lgw/Enc4Q72YNT4rrgrzPQ == $ 0 TABLE
BIN $ ML1 + EK/fR6GWj4HPP7BDMA == $ 0 TABLE

rn

rn
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ---- -------- -------------------
SC BIN $ ML1 + EK/fR6GWj4HPP7BDMA == $ 0 TABLE 2009-07-17:11:08:25
STUDENT BIN $ Lgw/Enc4Q72YNT4rrgrzPQ == $ 0 TABLE 2009-07-17:11:08:27

rn

rn
SQL> flashback table student to before drop;

rn
Flashback complete.

rn
SQL> flashback table sc to before drop;

rn
Flashback complete.

rn
SQL> select * from tab;

rn
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COURSE TABLE
SC TABLE
STUDENT TABLE

rn
SQL> show recyclebin;
SQL>

rn

SQL> purge recyclebin;

rn
Recycle Bin has been emptied.

rn

rn
SQL> purge table sc ;---- empty the Recycle Bin in the sc form

rn

SQL> flashback table "BIN $ 2DaB3OPWSZSN8IvMkgzg3w == $ 0" to before drop; - Flashback with the name of the Recycle Bin

rn
SQL> flashback table "BIN $ H743EKgMQZ645vfqUOP9qQ == $ 0" to before drop rename to old_a; - flashback at the same time to redefine the table name

rn

--------- Must have the dba role
select * from dba_users; --- View all users

rn
--------------- Modify the table name
SQL> rename a to b; - to table a table b change

rn

------------------ Notes (table-level, column level)

rn
SQL> comment on table student is 'student table';

rn
Note has been created.

rn
SQL> comment on table course is 'curriculum';

rn
Note has been created.

rn
SQL> comment on table sc is 'Course Selection Form';

rn
Note has been created.

rn

select * from user_tab_comments ----- See the table-level data dictionary

rn
SQL> comment on column student.sno is 'Student ID';

rn
Note has been created.

rn
SQL> comment on column student.sname is 'Name';

rn
Note has been created.

rn
SQL> comment on column student.sage is 'age';

rn
Note has been created.

rn

rn
select * from user_col_comments ----- View column-level data dictionary

rn

rn
SQL> select * from user_col_comments
2 where table_name = 'STUDENT';------ view the data dictionary table columns Students Notes

rn

rn
------------------------- Other database objects
--------------- View
create view name
as
select ....

rn

create view stu
as
select sname, cname, grade
from student, course, sc
where student.sno = sc.sno and sc.cno = course.cno;

rn

SQL> select * from stu ;----- Query View

rn

select * from user_views ;----- see what the view as much as

rn
View: virtual table imaginary: because he does not contain data tables: Because the multi-view the same as the query and table

rn
The essence of the view: that in the data dictionary, a select statement

rn

------------ Create a view, query each sector the average wage
create view avg_salary
as
select department_id department id, avg (salary) average wage
from employees
group by department_id;

rn

Note: If you create a view, select statement, there is a function, you must define an alias

rn

------------ Remove View
drop view name;

rn
---------------- View can not do DML (data manipulation language, insert, update, delete) operations are not necessarily

rn
As for the data to a table, without a function, without grouping data As is generally more than one table, with a function, with a group, can not be

rn
-------------------- Inline view

rn
select * from (select * from student where ssex = 'M')

rn

--------------------- top-n analysis of
---- Pseudo-column (in the table does not, but you can use in the sql statement)

rn
SQL> select rownum, sno, sname
2 from student;

rn
Note: rownum row number, so if it can only write rownum filter "and" =

rn

SQL> select * from student
2 where rownum <4;

rn
--------------- The oldest of three individuals
select * from (select * from student order by sage desc)
where rownum <4;

rn

-------------- Serial sequence: hair only integers database objects

rn

create table temp
(
id number,
name varchar2 (20),
primary key (id)
);
----------- Create sequence s1, start value 1, each increase of 1
create sequence s1
increment by 1
start with 1;

rn
-------------- nextval the value of the next one
insert into temp (id, name) values (s1.nextval, 'Zhang 3');
insert into temp (id, name) values (s1.nextval, 'Zhang 4');
insert into temp (id, name) values (s1.nextval, 'Zhang 5');
insert into temp (id, name) values (s1.nextval, 'Zhang 6');

rn

SQL> select s1.currval from dual ;---- view the sequence of current values.

rn

drop sequence s1 ;---- deleted sequence

rn
select * from user_sequences ;---- query sequence

rn
-------------- Index Role: To improve the query speed and reduce disk I / O

rn

create index name on table name (column name);

rn

When you create a primary key in the table, the only time constraint will automatically create a unique index.

rn

rn
The following is usually not worth creating an index, if:
A small amount of data tables in the query not often used as a query column that query result set end up getting a great frequently updated table (the index for the DML operation is that some negative impact)
Index column as part of an expression is used (for example, always check the condition SALARY * 12, this time to create an index in the SALARY column is no effect)

rn

rn
You should create an index, if:
? A column contains a large range of values
? A column that contains a lot of null values
? One or more of the columns often take place simultaneously in a WHERE clause or a join condition to be used in
? Table large, and often expect the query to retrieve less than 2-4% of the line

rn
SQL> insert into student
2 select * from student;

rn

SQL> update student
2 set sno = rownum;

rn
2097152 rows updated.

rn
SQL> commit;

rn
To submit completed.

rn
SQL> set timing on
SQL> select * from student
2 where sno = '2500 ';

rn
SNO SNAME SS SAGE SDEPT
-------- -------- - ---------- --------------------
2500 Wang Lin M 19 Computer

rn
Elapsed time: 00: 00: 05.82
SQL> create index i1 on student (sno);

rn
The index has been created.

rn
Elapsed time: 00: 00: 31.35
SQL> select * from student
2 where sno = '2500 ';

rn
SNO SNAME SS SAGE SDEPT
-------- -------- - ---------- --------------------
2500 Wang Lin M 19 Computer

rn
Elapsed time: 00: 00: 00.12
SQL>

rn
select * from user_indexes;

rn
drop index i1;

rn
------------- Synonyms

rn
Another form of access methods (table alias)

rn

select * from student;

rn

create synonym s for student;

rn
select * from s;

rn
drop synonym s;

rn
select * from user_synonyms;

rn

SQL> select * from user_objects ;---- see all objects under the current user

rn

----------------

rn