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

清疚 2021-09-21 03:22 314阅读 0赞

### 文章目录 ###

*   *   *  概念
         *  相关术语
         *  MySQL 相关操作
         *  数据库相关操作
         *  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 数据类型**

*  数值型
    
    *整数型*![整数型][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29zY2hpbmFfNDE3OTA5MDU_size_16_color_FFFFFF_t_70]  
    *浮点型*  
    ![浮点型][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29zY2hpbmFfNDE3OTA5MDU_size_16_color_FFFFFF_t_70 1]
 *  字符串类型  
    ![字符串型][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29zY2hpbmFfNDE3OTA5MDU_size_16_color_FFFFFF_t_70 2]  
    **注意：**

1.  `CHAR` 的查询效率高于 `VARCHAR`，`CHAR` 相当于是拿空间换时间，`VARCHAR` 是拿时间换空间。
2.  `CHAR` 存储的时候,会用空格填充到指定的长度，但在检索的时候会去掉后面的空格；`VARCHAR` 存储的时候不会进行填充，检索时后面的空格会留下。
3.  `TEXT` 列不能有默认值，检索时不存在大小写转换。

*  日期时间类型  
    ![日期时间类型][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29zY2hpbmFfNDE3OTA5MDU_size_16_color_FFFFFF_t_70 3]

**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，也可以放到经常查询的列作为主键。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29zY2hpbmFfNDE3OTA5MDU_size_16_color_FFFFFF_t_70]: /images/20210920/fa2c64db8f4f4a7abbcdebc11175c3d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29zY2hpbmFfNDE3OTA5MDU_size_16_color_FFFFFF_t_70 1]: /images/20210920/defd3ee8b4334cf8b9b5863a7821d7a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29zY2hpbmFfNDE3OTA5MDU_size_16_color_FFFFFF_t_70 2]: /images/20210920/fc69ea24dd684cdfb05c64dd2b788552.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29zY2hpbmFfNDE3OTA5MDU_size_16_color_FFFFFF_t_70 3]: /images/20210920/a08b5d3fa9a94be9b3ec73ef122a01ec.png