MySQL基础操作

发表于 更新于 本文字数： 14k 阅读时长 ≈ 12 分钟

数据库基础与MySQL安装

数据库

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。这些数据被存放在结构化的数据表里。数据表之间相互关联，反映了客观事物间的本质联系。
数据库种类大概有3种：层次式数据库、网络式数据库和关系式数据库。不同种类的数据库按不同的数据结构来联系和组织。

表

在关系数据库中，数据库表是一系列二维数组的集合，用来存储数据和操作数据的逻辑结构。它由纵向的列和横向的行组成。行被称为记录，是组织数据的单位；列被称为字段，每一列表示记录的一个属性，有相应的描述信息，如数据类型、数据宽度等。
一个有关学生信息的名为students的表中，每列包含所有学生某个特定类型的信息，比如“姓名”，而每行则包含了某个特定学生的所有信息

学号 姓名 性别 年龄 专业
20100803050130 张三 男 18 计算机科学与技术
20100803050131 李四 女 18 软件工程

数据类型

数据类型决定了数据在计算机中的存储格式，代表不同的信息类型。常用的数据类型有整数数据类型、浮点数数据类型、精确小数类型、二进制数据类型、日期/时间数据类型、字符串数据类型。

主键

主键（Primary Key）又称主码，用于唯一地标识表中的每一条记录。可以定义表中的一列或多列为主键，主键列上既不能有两行相同的值，也不能为空值。例如students表中的学号，每个学生都有属于自己的学号，并且学号唯一，此时学号就可以作为主键。姓名不能做主键的原因是有可能多个人具有相同的姓名，这违反了主键的唯一性，因此不能作为主键

SQL语言

对数据库进行查询和修改操作的语言叫作SQL。SQL的含义是结构化查询语言（Structured Query Language）。SQL有许多不同的类型，有3个主要的标准：ANSI（美国国家标准机构）SQL；对ANSI SQL修改后在1992年采纳的标准，称为SQL-92或SQL2；最近的SQL-99标准，从SQL2扩充而来，并增加了对象关系特征和许多其他新功能。各大数据库厂商提供不同版本的SQL，这些版本的SQL不但能包括原始的ANSI标准，而且在很大程度上支持SQL-92标准。

SQL语言包含以下4部分。

  1. 数据定义语言（DDL）：DROP、CREATE、ALTER等语句。
  2. 数据操作语言（DML）：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。
  3. 数据查询语言（DQL）：SELECT语句。
  4. 数据控制语言（DCL）：GRANT、REVOKE、COMMIT、ROLLBACK等语句。

数据库访问接口

不同的编程语言会为不同的数据库提供访问接口，程序通过这些接口执行SQL语句，进行数据库管理，主要的数据库访问接口有ODBC，JDBC，ADO.NET和PDO。

ODBC

ODBC（Open Database Connectivity，开放数据库连接）技术为访问不同的SQL数据库提供了一个共同的接口。ODBC使用SQL作为访问数据的标准。

JDBC

JDBC（Java Data Base Connectivity，Java数据库连接）用于Java应用程序连接数据库的标准方法，是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，由一组用Java语言编写的类和接口组成。

ADO.NET

ADO.NET是微软在.NET框架下开发设计的一组用于和数据源进行交互的面向对象类=库。

PDO

PDO（PHP Data Object）为PHP访问数据库定义了一个轻量级的、一致性的接口，提供了一个数据访问抽象层。这样，无论使用什么数据库，都可以通过一致的函数执行查询和获取数据。

MySQL简介

MySQL是一个小型关系数据库管理系统。与其他大型数据库管理系统（例如Oracle、DB2、SQL Server等）相比，MySQL规模小、功能有限，但是它体积小、速度快、成本低，并且提供的功能对稍微复杂的应用来说已经够用，这些特性使得MySQL成为世界上最受欢迎的开放源代码数据库。

