Shulou(Shulou.com)06/01 Report--

1. String class function

(1) CONCAT (s 1, 2, 3.) Connection string

For example: SELECT CONCAT ('1m 2') FROM DUAL

Output: 12

(2) LOWER (s) changes all strings to lowercase

For example: SELECT LOWER ('ABC') FROM DUAL

Output: abc

(3) UPPER (s) changes all strings to uppercase

For example: SELECT UPPER ('abc') FROM DUAL

Output: ABC

(4) LTRIM (s) remove the space on the left side of the string

For example: select LTRIM ('abc') from dual

Output: abc

(5) RTRIM (s) remove the space on the right side of the string

For example: select LTRIM ('abc') from dual

Output: abc

(6) TRIM (s) removes the spaces on the left and right sides of the string

For example: select LTRIM ('abc') from dual

Output: abc

(7) LPAD uses the string pad to fill the left side of s until the length reaches len

For example: SELECT LPAD ('1pm, 5pm, 0') FROM DUAL

Output: 00001

(8) RPAD uses the string pad to fill the right side of s until the length reaches len

For example: SELECT RPAD ('1pm, 5pm, 0') FROM DUAL

Output: 10000

(9) REPEAT (sPerry x) returns after repeating s by x

For example: select REPEAT ('axioma5) from dual

Output: aaaaa

(10) REPLACE replaces the character in the string containing form with target

For example: SELECT REPLACE ('abc','a','A') FROM DUAL

Output: Abc

(11) STRCMP (S1 Magi S2) compare S1 with S2. If the same returns 0 Magi S2 greater than S1, return 1 Magi S2 less than S1 return-1

For example: SELECT STRCMP, FROM DUAL.

Output:-1 0 1

(12) LEFT (sjournal x) returns x characters to the left of the string

For example: SELECT LEFT ('abc',2) FROM DUAL

Output: ab

(13) RIGHT (sjournal x) returns x characters to the right of the string

For example: SELECT RIGHT ('abc',2) FROM DUAL

Output: bc

(14) MID (sdepartment xrem y) returns the string x position starting y characters

For example: SELECT MID ('abcd',3,2) FROM DUAL

Output: cd

(15) SUBSTRING returns the string x bit starting y characters, which is basically the same as MID.

For example: SELECT SUBSTRING ('abcd',3,2) FROM DUAL

Output: cd

(16) INSERT (srecoery xrem yref) replaces the string x position starting y characters with form characters

For example: SELECT INSERT ('abcd',3,2,'FF') FROM DUAL

Output: abFF

(17) LENGTH (s) returns the length of s

For example: SELECT LENGTH ('123') FROM DUAL

Output: 3

(18) REVERSE (s) returns s to reverse the order

For example: SELECT REVERSE ('abc') FROM DUAL

Output: cba

two。 Digital class function

(1) CEIL (n) returns an integer greater than the maximum of n

For example: SELECT CEIL (100.1) FROM DUAL

Output: 101

(2) FLOOR (n) returns an integer less than the maximum of n

For example: SELECT CEIL (100.1) FROM DUAL

Output: 100

(3) RAND () returns a random number from 0 to 1

For example: SELECT RAND () FROM DUAL

Output: 0.13824381133377908

(4) ROUND (nQuery y) rounds n to retain y decimal places

For example: SELECT ROUND (10.127jue 2) FROM DUAL

Output: 10.13

(5) TRUNCATE (nQuery y) preserves n with y decimal places

For example: SELECT TRUNCATE (10.127777777) FROM DUAL

Output: 10.12

3. Date class function

(1) NOW () returns the current date and time

For example: SELECT NOW () FROM DUAL

Output: 2017-06-06 20:44:58

(2) CURDATE () returns the current date

For example: SELECT CURDATE () FROM DUAL

Output: 2017-06-06

(3) CURTIME () returns the current time

For example: select CURTIME () from dual

Output: 20:46:15

(4) WEEK (date) returns the week of the year

For example: SELECT WEEK ('2017-06-06') FROM DUAL

Output: 23

(5) YEAR (date) returns the year in the date

For example: SELECT YEAR ('2017-06-06') FROM DUAL

Output: 2017

(6) DATE_FORMAT (sfocus pattern) formatted date

For example: SELECT DATE_FORMAT (NOW (),'% Y% m% d') FROM DUAL

Output: 20170606

Format parameters:

% y represents a two-digit year. For example: (2017 returns 17)

% Y represents a four-digit year. For example: (2017 returns 2017)

% m represents a two-digit month. For example: (01jue 02pr .12)

% c represents the month of the number. For example: (1, 2, 3, 4, 4, 12)

% M means Yueming, English name.

D represents the number of days in two digits. For example: (01pr 02pr 03pr .31)

% e represents the number of days of the number. For example (1, 2, 3, 4, 4, and 31)

% H represents two-digit hours, 24-hour system. For example: (01pr 02pr .pr 24)

% I represents the number of two-digit minutes. For example: (01pd02.pr. 60)

S s represents the number of seconds of two digits. For example: (01pd02.pr. 60)

(7) DATEDIFF (D1 and D2) returns the number of days between D1 and D2

For example: SELECT DATEDIFF ('2017-06-07) FROM DUAL

Output: 1

(8) DATE_ADD (d _ intermediate y type) adds a specified type y value to the date

For example: select DATE_ADD ('2017-06-07 recording interval 1 month) from dual

Output: 2017-07-07

Type parameters:

YEAR represents year

MONTH represents month

DAY represents day

HOUR stands for hours

MINUTE stands for minutes

SECOND stands for seconds

(9) TO_DAYS (date) returns the number of days date has been in since 20 years.

4. Flow control class function

(1) IF (vQuery tmenf) returns t if v is true, otherwise returns f

For example: SELECT IF (1pm, 1pm, 2') FROM DUAL

Output: 2

(2) IFNULL (v1PowerV2) if v1 does not return v1 for null, otherwise return v2

For example: SELECT IFNULL (NULL,'1') FROM DUAL

Output: 1

(3) CASE WHEN v then v1 end determines that if v is true, return v1.

For example: SELECT CASE WHEN 1, 1 THEN 'true' ELSE' false' END FROM DUAL

Output: true

For example: SELECT CASE WHEN 1 THEN 'true' WHEN 2' 2 THEN 'test' ELSE' false' END FROM DUAL

Output: test

5. Conversion class function

(1) CAST (v as type) converts data types.

Type parameters:

Character type: CHAR

Date: DATE

Time: TIME

Date and time type: DATETIME

Floating point numbers: DECIMAL

Integer: SIGNED

Unsigned integers: UNSIGNED

6. Database class function

(1) DATABASE () returns the current database name

(2) VERSION () returns the current database version

(3) INET_ATON (ip) returns the IP represented by numbers.

(4) INET_NTOA (num) converts numeric IP to IP

(5) PASSWORD (s) returns the encrypted version

(6) MD5 (s) returns the MD5 encryption value

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.