MySQL 是一款開源關系型數據庫管理系統,廣泛應用于網站和應用程序的數據存儲。在接下來的 30 天里,我們將深入學習 MySQL,從安裝到 SQL 查詢操作,再到高級性能優化和數據安全。這篇文章將為您介紹本次學習計劃的詳細內容。
Day 1: MySQL 安裝和配置
安裝 MySQL sudo apt-get install mysql-server sudo service mysql start 登錄 MySQL mysql -u root -p 設置 MySQL 密碼 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword'; 退出 MySQL exit
Day 2: MySQL 基礎知識
創建數據庫 CREATE DATABASE dbname; 刪除數據庫 DROP DATABASE dbname; 選擇數據庫 USE dbname; 創建表 CREATE TABLE tablename ( column1 datatype, column2 datatype, ... ); 插入數據 INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...); 查詢數據 SELECT * FROM tablename; 更新數據 UPDATE tablename SET column1 = value1 WHERE condition; 刪除數據 DELETE FROM tablename WHERE condition;
Day 3-10: SQL 查詢操作
查詢語句 SELECT column1, column2, ... FROM tablename WHERE condition; 排序查詢 SELECT column1, column2, ... FROM tablename ORDER BY column1 ASC [DESC]; 分組查詢 SELECT column1, COUNT(column2) FROM tablename GROUP BY column1; 聯合查詢 SELECT * FROM tablename1 UNION SELECT * FROM tablename2; 子查詢 SELECT column1, column2, ... FROM (SELECT * FROM tablename WHERE condition) AS aliasname; 連接查詢 SELECT * FROM tablename1 INNER JOIN tablename2 ON tablename1.column = tablename2.column; 多表連接查詢 SELECT * FROM tablename1 INNER JOIN tablename2 ON tablename1.column = tablename2.column INNER JOIN tablename3 ON tablename2.column = tablename3.column;
Day 11-20: MySQL 高級技巧
索引優化 CREATE INDEX indexname ON tablename (column1, column2, ...); DROP INDEX indexname ON tablename; 分區表 CREATE TABLE tablename (column1 datatype, ...) PARTITION BY RANGE(column1) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (MAXVALUE) ); 透明加密 CREATE TABLE tablename (column1 datatype, ...) ENCRYPTION='Y'; 備份和恢復 mysqldump -u username -p dbname >backup.sql mysql -u username -p dbname< backup.sql
Day 21-30: 數據庫安全
訪問控制 GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'password'; REVOKE ALL PRIVILEGES ON dbname.* FROM 'username'@'localhost'; 防火墻配置 sudo ufw allow mysql SSL 配置 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem
以上是本次 MySQL 學習計劃的詳細內容,希望對您有幫助!我們一起努力成為 MySQL 的專家吧!