MySQL特点

  1. 速度：运行速度快。
  2. 价格：MySQL对多数个人来说是免费的。
  3. 容易使用：与其他大型数据库的设置和管理相比，其复杂程度较低，易于学习。
  4. 可移植性：能够工作在众多不同的系统平台上，例如Windows、Linux、UNIX、Mac OS等。
  5. 丰富的接口：提供了用于C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等语言的API。
  6. 支持查询语言：MySQL可以利用标准SQL语法和支持ODBC的应用程序。
  7. 安全性和连接性：十分灵活和安全的权限和密码系统，允许基于主机的验证。连接到服务器时，所有的密码传输均采用加密形式，从而保证了密码安全。由于MySQL是网络化的，因此可以在因特网上的任何地方访问，提高数据共享的效率。

MySQL命令简介

MySQL服务端提供了一些命令工具，这些工具都放在MySQL安装目录下的bin目录中：

  1. mysqld：SQL后台程序（MySQL服务器进程）。必须在该程序运行之后，客户端才能通过连接服务器来访问数据库。

  2. mysqld_safe：服务器启动脚本。在UNIX和NetWare中推荐使用mysqld_safe来启动mysqld服务器。mysqld_safe增加了一些安全特性，例如当出现错误时重启服务器并向错误日志文件写入运行时间信息。

  3. mysql.server：服务器启动脚本。该脚本用于使用包含为特定级别的、运行启动服务的脚本的、运行目录的系统。它调用mysqld_safe来启动MySQL服务器。

  4. mysql_multi：服务器启动脚本，可以启动或停止系统上安装的多个服务器。

  5. myisamchk：用来描述、检查、优化和维护MyISAM表的实用工具。

  6. mysqlbug：MySQL缺陷报告脚本。它可以用来向MySQL邮件系统发送缺陷报告。

  7. mysql_install_db：该脚本用默认权限创建MySQL授权表。通常只是在系统上首次安装MySQL时执行一次。同样，MySQL也提供了客户度的一些命令工具，这些工具也放在MySQL安装目录下的bin目录中：

  8. myisampack：压缩MyISAM表，以产生更小的只读表的一个工具。

  9. mysql：交互式输入SQL语句或从文件以批处理模式执行它们的命令行工具。

  10. mysqlaccess：检查访问主机名、用户名和数据库组合的权限的脚本。

  11. mysqladmin：执行管理操作的客户程序，例如创建或删除数据库、重载授权表、将表刷新到硬盘上以及重新打开日志文件。mysqladmin还可以用来检索版本、进程，以及服务器的状态信息。

  12. mysqlbinlog：从二进制日志读取语句的工具。在二进制日志文件中包含执行过的语句，可用来帮助系统从崩溃中恢复。

  13. mysqlcheck：检查、修复、分析以及优化表的表维护客户程序。

  14. mysqldump：将MySQL数据库转储到一个文件（例如SQL语句或tab分隔符文本文件）的客户程序。

  15. mysqlhotcopy：当服务器在运行时，快速备份MyISAM或ISAM表的工具。

  16. mysqlimport：使用LOAD DATA INFILE将文本文件导入相关表的客户程序。

  17. mysqlshow：显示数据库、表、列以及索引相关信息的客户程序。

  18. perror：显示系统或MySQL错误代码含义的工具。

数据库操作

MySQL安装完成后，会自动创建几个必须的数据库，可以使用show databases;查看当前已经存在或者创建的数据库。

创建和删除数据库

数据库可以想象为一个仓库，仓库内存放着所需的数据。

创建数据库

创建数据库就是在硬盘上划分一块区域用于数据的存储和管理，创建数据库的语法格式如下

1
CREATE DATABASE DATABASE_NAME;

例如：创建名称为students_manage的数据库，命令如下：

1
CREATE DATABASE STUDENTS_MANAGE;

当数据库创建成功后会显示Query OK, 1 row affected (0.05 sec)，表明语句执行成功，影响了1行，并输出执行该语句的消耗时长。

删除数据库

删除数据库使用DROP命令，如果要删除某个数据库命令格式如下：
DROP DATABASE DATABASE_NAME;

接下来，删除刚才创建的数据库STUDENTS_MANAGE。命令如下
DROP DATABASES STUDENTS_MANAGE;

数据表的创建和删除

