Requirements: How many departments does the company have?
select department_id from employees;//This code will find 107 records, there is a problem of department duplication!
select distinct department_id from employees;
Thinking: Is there any problem with the following code?
select employee_id,last_name,distinct department_id from employees;
Answer: There will be a problem (an error) and the cause of the error, because there are 107 data in the employee_id and last_name columns during the execution of the code.
After deduplication of department_id, there are only 12 data, the number is not right! So it was wrong! Summary: Be cautious when using distinct...
Concept: Single data in, single out result (single in, single out)
Classification learning:
1). Character function
2). Number function
3). Date function
4). Conversion function
5). General function
Character function:
1). Case conversion functions: 3
①.lower(m): Convert all characters in the string to all lowercase
②.upper(m): Convert all characters in the string to all uppercase
③.initcap(m): Convert the first letter in the string to uppercase, and the remaining letters to lowercase
Code:
select lower('ORACLE'),upper('oracle'),initcap('oRACLE')from dual;
Requirement: query the information of the person named Bell?
select *from employees where lower(last_name)='bell';
select *from employees where upper(last_name)='BELL';
select *from employees where initcap(last_name)='Bell';
2). Character control functions: 8
①.concat(m,n): concatenate string m and string n to get a longer new string
Code:
select concat('Hello','World')from dual;
select concat(last_name,first_name)from employees;
②.length(m): get the length of a certain data
Code:
select length('python'),length(123456)from dual;
select length(employee_id),length(first_name),length(hire_date)from employees;
③.substr(x,y,z): intercept x from position y to length z to get a new string and return it to the program
Parameter explanation:
x: Original string data
y: starting position (subscript, index)
z: length of interception
Code:
select substr('HelloWorld',1,5)from dual;
④.instr(m,n)：
Parameter explanation: Determine the position of a character in the string for the first time
m: string
n: one character
Code:
select instr('HelloWorld','l')from dual;
⑤.replace(x,y,z): replace all characters y in x with characters z
Parameter explanation:
x: string
y: a character in the string
z: the character to be replaced
Code:
select replace('aaabcdaacdaabefaanba','a','6')from dual;
⑥.trim(x from y): remove the first and last characters x in the y string
Parameter explanation:
x: character
y: string
Code:
select trim('m'from'mmmmHellmmmomWmmorldmmmmm')from dual;
⑦ and ⑧.lpad and rpad functions (understand)
lpad(x,y,z)：
rpad(x,y,z)：
Code:
select lpad(salary,10,'*'),rpad(salary,10,'*')from employees;
①.round(m,n): rounding
②.trunc(m,n): truncated
③.mod(m,n): Find the remainder
Code:
select round(439.456,1),round(439.456,0),round(439.456,-1)from dual;
select trunc(439.456,1),trunc(439.456,0),trunc(439.456,-1)from dual;
select mod(1100,300)from dual;
1). date
2). time
①.months_between(m,n): used to calculate the number of months between two dates (precise)
②.add_months(m,n): add or delete n months based on the original m months
③.last_day(m): get the last day of the month in which a certain date is located
Keywords: sysdate represents the current system time
Test the following code:
select sysdate from dual;
The above code can only display the date part of the data but not the time due to the internal format limitation of the database.
We can use the conversion function to_char(x,y) to display the date and time
Supplement: The special letters in oracle have the following
yyyy: year
mm: month
dd: day
day: week
hh: hour (1-12)
hh24: hour (0-23)
mi: minutes
ss: seconds
Refactor the above code to display date and time data:
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual;
See the following code:
select sysdate +1,sysdate,sysdate -2from dual;select(sysdate - hire_date)from employees;
to sum up:
Date data can be added and subtracted with numbers, and the result is the number of days forward or backward
For the date and date data can only be subtracted, the result is the number of days between the two date data
Case:
1). Query how many days have been since employees of the company joined the company? (Use truncation to keep to integer bits, alias worked_day)
select last_name,hire_date,trunc(sysdate - hire_date)"worked_day"from employees;
2). Query how many months have been since employees of the company joined the company? (Rough version: assuming that every month is 30 days)
select last_name,hire_date,(sysdate - hire_date)/30"month_work",months_between(sysdate,hire_date)"mon_work"from employees;
3). Inquire about the employees who join the company on the penultimate day of each month?
select last_name,hire_date from employees
where last_day(hire_date)-1= hire_date;
select last_day(sysdate)from dual;
select add_months(sysdate,2),add_months(sysdate,-3)from dual;
1). to_date
2). to_char
3). to_number
supplement:
Format characters involved in the conversion function:
9- - > Represents 1 bit
99- - > Represents 2 digits
For example:
select to_number('123456','999999')from dual;//Early version, need to define format(Digits)
select to_number('123456')from dual;//New version, format can be omitted
【Precautions】:
When using the to_number function for data conversion, we must calculate the correct length of the data that needs to be converted,
In order to precisely define the number of digits required in the format, once the number of digits is too small, an error will be reported directly! !
demand:
Query the department number of a company employee, if there is no department, it will display "No Department"
select last_name,department_id,nvl(to_char(department_id,'999'),'No department')from employees;
select last_name,department_id,nvl(to_char(department_id),'No department')from employees;
1). nvl(expr1,expr2)：
2). nvl2(expr1,expr2,expr3)：
The execution flow of the nvl2 function:
In the execution process, expr1 will be executed first, if the result of expr1 is not null, then expr2 will be executed;
If the result of expr1 is null, then execute expr3;
demand:
Query employee number, name, salary, bonus rate;
If the bonus rate is not empty, the result after bonus rate + 0.015 is displayed,
If the bonus rate is empty, then 0.01 will be displayed;
Code:
select employee_id,last_name,salary,commission_pct,
nvl2(commission_pct,commission_pct + 0.015,0.01) "new_comm"
from employees;
Introduction and explanation of the judgment structure in sql:
Two kinds:
1). case expression
Template format:
case field when expr1 then x
when expr2 then y
when expr3 then z
...
else n end;
demand:
Query the company employee’s number, name, salary, department number,
If it is an employee in department 70, it will show 1.1 times the salary,
If it is an employee in department 80, it will show 1.2 times the salary,
If it is an employee in department 90, it will show 1.3 times the salary,
The other departments have displayed normal wages;
code show as below:
select employee_id,last_name,salary,department_id,case department_id when 70 then salary *1.1
when 80 then salary *1.2else salary *1.3 end
- - when 90 then salary *1.3 end
- - else salary end
from employees
where department_id in(70,80,90);
2). decode function
Template format:
decode(field,expr1,val1,expr2,val2,...):
demand:
Query the company employee’s number, name, salary, department number,
If it is an employee in department 70, it will show 1.1 times the salary,
If it is an employee in department 80, it will show 1.2 times the salary,
If it is an employee in department 90, it will show 1.3 times the salary,
The other departments have displayed normal wages;
code show as below:
select employee_id,last_name,salary,department_id,decode(department_id,70,salary *1.1,80,salary *1.2,--90,salary *1.3,
salary *1.3)from employees
where department_id in(70,80,90);
Recommended Posts