【MySQL】表操作和库操作

概念

MySQL 是一种开源的关系型数据库管理系统，被广泛应用于网站开发、数据分析等领域。在 MySQL 中，我们经常需要使用一些常用的指令来管理数据库和数据表，比如创建数据库、创建数据表、插入数据、更新数据、查询数据等。本篇博客将会介绍一些常用的 MySQL 指令，并给出详细的语法和示例。

---

库操作

库（Database）是指一组相关的数据表的集合。一个库可以包含多个数据表，每个数据表可以包含多行多列的数据。库的操作是管理 MySQL 中数据的重要手段之一。

1.创建数据库

在 MySQL 中，我们可以使用 CREATE DATABASE 指令来创建一个新的数据库。其语法如下：

CREATE DATABASE database_name;

其中，database_name 为所要创建的数据库的名称。

例如，如果我们要创建一个名为 mydatabase 的数据库，可以使用以下指令：

CREATE DATABASE mydatabase;

---

2.删除数据库

如果我们要删除一个已经存在的数据库，可以使用 DROP DATABASE 指令。其语法如下：

DROP DATABASE database_name;

其中，database_name 为所要删除的数据库的名称。

例如，如果我们要删除名为 mydatabase 的数据库，可以使用以下指令：

DROP DATABASE mydatabase;

---

3.选择数据库

在 MySQL 中，我们可以使用 USE 指令来选择一个已经存在的数据库。其语法如下：

USE database_name;

其中，database_name 为所要选择的数据库的名称。

例如，如果我们要选择名为 mydatabase 的数据库，可以使用以下指令：

USE mydatabase;

---

4.显示数据库列表

如果我们想查看所有的数据库列表，可以使用 SHOW DATABASES 指令。其语法如下：

SHOW DATABASES;

例如，如果我们想查看所有的数据库列表，可以使用以下指令：

SHOW DATABASES;

---

表操作

表（Table）是指一组具有相同数据结构的数据集合。每个表由多个列组成，每列定义了表中的一种数据类型。表用于存储和组织数据，是 MySQL 数据库的核心组成部分。表的操作是管理 MySQL 中数据的基本手段之一。

1.创建数据表CREATE

在 MySQL 中，我们可以使用 CREATE TABLE 指令来创建一个新的数据表。其语法如下：

CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype,...
);

其中，table_name 为所要创建的数据表的名称，column1、column2、column3 等为数据表中的列名，datatype 为数据类型。

例如，如果我们要创建一个名为 customers 的数据表，其中包含 id、name、age 和 address 四个列，可以使用以下指令：

CREATE TABLE customers (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT,address VARCHAR(255),PRIMARY KEY (id)
);

其中，id 列为整数类型，不允许为空，且为自动递增的主键；name 列为长度不超过 50 的字符串类型，不允许为空；age 列为整数类型，允许为空；address 列为长度不超过 255 的字符串类型，允许为空。

---

2.删除数据表DROP

如果我们要删除一个已经存在的数据表，可以使用 DROP TABLE 指令。其语法如下：

DROP TABLE table_name;

例如，如果我们要删除名为 customers 的数据表，可以使用以下指令：

DROP TABLE customers;

---

3.插入数据INSERT

在 MySQL 中，我们可以使用 INSERT INTO 指令来向数据表中插入数据，其语法如下：

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

其中，table_name 为所要插入的数据表的名称，column1、column2 等为所要插入的列名，value1、value2 等为所要插入的值。

例如，如果我们要向名为 customers 的数据表中插入一个新的记录，其 name 为 ‘Tom’，age 为 25，address 为 ‘New York’，可以使用以下指令：

INSERT INTO customers (name, age, address)
VALUES ('Tom', 25, 'New York');

这将会在 customers 表中添加一个新的记录，其 name 为 ‘Tom’，age 为 25，address 为 ‘New York’。

插入多个数据
如果我们要插入多个记录，可以在 INSERT INTO 指令后添加多组值。例如，如果我们要向名为 customers 的数据表中插入两个新的记录，分别为 name 为 ‘John’，age 为 30，address 为 ‘London’，和 name 为 ‘Mary’，age 为 35，address 为 ‘Paris’，可以使用以下指令：

