【MySQL】MySQL数据库与表的基本概念及实践

目录

引言

数据库（Database）

表（Table）

插入数据

查询数据

基本查询

条件查询

排序查询结果

更新数据

更新特定记录

删除数据

删除特定记录

复杂查询

聚合函数

多表查询

索引

创建索引

删除索引

视图

创建视图

查询视图

---

引言

        MySQL 是一种广泛使用的关系型数据库管理系统（RDBMS），它以其高性能、可靠性、易用性和开源性等特点，成为众多Web应用、企业应用以及云服务的首选数据库。在MySQL中，数据库（Database）是数据的集合，而表（Table）则是数据库中存储数据的基本单位。本文将介绍MySQL中数据库与表的基本概念，并通过实践示例来加深理解。

数据库（Database）

        数据库是存储数据的地方，它按照数据结构来组织、存储和管理数据。在MySQL中，创建数据库是进行数据管理的第一步。创建数据库的基本语法如下：

CREATE DATABASE IF NOT EXISTS database_name;

• CREATE DATABASE 是用来创建数据库的SQL命令。
• IF NOT EXISTS 是可选的，用于防止在数据库已存在时发生错误。
• database_name 是你想要创建的数据库的名称。

例如，创建一个名为 test_db 的数据库：

CREATE DATABASE IF NOT EXISTS test_db;

表（Table）

        表是数据库中用于存储数据的二维结构，由行（Row）和列（Column）组成。每一行代表一条记录，每一列代表记录中的一个字段。创建表之前，需要先选择或创建一个数据库，因为表是存储在数据库中的。

创建表的基本语法如下：

CREATE TABLE IF NOT EXISTS table_name (  
    column1 datatype constraints,  
    column2 datatype constraints,  
    column3 datatype constraints,  
    ...  
);

• CREATE TABLE 是用来创建表的SQL命令。
• IF NOT EXISTS 同样是可选的，用于防止在表已存在时发生错误。
• table_name 是你想要创建的表的名称。
• column1, column2, ... 是表中的列名。
• datatype 是列的数据类型，如 INT, VARCHAR, DATE 等。
• constraints 是对列的约束，如 NOT NULL, UNIQUE, PRIMARY KEY 等。

例如，在 test_db 数据库中创建一个名为 students 的表，包含学生的ID、姓名和年龄：

USE test_db; -- 选择数据库  
  
CREATE TABLE IF NOT EXISTS students (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(100) NOT NULL,  
    age INT NOT NULL  
);

• USE test_db; 用于选择 test_db 数据库，之后创建的表将存储在这个数据库中。
• id 列被设置为自增主键，意味着每当你插入新记录时，MySQL会自动为该列分配一个唯一的值。
• name 和 age 列分别用于存储学生的姓名和年龄，NOT NULL 约束表示这些字段在插入记录时不能留空。

插入数据

创建表之后，可以向表中插入数据。插入数据的基本语法如下：

INSERT INTO table_name (column1, column2, column3, ...)  
VALUES (value1, value2, value3, ...);

例如，向 students 表中插入一条记录：

INSERT INTO students (name, age)  
VALUES ('张三', 20);

查询数据

查询数据是数据库操作中最常见的任务之一。在MySQL中，使用SELECT语句来检索表中的数据。

基本查询

查询students表中所有学生的姓名和年龄：

SELECT name, age FROM students;

条件查询

使用WHERE子句来指定查询条件，只检索满足条件的记录。

查询年龄大于18岁的所有学生：

SELECT name, age FROM students WHERE age > 18;

排序查询结果

使用ORDER BY子句对查询结果进行排序。

按年龄升序查询学生信息：

SELECT name, age FROM students ORDER BY age ASC;

按年龄降序查询学生信息：

SELECT name, age FROM students ORDER BY age DESC;

更新数据

使用UPDATE语句来修改表中的数据。

更新特定记录

将ID为1的学生的年龄更新为21岁：

UPDATE students SET age = 21 WHERE id = 1;

删除数据

使用DELETE语句来删除表中的数据。

删除特定记录

删除ID为1的学生记录：

DELETE FROM students WHERE id = 1;

警告：DELETE操作是危险的，因为它会永久删除数据。务必谨慎使用，并确保有备份。

复杂查询

聚合函数

使用聚合函数（如COUNT(), SUM(), AVG(), MAX(), MIN()）来对数据进行汇总。

查询每个年龄的学生数量：

SELECT age, COUNT(*) as count FROM students GROUP BY age;

多表查询

当数据分布在多个表中时，可以使用JOIN语句来结合这些表的数据。

假设我们有一个courses表，包含课程信息，和一个enrollments表，包含学生选课信息。

查询选课学生的姓名和课程名称：

SELECT students.name, courses.name AS course_name  
FROM students  
JOIN enrollments ON students.id = enrollments.student_id  
JOIN courses ON enrollments.course_id = courses.id;

索引

索引是帮助MySQL快速访问表中数据的数据结构。它们类似于书籍的目录，可以大大提高查询速度。

创建索引

在students表的name列上创建一个索引：

CREATE INDEX idx_name ON students(name);

删除索引

删除上面创建的索引：

DROP INDEX idx_name ON students;

视图

视图是基于SQL语句的结果集的可视化的表。视图可以包含表中的行或行与行之间的连接。

创建视图

创建一个名为view_older_students的视图，包含年龄大于18岁的学生信息：

CREATE VIEW view_older_students AS  
SELECT name, age FROM students WHERE age > 18;

查询视图

查询view_older_students视图中的数据：

SELECT * FROM view_older_students;