資料庫/表 基礎 DDL
CREATE DATABASE db_name;
USE db_name;
DROP DATABASE db_name; -- 直接刪除,不提示
SHOW TABLES;
DESC table_name; -- 等同 DESCRIBE
查詢去重
SELECT DISTINCT col1, col2 FROM table_name;
管理工具指令 (mysqladmin)
# 刪除資料庫 (有提示確認)
mysqladmin -u root -p DROP db_name
# 查看版本與狀態
mysqladmin -u root -p VERSION
mysqladmin -u root -p STATUS
mysqladmin -u root -p VARIABLES
版本與日期
SELECT VERSION(), CURRENT_DATE;
修改 Root 密碼
# 命令列
mysqladmin -u root -p'old' password 'new'
# 客戶端
mysql -u root -p
UPDATE mysql.user SET authentication_string=PASSWORD('new') WHERE User='root';
FLUSH PRIVILEGES;
權限授予 (GRANT)
-- 建立超級用戶 (僅 localhost)
GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' IDENTIFIED BY 'pwd' WITH GRANT OPTION;
-- 建立唯讀用戶
GRANT SELECT ON db_name.* TO 'readonly'@'%' IDENTIFIED BY 'pwd';
-- 檢視權限
SHOW GRANTS FOR 'user'@'host';
-- 回收權限
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';
顯示資訊指令
SHOW DATABASES;
USE mysql;
SHOW TABLES;
DESC user; -- user 表結構
SHOW VARIABLES LIKE 'char%'; -- 字元集參數
SHOW PROCESSLIST; -- 當前連線/查詢
SHOW ENGINE INNODB STATUS; -- InnoDB 狀態
建庫建表插入完整範例
DROP DATABASE IF EXISTS school;
CREATE DATABASE school CHARACTER SET utf8mb4;
USE school;
CREATE TABLE teacher (
id INT(3) AUTO_INCREMENT NOT NULL PRIMARY KEY,
name CHAR(10) NOT NULL,
address VARCHAR(50) DEFAULT '深圳',
year DATE
);
INSERT INTO teacher VALUES (NULL, 'glchengang', '深圳一中', '1976-10-10');
INSERT INTO teacher VALUES (NULL, 'jack', '深圳一中', '1975-12-23');
文字檔匯入
-- 格式:Tab 分隔,NULL 用 \N
3 rose 深圳二中 1976-10-10
4 mike 深圳一中 1975-12-23
LOAD DATA LOCAL INFILE 'teacher.txt' INTO TABLE teacher;
備份指令
mysqldump --opt school > school.bbb
# school.bbb 為純文字 SQL,可直接編輯/匯入