MySQL 数据类型、运算符和常用函数

MySQL 数据类型、运算符和常用函数

本篇概要：

• 1. MySQL 数据类型：
• • 1.1 数值类型；
  • 1.2 字符串类型；
  • 1.3 日期和时间类型；
• 2. MySQL 运算符；
• • 2.1 算数运算符；
  • 2.2 比较运算符；
  • 2.3 逻辑运算符和位运算符；
• 3. MySQL 常用函数；
• • 3.1 数学函数和聚合函数；
  • 3.2 字符串函数；
  • 3.3 日期和时间函数；
  • 3.4 数据加密函数；
  • 3.5 控制流函数；
  • 3.6 格式化函数和类型转换函数。

1. MySQL 数据类型：

• 为什么需要数据类型：保证数据的存储空间和效率

1.1 数值类型；

• 整数类型，主要有：TINTINT、SMALLINT、MEDIUMINT、INT、BIGINT
  • TINYINT，只占一个字节，存储范围 -128 到 127
  • SMALLINT，占两个字节，最大 32767
  • MEDIUMINT，占三个字节
  • INT，占四个字节
  • BIGINT，占八个字节
  • BOOL，对应 1 和 0

ALTER TABLE tabledata ADD mint INT(6);				// 显示的位数为 6 位，超过不会截断。
ALTER TABLE tabledata ADD mint INT(6) ZEROFILL; 	// 000123

• 浮点数类型，主要有：FLOAT、DOUBLE、DECIMAL
  • FLOAT：单精度，占四字节
  • DOUBLE：双精度，占八字节

ALTER TABLE tabledata ADD float4 FLOAT(5, 2);		// 数字不能超过 5 位，小数点后不能超过 2 位：123.25

1.2 字符串类型；

• 主要有：CHAR（定长字符串）、VARCHAR（可变长度字符串）、TEXT、BLOB
  • TEXT 不区分大小写，主要用来存储大块文本数据。
  • BLOB 区分大小写，只能存储二进制字符。超过范围将被截断。
  • 前面可以追加修饰词：TINYTEXT、LONGBLOB

CREATE TABLE table_string (CHARSTRING CHAR(10));	// 字符串最长 10 位，超过将被截断

• 比较复杂的字符串类型：ENUM（枚举类型），SET（SET 类型）
  • ENUM：有多个字符的选择，只能选择当中一个
  • SET：可以插入零个、一个或多个

ALTER TABLE table_string ADD GENDER ENUM('M', 'F');	// 初始情况没有数据，插入非法字符会插入空字符
ALTER TABLE table_string ADD SETCOL SET('A', 'B', 'CD', 'E');

1.3 日期和时间类型；

• 主要有：DATE、TIME、YEAR、DATETIME、TIMESTAMP
  • DATE：占三个字节，YYYY-MM-DD
  • TIME：HH:MM:SS
  • YEAR：YYYY
  • DATETIME：YYYY-MM-DD HH:MM:SS
  • TIMESTAMP：时间戳（1970 - 2037）

CREATE TABLE tabledate (birthday DATE);
INSERT INTO tabledate VALUES ('2018-04-01'),(20190701);ALTER TABLE tabledate ADD datetimecol DATETIME;		// 2018-08-01 00:00:00
ALTER TABLE tabledate ADD datetimecol DATETIME(2);	// 2018-08-01 00:00:00:00ALTER TABLE tabledate ADD time2 TIMESTAMP;			// 如果插入数据，自动插入当前时间

2. MySQL 运算符；

• MySQL 的运算符可以构建比较复杂的 SQL 语句，增加了 SQL 语句的灵活性。

2.1 算数运算符；

• 主要有：+ 、- 、* 、/ 、 %

SELECT 1.0 + 2;		// 算数加：2.0
SELECT 2 / 0;		// 算数除：NULL
SELECT 2 / NULL;	// 有 NULL 参与的算数运算都返回 NULL		
SELECT '20aa' * 5;	// 返回 100
SELECT 'aa20' * 5;	// 返回 0# 进阶：
SELECT sint - mormalint FROM tabledata；	// 返回算数减的数据

2.2 比较运算符；

• 主要有：= 、<> 、!= 、>= 、 < 、<=>、BETWEEN、IN、 IS NULL、LIKE、REGEXP、RLIKE

