day03 MYSQL数据库函数操作

文章目录

• • 字符串函数
  • • 练习
  • 数学函数
  • • 练习
  • 日期和时间函数
  • • 练习
  • 条件判断函数
  • • 练习
  • 系统信息函数
  • 加密函数
  • 其他函数
  • • 1.进制转换
    • 2.IP地址转换
  • 数据库下载

字符串函数

函数	作用
UPPER(列|字符串)	将字符串每个字符转为大写
LOWER(列|字符串)	将字符串每个字符转为小写
CONCAT(str1,str2,…)	将所有字符串连接成一个字符串
REPLACE(列|字符串,新字符串)	使用新字符串替换旧字符串
LENGTH(列|字符串)	求字符串长度
SUBSTR(列|字符串,开始点[,长度])	字符串截取
LEFT(str,len)	获取字符串左边len个字符组成的字符串
RIGHT(str,len)	获取字符串右边len个字符组成的字符串
MID(str,pos,len)	获取字符串中从pos(第几个)位置开始，长度为len的字符串
ASCII(字符)	返回与指定字符对应的十进制整数
CHR(数字)	返回与整数对应的字符
RPAD(列|字符串,长度,填充字符) LPAD(列|字符串,长度,填充字符)	用指定的字符在字符串右或左填充
LTRIM(字符串)、RTRIM(字符串)	去掉字符串左或右的空格
TRIM(列|字符串)	去掉字符串左右空格
INSTR(列|字符串,要查找的字符串,开始位置,出现位置)	查找一个子字符串是否在指定的位置上出现

练习

1. 把员工姓名首字母大写（其他字母小写）

SELECT CONCAT(UPPER(LEFT(ename,1)),LOWER(SUBSTR(ename,2))) FROM emp;

2. 查询出姓名长度是5的所有员工信息

SELECT * FROM emp WHERE LENGTH(ename)=5;

3. 查询出员工姓名前三个字母是‘JAM’的员工

SELECT * FROM emp WHERE SUBSTR(ename,1,3)='JAM';

4. 显示姓名的一部分（前面或者后面）

SELECT ename 原始姓名,SUBSTR(ename,3) FROM emp;
# 只显示后三个字符
SELECT ename 原始姓名,SUBSTR(ename,-3) FROM emp;

数学函数

函数	作用
ROUND(数字[,保留位数])	根据指定的保留位数对小数进行四舍五入，如果不指定保留位数，则小数点之后的数字全部进行全部四舍五入
TRUNCATE(数字[,截取位数])	保留指定位数的小数，如果不指定，则表示不保留
CEIL(数字)，CEILING(数字)	对小数向上取整
FLOOR(数字)	对小数向下取整
RAND([n])	随机返回0到1.0之间的小数，如果指定n每次产生的就都是重复的

练习

1. 通过数据库的函数，生成一个六位数字的随机验证码。

SELECT LPAD(TRUNCATE(RAND()*1000000,0),6,'0');

首先使用RAND生成随机数，在乘以1000000得到一个带小数的六位数，然后使用TRUNCATE函数实现取整，但是RAND的随机数可以第一位小数为0，所以要左侧补充0以实现六位。

日期和时间函数

