Commands and functions commonly used by Oracle Sql*plus

Oracle common commands and functions, do a collation, for later use, easy to consult.

Common commands:

Connection Sqlplus
Command line to run CMD entry, such as logging in the SYS mode, the command is as follows:
(1). Sqlplus "sys/zhb126 as SYSDBA"

(2). Sqlplus/nolog Connect sys/zhb126 as Sysdba

(3). Sqlplus Scott/tiger

Start a DB instance

Startup-Start the database while starting the control files, data files

Startup Mount-Starts the control file and does not start the data file while starting the database

Startup Nomount--Start a DB instance only to control file loss, at which point the control file cannot be started
To close a DB instance

Shutdown--normal shutdown (waits for all users to disconnect before shutting down)

Shutdown immediate--Recommended (force the user to disconnect immediately after executing the current SQL)

Shutdown transactional--(forces the connection to be disconnected immediately after the current user finishes execution)

Shutdown abort--forced shutdown (DB2 stop force for class DB2)

View Log Archive-start database with startup mount

Archive Log List

ALTER DATABASE Archivelog

ALTER DATABASE OPEN
View commands supported by Sqlplus

Help INDEX
Unlocking user Locks

Executed with the user password set in the first step of installing Oracle (e.g., Sqlplus "sys/zhb126 as SYSDBA")

Alter user Scott account unlock;

Then execute Sqlplus scott/tiger, which will ask you to modify Scott's new password.
Setting command line support spaces

Set Sqlblanklines on
Variable substitution-variable substitution-this process only schedules an execution plan to improve execution efficiency

Sql> SELECT * FROM dept where deptno = &t;

Enter the value of T: 10

Original Value 1:select * FROM dept where deptno = &t

New Value 1:select * FROM dept where deptno = 10
View the commands executed by the buffer (LIST/L)

Sql> L

1* SELECT * FROM dept where deptno = &t

sql> L1

1* SELECT * FROM dept where deptno = &t
retention, execution of buffer files
Sql> Save D:\test.sql--Save the buffer file to the specified file

File D:\test.sql has been created

Sql> @d:\test.sql--Execute the SQL that saved the file
View table structure (DESCRIBE/DESC)
Sql> DESC Dept
Save query output Results

Sql> Spool D:\spool.txt--Open spool and specify save path

sql>/--SQL statement to execute buffer

Sql> spool off--off spool

sql> Edit D:\spool.txt--Open File View save content

Common functions:
Sysdate for system date dual as virtual table
Date Function[Focus on the first four date functions]
1.add_months[return date plus (minus) The date after the specified month (before)
Select Sysdate s1,add_months (sysdate,10) S2,
Add_months (sysdate,5) S3 from dual;
2.last_day [Returns the date of the last day of the month]
Select Last_day (sysdate) from dual;
3.months_between[returns the number of months between dates]
Select Sysdate S1, Months_between (' January-April -04 ', sysdate) S2,
Months_between (' January-April-04 ', ' January-February -04 ') S3 from dual
4.next_day (D,day): Return to next week's date, day is 1-7 or Sunday-Saturday, 1 means Sunday
Select Sysdate s1,next_day (sysdate,1) S2,
Next_day (sysdate, ' Sunday ') S3 from DUAL
5.round[rounded to the nearest date] (day: rounded to the nearest Sunday)
Select Sysdate S1,
Round (sysdate) S2,
Round (sysdate, ' Year ') of year,
Round (sysdate, ' Month ') month,
Round (sysdate, ' Day ') Day from dual
6.trunc[truncated to the nearest date]
Select Sysdate S1,
Trunc (sysdate) S2,
Trunc (Sysdate, ' Year ') of year,
Trunc (sysdate, ' Month ') month,
Trunc (sysdate, ' Day ') Day from dual
7. Returns the latest date in the list of dates
Select Greatest (' January-January-04 ', ' April-January-04 ', ' October-February -04 ') from dual

character Functions(can be used for literal characters or database columns)
1. String interception
Select substr (' abcdef ', 1,3) from dual
2. Find the substring location
Select InStr (' Abcfdgfdhd ', ' FD ') from dual
3. String connection
Select ' HELLO ' | | ' Hello world ' from dual;
4.1) Remove spaces from the string
Select LTrim (' abc ') s1,
RTrim (' Zhang ') s2,
Trim (' Zhang ') S3 from dual
2) Remove the preamble and suffix
Select Trim (leading 9 from 9998767999) S1,
Trim (trailing 9 from 9998767999) S2,
Trim (9 from 9998767999) S3 from dual;
5. Returns the ASCII value of the first letter of the string
Select ASCII (' a ') from dual
6. Returns the letter corresponding to the ASCII value
Select CHR from dual
7. Calculating string Lengths
Select Length (' abcdef ') from dual
8.initcap (capitalized), lower (lowercase), upper (uppercase)
Select lower (' ABC ') s1,
Upper (' def ') S2,
Initcap (' EFG ') S3 from dual;
9.Replace
Select replace (' abc ', ' B ', ' XY ') from dual;
10.translate
Select Translate (' abc ', ' B ', ' xx ') from dual; --X is 1 bits
11.lpad [Left refill] rpad [right padding] (for controlling output format)
Select Lpad (' Func ', s1, ' = '), Rpad (' func ', '-') ' S2 from dual;
Select Lpad (dname,14, ' = ') from dept;
12.decode[Implement if: Then logic]
Select Deptno,decode (deptno,10, ' 1 ', 20, ' 2 ', 30, ' 3 ', ' other ') from dept;

