Oracle-day02 under

Five, single-line function
(a) Character functions

Characters commonly used function explanation:
(1) To find the length of the string
Statement:

select length(‘ABCD‘) from dual;

The results shown are:

(2) substring of string SUBSTR
Statement:

select substr(‘ABCD‘,2,2) from dual;

The results shown are:

(3) String stitching CONCAT
Statement:

select concat(‘ABC‘,‘D‘) from dual;

The query results are as follows:

We can also use | | Concatenation of strings

select ‘ABC‘||‘D‘ from dual;

The query results are as above.
(b) Numerical functions

Commonly used numerical functions to explain:
(1) Rounding function ROUND
Statement:

select round(100.567) from dual

The query results are as follows:

Statement:

select round(100.567,2) from dual

The query results are as follows:

(2) Intercept function TRUNC
Statement:

select trunc(100.567) from dual

Query Result:

Statement:

select trunc(100.567,2) from dual

(3) Modulo MOD
Statement:

select mod(10,3) from dual

Results:

(iii) Date function

We use the sysdate system variable to get the current date and time
The statements are as follows:

select sysdate from dual

The query results are as follows:

The usual date functions are explained:
(1) Add month function Add_months: Add the specified month based on the current date
Statement:

select add_months(sysdate,2) from dual

The query results are as follows:

(2) Ask the last day of the month Last_day
Statement:

select last_day(sysdate) from dual

The query results are as follows:

(3) Date interception TRUNC
Statement:

select TRUNC(sysdate) from dual

The query results are as follows:

Statement:

select TRUNC(sysdate,‘yyyy‘) from dual

The query results are as follows:

Statement:

select TRUNC(sysdate,‘mm‘) from dual

The query results are as follows:

(iv) Conversion function

Frequently used conversion functions to explain:
(1) Digital to string To_char
Statement:

select TO_CHAR(1024) from dual

Query Result:

(2) Date to string To_char
Statement:

select TO_CHAR(sysdate,‘yyyy-mm-dd‘) from dual

Query Result:

Statement:

select TO_CHAR(sysdate,‘yyyy-mm-dd hh:mi:ss‘) from dual

Query Result:

(3) string to date To_date
Statement:

select TO_DATE(‘2017-01-01‘,‘yyyy-mm-dd‘) from dual

The query results are as follows:

(4) string to digital To_number
Statement:

select to_number(‘100‘) from dual

(v) Other functions
(1) Null value processing function NVL
Usage:
NVL (detected value, if null value);
Statement:

select NVL(NULL,0) from dual

The query results are as follows:

Demand:
Displays a price record for the owner type ID 1 in the price list, or 9999999 if the upper value is NULL
Statement:

select PRICE,MINNUM,NVL(MAXNUM,9999999)from T_PRICETABLE where OWNERTYPEID=1

Query Result:

(2) Null value processing function NVL2
Usage:
NVL2 (the value to detect if the value is not NULL, if the value is null);
Requirement: Displays the price record of the owner type ID 1 in the price list, if the upper value is NULL, displays "not
Limit ".

Statement:

select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , ‘不限‘)from T_PRICETABLE where OWNERTYPEID=1

(3) Condition value decode
Grammar:

decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)

function returns the corresponding value according to the criteria
Requirements: Display the following information (do not associate the query owner type table, directly determine the value of 1 2 3)

Statement:

select name,decode( ownertypeid,1,‘ 居 民 ‘,2,‘ 行 政 事 业 单 位‘,3,‘商业‘) as 类型from T_OWNERS

The upper statement can also be implemented with a case and then statement.

select name ,(case ownertypeidwhen 1 then ‘居民‘when 2 then ‘行政事业单位‘when 3 then ‘商业‘else ‘其它‘end) from T_OWNERS

There is another way to do this:

select name,(casewhen ownertypeid= 1 then ‘居民‘when ownertypeid= 2 then ‘行政事业‘when ownertypeid= 3 then ‘商业‘end )from T_OWNERS

