1.数据类型
1.1数值型
1.1.1整形
整形又分为tinyint、smallint、mediumint、int/integer、biginit,占用的字节数分别为1、2、3、4、8。特点:(1)如果不设置有符号还是无符号,默认是有符号的,如果想要设置无符号需要添加unsigned关键字;(2)如果插入的数值超出了整形范围,会报out of range异常并且插入临界值;(3)如果不设置长度,会有默认长度;(4)长度代表了显示的最大宽度(而不是数值的范围),如果不够会用0在左边填充,但必须搭配zerofill使用,例如:id int(11) unsigned zerofill
1.1.2小数
(1)浮点型:float(M,D) double(M,D)
(2)定点型:dec(M,D) decimal(M,D)
M:整数部位+小数部位的长度
D:小数部位长度
特点:
(1)如果数据超过了取值范围则取临界值
(2)M和D都可以省略
(3)如果是定点型的数值,则M默认是10,D默认是0;如果是浮点型的数值则会根据插入的数值决定精度
1.2字符型
(1)较短的文本:char、varchar
(2)较长的文本:text、blob(较大的二进制数据)
- varchar和char的对比:
写法 | M的意义 | 特点 | 空间的耗费 | 效率 |
char(M) | 最大的字符数,可省略,默认为1 | 固定长度的字符 | 比较消耗 | 高 |
varchar(M) | 最大的字符数,不可以省略 | 可变长度的字符 | 比较节省 | 低 |
1.3日期型
(1)date:只保存日期 YYYY-MM-DD
(2)time:只保存时间 HH:mm:ss
(3)datetime:保存日期+时间 YYYY-MM-DD HH:mm:SS
(4)year:只保存年份 YYYY
(5)timestamp:保存日期+时间 YYYY-MM-DD HH:mm:SS
- datetime和timestamp
字节数 | 范围 | 是否受时区影响 | |
datatime | 8 | 1000-9999 | 否 |
timestamp | 4 | 1970~2038 | 是 |
2.常见的函数
函数的使用:select 函数名(实参列表)
好处:提高函数的重用性和隐藏实现细节
2.1字符函数
#1.CONCAT(str1, str2, str3...strn) :连接字符串
SELECT CONCAT('123','456','789') as str;
#2.SUBSTR(str FROM index) :截字符串
SELECT SUBSTR('123456789' from 2) as str;
#3.SUBSTR(str, index ,len)
SELECT SUBSTR('123456789', 2, 5) as str;
#4.SUBSTR(str, index )
SELECT SUBSTR('123456789',1) as str;
#5.SUBSTR(str from index for len)
SELECT SUBSTR('123456789' from 1 for 4) as str;
#6.UPPER(str) 字母变为大写
SELECT UPPER('abc') as str;
#7.LOWER(str) 字母变为小写
SELECT LOWER('ABC') as str;
#8.CHAR_LENGTH(str)返回字符串长度(以字符为单位)
SELECT CHAR_LENGTH('我爱中国') as num;
#9.LENGTH(str) 返回字符串长度(以字节为单位)
SELECT LENGTH('我爱中国') as num;
#10.REPLACE(str,oldstr,newstr) 替换字符串
SELECT REPLACE('i am spider man!', 'spider', 'super') as str;
#11.INSTR(str,substr) 获取子串第一次出现的索引值
SELECT INSTR('hello world', 'w') as `index`;
#12.REPEAT(str,count) 重复字符串
SELECT REPEAT('123', 3) as str;
#13.REVERSE(str) 反转字符串
SELECT REVERSE('hello world') as str;
#14.TRIM(str) 去除两端空格
SELECT TRIM(' abc ') as str;
#15.LTRIM(str) 去除左侧空格
SELECT LTRIM(' abc') as str;
#16.RTRIM(str) 去除右侧空格
SELECT RTRIM('abc ') as str;
#17.LPAD(str,len,padstr) 当长度不够的时候向左填充len个padstr
SELECT LPAD('hi', 4, '?') as str;
#18.RPAD(str,len,padstr) 当长度不够的时候向右填充len个padstr
SELECT RPAD('hi', 4, '?') as str;
2.2数学函数
#1.CEIL(X) 向上取整
SELECT CEIL(7.9) as num; #8
#2.FLOOR(X) 向下取整
SELECT FLOOR(7.9) as num; #7
#3.ROUND(X, D)四舍五入, 保留小数点后X位
SELECT ROUND(7.46, 1) as num;
#4.MOD(N,M) 取模
SELECT MOD(4, 3) as num;
#5.RAND() 获取0~1之间的随机数
SELECT RAND() as num;
SELECT RAND() * 1000 as num;
#6.TRUNCATE(X,D):截断,和round相似,只是取小数多少位
SELECT TRUNCATE(123.456, 2) as num;
2.3日期函数
#1.NOW() 返回当前日期+时间
SELECT NOW() as now;
#2.YEAR(date) 返回年份
SELECT YEAR('2022-09-06 21:50:25') as y;
#3.MONTH(date) 返回月份
SELECT MONTH('2022-09-06 21:50:25') as m;
#4.MONTHNAME(date) 以英文形式返回月份
SELECT MONTHNAME('2022-09-06 21:50:25') as m;
#5.DAY(date) 返回日期是多少号
SELECT DAY('2022-09-06 21:50:25') as d;
#6.HOUR(date/time) 返回小时
SELECT HOUR('2022-09-06 21:50:25') as h;
SELECT HOUR('21:50:25') as h;
#7.MINUTE(date/time) 返回分钟
SELECT MINUTE('2022-09-06 21:50:25') as M;
SELECT MINUTE('21:50:25') as M;
#8.SECOND(date/time) 返回秒数
SELECT SECOND('2022-09-06 21:50:25') as s;
SELECT SECOND('21:50:25') as s;
#9.CURTIME() 当前时间
SELECT CURTIME() as t;
#10.CURDATE() 当前日期
SELECT CURDATE() as d;
#11.DATEDIFF(expr1,expr2) 返回两个日期相差的天数
SELECT DATEDIFF('2022-01-31', '2022-01-01') as days; #30
SELECT DATEDIFF('2022-01-01', '2022-01-31') as days; #-30
#12.DATE_FORMAT(date,format) 将日期转化成自定义格式的字符串
SELECT DATE_FORMAT(NOW(), '%m-%d-%Y') as str; #09-06-2022
SELECT DATE_FORMAT(NOW(), '%d-%m-%Y') as str; #06-09-2022
#13.STR_TO_DATE(str,format) 将字符串转换成日期
SELECT STR_TO_DATE('06/09/2022', '%d/%m/%Y'); #2022-09-06
2.4流程控制函数case
语法1:类似于高级语言的switch
case 变量或者表达式或者字段
when 常量1 then 值1
when 常量2 then 值2
...
when 常量n then 值n
end
语法2:类似于高级语言中的if
case
when 条件1 then 值1
when 条件2 then 值2
...
when 条件n then 值n
end
e.g:
select job,
case job_id
when 'AA' then 'aa'
when 'BB' then 'bb'
when 'CC' then 'cc'
end as oo
from employees;
select job,
case
when salary > 2000 then 'A'
when salary > 4000 then 'B'
when salary > 6000 then 'C'
end as grade
from employees;