MySQL —— 数据库基础

阅读原文

前言

MySQL 是目前使用最广泛的关系型数据库，是在软件开发中所必备的技能之一，这是关于 MySQL 的系列文章，将从 MySQL 的基本概念、基本使用、SQL 语句、函数、聚合函数、表连接以及表设计等多方面对 MySQL 进行总结，而本篇是数据库基础部分，大多都是一些概念性的内容和表的创建，数据库博大精深，本系列文章内容较浅，适合于前端的同学们对 MySQL 的入门，当然这也是我的学习笔记，希望可以帮助大家。

MySQL 的基本概念

MySQL 的特点：开源免费、性能高、安装使用简单。

MySQL 的作用：

• 存储大量数据，方便检索和访问；
• 保持数据信息的一致、完整；
• 实现数据的共享和安全；
• 通过组合分析产生新的有用信息。

实体：只要是客观存在，能够被描述出来的都是实体，每一条数据都是一个实体。

DB：是数据库的意思，就是存储数据的仓库，可以存放结构化的数据。

DBMS：代表数据管理系统，是一种系统软件，提供操作数据库的环境，如 Navicat for MySQL，可以通过数据库管理系统对数据进行增、删、改、查。

SQL：结构化查询语言，专门用来和数据库进行交流的语言，几乎所有的 DBMS 都支持 SQL。

数据表：

• 表是数据库中包含所有数据的数据库对象，也是其他对象的基础；
• 表定义是一个集合，数据在表中是按行和列的格式组织的，用来存放数据；
• 行被称为记录，用来存放一个实体，列称为字段用来描述实体的某一个属性。

MySQL 的配置和命令

1、MySQL 配置

在 MySQL 的默认安装目录中 my.ini 文件是专门用来配置的，常用配置项如下：

• port：端口号；
• basedir：安装目录；
• datadir：数据存放目录
• charcter-set-server：字符集；
• default-storage-engine：存储引擎；
• sql-mode：语法模式；
• max-connections：最大连接数。

2、MySQL 命令

MySQL 服务启动和停止：

net start MySQL
net stop MySQL

连接和退出数据库：

mysql -h 127.0.0.1 -P 3306 -uroot -p123456
exit

-h 代表主机名，-p 代表端口号，-u 和 -p 分别代表当前 MySQL 服务的账号和密码。

查看当前 MySQL 中的数据库：

show dbs;
show database;

切换数据库：

use dataname;

查看数据库的表：

show tables;
show tables from mysql;

查看当前所在的数据库：

select database();

查询表结构：

desc tablename;

数据完整性

数据完整性是指，数据库种所有数据值均为正确状态，如果数据存储有不正确的的数据值，则该数据库丧失数据完整性，为了实现数据完整性，需要检验数据库表中每行每列的数据是否符合要求，在创建表时，应该保证以后输入的数据都是正确的，错误的数据不允许输入。

1、域（列）完整性

域完整性是对数据表中字段属性的约束，通常指数据的有效性，它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的，如关于年龄的字段，数据类型应为 int，关于普通的字符串值得类型应该为 varchar 等等，并在规定值类型时相应得规定值的长度，即所占的字节数。

数据类型图如下：

数值类型整数型：

• tinyint：存储所占 1 字节，无符号可以存储 0 ~ 255，有符号可以存储 -128 ~ 127；
• smallint：存储所占 2 字节，无符号可以存储 0 ~ 65535，有符号可以存储 -32768 ~ 32767；
• mediumint：存储所占 3 字节，无符号可以存储 0 ~ 16777215，有符号可以存储 -8388608 ~ 8388607；
• int：存储所占 4 字节，无符号可以存储 0 ~ 4294967295，有符号可以存储 -2147483648 ~ 2147483647；
• bigint：存储所占 8 字节，无符号可以存储 0 ~ 18446744073709551615，有符号可以存储 -9223372036854775808 ~ 9223372036854775807。

上面的整数型用来规定字段存储的数据必须为整数，从上到下需要占用的空间越来越多，能表示的数字也越来越大，定义字段时选择满足需求的数字范围即可。

数值类型小数型：

• float：单精度浮点型，存储所占 4 字节，数值范围为 -3.4E38 ~ 3.4E38，7 个有效位；
• double：双精度浮点型，存储所占 8 字节，数值范围为 -1.7E308~1.7E308，15 个有效位；
• decimal：定点型，存储所占 16 字节，28 个有效位，不存在精度损失，常用于银行账目计算。

