您好,登錄后才能下訂單哦!
小編給大家分享一下MySQL函數(shù)的使用示例,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!
案例:使用各種函數(shù)操作數(shù)據(jù),掌握各種函數(shù)的作用和使用方法。
(1)使用數(shù)學(xué)函數(shù)rand()生成3個(gè)10以內(nèi)的隨機(jī)整數(shù)。
(2)使用sin(),con(),tan(),cot()函數(shù)計(jì)算三角函數(shù)值,并將計(jì)算結(jié)果轉(zhuǎn)換成整數(shù)值。
(3)創(chuàng)建表,并使用字符串和日期函數(shù)對(duì)字段值進(jìn)行操作。
(4)使用case進(jìn)行條件判斷,如果m_birth
小于2000年,就顯示"old";如果m_birth大于2000年,則顯示"young"。
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)
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)
①創(chuàng)建表member,其中包含5個(gè)字段,分別為auto_increment約束的m_id字段、varchar類型的m_FN字段、varchar類型的m_LN字段、datetime類型m_birth字段和varchar類型的m_info字段。
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)
②插入一條記錄,m_id值為默認(rèn),m_FN值為"Halen",m_LN值為"Park",m_birth值為1970-06-29,m-info值為"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)
③返回m_FN的長(zhǎng)度,返回第一條記錄中人的全名,將m_info字段值轉(zhuǎn)換成小寫(xiě)字母。將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)
④計(jì)算第1條記錄中人的年齡,并計(jì)算m_birth字段中的值在那一年中的位置,按照"Saturday 4th October 1997"格式輸出時(shí)間值。
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)
⑤插入一條新的記錄,m_FN值為"Samuel",m_LN值為"Greem",m_birth值為系統(tǒng)當(dāng)前時(shí)間,m_info為空。使用last_insert_id查看最后插入的id值。
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)
可以看到表中有兩條記錄,接下來(lái)使用last_insert_id()函數(shù)查看最后插入的id值,SQL語(yǔ)句如下:
mysql> select last_insert_id();+------------------+| last_insert_id() |+------------------+| 3 |+------------------+1 row in set (0.00 sec)
mysql> 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 || 2019-08-20 12:43:23 | young |+---------------------+--------+3 rows in set (0.00 sec)
以上是“MySQL函數(shù)的使用示例”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對(duì)大家有所幫助,如果還想學(xué)習(xí)更多知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。