Oracle entry-level knowledge

Oracle entry-level knowledge

I.Oracle Basics

1. When should I use the Oracle database?

SQL SERVER claims to be a million-level data (data in one table), but it actually does more than 0.2 million pieces of data

Oracle is used for over 0.2 million data entries

2. Oracle version

Oracle8i/9i (internet) network-based;

Oracle10g/11g is based on the network, oracle10g/11g (grid) is based on the grid, and its client is a web program, which can be opened in IE: oracle> Database Control-orcl will be opened in IE. If the address cannot be found, the OracleDBConsoleorcl Service (DBC Controller) will not be started. This function is equivalent to SQL Server;

Orcale12c;

3. Oracle 12c-the upgrade of oracle 11g, the application server, is very dazzling, but for beginners, however

4.

II.Started Service

1. // oracle Core Services

OracleServiceORCL

2. // oracle listener service

OracleOraDb11g_home1TNSListener

3. // oracle Database schedule, no schedule, no need to start, occupy Resources

OracleJobSchedulerORCL

II.Oracle's default account scott

Password: tiger (set during installation)

There are several commonly used tables: emp, dept, salgrade, etc.

III. The biggest difference between SQL and Oracle:

SQL allows you to create databases at will;

However, Oracle only has one database, and only one database is installed on one computer.

Oracle only creates accounts,

Iv. Accounts, tablespaces, and databases

Case: two students create two different databases

Both of them need to find their own database in Oracle. I just said that one Oracle has only one database. What should I do? Create an account for each of the two students and create a database space for the account-table space ).

The tablespace replaces the database. Creating a database is to open up the account space.

Note: Oracle opens up space for account A and Account B. Are A and B using the same database?

Can different accounts communicate with each other?

The same database; mutual access is not allowed, unless access permissions are granted

5.Create databases and accounts

Database

Physical: database file. dbf; log file. log; control file. ctl;

Virtual: tablespace (available for Users)

Can I create a tablespace or an account first?

A: Create a tablespace and then give it to the user.

About account

Sys and system administrator accounts are allocated when software is installed. Therefore, you can use this account to open up space.

Scott (release account: Show us and use) password is tiger.

Note: Do not change the account and password. The world is the same. (It is best not to change it to prevent installation failure and database access failure)

About Account Logon

You can use the sys or system Administrator Account or the scott account to log on.

1. Select the oracle directory-Application Development-SQL plus from the Start Menu.

2. In the cmd status, run the SQL plus command to log on.

Of course, cmd user interface is poor, we use a third-party PL-SQL to operate oracle, but can not say that sqlplus does not need, after unix can only use sqlplus to write code, more sad reminder

Thoughts:

A. Can an Administrator Account call A table of A general account?

Select * from scott. emp;

A: Yes.

B. Can I change the table of a general account?

A: access is allowed only after authorization.

C. What is the mark of code writing?

';'. Use a semicolon to indicate that the code has been written. You can execute

D. What is the mode?

Mode is another account name

For example, scott is an account, and other objects are a mode.

Each object is in a pattern.

6. Create a tablespace

Based on application performance and management considerations, it is best to create independent tablespaces for different users.

Syntax for creating a tablespace:

Create tablespace name

Example:

Create tablespace ts_1

Datafile 'e: 111. dbf' -- database file address

Size 100 M -- Initial File Size

-- Only three rows above can be used.

Autoextend on next 32 maxsize unlimited

Logging

Extent management local

Segment space management auto;

VII. My Objects (My object)

Functions-

Procedures stored procedure-

Packages Package-

Package bodies Package-

Types type-

Type bodies Type-

Triggers trigger-

Java sources

Jobs

Queues queue

Queue table

Libraries

Directories

Tables Table-

Views-

Materialized views

Sequences sequence-

Users user-

Profiles control file-

Reles role-

Synonyms-

Database links

Tablespaces tablespace-

Clusters

8. Allocate an account to the tablespace and authorize the account role

1. First, create an account and specify the access tablespace, but do not assign any permissions to it. You cannot wait during logon!

Create user Username

Identified by 123.

Default tablespace -- specify the tablespace managed by the user

Case: create user u_dml

Identified by 123.

Default tablespace ts_1

2. Grant roles to the account

(Clearly differentiate accounts and roles)

Role: Li Pengfei is a student role: Student

Account: Li Pengfei is a student account: Li Pengfei

Role category:

Connect: Temporary User Role

Resource: formal user role

DBA: Administrator (generally not required)

Authorize a role:

Grant connect, resource to account

Revoke role:

Revoke connect, resource from account

Delete (UNDO) Users:

Drop user Username

DROP user Username CASCAED

-- CASCAED indicates that a user is deleted and the entity created by the user is deleted.

Operation authorization:

Case: grant the test user the permission to query the scott user's stu table

Grant select on scott. stu to test

9. Create a table

All created tables are in tables.

Two table creation methods:

1. View

2. Code

Create table tb_1 (

Sid number, -- number used for int type

Sname varchar2 (20) -- no varchar

Sex varchar2 (4)

);

Constraint writing in a table

PK

FK

Default

Unique

Check

Not null

-- View data in the table

Select * from tb_1;

Q:Does Orecle indicate columns?

Oracle does not represent columns, but there is a substitute (trigger + sequence)

-- Insert data

Insert into tb_1 (sid, sname, sex)

Values (10, 'zhang Tian', 'male ')

-- View-based table Creation

-- View version Add Table:

Right-click Tables -- New -- primary key: In the key, the name is obtained by yourself. The type is primary. Which column is selected as the primary key and two are selected for the joint primary key. How many primary keys are there? One primary key and multiple associated primary keys

Delete column: minus sign on the right

-- How to give me the table creation script?

Bottom right -- view SQL

-- Click "application" after the table is created"

-- Specify comments for tables and fields

Comment on table pet is 'Pet ';

COMMENT on COLUMN pet. name is 'Pet nicknames'

Note: Do not physically Delete the dbf file on the hard disk. When oracle is started, the file cannot be found, and oracle cannot be started. You need to reinstall oracle.

Delete users:

Drop user Username;

Delete tablespace:

Drop tablespace;

Views can also be deleted, but don't be bored. Use the Code if you want to use it.