The table.sql file used for testing was uploaded to my resource library... If you want to use it, you can use the following three tables for testing only. The specific contents of each table are as follows:

Department table

Area table


Employee table

There are too many specific contents. Some contents are displayed here

1. Basic query

1.1 basic use

Example 1: query s_ All records in dept table

select * 
from s_dept;

Example 2: query s_ ID, name, region in dept table_ ID three fields

select id,name,region_id
from s_dept;

Example 3: view all employees, including last_name and salary

select id,last_name,salary
from s_emp;

1.2 add basic operation

Example 1: view the id, name and annual salary of each employee

select id,last_name,salary*12
from s_emp;

Example 2: view the employee id, name and annual salary of each employee after the monthly salary increases by 100

select id,last_name,(salary+100)*12;
from s_emp;

1.3 alias fields

The new name is written right after the name
Syntax:

select old_column[as] new_column_name from table_name;

The as in brackets is optional

Example 1: view the employee id, name and annual salary of an employee. The annual salary column is annual

select id,last_name,salary*12 as annual
from s_emp;

perhaps

select id,last_name,salary*12 annual
from s_emp;

1.4 splicing

Syntax:

select col_name||'spe_char'||col_name from table_name；

Example 1: view the employee id and full name of the employee

select id,first_name||last_name
from s_emp;

Example 2: view the employee id, full name and position name of an employee. The full name and position name are combined into one column and displayed in the format of name and position name

select id,first_name||' '||last_name||','||title as name
from s_emp;

||Indicates concatenation of fields, such as first above_ name||’ ‘||last_name means first_name and space '' and last_name is spliced together to form a new field

1.5 replacement

Use the nvl function to replace null
Usage:

select nvl(col_name,change_value) from table_name;

Example: query the employee id, name and commission of all employees. If the Commission is blank, it will be displayed as 0

select id,last_name,nvl(commission_pct,0) commission_pct
from s_em;

1.6 weight removal

distinct keyword can remove duplicate data

Syntax:

select distinct col_name,col_name...
from table_name;

The distinct keyword can only be placed after the select keyword

If multiple columns appear after the distinct keyword, it means that multiple columns are jointly de duplicated, that is, multiple columns are considered to be duplicate records only when their values are the same.

For example, you can view the position name and department id of all employees. The same position and department can only be displayed once

select distinct dept_id,title
from s_emp;

1.7 adjustable field width

Use format to adjust the width of query results.
When the display result width has not been adjusted

select id,last_name
from s_emp;

The results are as follows:

Use format to adjust last_ The width occupied by the name column is actually the number of "-"
For example:

//Indicates last_ There are 15 "-" below the name column
column last_name format a15;
//Or abbreviated as
col last_name for a15;

//After that, check the format
select id,last_name
from s_emp;

The results are as follows:

Clear the set format

clear column
//perhaps
clear col

format can only set field columns of character type, otherwise it will be garbled

2. Condition query

Syntax:

select col_name,...
from table_name
where Screening conditions

Restrict query conditions and use the where clause;
Conditions can be multiple, and logical operators or parentheses are used for logical integration of conditions;
The where clause has the highest priority;

Logical comparison operator = > < > = < ==
Not equal operator, the following three are not equal to the meaning, often used is=
!= <> ^=

Filter by conditional expression

Conditional expression: = > < > = < ==
Not equal operator, the following three are not equal to the meaning, often used is=
!= <> ^=

Example: view the id and name of an employee whose salary is less than 1000

select id,last_name,salary
from s_emp
where salary<1000;

Filter by logical expression

Logical operator: and or not
Function: connects conditional expressions. It can be used when there are multiple conditions

Note: and has higher priority than or
be careful:
&It is used to create a temporary variable. Whenever you encounter this temporary variable, you will be prompted to enter a value
&&It is used to create a persistent variable. When this variable is referenced with the & & command, the user will not be prompted to type the value every time the variable is encountered, but only once when it is encountered for the first time
Unlike in mysql, and is equal to & &, or is equal to | and not is equal to!, But there are differences in oracle

Example 1: view the id and name of an employee whose department id is 41 and whose position name is Stock Clerk

select id,last_name,dept_id,title
from s_emp
where
dept_id = 41
and
title = 'Stock Clerk';

Example 2: view the id and name of an employee whose department is No. 41 or 44 and whose salary is greater than 1000

select id,last_name,dept_id,title
from s_emp
where salary > 1000
and
(dept_id = 41
or
dept_id = 44
);

Example 3: view the employee id and name of department 41 with salary greater than 1000, or department 44

select id,last_name,dept_id,title
from s_emp
where salary > 1000and
dept_id = 41
or
dept_id = 44;

The results of example 3 and example 2 are different. The difference is whether there are parentheses in the condition

Fuzzy query

Between... and... Means between two values

Example: check the employee id and name whose salary is between 700 and 1500

select id,last_name,salary
from s_emp
where salary between 700 and 1500;

