Hello everyone, I am xiao CAI, a desire to do CAI Not CAI xiao CAI in the Internet industry. Soft but just, soft praise, white piao just! Ghost ~ remember to give me a three – even oh!


This article mainly introduces the Mysql functions commonly used in practical applications

Refer to it if necessary

If it is helpful, do not forget the Sunday

Creation is not easy, white piao no righteousness!

Concept:

Equivalent to methods in Java, encapsulating a set of logical statements in a method body exposes method names 1) hiding implementation details 2) improving code reusability

Use:

Select function name (argument list) from table [

Body:

Character functions:

  • Length: Number of bytes (utF-8:3 bytes for a Chinese character, GBK: 2 bytes)

SELECT LENGTH('cbuc')        Output # 4

SELECT LENGTH('Chua Po Choi CBUC')   # 13 output

Copy the code

  • Concat: concatenates a string

SELECT CONCAT('C'.'_'.'BUC')   # output C_BUC

Copy the code

  • Upper: Uppercase letters

SELECT UPPER('cbuc')    # output CBUC

Copy the code

  • Lower: Lower the letter

SELECT LOWER('CBUC')   # output cbuc

Copy the code

  • Substr/substring: clipping string

    The method was refactored,

substr(str,pos)       # STR: the string to crop, pos: the length to crop

substr(str,pos,len)   # STR: the string to crop, pos/len: the number of bits to crop from

# the substring in the same way

Copy the code

  • Instr: Returns the index of the first occurrence of the substring, or 0 if none is present

SELECT INSTR('CAI Bu CAI'.'CAI')  Mysql > select * from '1';

Copy the code

  • Trim: string to remove characters

SELECT TRIM(' cbuc ')                 # output cbuc

SELECT TRIM('a' from 'aaaacbucaaaa')    # output cbuc

Copy the code

  • Lpad: Implements left padding of specified length with specified character

SELECT LPAD('cbuc'.6.The '*')        # * * cbuc output

Copy the code

  • Rpad: Implements a right fill of the specified length with the specified character

SELECT RPAD('cbuc'.6.The '*')       Output cbuc # * *

Copy the code

  • The replace replace

SELECT REPLACE('Little dishes love to sleep'.'sleep'.'eat')  Output side dish love to eat

Copy the code

Mathematical function

  • Round: round

SELECT round(1.5)  # 2 output

SELECT round(1.5)  The rounding is calculated as: absolute value rounding plus negative sign

Copy the code

  • Ceil: round up and return >= the minimum integer of the parameter

SELECT CEIL(1.5);  # 2 output

SELECT CEIL(1.5);  Output - # 1

Copy the code

  • Floor: Round down, return <= the largest integer for this parameter

SELECT FLOOR(1.5);  Output 1 #

SELECT FLOOR(1.5);  Output - 2 #

Copy the code

  • Truncate: truncation

SELECT TRUNCATE(3.1415926.2);  Output # 3.14

Copy the code

  • Mod: take over

SELECT MOD(10.3);  Output 1 #

SELECT MOD(10.- 3);  Output 1 #

Copy the code
Date function

  • Now: Returns the current system date and time

SELECT NOW(a)# output 2020-02-16 11:43:21

Copy the code

  • Curdate: Returns the current system date, excluding the time

SELECT CURDATE(a)Output # 2020-02-16

Copy the code

  • Curtime: returns the current time, excluding the date

SELECT CURTIME(a)# output 11:45:35

Copy the code

  • Year /month/day The value can be year, month, day, hour, minute, or second

SELECT YEAR(NOW())  Output 2020 other usage consistent

Copy the code

  • Str_to_date: Converts a character to a date in the specified format

SELECT STR_TO_DATE('02-17 2020'.'%c-%d %Y')   Output # 2020-02-17

Copy the code

  • Date_format: Converts dates to characters

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')  # Output 2020 February 17

Copy the code

  • Datediff: The difference between two dates

SELECT DATEDIFF(NOW(),'2020-02-12')           Output # 5

Copy the code

Other functions

  • VERSION: View the mysql VERSION

SELECT VERSION(a);# output 5.7.17

Copy the code

  • DATABASE: Displays the current DATABASE

SELECT DATABASE(a)# output cbuc_datebase

Copy the code

  • USER: View the current USER

SELECT USER(a)Output root @ # localhost

Copy the code

Flow control function

  • If function: similar to trinary operation

 SELECT IF(10<5.'big'.'small')  Small # output

Copy the code

  • Case function: Case can be used in two ways

  1. Switch case effect
Case Indicates the field or expression to be judged

When constant 1 then the value 1 or statement 1 to display;

When constant 2 then the value 2 or statement 2 to display;

.

Else The value n or statement n to display;

end

Copy the code
  1. Similar to multiple Ifs
case 

When condition 1 then the value 1 or statement 1 to display

When condition 2 then the value 2 or statement 2 to display

.

Else The value n or statement n to display

end

Copy the code
If you don’t like it, you’re a bad guy

This article is longer, can see here is good, the road to growth is endless

Today you work harder, tomorrow you will be able to say less words!