Common commands and functions of Oracle SQL * plus, oracleplus

Common commands and functions of Oracle SQL * plus, oracleplus

Common commands and functions in Oracle are sorted out for future use for convenient reference.

Common commands:

Connect to sqlplus
Run cmd in the command line to enter. For example, to log on in 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 database instance

Startup -- start the control file and data file while starting the database

Startup mount -- start the control file and data file while starting the database

Startup nomount -- only start the database instance to control file loss. In this case, the control file cannot be started.
Shut down database instances

Shutdown -- close normally (wait until all users are disconnected)

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

Shutdown transactional -- (force the current user to disconnect immediately after execution)

Shutdown abort -- force shutdown (db2 stop force)

View the log archiving method -- start the database with startup mount

Archive log list

Alter database archivelog

ALTER DATABASE OPEN
View commands supported by sqlplus

Help INDEX
Unlock user lock

Log On with the user password set in step 1 during oracle Installation (for example, sqlplus "sys/zhb126 as sysdba") and run

Alter user scott account unlock;

Then execute sqlplus scott/tiger and you will be asked to modify scott's new password.
Set the command line to support spaces

Set sqlblanklines on
Variable Substitution-variable substitution. Only one execution plan is arranged in this process, which improves the execution efficiency.

SQL> select * from dept where deptno = & t;

Input t value: 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
Buffer file persistence and execution
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 statement for saving the file
View the table structure (describe/desc)
SQL> desc dept
Save query output results

SQL> spool D: \ spool.txt -- enable spool and specify the Save path

SQL>/-- execute the SQL statement of the buffer zone

SQL> spool off -- disable spool

SQL> edit d: \ spool.txt -- open the file and view the saved content

Common functions:
Sysdate indicates that the system date dual is a virtual table.
Date Functions[Focus on the first four date functions]
1. add_months [returns the date after the specified month plus (minus) (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 ('1-January 1, April-04 ', sysdate) S2,
Months_between ('1-April-04 ', '1-February-04') S3 from dual
4. next_day (d, day): returns the date of the next week. day is 1-7 or Sunday-Saturday. 1 indicates Sunday.
Select sysdate S1, next_day (sysdate, 1) S2,
Next_day (sysdate, 'sunday') S3 FROM DUAL
5. round [round to the nearest date] (day: round to the nearest Sunday)
Select sysdate S1,
Round (sysdate) S2,
Round (sysdate, 'Year') year,
Round (sysdate, 'month') month,
Round (sysdate, 'day') day from dual
6. trunc [truncation to the closest date]
Select sysdate S1,
Trunc (sysdate) S2,
Trunc (sysdate, 'Year') year,
Trunc (sysdate, 'month') month,
Trunc (sysdate, 'day') day from dual
7. Return the latest date in the date list
Select greatest ('01-January-04 ', '04-January-04', '10-February-04 ') from dual

Character Functions(Can be used for literal characters or database columns)
1. String Truncation
Select substr ('abcdef', 1, 3) from dual
2. Locate the substring
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 leading 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. Return the letter corresponding to the ascii Value
Select chr (97) from dual
7. Calculate the string length
Select length ('abcdef ') from dual
8. initcap (uppercase for the first letter), lower (smaller write), and upper (greater capital)
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 bit
11. lpad [left filling] rpad [Right filling] (used to control the output format)
Select lpad ('func', 15, '=') s1, rpad ('func', 15, '-') s2 from dual;
Select lpad (dname, 14, '=') from dept;
12. decode [Implement the if... then logic]
Select deptno, decode (deptno, 10, '1', 20, '2', 30, '3', 'others') from dept;

Numeric Functions
1. Take the integer function (ceil rounded up and floor rounded down)
Select ceil (66.6) N1, floor (66.6) N2 from dual;
2. power and square root (sqrt)
Select power (3, 2) N1, sqrt (9) N2 from dual;
3. Remainder
Select mod (9,5) from dual;
4. Return a fixed number of decimal places (round: rounding, trunc: truncation)
Select round (66.667, 2) N1, trunc (66.667, 2) N2 from dual;
5. symbol of the return value (positive number is returned as 1, negative number is-1)
Select sign (-32), sign (293) from dual;

Conversion functions
1. to_char () [convert the date and number types to character types]
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 the character type to the date type]
Insert into emp (empno, hiredate) values (8000, to_date ('2017-10-10 ', 'yyyy-mm-dd '));
3. to_number () to numeric type
Select to_number (to_char (sysdate, 'hh12') from dual; // number of hours displayed

Other functions
User:
Return the Login User Name
Select user from dual;
Vsize:
Returns the number of bytes required by the expression.
Select vsize ('hello') from dual;
Nvl (ex1, ex2 ):
If the ex1 value is null, ex2 is returned. Otherwise, ex1 (commonly used) is returned)
For example, if an employee has no commission, 0 is displayed; otherwise, the Commission is displayed.
Select comm, nvl (comm, 0) from emp;
Nullif (ex1, ex2 ):
Returns NULL if the value is equal. Otherwise, the first value is returned.
For example, if the salary is equal to the Commission, it is blank; otherwise, the salary is displayed.
Select nullif (sal, comm), sal, comm from emp;
Coalesce:
Returns the first non-empty expression in the list.
Select comm, sal, coalesce (comm, sal, sal * 10) from emp;
Nvl2 (ex1, ex2, ex3 ):
If ex1 is not empty, ex2 is displayed; otherwise, ex3 is displayed.
For example, check the name of an employee with a Commission and their commission.
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) obtain the highest salary, minimum wage, average salary, total number of employees, number of jobs, and total salary of Department 30.
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. group with group by and having
1) Calculate the highest wage, minimum wage, total number of employees, number of employees, number of types of work, and total wage by group of departments
Select deptno, max (ename), max (sal ),
Min (ename), min (sal ),
Avg (sal ),
Count (*), count (job), count (distinct (job )),
Sum (sal) from emp group by deptno;
2) highest salary of Department 30, minimum wage, total number of employees, number of jobs, and total salary
Select deptno, max (ename), max (sal ),
Min (ename), min (sal ),
Avg (sal ),
Count (*), count (job), count (distinct (job )),
Sum (sal) from emp group by deptno having deptno = 30;
3. stddev returns the standard deviation of a set of values
Select deptno, stddev (sal) from emp group by deptno;
Variance returns the variance difference of a set of values
Select deptno, variance (sal) from emp group by deptno;
4. Group By with rollup and cube Operators
Rollup performs statistics and subtotal based on the first column of the group.
The cube calculates statistics based on all the columns in the group and returns the subtotal.
Select deptno, job, sum (sal) from emp group by deptno, job;
Select deptno, job, sum (sal) from emp group by rollup (deptno, job );
Cube generates statistics of all columns in the group and the final Subtotal
Select deptno, job, sum (sal) from emp group by cube (deptno, job );