CREATE TABLE customers (
cust_id INT NOT NULL AUTO_INCREMENT,
cust_name CHAR(50) NOT NULL,
cust_address CHAR(50) NULL,
cust_city CHAR(50) NULL,
cust_state CHAR(5) NULL,
cust_zip CHAR(10) NULL,
cust_country CHAR(50) NULL,
cust_contact CHAR(50) NULL,
cust_email CHAR(255) NULL,
PRIMARY KEY (cust_id)
) ENGINE = InnoDB;PRIMARY KEY (order_num, order_item)；SELECT LAST_INSERT_ID() 獲取；ALTER TABLE vendors ADD vend_phone CHAR(20); -- 增加列
ALTER TABLE vendors DROP vend_phone; -- 刪除列
ALTER TABLE orderitems ADD CONSTRAINT fk_oi_o
FOREIGN KEY (order_num) REFERENCES orders(order_num); -- 添加外鍵DROP TABLE customers;
RENAME TABLE backup_customers TO customers;視圖是虛擬表，不存儲數據，僅保存 SELECT 查詢。每次使用時動態執行。
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
-- 使用視圖簡化查詢
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
-- 刪除/重建
DROP VIEW viewname;
CREATE OR REPLACE VIEW viewname AS ...;含以下構造的視圖不可更新：GROUP BY/HAVING、JOIN、子查詢、UNION、聚集函數、DISTINCT、導出列。
將一組 SQL 封裝為單一單元，優勢：簡單、安全、高性能（減少網絡往返、編譯緩存）。
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage FROM products;
END//
DELIMITER ;
CALL productpricing();DELIMITER 臨時更改語句結束符，避免過程體內 ; 被誤判。
| 模式 | 關鍵字 | 方向 |
|---|---|---|
| 輸入 | IN | 調用者 → 過程 |
| 輸出 | OUT | 過程 → 調用者 |
| 雙向 | INOUT | 雙向 |
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
DECLARE total DECIMAL(8,2) DEFAULT 0;
DECLARE taxrate INT DEFAULT 6;
SELECT SUM(item_price*quantity) INTO total
FROM orderitems WHERE order_num = onumber;
IF taxable THEN
SET total = total + (total/100*taxrate);
END IF;
SELECT total INTO ototal;
END;DROP PROCEDURE IF EXISTS productpricing;
SHOW CREATE PROCEDURE ordertotal;
SHOW PROCEDURE STATUS LIKE 'ordertotal';游標是服務端結果集指針，支援逐行處理，僅能在存儲過程/函數/觸發器中使用。
DECLARE cursor_name CURSOR FOR SELECT ...（不取數據）；OPEN cursor_name（執行查詢、構建結果集）；FETCH cursor_name INTO var1, var2 ...（取行、移動指針）；CLOSE cursor_name（釋放資源）。CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
CREATE TABLE IF NOT EXISTS ordertotals (order_num INT, total DECIMAL(8,2));
OPEN ordernumbers;
REPEAT
FETCH ordernumbers INTO o;
IF NOT done THEN
CALL ordertotal(o, 1, t); -- 調用前章帶稅過程
INSERT INTO ordertotals VALUES (o, t);
END IF;
UNTIL done END REPEAT;
CLOSE ordernumbers;
END;響應 INSERT/UPDATE/DELETE 自動執行。每表每事件每時機僅一個觸發器，最多 6 個。
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';| 觸發器類型 | 可用虛擬表 | NEW 可寫? | OLD 可寫? |
|---|---|---|---|
| INSERT | NEW | BEFORE 可寫 | 無 |
| DELETE | OLD | 無 | 只讀 |
| UPDATE | NEW, OLD | BEFORE 可寫 | 只讀 |
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders (order_num, order_date, cust_id)
VALUES (OLD.order_num, OLD.order_date, OLD.cust_id);
END;保證一組操作全成功或全失敗（ACID）。
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT; -- 或 ROLLBACK;SAVEPOINT delete1;
-- ...操作...
ROLLBACK TO delete1; -- 部分回滾SET autocommit = 0; -- 關閉隱式提交，需顯式 COMMITCREATE TABLE mytable (
col1 INT,
col2 VARCHAR(100)
) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
SHOW CHARACTER SET; -- 查看支援字符集
SHOW COLLATION; -- 查看支援校對規則CREATE USER ben IDENTIFIED BY 'P@$$w0rd';
RENAME USER ben TO bforta;
DROP USER bforta;GRANT SELECT ON crashcourse.* TO bforta;
SHOW GRANTS FOR bforta;
REVOKE SELECT ON crashcourse.* FROM bforta;SET PASSWORD FOR bforta = PASSWORD('n3wp@$$w0rd');
SET PASSWORD = PASSWORD('n3wp@$$w0rd'); -- 當前用戶ANALYZE TABLE tbl_name; -- 檢查鍵分佈統計
CHECK TABLE tbl_name; -- 多維度完整性檢查
REPAIR TABLE tbl_name; -- 修復損壞表（MyISAM）
OPTIMIZE TABLE tbl_name; -- 整理碎片、回收空間
