Shulou(Shulou.com)06/01 Report--

There are several categories of single-line SQL functions:

Character function

Numeric function

Date of use

Date function

To put it bluntly, the use of the function is to add parameters that meet the definition in the process of using the function, and then execute the function to output the result value.

The difference between a single-line function and a multiline function:

One-line function output as a result per line

Multiline function output as a result of each set of lines

One-line function:

Manipulate data objects

The accept function returns a result

Transform only one row

Return one result per row

Data types can be converted, calculated, formatted

Can be nested

Parameters can be columns, values, or expressions

Function_name [(arg1, arg2,...)]

Character function:

1. Case conversion functions: LOWER, UPPER, INITCAP

These functions convert the case of a string:

Function result LOWER ('SQL Course') result is all lowercase UPPER (' SQL Course') result is all uppercase INITCAP ('SQL Course') result first character is uppercase

1.1. for example, the employee number of the employee Walsh is displayed, and the name is lowercase:

Select lower (last_name), employee_id from employees where last_name='Walsh'

1.2. for example, the salary of the employee Walsh is displayed, and the name is capitalized:

Select upper (first_name), salary from employees where first_name='Kevin'

1.3. display the last_name of Kevin and the initials of salary job_id in uppercase

Select initcap (job_id), last_name,salary from employees where first_name='Kevin'

Retrieve FIRST_NAME and LAST_ name values from the employee table, and FIRST_NAME contains the string "li"

Select first_name,last_name from employees where lower (first_name) like'% li%'

2. Character processing functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD | RPAD, TRIM, REPLACE

Name function result concatenation function CONCAT ('Hello',' World') HelloWorld character interception function SUBSTR ('HelloWorld',1,5) truncate 1-5 character Hello string statistical length LENGTH (' HelloWorld') 10 find character position function INSTR ('HelloWorld',' W') 6 fill function LPAD (salary,10,'*') * 24000 before filling function

RPAD (salary, 10,'*') 24000* replacement function

REPLACE ('JACK and JUE','J','BL')

BLACK and BLUE character clipping function TRIM ('H' FROM 'HelloWorld') elloWorld

2.1. display the first_name and last_name of employees together, and find the information of employees whose salary is greater than 5000.

Select concat (first_name,last_name) name, employee_id,salary from employees where salary > 5000 order by salary desc

2.1.1. Create a separate column information that is displayed as * infor that shows that the employee's salary is greater than 10000.

Select concat ('*', 'information') infor, job_id, salary from employees where salary > 10000

2.1.2. As can be seen from the following figure, the concat function can only concatenate two columns of character information, and the error is reported in the third column.

2.2.1. Intercept the first three letters of the employee's name and find the name of the employee whose salary is greater than 10000

Select substr (first_name,1,3) fname, salary from employees where salary > 10000

2.2.2. Check the name length of employees with a salary greater than 10000

Select length (last_name), salary from employees where salary > 10000

2.3.1. Find the location of Ellen user l.

Select instr (first_name,'l'), salary from employees where first_name='Ellen'

2.3.2. Find the job_id intercepted as REP from bit 4.

Select instr (first_name,'a'), salary,job_id from employees where substr (job_id,'4') = 'REP'

2.4.1. Set the salary to 5 digits, and fill it in front of the * sign if it is not enough.

Select first_name,lpad (salary,5,'*') from employees

2.5.1. Find the salary to show 10 digits, and fill in the deficiency with the * sign.

Select first_name,rpad (salary,10,'*') from employees

2.6.1. Change the employee's name that begins with B to the beginning of JC.

Select replace (first_name,'B','JC'), salary from employees order by first_name

2.7.1. Remove An if the employee's name is initials A.

Select trim ('A' from first_name), salary from employees order by first_name

2.8. check the fourth place of job_id, which starts with REP, and see where first_name an is.

Select employee_id,concat (first_name,last_name) Name,job_id,length (first_name), instr (first_name,'a') "contains'asides?"

From employees where substr (job_id,4) = 'REP'

2.9. The company plans to print a souvenir containing the employee's name for each employee. In order to maintain the overall beauty, the full name of the employee is now required.

Within 16 characters. When the combined length of the employee's FIRST_NAME and LAST_NAME exceeds 15 characters, the employee's scientific name is displayed. Member

