MYSQL(三)

mysql学习笔记整理-数据类型

数据类型

字符串

数据类型

下面是 mysql 支持的字符串类型

类型	大小	用途
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 字节	极大文本数据

char 类型是定长类型，比如定义了 20 长度的char类型，只存一个字符也占用 20 个长度，char 好处是处理速度快，缺点是空间占用大，把手机号、邮箱、密码等长度相对固定的设置为 char 类型是不错的选择。
varchar 类型与 char 相反，点用空间受内容影响，可以把文章标题、介绍等设置为 varchar 类型更合适。

字符串

字符串分二进制与非二进制类型，二进制用于储存图片、声音等文件，非二进制用于储存文本数据。
非二进制文本受字符集和校对规则影响。

字符集

字符集(Character set)是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同。常用的字符集有 GBK、BIG5、UTF8。
UTF8 字符包含文字内容更广，如韩文、日文、德文兼容度更高，也是推荐使用的字符集。
下面是查看服务器支持的字符集

SHOW CHARACTER SET;

表不设置字符集继承数据库，字段不设置字符集继承表的

校对规则

是在字符集内用于字符比较和排序的一套规则，以_ci 结束的为大小写不敏感、_bin 结束的为区分大小写。
下面是查看系统支持的校对规则

show COLLATION;

当使用不区分大小写的校对规则时 A 与 a 是相同的，否则则不相同，这会影响到排序与比对。
如果使用utf8_bin 校对规则时，下面的查询将匹配不到大写的PHP

select * from class WHERE cname = 'php';

修改表校对规则，对表的原字段将不影响，只对新增字段影响。

常用函数

大小写转换

SELECT UPPER(cname) as cname,LOWER(description) as `desc` from class;

Left&right
left 与 right 函数用于取左或右指定数量的字符，下面是取班级介绍前 8 个字符并用… 连接。

SELECT CONCAT(LEFT(description,8),'...') FROM class;

mid
从中间取字符串，参数二为起始，参数三为取的字符数量。下面是获取从第二个字符取两个字符值为hp的。

select *  from class where mid(cname,2,2) = 'hp';

substring
从指定位置开始向右取所有字符串，下面是获取从第二个位置开始的字符值为hp的记录。

select *  from class where SUBSTRING(cname,2) = 'hp';

char_length
获取字符串数量

concat
连接字符串使用

SELECT concat('编号:',id) as id,concat('班级:',cname) as name FROM class;

将所有班级前加上后盾人

UPDATE class SET cname = CONCAT('后盾人:',cname);

截取班级介绍，超过 8 个字符的后面连接…

SELECT
if(CHAR_LENGTH(description)>8,CONCAT(LEFT(description,8),'...'),LEFT(description,8)) as name
FROM class;

正则表达式

Mysql 支持正则表达式操作，可用于处理复杂的匹配操作。
查找第二个字符为h的字符串

SELECT * FROM class WHERE cname REGEXP '^.h';

查找班级名称中包含php 或 mysql的记录

SELECT * FROM class WHERE cname REGEXP 'php|mysql';

所有介绍中包含 php 与 mysql 的课程名前加上后盾人

update class set cname = REPLACE(cname,cname,concat('后盾人：',cname))
where description REGEXP  'php|mysql';

LIKE

在 LIKE 表达式中 % 用于匹配任意多个字符，_ 用于匹配一个字符。
查找第二个字符为 h 的班级。

SELECT *  FROM class WHERE cname LIKE '_h%';

数值类型

整型

MySQL 数据类型	范围（有符号）	范围（无符号）
tinyint(m)	1 个字节 范围(-128~127)	(0，255)
smallint(m)	2 个字节 范围(-32768~32767)	(0，65 535)
mediumint(m)	3 个字节 范围(-8388608~8388607)	(0，16 777 215)
int(m)	4 个字节 范围(-2147483648~2147483647)	(0，4 294 967 295)
bigint(m)	8 个字节 范围(+-9.22*10 的 18 次方)	(0，18 446 744 073 709 551 615)

• 取值范围如果加了 unsigned，则最大值翻倍，如 tinyint unsigned 的取值范围为(0~256)。
• m 的含义不是允许字段的长度，而是显示长度，在为字段设置 zerofill 时有效。

添加有前导零的字段

ALTER TABLE class ADD stu_count smallint(6) ZEROFILL default null;

在命令行查看（有些 GUI 软件不显示前导零)，结果如下：

+----+-------+--------------------------------------------+-----------+
| id | cname | description                                | stu_count |
+----+-------+--------------------------------------------+-----------+
|  4 | Mysql | 数据库                                     |    000001 |
|  5 | PHP   | 后盾人教你使用PHP快速开发网站              |      NULL |
+----+-------+--------------------------------------------+-----------+

浮点型

类型	大小	范围（有符号）	范围（无符号）
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<65 是总个数，d<30	依赖于 M 和 D 的值	依赖于 M 和 D 的值

下面是检测浮点数精度的示例

alter table class add e FLOAT(10,2);
update class set e = 12345678.66 where id=11;

查看结果时会发布浮点数结果不精确。

• float：2^23 = 8388608，一共七位，这意味着最多能有 7 位有效数字，但绝对能保证的为 6 位，即 float 的精度为 6~7 位有效数字
• double：2^52 = 4503599627370496，一共 16 位，double 的精度为 15~16 位
• 浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值
• decimal(m,d) 参数 m<65 是总个数，d<30 且 d<m 是小数位
• 对货币等对精度敏感的数据，应该用定点数 decimal 存储

ENUM/SET

ENUM

ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。换个枚举最大可以有 65535 个成员值

ALTER TABLE stu ADD sex ENUM('男','女') DEFAULT NULL;

可以使用索引或值添加 enum 数据

INSERT INTO stu (sname,class_id,sex) VALUES('李岗',1,'男');
INSERT INTO stu (sname,class_id,sex) VALUES('李玉',1,2);

可以使用值与索引检索 ENUM

SELECT * from stu WHERE sex='女';
SELECT * from stu WHERE sex=2;

SET

SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。一个 SET 类型最多可以包含 64 项元素。
使用 SET 类型添加文章属性字段

ALTER TABLE article ADD flag SET('推荐','置顶','图文','热门');

添加数据

INSERT INTO article (title,status,flag)VALUES('后盾人',1,'图文,推荐,置顶');

使用 find_in_set 查找数据

SELECT * FROM article WHERE find_in_set('图文',flag);

使用like 查找数据

SELECT * FROM article WHERE flag like '%置顶%'

二进制比较
可以使用二进制方式对 SET 类型进行模糊筛选。

SET 成员	十进制值	二进制值
推荐	1	0001
置顶	2	0010
图文	4	0100
热门	8	1000

获取包含图文与推荐的文章

SELECT * FROM article WHERE flag & 5;