expression
Those who have studied primary school mathematics should know that the combination of numbers and operators is called an expression, for example:
1 + 1 5 * 8
We can call the numbers in it operands, and the operators can be called operators. In particular, a single operand can also be regarded as a special expression.
There is also the concept of expression in MySQL, but the meaning of operands and operators has been expanded. Take a closer look below.
Operand
Operands in MySQL can be of the following types:
-
constant
Constants are easy to understand. The numbers, strings and time values we usually use can be called constants. They are definite values, such as number 1, string 'abc', time value 2019-08-16 17:10:43 and so on.
-
Listing
For a specific table, its column name can be regarded as part of the expression, such as student_ In the info table, both number and name can be used as operands.
-
function call
MySQL has the concept of a function, for example, to get the function NOW of the current time, and adding a parenthesis after the function is a function call, such as NOW().
If you don't know the concept of function, we will NAG in detail later. It's ok if you don't know now～
-
Scalar subquery or row subquery
We'll talk about this sub query in detail later
-
Other expressions
An expression can also be used as an operand and another operand to form a more complex expression, for example (assuming col is a column name):
-
(col - 5) / 3
-
(1 + 1) * 2 + col * 3
-
Tip: of course, there are more than a few things that can be used as operands, but this is an introductory book. After you are proficient in using MySQL, go to the document to see more types of operands.
Operator
For Xiaobai, it should be enough for us to be familiar with the following three operators:
-
arithmetic operator
It's the pile of addition, subtraction, multiplication and division. Let's see what MySQL supports:
Operator Example describe + a + b addition - a - b subtraction * a * b multiplication / a / b division DIV a DIV b Division, taking the integer part of the quotient % a % b Surplus - -a minus sign When using arithmetic operators in MySQL, it should be noted that DIV and / both represent division operators, but DIV only takes the integer part of the quotient and / retains the decimal part of the quotient. For example, expressions The result of 2 DIV 3 is 0, while the result of 2 / 3 is 0.6667.
-
Comparison operator
We have seen the comparison operators in the search criteria. Let's copy all the commonly used ones:
Operator Example describe = a = b a equals b < > or= a <> b a is not equal to b < a < b a less than b <= a <= b a is less than or equal to b > a > b a is greater than b >= a >= b a is greater than or equal to b BETWEEN a BETWEEN b AND c Meet B < = a < = C NOT BETWEEN a NOT BETWEEN b AND c Not satisfying B < = a < = C IN a IN (b1, b2, ...) a is one of b1, b2 NOT IN a NOT IN (b1, b2, ...) a is not any of b1, b2 IS NULL a IS NULL The value of a is NULL IS NOT NULL a IS NOT NULL The value of a is not NULL LIKE a LIKE b a match b NOT LIKE a NOT LIKE b a mismatch b The expression connected by comparison operators is also called Boolean expression, which indicates TRUE or FALSE, and can also be called TRUE or FALSE. For example, 1 > 3 means FALSE, 3= 2 means TRUE.
-
Logical operator
Logical operators are used to connect multiple Boolean expressions. We need to understand these logical operators:
Operator Example describe AND a AND b The expression is true only if a and b are true at the same time OR a OR b As long as either a or b is true, the expression is true XOR a XOR b a and b have and only one is true, and the expression is true
Use of expressions
As long as these operands and operators are combined with each other, they can form an expression. Expressions are mainly used in the following two ways:
-
Put in query list
In front of us, we put the column names in the query list (* represents all column names ~). Column name is only a super simple expression. We can treat any expression as part of the query list. For example, we can query student_ In the score table, add 100 to the data in the score field, as follows:
mysql> SELECT number, subject, score + 100 FROM student_score; +----------+-----------------------------+-------------+ | number | subject | score + 100 | +----------+-----------------------------+-------------+ | 20180101 | Postpartum care of sows | 178 | | 20180101 | On Saddam's war preparation | 188 | | 20180102 | Postpartum care of sows | 200 | | 20180102 | On Saddam's war preparation | 198 | | 20180103 | Postpartum care of sows | 159 | | 20180103 | On Saddam's war preparation | 161 | | 20180104 | Postpartum care of sows | 155 | | 20180104 | On Saddam's war preparation | 146 | +----------+-----------------------------+-------------+ 8 rows in set (0.00 sec) mysql>
number, subject and score + 100 are expressions, and the names of the columns in the result set will also use the names of these expressions by default. Therefore, if you think the original name is bad, we can use aliases:
mysql> SELECT number, subject, score + 100 AS score FROM student_score; +----------+-----------------------------+-------+ | number | subject | score | +----------+-----------------------------+-------+ | 20180101 | Postpartum care of sows | 178 | | 20180101 | On Saddam's war preparation | 188 | | 20180102 | Postpartum care of sows | 200 | | 20180102 | On Saddam's war preparation | 198 | | 20180103 | Postpartum care of sows | 159 | | 20180103 | On Saddam's war preparation | 161 | | 20180104 | Postpartum care of sows | 155 | | 20180104 | On Saddam's war preparation | 146 | +----------+-----------------------------+-------+ 8 rows in set (0.00 sec) mysql>
In this way, the score + 100 column can be displayed according to the alias score!
It should be noted that the expression placed in the query list may not involve column names, as follows:
mysql> SELECT 1 FROM student_info; +---+ | 1 | +---+ | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | +---+ 6 rows in set (0.01 sec) mysql>
Because student_ There are 6 records in info, so there are 6 results in the result set. However, there is only one constant 1 in our query list, so the values of all results are also constant 1. When the column name is not involved in the query list, we can even omit the table name after the FROM clause, as follows:
mysql> SELECT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql>
But what's the practical use of writing like this? It seems that there is. You can make a calculator [chuckle] ~
-
As search criteria
When we introduce the search criteria, we introduce expressions with column names. The search criteria can also be without column names, for example:
mysql> SELECT number, name, id_number, major FROM student_info WHERE 2 > 1; +----------+-----------+--------------------+--------------------------+ | number | name | id_number | major | +----------+-----------+--------------------+--------------------------+ | 20180101 | Du Ziteng | 158177199901044792 | Computer science and Engineering | | 20180102 | Du Qiyan | 151008199801178529 | Computer science and Engineering | | 20180103 | Fan Tong | 17156319980116959X | software engineering | | 20180104 | Shi Zhenxiang | 141992199701078600 | software engineering | | 20180105 | Fan Jian | 181048199308156368 | Aircraft design | | 20180106 | Zhu Yiqun | 197995199501078445 | Electronic information | +----------+-----------+--------------------+--------------------------+ 6 rows in set (0.00 sec) mysql>
Since our search condition is 2 > 1, which is true for every record in the table, the final query result is all records. But it's a little silly to write so. It doesn't cost a dime and has no practical significance. Therefore, generally, the search conditions will include column names.
function
In the process of using mysql, we often have some requirements, such as converting lowercase letters in a given text into uppercase letters, extracting the month value in a date data, and so on. In order to solve these common problems, the uncle who designed MySQL kindly provided us with many so-called functions, such as:
-
The UPPER function is used to convert lowercase letters into uppercase letters in a given text.
-
The MONTH function is used to extract the MONTH value from a date data.
-
The NOW function is used to get the current date and time.
If we want to use these functions, we can add a parenthesis () after the function name to call this function, which is called for short. For example, NOW() means calling the NOW function to get the current date and time. For some functions containing parameters, we can also fill the parameters in parentheses (). For example, UPPER('abc ') means to convert the string' ABC 'to uppercase format.
Here are some common MySQL built-in functions:
Text processing function
| name | Call example | Example results | describe |
|---|---|---|---|
| LEFT | LEFT('abc123', 3) | abc | The given string takes a substring of the specified length from the left |
| RIGHT | RIGHT('abc123', 3) | 123 | The given string takes a substring of the specified length from the right |
| LENGTH | LENGTH('abc') | 3 | The length of the given string |
| LOWER | LOWER('ABC') | abc | The lowercase format of the given string |
| UPPER | UPPER('abc') | ABC | Uppercase format of the given string |
| LTRIM | LTRIM(' abc') | abc | The format of the left space of the given string after removal |
| RTRIM | RTRIM('abc ') | abc | The format of the given string after removing the space on the right |
| SUBSTRING | SUBSTRING('abc123', 2, 3) | bc1 | The given string intercepts a substring of the specified length from the specified position |
| CONCAT | CONCAT('abc', '123', 'xyz') | abc123xyz | Concatenate the given strings into a new string |
Let's take the SUBSTRING function as an example:
mysql> SELECT SUBSTRING('abc123', 2, 3);
+---------------------------+
| SUBSTRING('abc123', 2, 3) |
+---------------------------+
| bc1 |
+---------------------------+
1 row in set (0.00 sec)
mysql>
As we mentioned earlier, function call is also an expression operand, which can be connected with other operands to form an expression as part of the query list or in the search criteria. Let's take the CONCAT function as an example:
mysql> SELECT CONCAT('Student number is', number, 'Where are your students<', subject, '>The results of the course are:', score) AS Achievement description FROM student_score;
+---------------------------------------------------------------------------------------+
| Achievement description |
+---------------------------------------------------------------------------------------+
| The score of student No. 20180101 in postpartum nursing of sows is: 78 |
| The grade of student No. 20180101 in the course on Saddam's war preparation is: 88 |
| The score of student No. 20180102 in postpartum nursing of sows is: 100 |
| The grade of student No. 20180102 in the course on Saddam's war preparation is: 98 |
| The score of student No. 20180103 in postpartum nursing of sows is: 59 |
| The grade of student No. 20180103 in the course on Saddam's war preparation is: 61 |
| The score of student No. 20180104 in the course of postpartum nursing of sows is: 55 |
| The grade of student No. 20180104 in the course on Saddam's war preparation is 46 |
+---------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql>
Date and time handler
Some of the following functions will use the current date. The date I edited the article is August 28, 2019. When actually calling these functions, your current time shall prevail.
| name | Call example | Example results | describe |
|---|---|---|---|
| NOW | NOW() | 2019-08-16 17:10:43 | Returns the current date and time |
| CURDATE | CURDATE() | 2019-08-16 | Returns the current date |
| CURTIME | CURTIME() | 17:10:43 | Returns the current time |
| DATE | DATE('2019-08-16 17:10:43') | 2019-08-16 | Extract the date of a given date and time value |
| DATE_ADD | DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 DAY) | 2019-08-18 17:10:43 | Adds the given date and time values to the specified time interval |
| DATE_SUB | DATE_SUB('2019-08-16 17:10:43', INTERVAL 2 DAY) | 2019-08-14 17:10:43 | Subtracts the specified time interval from the given date and time value |
| DATEDIFF | DATEDIFF('2019-08-16', '2019-08-17'); | -1 | Returns the number of days between two dates (a negative number indicates that the date represented by the previous parameter is relatively small) |
| DATE_FORMAT | DATE_FORMAT(NOW(),'%m-%d-%Y') | 08-16-2019 | Displays the date and time in the given format |
There are some points to note when using these functions:
-
When using the DATE_ADD and DATE_SUB functions, it should be noted that the time interval units for adding or subtracting can be defined by yourself. Below are some time units supported by MySQL:
Time unit describe MICROSECOND millisecond SECOND second MINUTE minute HOUR hour DAY day WEEK week MONTH month QUARTER quarter YEAR year If we increase the time value of 17:10:43 on August 16, 2019 by 2 minutes, it can be written as follows:
mysql> SELECT DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 MINUTE); +----------------------------------------------------+ | DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 MINUTE) | +----------------------------------------------------+ | 2019-08-16 17:12:43 | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql> -
When using the DATE_FORMAT function, it should be noted that we can customize the display format of date and time through some so-called format characters. Below are some date and time format characters commonly used in MySQL and their corresponding meanings:
Formatter describe %b Abbreviated month names (Jan, Feb,..., Dec) %D Date in month with English suffix (0th, 1st, 2nd,..., 31st)) %d Date in month in numeric format (00, 01, 02,..., 31) %f Microseconds (000000- 999999) %H 24-hour system (00-23) %h Twelve hour system (01-12) %i Minutes in numeric format (00-59) %M Month name (January, February,..., December) %m Month in numerical form (00-12) %p Morning or afternoon (AM for morning, PM for afternoon) %S Seconds (00-59) %s Seconds (00-59) %W Sunday, Monday,..., Saturday %w Day of the week (0 = Sunday, 1 = Monday, 6 = Saturday) %Y 4-digit year (e.g. 2019) %y 2-digit year (e.g. 19) We can describe the display format we want with the corresponding format symbol, like this:
mysql> SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p'); +----------------------------------------+ | DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') | +----------------------------------------+ | Aug 16 2019 05:10 PM | +----------------------------------------+ 1 row in set (0.00 sec) mysql>
'% B% d% Y% H:% I% p' is a display format described by formatter, which means that the corresponding date and time should be displayed in the following way:
-
First output the abbreviated month name (format character% b), that is, Aug in the example, and then output a space.
-
Then output the date in the month in digital format (format character% d), that is, 16 in the example, and then output a space.
-
Output a 4-digit year (format character% Y), that is, 2019 in the example, and then output a space.
-
Then output the hour in twelve hour system (format character% h), that is, 05 in the example, and then output a colon:.
-
Then output the minutes in numerical format (format character% i), that is, 10 in the example, and then output a space.
-
Finally, morning or afternoon (format character% p) is output, that is, PM in the example.
-
Numerical processing function
Some commonly used functions in mathematics are listed below, which can be very useful when encountering businesses requiring mathematical calculation:
| name | Call example | Example results | describe |
|---|---|---|---|
| ABS | ABS(-1) | 1 | Take absolute value |
| Pi | PI() | 3.141593 | Return pi |
| COS | COS(PI()) | -1 | Returns the cosine of an angle |
| EXP | EXP(1) | 2.718281828459045 | Returns the specified power of e |
| MOD | MOD(5,2) | 1 | Returns the remainder of division |
| RAND | RAND() | 0.7537623539136372 | Returns a random number |
| SIN | SIN(PI()/2) | 1 | Returns the sine of an angle |
| SQRT | SQRT(9) | 3 | Returns the square root of a number |
| TAN | TAN(0) | 0 | Returns the tangent of an angle |
Aggregate function
If the functions described above are placed in the query list in the form of function calls, the function will be called once for each record in the table that meets the WHERE condition. For example:
mysql> SELECT number, LEFT(name, 1) FROM student_info WHERE number < 20180106; +----------+---------------+ | number | LEFT(name, 1) | +----------+---------------+ | 20180101 | Du | | 20180102 | Du | | 20180103 | Model | | 20180104 | history | | 20180105 | Model | +----------+---------------+ 5 rows in set (0.00 sec) mysql>
The name field of each record in the student_info table that meets the search criteria of number < 20180106 will be used as the parameter of the LEFT function in turn. As a result, the first character of these people's names will be extracted. However, some functions are used to count data, such as the number of rows in the table and the maximum value of a column of data. We call this function poly Set function. The following describes several commonly used aggregate functions in MySQL:
| Function name | describe |
|---|---|
| COUNT | Returns the number of rows in a column |
| MAX | Returns the maximum value of a column |
| MIN | Returns the minimum value of a column |
| SUM | Returns the sum of the values in a column |
| AVG | Returns the average value of a column |
Tip: the name aggregate function is not intuitive. Understanding it as a statistical function may be more in line with the understanding habits of Chinese people.
COUNT function
The COUNT function is used to COUNT the number of rows. It can be used in the following two ways:
-
COUNT(*): counts the number of rows in the table, regardless of whether the column value is NULL or not.
-
Count (column name): count a specific column and ignore the row whose column is NULL.
The difference between the two is whether the rows with NULL value in the statistical column will be ignored! The difference between the two is whether the rows with NULL value in the statistical column will be ignored! The difference between the two is whether the rows with NULL value in the statistical column will be ignored! Important things have been said three times. I hope you can remember them. Let's count the students_ There are several rows of records in the info table:
mysql> SELECT COUNT(*) FROM student_info; +----------+ | COUNT(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) mysql>
MAX function
The MAX function is used to query the maximum value of data in a column, with student_ Take the score column in the score table as an example:
mysql> SELECT MAX(score) FROM student_score; +------------+ | MAX(score) | +------------+ | 100 | +------------+ 1 row in set (0.00 sec) mysql>
The maximum value of 100 in the score column is found ~
MIN function
The MIN function is used to query the minimum value of data in a column, with student_ Take the score column in the score table as an example:
mysql> SELECT MIN(score) FROM student_score; +------------+ | MIN(score) | +------------+ | 46 | +------------+ 1 row in set (0.00 sec) mysql>
The minimum value 46 of the score column is found ~
SUM function
Is the SUM function used to calculate the SUM of a column of data, or as student_ Take the score column in the score table as an example:
mysql> SELECT SUM(score) FROM student_score; +------------+ | SUM(score) | +------------+ | 585 | +------------+ 1 row in set (0.01 sec) mysql>
The total score of all students is 585, which is much faster than our own calculation
AVG function
Is the AVG function used to calculate the average of a column of data, or is it based on student_ Take the score column in the score table as an example:
mysql> SELECT AVG(score) FROM student_score; +------------+ | AVG(score) | +------------+ | 73.1250 | +------------+ 1 row in set (0.00 sec) mysql>
You can see that the average score is 73.1250.
Use of aggregation function under given search conditions
Aggregate functions do not necessarily count all records in a table. We can also specify search criteria to limit the scope of these aggregate functions. For example, we just want to count the average score of the course 'postpartum care of sows', which can be written as follows:
mysql> SELECT AVG(score) FROM student_score WHERE subject = 'Postpartum care of sows'; +------------+ | AVG(score) | +------------+ | 73.0000 | +------------+ 1 row in set (0.00 sec) mysql>
In other words, those records that are not in the search criteria do not participate in statistics.
Use of DISTINCT in aggregate functions
By default, the aggregation function described above will calculate all non NULL data of the specified column. If there are duplicate data in the specified column, you can choose to use DISTINCT to filter out these duplicate data. For example, we want to check student_ The student information of how many majors are stored in the info table can be written as follows:
mysql> SELECT COUNT(DISTINCT major) FROM student_info; +-----------------------+ | COUNT(DISTINCT major) | +-----------------------+ | 4 | +-----------------------+ 1 row in set (0.01 sec) mysql>
You can see that there are four majors in total.
Combined aggregate function
These aggregation functions can also be used in one query, for example:
mysql> SELECT COUNT(*) AS Total score records, MAX(score) AS Highest score, MIN(score) AS Minimum score, AVG(score) AS Average score FROM student_score; +--------------------+--------------+--------------+--------------+ | Total score records | Highest score | Minimum score | Average score | +--------------------+--------------+--------------+--------------+ | 8 | 100 | 46 | 73.1250 | +--------------------+--------------+--------------+--------------+ 1 row in set (0.00 sec) mysql>
Implicit type conversion
Scenario of implicit type conversion
As long as the type of a value does not match the type required by the context, MySQL will type convert the value according to the type required in the context. Since these type conversions are completed automatically by mysql, they can also be called implicit type conversion. We list several common scenarios of implicit type conversion:
-
Converts the operand type to the appropriate type for operator evaluation.
For example, the addition operator + requires that both operands must be numbers for calculation. Therefore, if an operand is not a number, it will be implicitly converted to a number. For example, the following examples:
1 + 2 → 3 '1' + 2 → 3 '1' + '2' → 3
Although '1' and '2' are strings, if they are used as operands of the addition operator +, they will be forcibly converted to numbers. Therefore, the above expressions will actually be treated as 1 + 2. The effects of these expressions when placed in the query list are as follows:
mysql> SELECT 1 + 2, '1' + 2, '1' + '2'; +-------+---------+-----------+ | 1 + 2 | '1' + 2 | '1' + '2' | +-------+---------+-----------+ | 3 | 3 | 3 | +-------+---------+-----------+ 1 row in set (0.00 sec) mysql>
-
Converts a function parameter to the type expected by the function.
Let's take the CONCAT function used to splice strings as an example. This function takes the values of string types as parameters. If we pass in other types of values as parameters when calling this function, MySQL will automatically convert the types of these values to string types:
CONCAT('1', '2') → '12' CONCAT('1', 2) → '12' CONCAT(1, 2) → '12'Although 1 and 2 are numbers, if they are used as parameters of the CONCAT function, they will be converted into strings. Therefore, the above expressions will be processed as CONCAT ('1 ',' 2). When these expressions are placed in the query list, the effects are as follows:
mysql> SELECT CONCAT('1', '2'), CONCAT('1', 2), CONCAT(1, 2); +------------------+----------------+--------------+ | CONCAT('1', '2') | CONCAT('1', 2) | CONCAT(1, 2) | +------------------+----------------+--------------+ | 12 | 12 | 12 | +------------------+----------------+--------------+ 1 row in set (0.00 sec) mysql> -
When storing data, convert a value to the type required by a column.
Let's create a simple table t:
CREATE TABLE t ( i1 TINYINT, i2 TINYINT, s VARCHAR(100) );This table has three columns. Columns i1 and i2 are used to store integers, and column s is used to store strings. If we don't fill in the expected type when storing data, like this:
mysql> INSERT INTO t(i1, i2, s) VALUES('100', '100', 200); Query OK, 1 row affected (0.01 sec) mysql>The value we fill in for columns i1 and i2 is a string value: '100', and the value we fill in for column s is an integer value: 200. Although the type is wrong, due to the existence of implicit type conversion, when inserting data, the string '100' will be transformed into the integer 100, and the integer 200 will be transformed into the string '200', so the insertion is successful. Let's see the effect:
mysql> SELECT * FROM t; +------+------+------+ | i1 | i2 | s | +------+------+------+ | 100 | 100 | 200 | +------+------+------+ 1 row in set (0.00 sec) mysql>
Considerations for type conversion
-
MySQL will try to convert the value to the type required in the expression instead of generating errors.
Normally, '23sfd' cannot be converted to a number, but MySQL stipulates that as long as the beginning of the string contains a number, the string will be converted to the beginning number. If the beginning does not contain a number, it will be converted to 0, for example:
'23sfd' → 23 '2019-08-28' → 2019 '11:30:32' → 11 'sfd' → 0
Take an example:
mysql> SELECT '23sfd' + 0, 'sfd' + 0; +-------------+-----------+ | '23sfd' + 0 | 'sfd' + 0 | +-------------+-----------+ | 23 | 0 | +-------------+-----------+ 1 row in set, 2 warnings (0.00 sec) mysql>
However, it should be noted that this cast cannot be used to store data, for example:
mysql> INSERT INTO t(i1, i2, s) VALUES('sfd', 'sfd', 'aaa'); ERROR 1366 (HY000): Incorrect integer value: 'sfd' for column 'i1' at row 1 mysql>Because columns i1 and i2 need integers, and the filled string 'sfd' cannot be successfully converted to integers, an error is reported.
-
The type of the operand is automatically promoted during the operation.
We know that the range of values that can be represented by different data types is different, and the results obtained after arithmetic calculation for small data types may be greater than the range that can be represented. For example, there is a record in table t as follows:
mysql> SELECT * FROM t; +------+------+------+ | i1 | i2 | s | +------+------+------+ | 100 | 100 | 200 | +------+------+------+ 1 row in set (0.00 sec) mysql>
The types of i1 and i2 columns are TINYINT, and the maximum positive integer that TINYINT can represent is 127. What happens if we add the values of i1 and i2 columns? See:
mysql> SELECT i1 + i2 FROM t; +---------+ | i1 + i2 | +---------+ | 200 | +---------+ 1 row in set (0.00 sec) mysql>
You can see that the final result is 200, but it is beyond the representation range of TINYINT type. In fact, in the process of operation, MySQL automatically promotes the operand of integer type to BIGINT, so as not to cause the embarrassment that the operation result is too large and exceeds the numerical range represented by TINYINT. Similarly, an operation with a floating-point number automatically converts the operand to DOUBLE.
Tip: there are implicit type conversions as well as explicit type conversions. In MySQL, you can use the CAST function to complete explicit type conversion, that is, we explicitly specify to convert a specific value to a specific type, but we do not intend to use this function. Interested students can go to the document to see ha (we will not talk in detail, but naturally this knowledge point is not so important for beginners).