在创建完数据库之后，接下来的工作就是创建数据表。所谓创建数据表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束的过程

创建表

数据库相当于仓库，而表则相当于数据库中不同的存储货物的区域，也就是说数据表数属于仓库的，因此在创建表之前，应该先明确这张表数据哪个数据库。确定数据库可以使用USE语句，例如使用名称为test的数据库，则语句如下：

1
USE TEST;

如果没有选择数据库则会出现“No database selected”的错误。

在选择好要使用的数据库后就可以创建表了，创建表的语法格式如下：

1
2
3
4
5
6
CREATE TABLE TABLE_NAME(
    字段名1 数据类型 [约束] [默认值],
    字段名2 数据类型 [约束] [默认值],
    ....
  	[表级别约束]
 );

在创建表时需要注意以下信息：

  1. 要创建的表的名称，不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
  2. 数据表中每一列（字段）的名称和数据类型，如果创建多列，就要用逗号隔开。
  3. 创建表语句结束后末尾要加“;”，表明该命令已经结束。

例如，创建学生信息表：（关于MySQL数据类型会在第3章详解介绍）

字段名称 数据类型 注释
id INT(11) 学生编号
student_name VARCHAR(30) 学生姓名
student_age INT(3) 学生年龄
student_gender CHAR(2) 学生性别

下面，通过示例来创建数据库和表

  1. 创建测试数据库
1
CREATE DATABASE TEST_DB;
  1. 使用数据库
1
USE TEST_DB;
  1. 创建数据表
1
2
3
4
5
6
CREATE TABLE STUDENT_INFO(
  ID INT(11),
  STUDENT_NAME VARCHAR(25),
  STUDENT_AGE VARCHAR(11),
  STUDENT_GENDER CHAR(2)
 );

依次执行上述SQL语句即可创建好数据表。使用SHOW TABLES;即可查看创建好的表。

数据表基本操作

新增数据

在表中新增数据可以使用INSERT语句。语法格式如下：

1
INSERT INTO TABLE_NAME (FIELD1,FIELD2,...FIELDN) VALUES (VALUE1,VALUES2,...VALUEN);

上述语句中table_name指表名，FILED则是指要插入值的字段名称，VALUE指要插入数据库的值。例如在上述表中插入数据：

1
INSERT INTO STUDENT_INFO (ID,STUDENT_NAME,STUDENT_AGE,STUDENT_GENDER) VALUES (1,'张三',18,'男');

运行上面的语句后对数据库进行查询

在上面的语句中，对表中的所有字段都插入了值，需要注意的是使用该语句是字段列的值和数据值的数量必须相同。如果确定是向表中的所有字段新增值，还有另外一种写法，此种写法可以省略所有的字段名称，但是值的顺序必须和创建表时书写的字段顺序保持一致，示例如下：

1
INSERT INTO STUDENT_INFO VALUES (1,'张三',18,'男');

需要注意的是，此种写法只能是所有字段都插入值的时候才能使用。

通常情况下，新增数据会向表中的所有字段插入值，但是也有一些特殊情况，只向数据表中的某几个字段添加值，此时，只需要写明要新增值的字段名称和对应的值即可(不能省略字段名称)，例如，新增一条数据，学生姓名为李四，性别为女。

1
INSERT INTO STUDENT_INFO (STUDENT_NAME,STUDENT_GENDER) VALUES ('李四','女');

执行该语句后对数据库进行查询，结果如图所示，可以看出没有新增值的字段使用NULL值填充。

删除数据

从数据表中删除数据使用DELETE语句，DELETE语句允许WHERE子句指定删除条件，DELETE语句基本格式如下：

1
DELETE FROM TABLE_NAME WHERE...

table_name指定要执行删除操作的表；“[WHERE <condition>]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。

例如，删除数据表中姓名为李四的数据：

1
DELETE FROM STUDENT_INFO WHERE STUDENT_NAME = "李四";

执行SQL语句后查询数据库，结果如图所示：

img

