MySQL 是最流行的关系数据库管理系统 (RDBMS) 之一,用于 Web 应用程序和其他数据驱动应用程序。无论您是初学者还是想要提高 MySQL 技能的人,了解基本查询都是至关重要的。本博客将引导您完成一些基本的 MySQL 查询,可用于数据库操作、表操作和数据管理。
首先,您需要一个数据库来存储表和数据。创建数据库很简单:
CREATE DATABASE my_database;
创建数据库后,使用以下查询来选择它:
USE my_database;
如果需要删除数据库,请使用以下命令:
DROP DATABASE my_database;
表是存储数据的地方。您可以创建包含特定列的表,如下所示:
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), age INT );
要查看所选数据库中的所有表:
SHOW TABLES;
如果你想了解表的结构,你可以描述它:
DESCRIBE users;
如果您需要通过添加或更改列来修改表格:
ALTER TABLE users ADD phone VARCHAR(15);
ALTER TABLE users MODIFY age TINYINT;
删除表:
DROP TABLE users;
将数据添加到表中:
INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 25);
从表中检索数据:
SELECT name, email FROM users WHERE age > 20;
要从表中检索所有数据:
SELECT * FROM users;
更新表中的数据:
UPDATE users SET age = 26 WHERE name = 'John Doe';
要从表中删除数据:
DELETE FROM users WHERE name = 'John Doe';
使用WHERE子句根据特定条件过滤记录:
SELECT * FROM users WHERE age > 20;
使用 AND 或 OR 组合多个条件:
SELECT * FROM users WHERE age > 20 AND name = 'John Doe';
根据值列表选择数据:
SELECT * FROM users WHERE age IN (20, 25, 30);
过滤一定范围内的数据:
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
使用 LIKE 子句搜索模式:
SELECT * FROM users WHERE name LIKE 'J%';
过滤具有 NULL 或 NOT NULL 值的记录:
SELECT * FROM users WHERE email IS NULL;
计算行数:
SELECT COUNT(*) FROM users;
计算列的总和:
SELECT SUM(age) FROM users;
求一列的平均值:
SELECT AVG(age) FROM users;
查找列的最大值或最小值:
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;
根据一列或多列对数据进行分组:
SELECT age, COUNT(*) FROM users GROUP BY age;
过滤分组数据:
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;
按升序或降序对数据进行排序:
SELECT * FROM users ORDER BY age DESC;
从多个表中获取满足条件的数据:
SELECT users.name, orders.order_date FROM users INNER JOIN orders ON users.id = orders.user_id;
从左表中获取数据并从右表中获取匹配的行:
SELECT users.name, orders.order_date FROM users LEFT JOIN orders ON users.id = orders.user_id;
从右表中获取数据并从左表中获取匹配的行:
SELECT users.name, orders.order_date FROM users RIGHT JOIN orders ON users.id = orders.user_id;
使用子查询来过滤结果:
SELECT name FROM users WHERE id = (SELECT user_id FROM orders WHERE order_id = 1);
使用子查询来计算值:
SELECT name, (SELECT COUNT(*) FROM orders WHERE users.id = orders.user_id) AS order_count FROM users;
根据查询创建虚拟表:
CREATE VIEW user_orders AS SELECT users.name, orders.order_date FROM users INNER JOIN orders ON users.id = orders.user_id;
Delete a view:
DROP VIEW user_orders;
Improve query performance by creating an index:
CREATE INDEX idx_name ON users (name);
Remove an index:
DROP INDEX idx_name ON users;
Understanding these basic MySQL queries is essential for anyone working with relational databases. Whether you are managing data, optimizing queries, or ensuring data integrity, these commands form the foundation of your MySQL skills. By mastering them, you'll be well-equipped to handle most database-related tasks with ease.
以上是基本 MySQL 查询:综合指南的详细内容。更多信息请关注PHP中文网其他相关文章!