Oracle Notes (i)

Oracle Basic theory

L Mainstream Database

Microsoft Corporation: SQL Server ACCESS

IBM Company: DB2 Infomix

Oracle Company: Oracle MySQL (AB Company)

Small database ACCESS FoxBase Development capacity: 100 people Visits: 10 people/second Development cost: 1000 RMB

Medium Database SQL Server My SQL Infomix Development capacity: 1000 people Visits: 500 people/second Development cost: 10000 RMB

Large database Oracle DB2 Number of developers: 36,000 Visit Volume: Massive Development cost: No Limit

L Database History

Hierarchical database Network type database

Relational database:

1970 IBM researcher Dr. E.f.codd laid the theoretical foundation of the relational database

1978 Oracle CEO Larry Alison developed the first commercial large-scale relational database Oracle

1983 IBM launches DB2.

L Oracle Version

Oracle9i (Internet Internet)-oracle10g (grid meshing)-oracle11g-oracle12c (Cloud Cloud)

L Oracle Platform Tools

SQL Plus Plsql Developer

L Oracle Service Services.msc

oraclexxxxxxtnslistener-Third-party tool monitoring service: 1521

oracleservicexxxx-Core Services (physical run structure-SGA xxxx instance database)

L Oracle Account

N SYSTEM Account

U sys role: SYSDBA owner can create database

U system role: Sysoper big Butler Cannot create database

U Scott role: normal instance user what authority does the big Butler give him?

To switch an account:

Conn scott/123456 Conn User name/password

View the data dictionary for the following table of Scott users:

DESC EMP; DESC Table Name

To view the table contents under the Scott User:

Select * from EMP; Select * FROM table name

L Custom Account conn system/123456;

Create an Account	-- Create a user CreateUser Rose identified by123456
Modify Account	-- Modify a user's password Alter user rose identified by 123456; -- Unlocking a user Alter user rose account unlock; -- to modify a user's restrictions on inserting data into a table space Alteruser Rose Quota 1m on users
Account Authorization	-- permissions granted to a linked database Grant create session to Rose; Grant Createtableto Rose; Grant create session, Createtableto Rose
Delete Account	Drop user Rose; ORA-01940: Unable to delete the currently connected user Select username,sid,serial# from V$session; Alter system kill session ' 8,836 '; ORA-01922: Must specify CASCADE to remove ' ROSE ' Drop user rose cascade;
Create a table	？？？？？

L Permissions

n System Permissions select * from System_privilege_map;

Grammar:

Authorization: Grant system permissions | roles | All to User

Revoke permissions: Revoke system permissions | roles | Allfrom Users

N Object Permissions select * from Table_privilege_map;

Grammar:

Authorization: Grant Object Permissions | All on object to user

Revoke permissions: Revoke object Permissions | All on object from user

Case one: Student management system student (Stuno Integer, Stuname varchar2 (Ten), Stubir Date,stusex char (3));

Step one: Create a table space

L -- create a table space Create Tablespace Demoschool DataFile ' d:/demoschool.dbf ' Size 5m Autoextend on

Step Two: Create an instance account, manage table spaces

Create user lily identified by 123456 default Tablespace demoschool; Alteruser Lily identified By123456default tablespace Demoschool

Step three: Grant the instance account system permissions

Step four: Log in to this account

Step five: Establish the student table

Step SIX: Insert 2 data into the table

Step Seven: Check the student's name

Step Eight: Modify student information

Step Nine: Delete students

Step Ten: Delete the table

Execute the SQL script in the physical path

@ d:/school.sql Start D:/school.sql

Delete Table space

Drop Tablespace Demoschool; --Delete the physical files in the hard drive together Drop tablespace demoschool including contents and datafiles;

Primary data types for Oracle databases

A String type

Char fixed-length character char field can store up to 2,000 bytes of information.

VARCHAR2 type variable length type VARCHAR2 can store up to 4,000 bytes of information.

Two. Number types

Number (P,s) is the most common numeric type that can hold a data range of 10^130~10^126 (without this value) and requires a 1~22 byte (byte) of storage space.

P is the abbreviation for Precison, the abbreviation of precision, indicating the number of digits of a valid number, up to 38 significant digits

S is the English abbreviation for scale and can be used in -84~127. When scale is positive, the number of digits from the decimal point to the lowest significant number, which is negative, indicates the number of digits from the maximum significant number to the

The integer type integer is the subtype of number, which is equivalent to number (38,0), which is used to store integers. If the inserted and updated values have decimals, they are rounded.

Float type float type is also a subtype of number.

Float (n), number n indicates the precision of the bit, the number of values that can be stored. The value of N can range from 1 to 126. To convert from binary to decimal precision, multiply n by 0.30103. To convert from decimal to binary precision, use 3.32193 times decimal precision. The maximum value for 126-bit binary precision is approximately equivalent to 38 decimal digits precision.

Three. Date type

Date is the most commonly used type of data, and dates data types store date and time information. Although date and time information can be represented by a character or number type, the date data type has special associated properties. For each date value, Oracle stores the following information: Century, year, month, date, hour, minute, and second. Typically consumes 7 bytes of storage space.

TIMESTAMP type

This is a 7-byte or 12-byte fixed-width date/time data type. It differs from the date data type because timestamp can contain fractional seconds, and timestamp with fractional seconds can hold up to 9 digits to the right of the decimal point

Four. LOB type

CLOB Data Type

It stores single-byte and multibyte-character data. Supports a fixed-width and variable-width character set. CLOB objects can store up to (4 gigabytes-1) * (databaseblock size)-sized characters

BLOB Data Type

It stores unstructured binary data large objects, which can be thought of as a bit stream without character set semantics, typically images, sounds, video, and other files. BLOB objects store binary data of up to (4 gigabytes-1) * (Database blocksize).

Five. Raw & LONG Raw type

LONG type It stores variable-length strings up to 2G of character data (2GB refers to 2000 megabytes instead of 2000 megabytes), and as with VARCHAR2 or char types, text stored in a long type is converted to character set.

2017-10-31 18:30:59

Oracle Notes (i)