The name of the worker consists of the initials of FIRST_NAME and the first 14 characters of LAST_NAME.

[problem-solving ideas] first of all, it is determined that where conditions restrict the merger of FIRST_NAME and LAST_NAME that have been carried out by employees with a length of more than 15.

Filter, and then use a character function after the SELECT clause to process it.

Select first_name,last_name,substr (first_name,1,1) | |''| | substr (last_name,1,14) format_name from employees where length (first_name) + length (last_name) > 15

Numerical function

Function example result ROUND: rounded to the specified decimal value

ROUND (45.926, 2) 45.93TRUNC: truncate the number TRUNC (45.926, 2) 45.92MOD: return the remainder

MOD (1600) 100

Here we use DUAL to test, which is a 'pseudo table' that can be used to test functions and expressions

1. There are three ways to test round function

Select round (45.9234), round (45.923), round (45.923) from dual

Select round (45.9259), round (45.623), round (45.923) from dual

The test of the above two statements shows that:

The first is to intercept 2 digits to the right of the decimal point, and if the third place is 5, it is rounded.

The second is the first place to the right of the standard decimal point.

The third is rounded to the left of the decimal point, which is not advanced enough to become 0, such as round (43.923), and the result becomes 40.

2. There are three ways to test trunc function

SELECT TRUNC (45.923), TRUNC (45.923), TRUNC (45.923) FROM DUAL

SELECT TRUNC (45.923), TRUNC (45.923), TRUNC (39.923) FROM DUAL

SELECT TRUNC (45.923), TRUNC (45.923), TRUNC (39.923) FROM DUAL

The command is to intercept 2 digits respectively. By default, the right side of the decimal place is deleted, and the first place on the left side of the decimal point is deleted to become 0.

When it is-3, it means to intercept 3 digits and directly change to 0.

3. Test the mod function to query the salary of the sales department and the remainder of 5000.

Select last_name,salary,mod (salary,5000) from employees where job_id='SA_REP'

Date function

Oracle internally uses numbers to store dates: century, year, month, day, hour, minute, second

The default date display format is DD-MON-RR

-you can specify only the last two digits of the year, allowing you to store 21st century dates in the middle of the 21st century.

-similarly, allow storage of 20th century dates in the middle of the 21st century

RR date format

Current year

Specify date in RR format YY format 199527-OCT-9519951995199527-OCT-1720171917200127-OCT-1720172017200127-OCT-9519952095

Use the SYSDATE function

The SYSDATE function returns:

Date

time

Select sysdate from dual

Arithmetic operation of date

Add or subtract a number from a date and the result is still the date.

The number of days between the two dates subtracted from the return date.

You can divide the hours by 24, and you can add hours to the date.

1. Inquire how many weeks employees of 90 departments have been on the job.

Select last_name, (sysdate-hire_date) / 7 as weeks from employees where department_id=90

2. Inquire how many months the employees of 30 departments have been employed.

Select first_name, (sysdate-hire_date) / 30 month from employees where department_id=30

Date operation function

The function meaning result MONTHS_BETWEEN ('01murSEPmur95century camera 11murJANmur94') the number of months between two dates 19.6774194ADD_MONTHS (' 06MARMARMARMULY 1) adds a number of months to the specified date, the next date of the specified date 06-APR-17NEXT_DAY ('01MurSEPMAY').

08-SEP-95

LAST_DAY ('01SEPMei95') the last day of the month 30-SEP-95 assumes that the current ROUND (SYSDATE,'MONTH') date is rounded 01-MAR-17ROUND (SYSDATE,' YEAR') date rounded 01-JAN-17TRUNC (SYSDATE,'MONTH') date truncated 01-MAR-17TRUNC (SYSDATE,' YEAR') date truncated 01-JAN-17

I feel that the above are not quite right. I don't understand yet. I'll talk to you later.

1. Query the information of employees who have joined 01-JAN-2012 for 100 months, including EMPLOYEES,LAST_NAME and HIRE_DATE fields.

Select last_name,hire_date,months_between ('01Mujanmuri 2012century camera date) day from employees where months_between (' 01Mujanmuri 2012century ghost date) > 100

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.