Collation of OracleDBA Learning Courses

Database Management Tool SQL * Plus start SQL * PLUS tool Cmd-gt; sqlplusnologConnsystemmanagerDesc table name View table structure Column

Database Management Tool SQL * Plus start SQL * PLUS tool Cmd-gt; sqlplus/nolog Conn system/manager Desc table name View table structure Column

Database Management Tool SQL * Plus
Start SQL * PLUS

Cmd-> sqlplus/nolog

Conn system/manager

View table structure by Desc table name

Column name for 9999 format Column

Column name for a99 format

List to view the instructions in the cache;

Del clears the last row of the cache

Run or/view the running cache zone command.

Set line 120 sets the width of the entire command line to 120 characters

Set pagesize 20 SET to display 20 rows per page

Save d: \ select _ emp. SQL Save the script file

@ D: \ select _ emp. Run the script file in SQL

Edit d: \ select _ emp. SQL Edit script file

Get d: \ select _ emp. SQL View Script File

Spool d: \ d. SQL

Spool off: output the command to the specified file.

SQL language Overview
Structured Query Language

Category

1. Data Query

2. Data operation DML

3. Data Definition DDL

CREATE ALTER DROP RENAME TRUNCATE

4. Transaction Control COMMIT ROLLBACK

5. Data Control DCL grant revoke

Conditional expressions
CASE expression for logical judgment

The DECODE function has the same function as the CASE expression.

SQL> SELECT ename, job, sal, DECODE (JOB, 'salesman', 1.20 * sal,

'Manager', 1.30 * sal,

'Analyst', 1.40 * sal,

Sal

)

LAST_Salary

FROM emp

Order by job;

SQL complex single-row Functions

LOWER () converts the character to lowercase

Upper () capital conversion

INITCAP uppercase letters

CONCAT connection string

INSTR ('My name ias ns ', 'My ')

The LPAD () RPAD () query structure is supplemented by characters

Substr ('My nasdf is sdfsdf ', 12) truncates a string

Length () String Length

Replace () replacement function

TRIM ()

Round () rounding rules

Mod () returns the remainder.

Date Functions

Alter session set NLS_date_lanauage = 'amercan ';

Sysdate Function

MONTHS_BETWEEN ()

ADD_MONTHS

NEXT_DAY (date, string)

LAST_DAY (date)

Grouping Functions
AVG, SUM, MAX, MIN, count, distinct

NVL (COL, 0)

NVL2 (EXPR1, EXPR2, EXP3)