PLSQL command summary v1.0, plsql command summary v1.0

PLSQL command summary v1.0, plsql command summary v1.0

1. Basic Query

(Table: emp column: ename, Deton, sal, indicates line feed)

1. Check the current user SQL> show user

2. query table structure SQL> desc emp (emp: Table Name) -- query the name, type, and whether it is null.

3. Clear screen SQL> host cls

4. set row width SQL> show linesize, set linesize 120 set column width SQL> col ename for a8, col sal for 9999

5. All expressions containing null are null.

Solution: nvl (a, B) -- if a is null, B is returned. If not null, a is returned.

-- Nvl (a, B, c) if a is null, B is returned; otherwise, c is returned.

6. distinct: Remove duplicate record SQL> select distinct Deton from emp

7. |: connector, concat: Connection Function

SQL> select concat ('hello', 'World') from emp -- Note: The select function must be followed by the from keyword.

8. pseudo table: dual

SQL> select 'hello' | 'World' string from dual, SQL> select ename | 'salary:' | sal information from emp

 

Ii. Filtering and sorting

1. Note: strings are case sensitive, Date Format sensitive such as DD-MON-RR: '17-November-81'

2. Modify the date format SQL> alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd' -- modify the format to 2017-11-81

3. Set the interval: between and -- the small value is placed after the previous shard.

4. in the set: in SQL> select * from emp where deptno in)

-- Corresponding, not in. in addition, if the cooperation contains null, not in cannot be used. Only in

SQL> select * from emp where deptno in (10, 20, null)

5. Fuzzy search

Query employee SQL statements whose names start with "S"> select * from emp where ename like's % '--' % 'indicates n characters

Employee SQL with four characters in the query name> select * from emp where ename like '----' -- '_' indicates a single character

Query SQL statements whose names contain underlines> select * from emp where wname like '% \ _ %' -- '\' indicates escape characters

6. Sort: order by desc: Descending asc: ascending -- by: the post-parameter can directly write numbers, corresponding to the columns in the select list

Multi-column sorting SQL> select * from emp order by deptno desc, sal desc -- desc only applies to the column before it

To ensure null values, you can add nulls last -- the default value of null is the maximum value.

 

Iii. Functions

1. Character Functions

1) SQL> select lower ('Hello World') to lowercase, upper ('Hello World') to uppercase, and initcap ('Hello World') to uppercase

2) substr -- Obtain the substring SQL> select substr ('Hello world', 4) substring from dual

3) length/lengthb character/byte number SQL> select length ('Hello World') character, lengthb ('Hello World') byte from dual

4) instr (a, B) -- Query B SQL> select instr ('Hello world','ll ') in a from dual

5) lpad/rpad -- left/right fill SQL> select lpad ('abcd', 10, '*') -- fill 6 digits in the left of abcd to make the string reach 10 digits

6) trim -- remove the specified character before and after SQL> select trim ('H' from 'Hello World') from dual -- take the middle part of two H

7) replace -- SQL> select replace ('Hello world', 'l', '*') from dual -- replace l *

2. Rounding to round

SQL> select round (45.926, 1) 1, round (45.926, 0) 2, round (45.926,-1) 3, round (45.926,-2)

1--45.9, 2--46, 3--50, 4--0

3. The current time is 10 months after sysdate: add_months (sysdate, 10)

4. Perform different operations based on the job

1) select ename, job, sal,

Case job when 'yuangong' then sal + 800

When 'jingli 'then sal + 1000

Else sal + 500

From emp

2) select ename, job, sal,

Decode (job, 'yuangong', sal + 800,

'Jinglil', sal + 1000,

Sal + 500)

From emp

5. sum: sum (column name) calculate the number of records: count (*/column name) calculate the average: avg (column name)

6. having: multi-row functions can be used instead of where functions.

Select deptno, avg (sal) from emp group by deptno having deptno = 10

 

4. Multi-table join query

1. External Connection SQL> select d. deptno, d. dname, count (e. empno) from emp e, dept d where ~

The left Outer Join will include the last invalid left table in the result, and the right outer join will include the right table.

2. subquery

1) subquery can be used after where select having from in the primary query

2. Except for the top-n issue, it is generally not sorted in the subquery.

 

V. Table operations

1. create table SQL> create table emp00 as select * from emp where deptno = 20

SQL> create table empinfo

Select e. empno, e. ename, e. sal, d. dname from emp e, dept where e. deptno = d. deptno

2. modify column SQL> alter table emp modify ename varchar2 (40)

Delete column SQL> alter table emp drop column photo

Rename column SQL> alter table emp rename column tname to username

Rename table SQL> rename text1 to text2

 

Vi. Others

1. Paging

SQL> select *

From (select rownum r, e1. * from (select * from emp order by sal) e1

Where rownum <= 8)

) Where r> = 5