MySQL common data types and functions _20160920

1. Common data types

　　For the data type of the field to be specified when creating the data table, I am sorting out several common work

You can refer to the data type http://www.w3school.com.cn/sql/sql_datatypes.asp

2. Function Processing

2.1 Handling of text (string)

We often need to work with string processing, take a part of the field/splice/replace/field length and so on MySQL built many functions to support these operations.

The following SQL code is used in work, organized, can be directly tested in SQLyog

Select INSTR ("abc", "C") as C in position in string abc, left ("ABCD", INSTR ("abc", "C")-1) as take ABCD string C front field, left ("ABCD", 3) as ABCD first 3 characters, Right ("ABCD", 3) as ABCD 3 characters, CONCAT ("AAA", "&", "111") as "with & Connection AAA and 111 characters", Concat_ws ("-", "AAA", "111") as " With-connect AAA and 111 characters ", replace (" ABCD "," C "," 111 ") as to replace C with 111, #对比insert INSERT (" ABCD ", 3,2," 111 ") as the 2-bit character after the string ABCD 3rd bit to 111, Length ("ABCD") as ABCD English string lengths, Long ("medium") as "in ' word length", Length ("-") as "symbol '-' Length", IF (Length (Left ("123", 1)) >=3, "Yes", " Not "as" judgment character ' 123 ' is not Chinese ", if (LENGTH (left (" Chinese ", 1)) >=3," yes "," not ") as" judgment character ' Chinese ' is not Chinese ", CONCAT (FORMAT (0.3568*100,2),"% ") as add percent to retain several decimals

　

MySQL common data types and functions _20160920