MySQL 基础（一）：数据类型与表管理

概念

简单一句话概括，数据库（DataBase）是按照数据结构来组织、管理和存储数据的仓库。

常见的数据库

1. Oracle
2. DB2
3. SQL Server
4. Postgre SQL
5. MySQL

相关术语

数据库系统（DataBase System）DBS

1. 数据库（DataBase）
2. 数据库管理系统（DataBase Management System）DBMS
3. 应用开发工具
4. 管理员及用户 DBA

SQL 语言–Stuctured Query Language：结构化查询语言

1. DDL 数据定义语言
2. DML 数据操作语言
3. DQL 数据查询语言
4. DCL 数据控制语言

MySQL 相关操作

配置文件

my.cnf 是 MySQL 的配置文件

登录/退出 MySQL

• 登录

mysql -u root -p
mysql -u root -proot 直接加上密码

• 获取版本号：

mysql -V
mysql --version

• 登录的同时打开数据库

mysql -u root -p -D db_name

• 退出：exit/quit

注意 ：

1. 命令行结束符默认使用 ;' 或 \g 结束;
2. 可以通过 help 或者 \h 或者 ? 加上相关关键字来查看手册。
   例如：help create database

SQL 语句语法规范

常用 MySQL 的关键字需要大写， 库名、表名、字段名等使用小写；SQL 语句支持拆行操作，拆分的时候不能把完整的单词拆开；数据库名称、表名称、字段名称不要使用 MySQL 的保留字。

常用的 SQL 语句

SELECT USER()  --得到登录用户
SELECT VERSION() --得到 MySQL 版本信息
SELECT NOW() --得到当前的日期时间
SELECT DATABASE() --得到当前打开的数据库
SHOW WARNINGS  --查看上一步操作产生的警告信息
SHOW DATABASES --查看所有的数据库
mysql 中的注释：# 或 -- 注释内容

数据库相关操作

创建数据库

• 创建数据库

1. CREATE (DATABASE|SCHEMA) db_name;
注意：数据库名称最好有意义；名称不要包含特殊字符或者 MySQL 关键字。

2. 检查数据库是否存在，不存在则创建
CREATE DATABASE [IF NOT EXISTS] db_name; 

3. 创建数据库的同时指定编码方式
CREATE DATABASE [IF NOT EXISTS] db_name 
DEFAULT CHARACTER SET 'charset';

• 查看当前服务器下的全部数据库

SHOW DATABASES|SCHEMAS;

• 查看指定数据库的详细信息

SHOW CREATE DATABASE db_name;

• 修改指定数据库的编码方式

ALTER DATABASE db_name [DAFAULT] CHARACTER SET 'charset';

• 打开指定数据库

USE db_name;

数据表的相关操作

• 数据表

1. 是数据库的重要组成部分之一，数据时保存在数据表中。
2. 数据表由行 (row) 和列 (column) 来组成。
3. 每个数据表至少有一列，行可以有零行、一行或多行。
4. 表名要求唯一，不要包含特殊字符，最好是有意义的。

• 创建数据表

CREATE TABLE [IF NOT EXISTS] tbl_name(
         字段名称 字段类型 [完整性约束],
         字段名称 字段类型 [完整性约束],
         ....
)ENGINE=存储引擎(默认INNODB) CHARSET=编码方式;

CREATE TABLE IF NOT EXISTS user(
      id INT,
      username VARCHAR(20),
      password CHAR(32),
      age TINYINT,
      tel CHAR(11),
      sex ENUM('男','女','保密')
)ENGINE=INNODB CHARSET=UTF8;

• 查看当前数据库下已有的数据表

SHOW TABLES;
SHOW FULL TABLES [FROM|IN] db_name [LIKE 'pattern'|WHERE expr];

• 查看表的详细信息

SHOW CREATE TABLE tbl_name;

• 查看表的结构

DESC tbl_name;
DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;

• 删除表

DROP TABLE IF EXISTS tbl_name;

• 向表中添加数据

INSERT INTO tbl_name(字段1，字段2...) 
VALUES(值1，值2...); -- 字段名省略时默认全部字段添加数据

• 表结构的相关操作

1. 添加字段
ALTER TABLE tbl_name ADD 字段名称 字段属性
[完整性约束条件] [FIRST|AFTER 字段名称];

2. 删除字段
ALTER TABLE tbl_name DROP 字段名称;

