mysql 存储引擎,数据类型和运算符

mysql 存储引擎

1.存储引擎其实就是如何存储数据
2.如何为存储的数据建立索引
3.如何更新，查询数据的等技术的实现方法

注意: 在Oracle 和 sqlserve 等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的，
而mysql数据库提供了多种存储引擎,用户可以根据不同的需求为数据表选择不同的存储引擎，也可以根据自己的需要编写存储引擎

查询mysql中支持的存储引擎:

命令:show engines;

show variables like 'have%';

Innodb 存储引擎的优缺点:

优点:提供了良好的事物管理,崩溃修复能力和并发控制

缺点:读写效率差,占用的数据空间相对比较大.

innodb 表是以下情况的理想引擎:

1.更新密集的表:innodb 存储引擎特别适合处理多重并发的更新请求
2.事务处理
3.自动灾难恢复:与其他存储引擎不同,Innodb 表能够从灾难中恢复， 虽然myisam表能够在灾难后修复，但其过程要长得多.

MyISAM 存储引擎的文件类型

frm:存储表的结构
myd:存储数据
myi:存储索引

myisam的优缺点：

优点：占用空间小,处理速度快

缺点：不支持事务机制


mysql 数据类型

mysql 支持的数据类型主要分成3类: 数字类型,字符串类型,日期和时间类型

数字类型:

1.精确类型:numeric,decimal,integer(int)...

integer 细分为:tinyint,bit,bool,smallint,mediumint,int,bigint 

2.近似类型:float,real,double...

在创建表时，使用哪种数字类型，应该遵循以下原则:
1.选择最小的可用类型,如果值永远不超过127,则使用tinyint比int 好。
2.对于完全都是数字的,可以选择整数类型.
3.浮点类型用于可能具有小数部分的数,例如货物单价,网上购物交付金额等.

字符串类型:

char:固定长度字符串,区间是:0~255 个字符.
varchar,text,blob

char 和 varchar 的一些区别:

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

翻译:CHAR、VARCHAR类型是相似的,但不同的方式存储和检索。他们在最大长度也不同,在尾部空格是否保留。

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255.
 When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, 
 trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

翻译:当你在创建表时,char所设置的长度是固定的,char 长度的范围是0 到 255,当存储一个char值,剩余长度在在尾部加空格填充,
当检索char值时,尾部空格会被清除，除非 pad_char_to_full_length sql 模式是开启的.
mysql 运算符

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section C.10.4, “Limits on Table Column Count and Row Size”.

翻译:VARCHAR列中的值是可变长度的字符串。可以指定长度的值从0到65535。VARCHAR的有效最大长度受到的最大行大小(65535字节,这是所有列共用的)和使用的字符集。参见C.10.4“限制表列数和行大小”。


In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

翻译:与CHAR、VARCHAR值被存储为一个字节或2字节长度的前缀+数据。前缀表示长度的字节数的值。一列使用一个长度字节如果值要求不超过255字节,两个长度字节如果值可能需要超过255字节。


If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.7, “Server SQL Modes”.

翻译:
如果严格的SQL模式不启用你赋值一个CHAR、VARCHAR列超过列的最大长度,该值截断以适应和生成一个警告。
是非空间截断的字符,可以导致一个错误发生(而不是一个警告)和抑制插入值的使用严格的SQL模式。参见5.1.7“服务器SQL模式”。


For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.

The following table illustrates the differences between CHAR and VARCHAR by showing the result of storing various string values into CHAR(4) and VARCHAR(4)columns (assuming that the column uses a single-byte character set such as latin1).

翻译:
为VARCHAR列,尾随的空格超过列长度是截断之前插入和生成一个警告,不管使用的SQL模式。CHAR列,截断多余的尾随空格插入值默默地不管SQL执行模式。


VARCHAR值存储时不填充。尾随的空格时保留值存储和检索,符合标准的SQL。


下表说明了差异CHAR、VARCHAR通过展示的结果存储各种字符串值为CHAR(4)和VARCHAR(4)列(假设等列使用单字节字符集latin1)中的一个。


Value       CHAR(4)     Storage Required    VARCHAR(4)  Storage Required
''          ' '         4 bytes             ''          1 byte
'ab'        'ab '       4 bytes             'ab'        3 bytes
'abcd'      'abcd'      4 bytes             'abcd'      5 bytes
'abcdefgh'  'abcd'      4 bytes             'abcd'      5 bytes


The values shown as stored in the last row of the table apply only when not using strict mode; if MySQL is running in strict mode, values that exceed the column length are not stored, and an error results.

If a given value is stored into the CHAR(4) and VARCHAR(4) columns, the values retrieved from the columns are not always the same because trailing spaces are removed from CHAR columns upon retrieval. The following example illustrates this difference:

翻译:

中存储的值显示为表的最后一行仅适用于当不使用严格模式;如果在严格模式下运行MySQL,价值超过列长度不存储,和一个错误的结果。


如果一个给定的值存储在CHAR(4)和VARCHAR(4)列,从列中检索值并不总是相同,因为尾随的空格从CHAR列检索。下面的例子说明了这种差异:


mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+
1 row in set (0.06 sec)

Values in CHAR and VARCHAR columns are sorted and compared according to the character set collation assigned to the column.

All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant. 
For example:

mysql> CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO names VALUES ('Monty');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Monty', myname = 'Monty  ' FROM names;
+------------------+--------------------+
| myname = 'Monty' | myname = 'Monty  ' |
+------------------+--------------------+
|                1 |                  1 |
+------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT myname LIKE 'Monty', myname LIKE 'Monty  ' FROM names;
+---------------------+-----------------------+
| myname LIKE 'Monty' | myname LIKE 'Monty  ' |
+---------------------+-----------------------+
|                   1 |                     0 |
+---------------------+-----------------------+
1 row in set (0.00 sec)

This is true for all MySQL versions, and is not affected by the server SQL mode

For those cases where trailing pad characters are stripped or comparisons ignore them, 
if a column has an index that requires unique values, 
inserting into the column values that differ only in number of trailing pad characters will result in a duplicate-key error. 
For example, if a table contains 'a', an attempt to store 'a 'causes a duplicate-key error.


日期和时间类型:

1.date :日期(YYYY-MM-DD)
2.time :时间(HH:MM:SS)
3.datetime :日期和时间(YYYY-MM-DD HH:MM:SS)
4.timestamp 
5.year

mysql 支持四种运算符具备哪些功能:

1.算数运算符 

+
-
*
/
%
div
mod

2.比较运算符
3.逻辑运算符
4.位运算符 (不常用)