庫/表管理
-- 庫
CREATE DATABASE db_name [CHARACTER SET utf8 COLLATE utf8_general_ci];
SHOW DATABASES;
SHOW CREATE DATABASE db_name;
ALTER DATABASE db_name CHARACTER SET gbk;
DROP DATABASE db_name;
-- 表
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
gender VARCHAR(6),
birthday DATE,
entry_date DATE,
job VARCHAR(20),
salary DOUBLE,
resume TEXT
);
ALTER TABLE employee ADD image BLOB;
ALTER TABLE employee MODIFY job VARCHAR(60);
ALTER TABLE employee DROP sex;
RENAME TABLE employee TO user;
ALTER TABLE user CHARACTER SET utf8;
ALTER TABLE user CHANGE name username VARCHAR(20);
SHOW TABLES;
SHOW CREATE TABLE user;
DESC user;
DROP TABLE user;
數據增刪改查 (CRUD)
-- 增
INSERT INTO employee (id,name,sex,birthday,salary,entry_date,resume)
VALUES (1,'zhangsan','male','1987-11-23',1500,'2010-2-18','good boy');
INSERT INTO employee VALUES (2,'wangwu','male','1988-11-23',1200,'2010-2-18','good boy'),
(3,'xiaohong','female','1983-11-23',1800,'2010-2-18','good girl');
-- 改
UPDATE employee SET salary=5000;
UPDATE employee SET salary=3000 WHERE name='zhangsan';
UPDATE employee SET salary=salary+1000 WHERE name='xiaohong';
-- 刪
DELETE FROM employee WHERE name='趙楠';
TRUNCATE employee; -- 高效清空
-- 查
SELECT * FROM student;
SELECT name, english FROM student;
SELECT DISTINCT english FROM student;
SELECT name, english+10 AS eng_bonus FROM student;
SELECT name, english+chinese+math AS total FROM student;
SELECT * FROM student WHERE english > 90;
SELECT * FROM student WHERE english BETWEEN 65 AND 85;
SELECT * FROM student WHERE math IN (89,90,91);
SELECT * FROM student WHERE name LIKE '李%';
SELECT * FROM student WHERE name LIKE '李_';
SELECT * FROM student WHERE math>80 AND chinese>80;
SELECT * FROM student WHERE english>80 OR (chinese+english+math)>200;
SELECT * FROM student ORDER BY math;
SELECT *, chinese+math+english AS total FROM student ORDER BY total DESC;
聚合與分組
SELECT COUNT(*) FROM student;
SELECT COUNT(*) FROM student WHERE math>90;
SELECT SUM(math), SUM(chinese), SUM(english) FROM student;
SELECT AVG(chinese) FROM student;
SELECT MAX(chinese), MIN(chinese) FROM student;
SELECT product, SUM(price) FROM orders GROUP BY product;
SELECT product, SUM(price) FROM orders GROUP BY product HAVING SUM(price)>100;
SELECT * FROM orders WHERE price>20 GROUP BY product ORDER BY price;
約束與關係
- 主鍵：
PRIMARY KEY (id) 或 PRIMARY KEY (firstname, lastname)
- 自增：
id INT PRIMARY KEY AUTO_INCREMENT
- 外鍵：
CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(id)
- 一對多：部門-員工
- 多對多：師生中間表
tea_stu
- 一對一：人-身份證（共用主鍵）
多表查詢模式
-- 隱式內連接（笛卡爾積 + WHERE 過濾）
SELECT e.* FROM department d, employee e
WHERE e.departmentid = d.id AND d.name='開發部';
-- 顯式 JOIN（推薦）
SELECT e.* FROM employee e
JOIN department d ON e.departmentid = d.id
WHERE d.name='開發部';
備份與恢復
mysqldump -uroot -p db_name > backup.sql
mysql -uroot -p db_name < backup.sql
