Oracle Study Notes-(2)

Oracle Study Notes --- (2)

II

Implement Chinese-English character set conversion under sqlplus

Alter session set
Nls_language = 'American ';
Alter session set nls_language = 'simplified
Chinese ';

Key knowledge points:
I. Operations on tables

1) create a table
Create Table Test
Select * from Dept; -- copy data and structure from a known table
Create Table Test as select *
From dept where 1 = 2; -- copy the structure from a known table but does not include data
2) insert data:
Insert
Into test select * from Dept;

Ii. Operators

Arithmetic Operator: + -*/
It can be used in select statements.
Join OPERATOR: | select deptno | dname from
Dept;
Comparison OPERATOR: >>=! = <
<= Like between is null in
Logical operators: Not and or

Set operators: intersect, Union, Union all, minus

Requirement: the number of columns corresponding to the set is the same as the data type.
The query cannot contain long columns.

The label of the column is the label of the first set.
When order by is used, the position sequence number must be used. column names cannot be used.

For example, use of the set operator:
Intersect, Union, Union all, minus
Select *
From EMP intersect select * from EMP where deptno = 10;
Select * from
EMP minus select * from EMP where deptno = 10;
Select * from EMP where
Deptno = 10 Union select * from EMP where deptno in (); -- duplicate rows not included

Select * from EMP where deptno = 10 Union all select * from EMP where
Deptno in (); -- includes duplicate rows

Iii. Common
Oracle Functions
Sysdate is the system date.
Dual is a virtual table
1) 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-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, and 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-December-04 ', '04-December-04', '10-December-04 ') from
Dual

2) 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 in 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, lower, and upper)

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 fill] (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 if... then logic]
Select
Deptno, decode (deptno, 10, '1', 20, '2', 30, '3', 'others') from
Dept;

3) digital 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;

Iv) 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

5) 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;

6) 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, and number of employees of the Department 30
, number of jobs and total 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. group with
and having

1) Calculate the highest salary, minimum wage, total number of employees, number of employees, number of jobs, and total salary by Department group.
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) maximum salary, minimum wage, total number of employees, number of jobs, and total salary of Department 30
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 between a set of values
select deptno, variance (SAL) from EMP group by
deptno;

4. Group by with rollup and cube Operators
Rollup
Calculate and subtotal based on the first column of the Group
Cube
Statistics by all the columns in the group and the final 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
Generate Statistics for all columns in the group and the final Subtotal

Select deptno, job, sum (SAL) from EMP Group
By cube (deptno, job );

Iv. Temporary table

Tables that exist only during sessions or during transaction processing.
The temporary table dynamically allocates space when inserting data.

Create
Global temporary
Table temp_dept
(DNO number,
Dname varchar2 (10 ))
On
Commit Delete rows;

Insert into temp_dept
Values (10, 'abc ');
Commit;
Select * from
Temp_dept; -- no data is displayed and data is automatically cleared.

On commit preserve rows: The table can always exist (retain data) during the session)

On commit Delete rows: clear data at the end of the transaction (table data is automatically deleted at the end of the transaction)