数值存储范围越小，精度越高，范围越大，精度越不准确，既要求精度，又固定小数点位数的数值存储，建议使用 decimal，优点在于可以自定义小数点位数，精度高，特殊情况，数值范围巨大，只能使用 float 类型，该类型一般不建议使用。

字符串类型：

• set：存储字符串对象，最多包含 64 个元素，并且可以取到集合中任意元素；
• enum：存储字符串对象，最多包含 65536 个元素，只能取到集合中一个元素；
• blob：用于存储二进制字符串，有子类型；
• text：用于存储大文本，有子类型；
• char：定长，最大 255 字节，存储空间未满，会在数据右侧填充空格；
• varchar：不定长，最大 65536 字节，当长度小于 4 时，会自动转换成 char。

上面类型中最常用的为 varchar 可以根据字段内容的自定义长度。

时间类型：

• year：yyyy 格式表示的年份值；
• timestamp：yyyymmddhhmmss 格式表示的时间戳；
• time：hh:mm:ss 格式表示的时间值；
• date：yyyy-mm-dd 格式表示的日期值；
• datetime：yyyy-mm-dd hh:mm:ss 格式表示的日期和时间。

默认值：默认值是指如果某个字段没有指定值的情况下，会为该字段指定一个预先设定的值。

非空约束：我们可以指定某个字段不能不输入，必须提供一个非空的值，即不能为 null。

2、实体（行）完整性

实体完整性是对关系中的记录唯一性，也就是主键的约束，准确地说，实体完整性是指关系中的主属性值不能为 null 且不能有相同值，定义表中的所有行唯一的标识，一般用主键。

主键：表中一列或者几列组合的值能用来唯一标识表中的每一行，这样的列或者列组合称为表的主键，主键表的数据不能重复，如果两列或者多列组合起来唯一标识表中的每一行，则该主键又称为 “组合主键”。

主键的选择标准：

• 最少性：尽量选择单个键作为主键；
• 稳定性：由于主键是用来在两个表之间建立关联的，不能经常更新或者说一般不更新，所以最好选择无意义的列作为主键，如各种 id 值。

3、引用完整性

引用完整性意思是对关系型数据库中建立关联关系的数据表之间数据参照引用的约束，也就是对外键的约束，准确地说，是指关系中的外键必须是另一个关系的主键有效值，或者是 null，参考完整性，维护表间数据的有效性，完整性，通常通过建立外部键联系另一张关联表的主键实现。

外键：用于连接两张表的字段称为外键，属于外键表，通常要关联的主键表的字段作为外键，保证了引用完整性。

外键约束：外键表的外键必须引用主键表的主键，主表没有记录。

如果一个表的外键引用了一个表的主键会有以下限制：

• 在外键表里不能添加主键表里没有的数据，外键表的数据与主键表的数据必须通过外键一一对应；
• 要想删除主键表的数据必须删除所有外键表的数据。

唯一约束：唯一约束是指某个字段值是唯一的，在所有的记录中不能有重复的值，如某个人的身份证号，需将外键的索引类型设置为 Unique。

标识列：当表中没有适合做主键的列时，此时创建作为主键的无意义列称为标识列，标识列的值是自动生成的，不能输入修改，且是自增的，可以设置每次的增量，具有 “断号保留” 的特性，当某一个实体被删除时，该标识列的值是永久保存的，其他新增实体会在这个标识列的基础上继续自增。

DDL 数据定义语言的使用

我们可以通过两种方式创建表，可以使用 DDL（data defintion language）数据定义语言来创建，也可以使用 DBMS 数据管理系统来创建，使用 DBMS 还是很方便的，而且也是最常用的方式，但 DDL 创建方式我们不得不说，就是通过代码的方式来实现表的创建和操作，因为这是最原始的方式，即使图形化的操作会更方便，我们也应该了解。

1、SQL 规范

SQL 语句的规范如下：

• SQL 语句不区分大小写，建议 SQL 关键字大写，表名和列表小写；
• 命令用分号结尾；
• 命令可以缩进和换行，一种类型的关键字放在一行；
• 可以写单行和多行注释 , # 和 -- 是单行注释， /*/ 是多行注释。

2、使用 DDL 操作表

在 DBMS 中的查询编辑器可以代替命令行实现命令的编写，为了视觉效果，我们都使用 Navicat for MySQL 的查询编辑器，操作界面如下图。