函数	作用
CURDATE()，CURRENT_DATE()	返回当前日期
CURTIME()，CURRENT_TIME()	返回当前时间
NOW()，CURRENT_TIMESTAMP()，LOCALTIME()， SYSDATE()，LOCALTIMESTAMP()	返回会当前日期和事件
YEAR(日期)	返回日期中的年份
MONTH(日期)	返回日期中的月份[1,12]
DAY(日期)	返回日期中的天[1,31]
HOUR(时间)	返回时间中的小时
MINUTE(时间)	返回实践中的分钟
SECOND(时间)	返回时间中的秒
DAYOFWEEK(日期)	返回星期几，1星期日，2星期一
WEEKDAY(日期)	返回星期几，0星期一，1星期二
DAYOFYEAR(日期)	计算指定日期是本年第几天
DAYOFMONTH(日期)	计算指定日期是本月第几天
QUARTER(日期)	计算指定日期是第几季度
TIME_TO_SEC(time)	将指定时间转换为秒
SEC_TO_TIME(sec)	将以秒为单位的时间转换为时分秒的格式
DATEDIFF(d1,d2)	计算两个日期相隔的天数
ADDDATE(date,n)	计算指定日期加上n天后的日期
SUBDATE(date,n)	计算指定日期减去n天后的日期
ADDTIME(time,n)	计算指定时间加上n秒后的时间
SUBTIME(time,n)	计算指定时间减去n秒后的时间
DATE_FORMAT(date,f)	按格式返回日期
TIME_FORMAT(time,f)	按格式返回时间

练习

1. 将当前日期和时间各加1天，10小时

SELECT ADDDATE(CURDATE(),1),ADDTIME(CURTIME(),'10:00:00');

2. 格式化输出日期和时间

SELECT CONCAT(DATE_FORMAT(CURDATE(),'%Y年%m月%d日'),
TIME_FORMAT(CURTIME(),' %H时%i分%s秒')) 当前时间;

DATE_FORMAT ; TIME_FORMAT格式化字符串，CURDATE，CURTIME获取当前时间，CONCAT连接在一起， 改名

条件判断函数

函数	作用
IF(expr,v1,v2)	如果表达式expr成立，返回结果v1；否则，返回结果v2。
IFNULL(v1,v2)	如果v1的值不为NULL，则返回v1，否则返回v2
CASE	分支(详见下面)

• IF(expr,v1,v2)

SELECT IF(1>0,'true','false');
->true

• IFNULL(v1,v2)

SELECT IFNULL(NULL,'maye');
->maye

• CASE

  语法1：

CASE
	WHEN expr1 THEN V1
	WHEN expr2 THEN V2
	...
	ELSE vn
END	

SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0

语法2：

CASE expr 
　　WHEN e1 THEN v1
　　WHEN e1 THEN v1
　　...
　　ELSE vn
END

SELECT CASE 1 
　　WHEN 1 THEN '我是1'
　　WHEN 2 THEN '我是2'
ELSE '你是谁'

练习

1. 按照工资sal的不同，区分不同人的等级（无其他含义，仅练习用）

SELECT ename,job,sal,
CASE sal
	WHEN 800 THEN '我是贫民阶层.'
	WHEN 3000 THEN '我是资产阶级.'
	WHEN 5000 THEN '我是大地主阶层'
	ELSE '我是平民阶层'
END 阶层 FROM emp;

系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

函数	作用
VERSION()	返回数据库的版本号
CONNECTION_ID()	返回服务器的连接数
DATABASE()、SCHEMA	返回当前数据库名
USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()、CURRENT_USER	返回当前用户
CHARSET(str)	返回字符串str的字符集
COLLATION(str)	返回字符串str的字符排列方式

加密函数

加密函数是MySQL用来对数据进行加密的函数.

函数	作用
MD5(str)	对字符串str进行散列，可以用于一些普通的不需要解密的数据加密，如密码
sha1	对字符串str进行散列，可以用于一些普通的不需要解密的数据加密，如密码，比MD5更安全

其他函数

1.进制转换

函数	作用
BIN(x)	返回x的二进制
HEX(x)	返回x的十六进制
OCT(x)	返回x的八进制
CONV(x,from_base,to_base)	将from_base进制的x，转成to_base进制

SELECT BIN(10),HEX(10),OCT(10),CONV(16,16,10);
->1010 A 12 22

2.IP地址转换

函数	作用
INET_ATON(IP)	将点分十进制的IP地址转为数字
INET_NTOA(number)	将数字形式的IP转为点分十进制

SELECT INET_ATON('192.168.0.1')
    ->3232235521
SELECT INET_NTOA(3232235521)
    ->192.168.0.1

数据库下载

点击进入链接 test.sql