MySQL Learning (quad)--functions, views

Note: This MySQL series blog is for individual learning notes only.

This blog mainly deals with MySQL functions (mathematical Functions, String functions, date-time functions, process control functions, etc.), views.

First, the function

1. Mathematical Functions

For mathematical functions, all mathematical functions will return NULL if an error occurs

1.1 ABS (X) returns the absolute value of X

1.2 Bin (x) returns the binary number of X

1.3 Oct (x) returns the octal number of X

1.4 Hex (x) returns the hexadecimal number of X

1.5 Ceiling (x) returns the smallest integer value greater than X

1.6 Floor (x) returns the maximum integer value less than x

1.7 exp (x) returns the X-square of E (E is the base of the natural number)

1.8 Greatest (x1,x2,x3,..., xn) returns the largest value in the collection

1.9 Least (x1,x2,x3,..., xn) returns the smallest value in the collection

1.10 ln (x) returns the natural logarithm of X

1.11 log (x, y) returns the Y-base logarithm of X

1.12 mod (x, y) returns the modulus (remainder) of x/y

1.13 Pi () returns the value of Pi (pi)

1.14 rand () returns a random floating-point value of v , ranging from 0 to 1 (that is, its range is 0 ≤ v ≤1.0). If an integer parameter N is specified, it is used as a seed value to produce a repeating sequence.

Rand (N), the generation of random numbers depends on the seed, the seed is different, produces different random numbers, the seed is the same, regardless of how many times the same random number is produced. Therefore, do not set a random seed unless necessary.

　　　　　　　　　　　　To get a random integer r in the range of i ≤ r ≤ J , you need to use the expression floor (I + RAND () * (J – i + 1)) .

For example, to get a random integer within the range of 7 to 12, including 7 and 12, use the following statement: SELECT Floor (7 + (RAND () * 6));

　　　　　　　　　　　　

　　　　　　　　　　　　the combination of ORDER by RAND () with LIMIT is useful for selecting random samples from a set of columns ;

In the where statement, where each execution occurs, RAND () is recalculated once;

　　

1.15 round (x), round (x, y) returns the rounding of x with a value of y decimal place. To retain the y -bit to the left of the x -Value decimal point, set y to a negative value.

　　

1.16 sign (x) returns the symbol x as-1, 0, or 1, depending on whether the value of x is negative, 0, or positive.

　　

1.17 sqrt (x) returns the two root of a non-negative x

1.18 pow (x, y) returns the result value of the y -exponentiation of x

1.19 truncate (x, y) returns the number xthat is removed to the y digit after the decimal point. If y has a value of 0, the result does not have a decimal point or a fractional part. Y can be set to a negative number to intercept (zero) x the value of all lows after the beginning of the Y-bit of the decimal point. The rounding direction of all numbers is close to zero.

2. Aggregation functions (in select queries commonly used in GROUP BY clauses)

2.1 avg (expr) returns the average of expr . AVG () returns NULL if no matching row is found

2.2 Count (expr) returns the number of non-null values in the row retrieved by the SELECT statement

2.3 min (expr) returns the minimum value of an expression

2.4 max (expr) returns the maximum value of an expression

2.5 sum (expr) returns an expression and

2.6 Group_concat () returns a string result with a non-null value for a connection from a group

　　　　　　

3. String functions

3.1 ASCII (str) returns the value of the leftmost character of the string str . If str is an empty string, the return value is 0. If str is NULL, the return value is null. ASCII () is used for characters with numeric values from 0 to 255.

3.2 Length (str) returns a string of byte lengths

　　　　　　　　

3.3 char_length (str) returns the string length

　　　　　　　　

3.2 bit_length (str) returns a binary string of str length.

3.3 concat (s1,s2,s3,.., sn) returns the string resulting from the connection parameter. If any one of the arguments is NULL, the return value is null.

3.4 Concat_ws (Sep, s1, S2,.., sn) represents concat with Separator, which is a special form of concat (). The first parameter is the delimiter for the other parameter. The position of the delimiter is placed between the two strings to be concatenated. The delimiter can be a string, or it can be another parameter. If the delimiter is null, the result is null. The function ignores NULL values after any delimiter parameters.

　　　　　　　　