注意：如果想删除表中的所有记录，还可以使用 TRUNCATE TABLE语句。TRUNCATE将直接删除原来的表，并重新创建一个表，其语法结构为TRUNCATE TABLE table_name。TRUNCATE直接删除表而不是删除记录，因此执行速度比DELETE快。执行DELETE操作时，一定要限定条件，如果不限定条件则会导致整张表中的记录被删除。

修改数据

表中有数据后，还可以对数据进行修改，MySQL中使用UPDATA语句更新表中的记录，可以更新特定的行或者同时更新所有的行。基本语法格式如下：

1
2
UPDATE TABLE_NAME SET COLUMN_NAME1 = VALUE1,CONLUMN_NAME2=VALUE2,...COLUMNN_NAMEN = VALUEN
WHERE ...

column_name1,column_name2,……,column_namen为指定更新的字段的名称；value1, value2,……,valuen为相对应的指定字段的更新值；condition指定更新的记录需要满足的条件。更新多列时，每个“列-值”对之间用逗号隔开，最后一列之后不需要逗号。

例如将表中的张三年龄修改为19：

1
UPDATE STUDENT_INFO SET STUDENT_AGE = 19 WHERE STUDENT_NAME = '张三';

修改完毕后查询数据表：

img

保证UPDATE以WHERE子句结束，通过WHERE子句指定被更新的记录所需要满足的条件，如果忽略WHERE子句，MySQL将更新表中所有的行。

删除数据表

如果要删除数据表也可以使用DROP命令。命令如下：

1
DROP TABLE TABLE_NAME;

接下来删除刚才创建的数据库：

1
DROP TABLE STUDENT_INFO;

删除完数据库后，再次运行SHOW TABLES;命令显示如下：

img

修改表

修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用ALTER TABLE语句修改表。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段等。

修改表名

MySQL是通过ALTER TABLE语句来实现表名的修改的，具体的语法规则如下：

1
ALTER TBALE OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME;

例如，将上述内容中STUDENT_INFO表名修改为STUDENT。

1
ALTER TABLE STUDENT_INFO RENAME TO STUDENT;

修改结束后，执行SHOW TABLES;查看修改结果：

img

修改字段的数据类型

修改字段的数据类型就是把字段的数据类型修改为另一种类型，例如，通常情况下会用字符“男”，“女”表示性别，但是也可以使用整型，用0和1表示，修改字段数据类型的语法规则如下：

1
ALTER TABLE TABLE_NAME MODIFY FIELD_NAME DATATYPE;

其中TABLE_NAME是指修改字段所属的表名，FIELD_NAME是被修改的字段名称，DATATYPE则是指修改后的数据类型。

例如将上述STUDENT表中的SUTDENT_GENDER字段数据类型由char类型修改为int类型：

1
ALTER TABLE STUDENT MODIFY STUDENT_GENDER INT(2);

需要注意的，修改字段数据类型时如果已有数据的数据类型和修改后的数据类型不兼容，则会报错。如图所示

img

针对这种情况，可以先删除表中的数据，再进行修改。

删除表中数据后再次进行修改，结果如图

img

修改结束后可以运行DESC TABLE;命令查看表结构，运行DESC STUDENT;结果如图：

img

从图中可以看出成功修改了字段STUDENT_GENDER的数据类型。

修改字段名

MySQL修改字段名的语法规则如下：

1
ALTER TABLE <TABLE_NAME> CHANGE <OLD_FIELD_NAME> <NEW_FIELD_NAME> <NEW_DATA_TYPE>

将上表中的STUDENT_GENDER字段名称修改为GENDER。

1
ALTER TABLE STUDENT CHANGE STUDENT_GENDER GENDER CHAR(2);

修改完毕后，执行DESC查看表结构

img

从图中可以看出，上述语句不但可以修改字段名称，还可以修改字段数据类型。

CHANGE也可以只修改数据类型，实现和MODIFY同样的效果，方法是将SQL语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。由于不同类型的数据在机器中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录，因此当数据库表中已经有数据时，不要轻易修改数据类型。

添加字段

有时在创建完数据库后或者随着业务的变化，需要在已经存在表中添加新的字段，一个完整字段包括字段名、数据类型、完整性约束。添加字段的语法格式如下：

