SQL语句小记（上）：https://developer.aliyun.com/article/1488662
LENGTH()函数 获取长度
一个汉字3个字节,
按照名字长度排序
SELECT * FROM reader ORDER BY LENGTH(`readername`)
SELECT *,LENGTH(`readername`) "名字长度" FROM reader ORDER BY 名字长度
readerid readername identity gender
school tel 名字长度
S1005 郭鹏 老师 男 信息学院 110 6
S1003 大王_ 学生 女 计算机学院 (NULL) 7
S1001 胡峰是的 学生 男 计算机学院 (NULL) 12
S1004 高文丽但是 学生 女 计算机学院 (NULL) 15
S1002 包朦穗啊是的 学生 女 计算机学院 1234 18
多字段排序,先写先排序
单行函数
字符函数
length() 长度
作用:获取参数值得字节数
utf-8 字符集
一个汉字占用3个字节
一个字母占用一个字节
例:统计type字段的长度
SELECT `type`,LENGTH(TYPE) 'type长度' FROM books
concat() 拼接
作用:拼接字符串
upper、lower 大小写转换
作用：大小写转换
例:将bookId字段转换为小写
SELECT `bookid`,LOWER(`bookid`) FROM `books`
substr、substring 切片
substr只是substring的简写,
注意:SQL中索引从1开始
# 从索引4开始输出 ---> "的SUBSTR函数使用" SELECT SUBSTR('SQL的SUBSTR函数使用',4) out_put
# 从索引5开始输出,长度为6的字符串 输出----> "SUBSTR" SELECT SUBSTR('SQL的SUBSTR函数使用',5,6) out_put
函数间可嵌套使用
instr 查索引
返回子串第一次出现的索引,找不到则返回 0
例:
SELECT INSTR('你好!SQL','SQL') OUTPUT
trim 除空格
去除空格输出
SELECT ' 张翠花 ' les,TRIM(' 张翠花 ') out_put
去除前后指定字符,注意,中间的无法去除
# 输出 张a翠花 SELECT TRIM('a' FROM 'aaaaaa张a翠花aaaaaaaa') out_put
lpad 左填充
填充在左边
# 使用 '啊' 将 '你好' 填充至长度为5 SELECT LPAD('你好',5,'啊') out_put # 输出 '啊啊啊你好' # 长度不足填充时,会截取 SELECT LPAD('你好',1,'啊') out_put # 输出 '好'
rpad 右填充
# 使用 '啊' 将 '你好' 填充至长度为5 SELECT RPAD('你好',5,'啊') out_put # 输出 '你好啊啊啊' # 长度不足填充时,会截取 SELECT RPAD('你好',1,'啊') out_put # 输出 '你'
replace 替换
注意:将所有出现的 都会 替换
SELECT REPLACE('PHP是世界上最好的编程语言','PHP','java') out_put #输出 'java是世界上最好的编程语言'
数学函数
round 四舍五入
SELECT ROUND(1.49),ROUND(1.5) #输出 1 2
# 四舍五入保留小数点后两位 SELECT ROUND(1.49321,2),ROUND(1.49754,2) # 输出 1.49 1.50
ceil与floor 向上取整与向下取整
SELECT CEIL(1.001),FLOOR(1.999) # 输出 2 1
truncate 截取
SELECT TRUNCATE(1.49754,2) # 保留小数点后两位 截断,不四舍五入 输出 1.49
mod 取余
mod(a,b) == a-a/b*b
SELECT MOD(624,10) select 624%10 # 同义
时间函数
now()
获取当前时间
curdate
curtime
SELECT NOW() # 2021-11-03 23:36:37 SELECT CURDATE() # 2021-11-03 SELECT CURTIME() # 23:37:04
year
只 获取年份
SELECT YEAR(NOW()) # 2021 SELECT YEAR('1998-07-24') # 1998 SELECT YEAR(pubdate) '年' FROM `books` #
month / monthname
SELECT MONTH(NOW()) # 11 SELECT MONTHNAME(NOW()) # November 返回月份英文名字
datediff 日期差
计算两个日期之间相差的天数, MAX(pubdate) 减去 MIN(pubdate),交换将会得到负值
SELECT DATEDIFF(MAX(`pubdate`),MIN(`pubdate`)) '相差天数' FROM `books`
SELECT DATEDIFF(NOW(),'2001-05-23') '计算活了多少天' # 7470
时间格式转换 str_to_date
作用:识别 年月份
不同格式的话,SQL并不知道哪个是年,哪个是月,哪个是日,需 使用 str_to_date标识
SELECT STR_TO_DATE('2020-3-25','%Y-%c-%d') out_put # 2020-03-25
日期转为字符 date-format
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') # 2021年11月03日
其他函数
SELECT VERSION() #mysql版本 SELECT DATABASE() # 当前数据库 SELECT USER() # 当前用户
流程控制函数
if
SELECT IF(10>9,'大','小') '比较大小10>9'
case
类似switch…case…
判断 number 字段的值与when 中是否相等,相等显示 对应then 中的值
SELECT number, CASE number WHEN 23 THEN '23' WHEN 27 THEN '27' ELSE '其他' END AS 显示 FROM books
多重if
SELECT number, CASE WHEN number >20 THEN '大于20的' WHEN number=20 THEN '等于20' ELSE '小于20' END AS 判断 FROM books
分组函数
基本功能:作统计使用
- NULL都不参与
- 都支持与distinct关键字
- 和分组函数一同查询的字段有限制,group by
# 行数不对等,count(*)只有一行,而type字段有 count(*) 行 SELECT COUNT(*),type FROM books
COUNT(字段) 计数
统计字段出现的次数,不会统计为 NULL 的
例:想查询出teble表 名字 中,带有 “王” 的,有多少个人
select count(name) from table where name like concat('%',王,'%')
# 统计type字段有多少个学院,去重 SELECT COUNT(DISTINCT `type`) FROM books
# 统计行数,只要有一个字段不为NULL,就会被统计 # COUNT(*) 最常用,效率最高 SELECT COUNT(*) FROM books # 以下都可以得到相同结果 select count(1) from books select count('一个常量') from books
SUM(字段) 求和
合计字段之和,NULL不参与运算求和
例:薪资表中,想统计一共发了多少薪资(每个人薪资之和)
select sum(Salary) '总薪资' from table
avg 平均
平均值会保留小数点后4位,NULL不参与运算求和
sum与avg一般只处理数值类型的
max / min
NULL不参与
SELECT SUM(`number`) '求和',AVG(`number`) '平均值',MAX(`number`) '最大值',MIN(`number`) '最小值' FROM books
分组查询group by
基本语法
select '字段','分组字段/表达式/函数' from 表名 [where 条件] 可省 group by '分组字段/表达式/函数' [order by 字段] 可省
查询每个学院 number字段的和与平均值
SELECT SUM(`number`),AVG(`number`),`type` FROM `books` GROUP BY `type`
having 关键字
添加分组后的筛选条件,—先查询,再进行having 筛选
放在最后
查询每个学院 number字段的和与平均值,且和大于100的
SELECT SUM(`number`),AVG(`number`),`type` FROM `books` GROUP BY `type` HAVING SUM(`number`)>100
where关键字
—> 查询时,根据条件筛选
分组前筛选—where-----在group by前
分组后筛选----having—在group by后
若where与having都可,优先where效更高
需求:隐藏那个手机号中间4位
隐藏 密码中间两位
SELECT REPLACE(`password`,SUBSTR(`password`,3,2),'**' ) out_put,`password` FROM `users`
