mysql笔记一（创建库、存储引擎、数据类型）

最近复习了一下mysql基础，想写一个系列笔记。一方面巩固自己的知识，一方面希望能够给初学mysql的朋友一些帮助。
OK，如何安装和创建mysql这里就不写了。直接看创建数据库。
用工具的创建和删除数据库这里就不写了。本人习惯用命令行一些。

一、创建数据库

mysql> CREATE DATABASE MYTEST;
Query OK, 1 row affected (0.30 sec)
mysql> SHOW DATABASE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE' at line 1
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DATA_MINING        |
| MYTEST             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql> DROP DATABASE MYTEST;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABSES;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABSES' at line 1
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DATA_MINING        |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

二、MySQL数据库中的存储引擎
存储引擎：存储引擎以插件的形式被数据库所引入。
查看数据库支持的存储引擎的命令：

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> SHOW ENGINES -G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-G' at line 1
mysql> SHOW ENGINES -G
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-G' at line 1
mysql> SHOW ENGINES \G //;  \g表示SQL命令的结束，\G命令除了表示命令的结束之外还可以使结果显示的更加美观
*************************** 1. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO //注释：表示存储引擎所支持的分布式是否符合XA规范
  Savepoints: NO //注释：表示存储引擎是否支持事物处理中的保存点
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

通过SHOW VARIABLES命令来查看所支持的存储引擎：

mysql> SHOW VARIABLES LIKE 'have%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| have_compress        | YES      |
| have_crypt           | YES      |
| have_csv             | YES      |
| have_dynamic_loading | YES      |
| have_geometry        | YES      |
| have_innodb          | YES      |
| have_ndbcluster      | NO       |
| have_openssl         | DISABLED | //表示支持但是还未开启
| have_partitioning    | YES      |
| have_profiling       | YES      |
| have_query_cache     | YES      |
| have_rtree_keys      | YES      |
| have_ssl             | DISABLED |
| have_symlink         | YES      |
+----------------------+----------+
14 rows in set (0.00 sec)

查看默认的搜索引擎命令：

mysql> SHOW VARIABLES LIKE 'storage_engine%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)
mysql> 

手动方式修改数据库默认存储引擎
若要修改默认的存储引擎需要修改my.ini配置文件中的[mysqld]标签下的default-storage-engine项
例如把默认存储修改为MyISAM,只需要修改default-storage-engine=MyISAM，然后重启mysql数据库服务即可生效。

不同的存储引擎的区别：可以查看http://menglimengwai.iteye.com/blog/464667
mysql中存储引擎可以决定表的类型，即决定表的存储方式。

---

三、mysql中的数据类型

查看帮助文档可以发现，mysql数据库支持整数类型、浮点数类型、定点数类型和位类型、日期和时间类型、字符串类型

整数类型
支持标准SQL中的所有整数类型（SMALLINT和INT）外，还进行了相应的扩展，扩展后增加了TINYINT、MEDIUMINT、BIGINT这三个整数类型。
各类型的一个对照关系：
类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 字节 (-128，127) (0，255) 小整数值
SMALLINT 2 字节 (-32 768，32767) (0，65535) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8388607) (0，16777215) 大整数值
INT或INTEGER 4 字节 (-2147483648，2147483647) (0，4294967295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值

查看mysql系统帮助的方法：HELP contents;

mysql> HELP contents;//查看mysql帮助文档支持的目录列表
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

查看数据类型的帮助信息：

mysql> HELP Data Types;
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE
mysql>  

查看INT的帮助信息：

mysql> HELP INT;
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

URL: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html


mysql> 

浮点数类型、定点数类型和位类型
mysql中除了支持标准SQL中的所有浮点数类型（FLOAT、DOUBLE）、定点数类型（DEC）外，还进行了相应的扩展。扩展后增加了位类型（BIT）。
浮点类型具体特性：
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) 双精度

当要求小数点10位精确到10位之后，需要选择DOUBLE类型。
当要求小数位精度非常高时可以选择DEC类型。DEC类型和DECIMAL是同名词。

一个测试FLOAT数据类型和DEC数据类型的示例：

mysql> CREATE TABLE f_test(
    ->  `a` FLOAT(38,30),
    -> b DECIMAL(38,30) );
Query OK, 0 rows affected (0.30 sec)
mysql> INSERT INTO f_test VALUES (12345.000000000000000000000000000001,12345.000000000000000000000000000001);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM F_TEST \G
ERROR 1146 (42S02): Table 'DATA_MINING.F_TEST' doesn't exist
mysql> SELECT * FROM f_test \G
*************************** 1. row ***************************
a: 12345.000000000000000000000000000000
b: 12345.000000000000000000000000000001
1 row in set (0.00 sec)
mysql>

从这个示例中可以看出，FLOAT、DOUBLE存储的近似值，而DEC数据类型存储的是字符串。因此可以提供更高的精度。
比如语句 FLOAT(7,3) 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字。

BIT数据类型取值范围是1至8位

3.日期和时间数据类型

　　在处理日期和时间类型的值时，MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。
类型 大小
(字节) 范围 格式 用途
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 8 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳
DATE、TIME 和 TEAR 类型
　　MySQL 用 DATE 和 TEAR 类型存储简单的日期值，使用 TIME 类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串，DATE 类型的值应该使用连字号作为分隔符分开，而 TIME 类型的值应该使用冒号作为分隔符分开。
　　需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。
　　MySQL 还对日期的年份中的两个数字的值，或是 SQL 语句中为 TEAR 类型输入的两个数字进行最大限度的通译。因为所有 TEAR 类型的值必须用 4 个数字存储。MySQL 试图将 2 个数字的年份转换为 4 个数字的值。把在 00-69 范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1979 之内。如果 MySQL 自动转换后的值并不符合我们的需要，请输入 4 个数字表示的年份。
DATEYIME 和 TIMESTAMP 类型
　　 除了日期和时间数据类型，MySQL 还支持 DATEYIME 和 TIMESTAMP 这两种混合类型。它们可以把日期和时间作为单个的值进行存储。这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。
　　如果我们对 TIMESTAMP 类型的字段没有明确赋值，或是被赋与了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它。

4.字符串数据类型

　　MySQL 提供了 8 个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。
类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-255字节 变长字符串
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字节 中等长度文本数据
LOGNGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

可变长数据类型和不可变长数据类型的区别：
可变长的数据类型根据数据的实际长度进行存储；
不可变长的数据类型根据最大数据长度进行存储。