(1) 创建表

创建表 student，创建一个标识列 id 不为空，设为主键并自增，创建 name 列，varchar 类型，64 位，不为空，创建 age 列，int 类型，不为空，创建 city 列，varchar 类型，32 位，默认值为 “北京”。

CREATE TABLE `student`
(
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(64) NOT NULL,
    `age` INT NOT NULL,
    `city` VARCHAR(32) DEFAULT "北京"
);

(2) 操作表的列

给上面的表 student 增加 idcard 列，值为空，将 idcard 列类型大小修改为 32 位，添加 province 列，插入到 city 列前面，查询表结构，删除 idcard 列，删除表 student。

ALTER TABLE `student` ADD COLUMN `idcard` VARCHAR(18) NULL;
ALTER TABLE `student` MODIFY COLUMN `idcard` VARCHAR(32) NULL;
ALTER TABLE `student` ADD COLUMN `province` VARCHAR(32) AFTER `age`;
DESC `student`;
ALTER TABLE `student` DROP COLUMN `idcard`;
DROP TABLE `student`;

(3) 添加主键约束

重新创建上面的 student 表，city 不设置默认值，不添加主键约束和标识列，再通过命令添加主键约束。

CREATE TABLE `student`
(
    `id` INT NOT NULL,
    `name` VARCHAR(64) NOT NULL,
    `age` INT NOT NULL,
    `city` VARCHAR(32)
);
ALTER TABLE `student` ADD PRIMARY KEY(id);

(4) 添加唯一约束

将上面我们 student 表中的 idcard 列再加回来，给 idcard 列添加唯一约束，外键名为 uq_idcard。

ALTER TABLE `student` ADD COLUMN `idcard` VARCHAR(18) NULL;
ALTER TABLE `student` ADD UNIQUE INDEX uq_idcard(`idcard`);

(5) 添加默认约束

将 student 表 city 列的默认值设置为 “北京”。

ALTER TABLE `student` MODIFY COLUMN `city` VARCHAR(64) DEFAULT "北京";

(6) 添加外键约束

创建一个子表 score，添加 student_id 列，添加外键 fk_student_id，将 student_id 与 student 表的主键 id 相关联，此时 student 为主表（主键表），score 为子表（外键表）。

CREATE TABLE `score`
(
    `student_id` INT
);
ALTER TABLE `score` ADD CONSTRAINT `fk_student_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`);

注意：在项目中一般不会添加这个约束，因为在开发时会频繁操作表和修改数据，会经常报错，非常麻烦，不添加这个约束并不影响数据的检索和修改。

(7) 删除约束

删除 score 表与 student 表的外键约束，即删除外键 fk_student_id。

ALTER TABLE `score` DROP FOREIGN KEY `fk_student_id`;

3、使用 DDL 创建一个完整的表关系

完整的表关系图如下：

DDL 命令如下：

-- 创建表 student
CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `idcard` VARCHAR(18) DEFAULT NULL,
    `age` INT(11) DEFAULT NULL,
    `city` VARCHAR(50) DEFAULT "北京",
    PRIMARY KEY (`id`)
);

-- 创建表 course
CREATE TABLE `course` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (`id`)
);

-- 创建表 score，并添加外键约束
CREATE TABLE `score` (
    `student_id` INT(11) NOT NULL DEFAULT "0",
    `course_id` INT(11) NOT NULL DEFAULT "0",
    `grade` FLOAT DEFAULT NULL,
    PRIMARY KEY (`student_id`, `course_id`),
    KEY `fk_courseid` (`course_id`),
    CONSTRAINT `fk_courseid` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`),
    CONSTRAINT `fk_student_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在实际开发当中使用 DDL 对表的编辑并不多，因为命令繁琐，在效率上，使用 DBMS 效率更高，但是 DDL 是学习 MySQL 数据库的根本之一，应该会写。

总结

到此 MySQL 系列文章的第一篇就结束了，里面大部分内容都是关于 MySQL 数据库的概念以及表的创建，非常枯燥，但又是初学 MySQL 所必须要懂的知识点，相信大家通过本节已经对 MySQL 有了一定的认识，那么下一篇将是重中之重，是在开发中经常使用的 DML 数据库操作语言，也就是我们平时所说的 SQL 语句，在此期待我们的下一篇 《MySQL —— SQL 语句总结》。