本文給大家匯總介紹了mysql中的常用的運算符以及常用函數(shù)的用法及示例，非常的全面，有需要的小伙伴可以參考下
我們先來看個示例
use?test; create?table?`employee`( ??emp_no?int?unsigned, ??emp_name?varchar(30), ??emp_sex?varchar(3), ??emp_age?tinyint?unsigned, ??sal?double, ??history?datetime ); insert?into?employee?values(1,?'張三',?'男',?18,?5000,?'2012-04-23'), (2,?'李四',?'男',?27,?4500,?'2013-05-23'), (3,?'王五',?'男',?23,?4700,?'2012-04-21'), (4,?'子龍',?'男',?19,?3800,?'2011-03-04'), (5,?'李白',?'男',?15,?6200,?'2015-09-09'), (6,?'劉備',?'男',?28,?2500,?'2016-02-11'), (7,?'呂布',?'男',?21,?6000,?'2010-10-18'), (8,?'尚香',?'女',?16,?4500,?'2011-09-26'), (9,?'小喬',?'女',?15,?null,?'2013-07-05'), (10,?'大喬',?'女',?16,?5000,?'2017-09-01');
常用的運算符:
1: 等于( = )
??select?*?from?employee?where?sal?=?3800; ??select?*?from?employee?where?sal?=?null;???--這里查詢不到為null的數(shù)據(jù)
2: 等于( )
??select?*?from?employee?where?sal??3800; ??select?*?from?employee?where?sal??null;??--這里可以查詢到為null的數(shù)據(jù)
3: is判斷(null)
??select?*?from?employee?where?sal?is?null; ??select?*?from?employee?where?sal?is?not?null;
4: null值判斷還可以使用isnull();
??select?*?from?employee?where?isnull(sal); ??select?*?from?employee?where?!isnull(sal);
5: 在區(qū)間(between)內(nèi)? between min and max? ps:這里是一個閉區(qū)間
select?*?from?employee?where?sal?between?4500?and?5000;
6: 不在區(qū)間內(nèi)
select?*?from?employee?where?sal?not?between?4500?and?5000; ??--null不為包括進(jìn)去
7: and 和 or
??select?*?from?employee?where?sal?not?between?4500?and?5000?or?sal?is?null; ??select?*?from?employee?where?sal?=?4500?and?emp_sex?=?'女';
8: 小于(), 小于等于(=)
select?*?from?employee?where?sal?>=?4500;
***************************************************************************************************************
數(shù)學(xué)函數(shù)
1: rand();
??select?rand()?from?dual;??--dual是一個偽表 ??select?1+1?from?dual; ??select?rand();??--可以簡寫
2: least(value1, value2, …) 返回最小值
??select?least(54,76,4,65,76,87,87,56,65,654,45,23,1,76); ??select?least(54,76,4,65,76,87,87,56,65,654,45,23,1,76)?as?min_value;??--列名可以起一個別名
3: greatest(value1, value2, …) 返回最大值
?select?greatest(54,76,4,65,76,87,87,56,65,654,45,23,1,76);
4: round(M, D); 返回M的四舍五入的值, D表示要保留幾們小數(shù),默認(rèn)值是0
??select?round(1.69); ??select?round(1.69,?1);
5: abs() 絕對值
??select?5-10; ??select?abs(5-10);
***************************************************************************************************************
匯總函數(shù)
1: avg();?
??select?*?from?employee?where?sal?>=?6000; ??select?avg(sal)?from?employee?where?sal?>=?6000;
2: count()
??select?count(*)?from?employee; ??select?count(emp_name)?from?employee; ??select?count(sal)?from?employee;???--打印9?這里會忽略null值 ??select?count(*)?from?employee?where?sal?>=?4000; ??select?count(*)?from?employee?where?sal?<p>3: sum()<br></p><pre class="brush:sql;toolbar:false">select?sum(sal)?from?employee?where?sal?>=?6000;
4: min()
?select?min(sal)?from?employee;
5: max()
?select?max(sal)?from?employee;
***************************************************************************************************************
日期函數(shù)
1: 獲取當(dāng)前的日期時間
??select?now(),?sysdate(),?current_timestamp(); ??select?now(6),?sysdate(6),?current_timestamp(6); ??ps:?now(),?current_timestamp();沒有區(qū)別,?表示sql開始執(zhí)行時的時間 ????sysdate()表示這個函數(shù)開始時間
2: 獲取當(dāng)前日期
select?curdate();? ??--只有年月日
3: 獲取當(dāng)前時間
select?curtime();? ??--只有時分秒
4: 日期的加運算date_add?????
??select?history,?date_add(history,?interval?'1?12:10'?day_minute)?from?employee;??--date_add(history,?interval?'1?12:10'?day_minute) ??select?history,?date_add(history,?interval?'1-1'?year_month)?from?employee;????--date_add(history,?interval?'1-1'?year_month) ??select?history,?date_add(history,?interval?'1'?second)?from?employee;???????--date_add(history,?interval?'1'?second)
5: 日期的減運算data_sub
select?history,?date_sub(history,?interval?'1-1'?year_month)?from?employee;
6: 計算日期差
?select?history,?sysdate(),?datediff(sysdate(),?history)?from?employee;?????--以天數(shù)來表示
7: 獲取日期的指定部分(把日期轉(zhuǎn)換為指定的格式)? date_format()
??select?history,?date_format(history,?'%Y年%m月%d號')?from?employee; ??select?history,?date_format(history,?'%d號')?from?employee; ??select?history,?date_format(history,?'%Y年%m月%d號?%H時%i分%s秒')?from?employee;
8: 計算出一個日期是星期幾
select?history,?dayname(history)?from?employee;
9: 中文日期字符串轉(zhuǎn)換日期str_to_date()
??insert?into?employee?values(11,?'張飛',?'男',?22,?3000,?'2017年02月01號');??--報錯 ??insert?into?employee?values(11,?'張飛',?'男',?22,?3000,?str_to_date('2017年02月01號',?'%Y年%m月%d號?%H時%i分%s秒'));
??? insert into employee values(12, ‘二哥’, ‘男’, 22, 3000, str_to_date(‘2017年02月01號 23時02分02秒’, ‘%Y年%m月%d號 %H時%i分%s秒’));
??? insert into employee values(12, ‘二哥’, ‘男’, 22, 3000, str_to_date(‘2017年02月01號 11時02分02秒’, ‘%Y年%m月%d號 %h時%i分%s秒’));
??? ps: 如果是h則表示12小制, 如果是大H則表示24小明制;
字符串函數(shù)
1: left(str, len) 返回字符串str的左端len個字符
select?left('abcdefg',?5);
2: length()
select?length('abcdefg');
3: lower(str) 返回小寫的字符串str
?select?lower('HELLO');
4: substring() 取子字符串, 第二個參數(shù)是截取的起始位置, 第三個參數(shù)是要截取的長度
select?substring('helloworld',2,3);
5: concat() 字符串拼接
?select?concat(emp_name,?'員工')?from?employee;
6: replace(替換
select?replace(emp_name,?'李',?'老')?from?employee?where?emp_name?=?'李四';