1
ALTER TABLE <TABLE_NAME> ADD <FIELD_NAME> <DATA_TYPE> [约束] [FIRST|AFTER 已存在字段]

新字段名为需要添加的字段的名称；“FIRST”为可选参数，其作用是将新添加的字段设置为表的第一个字段；“AFTER”为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面。

“FIRST”或“AFTER已存在字段名”用于指定新增字段在表中的位置，如果SQL语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列。

接下来，为STUDENT表新增字段STUDENT_HEIGHT。

1
ALTER TABLE STUDENT ADD STUDENT_HEIGHT FLOAT AFTER GENDER;

执行完上述语句后，查看表结构，如图所示：

img

可以看出在GENDER字段后添加了数据类型为FLOAT的STUDENT_HEIGHT字段。

删除字段

删除字段即将数据表中的某个字段从表中移除。语法格式如下：

1
ALTER TABLE TABLE_NAME DROP FIELD_NAME;

例如，删除表中的STUDENT_HEIGTHT字段。

1
ALTER TABLE STUDENT DROP STUDENT_HEIGTHT;

执行上述语句后查看表结构，如图所示，可以看出已经删除了STUDENT_HEIGHT字段

img

约束

数据库完整性（Database Integrity）是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。数据库完整性由各种各样的完整性约束来保证，因此可以说数据库完整性设计就是数据库完整性约束的设计。在数据库中包含了以下4个完整性：

  1. 实体完整性：实体完整性指表中行的完整性。主要用于保证操作的数据（记录）非空、唯一且不重复。即实体完整性要求每个关系（表）有且仅有一个主键，每一个主键值必须唯一，而且不允许为“空”（NULL）或重复。
  2. 域完整性：是指数据库表中的列必须满足某种特定的数据类型或约束。其中约束又包括取值范围、精度等规定。表中的CHECK、FOREIGN KEY 约束和DEFAULT、 NOT NULL定义都属于域完整性的范畴。
  3. 参照完整性：属于表间规则。对于永久关系的相关表，在更新、插入或删除记录时，如果只改其一，就会影响数据的完整性。如删除父表的某记录后，子表的相应记录未删除，致使这些记录称为孤立记录。对于更新、插入或删除表间数据的完整性，统称为参照完整性。
  4. 自定义完整性：对数据表中字段属性的约束，用户定义完整性规则（User-defined integrity）也称域完整性规则。包括字段的值域、字段的类型和字段的有效规则（如小数位数）等约束，是由确定关系结构时所定义的字段的属性决定的。如，百分制成绩的取值范围在0~100之间等。

主键约束

主键，又称主码，是表中一列或多列的组合。主键约束（Primary KeyConstraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。主键分为两种类型：单字段主键和多字段联合主键。

单字段主键

主键由一个字段组成，SQL语句格式分为以下3种情况。

  1. 在定义列的同时指定主键，语法规则如下：
1
字段名 数据类型 PRIMARY KEY;

新建表并添加主键

1
2
3
4
CREATE TABLE TEMP11(
	ID INT PRIMARY KEY,
  NAME VARCHAR(20)
);
  1. 定义完所有列后指定主键
1
2
3
4
5
CREATE TABLE TEMP11(
	ID INT,
  NAME VARCHAR(20),
  PRIMARY KEY(ID)
);
  1. 在创建完表后添加主键，语法格式如下
1
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(列名);

联合主键

主键由多个字段联合组成，语法规则如下：

1
PRIMARY KEY [FIELD1,FIELD2,...FIELDN]

例如，将上表中ID和NAME作为联合主键：

1
2
3
4
5
CREATE TABLE TEMP11(
  ID INT,
  NAME VARCHAR(20),
  PRIMARY KEY(ID,NAME)
 )

外键约束

外键用来在两个表的数据之间建立连接，可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

外键：首先它是表中的一个字段，虽可以不是本表的主键，但要对应另外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。

主表：具有关联关系的多张表中，被引用主键所在的表叫主表，又叫做父表。

从表：具有关联关系的多张表中，引用某张表主键表叫从表（外键所在的表），又叫做子表。

创建外键的语法规则如下：

