Oracle 11g Study Notes 10_29 (2)

SQL functions I. Character Functions

Character functions are the most common functions in Oracle,
Character functions:
. Lower (char): converts a string to lowercase.
. Upper (char): converts a string to an upper-case format.
. Length (char): returns the length of the string.
. Substr (char, m, n): returns the substring of a string.
Where M indicates the length of the substring starting from m, and N indicates the length of the substring.

Display the names of all employees in lower case
Display the names of all employees in uppercase
Display the name of an employee with exactly five characters
Display the first three characters of all employee names
Display the names of all employees in uppercase

// 1. Select upper (substr (ename, 1, 1) from EMP; // 2. Select upper (substr (ename, 1 ,; length (ename)-1) from EMP; // 3. Merge select upper (substr (ename, 1, 1) | Lower (substr (ename, 2, length (ename) -1) from EMP;

Display the names of all employees in lowercase

select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp;

. Replace (char1, search_string, replace_string)
Specifically, search_sting is the original string to be searched, and replace_string is the replacement string.
. Instr (char1, char2, [, N [, m]) obtains the position of the substring in the string.
Show the names of all employees, replace all "A" with ""

select replace(ename,'A','a') from emp;

Ii. mathematical functions

The input parameters and returned values of mathematical functions are numeric data types. Mathematical functions include
Cos, cosh, exp, LN, log, sin, Sinh, SQRT, tan, Tanh, ACOs, asin, atan, round,
Our most commonly used:
. Round (n, [m])
This function is used to perform rounding. If M is saved, it is rounded to a positive number. If M is a positive number, it is rounded to the M digit of the decimal point. If M is a negative number, rounding to the M digit before the decimal point
. Trunc (n, [m])
This function is used to intercept digits. If M is saved, the fractional part is truncated. If M is a positive number, it is truncated to the M digit of the decimal point. If M is a negative number, the first M digit of the decimal point is truncated.
. Mod (m, n) m modulo n
. Floor (n) returns the largest integer less than or equal to n.
. Ceil (n) returns the maximum integer greater than or equal to n.
Iii. Date Functions

The date function is used to process the shuju of the date type. By default, the date format is dd-mon-yy, that is, 12-7-78.
(1) sysdate: The function returns the system time.
(2) Add. Months (d, n)
(3) last_day (d): returns the last day of the month where the specified date is located.
Search for employees who have been hired for more than 8 months

select * from emp where sysdate > add_months(hiredate,8);

Here, add_months (hiredate, 8) indicates adding 8 months to the employment date. If sysdate is greater than the time it has been added for 8 months, the employee will be eligible. Because, if it is two months of employment, plus eight months is definitely more than the system time.
Display the names and dates of employment of employees who have served for 10 years

select * from emp where sysdate>= add_months(hiredate,12*10);

Shows the number of days each employee has joined the company.
If so:

Select sysdate-hiredate "days of employment", ename from EMP;

Employment days ename
--------------------
8732.63312 pangzi.com
8943.63312 Xiaohong
11639.6331 Smith
11574.6331 Allen
11572.6331 ward
Then there will be a decimal point in the number of days, because it counts the hours that are not enough for a day.
If you do not want a decimal point, we can do this:

Select floor (sysdate-hiredate) | 'day' "days of employment", ename from EMP;

Employment days ename
----------------------------------------------------
8732 days pangzi
8943-day red
11639 days Smith
11574 days Allen

11572 days ward

Find all employees employed on the last three days of each month

select hiredate, ename from emp where last_day(hiredate)-2 = hiredate;

Hiredate ename
---------------------
1981/9/28 Martin
Note that last_day (hiredate)-2 minus 2 is not 3;
Iv. conversion functions

The conversion function is used to convert data types from one type to another. In some cases, the data types allowed by Oracle Server are different from the actual ones. This is the implicit conversion data type of Oracle server.
For example:
Create Table T1 (id int );
Insert into T1 values ('10') --> so that Oracle will automatically set '10' --> 10
Create Table T2 (ID varchar2 (10 ));
Insert into T2 values (1); --> so that Oracle will automatically set 1 --> '1'
Although oracle can perform implicit data type conversion, it does not adapt to all situations. To improve reliability, use the Conversion Function for conversion.

* To_char
. Display the hour/minute/Second of the date
. To_char (time, 'yyyy-mm-dd hh24: MI: ss ')
YY: The year of two digits is 2012 --> 12
Yyyy: the year of a four-digit number.
MM: The month of double digits, December 31, August --> 08
DD: double-digit day 29 --> 29
Hh24: 24-hour format
Hh12: 12 hours
Mi, ss --> display minute \ second

select ename, empno, job, to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'), sal from emp;

Ename empno job to_char (hiredate, 'yyyy-MM-DDHH Sal)
---------------------------------------------------------------
Pangzi 9999 clerk 1988-12-02 00:00:00 2456.34
Xiaohong 9998 manager 00:00:00 28.90
Shouzi 9997 manager 16:51:38 3800.50
At last, I inserted the latest time, minute, and second. At the time of insertion, sysdate is used for hiredate. If the data is inserted with no time, minute, and second, the display time is zero.
. Display the specified currency symbol of the salary
9: displays numbers and ignores the preceding 0
0: displays numbers. If the number of digits is insufficient, fill the number with 0.
.: Display the decimal point at the specified position
,: Display a comma at a specified position
$: Add a dollar sign before a number
L: add the local currency symbol before the number
C: add the international currency symbol before the number.

select ename, to_char(sal,'L9999.99') from emp;

Ename to_char (SAL, 'l9999. 99 ')
---------------------------------
Pangzi ￥2456.34
Xiaohong ￥28.90
Shouzi ￥3800.50
Smith ￥800.00

select ename, to_char(sal,'L9,999.99') from emp;

Ename to_char (SAL, '2009, 999.99 ')
----------------------------------
Pangzi ￥2,456.34
Xiaohong ￥28.90
Shouzi ￥3,800.50
Smith ￥800.00

Show all employees who joined in 1980

select * from emp where to_char(hiredate,'yyyy')='1980';EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----------------------------------------------------------------
7369 Smith clerk 7902 800.00 20

Displays all employees who joined in March.
Select * from EMP where to_char (hiredate, 'mm') = 12;
 
Empno ename job Mgr hiredate Sal comm deptno
----------------------------------------------------------------
9999 pangzi clerk 7782 1988/12/2 2456.34 55.66 10
7369 Smith clerk 7902 800.00 20
7900 James Clerk 7698 950.00 30
7902 Ford analyst 7566 3000.00 20

Oracle ('yyyy-mm-dd hh24: MI: ss') can be used flexibly. If you want to get the time range, you can get the time range.
V. system functions

Terminal: the terminal identifier of the current session customer.
Lanuage: Voice
DB. Name: name of the current database
Nls_date_format: Date Format corresponding to the current session customer
Session_user: the user name of the database corresponding to the current session.
Current_schema: default solution name for the current session customer
HOST: returns the name of the host where the database is located.

select sys_context('userenv','nls_date_format') from dual;

Sys_context ('userenv', 'nls _ dat
--------------------------------------------------------------------------------
DD-MON-RR

select sys_context('userenv','db_name') from dual;

Sys_context ('userenv', 'db _ name
--------------------------------------------------------------------------------
Orcl

select sys_context('userenv','language') from dual;

Sys_context ('userenv', 'Register AG
--------------------------------------------------------------------------------
Simplified chinese_china.zhs16gbk

* Relationship between users and solutions:

Once a user is created, Oracle automatically creates a solution. Oracle manages database objects in a solution. The solution name is the same as the user name. The solution contains many data objects, such as tables, views, triggers, and stored procedures.

If the column is a Chinese column, the column name should be enclosed in double quotation marks. If you want to change the Chinese data in the columns of a table, enclose it in single quotes.