Oracle learning experience

1 .. sqlplus operation

1. query all tables in a database -- "selec * From tab;
2. Set pagesize 30;
3. Set the number of lines displayed in each row to set linesize 300;
4. Switch the user's conn user name [sys]/password [as sysdba/sys];
5. view the structure of a table --> DESC table name
6. Input/repeat the statement just executed

2... cmd
1. sqlplus username [sys]/password [as sysdba or sysoper];

 

3... SQL statement operation
1. Select job from EMP; query work Columns
2. Select distinct job works from EMP; query the work column to eliminate repeated items
3. Select job, Sal * 12 annual salary from EMP; query the work column to eliminate repeated items
4. Select * from EMP where ename like '_ M %'
5. Select * from EMP where Sal like '% 808080' to query all information of employees whose salary includes 5.

 

Oradim-New-Sid myoracle-startmode M-pfile D:/APP/Administrator/oradata/test. ora
Startup-pfile = D:/APP/Administrator/oradata/test. ora nomount

 

4... create and manage tables

1. Common Data Types
-------------------- | -------------------------
Data Type | description
-------------------- | --------------------------------------------
Number, | Number (n) represents an integer, number (m, n) represents a decimal number, decimal length is N, integer length is m
------------------- | ----------------------------------
Varchar, varchar2 |
--------------------- | -----------------------------------------
Date, | indicates the date type.
---------------------- | ------------------------------------------------
Blob, | binary data large object 4 GB
---------------------- | -----------------------------------------
Clob | 4 GB of text data
---------------------- | ----------------------------------------

Directly create a table
Create Table Name
{
Field Name field type [Default default value],
Field Name field type [Default default value],
Field Name field type [Default default value],
}

Query duplicate tables
Create Table table name as (subquery)
For example, create table temp as select * from EMP where 1 = 2; you can copy the table structure.

Delete table data
Delete table name
Delete table structures and Data
Drop Table Name

Create a person table
Create Table person
(
PID varchar2 (20) primary key,
Name varchar2 (20 ),
Age number (3 ),
Birthday date,
Sex varchar2 (2) default 'male'
);

Add data to the table
Insert into person (PID, name, age, birthday, sex)
Values ('20140901', 'zhangsan', 30, to_date ('2017-02-03 ', 'yyyy-mm-dd'), 'male ');

5. syntax of the Table Structure
1. Add
Alter table table name Add (column name type [Default] default value );
Alter table person add (address varchar2 (100) default 'no address ');
2. Modify
Alter table table name modify (column name type [Default] default value );
Alter table person modify (name varchar (10) default 'Anonymous ');

3. Rename the table
Rename table name 1 to existing table name

6 ...... Constraints

 

1. Use Cartesian sets to find the corresponding relationship

Create Table Nation (
Name varchar2 (20)
);
Insert into nation values ('China ');
Insert into nation values ('u.s ');
Insert into nation values ('uk ');
Insert into nation values ('Holland ');
Insert into nation values ('Brazil ');

Select t1.name, t2.name
From nation T1, nation T2 where t1.name <> t2.name;

2. constraint classification

. Primary key constraint

It is a unique identifier and cannot be empty.
Create Table person
(
PID varchar2 (20) primary key,
Name varchar2 (20 ),
Age number (3 ),
Birthday date,
Sex varchar2 (2) default 'male'
);

Or

Drop table person;
Create Table person
(
PID varchar2 (20 ),
Name varchar2 (20 ),
Age number (3 ),
Birthday date,
Sex varchar2 (2) default 'male ',
Constraint person_pid_pk primary key (PID)
); // Person_pid_pk indicates the constraint name

 

. Unique Constraint

Only one primary key constraint is allowed in a table,
If you do not want duplicate values in other columns, you can use the unique constraint.
Drop table person;
Create Table person
(
PID varchar2 (20) primary key,
Name varchar2 (20) unique,
Age number (3 ),
Birthday date,
Sex varchar2 (2) default 'male'
);

Or

Drop table person;
Create Table person
(
PID varchar2 (20) primary key,
Name varchar2 (20 ),
Age number (3 ),
Birthday date,
Sex varchar2 (2) default 'male ',
Constraint person_name_uk unique (PID)
); // Person_pid_pk indicates the constraint name

 

. Check Constraints

Check whether the content of a column is valid
For example, the age can only be 0-1 50

Drop table person;
Create Table person
(
PID varchar2 (20 ),
Name varchar2 (20 ),
Age number (3) Check (age between 0 and 150 ),
Birthday date,
Sex varchar2 (2) default 'male ',
Constraint person_pid_pk primary key (PID ),
Constraint person_name_uk unique (name)
); // Person_pid_pk indicates the constraint name

