SQL statement 1

// Duplicate records deleted by distinct
Select distinct class_id from news;

// Count () count the number of records
Select count (distinct class_id) from news;

// Concat ('','') or | connection string
Select 'news title: '| title news title, Concat ('news content:', content) news content from news;

// Between... and...
Select * from news where to_char (pubdate, 'yyyy-mm-dd') between '2017-11-10 'and '2017-12-31 ';

// Is not null is not a null record
Select * from news where content is not null;

// Is null is a null record
Select * from news where content is null;

// And statement
Select * from news where content is null and class_id = '3 ';

// Or statement
Select * from news where content is null or class_id = '3 ';

// Not reverse statements
Select * from news where not (content is null and class_id = '3 ');

// In statement
Select * from news where class_id in ('3', '4', '5 ');

// Not in statement
Select * from news where class_id not in ('3', '4', '5 ');

// The Link statement "%" can match content of any length, and "_" can match content of a length.
Select * from news where title like '% light % ';

// <> And! = Non-equal sign
Select count (*) from news where class_id! = '3 ';
Select count (*) from news where class_id <> '3 ';

// Order by ASC ascending DESC descending
Select * from news order by pubdate DESC
Select * from news order by pubdate DESC, class_id ASC

// Delimiter --------------------------------------------------------------------------------------------

// Upper () converts the string to uppercase letters
Select upper ('hangzhou') from dual; Result: Zhangze

// Lower () converts the string to lowercase letters. Result: Zhangze
Select lower ('hangzhou') from dual;

// Initcap () capital the first letter of a word
Select initcap ('zhang Ze') from dual; Result: Zhang Ze

// Substr () string Truncation
Select substr ('hello', 1, 3) from dual; Result: El
Select substr ('hello',) from dual; Result: El
// Adopt reverse truncation. If the input position is negative, reverse truncation is performed.
Select substr ('hello',-3, 3) from dual; Result: llo

// Length () String Length
Select length ('hello') from dual; Result: 5

// Replace () string replacement
Select Replace ('hello', 'l', 'x') from dual; Result: hexxo

// Numeric Function -----------------------------------------------------------------------------------

// Round ()
Select round (120.56) from dual; Result: 121
Select round (120.16) from dual; Result: 120
// Retain two decimal places
Select round (120.567, 2) from dual; Result: 120.57
// Round the integer
Select round (120.567,-2) from dual; Result: 100
Select round (156.567,-2) from dual; Result: 200
Select round (156.567,-3) from dual; Result: 0
Select round (556.567,-3) from dual; Result: 1000

// Trunc () truncates decimal places
Select trunc (120.56) from dual; Result: 120
// Specify the number of decimal places to retain
Select trunc (120.56, 1) from dual; Result: 120.5
// Use a negative number to indicate the number of digits
Select trunc (129.56,-2) from dual; Result: 100

// Mod () for remainder
Select Mod (10, 3) from dual; Result: 1
Select Mod (10, 2) from dual; Result: 0

// Date function callback --------------------------------------------------------------------------------------------
Date plus or minus rules
Date-number = Date
Date + number = Date
Date-date = Number
// Sysdate current date
Select sysdate from dual;
Select round (sysdate-pubdate)/7) from news;

// Months_between () returns the number of months in the specified date range.
Select months_between (sysdate, pubdate) from news;

// Add_months () on the specified date plus the specified number of months, find the date after
Select add_months (sysdate, 4) from dual;

// Next_day () is used to find the next occurrence date of the given week.
Select next_day (sysdate, 'thursday') from dual;

// Last_day () returns the date of the last day of the given date.
Select last_day (sysdate) from dual;

// Transform function ---------------------------------------------------------------------------------------------------

// To_char () is converted to a string.
// Used for date
Select to_char (sysdate, 'yyyy') from dual; Result: 2012
Select to_char (sysdate, 'mm') from dual; Result: 11
Select to_char (sysdate, 'dd') from dual; Result: 22
Select to_char (sysdate, 'yyyy-mm-dd') from dual; Result: 2012-11-22
Select to_char (sysdate, 'yyyymmdd') from dual; Result: 20121122
Select to_char (sysdate, 'yyyy/MM/dd') from dual; Result: 2012/11/22
Select to_char (sysdate, 'yyyy * Mm * dd') from dual; Result: 2012*11*22
// Used for numbers. $ indicates the dollar, and l indicates the abbreviation of local. The amount is displayed in the local language.
Select to_char ('123', '123') from dual; Result: 20202020
Select to_char ('20140901', '$20202020') from dual; Result: $99,999,999
Select to_char ('20140901', 'l99, 100000') from dual; Result: ￥20202020

// Convert to_number () to a number
Select to_number ('123') + to_number ('123') from dual; Result: 123
Select '20180101' + '20180101' from dual; Result: 123

// Convert to_date () to date
Select to_date ('1970-11-22 ', 'yyyy-mm-dd') from dual; Result: 2012

// Universal function callback ---------------------------------------------------------------------------------------------------
// Null is the result after calculation or null. To avoid this problem (for example, 5 + null = NULL), nvl () is used ()
// Nvl () can change the null value to the specified content.
Select (SAL + nvl (Comm, 0) * 12 Income from EMP;

// The decode () function is similar to the IF... elseif... else statement.
// Syntax decode (COL/expression, search1, result1 [, search2, result2,...] [, default])
// Description COL/expression: a list or expression
Search1, search2,..., searchn: the condition for comparison.
Result1, result2,..., resultn: Return Value
If COL/expression is the same as searchn, resultn is returned. If no condition matches COL/expression, default value default is returned.
Select decode (, 'content is 1', 2, 'content is 2', 3, 'content is 3', 'content is null') from dual; Result: content is 2