目录
一、连接MySQL数据库
# 连接到本地MySQL服务器
mysql -u root -p
# 连接到远程MySQL服务器
mysql -h hostname -u username -p
# 指定端口连接
mysql -h hostname -P port -u username -p二、数据库操作
-- 显示所有数据库
SHOW DATABASES;
-- 创建新数据库
CREATE DATABASE database_name;
-- 选择/使用数据库
USE database_name;
-- 删除数据库
DROP DATABASE database_name;三、表操作
-- 显示当前数据库中的所有表
SHOW TABLES;
-- 创建新表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 查看表结构
DESCRIBE users;
DESC users; -- 简写形式
-- 查看创建表的SQL语句
SHOW CREATE TABLE users;
-- 修改表名
ALTER TABLE old_table_name RENAME TO new_table_name;
-- 添加列
ALTER TABLE users ADD COLUMN age INT AFTER username;
-- 修改列
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);
-- 删除列
ALTER TABLE users DROP COLUMN age;
-- 删除表
DROP TABLE users;四、数据操作（CRUD）
1、插入数据
-- 插入单行数据
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
-- 插入多行数据
INSERT INTO users (username, email) VALUES
('jane_smith', 'jane@example.com'),
('bob_wilson', 'bob@example.com');2、查询数据
-- 查询所有数据
SELECT * FROM users;
-- 查询特定列
SELECT username, email FROM users;
-- 带条件的查询
SELECT * FROM users WHERE id = 1;
-- 使用LIKE进行模糊查询
SELECT * FROM users WHERE username LIKE 'j%';
-- 排序查询结果
SELECT * FROM users ORDER BY created_at DESC;
-- 限制返回结果数量
SELECT * FROM users LIMIT 10;
-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 20; -- 跳过20条，取10条3、更新数据
-- 更新单行数据
UPDATE users SET email = 'new_email@example.com' WHERE id = 1;
-- 更新多列数据
UPDATE users SET username = 'new_name', email = 'new_email@example.com' WHERE id = 1;
-- 使用表达式更新
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';4、删除数据
-- 删除特定行
DELETE FROM users WHERE id = 1;
-- 删除所有数据（清空表）
DELETE FROM users;
TRUNCATE TABLE users; -- 更高效的方式五、高级查询技巧
-- 去重查询
SELECT DISTINCT category FROM products;
-- 聚合函数
SELECT COUNT(*) FROM users;
SELECT AVG(price) FROM products;
SELECT MAX(price) FROM products;
SELECT MIN(price) FROM products;
SELECT SUM(quantity) FROM order_items;
-- 分组查询
SELECT category, COUNT(*) FROM products GROUP BY category;
-- HAVING子句（对分组结果过滤）
SELECT category, COUNT(*) FROM products
GROUP BY category
HAVING COUNT(*) > 10;
-- 连接查询
SELECT users.username, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;
-- 子查询
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);六、用户和权限管理
-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
-- 授予权限
GRANT SELECT, INSERT ON database_name.* TO 'username'@'localhost';
-- 授予所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
-- 撤销权限
REVOKE INSERT ON database_name.* FROM 'username'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
-- 删除用户
DROP USER 'username'@'localhost';七、实用技巧和最佳实践
1、备份数据库
mysqldump -u username -p database_name > backup.sql2、恢复数据库
mysql -u username -p database_name < backup.sql3、事务处理
START TRANSACTION;
-- 执行一系列操作
COMMIT; -- 提交事务
-- 或者
ROLLBACK; -- 回滚事务八、使用索引提高查询性能
-- 创建索引
CREATE INDEX idx_email ON users(email);
-- 查看表索引
SHOW INDEX FROM users;