SELECT 1 = 2;						// 返回 0（相当于 false）
SELECT 1 = NULL; 					// 返回 NULL（有 NULL 参与就**无法进行运算**）
SELECT 1 <=> NULL; 					// 返回 0（对空类型安全的比较运算符）
SELECT 'manong' = 'MANONG	';		// 返回 1（不区分大小写，忽略空格）
SELECT BINARY 'manong' = 'MANONG';	// 返回 0（二进制字符比较）
SELECT 'a' > 'm';					// 返回 0
SELECT 'b' BETWEEN 'a' AND 'C';		// 返回 1
SELECT NOT 'd' BETWEEN 'a' AND 'C';	// 返回 1
SELECT 5 IN (2, 3, 5, 6);			// 返回 1
SELECT 'manong' LIKE 'ma%';			// 返回 1（LIKE 会扫描整张表，会有性能问题，不建议使用）
SELECT 'manong' LIKE '_nong%';		// 返回 0
SELECT 'manong' LIKE '__nong%';		// 返回 1

2.3 逻辑运算符和位运算符；

• 逻辑运算符主要有：NOT 、! 、AND 、&& 、 OR 、||、XOR

SELECT NOT 1;						// 返回 0
SELECT (4 > 3) AND (3 > 5);			// 返回 0（小括号表示运算优先级）
SELECT (4 > 3) OR (3 > 5);			// 返回 1
SELECT (4 > 3) XOR (3 > 5);			// 亦或，返回 1
SELECT (4 > 3) XOR (6 > 5);			// 返回 0

• 逻辑运算符主要有：& 、| 、^ 、~ 、 >> 、<<

SELECT 9 | 4;						// 返回 13（1001，0100 => 1101，1 + 4 + 8 = 13）
SELECT 9 >> 1;						// 返回 4（1001 >> 100 => 4）
SELECT 9 << 1;						// 返回 18（1001 << 10010 => 2 + 16 = 18）	 
SELECT 9 ^ 5;	 					// 返回 12（1001，0101 => 1100，4 + 8 = 12）

3. MySQL 常用函数；

3.1 数学函数和聚合函数；

# 数学函数
SELECT ABS(-5.8);					// 取绝对值：5.8
SELECT CEILING(5.8);				// 取比它大的整数（简写 CEIL()）：6
SELECT FLOOR(5.8);					// 取比它小的整数：5
SELECT GREATEST(1, 2, 0, 12);		// 取最大值：12
SELECT LEAST(1, 2, 0, 12);			// 取最小值：0
SELECT MOD(2, 3);					// 除余运算：2
SELECT PI();						// 取 π 的值：3.141593
SELECT RAND(10);					// 取随机种子：0.6570515219653505
SELECT ROUND(10.23679, 4);			// 四舍五入：10.2368
SELECT TRUNCATE(10.23679, 4);		// 截断：10.2368
SELECT SIGN(-2.4);					// 判断正负数：-1
SELECT COS(1);						// 三角函数 cos()：0.5403023058681398
SELECT POWER(2, 3);					// 次方（简写 POW()）：8
SELECT SQRT(4);						// 开方：2
SELECT LN(10);						// 取对数（同 LOG()）：2.302585092994046
SELECT BIN(10);						// 十进制转二进制：1010
SELECT OCT(10);						// 十进制转八进制：12
SELECT HEX(10);						// 十进制十六进制：A# 聚合函数
SELECT AVG(NUM) FROM table_test;	// 求一栏的平均值
SELECT SUM(NUM) FROM table_test;	// 求一栏的和
SELECT MIN(NUM) FROM table_test;	// 求一栏的最小值
SELECT MAX(NUM) FROM table_test;	// 求一栏的最大值
SELECT COUNT(NUM) FROM table_test;	// 求一栏的个数

3.2 字符串函数；

SELECT LENGTH('hello');						// 求字符串长度：5
SELECT LCASE('HELLO');						// 取字符串小写（同 LOWER()）：hello
SELECT UCASE('hello');						// 取字符串大写（同 UPPER()）：HELLO
SELECT STRCMP('hello', 'yes');				// 比较两个字符串首字母大小：-1
SELECT POSITION('yes' IN 'yessir');			// 字符串查找：1
SELECT REPLACE('yes', 'y', 'b');			// 字符串替换：bes
SELECT INSERT('yes sir!', 2, 3, 'ah');		// 插入：yah sir!
SELECT CONCAT('hello ', 'world');			// 合并：hello world
SELECT CONCAT_WS(';', 'hello', 'world');	// 插入间隔符合并：hello;world
SELECT LEFT('helloworld', 3);				// 取最左边 n 个字符：hel
SELECT RIGHT('helloworld', 3);				// 取最右边 n 个字符：rld
SELECT LPAD('hello', 15, 'x');				// 最左边填充字符（位数不够不填充）：xxxxxhelloworld
SELECT RPAD('hello', 15, 'x');				// 最右边填充字符（位数不够不填充）：helloworldxxxxx
SELECT LTRIM('   helloworld');				// 最左边去空格：helloworld
SELECT RTRIM('helloworld   ');				// 最右边去空格：helloworld
SELECT TRIM('     helloworld   ');			// 两边去空格：helloworld
SELECT SUBSTRING('market', 2, 3);			// 取字符串：ark
SELECT ASCII('a');							// 取 ASCII 码：97

