MySQL SQL 指令速查手冊：DDL/DML/查詢/約束/連接/預存程序/觸發器/權限/備份

基礎操作

SHOW DATABASES;
CREATE DATABASE day15 DEFAULT CHARACTER SET utf8;
SHOW CREATE DATABASE day15;
DROP DATABASE day15;
ALTER DATABASE day15 DEFAULT CHARACTER SET gbk;
USE day15;
SHOW TABLES;

CREATE TABLE student(
  id INT, name VARCHAR(20), gender VARCHAR(2), age INT
);
DESC student;
DROP TABLE student;

增刪改查 (CRUD)

-- 新增
INSERT INTO student VALUES(1,'張三','男',20);
INSERT INTO student(id,name) VALUES(2,'李四');

-- 修改
UPDATE student SET gender='女';                    -- 全表修改（慎用）
UPDATE student SET gender='男' WHERE id=1;         -- 條件修改
UPDATE student SET gender='男',age=30 WHERE id=2;  -- 多欄位

-- 刪除
DELETE FROM student;                               -- 全表刪除（可回滾）
DELETE FROM student WHERE id=2;                    -- 條件刪除
TRUNCATE TABLE student;                            -- 刪除資料+約束，不可回滾

-- 查詢
SELECT * FROM student;
SELECT id,name,gender FROM student;
SELECT id AS '編號', name AS '姓名' FROM student;
SELECT id,name,gender,age,'java就業班' AS '年級' FROM student;  -- 常數欄
SELECT id,name,(servlet+jsp) AS '總成績' FROM student;         -- 合併欄位
SELECT DISTINCT gender FROM student;               -- 去重
SELECT * FROM student WHERE id=2 AND name='李四';  -- AND/OR
SELECT * FROM student WHERE jsp BETWEEN 75 AND 90; -- 範圍
SELECT * FROM student WHERE address IS NULL OR address='';     -- 空值
SELECT * FROM student WHERE name LIKE '李%';       -- 模糊
SELECT SUM(servlet), AVG(servlet), MAX(servlet), MIN(servlet), COUNT(*) FROM student; -- 聚合
SELECT * FROM student LIMIT 0,2;                   -- 分頁
SELECT * FROM student ORDER BY id DESC;            -- 排序
SELECT gender,COUNT(*) FROM student GROUP BY gender HAVING COUNT(*)>2; -- 分組篩選

資料約束

-- 預設值
CREATE TABLE student(address VARCHAR(20) DEFAULT '廣州天河');

-- 非空
CREATE TABLE student(gender VARCHAR(2) NOT NULL);

-- 唯一
CREATE TABLE student(id INT UNIQUE);

-- 主鍵（非空+唯一）
CREATE TABLE student(id INT PRIMARY KEY);

-- 自增
CREATE TABLE student(id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT);

-- 外鍵
CREATE TABLE dept(id INT PRIMARY KEY, deptName VARCHAR(20));
CREATE TABLE employee(
  id INT PRIMARY KEY, empName VARCHAR(20), deptId INT,
  CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
  ON UPDATE CASCADE ON DELETE CASCADE
);

關聯查詢

-- 內連接（最常用）
SELECT e.empName, d.deptName
FROM employee e INNER JOIN dept d ON e.deptId=d.id;

-- 左外連接（左表全顯示）
SELECT d.deptName, e.empName
FROM dept d LEFT OUTER JOIN employee e ON d.id=e.deptId;

-- 右外連接（右表全顯示）
SELECT d.deptName, e.empName
FROM employee e RIGHT OUTER JOIN dept d ON d.id=e.deptId;

-- 自連接（員工-上司）
SELECT e.empName, b.empName
FROM employee e LEFT OUTER JOIN employee b ON e.bossId=b.id;

預存程序

DELIMITER $
CREATE PROCEDURE pro_test() BEGIN SELECT * FROM employee; END $
CALL pro_test();

-- 輸入參數
CREATE PROCEDURE pro_findById(IN eid INT) BEGIN SELECT * FROM employee WHERE id=eid; END $

-- 輸出參數（需會話變量接收）
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20)) BEGIN SET str='helljava'; END $
SET @name=''; CALL pro_testOut(@name); SELECT @name;

-- 條件/迴圈/查詢結果賦值
CREATE PROCEDURE pro_testWhile(IN num INT, OUT result INT)
BEGIN
  DECLARE i INT DEFAULT 1; DECLARE vsum INT DEFAULT 0;
  WHILE i<=num DO SET vsum=vsum+i; SET i=i+1; END WHILE;
  SET result=vsum;
END $

觸發器

CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('員工表新增一筆');

CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('員工表修改一筆');

CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('員工表刪除一筆');

權限與備份

-- 修改密碼
UPDATE mysql.user SET Password=PASSWORD('123456') WHERE User='root';

-- 授權
GRANT SELECT,INSERT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

-- 備份/還原
mysqldump -u root -p day17 > c:/back.sql
mysql -u root -p day17 < d:/back.sql