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

This article mainly explains the use of common functions of mysql, the content is clear, interested friends can learn, I believe it will be helpful after reading.

Mysql function introduction: in order to simplify the operation, mysql provides a large number of functions for programmers to use (for example, if you want to enter the current time, you can call the now () function) where the function can appear: insert statement values (), update statement, delete statement, query statement and its clause. Aggregate function: the aggregate function is used to aggregate records (for example, if you don't want to know the exact information of each student record, but just want to know the number of student records, you can use count ()). Aggregate functions are used to deal with "pooling data" and do not require detailed record information. An aggregate function (aggregate function) is a function that runs on a row group and calculates and returns a single value.

Experimental table data (the following running data is based on this table):

Create table student (name varchar (15), gender varchar (15), age int); insert into student values ("lilei", "male", 18); insert into student values ("alex", "male", 17); insert into student values ("jack", "male", 20); insert into student values ("john", "male" 19); insert into student values ("nullpeople", "male", null) Avg (field) function: returns the average of the data in the specified field avg () calculates the average of the field by counting the number of rows in the table and calculating the sum of the data in the specified field. The avg () function ignores rows with column values of NULL, which are not counted if a row specifies that the field is null. Count (field) function: the number of rows (number of records) that returns the data of the specified field can be "*". When it is *, it represents the number of all records. When it is different from the number of fields, the number of records includes records with some fields as null, while the number of fields does not include records with null.

Max (field) function: returns the maximum value of the data of the specified field. If the data type of the specified field is a string type, compare by string first, and then return the maximum value. The max () function ignores rows whose column value is null (field) function: returns the minimum value of the data of the specified field. If the data type of the specified field is a string type, compare by string first, and then return the minimum value. Min () function ignores rows whose column value is null (field) function: returns the sum of the data of the specified field sum () function ignores rows with column value of null supplement: the field of the aggregate function ignores records with null value if the data is null. For example, avg: there are five rows of age data, but only four rows of age data, the result is only four rows, but if it is not for the field, then it will be calculated, for example, count (x) calculates the number of records, and the null value does not affect the result. There are some standard deviation aggregation functions, not described here, you can learn more about Baidu. The aggregate function also has an option DISTINCT in version 5. 0 +, which is similar to select, but ignores the same fields. [not available for count (x)] function for processing strings: merge string function: concat (str1,str2,str3...) Used to merge multiple strings into a single string. If there is a null in the value passed in, the end result is null. If you want to separate each string in the result of multiple string merging, you can use concat_ws (delimiter, str1,str2,str3...) If the delimiter passed in is null, the end result is null (but at this time if str has null does not affect the result) compare the string size function: strcmp (str1,str2) is used to compare the size of two strings. Returns 1 when the left is greater than the right, 0 zero when the left is equal to the right, and-1 comparison string function in similar programming languages (according to ascll code? Will be compared one by one from left to right until an unequal result is returned, otherwise the comparison will come to the end Get string byte function: length (str) is used to get the string byte length (returns the number of bytes, so note the character set) get the string character number function: char_length (str) is used to get the string length letter case conversion function: uppercase: upper (x), ucase (x) Lowercase lower (x), lcase (x) upper (x), ucase (x) are used to convert letters to uppercase, x can be a single letter or string lower (x), lcase (x) is used to convert letters to lowercase, x can be a single letter or string for already is, there is no case conversion. String lookup function: find_in_set (str1,str2) returns the position of the string str1 in str2. Str2 contains several strings separated by commas (you can see a list of str2, elements are multiple strings, and the result is the index position of str1 in the list of str2, starting with 1) field (str,str1,str2,str3...) Similar to find_in_set, but str2 changed from a list-like string to multiple strings, returning str in str1,str2,str3. The position in the. Locate (str1,str2): returns the position of the substring str1 in the string str2 position (str1 IN str2) returns the position of the substring str1 in the string str2 instr (str1,str2) returns the position of the substring str2 in the string str1 [note here] gets the substring at the specified location: elt (index,str1,str2,str3...) Return the string at the specified index position left (str,n) truncate n characters on the left of str right (str,n) truncate n characters on the right of str substring (str,index,len) truncate the len character string from the index position of str empty function: ltrim (str): remove the space on the left of the string str (str) remove the space on the right side of the string str trim () remove the space string replacement function on both sides of the string str: insert (str1,index,len) Str2) replace the len elements replace (str,str1,str2) of str1 with str2 from the index position of str1. Replace all the substrings str1 in str with the functions used by str2 to handle numeric values: absolute value function: abs (x) returns the absolute value of x up to the whole function: ceil (x) returns the integer rounded up and down of x: floor (x) returns the integer rounded down of x: modulus function: mod (x) Y) returns the result random number function of x mod y: rand () returns a random number within 0-1 if you want to use the same random value for all cases You can use rand (x), which returns the same random result when x is the same. Rounding function: round (xpeny) returns the numeric value x with y as a decimal result (rounded) numeric intercept function: truncate (xmemy) returns numeric x the result of intercepting y decimal places (not rounded) the function used to process time and date: get current date: curdate (), current_date () return format: get current time: curtime () The return format of current_time () is as follows: get the current date time: now () return format: select the number of months from the date: month (date) Monthname (date) choose the number of weeks from the date: week (date) return format: select the number of weeks from the date: year (date) return format: select the number of hours from time: hour (time) return format: select minutes from time: minute (time) return format: select the day of the week from time: weekday (date), dayname (date) return format: read the above content Do you have a further understanding of the use of common functions in mysql? if you want to learn more, please follow the industry information channel.

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.