Oracle Classic Tutorial Learning Notes

Oracle Learning

1. Create a constraint for the table :ALTER TABLE name ADD CONSTRAINT constraint name constraint content

Example: Alter bable infos add constraint un_stunsme unique (stuname)//UNIQUE constraint;

ALTER TABLE scores add constraint Ck_scores_trem CHECK (trem= ' S1 ' OR trem= ' S2 ') the value of//trem can only be S1 or S2;

Add foreign key ALTER TABLE score add constraint Fk_scores_infos_stuid FOREIGN KEY (stuid) REFERENCES infos (STUID);

2, Oracle can be the results of the query based on the table structure and data in the result set form a new table:CREATE Table table name as SELECT statement . If you want to copy the table structure, do not copy the contents of the Add a statement, such as: where 1 = 2, only the table structure will be copied.

3. Oracle can insert the result of the query as a result set into another table:INSERT INTO table SELECT clause .

4. The difference between truncate and delete in Oracle

Same point: All the data in the data table is deleted.

Differences: But truncate is a DDL command, deleted data cannot be recovered, delete is a DML command, deleted data can be recovered through log files , and if many files in the datasheet use truncate faster than delete. But truncate command is more dangerous, in the actual development, use with caution.

5. Set operations in Oracle :INTERSECT: Returns records that are common to two queries; UNION ALL: Returns all records for each query, including duplicate records;Union: Returns all records for each query, excluding duplicate records. minus: Returns the records that were retrieved by the first query minus the records retrieved by the second query.

6. Find out the name, job and salary of the employee with low salary in the EMP table than any salesperson (' salesman '):

Select Ename, JOB, Sal from EMP where Sal<any (SELECT SAL from emp where job= ' salesman ');

7. Pseudo-Columns in Oracle :

In the use of Oracle tables, there are additional columns in the actual table, called pseudo-columns. Pseudo-columns are like columns in a table, but are not stored in a table. A pseudo-column can only be queried and cannot be added or manipulated. pseudo-Columns are: ROWID, ROWNUM;

ROWID: Each row in the table has a physical address in the data file, and the ROWID pseudo-column returns the physical address of the row, and the ROWID value can uniquely identify a row in the table.

ROWNUM: Returns a row number identified by each row in the result set, the first row returns 1, and the ROWNUM pseudo-column allows you to limit the number of rows returned in the query result set. ROWID is generated when a record is inserted, rownum is generated when querying data, ROWID identifies the physical address of the row, RowNum identifies the order of rows in the query result.

For example: Query the name, salary, and work of the top 5 employees with the highest pay:

Select rownum,t.* from (select Ename, JOB, Sal from EMP ORDER by SAL DESC) T WHERE ROWNUM <= 5; Query for records between 5th and 10th in the EMP table: SELECT * FROM (select Rowmun R, ename, JOB, SAL from EMP where ROWNUM <=10) where R > 5;

8. Two commonly used functions in Oracle Database: 1, single-line function: Character function, comb function, conversion function, Date function, 2, aggregate function ; character function: ASCII (x): Returns the ASCII code of the character x; CONCAT (x, Y) : Connection string x and Y;

CSDN Collonn:6;

9, Create a table space , contains two data file size is 10MB,5MB, requires extent size unified 1 m:create tablespace MYSPACE datafile ' d:/a.ora ' size 10M , ' D:/b.ora ' size 5M EXTENT MANAGEMENT LOCAL UNIFORM size 1M;

1) The file name includes the full path and file name;

2) EXTENT MANAGEMENT Local indicates that the tablespace type is: Manage table spaces locally. The local management tablespace requires uniform data partitioning (Extent) size in Oracle, and 3, UNIFORM size specifies the uniform sizes of each partition.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Classic Tutorial Learning Notes