Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you the use of MySQL function examples, I believe most people do not know how, so share this article for everyone's reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

Case: Use various functions to operate data, master the role and use of various functions.

(1)Use the mathematical function rand() to generate 3 random integers up to 10.

(2)Calculates trigonometric values using sin(),con(),tan(),cot() functions and converts the results to integer values.

(3)Create tables and manipulate field values using string and date functions.

(4)Use case to make conditional judgment. If m_birth is less than 2000,"old" is displayed; if m_birth is greater than 2000,"young" is displayed.

(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) Calculate trigonometric values using sin(),con(),tan(),cot() functions and convert the results to 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 manipulate field values using string and date functions.

① Create table member, which contains 5 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.

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, m_id value is default,m_FN value is "Halen", m_LN value is "Park", m_birth value is 1970-06-29,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 to lowercase letters. Reverse the value of m_info.

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, and calculate the position of the value in the m_birth field in that year, and output the time value according to 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, m_FN value is "Samuel", m_LN value is "Greek", m_birth value is the current time of the system, m_info is empty. Use last_insert_id to see the id value of the last insert.

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 check 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 to make conditional judgment. If m_birth is less than 2000,"old" is displayed; if m_birth is greater than 2000,"young" is displayed. mysql> select m_birth,case when year(m_birth) 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) Above is "MySQL function use example" All the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to 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.