下载掌阅APP，畅读海量书库
立即打开
· 数据库设计理论
· 连接数据库
· 创建数据库和表
本章将讲解数据库设计理论，以简单的示例讲解三大范式以及MySQL中对数据库和表的基本操作（包括创建数据库、切换数据库、创建表等）。
关系模型（Relational Model）是一种基于表的数据模型，它广泛应用于数据库管理系统（DBMS）中。关系模型的核心思想是将数据组织成表，每个表包含一组相关的行和列。这些行和列之间的关系通过主键和外键来定义，从而实现数据的完整性、一致性和可维护性。关系模型中一些重要术语介绍如下：
· 属性（Attribute）：列的名称。
· 关系（Relation）：列属性之间存在的某种关联。
· 表（Table）：由多个属性以及众多元组所表示的各个实例组成。
· 键（Key）：由一个或多个属性组成，其值能唯一标识关系中的一个元组。如果某个关系A中的一个（组）属性是另一个关系B的键，则该（组）属性在A中被称为外键。
· 笛卡儿积（交叉连接，Cross Join）：第一个关系的每一行数据分别与第二个关系的每一行数据组合。
· 自然连接（Natural Join）：第一个关系的每一行数据与第二个关系的每一行数据进行匹配，若得到交叉部分则合并，若无交叉部分则舍弃。
· θ连接（Theta Join）：加上约束条件的笛卡儿积，先得到笛卡儿积，再根据约束条件删除不满足条件的元组。
· 外连接（Outer Join）：执行自然连接后，将舍弃的部分也加入，并且把匹配失败处的属性设置为null。
表2-1是一张酒店客人入住信息表。
表2-1 酒店客人入住信息表
通过表2-1可以发现，该表存在不符合规范的设计，总共有如下几点：
（1）信息重复：比如客房类型和客房状态存在大量的数据重复。
（2）更新异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被同步修改。
（3）插入异常：无法正确表示信息。
（4）删除异常：丢失有效信息。
下面利用三大范式对数据库进行改造。
第一范式（1NF）： 目标是确保每列的原子性，如果每列都是不可再分的最小数据单元（也被称为最小的原子单元），则满足第一范式。
例2.1 第一范式优化表格示例。
没有经过第一范式优化的表格如表2-2所示。
表2-2 没有经过第一范式优化的表格
经过第一范式优化的表格如表2-3所示。
表2-3 经过第一范式优化的表格
第二范式（2NF）： 要求每张表只描述一件事情。
例2.2 第二范式优化表格示例。
没有经过第二范式优化的表格如表2-4所示。
表2-4 没有经过第二范式优化的表格
经过第二范式优化之后，把一张表拆解成两张表，分别为Guest表和Room表。
Guest表如表2-5所示。
表2-5 Guest表
Room表如表2-6所示。
表2-6 Room表
第三范式（3NF）： 如果一个关系满足第二范式，并且除了主键以外的其他列都不依赖于主键列，则满足第三范式。
例2.3 第三范式优化表格示例。
没有经过第三范式优化的Room表如表2-7所示。
表2-7 没有经过第三范式优化的Room表
经过第三范式优化之后，拆成3张表，分别是Room表、RoomType表和RoomState表。
Room表如表2-8所示。
表2-8 Room表
RoomType表如表2-9所示。
表2-9 RoomType表
RoomState表如表2-10所示。
表2-10 RoomState表
如上所述，经过三大范式将一张存在问题的大表拆分成4张表，最终数据库设计结构如图2-1所示（PK表示主键，FK表示外键）。
图2-1 酒店管理系统数据库模型
数据库设计好了，本节就来连接数据库并进行基本信息查询。
要连接到SQL服务器，需要在调用MySQL时提供用户名，并且很可能需要提供密码。如果SQL服务器在其他计算机上运行，还必须指定主机名。此时应联系管理员，了解该使用哪些连接参数来进行连接（即使用的主机、用户名和密码）。当知道正确的参数之后，可以执行如下命令进行连接：
shell> mysql -h host -u user -p
Enter password: ********
host和user分别代表运行的MySQL服务器的主机名和MySQL账户的用户名。********代表密码，当MySQL显示“Enter password:”提示时，输入账户对应的密码。如果输入密码正确，那么会看到如下信息：
shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 8.0.25-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
出现“mysql>”提示符就表示可以输入SQL语句了。
如果在运行MySQL的同一台计算机上登录，则可以省略主机名，只需执行以下命令：
shell> mysql -u user -p
如果在尝试登录时收到错误提示信息，例如ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)，则表示MySQL服务器守护程序（在UNIX操作系统中）或服务（在Windows操作系统中）未运行，也就是需要启动MySQL服务。
本小节将介绍信息查询的基本原则，通过几个查询示例来熟悉MySQL的工作原理。
例2.4 查询服务器的版本号和当前日期。在“mysql>”提示符后面输入如下命令，然后按Enter键：
这是一个简单的查询，说明了有关MySQL查询的几点信息：
（1）查询通常由以分号结尾的SQL语句组成（有一些例外可以省略分号，QUIT语句就是其中之一）。
（2）当我们输入查询语句后，MySQL将它发送到服务器执行并显示结果，然后显示出下一个“mysql>”提示符，表明它已准备好接收另一个查询。
（3）MySQL以表格形式（行和列）显示查询的结果。第一行是列的标签（或称为列名），其他行是查询的结果。通常列标签是从数据库表中提取的列名称。如果要检索表达式的值而不是表列的值，那么MySQL会使用表达式本身来标记该列。
图2-2 SQL关键字不区分字母大小写示例
（4）MySQL显示返回了多少行数据以及执行查询用了多长时间，由此我们大致可以了解服务器的性能。不过这些值是不精确的，因为它们代表的是时钟时间，并且受到服务器负载和网络延迟等因素的影响。
例2.5 使用不同字母大小写进行相同内容的查询。
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;
查询结果如图2-2所示，在查询时，输入的SQL语句中的关键字可以不区分字母大小写，最后输出的答案是一致的，也不会抛出任何异常。
例2.6 把MySQL用作计算器。
执行结果如图2-3所示，MySQL给出了计算结果。
例2.7 用分号分隔多条SQL语句。
执行结果如图2-4所示，可以在一行中输入多条SQL语句（或命令），每条SQL语句之间只需用分号分隔即可。
图2-3 MySQL作为计算器
图2-4 一行执行多条SQL语句
例2.8 MySQL可以写成多行的形式，最后带上终止分号即可。
上述SQL语句的执行结果如图2-5所示。如果一条SQL查询语句冗长，在一行内写不下，那么可以写成多行的形式，最后带上终止分号即可，因为MySQL不是在每一行的末尾去找代表SQL语句结束的分号，而是在多个连续行去找分号来确定语句的结束位置。（MySQL接收自由格式的输入，在看到分号之后才会执行当前的SQL查询语句），当我们不输入分号时，之前输入的命令是不会被执行的。
例2.9 取消查询。
mysql> select
-> users
-> \c
mysql>
执行结果如图2-6所示，当输入“\c”之后，当前输入的命令就被取消执行了。
图2-5 多行查询
图2-6 取消执行当前输入的SQL命令
需要注意的是，在输入取消命令“\c”之后，MySQL回到“mysql>”提示符状态，表示已准备好接收新的查询。表2-11总结了MySQL显示不同提示符时所处的状态。
表2-11 MySQL所处状态的含义
（1）当以单行输入查询语句时，如果忘记以分号结尾，那么MySQL会一直等待我们输入“;”：
mysql> select user()
->
如果发生这种情况（我们认为已经输入完成一条查询语句，而唯一的响应是“->”提示符），那么很可能就是MySQL在等待分号。输入分号之后，MySQL才会开始执行输入的SQL查询语句：
（2）“'>”和“">”提示符表示当前正处于字符串的收集过程中（即表示MySQL正在等待用户输入表示字符串终止的配对符号）。在MySQL中，我们可以编写由一对“'”或一对“"”作为起止的字符串，而MySQL允许输入跨多行的字符串。当看到“'>”或“">”提示符时，表示输入了包含以“'”或“"”开头的字符串，但尚未输入终止字符串的配对引号。例如：
mysql> select * from mytable where name = 'clay and age <18;
'>
如果输入此select语句，然后按Enter键并等待结果，则没有任何反应。此时我们需要注意“'>”提示符提供的线索。它其实是告诉我们：MySQL希望看到字符串的其余部分（语句中clay开始的字符串缺少了第二个单引号）。此时我们可以选择输入“\c”取消本次查询语句的执行：
mysql> select * from mytable where name = clay and age < 18;
'> '\c
mysql>
提示符又变回“mysql>”，表明MySQL再次就绪，可以接收新的查询。
在学习如何创建数据库和表之前，可以使用如下步骤来查询当前服务器中存在的数据库：
使用show语句查询当前服务器上存在哪些数据库：
从以上信息可以了解到，当我们执行show databases语句后，服务器将查询当前存在的所有数据库。
mysql数据库存储了用户访问权限等信息。test数据库是系统提供的默认测试库，可以删除。上述语句显示的数据库列表可能和读者计算机上显示的数据库列表有所不同，因为不会显示当前用户没有访问权限的数据库。
如果mysql数据库存在，那么我们可以进入mysql数据库查询当前数据库中的表。比如，使用如下语句可以切换到mysql数据库：
使用如下语句可以查询当前数据库中的所有表：
执行结果如图2-7所示，首先切换到mysql数据库，然后查询出当前mysql数据库中的所有表。
图2-7 查询mysql数据库中的所有表
如果要创建一个新的数据库，那么可以执行如下语句：
create database DEMO;
执行结果如图2-8所示，创建数据库成功。
图2-8 创建数据库
在UNIX操作系统中，数据库的名称是区分字母大小写的（这与SQL关键字不区分字母大小写不同），因此在进入数据库时，必须使用数据库的名称DEMO来指向正确的数据库，而不能使用Demo、demo。对于表名也是如此（注意，在Windows中此限制不适用）。如图2-9所示，切换数据库时，由于未正确使用大小写而导致切换数据库操作失败。
如图2-10所示，唯有数据库名称的大小写正确才能正常完成数据库的切换。
图2-9 数据库切换操作失败
图2-10 数据库切换操作成功
如果在创建数据库时出现诸如ERROR 1044 (42000): Access denied for user clay@'localhost'的错误，则说明用户没有创建数据库所需的权限。
如图2-11所示，进入DMEO数据库中，此时该数据库中并没有任何表。
图2-11 切换数据库并查询所有表
如果要在DEMO数据库中创建表，那么在创建之前需要了解如下信息：
· 表名：代表数据表的名称，如果我们想要存储用户信息，则可以取名为userinfo。
· 表字段名：表示表中有哪些属性，比如name、age、address等字段。
以下为创建MySQL数据表的通用语法：
create table table_name (column_name column_type);
例2.10 在DEMO数据库中创建数据表userinfo，SQL语句如下：
create table if not exists `userinfo`(
`id` int unsigned auto_increment,
`name` varchar(100) not null,
`age` int not null,
`date` date,
primary key ( `id` ))engine=innodb default charset=utf8;
上述语句说明如下：
· 如果不希望字段的值为null，那么可以将字段的属性设置为not null，如果在操作数据库时将null输入到该字段，则会报错。
· auto_increment：把列定义为自增的属性，一般用于主键，数值会自动加1。
· primary key：用于把列定义为主键，可以使用多列来定义主键，列之间以逗号分隔。
· engine：设置存储引擎。
· charset：设置字符集的编码。
· varchar(100)与int：代表此字段使用的数据类型，后续章节会专门讲解每一种数据类型。
创建结果如图2-12所示。
图2-12 创建表
如果想查询此表的结构，可以执行如下语句：
describe userinfo;
执行结果如图2-13所示，可以清晰地看到此表的结构及其说明。
图2-13 查询表的结构及其说明
此后若再次查询当前数据库中的表，则会显示已创建表的基本信息，如图2-14所示。
图2-14 查询数据库中表的基本信息
1．查看当前系统中所有的数据库。
2．创建名为userinfo的数据库。
3．根据表2-5、表2-6、表2-8、表2-9、表2-10的信息，使用SQL语句在数据库中创建表。