MySQL修炼手册1——初探MySQL：连接数据库并执行第一条SQL语句

写在开头

我们将深入学习MySQL的数据库连接和基本SQL语句，通过实际操作演示每个步骤，帮助读者更好地理解和掌握MySQL的基础知识。

1 数据库连接

1.1 连接到不同主机的数据库

连接远程数据库

mysql -h remote_host -u your_username -p

• remote_host: 远程主机地址
• your_username: 连接用户名
• 输入密码完成连接

处理连接问题

在连接过程中可能遇到的问题和解决方法：

• 连接超时： 检查远程主机的防火墙设置，确保端口开放。
• 拒绝连接： 检查用户名和密码是否正确，以及是否被授权访问。

1.2 使用不同用户身份连接

连接到数据库

mysql -u your_username -p

• your_username: 连接用户名
• 输入密码完成连接

用户权限管理

-- 创建新用户
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'user_password';

-- 赋予用户权限
GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

2 基本SQL语句

在这一部分，我们将创建一个简单的测试表，以水果为例，并详细展开基本SQL语句的使用，包括SELECT、INSERT、UPDATE和DELETE。

2.1 创建测试表

首先，我们创建一个名为 fruits 的测试表，该表包含水果的名称（name）、价格（price）和产地（origin）信息。

CREATE TABLE fruits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(8, 2),
    origin VARCHAR(100)
);

此表定义了四个字段：id（主键，自增），name（水果名称），price（价格），origin（产地）。

2.2 SELECT语句

2.2.1 查询所有列

SELECT * FROM fruits;

这将返回 fruits 表中所有水果的信息。

2.2.2 查询特定列

SELECT name, price FROM fruits;

这将返回 fruits 表中所有水果的名称和价格信息。

2.2.3 使用别名

SELECT name AS fruit_name, price AS fruit_price FROM fruits;

这将返回 fruits 表中所有水果的名称和价格信息，并使用别名进行重命名。

2.2.4 使用DISTINCT关键字

SELECT DISTINCT origin FROM fruits;

这将返回 fruits 表中所有不重复的产地信息。

2.3 INSERT语句

2.3.1 插入单行数据

INSERT INTO fruits (name, price, origin) VALUES ('Apple', 2.5, 'USA');

这将在 fruits 表中插入一行新的水果数据。

2.3.2 插入多行数据

INSERT INTO fruits (name, price, origin) VALUES 
('Banana', 1.8, 'Brazil'),
('Orange', 3.0, 'Spain'),
('Grapes', 4.5, 'Italy');

这将在 fruits 表中插入三行新的水果数据。

2.4 UPDATE语句

2.4.1 条件更新数据

UPDATE fruits SET price = 2.8 WHERE name = 'Banana';

这将更新 fruits 表中水果名称为 ‘Banana’ 的价格为 2.8。

2.4.2 批量更新数据

UPDATE fruits SET price = price * 1.1;

这将更新 fruits 表中所有水果的价格增加 10%。

2.5 DELETE语句

2.5.1 删除特定数据

DELETE FROM fruits WHERE name = 'Orange';

这将从 fruits 表中删除名称为 ‘Orange’ 的水果数据。

2.6 综合操作

现在，让我们进行一些综合操作，结合以上的SQL语句，以更全面地展示它们的用法。

2.6.1 查询特定条件下的数据

SELECT name, price FROM fruits WHERE price > 3.0;

这将返回 fruits 表中价格大于 3.0 的水果的名称和价格信息。

2.6.2 插入并查询新数据

INSERT INTO fruits (name, price, origin) VALUES ('Mango', 5.2, 'India');
SELECT * FROM fruits WHERE name = 'Mango';

这将插入一行新的水果数据，并查询该水果的所有信息。

2.6.3 更新并查询数据

UPDATE fruits SET origin = 'Mexico' WHERE name = 'Grapes';
SELECT * FROM fruits WHERE name = 'Grapes';

这将更新名称为 ‘Grapes’ 的水果的产地，并查询更新后的水果信息。

2.6.4 删除并查询数据

DELETE FROM fruits WHERE price < 2.0;
SELECT * FROM fruits;

这将删除价格低于 2.0 的水果数据，并查询剩余的所有水果信息。