//It's actually equal to the following
select id,last_name,salary
from s_emp
where 
salary>=700
and
salary<=1500;

in() indicates that the value is in a specified list

Example: view the salary of employees 1, 3, 5, 7 and 9

select id,last_name,salary
from s_emp
where id in(1,3,5,7,9);

like is used when the value is imprecise

Usually used in conjunction with wildcards:
%, wildcard 0 to more than one character
-, with one character, and there must be one character
\, escape character, which needs to be specified with escape keyword. Transfer character can only escape the following character

Example 1: view the id and salary of an employee whose name begins with the letter C

select id,last_name,salary
from s_emp
where last_name like 'C%';

Example 2: check the employee id and salary whose name is no less than 5 and the fourth letter is n

select id,last_name,salary
from s_emp
where last_name like '___n_%';

Example 3: change one of the employee names_ Employee id and salary

select id,last_name,salary
from s_emp
where last_name like '%\_%' escape '\';

is null and is not null

Note: it is used when the judgment value is null. The judgment of null value cannot use the equal sign
Example: view the id and name of the employee whose name is empty

select id,last_name,commission_pct
from s_emp
where commission_pct is null;

3. Sort query

Syntax:

select col_name
from table_name
order by col_name [asc|desc];

be careful:
(1) The order by statement only displays and adjusts the query records and does not change the query results, so the execution right is the lowest and the last execution is performed
(2) The default value for sorting is asc: ascending, desc: descending
(3) If there are multiple columns to sort, the premise for the subsequent columns to sort is that there are duplicate (same) values after the previous columns are sorted.

Example: view the employee's id, name and salary. They are displayed in descending order of salary. If the salary is the same, they are listed in ascending order of name

select id,last_name,salary
from s_emp
order by salary desc,last_name;

First arrange the first column in ascending order. If the first column has duplicate values, then arrange the second column in descending order, and so on

4. Functions

Many common functions are built in oracle database

4.1 single line function

It can also be called a single valued function. Each row of data (a field value) will return a result.

Dumb Watch

In Oracle, there is a special table: dual
Dual is called a DUMMY table. It is a virtual table with single row and single column. It is automatically created by Oracle. This table has only one column: DUMMY. The data type is VERCHAR2(1). There is only one data 'X' in the dual table. Oracle has internal logic to ensure that there is always only one data in the dual table.
For example:

In practice, the dual table is mainly used to select system variables or find the value of an expression, because dual is used to construct the completed query

For example, the result of query expression 1 + 1

select 1+1 from dual;

According to the requirements of sql statements, there is no way to query without a table, and the expression 1 + 1 does not belong to any table, so there is the concept of dummy table dual. However, note that only oracle database has this dummy table dual

4.1.1 character function

The content in [] indicates that it can be added or not

1. ASCII(X), returns the ASCII code of the character X

select ascii('a') as result from dual;

2. CONCAT(X,Y), connection strings X and Y

select concat('hello','world') as result from dual;

3. Instr (x, str, [, start] [, n]) to find str from X. you can specify to start from start or n

Example 1:

select instr('Hello World','o') as result from dual;

Starting with the first letter H, o is the fifth letter

Example 2:

select instr('Hello World','o',6) as result from dual;

Note that here we start with the sixth letter space, and there is an O letter before and after the space. Here we get the following o letter, which is the position of the eighth letter

Example 3:

select instr('Hello World','o',-1) as result from dual;

Here we start from the last one and look forward from the back, but the order of each letter has been fixed, so we are looking for the serial number of the second o, that is, the eighth position from the front to the back

4. LENGTH(X) returns the length of X

select length('world') from dual;

5. LOWER(X), convert X to lowercase

select lower('HELLO') from dual;

6. UPPER(X), convert X to uppercase

select upper('hello') from dual;

7. INITCAP(X), the first letter of X is converted to uppercase, and other letters are lowercase

 select initcap('hello') from dual;

8. LTRIM(X[,TRIM_STR]), truncate the left side of X_ STR string, blank space is truncated by default

 select LTRIM('--hello--','-') from dual;

Here is to cut off all the '-' on the left

9. RTRIM(X[,TRIM_STR]), cut off the trim on the right side of X_ STR string, blank space is truncated by default

 select RTRIM('--hello--','-') from dual;

Here is to cut off all the '-' on the right

