【MySQL新手入门系列二】：手把手教你入门MySQL - 数据库及数据表操作

分手后的思念是犯贱 2023-10-12 13:40 42阅读 0赞

如果您是一位刚刚开始学习MySQL的新手，本文将为您提供一些实用的入门知识和技巧，帮助您快速上手。  
[【MySQL新手入门系列一】：手把手教你入门MySQL][MySQL_MySQL]  
前面我们已经大致讲了一下mysql的安装等介绍，本篇文章将以windows为例，介绍MySQL的数据库及数据表的操作（增删改查）。

#### 目录 ####

*   *  一、概览
     *  二、MySQL数据库操作
     *   *  2.1 创建数据库
         *  2.2 查看所有数据库
         *  2.3 修改数据库
         *  2.4 删除数据库
     *  三、MySQL 数据表操作
     *   *  3.1 学习如何创建表
         *  3.2 学习如何删除表
         *  3.3 学习如何修改表结构
         *  3.4 数据类型介绍和使用DATE、TIME、DATETIME和TIMESTAMP等时间数据类型
         *  3.5 如何使用SQL约束来保护数据的完整性
         *  3.6 表的备份与恢复

### 一、概览 ###

在MySQL安装完成后，接下来，我们就要创建数据库及创建数据表。

### 二、MySQL数据库操作 ###

MySQL 安装好之后就可以进行数据库的相关操作，这时候，是没有我们自己使用的数据库的，所以我们得先新建一个我们自己的数据库。

#### 2.1 创建数据库 ####

语法：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
    [[DEFAULT] CHARACTER SET <字符集名>] 
    [[DEFAULT] COLLATE <校对规则名>];

语法说明如下：

> 1、<数据库名>：创建数据库的名称，不能以数字开头  
> 2、IF NOT EXISTS：在创建数据库之前进行判断，当数据库不存在时，才会执行创建语句。  
> 3、\[DEFAULT\] CHARACTER SET：指定数据库的字符集。目的是为了避免在数据库中存储的数据出现乱码的情况。  
> 4、\[DEFAULT\] COLLATE：指定字符集的默认校对规则。

我们来举个例子，现在有个书店，要存书书相关的信息，创建一个新的数据库，命名为 “bookstore”：

CREATE DATABASE IF NOT EXISTS bookstore
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

如果不想设置一些属性，可直接用下面简单的语句即可：

CREATE DATABASE bookstore;

当看到

“Query OK, 1 row affected (0.32 sec);”

提示中，“Query OK”表示命令执行成功，“0.32 sec” 表示执行的时间。

#### 2.2 查看所有数据库 ####

实用语法

> SHOW DATABASES \[LIKE ‘数据库名’\];

如：  
![在这里插入图片描述][aedd0d1ca20340dc9766e5ef212cdb72.png]  
第一个就是我们刚刚创建的bookstore

#### 2.3 修改数据库 ####

*  语法：

ALTER DATABASE [数据库名] { 
    [ DEFAULT ] CHARACTER SET <字符集名> |
    [ DEFAULT ] COLLATE <校对规则名>}

下面是一个实例 SQL 语句，用于修改一个名为 exampleDB 的数据库的默认字符集和校对规则

ALTER DATABASE exampleDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

这个 SQL 语句会将 exampleDB 的默认字符集和校对规则修改为 utf8mb4 和 utf8mb4\_unicode\_ci，同时保留该数据库中原有的数据。

*  还可以**重命名**数据库：

ALTER DATABASE exampleDB RENAME TO newDBName;

将名为 exampleDB 的数据库修改为名为 newDBName。需要注意的是，在修改数据库名称过程中，你需要关闭该数据库的所有连接，否则会报错。

#### 2.4 删除数据库 ####

*  语法

DROP DATABASE [ IF EXISTS ] <数据库名>

其中，database\_name 表示要删除的数据库的名称。如果指定了 IF EXISTS，那么在删除一个不存在的数据库时，不会报错而是静默返回。

### 三、MySQL 数据表操作 ###

数据表是数据库的重要组成部分，我们上面介绍了如何新建一个数据库。下面介绍表相关操作。

#### 3.1 学习如何创建表 ####

*  语法

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];
    
    CREATE TABLE table_name (
        column1 datatype [constraint],
        column2 datatype [constraint],
        column3 datatype [constraint],
        ...
        PRIMARY KEY (one or more columns)
    );

其中，table\_name 表示要创建的表的名称。column1, column2, column3 等则表示表中的列名，datatype 表示该列的数据类型。如果不需要设置约束，则可以省略 \[constraint\]。PRIMARY KEY 用于指定表中的主键，可以需要设置一个或多个列作为主键，以保证表中的数据唯一性。

*  举例说明：  
    创建一个新的数据表，命名为 “books”，包括图书编号、图书名称、作者和价格：

CREATE TABLE books(
      id INT(11) NOT NULL AUTO_INCREMENT,
      title VARCHAR(255) DEFAULT NULL,
      author VARCHAR(255) DEFAULT NULL,
      price DECIMAL(10,2) DEFAULT NULL,
      PRIMARY KEY(id)
    );

