Oracle Basics-Learning Notes

A "user" Sys\system\sysman\scott

1. View all users of the database (Dba_users data dictionary):

Select username from dba_users;

2. View Current User:

Show user;

3. Enable (Unlock) database users:

Alter user username account unlock;

Two "table Space"

Relationship between database and tablespace: A database can have one or more table spaces;

Table space and data file relationship: A table space corresponding to a data file;

Permanent table space : Mainly used for storing tables, views, stored procedures;

temporary table space : Mainly used to store some database operations in the middle of the process of execution, when the completion of the content will be released automatically, do not be permanently saved;

undo Tablespace : used primarily to save old values of the transaction's modified data, that is, the data before it is modified;

1. View the user's tablespace (dba_tablespaces and User_tablespaces, Dba_users, and user_users data dictionaries):

Select Tablespace_name from Dba_tablespaces;

Select Tablespace_name from User_tablespaces;

Select Username,default_tablespace,temporary_tablespace from Dba_users;

Select Username,default_tablespace,temporary_tablespace from User_users;

2. Set the default or temporary tablespace for the user:

Alter user username defalut|temporary tablespace tablespace_name;

3. CREATE TABLE space:

Create [temporary] tablespace tablespace_name tempfile|datafile 'xx.dbf' size xx;

4. View the path to the table space corresponding data file (Dba_data_files and Dba_temp_files data dictionary):

Select Tablespace_name,file_name from Dba_data_files;

Select Tablespace_name,file_name from Dba_temp_files;

5. Modify the status of the table space:

(online or offline)

Alter tablespace Tablespace_name online|offline;

(read-only or writable)

Alter tablespace tablespace_name read Only|read write;

6, modify the table space data file:

(Add data file)

Alter tablespace tablespace_name add datafile 'xx.dbf' size xx;

(Delete data file)

Alter tablespace tablespace_name drop datafile 'xx.dbf';

7. Delete Table space:

Drop tablespace tablespace_name [including contents]--including data files deleted

Three "Understanding Table"

1. table -Basic storage unit; two-dimensional structure; rows and columns;

Conventions:

1), each column of data must have the same data type;

2), the class name is unique;

3), the uniqueness of each row of data;

2. data type --character type, numeric type, date type, other type

Character--char (n) [n maximum = 2000], nchar (n) [n maximum = 1000]

--VARCHAR2 (n), NVARCHAR2 (n)

Numeric--number (p,s) [P is a valid number, S is the number of digits after the decimal point]

--float (N)

Date type--date[range: A.D. January 1, 4712 to December 31, 9999 A.D., can be accurate to seconds]

--timestamp[can be accurate to milliseconds]

Other types of--blob[can hold 4GB of data, in binary form to store]

--clob[can store 4GB of data, as a string to store]

1. Create a table:

CREATE TABLE table_name(

column_name datatype,

......

);

2, modify the table:

(add field)

ALTER TABLE table_name add column_name datatype;

(change field data type)

ALTER TABLE table_name modify column_name datatype;

(delete field)

ALTER TABLE table_name drop column column_name;

(Modify field name)

ALTER TABLE table_name rename column old_column_name to new_column_name;

(Modify table name)

Rename table_name to new_table_name;

3. Delete the table:

TRUNCATE TABLE tablename; --Does not delete the table, only empties the data in the TABLE_NAME table, the speed is fast

DROP TABLE table_name; --Delete table

Four "operational data"

1. Insert Data:

INSERT INTO table_name (column1,column2,...) VALUES (value1,value2 ,...);

2. Add a default value to the field:

CREATE TABLE table_name(column1 datatype default sysdate,column2 DataType,...);

ALTER TABLE table_name modify column_name default sysdate;

3. Copy data:

CREATE TABLE table_name1 as select column1,column2,... from table_ Name2;

Insert INTO table_name1 [(column1,...)] Select column1,... from table_name2;

4. Modify the data:

UPDATE table_name set column1=value1,... [where conditions];

5. Delete data:

Delete from table_name [where conditions];

TRUNCATE TABLE table_name;

Five "Constraints"

Role: Define the rules, ensure completeness;

1, non-null constraints

1), add non-null constraints:

CREATE TABLE table_name(column_name datatype not null,...);

ALTER TABLE table_name modify column_name datatype not null;

2), delete the non-null constraint:

ALTER TABLE table_name modify column_name datatype null;

2. PRIMARY KEY constraint [A table can only design one primary key constraint; A PRIMARY KEY constraint can consist of multiple fields (federated primary key or composite primary key)]

1), add a PRIMARY KEY constraint:

CREATE TABLE table_name (column_name datatype primary key,...);

CREATE TABLE table_name (column_name1 datatype,column_name2 datatype,...,

constraint constraint_name PRIMARY Key (column_name1,column_name2,...)); --Create a federated primary key

2), modify the table to add a PRIMARY KEY constraint:

ALTER TABLE table_name add constraint constarint_name primary key (column1 ,column2,...);

3), rename the primary KEY constraint:

Rename constraint old_constraint_name to new_constraint_name;

4), enable | Disable PRIMARY KEY constraint:

ALTER TABLE table_name disable|enable constraint constraint_name;

5), delete the primary KEY constraint:

ALTER TABLE table_name drop constraint constraint_name;

ALTER TABLE table_name drop PRIMARY key [CASCADE];

3, FOREIGN KEY constraints

1), add foreign KEY constraints:

CREATE TABLE table1 (column_name datatype references table2(column_name ),...);

[Table2 column_name needs to be the primary key and the data type must be the same, the value of the foreign key is either contained in the value of the primary table primary key, or is null]

CREATE TABLE table1 (column_name datatype,...,

constraint constraint_name foreight Key (column_name) References table2 (column_name) [ondelete CASCADE]);

2), modify the table to add foreign KEY constraints:

ALTER TABLE table1 add constraint contraint_name foreight key(column_name ) references table2(column_name) [ondelete CASCADE];

3), enable | Disable FOREIGN KEY constraints:

ALTER TABLE table_name disable|enable constraint constraint_name;

4), delete foreign KEY constraint

ALTER TABLE table_name drop constraint constraint_name;

4. Unique Constraint

1), add a unique constraint:

CREATE TABLE table_name (column_name datatype UNIQUE,...);

CREATE TABLE table_name (column_name datatype,...,

constraint constraint_name UNIQUE (column_name));

2), modify the table to add a unique constraint:

ALTER TABLE table_name add constraint constraint_name UNIQUE(column_name );

3), enable | disable UNIQUE constraints:

ALTER TABLE table_name disable|enable constraint constraint_name;

4), delete unique constraint

ALTER TABLE table_name drop constraint constraint_name;

5, check the constraints

1), add CHECK constraint:

CREATE TABLE table_name (column_name datatype Check (expressions),...);

CREATE TABLE table_name (column_name datatype,...,

constraint constraint_name Check (expressions));

2), modify the table to add CHECK constraints:

ALTER TABLE table_name add constraint constraint_name Check (expressions);

3), enable | disable CHECK constraint:

ALTER TABLE table_name disable|enable constraint constraint_name;

4) Delete Check constraint:

ALTER TABLE table_name drop constraint constraint_name;

Oracle Basics-Learning Notes