MySQL學習筆記（四）運算符和函數(shù)

一、知識回顧：
連接
1、SELECT語句
SELECT * FROM tabA JOIN tabB ON tabA.name = tabB.name;//表示返回都含有的name值對應的字段

2、多表更新
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name SET goods_cate=cate_id;//將 
tdb_goods 表和 tdb_goods_cates 表通過【連接條件】 goods_cate=cate_name 連接，然后【更新值】 goods_cate=cate_id

多表更新之一步到位
1.建表、查詢、寫入三合一：
CREATE TABLE tdb_goods_brands (
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
) SELECT brand_name FROM tdb_goods GROUP BY brand_name;

2.多表更新：
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;

3.通過ALTER TABLE語句修改數(shù)據(jù)表結構
ALTER TABLE tdb_goods 
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

3、多表刪除
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY 
goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;
二、字符函數(shù)
字符函數(shù)<br>
1.CONCAT() ,字符連接<br>
SELECT CONCAT('a','-','b');  結果為:a-b<br>
2.CONCAT_WS(), 使用指定的分隔符進行字符連接<br>
SELECT CONCAT_WS('|','A','B','C');   結果為： A|B|C<br>
3.FORMAT()  數(shù)字格式化
SELECT FORMAT(12560.7,2);  結果：12，560.70
SELECT FORMAT(12560.78,1); 結果：12，560.8
4.LOWER() 轉換成小寫字母
5.UPPER() 轉換成大寫字母
6.LEFT()  獲取左側字符
SELECT LEFT('mysql',2);  結果：my
7.RIGHT() 獲取右側字符
8.LTRIM() 刪除前導空格(=LEFT TRIM())
9.RTRIM() 刪除后續(xù)空格
10.TRIM（）刪除前后兩邊的空格，還可以刪除指定的前導和后續(xù)的字符,不能刪除中間的字符
SELECT TRIM(LEADING'?','??MYSQL????');  結果：MYSQL????
SELECT TRIM(TRAILING'?','??MYSQL????');  結果：??MYSQL
SELECT TRIM(BOTH'?','??MYSQL???');  結果：MYSQL
11.REPLACE() 替換字符
SELECT REPLACE('??MYSQL???','?','-');  結果：--MYSQL---
12. SUBSTRING(string，offset，length)   截取字符串
SELECT SUBSTRING('MYSQL',2,3);  結果：SQL
13.[NOT]LIKE  模糊匹配
(%)：代表任意個字符，0個或多個
(_)：代表任意一個字符，只有一個
SELECT name FROM test WHERE name LIKE'%O%';  結果：輸入name 中帶‘O’的name
SELECT name FROM test WHERE name LIKE'%1%%' ESCAPE'1';   找到中間帶% 的匹配name
三、數(shù)值運算符和函數(shù)
SELECT CEIL(3.01) ==>4 /*有n.xx 都是n+1 進一取整 向上取整
SELECT FLOOR(3.99) ==>3 /*取n.xx 都是n 舍一取整 向下取整
SELECT 3 DIV 4 ==> 0 /*整數(shù)除法
SELECT 3/4 ==>0.75 /*除法
SELECT 21 MOD 2 ==>1 /*取余數(shù)(取模)整數(shù),小數(shù)都可以
SELECT 21 % 2 ==>1 /*取余數(shù)(取模)整數(shù),小數(shù)都可以
SELECT POWER(3,4) ==>81 /*3的4次方 冪運算
SELECT ROUND(3.1415926,4) ==>3.142 /*四舍五入
SELECT TRUNCATE(123.89,1) ==>123.8 /*截取小數(shù)點后的位置
SELECT TRUNCATE(123.89,0) ==>123 /*截取小數(shù)點后的位置,0位為整數(shù)部分
SELECT TRUNCATE(123.89,-1) ==>120 /*截取-1,從個位起去掉后面的數(shù)值替換為0
四、比較運算符和函數(shù)：
比較運算符和函數(shù)：（給出的都是閉合的區(qū)間）
（1）想在first_name這個字段中查找哪個字段值為NULL，可以用IS [NOT] NULL,這比較運算符，
如：SELECT * FROM test WHERE first_name IS NULL;
（2）[NOT] IN 的具體用法，SELECT 16 IN(1，2，16，68),返回的是true就是1.
（3）[NOT] BETWEEN....AND....具體用法：SELECT 6 BETWEEN 0 AND 29，返回的是true也就是1。也可以是字符串。
五、日期時間函數(shù)：
NOW():顯示當前日期和時間：
SELECT NOW();
CURDATE():顯示當前日期：
SELECT CURDATE();
CURTIME():顯示當前時間：
SELECT CURTIME();
DATE_ADD():指定日期加上一段日期：
SELECT DATE_ADD('2016-6-6',INTERVAL 1 YEAR);//后面的單位可以是week,year,month等
DATEDIFF():計算兩個日期相差的時間：
SELECT DATEDIFF('2016-6-6','2015-6-6');
DATE_FORMAT():將日期以指定格式顯示：
SELECT DATE_FORMAT('2016-6-6','%m/%d/%Y');
六、信息函數(shù)：
（1）CONNECTION_ID(); // 連接ID
mysql> SELECT CONNECTION_ID();
（2）SELECT DATABASE(); // 當前數(shù)據(jù)庫
（3）LAST_INSERT_ID(); // 最后句插入記錄的 ID 號，如果是一次insert中插入的是多條記錄，得到的是多條中的第一條（而不是最后一條?。?（4）VERSION(); // 版本的信息
（5)USER(); // 當前用戶
SELECT USER();
七、聚合函數(shù)：只有一個返回值
AVG() - 平均值
SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;
COUNT() - 計數(shù)
SELECT COUNT(goods_id) as counts FROM tdb_goods;
MAX() - 最大值
SELECT MAX(goods_price) as counts FROM tdb_goods;
MIN() - 最小值
SELECT MIN(goods_price) as counts FROM tdb_goods;
SUM() - 求和
SELECT SUM(goods_price) as counts FROM tdb_goods;
八、加密函數(shù)：
MD5():生成信息摘要：
SELECT MD5('213');
PASSWORD():修改mysql密碼：
SET PASSWORD=PASSWORD('1234');
九、自定義函數(shù)：
自定義函數(shù)：UDF是對MySQL擴展的途徑，其用法與內(nèi)置函數(shù)相同。
必要條件：返回值（必須），參數(shù)（非必須）    函數(shù)可以返回任意類型的值，同樣可以接收這些類型的參數(shù)，參數(shù)與返回值沒有必然的內(nèi)在聯(lián)系
MySQL中參數(shù)的數(shù)量不能超過1024個
創(chuàng)建自定義函數(shù)：
CREATE FUNCTION function_name(參數(shù)) RETURNS 返回值類型
{STRING|INTEGER|REAL|DECIMAL}
routine_body
RETURN 返回值

routine_body 函數(shù)體
1、函數(shù)體由合法的SQL語句構成；
2、函數(shù)體可以是簡單的SELECT或INSERT語句；
3、函數(shù)體如果為復合結構則使用BEGIN...END語句；
4、復合結構可以包含聲明，循環(huán)，控制結構；

刪除函數(shù)：
DROP FUNCTION [IF EXISTS] function_name;


創(chuàng)建日期時間自定義函數(shù)：
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H點:%i分:%s秒');

SELECT f1();

【創(chuàng)建函數(shù)之前要寫 set names utf8mb4; 否則調(diào)用時會出錯】

DATE_FORMAT()函數(shù)在格式化日期格式時，應該是百分號在代表年、月、日的字母前面，字母的大小寫不同，所表示格式也有所區(qū)別：
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'),其結果是：
2015-07-04
因此，各字母所表示的含義為：
Y：2015              
y：15
M：july
m：07
D：4th
d：04

創(chuàng)建帶參數(shù)的自定義函數(shù)：
CREATE FUNCTION f2（num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED）
RETURNS FLOAT(10，2) UNSIGNED
RETURN(num1+num2)
刪除函數(shù):
   DROP FUNCTION fun_name;

//創(chuàng)建具有復合結構函數(shù)體的自定義函數(shù)
修改分隔符：DELEMITER 分隔符
Eg：DELIMITER // /* 將分隔符修改為 '//' */
當函數(shù)體內(nèi)需要執(zhí)行的是多條語句時，要使用BEGIN...END語句
且當編寫函數(shù)體內(nèi)容的時候，需要使用 DELIMITER 關鍵字將分隔符先修改為別的，否則編寫語句的時候寫到 ';' 的時候會直接執(zhí)行，導致函數(shù)編寫失敗
刪除函數(shù)：DROP FUNCTION [IF EXISTS] function_name
*/
Eg：
DELIMITER //定義分隔符
CREATE FUNCTION ADD_USER(p_id SMALLINT,username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT user(p_id,username) VALUES(p_id,username);
RETURN LAST_INSERT_ID();
END
十、自定義函數(shù)回顧
【自定義函數(shù)】簡稱UDF;是對MySQL擴展的一種途徑
語法: CREATE FUNCTION ....
------------------------------------------
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
創(chuàng)建一個函數(shù):
沒有參數(shù)的函數(shù):
CREATE FUNCTION f1()
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H點%i分%s秒');

帶參數(shù)的函數(shù):
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;
說明 f1、f2是函數(shù)名 num1、num2是形參
RETURNS (returns) 后面跟的是返回值的數(shù)據(jù)類型
RETURN 后面跟的是函數(shù)體
創(chuàng)建一個執(zhí)行多條sql語句的函數(shù) f3(),并放回主鍵
如果有多條sql語句，必須放在 BEGIN 與END 之間
修改 結束符號成 ## 原本是; 當然你也可以改回來
1、DELIMITER ##
創(chuàng)建一個函數(shù)(功能是傳遞一個參數(shù),參數(shù)是表 test 的test字段值，返回插入數(shù)據(jù)的主鍵)
2、CREATE FUNCTION f3(test varchar(20))
RETURNS INT UNSIGNED
BEGIN
INSERT `test`(`test`)values(test);
RETURN LAST_INSERT_ID();
END
##
修改結束符變回 ; (delimiter)
3、DELIMITER ; 
刪除一個函數(shù):
DROP FUNCTION IF EXISTS `f1`;
調(diào)用一個函數(shù):
SELECT f1();
SELECT f2(23,2);
最后編輯于 ：
?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容