-sql study of five knowledge systems-fourth day

5. mysql Common functions

5.1 String Functions

Concat (S1,S2....,S3) merges the string and returns null if the argument has null;

Concat_ws (SEP,S1,S2...,SN) merges strings and concatenates them by delimiter, ignoring the parameter if there is a null value in the argument.

5.1.2, comparing string sizes

STRCMP (S1,S2), if s1>s2, returns 1. Equal returns 0, less than return-1.

5.1.3, getting string lengths

LENGTH () Char_length ()

Note: Length indicates the number of bytes that the character occupies, and char_length represents the string.

5.1.4, uppercase and lowercase conversion functions

UPPER () UCASE () converts a string to uppercase

LOWER () LCASE () converts a string to lowercase

5.1.5, intercepting String functions

Left (str,num) intercepts num characters from the

Right (str,num) intercepts num characters,

SUBSTRING (Str,num,len) A string of Len lengths starting at NUM positions in the ground, and the MySQL subscript is starting from 1. With Mid (Str,num,len)

5.1.6, remove the trailing spaces

LTRIM (str) Remove left space

RTRIM (str) Remove right space

TRIM (str) to remove left and right side spaces

5.1.7, replacing strings

INSERT (STR,POS,LEN,NEWSTR)

REPLACE (STR,OLDSTR,NEWSTR)

5.2. Numerical functions

5.2.1, obtaining random numbers

RAND (), rand (x), where Rand () is a completely random function, rand (x) random number is the same

5.2.2, get Integer function

Ceil (x) returns the smallest integer greater than or equal to X.

Floor (x) returns the largest integer less than or equal to X.

5.2.3, intercepting numerical functions

TRUNCATE (x, y) returns the value of the Y-bit after the decimal point of the value X. If Y is negative, the Y-bit is truncated to the left of the decimal point.

5.2.4, rounding function

ROUND (x) value x value after rounding operation

ROUND (x, y) holds the numeric value x y digits after the decimal point, rounded. Also if Y is negative, the decimal point is left.

5.3. Date Time function

5.3.1, gets the current date and time of the function

Now (), Current_timestamp (), localtime (), Sysdate ()

5.3.2, getting the current date

Curdate (), Current_date ()

These two functions will only return the date part of the data

5.3.3, getting the current time

Curtime (), Current_time ()

These two functions will only return the time part of the data

5.3.4, different ways to display the date and time

Unix_timestamp () Displays the current time in UNIX format

Year (), Get years

QUARTER (), Quarterly

Month (), MONTHNAME () displays the name of the month

WEEK (), Week

DayOfMonth (), the day ordinal of the month, DayOfYear (), the Day of the year

HOUR (), hour

MINUTE (), min

SECOND () seconds

The above function also has a common expression EXTRACT (type from date)

For example:

SELECT EXTRACT, EXTRACT (QUARTER from Now ()),
EXTRACT (MONTH from today ()), EXTRACT (WEEK from Now ()),
EXTRACT (HOUR from today ()), EXTRACT (MINUTE from Now ()),
EXTRACT (SECOND from now ())

5.3.5, with a specified datetime operation

Adddate (), subdate (), Increase reduction date

Addtime (), subtime (), Increase reduction time

5.4. System Information function

Version () to get the database's versioning information

Database (), gets the name

User (), Get username

LAST_INSERT_ID () Gets the last auto-inserted ID

PASSWORD () to string encryption

-sql study of five knowledge systems-fourth day