. Non-empty Constraint

For example, the name field cannot be blank.

Drop table person;
Create Table person
(
PID varchar2 (20) primary key,
Name varchar2 (20) not null,
Age number (3 ),
Birthday date,
Sex varchar2 (2) default 'male'
);

 

. Foreign key constraint

Foreign key constraints between two tables

Drop table person2;
Create Table person2
(
PID varchar2 (20) primary key,
Name varchar2 (20) not null,
Age number (3 ),
Birthday date,
Sex varchar2 (2) default 'male ',
Foreign key (PID) References person (PID)
);

To delete a parent table, you can use cascading deletion.
Drop table person cascade constraint;

Drop table person;
Create Table person
(
PID varchar2 (20 ),
Name varchar2 (20 ),
Age number (3 ),
Birthday date,
Sex varchar2 (2) default 'male'
);
 
Add Constraints
Alter table add constraint name constraint type (constraint field)

Delete Constraints
Alter table drop constraint name

Rownum is a pseudo column that can be used to access data in a table.

7... View

1. Create a view

Create view name as (subquery)
Create view empv20 as select empno, ename, job, hiredate
From EMP where deptno = 20;
Create a view

 
If you are prompted that you do not have the permission, you can log on to the conn system/password.
Grant create any view to Scott;
Revoke revoke create any view from Scott;

Create or replace view name as (subquery)
Create view empv20 as select empno, ename, job, hiredate
From EMP where deptno = 20;
If a view exists, delete it and create it again.

* *** Complex queries can be created using views

2. Update the view

No real data exists in the view, and the Creation conditions cannot be updated when updating the view.

The essence of a view is to query, so to avoid updating a view
You can add a statement during creation.
Create or replace view empv20 as select empno, ename, job, hiredate
From EMP where deptno = 20 with read only;

8 ...... Sequence

In many databases, columns are automatically increased, but not in Oracle.
Therefore, you must manually complete the automatic growth operation.

Create sequence name
(
[Increment by N] [start with N],


);

Two operations
Nextval
Currval
 
Create Table testseq
(
Next number,
Curr number
);

Add a sequence to a table
Insert into testseq (next, curr) values (myse. nextval, myse. currval );

Create sequence myse increment by 2 start with 1;

Create sequence myse1
Maxvalue 10
Start with 1
Increment by 1
Cycle
Cache 2;

Delete Sequence
Drop sequence name

9 ...... Synonym
1. Select sysdate from dual;
Query System Time
Dual is a table under sys (SYS. Dual)
 

Scott can view the table as a synonym.

Create a synonym
Create synonym name for user name. Table name --> synonym name = user name. Table Name

Delete Synonym
Drop synonym name

10 ...... user management

Establish and authorize Oracle squadron users

1. Create a user
Create User Username identified by password;
For example, create user test identified by test123;

2. Authorize the user
Grant permission 1, permission 2 to user name
For example, grant create session to test; you can log on to it.

If you want to grant Multiple permissions to one user at a time, you can
Define these permissions as a role

For example
Grant connect, resource to user name.

Connect and resource are both roles.

3. delete a user
Drop User Username

4. Change the User Password

Under sys
Alter User Username identified by new password.

5. invalidate the password
Alter User Username Password expire;
To allow it to update its password

6. Lock the user
Alter User Username account lock;
 
7. Unlock the user
Alter User Username account unlock;
 
8. Authorize access to a table
Grant select, delete on Scott. EMP to user name

9. revoke permissions
Revoke select, delete on Scott. EMP from user name

 

11 ...... database backup and recovery
. Database Backup exp
Database recovery imp

Run the exp command in cmd.

Will prompt some information
Press enter.
 

12 ...... nested table

One department has multiple projects

Create a project category
Create type project_ty as object (
Proid number (4 ),
Pname varchar (20)
);
/

** Note/indispensable

// Use an alias
Create type project_nt as table of project_ty;
/

// Create a department table nested project category table
Create Table Department
(
Deptno number (2) primary key,
Dname varchar (50 ),
Projects project_nt
) Nested table projects store as project_nt_tab_temp;

 

// Store data
Insert into Department (deptno, dname, Projects)
Values (1, 'technical Department ', project_nt (
Project_ty ('1', 'thesis management system '),
Project_ty ('2', 'employee management system '),
Project_ty ('3', 'salary management system ')
)
);

12 ...... database design paradigm

 

 

 

 
 

 

 

 

 

 

 

PM