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

发布时间:2024年01月09日

写在开头

我们将深入学习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语句的理解和记忆,为日后更复杂的数据库查询和分析操作打下坚实基础。

文章来源:https://blog.csdn.net/qq_41780234/article/details/135463932
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。