3.5 Insert (str, x, Y, instr) starts the string str from the x position, and the Y-character-length substring is replaced with InStr, returning the result.

　　　　　　　　

3.6 Find_in_set (str, strlist) strlist is a string of self-chaining separated by ', ' symbols, returning the position of STR in strlist.

　　　　　　　　

3.7 LCase (str) or lower (str) returns a lowercase string

3.8 UCase (str) or upper (str) returns an uppercase string

3.9 Left (str, x) returns the X-character to the ieft of STR

3.10 Right (str, x) returns the X-character of Str

3.11 LTrim (str) Remove the left space of str

3.12 Trim (str) removes space on both sides of STR

3.13 RTrim (str) Remove the space to the right of STR

3.14 position (subStr in str) returns the position of the substring subStr the first time it appears in Str

3.15 repeat (str, x) returns the result of STR repeating x

3.16 Reverse (str) Reverse str

3.17 strcmp (S1, S2) compare S1 and S2

　　

4. Date-time functions

4.1 curdate () or current_date () returns the current date

4.2 curtime () or Current_time () returns the current time

4.3 now () or Current_timestamp () returns the current date and time value in the form of ' yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS, depending on whether the function is used in a string or in a digital context.

　　　　　　

4.4 Year (date) returns the date of day

4.5 Month (date) returns the months of the date

4.6 MonthName (date) returns the month name of the date

4.7 Week (date) returns the week ordinal of a date year

4.8 Day or DayOfMonth (date) returns the date for that month, ranging from 1 to 31

4.9 dayname (date) returns the weekday name for date

4.10 DayOfWeek (date) returns the weekday index of date (1 = Sunday, 2 = Monday, ..., 7 = Saturday).

4.11 dayofyear (date) returns the number of days in a year that corresponds to a date , ranging from 1 to 366.

　　　　　　

　　

4.12 Date (date|datetime) Fetch part

4.13 DateDiff (expr1, EXPR2) returns the number of days between the start time expr and the end time expr2 .

　　　　　　

4.14 date_add (date|datetime, Interval expr type) performs a date operation. Date is used to specify the start time. expr is an expression that specifies the time interval value to be added or subtracted from the start date. If you want to perform a minus operation, add a '-' before expr. type is a keyword that indicates the kind of time interval.

4.15 date_sub (date|datetime, Interval expr type) Date_add () performs the add operation, Date_sub () performs the subtraction operation

Date_sub () is the inverse process of date_add

4.16 date_format (date, format) arranges the format of the date value based on the format string.

5. Cryptographic functions

5.1 MD5 (STR) MD5 encryption, low-impact, irreversible, or null if the argument is null. For example, the return value can be used as a hash keyword.

　　　　　　　

5.2 Password (str) computes and returns a password string from the source code STR and returns NULL when the argument is null. The PASSWORD () function is used in the authentication system of the MySQL server; you should not use it in your personal application.

　　　　　　

5.3 sha (str) or SHA1 (str) SHA1 () can be considered a more secure password function, equivalent to MD5 ().

6. Process Control functions

6.1 Case [value] when [compare-value] then result [when [compare-value] then result] ... [Else result] End

　　　　　　

6.2 If (expr1,expr2,expr3) if expr1 is True (expr1 <> 0 and EXPR1 <> NULL), the return value of if () is expr2; Otherwise the return value is EXPR3. The return value of IF () is a numeric value or a string value.

　　　　　　

6.3 Ifnull (EXPR1, expr2) if expr1 is not NULL, the return value of Ifnull () is expr1; Otherwise its return value is expr2. The return value of Ifnull () is either a number or a string

6.4 Nullif (EXPR1, expr2) if expr1 = expr2 is established, then the return value is NULL, otherwise the return value is expr1.

7 System Information functions

7.1 User () or current_user () returns the current session authenticated username and hostname combination.

　　　　　　

7.2 CharSet (str) returns the character set of the string argument.

7.3 Database () returns the default (current) name of the databases using the UTF8 character set.

7.4 Row_count () Row_count () returns the number of rows that were upgraded, inserted, or deleted by the preceding statement.

7.5 version () database versions

　　MySQL Function Summary:

1.mysql function will certainly affect the query speed, should be in the construction of the table, through a reasonable table structure to reduce the use of functions, such as the addition of redundant fields.