#### 3.2 学习如何删除表 ####

*  语法

DROP TABLE [IF EXISTS] table_name;

其中，table\_name 表示要删除的表的名称。如果指定了 IF EXISTS，那么在删除一个不存在的表时，不会报错而是静默返回。

*  举例说明：

删除名为 users 的数据库表
    DROP TABLE users;

这个 SQL 语句会删除名为 users 的数据库表及其中的所有数据。

#### 3.3 学习如何修改表结构 ####

*  语法

ALTER TABLE <表名> [修改选项]；
    
    ALTER TABLE table_name 
        ADD [COLUMN] column_name data_type [NULL | NOT NULL] [DEFAULT default_value] [AFTER | FIRST column_name],
        DROP [COLUMN] column_name,
        MODIFY [COLUMN] column_name new_data_type [NULL | NOT NULL] [DEFAULT default_value] [FIRST | AFTER column_name],
        RENAME [COLUMN] old_column_name TO new_column_name,
        RENAME TO new_table_name;

参数比较多，其中，ADD 可以添加一个新的列，DROP 可以删除一个已经存在的列，MODIFY 可以修改一个已经存在的列的数据类型等属性，RENAME 可以重命名一个已经存在的列或者整个表。

*  举例说明：

添加一个新列 age 到一个名为 users 的表中
    ALTER TABLE users ADD COLUMN age INT(11) AFTER email;

删除一个名为 age 的列
    ALTER TABLE users DROP COLUMN age;

修改一个名为 username 的列的数据类型和默认值
    ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL DEFAULT 'anonymous' AFTER id;

将一个名为 name 的列重命名为 full_name
    ALTER TABLE users RENAME COLUMN name TO full_name;

将一个名为 users 的表重命名为新名称 customers
    ALTER TABLE users RENAME TO customers;

#### 3.4 数据类型介绍和使用DATE、TIME、DATETIME和TIMESTAMP等时间数据类型 ####

在 SQL 中，有许多不同的数据类型，包括数字类型、字符类型、日期时间类型等等。下面简要介绍一些常见的数据类型：

*  `INT` / `INTEGER`：整数类型
 *  `VARCHAR`：可变长度字符串类型
 *  `CHAR`：定长字符串类型
 *  `TEXT`：文本字符串类型
 *  `DECIMAL` / `NUMERIC`：定点数类型
 *  `FLOAT` / `DOUBLE`：浮点数类型
 *  `DATE`：日期类型，仅精确到日期
 *  `TIME`：时间类型，仅精确到时分秒
 *  `DATETIME`：日期时间类型，精确到毫秒
 *  `TIMESTAMP`：时间戳类型，精确到秒

下面是使用不同时间数据类型的示例 SQL 语句：

