---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