1
[CONSTRAINT <外键名>] FOREIGN KEY 字段名1,[字段名2...] REFERENCES <主表名> 主键列1,[主键列2...]

“外键名”为定义的外键约束的名称，一个表中不能有相同名称的外键；“字段名”表示子表需要添加外键约束的字段列；“主表名”即被子表外键所依赖的表的名称；“主键列”表示主表中定义的主键列，或者列组合。

添加外键约束有两种方式：

  1. 在创建表时添加
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE TB_STUDENT(
  ID INT PRIMARY KEY AUTO_INCREMENT,
  USERANEM VARCHAR(20),
  USERPASS VARCHAR(50)
  )
CREATE TABLE TB_STUDENT_INFO(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL,
	TELPHONE VARCHAR(14) NOT NULL,
	ADDRESS VARCHAR(30),
	STUDENT_ID INT,
	CONSTRAINT FK_STUDENT_ID FOREIGN KEY(STUDENT_ID) REFERENCES TB_STUDENT(ID)
)
  1. 表创建结束后添加外键约束
1
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (列名) REFERENCES 被引用表(列名);

注意：当主表的记录被从表记录参照时，主表记录不允许被删除，必须先把从表里参照该记录的所有记录全部删除后，才可以删除主表的该记录。还有一种方式，删除主表记录时级联删除从表中所有参照该记录的从表记录，通过触发器可以实现第二种方式。

非空约束

非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。

非空约束语法规则如下：

1
字段名 数据类型 not null

添加非空约束的方式有两种：

  1. 在创建表时添加
1
2
3
4
CREATE TABLE TEMP11(
	ID INT,
	NAME VARCHAR(20) NOT NULL
)
  1. 创建表结束后添加
1
ALTER TABLE TABLENAME MODIFY COLUMN_NAME DATATYPE NOT NULL ;

从上述代码可以看出，第2种方式其实本质上就是在修改列。

唯一约束

唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

唯一约束的语法规则如下：

1
字段名 数据类型 UNIQUE

添加唯一约束的方式有两种：

  1. 在创建表时添加：
1
2
3
4
CREATE TABLE TEMP11(
	ID INT,
	NAME VARCHAR(20) UNIQUE
)

当某个字段添加唯一约束后该字段的值唯一即不能重复。
2.表创建完成以后添加

1
ALTER TBALE TABLENAME ADD CONSTRAINT 约束名 UNIQUE(列名)

默认约束

默认约束（Default Constraint）指定某列的默认值。如男性同学较多，性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。

默认约束的语法规则如下：

1
字段名 数据类型 DEFAULT 默认值

添加默认约束的方式有2种：

  1. 创建表时添加
1
2
3
4
5
6
CREATE TABLE TEMP11(
	ID INT ,
	NAME VARCHAR(20),
  #该字段默认值是当前时间
	DAY DATETIME DEFAULT NOW() 
)
  1. 创建表结束时添加
1
ALTER TABLE 表名 CHANGE 旧列名 新列名 DATATYPE DEFAULTE 默认值

从代码中可以看出，第2种添加默认约束的方式和修改列名的语法一致。

删除约束

当不再需要约束时，可以删除约束，删除约束的语法格式如下：

1
2
ALTER TABLE 表名 DROP  primary key; //删除主键
ALTER TABLE 表名 drop index 字段名;  //删除唯一约束

设置字段自增加

在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的主键值。可以通过为表主键添加AUTO_INCREMENT关键字来实现。默认的，在MySQL中AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLIN、INT、BIGINT等）。

设置表的属性值自动增加的语法规则如下：

1
字段名 数据类型 AUTO_INCERMENT;

定义数据表temp1,指定字段ID为自增长：

1
2
3
4
CREATE TABLE TEMP1(
  ID INT(11) PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
 );

在数据库中创建好表，并新增两条数据：

1
2
INSERT INTO TEMP1 (NAME) VALUES ("张三");
INSERT INTO TEMP1 (NAME) VALUES ("李四");

执行结束后查询数据库：

img

在插入数据时，并没有为ID设置值，ID值为数据库自动生成并插入。每条数据的ID都比前一条数据的值大1.

