Oracle Classic Tutorial Learning Notes

Oracle Learning

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

Demo sample: 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;

Join the 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 to form a new table:CREATE Table table name as SELECT statement。

Suppose that you want to copy a table structure, and not copy the contents of it, add a statement that is not valid. For example: where 1 = 2, only the table structure is replicated.

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

4. Differences between truncate and delete in Oracle

Same point: All the data in the data table can be deleted.

Different points: But truncate is a DDL command, the deleted data cannot be recovered; Delete is a DML command. The deleted data can be recovered from the log file, and if there are very many files in the data table, use truncate faster than delete.

But truncate command is more critical, in the actual development, careful use.

5. Set operations in Oracle :INTERSECT: Returns the records owned by two queries; UNION All: Returns all records for each query. contains repeated records;UNION: Returns all records for each query, without repeating 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 other salesperson (' salesman '):

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

7. Pseudo-Columns in Oracle :

During the use of Oracle's tables. Other additional columns in the actual table are called pseudo-columns.

Pseudo-columns are like columns in a table, but are not stored in a table. Pseudo-Columns 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 uniquely identifies a row in the table.

ROWNUM: Returns a row number identified by each row in the result set, and the first row returns 1. The rownum pseudo-column enables you to limit the number of rows returned in a 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, and RowNum identifies the order of the rows in the query results.

For example: The name, salary, and work of the top 5 employees with the highest wages:

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 kinds of functions commonly used 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 . Including two data file sizes are each 10MB. 5MB. Requires EXTENT size unity of 1 m:CREATE tablespace MYSPACE datafile ' d:/a.ora ' size 10M, ' d:/b.ora ' size 5M EXTENT MANAGEMENT LOCAL UN Iform SIZE 1M;

1) The file name contains 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 a uniform size of data partitions (Extent) in Oracle, and 3, UNIFORM size specifies the uniform size of each partition.

Oracle Classic Tutorial Learning Notes