INSERT INTO customers (name, age, address)
VALUES ('John', 30, 'London'), ('Mary', 35, 'Paris');

这将会在 customers 表中添加两个新的记录，分别为 name 为 ‘John’，age 为 30，address 为 ‘London’，和 name 为 ‘Mary’，age 为 35，address 为 ‘Paris’。

插入默认值
如果我们要插入默认值，可以省略 VALUES 子句。例如，如果我们要向名为 customers 的数据表中插入一个新的记录，其中所有列的值均为默认值，可以使用以下指令：

INSERT INTO customers DEFAULT VALUES;

在这个例子中，所有列的值均为其默认值。

插入查询结果
如果我们要将查询结果插入到一个数据表中，可以使用 INSERT INTO 指令和 SELECT 指令。例如，如果我们要将名为 orders 的数据表中订单总金额大于 100 的记录插入到名为 big_orders 的数据表中，可以使用以下指令：

INSERT INTO big_orders (order_id, total_amount)
SELECT order_id, SUM(amount)
FROM orders
GROUP BY order_id
HAVING SUM(amount) > 100;

在这个例子中，我们使用 SELECT 指令查询名为 orders 的数据表中订单总金额大于 100 的记录，并将其插入到名为 big_orders 的数据表中。

---

4.更新数据UPDATE

如果我们要更新数据表中的数据，可以使用 UPDATE 指令，其语法如下：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

其中，table_name 为所要更新的数据表的名称，column1、column2 等为所要更新的列名，value1、value2 等为所要更新的值，condition 为所要更新的记录的条件。

更新单个记录
例如，如果我们要更新名为 customers 的数据表中 id 为 1 的记录的 name 和 age 列，可以使用以下指令：

UPDATE customers
SET name = 'Tom', age = 25
WHERE id = 1;

这将会将 id 为 1 的记录的 name 列更新为 ‘Tom’，age 列更新为 25。

更新多个记录
如果我们要更新多个记录，可以使用 UPDATE 指令结合 WHERE 子句中的条件进行更新。例如，如果我们要将所有 age 大于 30 的记录的 name 列更新为 ‘John’，可以使用以下指令：

UPDATE customers
SET name = 'John'
WHERE age > 30;

更新多个列
如果我们要更新多个列，可以使用 UPDATE 指令中的 SET 子句。例如，如果我们要将所有 age 大于 30 的记录的 name 列更新为 ‘John’，address 列更新为 ‘New York’，可以使用以下指令：

UPDATE customers
SET name = 'John', address = 'New York'
WHERE age > 30;

注意，在 SET 子句中列出的列和值必须一一对应。

更新 NULL 值
如果我们要将 NULL 值更新为一个非空值，可以使用 UPDATE 指令中的 IFNULL 函数。例如，如果我们要将名为 customers 的数据表中 age 列为 NULL 的记录的 age 列更新为 18，可以使用以下指令：

UPDATE customers
SET age = IFNULL(age, 18)
WHERE age IS NULL;

在这个例子中，如果 age 列为 NULL，则使用 IFNULL 函数将其替换为 18。

更新自动递增列
如果我们要更新自动递增列的值，可以使用 SET 子句来指定新的值。例如，如果我们要将名为 customers 的数据表中 id 为 1 的记录的 id 列更新为 100，可以使用以下指令：

SET @row := 0;
UPDATE customers
SET id = (@row := @row + 1)
WHERE id = 1;

在这个例子中，我们使用 SET 子句将 id 列更新为 @row 的值加 1，从而将其设置为 100。

---

5.修改数据ALTER

ALTER 操作是 MySQL 中的一个非常重要的操作，用于修改数据库表的结构，包括增加、删除、修改表的列，修改表的数据类型，修改表的索引等。下面是 ALTER 命令的语法：

ALTER TABLE table_name action;

其中 table_name 是要操作的表的名称，action 表示要执行的操作。
ALTER 操作的常用语法包括：

增加列
要在表中增加一列，可以使用以下语法：

ALTER TABLE table_name ADD column_name data_type;

例如，以下语句将在表 users 中增加一列 age：

ALTER TABLE users ADD age INT;

修改列
要修改表中的一列，可以使用以下语法：

