Getting Started with Oracle

DDL(data definition Language) Definition language:

CREATE ALTER DROP Object

TRUNCATE table name;

DML(Data manipulation language) manipulation language:

INSERT UPDATE DELETE SELECT

Common Oracle database Objects :

User tablespace table View index sequence synonym trigger procedure

Master the names of the above objects

Common DBMS: Oracle, Access, SQL Server, MySQL

Oracle Service name : Oracleservicexx

To connect to an Oracle database, you must start the service for that database

Data type

Character type: Char varchar2 nchar nvarchar2 long Long raw

Digital type: Number integer

Date Type: Date

First, create a table

Create table table name

（

Column name 1 whether the data type can be null primary Key,

Column Name 2 Data Type default Default Value is nullable,

Column Name 3 Data Type Nullable references reference table table name ( Reference column name ) , // when there is a foreign key

...

）

Example: create a Student table whose table structure is defined as shown in the following table

Column Name	Data type	Whether or not to empty	Other constraints	Description
Sno	CHAR (9)	Whether	Primary key	School Number
Sname	varchar (20)	Whether		Name
Ssex	CHAR (2)	Whether	The default value is ' Male '	Gender
Sage	Int	Can		Age
Sdept	CHAR (10)	Can		Your department

CREATE TABLE Student

(

Sno CHAR (9) NOT null PRIMARY KEY,

Sname VarCHAR () NOT NULL ,

Ssex CHAR (2) Default ' male ' NOT null ,

Sage INT ,

sdept CHAR (Ten)

)；

second, modify the table Alter Table

1. Add columns

Alter table table name

ADD ( column name data type [ default defaults ] is nullable );

2. Modify Columns

Alter table table name

Modify (the column name new data type [ default defaults ] is nullable );

3. Delete Columns

Alter table table name

Drop ( column name [, column name 2, column name 3, ... ). ]);

4. Add a PRIMARY KEY constraint (which belongs to the principal integrity)

Alter table table name

Add Primary key ( column name [, column name 2, ... ). ] );

5. Add unique key constraint (belongs to the principal integrity)

Alter table table name

Add unique ( column name [, column name 2, ... ). ] );

6. Add foreign KEY constraint (referential integrity)

Alter table table name

Add Foreign key ( column name ) References reference table table name ( Reference column name );

7. Add Check constraint (user-defined integrity)

Alter table table name

Add Check ( check condition );

Iii. inserting data, adding records

Insert into table name ( column name [, column name 2,... ). ] )

VALUES ( column value [, column value 2,... ). ] );

Note: column names and column values are aligned up and down

Cases:

insert a row into the student table: School Number Sno is 123803, the name sname "Lei Feng".

Insert into student( Sno, sname )

Values ( 123803, ' Lei feng ' );

Iv. Modification of records

Update Table name

Set Modify Data column = new value

Where conditions;

Cases:

Modify The table to change (what is the condition) (what column) to (what value)

Modify the student table to change the age of Sno to 123803 Sage to 21

Update Student

Set sage=

Where sno=123803 ;

Revise the student's age and ask all students to increase their age by one year

Update student set sage=sage+1;

V. Deletion of records

Delete from table name

Where condition;

Example: Delete a record in the * * * * * * * * table.

Delete from ******

Where condition;

Examples of conditions:

Sno to 123803:sno=123803

Phone call is empty: Phone is null

Wages salary higher than 3500:salary>3500

Score grade between 70~90: Grade between and 90

The work city is located in "Beijing", "Shanghai", "Guangzhou", the Three land: Urban in (' Beijing ', ' Shanghai ', ' Guangzhou ')

Book titled "Java" in Title (bname): bname like '%java% '

Vi. Inquiries

columns to display in the Select result

From table name [, table 2, ... .. ]

Where Query Condition [ and join condition ] /* No condition, this line does not write */

GROUP by group column name/* is not grouped, this line is not written; In general, the "per" word appears in the query request */

The Having group filter condition/* Does not have a group filter, this line is not written; In general, there are aggregate functions in the condition */

Order BY sort column name [asc| DESC]/* No sorting required, this line does not write----ascending |

Grouping statistics function: Count () Avg () min () max () sum ()

string addition: Last_Name | | First_Name

Single-line subquery/multirow subquery: In all

Select * FROM Student

Where Sno Not in (select Sno from SC)

Select * FROM Student

Where Sdept like (select Sdept from dept where fzr = ' Wang Ming ')

Cases:

1. Search from (score table) ... The lowest (score)

Select min(score)

From the score table ;

2, from (student table) to find out: (what) students are "Hangzhou" School number, name, date of birth.

Select Study number, name, date of birth

From student Table

Where origin = ' Hangzhou ';

3, the use of multi-table equivalent connection query , lists the student number, name, the course name and results of their courses

Student form (school number, name)

Curriculum (course number, course name)

Score Table (School number, course number, score)

Select study number, name, course name, score

From student table, timetable, score table

Where Student table. Study Number = Score table. Study number and schedule. Course Number = Score table. Course Number

Vii. Creating an Index

Create Index index name

On table ( column )

Cases:

Create an index on the * * * column of the * * * Table * * *

Create Index index name

On table ( column )

The role of the index

Which constraints automatically create unique indexes: Primary key, Unique key

Viii. Creating sequences

Create Sequence sequence name

Start with start value

INCREMENT by increment value

MaxValue maximum value ;

Cases:

Create a sequence * * * *, this sequence requires starting from 1, increment is 5, the maximum value is 100000

Create Sequence * * * *

Start with 1

INCREMENT by 5

MaxValue 100000;

Ix. Creating a View

Cases:

Create a view named * * * that shows ...

CREATE OR REPLACE View * * *

As

Query statements

Or

CREATE View * * *

As

Query statements

Modify View

CREATE OR REPLACE View * * *

As

Query statements

Create user

Create user username identified by password ;

Authorized

Grant permission to user name ;

Grant Permissions on the object name (table name, view name, etc.) to the user name ;

Revoke (or retract) permissions

revoke permissions from user name ;

revoke permissions on object name (table name, view name, etc. ) from user name ;

ER the rules for converting diagrams into relational patterns

Entity name--Relationship name (table name)

Properties--Properties

Contact: 1:1 Add the primary key attribute of another entity as a foreign key in any one of the end entities of the contact

1:n Add the primary key attribute of the 1-side entity as a foreign key in the N-end entity of the contact

M:N contact becomes a new relationship (new table), the primary key of both entities as the foreign key of the new relationship

————————————————————

Ix. Creating triggers

Cases:

creates a trigger that is named * * *, which is activated when a row of records is modified in student table studnet and outputs "successfully modified."

CREATE OR REPLACE Trigger * * * * *

After Update On Student

BEGIN

END;

X. Creating a Stored Procedure

Create Or Replace Procedure Procedure name

As

Getting Started with Oracle