启动MySQL服务:
# Linux系统
sudo systemctl start mysql
# Windows系统
net start mysql
安全配置(推荐):
mysql_secure_installation
这会引导您设置root密码、移除匿名用户、禁止root远程登录等安全选项。
登录MySQL:
mysql -u root -p
创建新数据库:
CREATE DATABASE database_name;
例如:
CREATE DATABASE my_shop;
查看所有数据库:
SHOW DATABASES;
创建用户:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
示例:
CREATE USER 'shop_admin'@'localhost' IDENTIFIED BY 'secure_password123';
授予权限:
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
示例:
GRANT ALL PRIVILEGES ON my_shop.* TO 'shop_admin'@'localhost';
刷新权限:
FLUSH PRIVILEGES;
查看用户权限:
SHOW GRANTS FOR 'username'@'host';
使用数据库:
USE database_name;
创建表:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
示例:
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0
);
查看表结构:
DESCRIBE table_name;
备份数据库:
mysqldump -u username -p database_name > backup_file.sql
恢复数据库:
mysql -u username -p database_name < backup_file.sql
优化表:
OPTIMIZE TABLE table_name;
修复表:
REPAIR TABLE table_name;
查看运行状态:
SHOW STATUS;
查看进程:
SHOW PROCESSLIST;
设置字符集:
ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
事件调度器:
-- 启用事件调度器
SET GLOBAL event_scheduler = ON;
-- 创建定时事件
CREATE EVENT my_event
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;
存储过程:
DELIMITER //
CREATE PROCEDURE update_product_price(IN product_id INT, IN price_change DECIMAL(10,2))
BEGIN
UPDATE products SET price = price + price_change WHERE id = product_id;
END //
DELIMITER ;
通过以上命令和操作,您可以有效地创建和管理MySQL数据库。根据实际需求,您可能需要进一步学习更高级的数据库管理技术。