Mysql学习Day2 文本处理、日期处理、数值处理、聚焦函数和过滤分组

1-文本处理函数

//文本处理函数 UPPER()把全部文本都变成大写 ,转化成小写 Lower()
 SELECT  u.user_name , UPPER(u.user_name) AS '大写' from sys_user   u where  u.id< 10 

//Locate(Substr,str，X)查询子串Substr在第X个字符后str中第一次出现的位置
//如果没有的话返回的是0
SELECT LOCATE('abcd','AAAAAabcdefg')
SELECT LOCATE('abcd','AabcdAAabcdefg',3)


//Left()返回指定长度字符串左边的, Right（）返回右边的
 SELECT  LEFT(u.user_name,2) , RIGHT(u.user_name ,3) AS '大写' from sys_user   u where  u.id< 10 

//Substring（str ,position）截取从第几个开始的子字符串，包括第position
SELECT Substring('1234567890',2)  --> 234567890
//但是如果后面是0的话返回的是一个NULL字符串
SELECT Substring('1234567890',0)  --> NULL

//Substring（str ,position，length）截取长度为length,从第几个开始的子字符串，包括第position
SELECT Substring('1234567890',2,4)  --> 2345

SELECT Substring('1234567890',2,10)
 -->234567890如果超过长度返回最大的

SELECT Substring('1234567890',0,10) --> NULL

//听起来像的，发音类似的
 SELECT u.user_name from sys_user   u where  Soundex(u.user_name)=SOUNDEX('往')

2-日期和时间处理函数

//日期不能直接用来查询，因为数据库里面的类型是datetime存的既有日期也有时间
//------------------1.Date()返回日期时间的日期部分
SELECT u.user_name from sys_user   u where  (u.gmt_create) ='2018-01-31'
SELECT u.user_name from sys_user   u where  Date(u.gmt_create) ='2018-01-31'
//同理时间可以用BETWEEN AND 
 SELECT u.user_name from sys_user   u where  Date(u.gmt_create)
  BETWEEN '2018-01-31' AND '2018-05-30'
//------------------2.Year()返回日期时间的年部分 Month()月 Day()日

//------------------3.Hour（）小时Minute()分钟Second()秒
CurTime()返回当前时间 CurDate()返回当前日期

//------------------5.DateOfWeek()对于一个日期返回星期几，而且返回的结果是1-7星期日是1 
，星期六是7
SELECT DAYOFWEEK('2018-07-14')

//------------------6.AddDate()增加一个日期，天或者周等  AddTime()增加一个时间（分，时等）

//-------------------7.DateDiff()计算两个日期只差 Date_Add()高度灵活的日期运算函数


//-------------------8.Date_Format()格式化日期或者时间串
SELECT DATE_FORMAT(u.gmt_create,'%yy-%m-%d %H:%i:%s')
FROM  sys_user u where id< 5

---

常用格式化代码

---

%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值（00-31）
%e 月的天，数值（0-31）
%f 微秒
%H 小时（00-23）
%h 小时（01-12）
%I 小时（01-12）
%i 分钟，数值（00-59）
%j 年的天（001-366）
%k 小时（0-23）
%l 小时（1-12）
%M 月名
%m 月，数值（00-12）
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒（00-59）
%s 秒（00-59）
%T 时间, 24-小时（hh:mm:ss）
%U 周（00-53）星期日是一周的第一天
%u 周（00-53）星期一是一周的第一天
%V 周（01-53）星期日是一周的第一天，与 %X 使用
%v 周（01-53）星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天（0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位

3-数值处理函数

Rand()随机函数
----获取随机数0-1范围：SELECT RAND(); 
----取整 
----SELECT CEIL(RAND()); 
----SELECT CEILING(RAND()); 
----SELECT FLOOR(RAND()); 
----CEIL(RAND()*N)的取值范围：1-N 
----取x-y之间的随机数：SELECT (CEIL(RAND() * (y-x+1)) + (x-1)); 
----随机取整数3或4：SELECT (CEIL(RAND() * 2) + 2); 
----随机取整数100-150：SELECT (CEIL(RAND() * 51) + 99);

 SELECT   ABS(-3.6)--------------------绝对值函数
 SELECT  PI() -------------------------PI
 SELECT MOD(10,3);---------------------10/3的余数函数
 select SQRT(16);-----------------------平方根函数

4-聚焦函数

平均值函数

SELECT  AVG(a.account) from admin_advertiser a where id <10

查询某列的行数即有多少数据

SELECT  AVG(a.account) from admin_advertiser a where id <10

某列的最大值、最小值、总和忽略为空的数据

SELECT  MAX(a.account) from admin_advertiser a 
SELECT  MIN(a.account) from admin_advertiser a 
SELECT  SUM(a.account) from admin_advertiser a 

DISTINCT 去掉相同的数据，如果是
SELECT AVG( DISTINCT price )则平均值只会考虑价格不同的数据

5-过滤分组，所有where能用的同样适用having，只不过where过滤的是行，having过滤的是列

注意Having过滤的是前面查询的分组
SELECT id ,   account  as a from admin_advertiser  group by id
Having account >=2