*  使用 `DATE` 存储日期数据
    
        CREATE TABLE sales (
            id INT(11) NOT NULL AUTO_INCREMENT,
            customer_id INT(11) NOT NULL,
            order_date DATE NOT NULL,
            total_amount DECIMAL(10,2) NOT NULL,
            PRIMARY KEY (id)
        );
    
    `order_date` 列被指定为 `DATE` 类型，因此它只精确到日期，而不包括时间。
 *  使用 `TIME` 存储时间数据
    
        CREATE TABLE employees (
            id INT(11) NOT NULL AUTO_INCREMENT,
            name VARCHAR(50) NOT NULL,
            start_time TIME NOT NULL,
            PRIMARY KEY (id)
        );
    
    start\_time`列被指定为`TIME\` 类型，因此它只精确到时分秒，而不包括日期。
 *  使用 `DATETIME` 存储日期时间数据
    
        CREATE TABLE log (
            id INT(11) NOT NULL AUTO_INCREMENT,
            access_time DATETIME NOT NULL,
            user_id INT(11) NOT NULL,
            PRIMARY KEY (id)
        );
    
    access\_time`列被指定为`DATETIME\` 类型，因此它包括日期和时间，精确到毫秒。
 *  使用 `TIMESTAMP` 存储时间戳数据
    
        CREATE TABLE bookings (
            id INT(11) NOT NULL AUTO_INCREMENT,
            checkin_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            checkout_time TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:01',
            room_id INT(11) NOT NULL,
            PRIMARY KEY (id)
        );
    
    checkin\_time`和`checkout\_time`列都被指定为`TIMESTAMP`类型，因此它们包括日期和时间，精确到秒。其中，`checkin\_time`的默认值被设置为当前时间戳，`checkout\_time`的默认值被设置为`’1970-01-01 00:00:01’\`，代表最小时间戳值。

需要注意的是，在不同的数据库管理系统中，时间数据类型可能会有所不同，例如 `DATETIME` 和 `TIMESTAMP` 的差异。此外，还需要仔细考虑如何处理时区、闰秒等问题。在实际使用中，建议参考相关的文档和标准，并遵守最佳实践。

#### 3.5 如何使用SQL约束来保护数据的完整性 ####

在 SQL 中，可以使用约束（Constraint）来限制或保护表中的数据完整性。这些约束包括列级约束和表级约束。

列级约束是针对单个列的约束，例如 NOT NULL、UNIQUE、PRIMARY KEY 和 FOREIGN KEY 约束。表级约束是针对多个列的约束，例如 CHECK 约束。

下面是一些常见的 SQL 约束以及如何使用它们来保护数据的完整性：

*  `NOT NULL` 约束：用于确保列中的值不为空。
    
        CREATE TABLE Users (
            ID INT NOT NULL,
            FirstName VARCHAR(50) NOT NULL,
            LastName VARCHAR(50) NOT NULL,
            Email VARCHAR(100) NOT NULL,
            CONSTRAINT PK_Users PRIMARY KEY (ID)
        );
    
    `ID`、`FirstName`、`LastName` 和 `Email` 列都被设置为 `NOT NULL`，以确保这些列中的值不能为空。
 *  `UNIQUE` 约束：用于确保列中的值是唯一的。
    
        CREATE TABLE Products (
            ID INT NOT NULL,
            Name VARCHAR(100) NOT NULL,
            Description TEXT,
            SKU VARCHAR(50) UNIQUE,
            CONSTRAINT PK_Products PRIMARY KEY (ID)
        );
    
    `SKU` 列被设置为 `UNIQUE`，以确保每个产品都有唯一的 SKU 编号。
 *  `PRIMARY KEY` 约束：用于指定一个或多个列是表的主键，确保每行都有唯一的标识符。
    
        CREATE TABLE Orders (
            ID INT PRIMARY KEY,
            CustomerID INT NOT NULL,
            OrderDate DATETIME DEFAULT NULL,
            CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
        );
    
    `ID` 列被设置为 `PRIMARY KEY`，以确保每个订单都有唯一的标识符。
 *  `FOREIGN KEY` 约束：用于确保列中的值与另一个表中的列的值匹配。
    
        CREATE TABLE OrderDetails (
            ID INT PRIMARY KEY,
            OrderID INT NOT NULL,
            ProductID INT NOT NULL,
            Quantity INT NOT NULL,
            CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY (OrderID) REFERENCES Orders(ID),
            CONSTRAINT FK_OrderDetails_Products FOREIGN KEY (ProductID) REFERENCES Products(ID)
        );
    
    OrderID`和`ProductID`列都被设置为`FOREIGN KEY\`，以确保订单详情行与订单和产品表中的正确行匹配。
 *  `CHECK` 约束：用于确保列中的值符合特定条件。
    
        CREATE TABLE Employees (
            ID INT PRIMARY KEY,
            FirstName VARCHAR(50) NOT NULL,
            LastName VARCHAR(50) NOT NULL,
            HireDate DATE,
            Salary DECIMAL(10,2) NOT NULL,
            CONSTRAINT CK_Employees_Salary CHECK (Salary > 0)
        );
    
    Salary`列被设置为`CHECK\` 约束，以确保每个员工的工资大于 0。

通过使用约束，可以在 SQL 中保护数据的完整性和合法性。当尝试向表中插入或更新数据时，如果违反任何约束，则 SQL 将抛出错误，阻止事务的提交。

#### 3.6 表的备份与恢复 ####

在 SQL 中，我们可以使用备份和还原功能来保存和恢复数据库中的数据。

MySQL 数据库也提供了备份和恢复功能，可以使用 `mysqldump` 命令进行备份，使用 `mysql` 命令进行还原。下面是备份和恢复 MySQL 数据库表的语法及示例：

*  备份MySQL表
    
        /*备份整个数据库*/ 
        mysqldump -u root -p database_name > backup_file.sql 
        
        /*备份单个表结构和数据*/
        mysqldump -u root -p database_name table_name > backup_file.sql 
        
        /*备份单个表结构*/
        mysqldump -u root -p -d database_name table_name > backup_file.sql
    
    在执行备份命令时，`-u` 表示登录 MySQL 数据库的用户名，`-p` 表示在输入密码时提示，`database_name` 表示需要备份的数据库名，`table_name` 表示需要备份的数据库表名。备份文件 `backup_file.sql` 可以自行决定，同时也可以指定文件保存目录。
 *  恢复MySQL表
    
        /*从备份文件中还原整个数据库*/ 
        mysql -u root -p database_name < backup_file.sql 
        
        /*从备份文件中还原单个表结构和数据*/
        mysql -u root -p database_name < backup_file.sql 
        
        /*从备份文件中还原单个表结构*/
        mysql -u root -p database_name < backup_file.sql
    
    还原命令中的语法和备份命令大致相同，唯一的区别在于把备份文件作为标准输入来还原表格。在执行还原操作时，需要注意确保当前数据库表和备份文件中的表名和结构一致。

需要注意的是，MySQL 备份和恢复命令通常需要管理员权限，且执行时间较长，同时也需要考虑备份文件的安全和管理。在实际使用中，需要仔细考虑备份和恢复策略，并遵守相关的数据库安全和最佳实践。

[MySQL_MySQL]: http://t.csdn.cn/jlOwA
[aedd0d1ca20340dc9766e5ef212cdb72.png]: https://img-blog.csdnimg.cn/aedd0d1ca20340dc9766e5ef212cdb72.png