Mysql之数据类型
1. 数值类型
|
类型 |
大小 |
范围（有符号） |
范围（无符号） |
用途 |
|
TINYINT |
1 字节 |
(-128，127)（负号占了一数字位） |
(0，255) |
小整数值 |
|
SMALLINT |
2 字节 |
(-32 768，32 767) |
(0，65 535) |
大整数值 |
|
MEDIUMINT |
3 字节 |
(-8 388 608，8 388 607) |
(0，16 777 215) |
大整数值 |
|
INT或INTEGER |
4 字节 |
(-2 147 483 648，2 147 483 647) |
(0，4 294 967 295) |
大整数值 |
|
BIGINT |
8 字节 |
(-9 233 372 036 854 775 808，9 223 372 036 854 775 807) |
(0，18 446 744 073 709 551 615) |
极大整数值 |
|
FLOAT |
4 字节 |
(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) |
0，(1.175 494 351 E-38，3.402 823 466 E+38) |
单精度 |
|
DOUBLE |
8 字节 |
(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) |
0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) |
双精度 |
|
DECIMAL |
对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 |
依赖于M和D的值 |
依赖于M和D的值 |
小数值 |
整数型结论：整型默认使用的是有符号的。 整型是有范围的。 整型的宽度指的是显示宽度，不是存储宽度。显示宽度只是指明mysql最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大鱼显示宽度的值，只要该值不超过该类型的取值范围，数值依然可以插入，并且能够显示出来。
浮点数类型(默认会按照实际的精度) 定点数类型(默认精度为(10,0)） 货币，科学数据使用DECIMAL类型比较好
2. 字符类型
|
类型 |
大小 |
用途 |
|
CHAR |
0-255字节 |
定长字符串 |
|
VARCHAR |
0-65535 字节 |
变长字符串 |
|
TINYBLOB |
0-255字节 |
不超过 255 个字符的二进制字符串 |
|
TINYTEXT |
0-255字节 |
短文本字符串 |
|
BLOB |
0-65 535字节 |
二进制形式的长文本数据 |
|
TEXT |
0-65 535字节 |
长文本数据 |
|
MEDIUMBLOB |
0-16 777 215字节 |
二进制形式的中等长度文本数据 |
|
MEDIUMTEXT |
0-16 777 215字节 |
中等长度文本数据 |
|
LONGBLOB |
0-4 294 967 295字节 |
二进制形式的极大文本数据 |
|
LONGTEXT |
0-4 294 967 295字节 |
极大文本数据 |
1. char和varchar类型的存储区别
create table temp1 (ch char(4),vch varchar(4));
insert into temp1 values('ab ','ab ');
select concat('(',ch,')'),concat('(',vch,')') from temp1;
char类型把后面的空格删除了，varchar类型后面的空格还保留。
结论：char(5) 只能存5个字符。简单粗暴，浪费空间，存取速度快。
varchar(5) 节省空间，存取速度慢
1-2bytes+a
字符类型的宽度是存取的值的宽度。
3. 时间类型
|
类型 |
大小 |
范围 |
格式 |
用途 |
|
DATE |
3 |
1000-01-01/9999-12-31 |
YYYY-MM-DD |
日期值 |
|
TIME |
3 |
'-838:59:59'/'838:59:59' |
HH:MM:SS |
时间值或持续时间 |
|
YEAR |
1 |
1901/2155 |
YYYY |
年份值 |
|
DATETIME |
8 |
1000-01-01 00:00:00/9999-12-31 23:59:59 |
YYYY-MM-DD HH:MM:SS |
混合日期和时间值 |
|
TIMESTAMP |
4 |
1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 |
YYYYMMDD HHMMSS |
混合日期和时间值，时间戳 |
1. 如何分隔时间类型？
老师的博客地址：http://www.cnblogs.com/yuanchenqi/articles/7250680.html
CURDATE()或CURRENT_DATE() 返回当前的日期 CURTIME()或CURRENT_TIME() 返回当前的时间 DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH); DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值 DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH); DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7) DAYOFMONTH(date) 返回date是一个月的第几天(1~31) DAYOFYEAR(date) 返回date是一年的第几天(1~366) DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE); FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts HOUR(time) 返回time的小时值(0~23) MINUTE(time) 返回time的分钟值(0~59) MONTH(date) 返回date的月份值(1~12) MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE); NOW() 返回当前的日期和时间 QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE); WEEK(date) 返回日期date为一年中第几周(0~53) YEAR(date) 返回日期date的年份(1000~9999)
select DATE_FORMAT(d,"%Y/%m") from 表
结果就是：2018/03
4. 枚举类型
enum('male','female') 单选
set('play','read') 多选
5. 如何选择数据类型
1. 整数和浮点数
根据用户需求，选择对应的整数类型；小数类型为float类型，如果要求精度高，优先选择DOUBLE。
2. 浮点数和定点数
对于精度要求更高的话，选择DECIMAL类型。如果进行数值比较，还是选择DECIMAL类型。
3. 日期与时间类型
只记录年份，使用YEAR类型
只记录时间，使用TIME类型
记录日期和时间，使用TIMESTAMP或者DATETIME。TIMESTAMP比DATETIME取值范围小。
4. CHAR与VARCHAR
CHAR是固定长度字符，VARCHAR是可变长度字符
CHAR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部空格
对于MyISAM存储引擎，最好使用CHAR，可以使整个表静态化，从而使数据检索更快
对于InnoDB存储引擎，使用VARCHAR，比较节省空间，对磁盘I/O和数据存储总量比较好。
5. ENUM和SET
ENUM只能取单值。
SET可取多值。
6. BLOB和TEXT
BLOB是二进制字符串，主要存储图片，音频信息
TEXT是非二进制字符串，只能存储纯文本文件
7. 各表使用一致的数据类型
8. 选择更短的数据类型
9. 简单的数据类型能够进行更快的处理
10. 尽可能避免NULL值
6. 运算符
1. 算术运算符
1. 例子：
mysql> select num+2,num-2,num*2,num/2,num%2 from temp2; +-------+-------+-------+---------+-------+ | num+2 | num-2 | num*2 | num/2 | num%2 | +-------+-------+-------+---------+-------+ | 52 | 48 | 100 | 25.0000 | 0 | | 102 | 98 | 200 | 50.0000 | 0 | +-------+-------+-------+---------+-------+ 2 rows in set (0.00 sec)
2. 比较运算符
1. 等于运算符(=)
用来判断数字，字符串和表达式是否相等。如果相等，则返回值为1，否则返回值为0.
例子： select 1=1,1='1','a'='b','b'='b',(1+5)=(2+4);
+-----+-------+---------+---------+-------------+
| 1=1 | 1='1' | 'a'='b' | 'b'='b' | (1+5)=(2+4) |
+-----+-------+---------+---------+-------------+
| 1 | 1 | 0 | 1 | 1 |
+-----+-------+---------+---------+-------------+
注意： 字符'1'，进行判断时候，mysql把它转换成1
字符串必须使用引号，否则报错。 ERROR 1054 (42S22): Unknown column 'b' in 'field list'
若有一个或者两个参数为NULL，则比较运算的结果为NULL
2. 安全等于运算符(<=>)
此操作符与=号一样，不过<=>可以用来判断NULL值。在两个操作数均为NULL，其返回值为1而不为NULL;当一个操作数为NULL时，其返回值为0而不为NULL
例子： select 1=1,1='1','a'='b','b'='b',(1+5)=(2+4),NULL<=>NULL,NULL<=>1;
+-----+-------+---------+---------+-------------+-------------+----------+
| 1=1 | 1='1' | 'a'='b' | 'b'='b' | (1+5)=(2+4) | NULL<=>NULL | NULL<=>1 |
+-----+-------+---------+---------+-------------+-------------+----------+
| 1 | 1 | 0 | 1 | 1 | 1 | 0 |
+-----+-------+---------+---------+-------------+-------------+----------+
注意： 当比较数字和字符串，表达式的时候，那么它与=的结果一样，但是比较NULL的时候，就不一样了。
3. 不等于运算符(<>或者!=)
此操作符用来判断数字，字符，表达式是否不相等。如果不相等，返回值为1；否则，返回值为0.不能判断NULL
例子： select 1!=1,1!='1','a'!='b','b'!='b',(1+5)!=(3+4),NULL!=NULL,NULL<>1;
+------+--------+----------+----------+--------------+------------+---------+
| 1!=1 | 1!='1' | 'a'!='b' | 'b'!='b' | (1+5)!=(3+4) | NULL!=NULL | NULL<>1 |
+------+--------+----------+----------+--------------+------------+---------+
| 0 | 0 | 1 | 0 | 1 | NULL | NULL |
+------+--------+----------+----------+--------------+------------+---------+
4. 小于或等于运算符(<=)
mysql> select 1<=1,1<=0,'a'<='b','b'<='a';
+------+------+----------+----------+
| 1<=1 | 1<=0 | 'a'<='b' | 'b'<='a' |
+------+------+----------+----------+
| 1 | 0 | 1 | 0 |
+------+------+----------+----------+
注意：数字比大小，字符比顺序（按照英文字母顺序）
比较NULL，返回NULL
5. 小于运算符(<)
mysql> select 1<1,1<0,'a'<'b','b'<'a';
+-----+-----+---------+---------+
| 1<1 | 1<0 | 'a'<'b' | 'b'<'a' |
+-----+-----+---------+---------+
| 0 | 0 | 1 | 0 |
+-----+-----+---------+---------+
注意：比较NULL,返回NULL
6. 大于或等于运算符(>=)
mysql> select 1>=1,1>=0,'a'>='b','b'>='a',NULL>=NULL;
+------+------+----------+----------+------------+
| 1>=1 | 1>=0 | 'a'>='b' | 'b'>='a' | NULL>=NULL |
+------+------+----------+----------+------------+
| 1 | 1 | 0 | 1 | NULL |
+------+------+----------+----------+------------+
7. 大于运算符(>)
mysql> select 1>1,1>0,'a'>'b','b'>'a',NULL>NULL;
+-----+-----+---------+---------+-----------+
| 1>1 | 1>0 | 'a'>'b' | 'b'>'a' | NULL>NULL |
+-----+-----+---------+---------+-----------+
| 0 | 1 | 0 | 1 | NULL |
+-----+-----+---------+---------+-----------+
8. is NULL, isnull, is not null
mysql> select null is NULL,isnull(null),isnull(10),10 is not null,null is not null;
+--------------+--------------+------------+----------------+------------------+
| null is NULL | isnull(null) | isnull(10) | 10 is not null | null is not null |
+--------------+--------------+------------+----------------+------------------+
| 1 | 1 | 0 | 1 | 0 |
+--------------+--------------+------------+----------------+------------------+
9. BETWEEN AND 区间判断
mysql> select 4 between 4 and 10,10 between 4 and 10;
+--------------------+---------------------+
| 4 between 4 and 10 | 10 between 4 and 10 |
+--------------------+---------------------+
| 1 | 1 |
+--------------------+---------------------+
10. LEAST 取最小值
取最小值的运算符，如果值有一个为NULL，则返回值为NULL
mysql> select least(38,383,38385696,384522787),least('a','b','d','y','ww'),least(null,'a','1');
+----------------------------------+-----------------------------+---------------------+
| least(38,383,38385696,384522787) | least('a','b','d','y','ww') | least(null,'a','1') |
+----------------------------------+-----------------------------+---------------------+
| 38 | a | NULL |
+----------------------------------+-----------------------------+---------------------+
11. GREATEST 取最大值
取最大值的运算符，如果值有一个为NULL，则返回值为NULL
mysql> select greatest(38,383,38385696,384522787),greatest('a','b','d','y','ww'),greatest(null,'a','1');
+-------------------------------------+--------------------------------+------------------------+
| greatest(38,383,38385696,384522787) | greatest('a','b','d','y','ww') | greatest(null,'a','1') |
+-------------------------------------+--------------------------------+------------------------+
| 384522787 | y | NULL |
+-------------------------------------+--------------------------------+------------------------+
12. IN,NOT IN
mysql> select 5 in (1, 2, 4, 6), 'ab' in ('bc','ab','sdsfsdf'), 8 not in (1, 22,33);
+-------------------+-------------------------------+---------------------+
| 5 in (1, 2, 4, 6) | 'ab' in ('bc','ab','sdsfsdf') | 8 not in (1, 22,33) |
+-------------------+-------------------------------+---------------------+
| 0 | 1 | 1 |
+-------------------+-------------------------------+---------------------+
13. LIKE
用来匹配字符串
LIKE % 匹配任何字符
LIKE _ 匹配单一字符
mysql> select 'a' like 'b%a';
+----------------+
| 'a' like 'b%a' |
+----------------+
| 0 |
+----------------+
1 row in set (0.00 sec)
mysql> select 'a' like 'a%';
+---------------+
| 'a' like 'a%' |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql> select 'a' like 'a_';
+---------------+
| 'a' like 'a_' |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
14. REGEXP
^ 匹配以某个字符开头的字符串
$ 匹配以某个字符结尾的字符串
. 匹配任何一个单一字符
[...] 匹配在括号内的任何字符
* 匹配任何数量的任何字符
mysql> select 'a' regexp '^a','ab' regexp 'b$','abcefgg' regexp '[abc]','sdffsdf' regexp 'a.c';
+-----------------+------------------+--------------------------+------------------------+
| 'a' regexp '^a' | 'ab' regexp 'b$' | 'abcefgg' regexp '[abc]' | 'sdffsdf' regexp 'a.c' |
+-----------------+------------------+--------------------------+------------------------+
| 1 | 1 | 1 | 0 |
+-----------------+------------------+--------------------------+------------------------+
3. 逻辑运算符
1. NOT或者!
表示当操作数为0时，返回值为1，不为0时，返回值为0，为null时，返回值为null.
mysql> select not 10,not 0,not null;
+--------+-------+----------+
| not 10 | not 0 | not null |
+--------+-------+----------+
| 0 | 1 | NULL |
+--------+-------+----------+
2. AND或者&&
表示当所有操作数为非零值并且不为null时，计算所得结果为1；当一个或多个操作数为0时，所得结果为0，其余情况返回值为null
mysql> select 1 and 5,1 and 0,1 and null;
+---------+---------+------------+
| 1 and 5 | 1 and 0 | 1 and null |
+---------+---------+------------+
| 1 | 0 | NULL |
+---------+---------+------------+
3. OR或者||
表示当两个操作数均为非NULL值，且任意一个操作数为非零时，返回值1，否则为0；当有一个为NULL,且另一个操作数为非零值时，则返回1，否则为NULL;当两个操作数均为NULL，结果为NULL
mysql> select 1 or 2 ,1 or 0, null or 1,null or 0 ,null or null ,0 or 0;
+--------+--------+-----------+-----------+--------------+--------+
| 1 or 2 | 1 or 0 | null or 1 | null or 0 | null or null | 0 or 0 |
+--------+--------+-----------+-----------+--------------+--------+
| 1 | 1 | 1 | NULL | NULL | 0 |
+--------+--------+-----------+-----------+--------------+--------+
4. XOR
表示当任意一个操作数为NULL时，返回值为NULL;对于非NULL的操作数，如果两个操作数都是非0值或者都是0值，则返回为0；如果一个为0，另外一个非0，则返回1.
mysql> select null xor null,1 xor 1 ,0 xor 0,1 xor 0;
+---------------+---------+---------+---------+
| null xor null | 1 xor 1 | 0 xor 0 | 1 xor 0 |
+---------------+---------+---------+---------+
| NULL | 0 | 0 | 1 |
+---------------+---------+---------+---------+
a XOR b 等同于（a AND (NOT b)）或者(NOT a) AND b
4. 位操作运算符
5. 运算符的优先级
浙公网安备 33010602011771号