3.3 日期和时间函数；

SELECT NOW();								// 取现在的时间：2019-07-13 14:29:08
SELECT CURTIME();							// 取现在的时分秒：14:29:51
SELECT CURDATE();							// 取现在的年月日：2019-07-13
SELECT YEAR('2019-07-13');					// 取年份：2019
SELECT MONTH('2019-07-13');					// 取月份：7
SELECT MONTHNAME('2019-07-13');				// 取月份的英文名称：July
SELECT DAYOFYEAR('2019-07-13');				// 一年中的的第几天：194
SELECT DAYOFWEEK('2019-07-13');				// 一周中的第几天：7
SELECT DAYNAME('2019-07-13');				// 周几的英文名称：Saturday
SELECT WEEK('2019-07-13');					// 一年中的第几周：27
SELECT HOUR('14:31:12');					// 取小时：14
SELECT MINUTE('14:31:12');					// 取分钟：31
SELECT SECOND('14:31:12');					// 取秒：12
SELECT DATE_ADD(NOW(), INTERVAL 3 MONTH);	// 添加时间：2019-10-13 14:33:24    
SELECT DATE_SUB(NOW(), INTERVAL 3 MONTH);	// 减去时间：2019-04-13 14:33:56   

3.4 数据加密函数；

SELECT PASSWORD('secret');
// 返回： *14E65567ABDB5135D0CFD9A70B3032C179A49EE7
// 通常用于 MySQL 数据库的安全系统，而且加密过程不可逆，大小写区分
// 算法和 UNIX 密码加密不一样
SELECT ENCRYPT('secret', 'key');		// 返回：keAGULNA5/Jds（不可逆）SELECT ENCODE('secret', 'key');
SELECT DECODE('secret', 'key');			// 可逆，加解密程度不高SELECT AES_ENCRYPT('secret', 'key');
SELECT AES_DECRYPT('secret', 'key');SELECT MD5('secret');					// 返回：5ebe2294ecd0e0f08eab7690d2a6ee69
SELECT SHA('secret');					// 返回：e5e9fa1ba31ecd1ae84f75caaa474f3a663f05f4

3.5 控制流函数；

SELECT IF(1 < 10, 2, 3);					// 返回：2
SELECT IFNULL(2, 3);						// 返回：2（空返回第二个参数，不空返回第一个参数）
SELECT IFNULL(NULL, 3);						// 返回：3
SELECT NULLIF(2, 3);						// 返回：2（相等返回 NULL，否则返回第一个参数）SELECT CASE WHEN 1 then 199 ELSE 10 
end;
// 返回 199
SELECT CASE WHEN 0 THEN 199 WHEN 1 THEN 299ELSE 10 
end;
// 返回 299
SELECTCASE 'green'WHEN 'red' THEN 100WHEN 'green' THEN 200ELSE 300
END;
// 返回 200

3.6 格式化函数和类型转换函数。

# 格式化函数
SELECT DATE_FORMAT(NOW(), '%W, %D %M %Y %r');	// 返回：Saturday, 13th July 2019 05:30:10 PM 
SELECT TIME_FORMAT('100:21:12', '%h:%i %p');	// 返回：04:21 AM SELECT INET_ATON('192.168.2.10');		// 返回：3232236042
SELECT INET_NTOA('3232236042');			// 返回：192.168.2.10# 类型转换
SELECT 1 + CAST('99' AS SIGNED);		
// 返回 100
// AS 后面加类型： BINARY、CHAR、DATE、TIME、DATETIME、SIGNED、UNSIGNED
SELECT 'f' = BINARY 'F', 'f' = CAST('F' AS BINARY);
// 返回	+------------------+---------------------------+
//		| 'f' = BINARY 'F' | 'f' = CAST('F' AS BINARY) |
//		+------------------+---------------------------+
//		|                0 |                         0 |
//		+------------------+---------------------------+
SELECT 'f' = 'F', 'f' = CAST('F' AS BINARY);
// 返回	+-----------+---------------------------+
//		| 'f' = 'F' | 'f' = CAST('F' AS BINARY) |
//		+-----------+---------------------------+
//		|         1 |                         0 |
//		+-----------+---------------------------+SELECT CONVERT('23', SIGNED);		// 返回 23
SELECT CONVERT('hello' using utf8);	// 返回 hello