numeric functions
1. Rounding function (Ceil up, floor down rounding)
Select Ceil (66.6) N1,floor (66.6) N2 from dual;
2. exponentiation (Power) and square root (sqrt)
Select Power (3,2) n1,sqrt (9) N2 from dual;
3. Finding redundancy
Select mod (9,5) from dual;
4. Return a fixed number of decimal digits (round: Rounding, Trunc: direct truncation)
Select Round (66.667,2) N1,trunc (66.667,2) N2 from dual;
5. The sign of the return value (positive return is 1, negative is-1)
Select sign ( -32), sign (293) from dual;

conversion function
1.to_char () [Convert date and number types to character type]
1) Select To_char (sysdate) s1,
To_char (Sysdate, ' Yyyy-mm-dd ') s2,
To_char (sysdate, ' yyyy ') S3,
To_char (sysdate, ' Yyyy-mm-dd hh12:mi:ss ') S4,
To_char (sysdate , ' Hh24:mi:ss ') S5,
To_char (sysdate, ' Day ') S6 from dual;
2) Select Sal,to_char (Sal, ' $99999 ') N1,to_char (Sal, ' $99,999 ') n2 from EMP
2. To_date () [Convert character type to date type]
INSERT INTO EMP (empno,hiredate) VALUES (8000,to_date (' 2004-10-10 ', ' yyyy-mm-dd '));
3. To_number () converted to numeric type
Select To_number (To_char (sysdate, ' Hh12 ')) from dual;//number of hours displayed as numbers

Other functions
User
Returns the user name of the login
Select User from Dual;
Vsize:
The number of bytes required to return an expression
Select Vsize (' HELLO ') from dual;
NVL (EX1,EX2):
EX1 value is null returns EX2, otherwise the value itself is returned EX1 (common)
Example: If an employee does not have a commission, 0 is displayed, otherwise the Commission is displayed
Select COMM,NVL (comm,0) from EMP;
Nullif (EX1,EX2):
value is equal to NULL, otherwise the first value is returned
Example: If wages and commissions are equal, the display is empty, otherwise the wages are displayed
Select Nullif (Sal,comm), sal,comm from EMP;
COALESCE:
Returns the first non-empty expression in a list
Select Comm,sal,coalesce (comm,sal,sal*10) from EMP;
NVL2 (EX1,EX2,EX3):
If Ex1 is not empty, display ex2, otherwise ex3
such as: View the names of employees with commissions and their commissions
Select NVL2 (Comm,ename, ') as Havecommname,comm from EMP;

Grouping Functions
Max min avg count sum
1. The entire result set is a group
1) 30 of the department's maximum wage, minimum wage, average wage, total number, number of people working, number of jobs and sum of wages
Select Max (ename), Max (SAL),
Min (ename), Min (Sal),
AVG (SAL),
Count (*), COUNT (Job), COUNT (Distinct (job)),
Sum (SAL) from EMP where deptno=30;
2. Groupings with GROUP by and having
1) The highest wage, minimum wage, total number, number of workers, number of jobs and sum of wages in accordance with the Department group
Select Deptno, Max (ename), Max (SAL),
Min (ename), Min (Sal),
AVG (SAL),
Count (*), COUNT (Job), COUNT (Distinct (job)),
Sum (SAL) from the EMP group by DEPTNO;
2) Department 30 maximum wage, minimum wage, total number, number of jobs, number of jobs and sum of wages
Select Deptno, Max (ename), Max (SAL),
Min (ename), Min (Sal),
AVG (SAL),
Count (*), COUNT (Job), COUNT (Distinct (job)),
Sum (SAL) from the EMP group by DEPTNO have deptno=30;
3.stddev returns the standard deviation of a set of values
Select Deptno,stddev (SAL) from the EMP group by DEPTNO;
Variance returns the variance difference of a set of values
Select Deptno,variance (SAL) from the EMP group by DEPTNO;
4. Group BY with rollup and cube operators
Rollup statistics and final subtotals by the first column in a group
Cube statistics and final subtotal by all columns grouped
Select Deptno,job, sum (SAL) from the EMP group by Deptno,job;
Select Deptno,job, sum (SAL) from the EMP Group by Rollup (Deptno,job);
Cube generates statistics and final subtotals for all columns within a group
Select Deptno,job, sum (SAL) from the EMP Group by Cube (Deptno,job);

Commands and functions commonly used by Oracle Sql*plus