MySQL common built-in functions detailed description

The function can use the field name as a variable, and the value of the variable is all the values of that column, and when you organize 98 of online dictionary data (http://zidian.98zw.com/), there is a need to Want to extract the first pinyin from the Polyphone duoyinzi field value as the value of the phonetic pinyin field, such as: Duoyinzi (ā,á,ǎ,à,a), want to extract ā as the value of pinyin; there are tens of thousands of of data, do not want to use a program to deal with, just want to use a SQL to achieve, Later learned the next MySQL commonly used built-in functions, can be done; sql:update Ol_zidian set Pinyin=left (Duoyinzi,locate (Duoyinzi, ', ')); This means that the value of the pinyin is equal to the first occurrence of the Douyinzi left, the position of the number;

Here is a general description of common MySQL built-in functions, I hope you use Help

First, the numerical function
ABS (x): Return absolute value
Ceil (x): Returns the smallest integer greater than X
Floor (x): Returns the largest integer less than x
MoD (x, y): Returns the modulo of X and Y
RAND (): Returns a random number between 0-1 select round (rand () *100);
Round (x, y): Returns the rounding result of the Y-decimal fraction of the parameter X
Truncate (x, y): Returns the result of a number x truncated to a Y decimal place

Second, String function
In PHP, most of MySQL has

1.concat (S1,S2....SN): Concatenate incoming parameters into a string
2.insert (Str,x,y,insert): From the X position of STR, replace the y-length string with insert
Select Insert (' ABCDEFG ', 2,3, ' hello ');
3.lower (str), Upper (str): Converts a string to uppercase, lowercase
4.left (str,x) Right (str,x) returns the left side of the STR (to) x characters, and x NULL to return NULL
5.lpad (Str,n,pad) rpad (Str,n,pad) fills the string str from the leftmost (right) with a pad until the total length

Reached N
Select Name,lpad (name,10, ' # '), Rpad (name,10, ' (') from cats;
6.trim (), LTrim (), RTrim () remove both sides, left, right space
Select Concat (' 1 ', trim (' abc '), ' 2 '), concat (' 1 ', LTrim (' abc '), ' 2 '), concat

(' 1 ', RTrim (' abc '), ' 2 ') \g;
7.replace (STR,A,B) replaces all string A with string B in string str
8.STRCMP (S1,S2): If S1 is smaller than S2, returns-1 if S1 is larger than S2 and returns 1 if equal is 0 (compared to

ASC2 code)
9.substring (str,x,y) returns a substring of length y from position x in the string str

Three, date function
    complete with php timestamp
    curdate () Curtime () Now ();   Select Curdate ();
        Select Unix_timestamp (now ());
        Select From_unixtime (1331110656);
        Select Week (now ()), Year (now ());
        Select Hour (Curtime ()), Minute (Curtime ());
        Select MonthName (now ());
        Select Date_format (now (), "%y-%m-%d%h:%i:%s");
Four, Process Control statements
    CREATE TABLE salary (ID int,salary decimal (9,2));

if (value,t,f); Select Id,salary,if (salary>300, ' High ', ' low ') from salary;
Ifnull (t,f) Select Id,salary,ifnull (salary,0) from salary;
case [value1] and then [Result1]...else[default]end;
Select Case when salary<=300 and ' low ' else ' high ' end from salary;
V. Other functions (\s can be seen)
Database () Select database ();
Version ()
User ()
Inet_aton (IP) converts a string address to a network address
Password () encrypt MySQL user
MD5 () Encryption of user passwords
SELECT * from Mysql.user \g;

The index of the full-text index type fulltext in MySQL. Fulltext indexes are only available for MyISAM tables; they can be created as part of a CREATE TABLE statement from char, varchar, or text columns, or subsequently added using ALTER TABLE or CREATE INDEX. For larger datasets, enter your data into a table without a Fulltext index, and then create an index that is faster than entering the data into an existing Fulltext index.

MySQL common built-in functions detailed description