【Mysql 學習】算術運算及字串，數值函式
邏輯運算
not 表示 非邏輯！not null 返回 的仍是null
mysql> select not 1,not 0, not null;
+-------+-------+----------+
| not 1 | not 0 | not null |
+-------+-------+----------+
| 0 | 1 | NULL |
+-------+-------+----------+
1 row in set (0.00 sec)
and 與 邏輯 任何 與null的邏輯運算的結果都是 null！
mysql> select (1 and 0) ,( 0 and 1 ) ,( 2 and 2 ) ,( 1 and null );
+-----------+-------------+-------------+----------------+
| (1 and 0) | ( 0 and 1 ) | ( 2 and 2 ) | ( 1 and null ) |
+-----------+-------------+-------------+----------------+
| 0 | 0 | 1 | NULL |
+-----------+-------------+-------------+----------------+
1 row in set (0.00 sec)
mysql> select (1 and 0) ,( 0 and 1) ,(2 and 2), (1 and null) ,(null and null);
+-----------+------------+-----------+--------------+-----------------+
| (1 and 0) | ( 0 and 1) | (2 and 2) | (1 and null) | (null and null) |
+-----------+------------+-----------+--------------+-----------------+
| 0 | 0 | 1 | NULL | NULL |
+-----------+------------+-----------+--------------+-----------------+
1 row in set (0.00 sec)
or 邏輯或 運算
mysql> select (1 or 0) ,( 0 or 1) ,(2 or 2), (1 or null) ,(null or null);
+----------+-----------+----------+--------------+----------------+
| (1 or 0) | ( 0 or 1) | (2 or 2) | (1 or null) | (null or null) |
+----------+-----------+----------+--------------+----------------+
| 1 | 1 | 1 | 1 | NULL |
+----------+-----------+----------+--------------+----------------+
1 row in set (0.01 sec)
xor 異或運算。
mysql> select 1 xor 1 ,0 xor 1 ,0 xor 0 , 1 xor null,0 xor null;
+---------+---------+----------+------------+------------+
| 1 xor 1 | 0 xor 1 | 0 xor 0 | 1 xor null | 0 xor null |
+---------+---------+----------+------------+------------+
| 0 | 1 | 0 | NULL | NULL |
+---------+---------+----------+------------+------------+
1 row in set (0.01 sec)
mysql> select 2&5; 0010&0101
+-----+
| 2&5 |
+-----+
| 0 |
+-----+
1 row in set (0.01 sec)
mysql> select 5&5;
+-----+
| 5&5 |
+-----+
| 5 |
+-----+
1 row in set (0.01 sec)
mysql> select 2|3; 或 運算或運算對運算元的二進位制做或運算
+-----+
| 2|3 |
+-----+
| 3 |
+-----+
1 row in set (0.01 sec)
或運算 對運算元的二進位制做與運算
mysql> select 2&3&4; 0010&0011&0100
+-------+
| 2&3&4 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
mysql> select 2^3; 位異或運算 對運算元的二進位制做異或
+-----+
| 2^3 |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
mysql> select ~1; 位 取反！
+----------------------+
| ~1 |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.00 sec)
mysql> select 1000 >>3; -右移位操作！除以2的3次方
+----------+
| 1000 >>3 |
+----------+
| 125 |
+----------+
1 row in set (0.01 sec)
mysql> select 1000 <<3;左移位操作！乘以2的3次方
+----------+
| 1000 <<3 |
+----------+
| 8000 |
+----------+
1 row in set (0.00 sec)
--常用函式。
在預設狀態下, 在函式和緊隨其後的括號之間不得存在空格。這能幫助 MySQL 分析程式區分一些同函式名相同的函式呼叫以及表或列。不過，函式自變數周圍允許有空格出現。
--當前使用的資料庫
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
--當前的資料庫版本
mysql> select version();
+----------------+
| version() |
+----------------+
| 5.1.7-beta-log |
+----------------+
1 row in set (0.02 sec)
-當前的登入使用者
mysql> select user();
+----------------+
| user() |
+----------------+
| |
+----------------+
1 row in set (0.01 sec)
--返回主機ip地址的數字
mysql> select inet_aton('192.168.12.128');
+-----------------------------+
| inet_aton('192.168.12.128') |
+-----------------------------+
| 3232238720 |
+-----------------------------+
1 row in set (0.00 sec)
--返回數字代表的ip
mysql> select inet_ntoa(3232238720);
+-----------------------+
| inet_ntoa(3232238720) |
+-----------------------+
| 192.168.12.128 |
+-----------------------+
1 row in set (0.00 sec)
---返回字串密碼的加密版
mysql> select password('12356');
+-------------------------------------------+
| password('12356') |
+-------------------------------------------+
| *DC594838253636AA6E73A5366878F6F0502BDC5D |
+-------------------------------------------+
1 row in set (0.00 sec)
--字串函式：
字串指用單引號(‘'’)或雙引號(‘"’)引起來的字元序列。例如：
'a string'
"another string"
如果SQL伺服器模式啟用了NSI_QUOTES，可以只用單引號引用字串。用雙引號引用的字串被解釋為一個識別符。
--連線字元
mysql> select concat('aa','bb','yangqilong'),concat('yangql' ,null);
+--------------------------------+------------------------+
| concat('aa','bb','yangqilong') | concat('yangql' ,null) |
+--------------------------------+------------------------+
| aabbyangqilong | NULL |
+--------------------------------+------------------------+
1 row in set (0.00 sec)
---insert(str,x，y ，insrt)將字串從第x位置開始，y個字元替換為instr
mysql> select insert ('beijing2008iloveyou',12,3,'me');
+------------------------------------------+
| insert ('beijing2008iloveyou',12,3,'me') |
+------------------------------------------+
| beijing2008meveyou |
+------------------------------------------+
1 row in set (0.00 sec)
--轉換大小寫
mysql> select lower('YANGQILONG') ,upper('yangqilong');
+---------------------+---------------------+
| lower('YANGQILONG') | upper('yangqilong') |
+---------------------+---------------------+
| yangqilong | YANGQILONG |
+---------------------+---------------------+
1 row in set (0.00 sec)
--left(str,x)返回字串最左邊的x個字元
--right(str,x)返回字串最右邊的x個字元
mysql> select left('yangqlloveMysql',6), left ('yangqlloveMysql',null),right('yangqlloveMysql',9);
+---------------------------+-------------------------------+----------------------------+
| left('yangqlloveMysql',6) | left ('yangqlloveMysql',null) | right('yangqlloveMysql',9) |
+---------------------------+-------------------------------+----------------------------+
| yangql | | loveMysql |
+---------------------------+-------------------------------+----------------------------+
1 row in set (0.00 sec)
--字元填充函式 lpad('yangql',11,'mysql')
mysql> select lpad('yangql',11,'mysql') ,rpad('mysql', 11,'yangql');
+---------------------------+----------------------------+
| lpad('yangql',11,'mysql') | rpad('mysql', 11,'yangql') |
+---------------------------+----------------------------+
| mysqlyangql | mysqlyangql |
+---------------------------+----------------------------+
1 row in set (0.00 sec)
--去掉str 左邊的空格！
mysql> select ltrim(' |yangqlmysql') ,rtrim('yangqlmysql| ');
+------------------------+--------------------------+
| ltrim(' |yangqlmysql') | rtrim('yangqlmysql| ') |
+------------------------+--------------------------+
| |yangqlmysql | yangqlmysql| |
+------------------------+--------------------------+
1 row in set (0.00 sec)
--repeat(str,N)重複 str N 次！
mysql> select repeat('yangql ',2);
+---------------------+
| repeat('yangql ',2) |
+---------------------+
| yangql yangql |
+---------------------+
1 row in set (0.00 sec)
--比較字串的大小
mysql> select strcmp('a','b') ,strcmp('a','a'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('a','a') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
| -1 | 0 | 1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
--substring(STR,X,Y)返回從字串str x 位置起y個字元長度的字串！
mysql> select substring('yangql mysql',1,6) ,substring('yangql mysql',7,12);
+-------------------------------+--------------------------------+
| substring('yangql mysql',1,6) | substring('yangql mysql',7,12) |
+-------------------------------+--------------------------------+
| yangql | mysql |
+-------------------------------+--------------------------------+
1 row in set (0.00 sec)
--去掉 str 兩邊的空格，如果中間有空格，則不去掉！
mysql> select trim(' | yangql mysql |');
+---------------------------+
| trim(' | yangql mysql |') |
+---------------------------+
| | yangql mysql | |
+---------------------------+
1 row in set (0.00 sec)
--數值函式
--ABS(X) 返回x的絕對值。
mysql> select abs(-0.5),abs(0.5);
+-----------+----------+
| abs(-0.5) | abs(0.5) |
+-----------+----------+
| 0.5 | 0.5 |
+-----------+----------+
1 row in set (0.01 sec)
--CEIL(X) :返回大於x的最小整數。
mysql> select ceil(-0.5),ceil(0.5);
+------------+-----------+
| ceil(-0.5) | ceil(0.5) |
+------------+-----------+
| 0 | 1 |
+------------+-----------+
1 row in set (0.00 sec)
--FLOOR(X):返回小於X的最大整數，和CEIL 相反！
mysql> select floor(-0.5),floor(0.5);
+-------------+------------+
| floor(-0.5) | floor(0.5) |
+-------------+------------+
| -1 | 0 |
+-------------+------------+
1 row in set (0.00 sec)
--隨機值函式：返回0--1 之間的數值，不重複！
mysql> select rand(),rand();
+------------------+-------------------+
| rand() | rand() |
+------------------+-------------------+
| 0.65191487563021 | 0.031441814295428 |
+------------------+-------------------+
1 row in set (0.00 sec)
--利用ceil 和rand 可以產生指定範圍內的隨機數
mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
| 21 | 92 |
+------------------+------------------+
1 row in set (0.00 sec)
--ROUND(X,Y):返回x的四捨五入的有y位 小數的值！
mysql> select round(1.25),round(1.25,1),round(1.25,2);
+-------------+---------------+---------------+
| round(1.25) | round(1.25,1) | round(1.25,2) |
+-------------+---------------+---------------+
| 1 | 1.3 | 1.25 |
+-------------+---------------+---------------+
1 row in set (0.00 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-683137/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Mysql學習】算術運算及字串，數值函式MySql字串函式
- Oracle 函式大全(字串函式，數學函式，日期函式，邏輯運算函式，其他函式)Oracle函式字串
- Python基礎學習篇-2-數值運算和字串Python字串
- mysql注入方法邏輯運算及常用函式MySql函式
- MySQL函式學習(一)-----字串函式MySql函式字串
- MYSQL學習筆記15: 數值函式MySql筆記函式
- OpenCV計算機視覺學習（2）——影像算術運算 & 掩膜mask操作（數值計算，影像融合，邊界填充）OpenCV計算機視覺
- Python學習-算術運算子,賦值運算子和複合運算子Python賦值
- 利用字串實現高精度數值運算（四）字串
- 利用字串實現高精度數值運算（三）字串
- 利用字串實現高精度數值運算（二）字串
- 利用字串實現高精度數值運算（一）字串
- SHELL之數值運算
- javascript 冪函式運算JavaScript函式
- Sass學習筆記–初步瞭解函式、運算、條件判斷及迴圈筆記函式
- Python3 基礎學習之數值簡單運算Python
- 計算機系統002 – 數值運算計算機
- 計算機系統002 - 數值運算計算機
- 【LeetCode 38_字串_算術運算】Count and SayLeetCode字串
- 學習位運算
- MKL稀疏矩陣運算示例及函式封裝矩陣函式封裝
- MySQL函式大全(字串函式，數學函式，日期函式，系統級函式，聚合函式)MySql函式字串
- python-算數運算子對應的函式Python函式
- （位運算）兩個字串的位運算字串
- Mysql中日期計算函式MySql函式
- NumPy常用的位運算函式函式
- 8 連續函式的運算函式
- numpy學習回顧-數學函式及邏輯函式函式
- 字串函式學習三字串函式
- 字串函式學習一字串函式
- 字串函式學習二字串函式
- 【LeetCode 67_字串_算術運算】Add BinaryLeetCode字串
- 探索MySQL高階語句（數學函式、聚合函式、字串函式、日期時間函式）MySql函式字串
- [轉載] python數學計算模組之math常用函式學習使用Python函式
- VB學習要點1----日期型資料作算術運算
- VB學習要點4----日期型資料作算術運算
- 【Mysql 學習】日期函式函式MySql函式
- 物聯網學習教程—函式引數和函式的值函式