第 2 章：数据库与表的基本操作

在上一章中，我们学习了数据库的基本概念和 MySQL 的安装配置。现在我们开始学习如何创建和管理数据库与数据表，这是使用 MySQL 的基础操作。

本章内容概要

• 登录与退出 MySQL
• 数据库的创建、查看、选择与删除
• 表的创建、查看、修改与删除
• 数据类型详解
• 表结构设计基础
• 实战练习：创建学生管理系统数据库

登录与退出 MySQL

登录 MySQL

要使用 MySQL，首先需要登录到 MySQL 服务器：

mysql -u root -p

• -u root 指定用户名为 root
• -p 表示需要输入密码

执行命令后，系统会提示输入密码，输入正确密码后即可进入 MySQL 命令行界面。

退出 MySQL

在 MySQL 命令行界面中，可以使用以下任一命令退出：

EXIT;
-- 或者
QUIT;
-- 或者
\q;

数据库的基本操作

查看所有数据库

登录 MySQL 后，可以查看系统中所有的数据库：

SHOW DATABASES;

执行结果可能如下：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

这些是 MySQL 系统自带的数据库，我们将在后续章节中介绍它们的作用。

创建数据库

使用 CREATE DATABASE 命令创建新数据库：

-- 基本语法
CREATE DATABASE 数据库名;

-- 示例
CREATE DATABASE school_db;

为了避免数据库重名错误，可以使用以下语法：

-- 如果数据库不存在则创建
CREATE DATABASE IF NOT EXISTS school_db;

可以指定字符集和排序规则：

CREATE DATABASE school_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

选择数据库

创建数据库后，需要选择要操作的数据库：

USE school_db;

查看当前选择的数据库

SELECT DATABASE();

删除数据库

使用 DROP DATABASE 命令删除数据库：

-- 基本语法
DROP DATABASE 数据库名;

-- 示例
DROP DATABASE school_db;

为了避免尝试删除不存在的数据库而出错，可以使用：

DROP DATABASE IF EXISTS school_db;

数据表的基本操作

MySQL 数据类型详解

在创建表之前，我们需要了解 MySQL 支持的主要数据类型：

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128, 127)	(0, 255)	小整数值
SMALLINT	2 字节	(-32768, 32767)	(0, 65535)	大整数值
MEDIUMINT	3 字节	(-8388608, 8388607)	(0, 16777215)	大整数值
INT	4 字节	(-2147483648, 2147483647)	(0, 4294967295)	大整数值
BIGINT	8 字节	(-2^63, 2^63-1)	(0, 2^64-1)	极大整数值
FLOAT	4 字节	单精度浮点数	单精度浮点数	单精度浮点数值
DOUBLE	8 字节	双精度浮点数	双精度浮点数	双精度浮点数值
DECIMAL	可变	依赖于M和D	依赖于M和D	小数值

日期和时间类型

类型	大小	范围	格式	用途
DATE	3 字节	1000-01-01 到 9999-12-31	YYYY-MM-DD	日期值
TIME	3 字节	-838:59:59 到 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1 字节	1901 到 2155	YYYY	年份值
DATETIME	8 字节	1000-01-01 00:00:00 到 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4 字节	1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
CHAR	0-255 字符	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYTEXT	0-255 字节	短文本字符串
TEXT	0-65535 字节	长文本数据
MEDIUMTEXT	0-16777215 字节	中等长度文本数据
LONGTEXT	0-4294967295 字节	极大文本数据
BINARY	0-255 字节	定长二进制字符串
VARBINARY	0-65535 字节	变长二进制字符串

创建数据表

使用 CREATE TABLE 命令创建数据表：

CREATE TABLE 表名 (
    列名1 数据类型 [约束条件],
    列名2 数据类型 [约束条件],
    ...
);

让我们创建一个学生表作为示例：

USE school_db;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED,
    gender ENUM('男', '女') DEFAULT '男',
    email VARCHAR(100) UNIQUE,
    birth_date DATE,
    enrollment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

在这个例子中，我们使用了以下约束条件：

• AUTO_INCREMENT - 自动递增
• PRIMARY KEY - 主键约束
• NOT NULL - 非空约束
• UNIQUE - 唯一约束
• DEFAULT - 默认值
• UNSIGNED - 无符号（只能为正数）

