Mysql中的基本数据类型以及查询

数据类型

数据类型（data_type）是指系统中所允许的数据的类型。

MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。

数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。

例如，列中存储的为数字，则相应的数据类型应该为数值类型。 如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。

MySQL 的数据类型有大概可以分为 5 种：整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。

数值类型

整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型包括 FLOAT 和 DOUBLE，定点数类型为 DECIMAL。

数值类型

类型名称	说明	存储需求
TINYINT	-128〜127	1个字节
SMALLINT	-32768〜32767	2个宇节
MEDIUMINT	-8388608〜8388607	3个字节
INT (INTEGHR)	-2147483648〜2147483647	4个字节
BIGINT	-9223372036854775808〜9223372036854775807	8个字节
FLOAT	-3.402823466E+38～-1.175494351E-38	4 个字节
DOUBLE	-1.7976931348623157E+308～-2.2250738585072014E-308	8 个字节
DECIMAL (M, D)	DECIMAL 如果不指定精度，默认为（10，0）。	M+2 个字节

特别的是 DECIMAL (M, D)M代表整数的位数 D代表小数的位数。

日期时间类型

包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

日期时间类型

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901 ~ 2155	1 个字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 个字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12- 31 23:59:59	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 个字节

特别的是TIMESTAMP类型 字节数少但是范围有限

字符串类型

包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT等。括号中的 M 表示可以为其指定长度。

字符串类型

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8
TEXT	小的非二进制字符串	L+2字节，在此，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32

特别的是M可以指定CHAR、VARCHAR的长度，但是范围有限。当不确定长度时应该使用TEXT、MEDIUMTEXT、LONGTEXT 易建立检索。

CHAR 和 VARCHAR 的区别如下:

CHAR 是固定长度字符，VARCHAR 是可变长度字符。

CHAR 会自动删除插入数据的尾部空格，VARCHAR 不会删除尾部空格。

CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用 VARCHAR类型来实现。

存储引擎对于选择 CHAR 和 VARCHAR 的影响:

对于 MyISAM 存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。

对于InnoDB存储引擎，最好使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存 储，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。

二进制类型

包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。括号中的 M 表示可以为其指定长度。

二进制类型

类型名称	说明	存储需求
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^32

MySQL查询数据

在 MySQL 中，可以使用 SELECT 语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要 的操作。

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

其中，各条子句的含义如下：

{*|} 包含星号通配符的字段列表，表示所要查询字段的名称。

表 1 和表 2 表示查询数据的来源，可以是单个或多个。

WHERE 是可选项，如果选择该项，将限定查询数据必须满足该查询条件。

GROUP BY< 字段 > ，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。

[ORDER BY< 字段 >] ，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。

[LIMIT[，]] ，该子句告诉 MySQL 每次显示查询出来的数据条数。

使用 DISTINCT 过滤重复数据

DISTINCT 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据给用户。 DISTINCT 关键字的语法格式为：

SELECT DISTINCT <字段名> FROM <表名>;

使用 DISTINCT 关键字时需要注意以下几点：

DISTINCT 关键字只能在 SELECT 语句中使用。

在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最前面。

如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下才会被去重。 其中，“字段名”为需要消除重复记录的字段名称，多个字段时用逗号隔开。

通过AS设置别名

当表名很长或者执行一些特殊查询的时候，为了方便操作，可以为表指定一个别名，用这个别名代替表原来的名称。 为表指定别名的基本语法格式为：

<表名> [AS] <别名>

表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。在条件表达式中不能使用字段的别名，否则会出现“ERROR 1054 (42S22): Unknown column”这样的错误提示信息。

LIMIT：限制查询结果的条数，指定初始位置

LIMIT 关键字可以指定查询结果从哪条记录开始显示，显示多少条记录。 LIMIT 指定初始位置的基本语法格式如下：

LIMIT 初始位置，记录数

其中，“初始位置”表示从哪条记录开始显示；“记录数”表示显示记录的条数。第一条记录的位置是 0，第二条记录的位置是 1。后面的记录依次类推。 注意：LIMIT 后的两个参数必须都是正整数。

示例：在 tb_students_info 表中，使用 LIMIT 子句返回从第 4 条记录开始的行数为 5 的记录

SELECT * FROM tb_students_info LIMIT 3,5;

其中，“记录数”表示显示记录的条数。如果“记录数”的值小于查询结果的总数，则会从第一条记录开始，显示指定条数的记录。如果“记录数”的值 大于查询结果的总数，则会直接显示查询出来的所有记录。

示例：显示 tb_students_info 表查询结果的前 4 行

1 SELECT * FROM tb_students_info LIMIT 4;

当LIMIT 可以和 OFFSET 组合使用，语法格式如下：

1 LIMIT 记录数 OFFSET 初始位置

示例：在 tb_students_info 表中，使用 LIMIT OFFSET 返回从第 4 条记录开始的行数为 5 的记录

1 SELECT * FROM tb_students_info LIMIT 5 OFFSET 3;

ORDER BY：对查询结果排序

查询到的数据一般都是按照数据最初被添加到表中的顺序来显示。为了使查询结果的顺序满足用户的要求，MySQL 提供了 ORDER BY 关键字来对查询结果 进行排序。

字段名：表示需要排序的字段名称，多个字段时用逗号隔开。

ASC | DESC ：ASC表示字段按升序排序；DESC表示字段按降序排序。其中ASC为默认值。

使用 ORDER BY 关键字应该注意以下几个方面：

当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待。

ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。

WHERE：条件查询数据

在 MySQL 中，如果需要有条件的从数据表中查询数据，可以使用 WHERE 关键字来指定查询条件。 使用 WHERE 关键字的语法格式如下：

查询条件可以是：

带比较运算符和逻辑运算符的查询条件

带 BETWEEN AND 关键字的查询条件

带 IS NULL 关键字的查询条件 带 IN 关键字的查询条件

带 LIKE 关键字的查询条件

多条件的查询语句

在 WHERE 关键词后可以有多个查询条件，这样能够使查询结果更加精确。

多个查询条件时用逻辑运算符 AND（&&）、OR（||）或 XOR 隔开。

AND：记录满足所有查询条件时，才会被查询出来。

OR：记录满足任意一个查询条件时，才会被查询出来。

XOR：记录满足其中一个条件，并且不满足另一个条件时，才会被查询出来。

LIKE：模糊查询

在 MySQL 中，LIKE 关键字主要用于搜索匹配字段中的指定内容。其语法格式如下：

NOT ：可选参数，字段中的内容与指定的字符串不匹配时满足条件。

字符串：指定用来匹配的字符串。“字符串”可以是一个很完整的字符串，也可以包含通配符。 LIKE 关键字支持百分号“ % ”和下划线“ _ ”通配符。

默认情况下， LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写，可以加入 BINARY 关键字。

BETWEEN AND：范围查询

MySQL 提供了 BETWEEN AND 关键字，用来判断字段的数值是否在指定范围内。BETWEEN AND 需要两个参数，即范围的起始值和终止值。如果字段值在 指定的范围内，则这些记录被返回。如果不在指定范围内，则不会被返回。

NOT：可选参数，表示指定范围之外的值。如果字段值不满足指定范围内的值，则这些记录被返回。

取值1：表示范围的起始值。

取值2：表示范围的终止值。

IS NULL：空值查询

MySQL 提供了 IS NULL 关键字，用来判断字段的值是否为空值（NULL）。空值不同于 0，也不同于空字符串。如果字段的值是空值，则满足查询条件，该 记录将被查询出来。如果字段的值不是空值，则不满足查询条件。