MySQL Exercise 3: Use of MySQL functions

Case: Use various functions to manipulate data and master the functions and usage of various functions.
(1) Use the mathematical function rand() to generate 3 random integers within 10.
(2) Use sin(), con(), tan(), cot() functions to calculate trigonometric function values, and convert the calculation results into integer values.
(3) Create a table and use string and date functions to operate on field values.
(4) Use case for conditional judgment. If m_birth is less than 2000, it will display "old"; if m_birth is greater than 2000, it will display "young".

---

(Free learning recommendation: mysql video tutorial)

(1 ), use the mathematical function rand() to generate three random integers within 10.

mysql> select round( rand() * 10 ),round( rand() * 10 ), round( rand() * 10 );+----------------------+----------------------+----------------------+| round( rand() * 10 ) | round( rand() * 10 ) | round( rand() * 10 ) |+----------------------+----------------------+----------------------+|                    9 |                    8 |                    1 |+----------------------+----------------------+----------------------+1 row in set (0.00 sec)

(2). Use sin(), con(), tan(), cot() functions to calculate trigonometric function values, and convert the calculation results into integer values.

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.00 sec)

(3), create a table, and use string and date functions to operate on field values.

① Create table member, which contains 5 fields, namely the m_id field of auto_increment constraint, the m_FN field of varchar type, the m_LN field of varchar type, the datetime type m_birth field and the m_info field of varchar type.

mysql> 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.21 sec)

②Insert a record, the m_id value is 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".

mysql> insert into member values
    -> ( null,'Halen','Park','1970-06-29','GoodMan');Query OK, 1 row affected (0.01 sec)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)

③Return the length of m_FN, return the full name of the person in the first record, and convert the m_info field value into lowercase letters. 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) |+--------------+-------------------+---------------+-----------------+|            5 | HalenPark         | goodman       | naMdooG         |+--------------+-------------------+---------------+-----------------+1 row in set (0.00 sec)

④ Calculate the age of the person in the first record, calculate the position of the value in the m_birth field in that year, and output the time value in the format of "Saturday 4th October 1997".

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             |+------+------+-----------------------+|   49 |  180 | Monday 29th June 1970 |+------+------+-----------------------+1 row in set (0.00 sec)

⑤Insert a new record, the m_FN value is "Samuel", the m_LN value is "Greem", the m_birth value is the current system time, and m_info is empty. 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.04 sec)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 | 2019-08-20 12:43:23 | NULL    |+------+--------+-------+---------------------+---------+3 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 SQL statement is as follows:

mysql> select last_insert_id();+------------------+| last_insert_id() |+------------------+|                3 |+------------------+1 row in set (0.00 sec)

(4), use case for conditions Judge, if m_birth is less than 2000, it will display "old"; if m_birth is greater than 2000, it will display "young".

mysql> select m_birth,case when year(m_birth)<2000 then &#39;old&#39;
    -> 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    || 2019-08-20 12:43:23 | young  |+---------------------+--------+3 rows in set (0.00 sec)

Related free learning recommendations: mysql database(video)

The above is the detailed content of MySQL Exercise 3: Use of MySQL functions. For more information, please follow other related articles on the PHP Chinese website!