Oracle---unit02:oracle string operations, Oracle numeric operations, Oracle date operations, NULL operations

Source: Internet
Author: User
Tags date1 rtrim truncated

---DQL: Data query statements
The---Select statement must contain at least two words, namely the Select and from words.
---the SELECT clause to specify the field to query.
---the table that the FROM clause uses to customize the data source. The--select statement is used to query the data in a table, and an SQL statement consists of multiple clauses.
--a clause has a keyword plus a list of contents.
The--select statement must contain two clauses,
--select clauses and from clauses
The--select clause is used to specify what to query (fields, functions, expressions),
The--FROM clause is used to specify the table for the data source. ---Select you can specify a function or expression in addition to the specific fields in the report. ---* stars represent all fields
SELECT * from EMP_RR; SELECT empno,ename,job,sal from EMP_RR;
-View each employee's annual salary
Select ename,sal,sal*12 from EMP_RR;---the SELECT statement to add a WHERE clause, you can only query for records that meet the criteria.
---> See only four fields, only the department number is 20.
SELECT Ename,job,sal,deptno from Emp_rr WHERE deptno=20;
---> Wages greater than 2500
SELECT Ename,job,sal,deptno from Emp_rr WHERE sal>2500;--> becomes a column, the plus sign in Java. Connection number
SELECT CONCAT (ename,sal) from EMP_RR;---string function
---concat and "| |"
---1.CONCAT (char1,char2)
---Returns the result of two strings concatenated, two parameters char1, Char2 is the two string to concatenate.
---equivalent operation: connection operator "| |"
---If either char1 and char2 are null, it is equivalent to connecting a space
SELECT CONCAT (ename,sal) from EMP_RR;
SELECT CONCAT (CONCAT (ename, ': '), Sal) from EMP_RR;
---Multiple string connections, using | | More intuitive
SELECT ename| | ': ' | | Sal from EMP_RR; the salary of the--->smith is 800 and the department number is 20.
SELECT ename| | ': ' | | sal| | ': ' | | Deptno from Emp_rr WHERE ename= ' SMITH ' and deptno=20;--->length function: string length (number of)
---is meaningless for char because char is a fixed length.
---2.LENGTH (char)
---used to return the length of a string
---If the character type is VARCHAR2, returns the actual length of the character
---If the character type is char, the length also includes the trailing space---gets the length of the specified string
---> see the number of characters per employee name?
SELECT Ename,length (ename) from EMP_RR;
---> Employees with a 4-letter name?
SELECT ename from Emp_rr WHERE LENGTH (ename) =4;---3.lower,upper,initcap
---Convert a string to full lowercase, all uppercase, and capitalize first letter
---Dual: pseudo-table
---when the queried data is not related to any table, you can query the pseudo-table so that only one record is queried. SELECT UPPER (' HelloWorld ') from EMP_RR;
---> Show 14 data, check it out on your own table.
---Dual: a pseudo-table that can be replaced with a pseudo-table when the contents of the query are not data in any table.
---in initcap, display spaces are supported.
SELECT
UPPER (' HelloWorld '),
LOWER (' HELLOWORLD '),
Initcap (' HELLO World ')
from dual;
---shielding case applications:
---If you want to convert the case, add upper () or lower () before the letter.
SELECT Ename,sal,deptno from Emp_rr WHERE UPPER (ename) =upper (' Scott ');---4.trim,ltrim,rtrim
---Remove both sides of the blank, white space is a kind of, the database more strong points, you want to go to what.
---> Remove specified character content on both sides of a string
SELECT TRIM (' e ' from ' eeeeeliteeeee ') from dual;
--->litselect
LTRIM (' estestestestliteeeeeee ', ' est ')
from dual;
--->liteeeeeeeselect
RTRIM (' estesetsetsetsliteeeeeeeeeee ', ' e ')
from dual;
--->estesetsetsetslitselect
LTRIM (' esfffrfsfefliteseseses ', ' EFS ')
from dual;
--->rfsfefliteseseses
---as long as there is one of them in the string, the left character is killed. ---5.LPAD (char1,n,char2), Rpad
---the complement function, CHAR1 displays n bits, and if CHAR1 is less than n bits, the Left (right) complements several char2 characters that have reached the number of digits.
SELECT Ename,sal,lpad (sal,15, ' $ ') from EMP_RR;
---lpad, rpad complement function (left and right to fill the $ symbol) write 3, the excess will be deleted.
SELECT Ename,sal,rpad (sal,3, ') from EMP_RR;---6.SUBSTR (char,m[,n])
The contents of the---square brackets are optional.
---intercepts a char string, starting at m and intercepting N characters consecutively.
---need to note: The database subscript is starting from 1!!!
---n is truncated to the end of the current string if the number that is not specified or specified exceeds the actual character length that can be intercepted. n is the length.
---if M is negative, it is intercepted from the countdown position. The number at the end cannot be negative.
SELECT SUBSTR (' thinking in from dual;
--->in
SELECT SUBSTR (' thinking in from dual;
--->in
--->n=-2 is null---7. INSTR (Char1,char2[,n[,m])
---View the location of char2 in Char1, N and M do not specify a default of 1
---N to find starting from the first few characters
---m for the first few occurrences of the view
SELECT INSTR (' thinking in from dual;
--->3
SELECT INSTR (' thinking in from dual;
--->3, or 3?
SELECT INSTR (' thinking in from dual;
--->6
SELECT INSTR (' thinking in from dual;
--->10
SELECT INSTR (' thinking in from dual;
--->0
--->java returns 0 in -1,oracle.
---need to note: The database subscript is starting from 1!!! 0 means no more.
---numeric type:
---1.NUMBER (P) denotes integers
---full syntax: number (Precision,scale)
--If no scale is set, the default value is 0, that is, number (p) represents an integer
--p represents the total number of digits, with a value of 1-38
---used to store data in a table, such as encoding, age, number of times, etc., recorded in integers
CREATE TABLE STUDENT_RR (
ID Number (4),
Name CHAR (20)
);
DESC STUDENT_RR---2. Number (P,s) indicates floating-point number
---number (precision,scale)
--precision:number Maximum number length that can be stored (excluding left and right sides of 0)
--scale: Maximum number length to the right of the decimal point (including 0 on the left)
--if S is specified but no p is specified, p defaults to 38, such as:
--List Number (*,s)
--often used to make data with decimal digits in the amount, grade, etc. of the table
CREATE TABLE STUDENT_RR (
ID Number (4),
Name CHAR (20),
Score Number (5,2)
);
DESC STUDENT_RR
Variant data type for---number: the internal implementation is numbers, which can be interpreted as an alias for the name, with the purpose of being compatible with multiple databases and programming languages
--numeric (p,s): Fully mapped to number (P,s)
--decimal (p,s) or Dec (p,s): Fully mapped to number (P,s)
--integer or int: fully mapped to number (38) type
--smallint: Fully mapped to number (38) type
--float (b): Map to Number type
--double Precision: Map to Number type
--real: Mapping to Number Type
---numeric functions
--1.round (N[,m])
--Retains the N decimal point after the M bit.
--m does not write default to 0, that is: reserved to integer digits
If the--m is negative, the number is reserved for more than 10 digits.

--round (M,n): Rounding (Wu Zetian: 5 days thought)
--reserved m to n digits after the decimal point
--n is 0 or does not specify that this is reserved to an integer
--n is negative this is the number of digits left before the decimal point
SELECT ROUND (45.678,2) from DUAL;
--->45.68
SELECT ROUND (45.678,0) from DUAL;
--->46
SELECT ROUND (45.678,-1) from DUAL;
--->50
---Negative number-1 is the number of bits, into 10
SELECT ROUND (45.678,-2) from DUAL;
--->0
SELECT ROUND (55.678,-2) from DUAL;
--->100---2.TRUNC (n[,m])
---intercept numbers
---parameter meaning is consistent with round
---No rounding, direct intercept of numbers
SELECT TRUNC (45.678,2) from dual;
--->45.67
SELECT TRUNC (45.678,0) from dual;
--->45
SELECT TRUNC (45.678,-1) from dual;
--->40
SELECT TRUNC (45.678,-2) from dual;
--->0
SELECT TRUNC (55.678,-2) from DUAL;
--->0---3.MOD
---Seek the remainder, which is equivalent to
---m divided by N for remainder, n if 0 then directly returns m---MOD (m,n): Returns the remainder after m divided by n
---n is 0 to return m directly
---The salary value by 1000 to take the remainder
SELECT Ename,sal,mod (sal,1000) from EMP_RR;---4.CEIL and floor: Rounding up, rounding down
---just translated: ceilings and floors.
---n is an integer value
SELECT ceil (45.678) from DUAL;
--->46
SELECT Floor (45.678) from DUAL;
--->45---date type related functions:---date Related keywords:
---1.SYSDATE: Returns a value that represents the date of the current system time
---2.SYSTIMESTAMP: Returns the timestamp type value for the current time
SELECT sysdate from dual;
--->22-8 month-17
SELECT Systimestamp from dual;
--->22-8-17 03.42.51.562000000 pm +08:00
--insert into EMP (ename,hiredate) VALUES (' Jack ', sysdate);---date conversion function
---1:to_date ()
---Resolves a given string to a date value in the given format
---Other characters in a date format string that are not letters or symbols need to be enclosed in double quotation marks.
---single quotation marks: representing strings
SELECT to_date (' 1990-09-13 21:55:21 ', ' yyyy-mm-dd HH24:MI:SS ') from dual;
--->13-9 month-90
SELECT to_date (' September 13, 1990 21:55 21 sec ',
' YYYY ' year "MM" month "DD" Day "HH24" when "MI" minutes "SS" seconds "') from dual;
--->13-9 month -90---Note: RR and yy are different, RR and yy are two digits for the year, but
---when you use the To_date function to parse a two-digit number into an actual date, the RR will determine its own actual, and YY will not.
SELECT to_date (' 99-09-13 ', ' YY-MM-DD ') from dual;
--->13-9 month-99
SELECT To_char (to_date (' 99-10-18 ', ' yy-mm-dd '), ' YYYY-MM-DD ') from dual;
--->2099-10-18---> See figure sys vs. user century comparison chart:
SELECT To_char (to_date (' 49-10-21 ', ' rr-mm-dd '), ' YYYY-MM-DD ') from dual;
--->2049-10-21
SELECT To_char (to_date (' 61-10-21 ', ' rr-mm-dd '), ' YYYY-MM-DD ') from dual;
--->1961-10-21---2.to_char function
---Convert other types of data to character types
SELECT to_char (sysdate, ' Yyyy-mm-dd HH24:MI:SS ') from dual;
--->2017-08-22 16:07:35---date can be calculated
---1. Adding a number to a date is equivalent to calculating the number of days to add and subtract
---2. Two dates subtracted from the difference in days
---date is larger than the size, the later the greater. ---See what day it's 100 days from now?
SELECT sysdate+100 from dual;
--->30-11 month -17---See how many days each employee has been on the job so far today?
SELECT ename,sysdate-hiredate from EMP_RR;
---> More than 10,003 days, most of them are eight years in the job.
---date common functions:
---1.last_day (date)
---Return to the end date of the month to which the date is set
---last_day (date): Returns the last day of the month on which date is
---is useful for calculating certain business logic according to natural months, or for arranging month-end recurring events---View the month of the current date?
SELECT last_day (' 20月-February -09 ') from DUAL;
--->28-2 month -09---See the end of the month?
SELECT Last_day (sysdate) from dual;
--->31-8 month -17select ename,last_day (hiredate) from EMP_RR;
---> Each employee's entry at the end of the month. ---2.add_months (date,i)
---The specified date plus the specified month, if I is negative, subtract
---add_months (date,i): Date value returned date plus I month
--parameter I can be any number, most of the time take a positive integer
--If I is a decimal, it will be truncated and then participate in the operation
--If I is a negative number, I get the date value minus I months.
--Calculating the 20 anniversary of staff entry
Select Ename,add_months (hiredate,20*12) as "20 Anniversary" from EMP_RR;---See the date of each employee's regularization?
SELECT ename,add_months (hiredate,3) from EMP_RR;
---3.months_between (date1,date2)
---calculates how many months between two dates are calculated using Date1-date2.
--See how many months each employee has been on the job until today?
SELECT Ename,months_between (sysdate,hiredate) from EMP_RR;---4.next_day (date,i)
---Returns the date of the specified week within one week of the day following the date given.
---next_day (date,i)
---Returns the date of the week of the week from the second day of the given date. I represents the week:
---1 for Sunday. 2 is Monday, and so on.
SELECT Next_day (sysdate,6) from dual;
--->25-8 month -17--next_day (Date,char): Returns the next week of the date data, and the weeks are determined by the parameter char.
--In the Chinese environment, the use of "Wednesday" in the form of the English environment, the need to use "Wednesday"
--The week of this English. To avoid trouble, you can use the number 1-7 to represent the Sunday-Saturday
--next_day not tomorrow!
--Query the date of next Wednesday
Select Next_day (sysdate,4) as "Next_wedn" from DUAL;
--->23-8 month -17??????????????????????????????????????????????????????????????????????
---query what's the date next Wednesday?
---Yesterday output execution is today's date??? Today output execution is the date of next Wednesday. (This section also needs to be studied)
--->30-8 month-17
SELECT Next_day (sysdate,4) as Next_wedn from dual;
--->23-8 month-17
--->30-8 month-17
SELECT Next_day (sysdate,5) as Next_wedn from dual;
--->24-8 month -17---next_day query what's the next n?
---According to the concept of foreign weeks, according to China's need to add one.
SELECT Next_day (to_date (' 2017-03-01 ', ' yyyy-mm-dd '), 7) from dual;---5.LEAST and greatest
---the minimum and maximum values, these two functions are available for all data types that can be compared
---for the date, the maximum is the latest date, the minimum value is the earliest date---greatest (EXPR1[,EXPR2[,EXPR3]] ...)
---LEAST (EXPR1[,EXPR2[,EXPR3] ...)
---is also called a comparison function, can have multiple parameter values, and the result is the largest or smallest value in the argument list
---parameter types must be consistent
---is worth more, the second argument in the argument list is implicitly converted to the data type of the first parameter,
---So if you can convert, then continue to compare, if not converted will be an error
SELECT LEAST (sysdate, ' October-October -08 ') from DUAL;
---> October-October -08select LEAST (sysdate,to_date (' 2008-08-08 ', ' Yyyy-mm-dd ')) from dual;
--->08-8 month-08
SELECT Greatest (Sysdate,to_date (' 2008-08-08 ', ' Yyyy-mm-dd ')) from dual;
--->23-8 month -17---6.EXTRACT
---Extracts the value of a specified time component in a date
SELECT EXTRACT (year from sysdate) from dual;
--->2017---See the employees in the 1980?
SELECT ename,sal,hiredate from EMP_RR WHERE EXTRACT (year from hiredate) = 1980;
--->smith 800 1 July-December -80---EXTRACT (date from datetime): Extracts the data specified by date from the parameter datetime, such as extracting the year, month, day
SELECT EXTRACT (year from sysdate) current_year from dual;
--->2017
SELECT EXTRACT (HOUR from TIMESTAMP ' 2008-10-10 10:10:10 ') from dual;
--->10
---null (empty is null,null, which is empty)
---Important concepts in the database: null, which is the null value
---sometimes some field values in the table, data unknown or temporarily absent, value null
---any data type is preferable to null---null and empty string
---Empty---objectively does not exist; the empty string---objectively invisible.
---(there is a string out, invisible.) This is like air and vacuum, empty string is like air, it is objectively exist, but you can't see,
---Null is objectively nonexistent at all. Null in the computer does not exist objectively, NULL is not in memory at all, and strings exist in binary. ---the difference between null and empty strings in Java?
---null: The objective does not exist, not in memory. Vacuum
---empty string: The objective in memory is encoded, but displayed, invisible. (belonging to the objective existence) (AIR)
---is like the concept of air and vacuum, the air exists, but you can't see. Null is a vacuum, and objectivity does not exist. ---null operation:
---inserting null values
CREATE TABLE Student_r (ID number (4), name char (a), gender char (1));
--->table student_r has been created.
INSERT into Student_r VALUES (1000, ' Li Mo sorrow ', ' F ');
--->1 line is inserted.
INSERT into Student_r VALUES (1001, ' 林平 ', NULL);
--->1 line is inserted. (explicitly insert a null value)
INSERT into Student_r (id,name) VALUES (1002, ' Zhang Mowgli ');
--->1 line is inserted. (implicitly inserting null values)
SELECT * from Student_r;---2: Update null values
UPDATE student_r SET gender=null WHERE id=1000;
--->1 line has been updated.
SELECT * from Student_r;
ROLLBACK;
---> Fallback is complete. ---to determine the null value as a condition:
---judgment drug use is null and is not NULL cannot be used "=" to determine null.
DELETE from Student_r WHERE gender=null;
SELECT *from Student_r;
--->0 row has been deleted.
---Null is not a value, it can be said to be a state. Logically speaking, =null equals emptiness, which is strictly wrong. (more rigorous in the database than in Java)
---in the database, you cannot write =nuul when judging if the value of a field is null, because no value is =null.
---Note: write is null
DELETE from Student_r WHERE gender is NULL;
--->2 row has been deleted.
DELETE from Student_r WHERE gender are not NULL;
--->1 row has been deleted. Operations that---NULL
---null and string connection equals nothing.
---null and numeric operation, result or null
SELECT ename| | NULL from EMP_RR;---is connected to a string in Java?
---java and string concatenation, it really spelled a null
---package Oracle;--public class Demo {
--public static void main (String[]args) {
--String Str=null;
--System.out.println ("str:" +STR);
-- }
--}--->str:null---and numeric operations, the result is still null.
---View each employee's income (Payroll plus performance)
SELECT ename,sal,comm,sal+comm from EMP_RR;---null value function
---1.NVL (a1,a2)
---when A1 is null, the function returns A2, otherwise the A1 itself is returned, and all functions function to replace the null value with the specified value.
SELECT ENAME,SAL,COMM,SAL+NVL (comm,0) from EMP_RR;
---Calculate employee monthly income
Select ENAME,SAL,COMM,SAL+NVL (comm,0) as "salary" from EMP_RR;---View the performance of each employee, the performance of which is shown as "performance" is shown as "no performance".
---2.nvl2 (a1,a2,a3)
---when A1 is not NULL, the function returns A2, and when A1 is NULL, the function returns A3
SELECT ename,comm,nvl2 (Comm, ' performance ', ' no performance ') from EMP_RR,---NVL2 can implement NVL functionality, but NVL does not fully implement NVL2 functionality.
SELECT ENAME,SAL,COMM,SAL+NVL (comm,0) from EMP_RR;
SELECT Ename,sal,comm,sal+nvl2 (comm,comm,0) from EMP_RR;
SELECT Ename,sal,comm,nvl2 (comm,sal+comm,sal) from EMP_RR;---NVL2 (EXPR1,EXPR2,EXPR3): Similar to the NVL function, NULL is converted to the actual value
The---NVL2 is used to determine if EXPR1 is null, if not NULL, returns EXPR2, or null if it returns EXPR3.
Select ENAME,SAL,COMM,NVL2 (comm,sal+comm,sal) as "salary" from EMP_RR; *******************************************************************************************

Oracle---unit02:oracle string operations, Oracle numeric operations, Oracle date operations, NULL operations

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.