Introduction to 1.Oracle databases
What is the difference between a database system and a database management system?
parsing: Database system = Database management system +oper+ operator + hardware
Oracle Database management system
Core products of Oracle Corporation
Currently the most popular database
Major Version oracle8i/9i (Internet),oracle10g/11g (GRID)
based on C/S system structure
2.
Oracle main components
PGA:
SGA: system Global Area systems globals
DBWR(Read and write data files)
LGWR (log read and write)
Pmon (monitoring data running state) after an abnormally interrupted cleanup, processing, and freeing of resources
Smon (Cleanup temp table space)
CKPT Checkpoint(checkpoint guarantees database consistency, which means writing dirty data to the hard disk to ensure that the memory and data on the hard disk are the same)
Database
A collection of data stored on disk
Physical representation of data files, log files, control files, etc.
Logically present in tablespace form
You must first create a database before you can use Oracle
DB instance
Each database that is launched corresponds to a database instance that is accessed and controlled by the database
in order to run the database, The composition of all processes and allocated memory structures that are run by the Oracle system
Data files
the extension is . DBF, the file used to store database data
There is no one-to-one correspondence between database tables and data files
Control files
the extension is . CTL, which is required to start and run the database
The default contains 3 control files, the contents of each control file are the same
Log file
the extension is . Log, which records all changes to the data
Looping between multiple log file groups
2.
relationship of instance and database
parsing:
Instance: After the database service starts, the cells in memory
database: Files on a hard disk
4.Oracle Installation attention is a matter
The path does not have Chinese Special Characters Space (any path)
5. location of the listening file
6.
start Oracle Services
opening sequence: Start the listening service first, then turn on Oracle Services
Tnslistener Monitoring Service
ORACLESERVICEORCL Oracle Services
connecting to a database :
Common Ways to connect:
6.
Database User
SYS: Super admin manages normal admin and normal user
System: General Administrator
Scott: Regular users
7.
Data Type
6.
Roles and Permissions
Note: In Oracle, the table is isolated based on the user .
Each user sees only the tables that they create
Create users and authorizations
1 -- 2 create user Liutao identified by 123 3 -- authorization 5 grant connect to Liutao 6 to Liutao
Oracle includes two classes of permissions (privileges), one for system permissions and the other for object permissions .
CONNECTrole:--are the typical rights granted to end users, the most basic
CREATE SESSION--Establish a session
RESOURCErole:--is granted to developers
CREATE CLUSTER--Build a cluster
CREATE PROCEDURE--Build Process
CREATE SEQUENCE--set up a sequence
CREATE TABLE--Build Table
CREATE TRIGGER--Creating triggers
CREATE TYPE--Build Type
CREATE OPERATOR--Create operator
CREATE Indextype--Create an index type
DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.
RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.
Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.
for normal users: Grant Connect, resource permissions.
for DBA Administration User: Grant Connect,resource, dba authority.
7. How to view all the tables that the user manages
1 -- How to view all the tables that the user manages 2 Select * from where owner='liutao'
6.
Pseudo-Column
What is a pseudo-column?
a pseudo-column in Oracle is like a column in a table, but it is not stored in a table, and a pseudo-column can be queried in a table, but cannot be added, deleted, and modified.
Common pseudo-columns have ROWID and ROWNUM
1. ROWID
2.ROWNUM
ROWNUM is the ordinal of the row in the result set returned by the query, which you can use to limit the number of rows returned by the query
Question one:
Data Sheet
--Query the record of natural sequential query
1 Select * from 2 (3 Select STUDENT. * from STUDENT 4 )temp5where rn=5
--4-5 records on the second page of the paging query
1 Select * from2 (3 Select Temp.*, ROWNUM RN from 4 (5 Select * fromSTUDENT6)Temp7 whereROWNUM<=68 )9 whereRn>=4
--between and notation
1 SELECT * from 2 (3 SELECTA.*, ROWNUM RN4 from(SELECT * fromSTUDENT) A5 )6 WHERERnbetween 4 and 6
Oracle Database Foundation