3. 添加默认值
ALTER TABLE tbl_name ALTER 字段名称 
SET DEFAULT 默认值;

4. 删除默认值
ALTER TABLE tbl_name ALTER 字段名称 
DROP DEFAULT;

5. 修改字段类型
ALTER TABLE tbl_name MODIFY 字段名称 字段类型
[FIRST|AFTER 字段名称];

6. 修改字段名、字段类型
ALTER TABLE tbl_name CHANGE 原字段名称 新字段名称 
字段类型 [FIRST|AFTER 字段名称]

7. 添加主键
ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称);

8. 删除主键
ALTER TABLE tbl_name DROP PRIMARY KEY;
如果有AUTO_INCREMENT,应先去掉它，再删除主键。 

9. 添加唯一索引
ALTER TABLE tbl_name ADD UNIQUE KEY|INDEX index_name(字段名称);

10. 删除唯一索引
ALTER TABLE tbl_name DROP INDEX index_name;

11. 修改数据表的名称
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
或
RENAME TABLE tbl_name TO new_tbl_name;

12. 修改 AUTO_INCREMENT 的值
ALTER TABLE tbl_name AUTO_INCREMENT=值;

MySQL 数据类型

• 数值型

  整数型
  浮点型
  

• 字符串类型
  
  注意：

1. CHAR 的查询效率高于 VARCHAR，CHAR 相当于是拿空间换时间，VARCHAR 是拿时间换空间。

2. CHAR 存储的时候,会用空格填充到指定的长度，但在检索的时候会去掉后面的空格；VARCHAR 存储的时候不会进行填充，检索时后面的空格会留下。

3. TEXT 列不能有默认值，检索时不存在大小写转换。

• 日期时间类型
  

MySQL 完整性约束

1. UNSIGNED 无符号，没有负数，从 0 开始；
2. ZEROFILL 零填充,当数据的显示长度不够的时候使用前补 0 的效果填充至指定长度；
3. NOT NULL 非空约束，也就是插入值的时候该字段必须给值；
4. DEFAULT 默认值，如果插入记录的时候没有赋值，则使用默认的；
5. PRIMARY KEY 主键，标识记录的唯一性，值不能重复，一个表只能有一个主键；
6. UNIQUE KEY 唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是 null 值除外；
7. AUTO_INCREMENT 自增长，只能用于数值列，而且配合索引使用用,默认起始值是 1，每次增长 1；
8. FOREIGN KEY 外键约束；

MySQL 存储引擎

MyISAM 存储引擎

1. 默认 MyISAM 的表会在磁盘中产生 3 个文件：.frm 表结构文件、.MYD 数据文件、.MYI 索引文件 。

2. 可以在建表的时候指定数据文件和索引文件的存储位置，只有 MyISAM 表支持：DATA DIRECTORY [=] 数据保存的绝对路径、INDEX DIRECTORY [=] 索引文件保存的绝对路径 。

3. MyISAM 的单表最大支持的数据量是 2 的 26 次方记录。

4. 每个表最多可以建立 64 个索引。

5. 如果是复合索引，每个复合索引最多包含 16 个列，索引值最大长度是 1000B。

6. MyISAM 引擎的存储格式

   SHOW TABLE STATUS LIKE ‘表名’\G – 查看存储格式

   a. 定长（FIXED静态）是指字段中不包含 VARCHAR/TEXT/BLOB。
   特点：静态表的查询速度很快；当数据出现异常时，也很容易恢复

   b. 动态（DYNAMIC） 只要字段中包含 VARCHAR/TEXT/BLOB。
   特点：处理数据复杂，性能很低，但占用空间小。

   c. 压缩（COMPRESSED） myisampack 创建 。

InnoDB 存储引擎

1. 设计遵循 ACID 模型，支持事务，具有从服务崩溃中恢复的能力，能够最大限度保护用户的数据。
2. 支持行级锁，可以提升多用户并发时的读写性能。
3. 支持外键，保证数据的一致性和完整性。
4. InnoDB 拥有自己的缓冲池，常用的数据和索引都在缓存中。
5. 在 INSERT、uPDATE、DELETE 操作，InnoDB 会使用一种 change buffering 的进制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘 I/O，提高性能。
6. 创建 InnoDB 表后会产生两个文件：.frm 表结构文件、.ibd 数据和索引存储表空间中。
7. 所有的表都需要创建主键，最后配合 AUTO_INCREMENT，也可以放到经常查询的列作为主键。