您好,登錄后才能下訂單哦!
這篇文章將為大家詳細(xì)講解有關(guān)mysql中運(yùn)算符的使用示例,小編覺(jué)得挺實(shí)用的,因此分享給大家做個(gè)參考,希望大家閱讀完這篇文章后可以有所收獲。
案例:創(chuàng)建數(shù)據(jù)表tmp15,其中包含varchar類型的字段note和int類型的字段price。
使用運(yùn)算符對(duì)表tmp15中不同的字段進(jìn)行運(yùn)算。
使用邏輯操作符對(duì)數(shù)據(jù)進(jìn)行邏輯操作。
使用位操作符對(duì)數(shù)據(jù)進(jìn)行位操作。
首先創(chuàng)建tmp15表,插入一條記錄,note值為"Thisisgood",price值為50,SQL語(yǔ)句如下:
mysql> create table tmp15 -> ( -> note varchar(100), -> price int -> );Query OK, 0 rows affected (0.13 sec)mysql> into tmp15 values -> ( -> "Thisisgood",50 -> ); mysql> insert into tmp15 values -> ("Thisisgood",50);Query OK, 1 row affected (0.06 sec)
(1)對(duì)表tmp15中的整型數(shù)值字段price進(jìn)行算數(shù)運(yùn)算,SQL語(yǔ)句如下:
mysql> select price, -> price + 10, -> price - 10, -> price * 2, -> price / 2, -> price % 3 -> from tmp15;+-------+------------+------------+-----------+-----------+-----------+| price | price + 10 | price - 10 | price * 2 | price / 2 | price % 3 |+-------+------------+------------+-----------+-----------+-----------+| 50 | 60 | 40 | 100 | 25.0000 | 2 |+-------+------------+------------+-----------+-----------+-----------+1 row in set (0.00 sec)
(2)對(duì)表tmp15中的整型數(shù)值字段price進(jìn)行比較運(yùn)算,SQL語(yǔ)句如下:
mysql> select price, -> price>10, -> price<10, -> price != 10, -> price = 10, -> price<=>10, -> price<>10 -> from tmp15;+-------+----------+----------+-------------+------------+------------+-----------+| price | price>10 | price<10 | price != 10 | price = 10 | price<=>10 | price<>10 |+-------+----------+----------+-------------+------------+------------+-----------+| 50 | 1 | 0 | 1 | 0 | 0 | 1 |+-------+----------+----------+-------------+------------+------------+-----------+1 row in set (0.00 sec)
(3)判斷price值是否落在30—80區(qū)間、返回70、30相比最大的值、判斷price是否為in列表(10、20、50、35)中的某個(gè)值,SQL語(yǔ)句如下:
mysql> select price, -> price between 30 and 80, -> greatest(price,70,30), -> price in(10,20,50,35) -> from tmp15;+-------+-------------------------+-----------------------+-----------------------+| price | price between 30 and 80 | greatest(price,70,30) | price in(10,20,50,35) |+-------+-------------------------+-----------------------+-----------------------+| 50 | 1 | 70 | 1 |+-------+-------------------------+-----------------------+-----------------------+1 row in set (0.00 sec)
(4)對(duì)tmp15中的字符串?dāng)?shù)值字段note進(jìn)行比較運(yùn)算,判斷表tmp15中note字段是否為空、使用LIKE判斷是否以字母"t"開(kāi)頭、使用regexp判斷是否以字母“y”結(jié)尾、判斷是否包含字母“g”或者“m”,SQL語(yǔ)句如下:
mysql> select note, -> note is null, -> note like 't%', -> note regexp '$y', -> note regexp '[gm]' -> from tmp15;+------------+--------------+----------------+------------------+--------------------+| note | note is null | note like 't%' | note regexp '$y' | note regexp '[gm]' |+------------+--------------+----------------+------------------+--------------------+| Thisisgood | 0 | 1 | 0 | 1 |+------------+--------------+----------------+------------------+--------------------+1 row in set (0.05 sec)
(5)將price字段值與null、0進(jìn)行邏輯運(yùn)算,SQL語(yǔ)句如下:
mysql> select price, -> price && 1, -> price && null, -> price || 0, -> price and 0, -> 0 and null, -> price or null -> from tmp15;+-------+------------+---------------+------------+-------------+------------+---------------+| price | price && 1 | price && null | price || 0 | price and 0 | 0 and null | price or null |+-------+------------+---------------+------------+-------------+------------+---------------+| 50 | 1 | NULL | 1 | 0 | 0 | 1 |+-------+------------+---------------+------------+-------------+------------+---------------+1 row in set (0.00 sec)mysql> select price, -> !price, -> not null, -> price xor 3, -> 0 xor null, -> price xor 0 -> from tmp15;+-------+--------+----------+-------------+------------+-------------+| price | !price | not null | price xor 3 | 0 xor null | price xor 0 |+-------+--------+----------+-------------+------------+-------------+| 50 | 0 | NULL | 0 | NULL | 1 |+-------+--------+----------+-------------+------------+-------------+1 row in set (0.00 sec)
(6)將price字段值與2、4進(jìn)行按位與、按位或 操作,并對(duì)price進(jìn)行按位操作,SQL語(yǔ)句如下:
mysql> select price, -> price & 2, -> price | 4, -> ~price from tmp15;+-------+-----------+-----------+----------------------+| price | price & 2 | price | 4 | ~price |+-------+-----------+-----------+----------------------+| 50 | 2 | 54 | 18446744073709551565 |+-------+-----------+-----------+----------------------+1 row in set (0.00 sec)
(7)將price字段值分別額左移和右移兩位,SQL語(yǔ)句如下:
mysql> select price, -> price<<2, -> price>>2 -> from tmp15;+-------+----------+----------+| price | price<<2 | price>>2 |+-------+----------+----------+| 50 | 200 | 12 |+-------+----------+----------+1 row in set (0.00 sec)
關(guān)于“mysql中運(yùn)算符的使用示例”這篇文章就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,使各位可以學(xué)到更多知識(shí),如果覺(jué)得文章不錯(cuò),請(qǐng)把它分享出去讓更多的人看到。
免責(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)容。