查看表结构

创建表后，可以使用以下命令查看表结构：

-- 查看表结构
DESCRIBE students;
-- 或者简写为
DESC students;

也可以使用：

SHOW COLUMNS FROM students;

查看所有表

查看当前数据库中的所有表：

SHOW TABLES;

查看创建表的语句

如果想查看创建表时使用的 SQL 语句：

SHOW CREATE TABLE students;

修改表结构

使用 ALTER TABLE 命令修改表结构：

添加列

-- 添加单列
ALTER TABLE students ADD COLUMN phone VARCHAR(20);

-- 添加多列
ALTER TABLE students 
ADD COLUMN address VARCHAR(200),
ADD COLUMN major VARCHAR(50);

修改列

-- 修改列定义
ALTER TABLE students MODIFY COLUMN phone VARCHAR(15);

-- 修改列名和定义
ALTER TABLE students CHANGE COLUMN phone telephone VARCHAR(15);

删除列

ALTER TABLE students DROP COLUMN address;

重命名表

-- 方法一
ALTER TABLE students RENAME TO student_info;

-- 方法二
RENAME TABLE student_info TO students;

删除表

使用 DROP TABLE 命令删除表：

-- 基本语法
DROP TABLE 表名;

-- 示例
DROP TABLE students;

为了避免删除不存在的表而出错：

DROP TABLE IF EXISTS students;

表结构设计基础

在设计表结构时，需要考虑以下几个原则：

1. 第一范式（1NF）

确保每列都是不可分割的原子值。

2. 合理选择数据类型

• 选择合适的数据类型可以节省存储空间
• 提高查询效率
• 避免数据溢出

3. 设置适当的约束

• 主键约束保证记录唯一性
• 非空约束保证数据完整性
• 唯一约束防止重复数据

4. 合理使用默认值

为常用值设置默认值，减少数据录入工作量。

实战练习：创建学生管理系统数据库

让我们通过一个完整的例子来练习数据库和表的操作。

1. 创建数据库

CREATE DATABASE IF NOT EXISTS student_management 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE student_management;

2. 创建学生表

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',
    student_number VARCHAR(20) NOT NULL UNIQUE COMMENT '学号',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    gender ENUM('男', '女') DEFAULT '男' COMMENT '性别',
    birth_date DATE COMMENT '出生日期',
    phone VARCHAR(20) COMMENT '联系电话',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    address VARCHAR(200) COMMENT '地址',
    enrollment_date DATE NOT NULL COMMENT '入学日期',
    major VARCHAR(50) COMMENT '专业',
    class_name VARCHAR(50) COMMENT '班级',
    is_active BOOLEAN DEFAULT TRUE COMMENT '是否在读',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) COMMENT '学生表';

3. 创建课程表

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '课程ID',
    course_code VARCHAR(20) NOT NULL UNIQUE COMMENT '课程代码',
    course_name VARCHAR(100) NOT NULL COMMENT '课程名称',
    credits TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '学分',
    description TEXT COMMENT '课程描述',
    teacher VARCHAR(50) COMMENT '授课教师',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) COMMENT '课程表';

4. 创建成绩表

CREATE TABLE grades (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成绩ID',
    student_id INT NOT NULL COMMENT '学生ID',
    course_id INT NOT NULL COMMENT '课程ID',
    grade DECIMAL(5,2) COMMENT '成绩',
    exam_date DATE COMMENT '考试日期',
    semester VARCHAR(20) COMMENT '学期',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
) COMMENT '成绩表';

5. 查看创建的表

SHOW TABLES;

6. 查看表结构

DESCRIBE students;
DESCRIBE courses;
DESCRIBE grades;

小结

在本章中，我们学习了：

1. 数据库操作 - 创建、查看、选择和删除数据库
2. 数据类型 - 数值型、字符串型、日期时间型等主要数据类型
3. 表操作 - 创建、查看、修改和删除数据表
4. 表结构设计 - 基本的设计原则和约束条件
5. 实战练习 - 创建学生管理系统的数据库和相关表

下一章我们将学习数据的基本操作，包括如何向表中插入数据、查询数据、更新数据和删除数据。