2. If you really want to use functions, such as the formatting of time, priority is placed on the business logic layer rather than in the SQL query statement.

3. The biggest disadvantage of using a function when querying is that if you are querying a column, the column uses a function and the column will no longer use the index.

Second, Views (view)

In the query, we often look at the results of the query as a temporary table, the view can be seen as a virtual table, the table is a projection of some kind of operation. The data of the temporary table does not change, and the projected data changes according to the data of the original table.

A view in a computer database is a virtual table whose contents are defined by a query. As with a real table, a view contains a series of column and row data with names. However, the view does not exist in the database as a stored set of data values.

1. Syntax for creating views

Create [algorithm = merge|temptable|undefined] View <view_name> as <select query >;

Once the view is created, it can be viewed as a table.

　　　　　　

2. In some cases, the view is also modifiable, which requires that the data of the view correspond to the data one by one of the table; one by one corresponds to: According to the select relationship, the rows taken from the table can only be computed for the row identified in the view, and conversely, any row in the view can be drawn according to the select relationship A determined row in the Anti-roll table.

　　　　　　

　　　　　　

　　　　　　

　　　　　　

3. Show CREATE VIEW <view_name> This statement gives 1 CREATE VIEW statements created for the given views

　　　　　　

4. Drop View <view_name> [, <view_name>] ability to delete 1 or more views

5. Create a table using Create, generate three files, frm (table structure, auto-definition, etc.), MYD (data), MYI (index), you can see that the view has only one table structure definition.

　　　　　　

6. algorithm algorithm three values: Merge, temptable, or undefined. If there is no algorithm clause, the default algorithm is undefined (undefined). The algorithm affects how MySQL handles views.

For some simple views, it does not create a temporary table in the process of functioning, but only to save the conditions, the next time to query, the conditions to merge, directly to the base table , the construction of temporary tables compared to the overhead.

　　　　　　If you do not specify algorithm, you are more inclined to use merge.

　　　　　　

Algorithm=temptable is suitable for more complex views, such as views with aggregate functions, which cannot be combined to query the base table, it is necessary to generate a temporary table.

　　　　　　

View Summary:

1. Once the view is created, it can be viewed as a table .

2. The view can simplify the query, for some complex statistics, you can first use the view to generate an intermediate result, and then query the View .

3. more granular permissions control, for example, you can use the view to hide the User table password field, and open it to others .

4. large amount of data, you can use the view to save the query results of all the tables in a view in a table .

5. The change of the data of the table will affect the change of the view;

6. The view can also be modified in some cases, requiring that the data of the view correspond to the data of the table one by one. The so-called one by one correspondence, must be through the select relationship, the view can detect the corresponding data in the base table, the base table can also query the corresponding data in the view, for example, the select sentence has an order by limit, etc. can not be corresponding.

7. The view definition is subject to the following limitations:

The SELECT statement cannot contain subqueries from the FROM clause.

The SELECT statement cannot reference a system or user variable.

The SELECT statement cannot reference a preprocessing statement parameter.

8. Some views are updatable. That is, you can use them in statements such as update, delete, or insert to update the contents of the base table. For updatable views, you must have a one-to-one relationship between the rows in the view and the rows in the base table. There are also specific other structures that make the view non-updatable.

　　　　　　

9. algorithm (algorithm)　　　

The optional algorithm clause is a MySQL extension to standard SQL. Algorithm three values are desirable: MERGE, temptable, or undefined. If there is no algorithm clause, the default algorithm is undefined (undefined). The algorithm affects how MySQL handles views.

　　　　For merge, the text of the statement referencing the view is merged with the view definition so that some part of the view definition supersedes the corresponding part of the statement.

　　　　For temptable, the result of the view is placed in a temporary table, which is then used to execute the statement, and a temporary table is a little expensive.

　　　　For Undefined,mysql, you will select the algorithm that you want to use. If possible, it tends to merge rather than temptable, because the merge is usually more efficient, and if a temporary table is used, the view is not updatable .

　　　　The 1 reasons for explicitly selecting temptable are that the locks on the base table can be released after the temporary table is created and before the statement processing is complete . Locking is released faster than the merge algorithm, so that other clients using the view are not masked for long periods of time.

MySQL Learning (quad)--functions, views