Vi. Conversion of Ranks
Demand: monthly statistics on water charges in various regions in 2012, such as


Demand: Quarterly statistics on water charges in various regions in 2012, such as

The statements are as follows:

Vii. Analytical functions
The following three analysis functions can be used for ranking.
Examples of three ranking methods

(1) RANK
The same values rank the same, the ranking jumps
Requirements: Sort the usenum fields of the T_account table, the same values rank the same, the ranking jumps
Statement:

select rank() over(order by usenum desc ),usenum fromT_ACCOUNT

Results:

(2) Dense_rank the same values ranked the same, ranking consecutive
Requirements: Sort the usenum fields of the T_account table, the same values are ranked the same, the ranking is continuous
Statement:

select dense_rank() over(order by usenum desc ),usenumfrom T_ACCOUNT

Results:

(3) Row_number
Returns successive rankings, regardless of the value equality
Requirements: Sorts the usenum fields of the T_account table, returning successive rankings, regardless of whether the value is
No equal
Statement:

select row_number() over(order by usenum desc ),usenumfrom T_ACCOUNT

Paging queries implemented with the ROW_NUMBER () analysis function are much simpler than the three-layer nested subqueries:

select * from(select row_number() over(order by usenum desc )rownumber,usenum from T_ACCOUNT)where rownumber>10 and rownumber<=20

The query results are as follows:

Eight, set operation
(a) What is a set operation
Set operation, a set operation is to combine two or more result sets into a single result set. Set operation
Including:
·· UNION all, which returns all records for each query, including duplicate records.
·· UNION, which returns all records for each query, excluding duplicate records.
·· INTERSECT (intersection), returns a record that is common to two queries.
·· Minus (difference), returns the records retrieved by the first query minus the records retrieved by the second query.
After the remaining records.

(ii) the set operation

UNION all does not remove duplicate records

select * from t_owners where id<=7union allselect * from t_owners where id>=5

The results are as follows:

UNION removes duplicate records

select * from t_owners where id<=7unionselect * from t_owners where id>=5

(iii) Intersection operation

select * from t_owners where id<=7intersectselect * from t_owners where id>=5

Results:

(d) Differential set operation

select * from t_owners where id<=7minusselect * from t_owners where id>=5

Results:

If we use the minus operator for paging, the statement is as follows:

select rownum,t.* from T_ACCOUNT t where rownum<=20minusselect rownum,t.* from T_ACCOUNT t where rownum<=10

Ix. Summary
(i) Summary of knowledge points
(ii) on-machine task layout
Functions related to the development of statistical modules for the "tap water charging system"
1. Daily fee (total)
Statistics on a day of charges, grouped by region, the effect is as follows:

2. Daily fee (toll-teller)
Statistics of a fee for a certain day of the charges, grouped by region, the effect is as follows:

3. Monthly fee Report form (total)
Statistics of the charge records for a certain month, grouped by region

4. Monthly fee report (fee-paying member)
Statistic the charge record of a certain charge member in a certain year, summarize by the area group
5. Annual report of fees and charges (sub-regional statistics)
Statistics of the annual fees, grouped by region, the effect is as follows:

6. Annual report of the fee (monthly statistics)
Statistics of the annual fees, grouped by month, the effect is as follows

7. Annual report of the fee (monthly statistics)
Statistics of the annual fees, grouped by month, the effect is as follows

8. Statistics of water consumption, amount of charges (statistics by type)
The amount of water (integers, rounding) and charges for each inhabitant are counted according to the owner type, as
If the type has no data in the ledger table, a record with a value of 0 is required, with the following effect:

Analysis: The knowledge points used here include left outer joins, SUM (), group BY, Round (), and
NVL ()
9. Count the number of owners per region and list the total

10. Count the number of homeowners in each region, and list 0 if there are no owners in the area
:

Oracle-day02 under