1. MySQL 数据类型:
1.1 数值类型;
- 整数类型,主要有:
TINTINT
、SMALLINT
、MEDIUMINT
、INT
、BIGINT
TINYINT
,只占一个字节,存储范围 -128 到 127SMALLINT
,占两个字节,最大 32767MEDIUMINT
,占三个字节INT
,占四个字节BIGINT
,占八个字节BOOL
,对应 1 和 0
ALTER TABLE tabledata ADD mint INT(6);
ALTER TABLE tabledata ADD mint INT(6) ZEROFILL;
- 浮点数类型,主要有:
FLOAT
、DOUBLE
、DECIMAL
FLOAT
:单精度,占四字节DOUBLE
:双精度,占八字节
ALTER TABLE tabledata ADD float4 FLOAT(5, 2);
1.2 字符串类型;
- 主要有:
CHAR
(定长字符串)、VARCHAR
(可变长度字符串)、TEXT
、BLOB
TEXT
不区分大小写,主要用来存储大块文本数据。BLOB
区分大小写,只能存储二进制字符。超过范围将被截断。- 前面可以追加修饰词:
TINYTEXT
、LONGBLOB
CREATE TABLE table_string (CHARSTRING CHAR(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;
ALTER TABLE tabledate ADD datetimecol DATETIME(2);
ALTER TABLE tabledate ADD time2 TIMESTAMP;
2. MySQL 运算符;
- MySQL 的运算符可以构建比较复杂的 SQL 语句,增加了 SQL 语句的灵活性。
2.1 算数运算符;
SELECT 1.0 + 2;
SELECT 2 / 0;
SELECT 2 / NULL;
SELECT '20aa' * 5;
SELECT 'aa20' * 5;
SELECT sint - mormalint FROM tabledata;
2.2 比较运算符;
- 主要有:
=
、<>
、!=
、>=
、 <
、<=>
、BETWEEN
、IN
、 IS NULL
、LIKE
、REGEXP
、RLIKE
SELECT 1 = 2;
SELECT 1 = NULL;
SELECT 1 <=> NULL;
SELECT 'manong' = 'MANONG ';
SELECT BINARY 'manong' = 'MANONG';
SELECT 'a' > 'm';
SELECT 'b' BETWEEN 'a' AND 'C';
SELECT NOT 'd' BETWEEN 'a' AND 'C';
SELECT 5 IN (2, 3, 5, 6);
SELECT 'manong' LIKE 'ma%';
SELECT 'manong' LIKE '_nong%';
SELECT 'manong' LIKE '__nong%';
2.3 逻辑运算符和位运算符;
- 逻辑运算符主要有:
NOT
、!
、AND
、&&
、 OR
、||
、XOR
SELECT NOT 1;
SELECT (4 > 3) AND (3 > 5);
SELECT (4 > 3) OR (3 > 5);
SELECT (4 > 3) XOR (3 > 5);
SELECT (4 > 3) XOR (6 > 5);
- 逻辑运算符主要有:
&
、|
、^
、~
、 >>
、<<
SELECT 9 | 4;
SELECT 9 >> 1;
SELECT 9 << 1;
SELECT 9 ^ 5;
3. MySQL 常用函数;
3.1 数学函数和聚合函数;
SELECT ABS(-5.8);
SELECT CEILING(5.8);
SELECT FLOOR(5.8);
SELECT GREATEST(1, 2, 0, 12);
SELECT LEAST(1, 2, 0, 12);
SELECT MOD(2, 3);
SELECT PI();
SELECT RAND(10);
SELECT ROUND(10.23679, 4);
SELECT TRUNCATE(10.23679, 4);
SELECT SIGN(-2.4);
SELECT COS(1);
SELECT POWER(2, 3);
SELECT SQRT(4);
SELECT LN(10);
SELECT BIN(10);
SELECT OCT(10);
SELECT HEX(10);
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');
SELECT LCASE('HELLO');
SELECT UCASE('hello');
SELECT STRCMP('hello', 'yes');
SELECT POSITION('yes' IN 'yessir');
SELECT REPLACE('yes', 'y', 'b');
SELECT INSERT('yes sir!', 2, 3, 'ah');
SELECT CONCAT('hello ', 'world');
SELECT CONCAT_WS(';', 'hello', 'world');
SELECT LEFT('helloworld', 3);
SELECT RIGHT('helloworld', 3);
SELECT LPAD('hello', 15, 'x');
SELECT RPAD('hello', 15, 'x');
SELECT LTRIM(' helloworld');
SELECT RTRIM('helloworld ');
SELECT TRIM(' helloworld ');
SELECT SUBSTRING('market', 2, 3);
SELECT ASCII('a');
3.3 日期和时间函数;
SELECT NOW();
SELECT CURTIME();
SELECT CURDATE();
SELECT YEAR('2019-07-13');
SELECT MONTH('2019-07-13');
SELECT MONTHNAME('2019-07-13');
SELECT DAYOFYEAR('2019-07-13');
SELECT DAYOFWEEK('2019-07-13');
SELECT DAYNAME('2019-07-13');
SELECT WEEK('2019-07-13');
SELECT HOUR('14:31:12');
SELECT MINUTE('14:31:12');
SELECT SECOND('14:31:12');
SELECT DATE_ADD(NOW(), INTERVAL 3 MONTH);
SELECT DATE_SUB(NOW(), INTERVAL 3 MONTH);
3.4 数据加密函数;
SELECT PASSWORD('secret');
SELECT ENCRYPT('secret', 'key');
SELECT ENCODE('secret', 'key');
SELECT DECODE('secret', 'key');
SELECT AES_ENCRYPT('secret', 'key');
SELECT AES_DECRYPT('secret', 'key');
SELECT MD5('secret');
SELECT SHA('secret');
3.5 控制流函数;
SELECT IF(1 < 10, 2, 3);
SELECT IFNULL(2, 3);
SELECT IFNULL(NULL, 3);
SELECT NULLIF(2, 3);
SELECT
CASE
WHEN 1 then 199
ELSE 10
end;
SELECT
CASE
WHEN 0 THEN 199
WHEN 1 THEN 299
ELSE 10
end;
SELECT
CASE 'green'
WHEN 'red' THEN 100
WHEN 'green' THEN 200
ELSE 300
END;
3.6 格式化函数和类型转换函数。
SELECT DATE_FORMAT(NOW(), '%W, %D %M %Y %r');
SELECT TIME_FORMAT('100:21:12', '%h:%i %p');
SELECT INET_ATON('192.168.2.10');
SELECT INET_NTOA('3232236042');
SELECT 1 + CAST('99' AS SIGNED);
SELECT 'f' = BINARY 'F', 'f' = CAST('F' AS BINARY);
SELECT 'f' = 'F', 'f' = CAST('F' AS BINARY);
SELECT CONVERT('23', SIGNED);
SELECT CONVERT('hello' using utf8);