10. TRIM([TRIM_STR FROM X), truncate the trim_str string on both sides of X, and truncate the space by default

11. REPLACE(X,old,new). Find old in X and replace with new

select replace('cast','a','o') from dual;

12. SUBSTR(X,start[,length]) returns the string of X, starting from start (including start), intercepting length characters. When the default length is, it defaults to the end

 select substr('hello',2,3) as result from dual;

4.1.2 digital function

Functions that specifically operate on numbers. Common digital functions include:

Special attention should be paid to the following:
1. ROUND(X[,Y]), X is rounded in position Y, the first parameter indicates the number to be rounded, and the second parameter indicates the bit to be reserved

 select round(31.415,2) as result from dual;

When reserved to bits

 select round(31.415,0) as result from dual;

If the second parameter is not written, it defaults to 0, which means it is reserved to bits

When you keep it to the tenth place,

select round(31.415,-1) as result from dual;

2. TRUNC(X[,Y]), X is truncated in the Y-th bit. trunc and round are used in the same way, but trunc will only round off and will not carry

 select trunc(31.415,2) as result from dual;

3. MOD(X,Y), the remainder of X divided by Y. the first parameter represents the number to be taken, and the second parameter represents parameter 1 and who to take the remainder

select mod(10,3) from dual;

4.1.3 date function

sysdate is a keyword used to represent the current time in Oracle, and can be used to participate in time operation

//Displays the current time
select sysdate from dual;

//Show this time tomorrow
select sysdate + 1 from dual;
//Show this time yesterday
select sysdate - 1 from dual;
//Show this date after 1 hour
select sysdate + 1/24 from dual;

When sysdate participates in the addition and subtraction of time, the unit is day

In particular, in different session environments in oracle, the default format of date data is also different, as shown below

In Chinese environment:

 alter session set nls_language='simplified chinese';
 select sysdate from dual;

In English environment:

alter session set nls_language=english;
select sysdate from dual;

Common date functions:
1,months_between
For example, how many months is the difference between 30 days and now

select months_between(sysdate+30,sysdate) as result 
from dual;

2,add_months
For example, specify a date and push back 2 months
Chinese environment

select add_months('01-10 month-2020',2) as result from dual;

English environment

select add_months('01-OCT-2021',2) as result from dual;

Note that this number can also be negative, indicating the number of months before

3,next_day
For example: what day is the next Friday closest to the current time
In Chinese environment:

select next_day(sysdate,'Friday') from dual;

In English environment:

select next_day(sysdate,'FRIDAY') from dual;

4,last_day
For example, the last day of the month in which the current date is located (month end)

select last_day(sysdate)	
from dual;

5,round
For example, round the current date to the month
Suppose today's date is 22-november-21

select round(sysdate,'MONTH') from dual;

When the 'Day' is greater than 15, the 'month' will be carried

For example, round the current date to the year

select round(sysdate,'YEAR') from dual;

When the 'month' is greater than 6, the year will be rounded

6,trunc
Intercept the date, which is similar to round, but only discards and does not carry.

select trunc(sysdate,'MONTH') from dual;

You can see that the month part is not carried in the same way as round

4.2 conversion function

You can convert one type of data into another. There are three main types
1. TO_CHAR, convert a number or date function into characters
2. TO_NUMBER to convert characters to numbers
3. TO_DATE to convert characters to dates

4.2.1 TO_CHAR

Number to character

Common formats:

For example:

select to_char(salary,'$999,999,00') as result from s_emp;

result:
RESULT

  $25,00
  $14,50
  $14,00
  $14,50
  $15,50
  $12,00
  $12,50
  $11,00
  $13,00
  ......

select to_char(salary,'L999,999.00') as result from s_emp;

result
RESULT

      ￥2,500.00
      ￥1,450.00
      ￥1,400.00
      ￥1,450.00
      ￥1,550.00
      ￥1,200.00
      ￥1,250.00
      ￥1,100.00
      ￥1,300.00
		.......

select to_char(-10,'999PR') as result from dual;

Date to character

Common formats:

For example:

select to_char(sysdate,'yyyy mm MONTH mon MON D DD DDD DAY DY') from dual;

select to_char(sysdate,'dd-mm-yy') from dual;

select to_char(sysdate,'yy-mm-dd hh:mi:ss') from dual;

4.2.2 TO_NUMBER

Convert characters to numbers

select to_number('1000')
from dual;

Note, however, that characters such as abc cannot be converted to numbers

4.2.3 TO_DATE

Convert character to date

select to_date('10-12-2022','dd-mm-yyyy') as result from dual;

alter session set nls_language='simplified chinese';
select to_date('25-5 month-22','dd-month-yy') as result from dual;

select to_date('22/5 month/25','yy/month/dd') as resultfrom dual;

alter session set nls_language=english;
select to_date('25-MAY-22','dd-MONTH-yy') as result from dual;

4.2 aggregate function

It can also be called multi line function, grouping function and group function. It can operate multi line data and return a result. It is generally used in combination with group grouping. Of course, it can also be used alone. By default, all data is a group.

The aggregation function can operate multiple rows of data and calculate a result as required. Generally, in combination with grouping operation, a pile of data is divided into several different groups according to certain conditions, and then the aggregation function is executed for each group. Finally, each group obtains a result.

Common grouping functions:
avg, average
count to calculate how many pieces of data there are
max, max
min, find the minimum value
Sum, sum

Find the average salary of employees, the total salary of all employees, the maximum salary, the minimum salary and the total number of employees

select avg(salary) from s_emp;
select sum(salary) from s_emp;
select max(salary) from s_emp;
select min(salary) from s_emp；
select count(*) from s_emp;