3 SQL语句练习

在这一部分，我们将通过对一个关于水果的测试表进行实际操作，帮助读者练习基本的SQL语句使用。

3.1 练习基本SQL语句的使用

练习1：查询指定列

首先，我们创建一个名为 fruits 的测试表：

-- 创建测试表
CREATE TABLE fruits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    color VARCHAR(20),
    taste VARCHAR(20)
);

-- 插入测试数据
INSERT INTO fruits (name, color, taste) VALUES
('Apple', 'Red', 'Sweet'),
('Banana', 'Yellow', 'Sweet'),
('Orange', 'Orange', 'Citrus'),
('Grapes', 'Purple', 'Sweet'),
('Kiwi', 'Brown', 'Tart');

现在，我们来查询水果表的指定列：

-- 查询水果名称和颜色
SELECT name, color FROM fruits;

练习2：插入新数据

接下来，我们插入一条新的水果数据：

-- 插入新的水果数据
INSERT INTO fruits (name, color, taste) VALUES ('Strawberry', 'Red', 'Sweet');

练习3：更新数据

我们将尝试更新水果表中某个水果的口感：

-- 更新水果口感
UPDATE fruits SET taste = 'Sweet and Tart' WHERE name = 'Kiwi';

练习4：删除数据

最后，我们删除口感为甜的水果：

-- 删除口感为甜的水果
DELETE FROM fruits WHERE taste = 'Sweet';

3.2 理解SQL语句执行结果

数据集的结构

执行 SELECT * FROM fruits; 后，观察数据集的结构，了解每个字段的含义和数据类型。

-- 查询所有水果数据
SELECT * FROM fruits;

空值的处理

执行 SELECT * FROM fruits WHERE color IS NULL;，观察对空值的处理结果，检查是否存在颜色为空的水果。

-- 查询颜色为空的水果
SELECT * FROM fruits WHERE color IS NULL;

3.3 进阶操作

练习5：条件查询

我们尝试进行条件查询，找出颜色为红色且口感甜的水果：

-- 条件查询：颜色为红色且口感甜的水果
SELECT * FROM fruits WHERE color = 'Red' AND taste = 'Sweet';

练习6：排序

让我们按照口感的字母顺序对水果进行排序：

-- 按照口感字母顺序对水果进行排序
SELECT * FROM fruits ORDER BY taste;

练习7：分组和聚合

我们对口感相同的水果进行分组，并计算每组中水果的数量：

-- 分组和聚合：计算每种口感的水果数量
SELECT taste, COUNT(*) as num_of_fruits FROM fruits GROUP BY taste;

练习8：连接表

如果我们有另外一个描述水果产地的表 origin，我们可以通过连接这两个表来获取更多信息：

-- 创建产地表
CREATE TABLE origin (
    fruit_id INT,
    country VARCHAR(50),
    FOREIGN KEY (fruit_id) REFERENCES fruits(id)
);

-- 插入产地数据
INSERT INTO origin (fruit_id, country) VALUES
(1, 'USA'),
(2, 'Ecuador'),
(3, 'USA'),
(4, 'Italy'),
(5, 'New Zealand');

现在，我们尝试连接这两个表，获取水果和产地的信息：

-- 连接表：获取水果和产地的信息
SELECT fruits.name, fruits.color, fruits.taste, origin.country
FROM fruits
JOIN origin ON fruits.id = origin.fruit_id;

3.4 稍微复杂的操作

聚合查询结果

执行 SELECT AVG(id) as average_id FROM fruits;，观察对聚合查询结果的处理。

-- 聚合查询：计算id的平均值
SELECT AVG(id) as average_id FROM fruits;

子查询

执行 SELECT * FROM fruits WHERE id IN (SELECT fruit_id FROM origin WHERE country = 'USA');，观察对子查询的处理。

-- 子查询：查询产自美国的水果
SELECT * FROM fruits WHERE id IN (SELECT fruit_id FROM origin WHERE country = 'USA');

写在最后

希望通过本文的练习，读者将更全面地了解SQL语句的应用，包括条件查询、排序、分组和聚合、连接表等操作。实际操作将有助于加深对SQL语句的理解和记忆，为日后更复杂的数据库查询和分析操作打下坚实基础。