查看表结构

DESCRIBE/DESC语句可以查看表的字段信息，其中包括字段名、字段数据类型、是否为主键、是否有默认值等。语法规则如下：

1
DESC 表名;

例如，查看temp1的表结构

img

其中，各个字段的含义分别解释如下：

  • NULL：表示该列是否可以存储NULL值。
  • Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
  • Default：表示该列是否有默认值，有的话指定值是多少。
  • Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

数据库备份与恢复

数据备份是数据库管理员非常重要的工作之一。系统意外崩溃或者硬件的损坏都可能导致数据库的丢失，因此MySQL管理员应该定期地备份数据库，使得在意外情况发生时，尽可能减少损失。

使用MySQLdump命令备份

MySQLdump是MySQL提供的一个非常有用的数据库备份工具。MySQLdump命令执行时，可以将数据库备份成一个文本文件，该文件中实际包含了多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。MySQLdump备份数据库语句的基本语法格式如下：

1
mysqldump -u user -p dbname tbname1 tbname2 ... tbnamen >filename.sql;

user表示用户名称；password为登录密码；dbname为需要备份的数据库名称；tbname为dbname数据库中需要备份的数据表，可以指定多个需要备份的表，多个表之间用空格分隔；右箭头符号“>”告诉MySQLdump将备份数据表的定义和数据写入备份文件；filename.sql为备份文件的名称。如果要备份整个数据库则省略表名即可。

注意：要保证保存文件的路径存在，否则会提示错误。

恢复数据

对于已经备份的包含CREATE、INSERT语句的文本文件，可以使用MySQL命令导入到数据库中。本小节将介绍MySQL命令导入sql文件的方法。备份的sql文件中包含CREATE、INSERT语句（有时也会有DROP语句）。MySQL命令可以直接执行文件中的这些语句。其语法如下：

1
mysql -u user -p dbname < filename.sql

user是执行backup.sql中语句的用户名；-p表示输入用户密码；dbname是数据库名。

如果已经登录MySQL服务器，还可以使用source命令导入sql文件。source语句语法如下：

1
source filename;

数据库设计范式

设计出性能良好、结构合理且符合业务的需求的数据库及数据表是需要具备一定经验及技巧的，并且还需要遵循一定的规则，在关系型数据库中这种规则就叫做范式，数据库设计总共有6大范式，一般设计数据库满足三大范式即可。

第一范式

第一范式是最基本的范式，指在关系模型中，所有的列都应该是原子列，即数据库表的每一列都是不可分割的原子数据项。

例如下面的数据表就是一个反例。

student_id student_name student_age address
1 张三 18 陕西省西安市未央区明光路11号
2 李四 20 陕西省西安市未央区明光路21号

第二范式

第二范式是指在满足第一范式的基础上，实体的属性完全依赖于主键列，而不能只能与主键的某一部分相关（这种情况一般出现在表中有联合主键的情况）。即在一个数据库表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。例如下表就违反了第二范式：

student_num student_name course_num course_name
20181101 张三 09001 Java
20181101 张三 09002 MySQL
20181102 李四 09001 Java
20181104 赵六 09002 MySQL

在上表中需要学生编号和课程编号作为联合主键，课程名称值依赖于课程编号，而和学号没有关系，随着数据的增加，会产生大量的冗余数据。

可以对上表进行分割：

表1：学生表

student_num student_name
20181101 张三
20181102 李四
20181103 王五
20181104 赵六

表2：课程表

course_num course_name
09001 Java
09002 MySQL

表3：选课表

student_num course_num
20181101 09001
20181101 09002
20181102 09001
20181103 09002

2.8.3 第三范式
第三范式是在满足第二范式的基础上，任何非主键列都与主键列直接相关，不能间接相关，例如下表：

order_num good_id good_price good_count good_total_price
20201001002 1001 18.0 2 36
20201001003 1002 18.0 3 54

上表是一个订单表，每行数据是一个订单，表中的good_total_price和订单主键订单号没有直接关键，因为该字段是依赖good_price和good_count计算而来的，所以违反了第三范式。对上表进行修改。