I. actual purpose

Use all kinds of functions to operate data, and master the functions and use methods of all kinds of functions.

II. Actual operation process

1 use the math function RAND() to generate 3 random integers within 10.

The random number generated by the RAND() function is between 0 and 1. To generate a random number between 0 and 10, RAND() needs to be multiplied by 10. If it is required to be an integer, the decimal part of the result must also be removed. Here, the run() function is used. The execution process is as follows:

mysql> SELECT ROUND(RAND() * 10),  ROUND(RAND() * 10), ROUND(RAND() * 10);
+--------------------+--------------------+--------------------+
| ROUND(RAND() * 10) | ROUND(RAND() * 10) | ROUND(RAND() * 10) |
+--------------------+--------------------+--------------------+
|                  6 |                  4 |                 10 |
+--------------------+--------------------+--------------------+
1 row in set (0.01 sec)

2 use SIN(), COS(), TAN(), COT() function to calculate trigonometric function value and convert the calculation result to integer value.

The value calculated by the trigonometric function in MySQL is not necessarily an integer value. It needs to be converted to an integer by using mathematical functions, such as ROUND(), FLOOR(), etc. the execution process is as follows:

mysql> SELECT PI(), sin(PI()/2),cos(PI()), ROUND(tan(PI()/4)), FLOOR(cot(PI()/4));
+----------+-------------+-----------+--------------------+--------------------+
| PI()     | sin(PI()/2) | cos(PI()) | ROUND(tan(PI()/4)) | FLOOR(cot(PI()/4)) |
+----------+-------------+-----------+--------------------+--------------------+
| 3.141593 |           1 |        -1 |                  1 |                  1 |
+----------+-------------+-----------+--------------------+--------------------+
1 row in set (0.01 sec)

3 create a table and use string and date functions to manipulate field values.

(1) Create the table member, which contains three fields, namely, m ID field of auto increment constraint, m FN field of VARCHAR type, m ln field of VARCHAR type, m birth field of DATETIME type and M info field of VARCHAR type.

(2) Insert a record. The m_id value is the default, the m_FN value is "Halen", the m_LN value is "Park", the m_birth value is 1970-06-29, and the m_info value is "GoodMan".

(3) Returns the length of m_FN, the full name of the person in the first record, and converts the value of m_info field to lowercase letters. Reverse the value of M ﹣ info.

(4) Calculate the age of the person in the first record, and calculate the position of the value in the m ﹤ birth field in that year, and output the time value in the format of "Saturday October 4th 1997".

(5) Insert a new record. The m ﹣ FN value is "Samuel", the m ﹣ ln value is "Green", the m ﹣ birth value is the current time of the system, and the m ﹣ info value is null. Use last? Insert? Id() to view the last inserted ID value.

The operation process is as follows:

(1) Create the table member. The input statement is as follows:

CREATE TABLE member
     (
     m_id    INT AUTO_INCREMENT PRIMARY KEY,
     m_FN   VARCHAR(100),
     m_LN   VARCHAR(100),
     m_birth  DATETIME,
     m_info   VARCHAR(255) NULL
     );
Query OK, 0 rows affected (0.01 sec)

(2) Insert a record. The input statement is as follows:

mysql> INSERT INTO member VALUES (NULL, 'Halen ', 'Park', '1970-06-29', 'GoodMan ');
Query OK, 1 row affected (0.04 sec)

Use the SELECT statement to view the insert results,

mysql> SELECT * FROM member;
+------+--------+------+---------------------+----------+
| m_id | m_FN   | m_LN | m_birth             | m_info   |
+------+--------+------+---------------------+----------+
|    1 | Halen  | Park | 1970-06-29 00:00:00 | GoodMan  |
+------+--------+------+---------------------+----------+
1 row in set (0.00 sec)

(3) Return the length of M ﹣ FN, return the full name of the person in the first record, convert the value of M ﹣ info field to lowercase letters, and output the value of M ﹣ info in reverse.

mysql> SELECT LENGTH(m_FN), CONCAT(m_FN, m_LN),LOWER(m_info), REVERSE(m_info) FROM member;
+--------------+--------------------+---------------+-----------------+
| LENGTH(m_FN) | CONCAT(m_FN, m_LN) | LOWER(m_info) | REVERSE(m_info) |
+--------------+--------------------+---------------+-----------------+
|            6 | Halen Park         | goodman       |  naMdooG        |
+--------------+--------------------+---------------+-----------------+
1 row in set (0.00 sec)

(4) Calculate the age of the person in the first record, and calculate the position of the value in the m ﹤ birth field in that year, and output the time value in the format of "Saturday October 4th 1997".

SELECT YEAR(CURDATE())-YEAR(m_birth) AS age, DAYOFYEAR(m_birth) AS days, DATE_FORMAT(m_birth, '%W %D %M %Y') AS birthDate FROM member;

The statement execution results are as follows:

mysql> SELECT YEAR(CURDATE())-YEAR(m_birth) AS age, DAYOFYEAR(m_birth) AS days, DATE_FORMAT(m_birth, '%W %D %M %Y') AS birthDate FROM member;
+------+------+-----------------------+
| age  | days | birthDate             |
+------+------+-----------------------+
|   50 |  180 | Monday 29th June 1970 |
+------+------+-----------------------+
1 row in set (0.01 sec)

(5) Insert a new record. The m ﹣ FN value is "Samuel", the m ﹣ ln value is "Green", the m ﹣ birth value is the current time of the system, and the m ﹣ info value is null. Use last? Insert? Id() to view the last inserted ID value.

mysql> INSERT INTO member VALUES (NULL, 'Samuel', 'Green', NOW(),NULL);
Query OK, 1 row affected (0.03 sec)

Use the SELECT statement to view the insert results:

mysql> SELECT * FROM member;
+------+--------+-------+---------------------+----------+
| m_id | m_FN   | m_LN  | m_birth             | m_info   |
+------+--------+-------+---------------------+----------+
|    1 | Halen  | Park  | 1970-06-29 00:00:00 | GoodMan  |
|    2 | Samuel | Green | 2020-01-12 18:32:25 | NULL     |
+------+--------+-------+---------------------+----------+
2 rows in set (0.00 sec)

You can see that there are two records in the table. Next, use the last insert id() function to view the last inserted ID value. The input statement is as follows:

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

The last inserted record is the second record with an ID value of 2, so the return value is 2.

Use CASE to judge the conditions. If the m ﹤ birth is less than 2000, it will display "old". If the m ﹤ birth is greater than 2000, it will display "young". The input statement is as follows:

SELECT m_birth, CASE WHEN YEAR(m_birth) < 2000  THEN  'old'
     WHEN YEAR(m_birth) > 2000 THEN  'young'
     ELSE 'not born' END AS status FROM member;
+---------------------+--------+
| m_birth             | status |
+---------------------+--------+
| 1970-06-29 00:00:00 | old    |
| 2020-01-12 18:32:25 | young  |
+---------------------+--------+
2 rows in set (0.00 sec)