ALTER TABLE table_name MODIFY column_name data_type;

例如，以下语句将修改表 users 中的列 age 的数据类型为 FLOAT：

ALTER TABLE users MODIFY age FLOAT;

删除列
要删除表中的一列，可以使用以下语法：

ALTER TABLE table_name DROP column_name;

例如，以下语句将删除表 users 中的列 age：

ALTER TABLE users DROP age;

修改列名
要修改表中的一列的名称，可以使用以下语法：

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

例如，以下语句将表 users 中的列 age 的名称修改为 new_age：

ALTER TABLE users RENAME COLUMN age TO new_age;

添加主键
要在表中添加主键，可以使用以下语法：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

例如，以下语句将在表 users 中添加主键，主键列为 id：

ALTER TABLE users ADD PRIMARY KEY (id);

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customer

---

6.查询数据SELECT

SELECT是SQL中最常用的语句之一，它用于从表中检索数据。可以使用SELECT语句检索特定列或所有列。例如，以下语句将检索名为"customers"的表中的所有列：

SELECT * FROM customers;

此外，我们可以选择特定的列来检索，如下所示：

---

SELECT customer_name, customer_email FROM customers;

此语句将只返回名为"customer_name"和"customer_email"的列。

7.WHERE子句

WHERE子句用于筛选SELECT语句中的数据。例如，以下语句将检索"customers"表中姓为"Smith"的客户：

SELECT * FROM customers
WHERE customer_last_name = 'Smith';

8.ORDER BY子句

ORDER BY子句用于按特定列的升序或降序对检索的数据进行排序。例如，以下语句将按客户姓名的字母顺序对"customers"表中的数据进行排序：

SELECT * FROM customers
ORDER BY customer_name;

如果想要以降序的方式排序，我们可以使用DESC关键字，如下所示：

SELECT * FROM customers
ORDER BY customer_name DESC;

9.LIMIT子句

LIMIT子句用于限制SELECT语句返回的结果数。例如，以下语句将只返回"customers"表中前5个结果：

SELECT * FROM customers
LIMIT 5;

可以使用OFFSET关键字指定从哪个位置开始返回结果，如下所示：

SELECT * FROM customers
LIMIT 5 OFFSET 10;

此语句将从第11行开始返回结果，总共返回5行数据。

10.GROUP BY子句

GROUP BY子句用于根据一个或多个列对SELECT语句的结果进行分组。例如，以下语句将按客户姓氏分组并计算每个组中的客户数：

SELECT customer_last_name, COUNT(*) as num_customers
FROM customers
GROUP BY customer_last_name;

11.HAVING子句

HAVING子句用于在GROUP BY子句中使用聚合函数来筛选分组后的数据。例如，以下语句将筛选出客户数大于5的客户姓氏：

SELECT customer_last_name, COUNT(*) as num_customers
FROM customers
GROUP BY customer_last_name
HAVING num_customers > 5;

使用注意事项

1. 数据库备份与恢复
   在使用 MySQL 进行数据管理时，定期进行数据备份非常重要。如果数据库损坏或出现其他问题，可以通过备份文件进行数据恢复。可以使用 MySQL 提供的 mysqldump 工具进行备份和恢复操作。
2. 数据库安全
   为了保证数据库的安全，需要对 MySQL 数据库进行正确的设置和保护。建议设置复杂的密码，并对数据库进行访问控制和授权管理。
3. 数据库性能优化
   MySQL 的性能取决于很多因素，如查询语句、索引、表结构等。在使用 MySQL 时，需要对查询语句进行优化，使用正确的索引，避免频繁的表连接操作等。
4. 数据库版本管理
   MySQL 的每个版本都有其独特的特点和优化。建议使用最新版本的 MySQL，并定期升级以保持系统的稳定性和性能。
5. 数据库错误处理
   在使用 MySQL 时，经常会遇到各种错误，如语法错误、连接错误、数据类型错误等。建议仔细阅读错误信息，并对错误进行适当的处理。
6. 数据库字符集设置
   MySQL 支持多种字符集，如 utf8、gbk、latin1 等。在创建数据库和表时，需要正确设置字符集。如果字符集设置不正确，可能会导致数据乱码等问题。

---