\u76ee\u5f55<\/p>\n
\u524d\u8a00<\/p>\n
\u4e00\u3001\u6570\u636e\u51c6\u5907\u4e0e\u56de\u987e<\/p>\n
1.1 study \u8868\u7ed3\u6784\u4e0e\u5df2\u6709\u8bed\u53e5\u6574\u7406<\/p>\n
1.2 \u51c6\u5907\u7b2c\u4e8c\u5f20\u6d4b\u8bd5\u8868（\u7528\u4e8e\u8fde\u8868\u67e5\u8be2）<\/p>\n
\u4e8c\u3001\u591a\u6761\u4ef6\u67e5\u8be2\u3001\u6a21\u7cca\u67e5\u8be2\u4e0e NULL \u5224\u65ad<\/p>\n
\u4e09\u3001\u5206\u7ec4\u4e0e HAVING（\u5206\u7ec4\u540e\u8fc7\u6ee4）<\/p>\n
\u56db\u3001\u8fde\u8868\u67e5\u8be2<\/p>\n
4.1 \u9690\u5f0f\u5185\u8fde\u63a5（WHERE \u6761\u4ef6，\u4e0d\u63a8\u8350）<\/p>\n
4.2 \u663e\u5f0f\u5185\u8fde\u63a5 INNER JOIN（\u53ea\u53d6\u4e24\u8fb9\u5339\u914d\u5230\u7684\u6570\u636e）<\/p>\n
4.3 \u5de6\u5916\u8fde\u63a5 LEFT JOIN：\u5de6\u8868\u5168\u90e8，\u53f3\u8868\u5339\u914d\u4e0d\u5230\u5219\u586b NULL<\/p>\n
4.4 \u53f3\u5916\u8fde\u63a5 RIGHT JOIN：\u53f3\u8868\u5168\u90e8，\u5de6\u8868\u5339\u914d\u4e0d\u5230\u5219\u586b NULL<\/p>\n
4.5 \u81ea\u8fde\u63a5：\u4e00\u5f20\u8868\u81ea\u5df1\u8fde\u63a5\u81ea\u5df1<\/p>\n
\u4e94\u3001\u5b50\u67e5\u8be2<\/p>\n
5.1 \u6807\u91cf\u5b50\u67e5\u8be2（\u8fd4\u56de\u5355\u4e2a\u503c）<\/p>\n
5.2 \u5217\u5b50\u67e5\u8be2（\u8fd4\u56de\u4e00\u5217，\u642d\u914d IN）<\/p>\n
5.3 EXISTS \u5b50\u67e5\u8be2：\u5224\u65ad\u5b50\u67e5\u8be2\u662f\u5426\u6709\u7ed3\u679c<\/p>\n
\u516d\u3001UNION \u96c6\u5408\u5408\u5e76<\/p>\n
\u4e03\u3001DDL \u8865\u5145\u8bed\u53e5<\/p>\n
\u516b\u3001DCL \u7b80\u5355\u6f14\u793a（MySQL 8.0）<\/p>\n
\u4e5d\u3001\u5e38\u7528\u51fd\u6570<\/p>\n
\u5341\u3001\u5b9e\u6218\u7ec3\u4e60<\/p>\n
\u7ec3\u4e60 1：\u67e5\u8be2\u6bcf\u4e2a\u73ed\u7ea7\u5e74\u9f84\u6700\u5927\u7684\u5b66\u751f<\/p>\n
\u7ec3\u4e60 2：\u4f7f\u7528\u5b50\u67e5\u8be2\u627e\u51fa\u6ca1\u6709\u5b66\u751f\u7684\u73ed\u7ea7<\/p>\n
\u7ec3\u4e60 3：\u7edf\u8ba1\u6bcf\u4e2a\u73ed\u7ea7\u7684\u7537\u5973\u5b66\u751f\u4eba\u6570<\/p>\n
\u7ec3\u4e60 4：\u67e5\u8be2\u5e74\u9f84\u5927\u4e8e\u73ed\u7ea7\u5e73\u5747\u5e74\u9f84\u7684\u5b66\u751f<\/p>\n
\u7ec3\u4e60 5：\u4f7f\u7528 UNION \u5408\u5e76\u4e0d\u540c\u6761\u4ef6\u7684\u67e5\u8be2\u7ed3\u679c<\/p>\n
\u5341\u4e00\u3001\u6838\u5fc3\u6982\u5ff5\u4e0eSQL\u8bed\u53e5\u8865\u5145<\/p>\n
1. \u57fa\u7840\u7bc7\u8865\u5145<\/p>\n
1.1 \u7ea6\u675f（Constraints）<\/p>\n
1.2 \u591a\u8868\u5173\u7cfb\u8bbe\u8ba1\u4e0e\u5b9e\u73b0<\/p>\n
1. \u4e00\u5bf9\u591a\u5173\u7cfb（One-to-Many）<\/p>\n
2. \u591a\u5bf9\u591a\u5173\u7cfb（Many-to-Many）<\/p>\n
3. \u4e00\u5bf9\u4e00\u5173\u7cfb（One-to-One）<\/p>\n
4. \u5916\u952e\u7ea6\u675f\u4e0e\u7ea7\u8054\u64cd\u4f5c<\/p>\n
1.3 \u4e8b\u52a1（Transactions）<\/p>\n
\u5e76\u53d1\u4e8b\u52a1\u95ee\u9898<\/p>\n
\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b<\/p>\n
2. \u8fdb\u9636\u7bc7\u8865\u5145<\/p>\n
2.1 \u5b58\u50a8\u5f15\u64ce<\/p>\n
2.2 \u7d22\u5f15（Indexes）<\/p>\n
2.3 SQL\u4f18\u5316\u793a\u4f8b<\/p>\n
2.4 \u89c6\u56fe（Views）<\/p>\n
2.5 \u5b58\u50a8\u8fc7\u7a0b（Stored Procedures）<\/p>\n
2.6 \u89e6\u53d1\u5668（Triggers）<\/p>\n
2.7 \u9501（Locks）<\/p>\n
3. \u8fd0\u7ef4\u7bc7\u8865\u5145<\/p>\n
3.1 \u65e5\u5fd7\u7ba1\u7406<\/p>\n
3.2 \u4e3b\u4ece\u590d\u5236\u914d\u7f6e\u793a\u4f8b<\/p>\n
3.3 \u5206\u5e93\u5206\u8868\u793a\u4f8b<\/p>\n
3.4 \u7cfb\u7edf\u7ba1\u7406\u4e0e\u7ef4\u62a4<\/p>\n
4. InnoDB\u5f15\u64ce\u6838\u5fc3\u7279\u6027<\/p>\n
\u603b\u7ed3<\/p>\n
\u63d0\u793a：\u672c\u6587\u5185\u5bb9\u8bbe\u8ba1\u8f83\u5e7f，\u5efa\u8bae\u5148\u67e5\u9605\u76ee\u5f55<\/p>\n
\u672c\u6587\u662f study \u8868 SQL \u7ec3\u4e60\u7684\u5b8c\u6574\u7eed\u7bc7，\u65e8\u5728\u7cfb\u7edf\u6027\u5730\u8865\u9f50 MySQL \u57fa\u7840 SQL \u8bed\u6cd5\u7684\u5269\u4f59\u6838\u5fc3\u90e8\u5206\u3002\u6211\u4eec\u5c06\u57fa\u4e8e\u5df2\u6709\u7684\u6570\u636e\u8868，\u9010\u6b65\u6f14\u793a\u5185\u8fde\u63a5\u3001\u5de6\u8fde\u63a5\u3001\u53f3\u8fde\u63a5\u3001\u81ea\u8fde\u63a5\u3001\u5b50\u67e5\u8be2\u3001UNION\u3001EXISTS\u3001\u805a\u5408\u51fd\u6570\u3001\u591a\u6761\u4ef6\u67e5\u8be2\u3001HAVING \u8fc7\u6ee4\u7b49\u5173\u952e\u64cd\u4f5c，\u5e76\u63d0\u4f9b\u53ef\u76f4\u63a5\u8fd0\u884c\u7684\u4ee3\u7801\u793a\u4f8b\u3002<\/p>\n
\u5728\u5f00\u59cb\u524d，\u8bf7\u786e\u4fdd\u5df2\u521b\u5efa study \u8868\u5e76\u63d2\u5165\u6d4b\u8bd5\u6570\u636e\u3002\u540c\u65f6，\u4e3a\u4e86\u6f14\u793a\u8fde\u8868\u67e5\u8be2，\u6211\u4eec\u8fd8\u9700\u8981\u51c6\u5907\u7b2c\u4e8c\u5f20\u73ed\u7ea7\u8868 class_info\u3002<\/p>\n
\u9996\u5148，\u56de\u987e\u5e76\u4fee\u6b63\u4f60\u63d0\u4f9b\u7684\u539f\u6709 SQL \u8bed\u53e5\u3002\u6ce8\u610f MySQL \u4e2d\u7684\u51e0\u4e2a\u5173\u952e\u70b9：<\/p>\n
— \u57fa\u7840\u589e\u5220\u6539\u67e5
\nINSERT INTO study(name, age, sex, class) VALUES('\u5f20\u4e91\u821f', 29, '\u7537', 2);
\nSELECT * FROM study;
\nDELETE FROM study WHERE id = 8;
\nUPDATE study SET name = '\u5de6\u82cf', sex = '\u5973' WHERE id = 9;
\nSELECT class FROM study WHERE class = 1;
\nSELECT class FROM study GROUP BY class;
\n— \u2757 \u9519\u8bef\u793a\u4f8b（\u5728 ONLY_FULL_GROUP_BY \u5f00\u542f\u65f6\u7981\u6b62\u6267\u884c）：
\n— SELECT * FROM study GROUP BY class; — \u5206\u7ec4\u540e\u53ea\u80fd\u67e5\u5206\u7ec4\u5b57\u6bb5\/\u805a\u5408\u51fd\u6570
\nSELECT name FROM study WHERE id IN (1, 2, 3);
\nALTER TABLE study RENAME COLUMN sex TO to_sex;
\nSELECT name AS '\u540d\u5b57' FROM study;
\nSELECT * FROM study LIMIT 0, 3;
\nSELECT name AS user_infos_example FROM study WHERE id IN (1, 2);
\nSELECT name, to_sex, class FROM study WHERE class = 1;
\nSELECT name FROM study WHERE id > 3;
\nSELECT name FROM study WHERE to_sex NOT IN ('\u7537');
\nSELECT * FROM study ORDER BY age ASC, id ASC;
\nSELECT AVG(age) AS new FROM study WHERE age > 30;
\nSELECT ROUND(age, 1) AS new_age FROM study WHERE age > 30;
\nSELECT age AS new_age FROM study;
\nINSERT INTO study(name, gender, age) VALUES('qqq', '\u7537', 19), ('aaa', '\u7537', 38), ('yyy', '\u5973', 15); <\/p>\n
\u521b\u5efa\u73ed\u7ea7\u8868 class_info，\u5e76\u4e0e study \u8868\u7684 class \u5b57\u6bb5\u5efa\u7acb\u5173\u8054\u3002<\/p>\n
— \u73ed\u7ea7\u8868 class_info
\nCREATE TABLE class_info (
\n cid INT PRIMARY KEY AUTO_INCREMENT,
\n cname VARCHAR(20) COMMENT '\u73ed\u7ea7\u540d\u79f0'
\n);<\/p>\n
INSERT INTO class_info(cname) VALUES ('\u4e00\u73ed'), ('\u4e8c\u73ed'), ('\u4e09\u73ed');<\/p>\n
— study \u8868\u7684 class \u5b57\u6bb5\u5bf9\u5e94 class_info.cid
\nSELECT * FROM study;
\nSELECT * FROM class_info; <\/p>\n
— AND \u5e76\u4e14
\nSELECT * FROM study WHERE age > 18 AND class = 2;<\/p>\n
— OR \u6216\u8005
\nSELECT * FROM study WHERE age < 20 OR class = 1;<\/p>\n
— \u6a21\u7cca\u67e5\u8be2：% \u5339\u914d\u4efb\u610f\u5b57\u7b26，_ \u5339\u914d\u5355\u4e2a\u5b57\u7b26
\nSELECT * FROM study WHERE name LIKE '\u5f20%';
\nSELECT * FROM study WHERE name LIKE '_\u4e91%';<\/p>\n
— \u5224\u65ad NULL：\u4e0d\u80fd\u7528 = NULL，\u5fc5\u987b\u7528 IS NULL \u6216 IS NOT NULL
\nSELECT * FROM study WHERE to_sex IS NULL;
\nSELECT * FROM study WHERE to_sex IS NOT NULL;<\/p>\n
— BETWEEN \u8303\u56f4\u67e5\u8be2
\nSELECT * FROM study WHERE age BETWEEN 18 AND 30; <\/p>\n
WHERE \u7528\u4e8e\u5206\u7ec4\u524d\u8fc7\u6ee4\u539f\u59cb\u6570\u636e，HAVING \u7528\u4e8e\u5206\u7ec4\u540e\u5bf9\u805a\u5408\u7ed3\u679c\u8fdb\u884c\u8fc7\u6ee4\u3002<\/p>\n
— \u67e5\u8be2\u6bcf\u4e2a\u73ed\u7ea7\u7684\u5b66\u751f\u6570\u91cf\u53ca\u5e73\u5747\u5e74\u9f84，\u4e14\u53ea\u663e\u793a\u5b66\u751f\u6570 \u2265 2 \u7684\u73ed\u7ea7
\nSELECT class, COUNT(*) AS num, AVG(age) AS avg_age
\nFROM study
\nWHERE age >= 16
\nGROUP BY class
\nHAVING COUNT(*) >= 2; <\/p>\n
SELECT s.*, ci.cname
\nFROM study s, class_info ci
\nWHERE s.class = ci.cid; <\/p>\n
SELECT s.name, s.age, ci.cname
\nFROM study s
\nINNER JOIN class_info ci ON s.class = ci.cid; <\/p>\n
— study \u5168\u90e8\u5b66\u751f，\u5bf9\u5e94\u73ed\u7ea7，\u6ca1\u6709\u73ed\u7ea7\u5219\u73ed\u7ea7\u540d\u79f0\u4e3a NULL
\nSELECT s.name, s.age, ci.cname
\nFROM study s
\nLEFT JOIN class_info ci ON s.class = ci.cid; <\/p>\n
SELECT s.name, s.age, ci.cname
\nFROM study s
\nRIGHT JOIN class_info ci ON s.class = ci.cid; <\/p>\n
\u793a\u4f8b\u573a\u666f：\u4e3a study \u8868\u589e\u52a0 leader_id \u5b57\u6bb5，\u4ee3\u8868\u5b66\u751f\u7ec4\u957f\u7684 id\u3002<\/p>\n
ALTER TABLE study ADD COLUMN leader_id INT;<\/p>\n
— \u67e5\u8be2\u5b66\u751f\u548c\u4ed6\u7684\u7ec4\u957f\u540d\u5b57
\nSELECT a.name AS student_name, b.name AS leader_name
\nFROM study a
\nLEFT JOIN study b ON a.leader_id = b.id; <\/p>\n
— \u67e5\u8be2\u4e00\u73ed\u7684\u5b66\u751f：\u5148\u67e5\u4e00\u73ed\u7684 cid
\nSELECT * FROM study
\nWHERE class = (SELECT cid FROM class_info WHERE cname = '\u4e00\u73ed'); <\/p>\n
SELECT * FROM study
\nWHERE class IN (SELECT cid FROM class_info WHERE cid > 1); <\/p>\n
— \u5b58\u5728 id > 2 \u7684\u73ed\u7ea7，\u5c31\u67e5\u8be2\u8be5\u73ed\u7ea7\u7684\u5168\u90e8\u5b66\u751f
\nSELECT * FROM study s
\nWHERE EXISTS (
\n SELECT * FROM class_info ci
\n WHERE ci.cid > 2 AND s.class = ci.cid
\n); <\/p>\n
— UNION \u81ea\u52a8\u53bb\u91cd
\nSELECT name, age FROM study WHERE class = 1
\nUNION
\nSELECT name, age FROM study WHERE age > 25;<\/p>\n
— UNION ALL \u4e0d\u53bb\u91cd，\u901f\u5ea6\u66f4\u5feb
\nSELECT name, age FROM study WHERE class = 1
\nUNION ALL
\nSELECT name, age FROM study WHERE age > 25; <\/p>\n
— \u6dfb\u52a0\u5b57\u6bb5
\nALTER TABLE study ADD COLUMN phone VARCHAR(11);<\/p>\n
— \u4fee\u6539\u5b57\u6bb5\u7c7b\u578b
\nALTER TABLE study MODIFY COLUMN phone VARCHAR(13);<\/p>\n
— \u5220\u9664\u5b57\u6bb5
\nALTER TABLE study DROP COLUMN phone;<\/p>\n
— \u4fee\u6539\u8868\u540d
\nRENAME TABLE study TO student;<\/p>\n
— \u5220\u9664\u8868
\nDROP TABLE IF EXISTS class_info; <\/p>\n
— \u521b\u5efa\u7528\u6237
\nCREATE USER IF NOT EXISTS 'stu_user'@'localhost' IDENTIFIED BY '123456';<\/p>\n
— \u6388\u6743
\nGRANT SELECT, INSERT, UPDATE ON test.* TO 'stu_user'@'localhost';
\nFLUSH PRIVILEGES;<\/p>\n
— \u5220\u9664\u7528\u6237
\nDROP USER 'stu_user'@'localhost'; <\/p>\n
— \u5b57\u7b26\u4e32\u51fd\u6570
\nSELECT name, UPPER(name), LOWER(name) FROM study;
\nSELECT name, LENGTH(name) FROM study;<\/p>\n
— \u65e5\u671f\u51fd\u6570
\nSELECT NOW(), CURDATE();<\/p>\n
— \u6761\u4ef6\u5224\u65ad
\nSELECT name, IF(age >= 18, '\u6210\u5e74', '\u672a\u6210\u5e74') AS status FROM study;<\/p>\n
— CASE \u591a\u5206\u652f
\nSELECT name,
\n CASE
\n WHEN class = 1 THEN '\u4e00\u73ed'
\n WHEN class = 2 THEN '\u4e8c\u73ed'
\n ELSE '\u5176\u4ed6\u73ed\u7ea7'
\n END AS className
\nFROM study; <\/p>\n
\u4e3a\u4e86\u5de9\u56fa\u672c\u6587\u6240\u5b66\u7684 SQL \u77e5\u8bc6，\u8bf7\u5c1d\u8bd5\u5b8c\u6210\u4ee5\u4e0b 5 \u9053\u7efc\u5408\u7ec3\u4e60\u9898\u3002\u6240\u6709\u9898\u76ee\u5747\u57fa\u4e8e study \u548c class_info \u8868，\u5efa\u8bae\u5728\u672c\u5730 MySQL \u73af\u5883\u4e2d\u6267\u884c\u9a8c\u8bc1\u3002<\/p>\n
\u9898\u76ee：\u4f7f\u7528\u5206\u7ec4\u805a\u5408\u4e0e\u5b50\u67e5\u8be2，\u627e\u51fa\u6bcf\u4e2a\u73ed\u7ea7\u4e2d\u5e74\u9f84\u6700\u5927\u7684\u5b66\u751f\u4fe1\u606f（\u5305\u62ec\u5b66\u751f\u59d3\u540d\u3001\u5e74\u9f84\u3001\u73ed\u7ea7\u540d\u79f0）\u3002<\/p>\n
— \u53c2\u8003\u7b54\u6848
\nSELECT s.name, s.age, ci.cname
\nFROM study s
\nINNER JOIN class_info ci ON s.class = ci.cid
\nWHERE (s.class, s.age) IN (
\n SELECT class, MAX(age)
\n FROM study
\n GROUP BY class
\n); <\/p>\n
\u89e3\u6790：\u5148\u901a\u8fc7\u5b50\u67e5\u8be2\u83b7\u53d6\u6bcf\u4e2a\u73ed\u7ea7\u7684\u6700\u5927\u5e74\u9f84，\u7136\u540e\u901a\u8fc7 IN \u6761\u4ef6\u5339\u914d\u539f\u8868，\u6700\u540e\u8fde\u8868\u83b7\u53d6\u73ed\u7ea7\u540d\u79f0\u3002<\/p>\n
\u9898\u76ee：\u4f7f\u7528 NOT EXISTS \u6216 NOT IN \u5b50\u67e5\u8be2，\u67e5\u8be2\u51fa\u6ca1\u6709\u4efb\u4f55\u5b66\u751f\u7684\u73ed\u7ea7\u4fe1\u606f\u3002<\/p>\n
— \u65b9\u6cd5\u4e00：NOT EXISTS
\nSELECT *
\nFROM class_info ci
\nWHERE NOT EXISTS (
\n SELECT 1
\n FROM study s
\n WHERE s.class = ci.cid
\n);<\/p>\n
— \u65b9\u6cd5\u4e8c：NOT IN
\nSELECT *
\nFROM class_info
\nWHERE cid NOT IN (SELECT DISTINCT class FROM study WHERE class IS NOT NULL); <\/p>\n
\u89e3\u6790：NOT EXISTS \u66f4\u76f4\u89c2，\u68c0\u67e5\u662f\u5426\u5b58\u5728\u5173\u8054\u8bb0\u5f55；NOT IN \u9700\u6ce8\u610f\u5b50\u67e5\u8be2\u7ed3\u679c\u6392\u9664 NULL \u503c\u3002<\/p>\n
\u9898\u76ee：\u4f7f\u7528 GROUP BY \u4e0e CASE \u8868\u8fbe\u5f0f，\u7edf\u8ba1\u6bcf\u4e2a\u73ed\u7ea7\u7684\u7537\u751f\u548c\u5973\u751f\u4eba\u6570\u3002<\/p>\n
— \u53c2\u8003\u7b54\u6848
\nSELECT
\n ci.cname AS \u73ed\u7ea7,
\n COUNT(CASE WHEN s.to_sex = '\u7537' THEN 1 END) AS \u7537\u751f\u4eba\u6570,
\n COUNT(CASE WHEN s.to_sex = '\u5973' THEN 1 END) AS \u5973\u751f\u4eba\u6570
\nFROM study s
\nRIGHT JOIN class_info ci ON s.class = ci.cid
\nGROUP BY ci.cid, ci.cname; <\/p>\n
\u89e3\u6790：\u4f7f\u7528 RIGHT JOIN \u786e\u4fdd\u6240\u6709\u73ed\u7ea7\u90fd\u663e\u793a，\u5373\u4f7f\u6ca1\u6709\u5b66\u751f；CASE \u914d\u5408 COUNT \u5b9e\u73b0\u6761\u4ef6\u8ba1\u6570\u3002<\/p>\n
\u9898\u76ee：\u4f7f\u7528\u5173\u8054\u5b50\u67e5\u8be2，\u67e5\u8be2\u51fa\u5e74\u9f84\u5927\u4e8e\u5176\u6240\u5728\u73ed\u7ea7\u5e73\u5747\u5e74\u9f84\u7684\u5b66\u751f\u4fe1\u606f\u3002<\/p>\n
— \u53c2\u8003\u7b54\u6848
\nSELECT s.name, s.age, ci.cname
\nFROM study s
\nINNER JOIN class_info ci ON s.class = ci.cid
\nWHERE s.age > (
\n SELECT AVG(age)
\n FROM study s2
\n WHERE s2.class = s.class
\n); <\/p>\n
\u89e3\u6790：\u5173\u8054\u5b50\u67e5\u8be2\u4e3a\u6bcf\u4e2a\u5b66\u751f\u8ba1\u7b97\u5176\u6240\u5728\u73ed\u7ea7\u7684\u5e73\u5747\u5e74\u9f84，\u5916\u5c42\u67e5\u8be2\u7b5b\u9009\u5e74\u9f84\u5927\u4e8e\u8be5\u5e73\u5747\u503c\u7684\u5b66\u751f\u3002<\/p>\n
\u9898\u76ee：\u67e5\u8be2\u201c\u4e00\u73ed\u7684\u6240\u6709\u5b66\u751f\u201d\u4e0e\u201c\u5e74\u9f84\u5c0f\u4e8e 20 \u5c81\u7684\u6240\u6709\u5b66\u751f\u201d\u7684\u5e76\u96c6，\u8981\u6c42\u663e\u793a\u5b66\u751f\u59d3\u540d\u3001\u5e74\u9f84\u3001\u73ed\u7ea7\u540d\u79f0，\u5e76\u53bb\u9664\u91cd\u590d\u8bb0\u5f55\u3002<\/p>\n
— \u53c2\u8003\u7b54\u6848
\nSELECT s.name, s.age, ci.cname
\nFROM study s
\nINNER JOIN class_info ci ON s.class = ci.cid
\nWHERE ci.cname = '\u4e00\u73ed'
\nUNION
\nSELECT s.name, s.age, ci.cname
\nFROM study s
\nINNER JOIN class_info ci ON s.class = ci.cid
\nWHERE s.age < 20; <\/p>\n
\u89e3\u6790：\u4e24\u4e2a\u67e5\u8be2\u5206\u522b\u901a\u8fc7\u8fde\u8868\u83b7\u53d6\u73ed\u7ea7\u540d\u79f0，\u4f7f\u7528 UNION \u81ea\u52a8\u53bb\u91cd\u5408\u5e76\u7ed3\u679c\u3002<\/p>\n
\u63d0\u793a：\u5b8c\u6210\u7ec3\u4e60\u540e，\u53ef\u5c1d\u8bd5\u4fee\u6539\u6761\u4ef6\u6216\u4f7f\u7528\u5176\u4ed6\u8bed\u6cd5（\u5982 LEFT JOIN\u3001HAVING）\u5b9e\u73b0\u76f8\u540c\u6548\u679c，\u4ee5\u52a0\u6df1\u7406\u89e3\u3002<\/p>\n
\u4e3a\u4e86\u6784\u5efa\u66f4\u5b8c\u6574\u7684MySQL\u77e5\u8bc6\u4f53\u7cfb，\u4ee5\u4e0b\u8865\u5145\u57fa\u7840\u7bc7\u3001\u8fdb\u9636\u7bc7\u548c\u8fd0\u7ef4\u7bc7\u7684\u6838\u5fc3\u6982\u5ff5\u53ca\u5bf9\u5e94\u7684SQL\u8bed\u53e5\u793a\u4f8b\u3002<\/p>\n
\u7ea6\u675f（Constraints）\u662f\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf（DBMS）\u7528\u4e8e\u5f3a\u5236\u6570\u636e\u5b8c\u6574\u6027\u548c\u4e00\u81f4\u6027\u7684\u89c4\u5219\u3002\u5b83\u4eec\u5b9a\u4e49\u5728\u8868\u7ed3\u6784\u4e0a，\u786e\u4fdd\u8868\u4e2d\u7684\u6570\u636e\u7b26\u5408\u7279\u5b9a\u7684\u4e1a\u52a1\u89c4\u5219\u548c\u903b\u8f91\u8981\u6c42，\u9632\u6b62\u65e0\u6548\u6216\u4e0d\u4e00\u81f4\u7684\u6570\u636e\u88ab\u63d2\u5165\u3001\u66f4\u65b0\u6216\u5220\u9664\u3002\u7ea6\u675f\u662f\u6570\u636e\u5e93\u8bbe\u8ba1\u7684\u91cd\u8981\u7ec4\u6210\u90e8\u5206，\u80fd\u6709\u6548\u7ef4\u62a4\u6570\u636e\u7684\u51c6\u786e\u6027\u548c\u53ef\u9760\u6027\u3002<\/p>\n
MySQL \u652f\u6301\u4ee5\u4e0b\u51e0\u79cd\u4e3b\u8981\u7ea6\u675f\u7c7b\u578b：<\/p>\n
\u7ea6\u675f\u53ef\u4ee5\u5728\u521b\u5efa\u8868\u65f6\u5b9a\u4e49，\u4e5f\u53ef\u4ee5\u5728\u8868\u521b\u5efa\u540e\u901a\u8fc7 ALTER TABLE \u8bed\u53e5\u6dfb\u52a0\u6216\u5220\u9664\u3002\u5408\u7406\u4f7f\u7528\u7ea6\u675f\u80fd\u51cf\u5c11\u5e94\u7528\u5c42\u7684\u6821\u9a8c\u903b\u8f91，\u5c06\u6570\u636e\u89c4\u5219\u4e0b\u63a8\u5230\u6570\u636e\u5e93\u5c42\u9762\u3002<\/p>\n
— \u521b\u5efa\u5e26\u7ea6\u675f\u7684\u8868
\nCREATE TABLE student_constraint (
\n id INT PRIMARY KEY AUTO_INCREMENT COMMENT '\u4e3b\u952e',
\n name VARCHAR(50) NOT NULL COMMENT '\u59d3\u540d，\u975e\u7a7a',
\n email VARCHAR(100) UNIQUE COMMENT '\u90ae\u7bb1，\u552f\u4e00',
\n age INT DEFAULT 18 COMMENT '\u5e74\u9f84，\u9ed8\u8ba418',
\n class_id INT,
\n CHECK (age >= 0 AND age <= 120) COMMENT '\u68c0\u67e5\u7ea6\u675f：\u5e74\u9f84\u8303\u56f4',
\n CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class_info(cid)
\n ON DELETE SET NULL ON UPDATE CASCADE
\n);<\/p>\n
— \u6dfb\u52a0\u7ea6\u675f
\nALTER TABLE study ADD CONSTRAINT pk_study PRIMARY KEY (id);
\nALTER TABLE study MODIFY COLUMN name VARCHAR(50) NOT NULL;
\nALTER TABLE study ADD CONSTRAINT uq_email UNIQUE (email);
\nALTER TABLE study ADD CONSTRAINT fk_study_class FOREIGN KEY (class) REFERENCES class_info(cid);<\/p>\n
— \u5220\u9664\u5916\u952e\u7ea6\u675f
\nALTER TABLE study DROP FOREIGN KEY fk_study_class; <\/p>\n
\u5728\u6570\u636e\u5e93\u8bbe\u8ba1\u4e2d，\u8868\u4e0e\u8868\u4e4b\u95f4\u901a\u5e38\u5b58\u5728\u4e09\u79cd\u57fa\u672c\u5173\u7cfb：\u4e00\u5bf9\u591a（One-to-Many）\u3001\u591a\u5bf9\u591a（Many-to-Many）\u548c\u4e00\u5bf9\u4e00（One-to-One）\u3002\u5408\u7406\u8bbe\u8ba1\u8fd9\u4e9b\u5173\u7cfb\u662f\u6784\u5efa\u9ad8\u6548\u3001\u53ef\u7ef4\u62a4\u6570\u636e\u5e93\u7684\u5173\u952e\u3002<\/p>\n
\u4e00\u5bf9\u591a\u662f\u6700\u5e38\u89c1\u7684\u5173\u7cfb\u3002\u4e00\u4e2a\u5b9e\u4f53（\u5982\u73ed\u7ea7）\u53ef\u4ee5\u5173\u8054\u591a\u4e2a\u5176\u4ed6\u5b9e\u4f53（\u5982\u5b66\u751f），\u4f46\u6bcf\u4e2a\u5b66\u751f\u53ea\u5c5e\u4e8e\u4e00\u4e2a\u73ed\u7ea7\u3002\u5728\u6570\u636e\u5e93\u4e2d，\u901a\u5e38\u5728\u201c\u591a\u201d\u7684\u4e00\u65b9（\u5b66\u751f\u8868）\u6dfb\u52a0\u4e00\u4e2a\u5916\u952e\u5b57\u6bb5，\u6307\u5411\u201c\u4e00\u201d\u7684\u4e00\u65b9（\u73ed\u7ea7\u8868）\u7684\u4e3b\u952e\u3002<\/p>\n
\u793a\u4f8b：\u672c\u6587\u4e2d\u7684 study \u8868（\u5b66\u751f）\u4e0e class_info \u8868（\u73ed\u7ea7）\u5c31\u662f\u5178\u578b\u7684\u4e00\u5bf9\u591a\u5173\u7cfb\u3002<\/p>\n
— \u4e00\u5bf9\u591a\u5173\u7cfb\u5df2\u5728 study \u8868\u4e2d\u5b9e\u73b0
\n— study.class \u5b57\u6bb5\u4f5c\u4e3a\u5916\u952e，\u5f15\u7528 class_info.cid
\nSELECT s.name, s.age, ci.cname
\nFROM study s
\nINNER JOIN class_info ci ON s.class = ci.cid; <\/p>\n
\u591a\u5bf9\u591a\u5173\u7cfb\u6307\u4e00\u4e2a\u5b9e\u4f53\u53ef\u4ee5\u5173\u8054\u591a\u4e2a\u5176\u4ed6\u5b9e\u4f53，\u53cd\u4e4b\u4ea6\u7136\u3002\u4f8b\u5982，\u4e00\u4e2a\u5b66\u751f\u53ef\u4ee5\u9009\u62e9\u591a\u95e8\u8bfe\u7a0b，\u4e00\u95e8\u8bfe\u7a0b\u4e5f\u53ef\u4ee5\u88ab\u591a\u4e2a\u5b66\u751f\u9009\u62e9\u3002\u8fd9\u79cd\u5173\u7cfb\u65e0\u6cd5\u76f4\u63a5\u901a\u8fc7\u4e24\u4e2a\u8868\u7684\u5916\u952e\u5b9e\u73b0，\u9700\u8981\u5f15\u5165\u4e00\u4e2a\u5173\u8054\u8868（Junction Table）\u6765\u5b58\u50a8\u4e24\u4e2a\u8868\u4e4b\u95f4\u7684\u5173\u8054\u5173\u7cfb\u3002<\/p>\n
\u8bbe\u8ba1\u8981\u70b9：<\/p>\n
— \u521b\u5efa\u8bfe\u7a0b\u8868
\nCREATE TABLE course (
\n course_id INT PRIMARY KEY AUTO_INCREMENT,
\n course_name VARCHAR(50) NOT NULL
\n);<\/p>\n
— \u521b\u5efa\u5b66\u751f\u9009\u8bfe\u5173\u8054\u8868
\nCREATE TABLE student_course (
\n student_id INT,
\n course_id INT,
\n score DECIMAL(5,2), — \u53ef\u9009：\u8bb0\u5f55\u5b66\u751f\u5728\u8be5\u8bfe\u7a0b\u7684\u6210\u7ee9
\n PRIMARY KEY (student_id, course_id), — \u590d\u5408\u4e3b\u952e
\n FOREIGN KEY (student_id) REFERENCES study(id) ON DELETE CASCADE,
\n FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE CASCADE
\n);<\/p>\n
— \u67e5\u8be2\u5b66\u751f\u53ca\u5176\u6240\u9009\u8bfe\u7a0b
\nSELECT s.name AS student_name, c.course_name, sc.score
\nFROM study s
\nINNER JOIN student_course sc ON s.id = sc.student_id
\nINNER JOIN course c ON sc.course_id = c.course_id; <\/p>\n
\u4e00\u5bf9\u4e00\u5173\u7cfb\u6307\u4e00\u4e2a\u5b9e\u4f53\u6700\u591a\u53ea\u4e0e\u53e6\u4e00\u4e2a\u5b9e\u4f53\u5173\u8054\u4e00\u6b21\u3002\u8fd9\u79cd\u5173\u7cfb\u76f8\u5bf9\u5c11\u89c1，\u901a\u5e38\u7528\u4e8e\u5c06\u4e00\u5f20\u5927\u8868\u62c6\u5206\u4e3a\u591a\u4e2a\u5c0f\u8868，\u4ee5\u63d0\u5347\u67e5\u8be2\u6027\u80fd\u6216\u6ee1\u8db3\u4e1a\u52a1\u9694\u79bb\u9700\u6c42\u3002<\/p>\n
\u5b9e\u73b0\u65b9\u5f0f：<\/p>\n
— \u65b9\u5f0f\u4e00：\u5171\u4eab\u4e3b\u952e（student_detail \u8868\u7684\u4e3b\u952e student_id \u5f15\u7528 study.id）
\nCREATE TABLE student_detail (
\n student_id INT PRIMARY KEY, — \u65e2\u662f\u4e3b\u952e，\u4e5f\u662f\u5916\u952e
\n phone VARCHAR(20),
\n address TEXT,
\n FOREIGN KEY (student_id) REFERENCES study(id) ON DELETE CASCADE
\n);<\/p>\n
— \u65b9\u5f0f\u4e8c：\u5916\u952e\u552f\u4e00\u7ea6\u675f
\nCREATE TABLE student_detail (
\n id INT PRIMARY KEY AUTO_INCREMENT,
\n student_id INT UNIQUE, — \u552f\u4e00\u7ea6\u675f\u786e\u4fdd\u4e00\u5bf9\u4e00
\n phone VARCHAR(20),
\n address TEXT,
\n FOREIGN KEY (student_id) REFERENCES study(id) ON DELETE CASCADE
\n);<\/p>\n
— \u67e5\u8be2\u5b66\u751f\u53ca\u5176\u8be6\u7ec6\u4fe1\u606f
\nSELECT s.name, s.age, sd.phone, sd.address
\nFROM study s
\nLEFT JOIN student_detail sd ON s.id = sd.student_id; <\/p>\n
\u5728\u5b9a\u4e49\u5916\u952e\u65f6，\u53ef\u4ee5\u6307\u5b9a\u7ea7\u8054\u64cd\u4f5c，\u4ee5\u81ea\u52a8\u7ef4\u62a4\u6570\u636e\u7684\u4e00\u81f4\u6027：<\/p>\n
— \u793a\u4f8b：\u5e26\u7ea7\u8054\u64cd\u4f5c\u7684\u5916\u952e
\nALTER TABLE study
\nADD CONSTRAINT fk_study_class
\nFOREIGN KEY (class) REFERENCES class_info(cid)
\nON DELETE SET NULL ON UPDATE CASCADE; <\/p>\n
\u5408\u7406\u8fd0\u7528\u591a\u8868\u5173\u7cfb\u8bbe\u8ba1\u548c\u5916\u952e\u7ea6\u675f，\u53ef\u4ee5\u6781\u5927\u5730\u4fdd\u8bc1\u6570\u636e\u7684\u5b8c\u6574\u6027\u548c\u4e00\u81f4\u6027，\u4e3a\u590d\u6742\u7684\u4e1a\u52a1\u67e5\u8be2\u6253\u4e0b\u575a\u5b9e\u57fa\u7840\u3002<\/p>\n
\u4e8b\u52a1（Transactions）\u662f\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf（DBMS）\u4e2d\u4fdd\u8bc1\u6570\u636e\u4e00\u81f4\u6027\u548c\u5b8c\u6574\u6027\u7684\u6838\u5fc3\u673a\u5236\u3002\u5b83\u662f\u4e00\u7ec4\u4e0d\u53ef\u5206\u5272\u7684\u6570\u636e\u5e93\u64cd\u4f5c\u5e8f\u5217，\u8981\u4e48\u5168\u90e8\u6210\u529f\u6267\u884c，\u8981\u4e48\u5168\u90e8\u5931\u8d25\u56de\u6eda，\u786e\u4fdd\u6570\u636e\u5e93\u4ece\u4e00\u4e2a\u4e00\u81f4\u6027\u72b6\u6001\u8f6c\u6362\u5230\u53e6\u4e00\u4e2a\u4e00\u81f4\u6027\u72b6\u6001\u3002\u4e8b\u52a1\u4e3b\u8981\u7528\u4e8e\u5904\u7406\u9700\u8981\u591a\u4e2a\u6b65\u9aa4\u624d\u80fd\u5b8c\u6210\u7684\u4e1a\u52a1\u903b\u8f91，\u5982\u94f6\u884c\u8f6c\u8d26\u3001\u8ba2\u5355\u652f\u4ed8\u7b49\u573a\u666f\u3002<\/p>\n
\u4e8b\u52a1\u5177\u6709 ACID \u56db\u5927\u7279\u6027：<\/p>\n
\u5f53\u591a\u4e2a\u4e8b\u52a1\u5e76\u53d1\u6267\u884c\u65f6，\u5982\u679c\u6ca1\u6709\u9002\u5f53\u7684\u9694\u79bb\u673a\u5236，\u53ef\u80fd\u4f1a\u51fa\u73b0\u4ee5\u4e0b\u95ee\u9898：<\/p>\n
MySQL \u63d0\u4f9b\u4e86\u56db\u79cd\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b\u6765\u89e3\u51b3\u4e0a\u8ff0\u5e76\u53d1\u95ee\u9898：<\/p>\n
— \u4e8b\u52a1\u57fa\u672c\u64cd\u4f5c
\nSTART TRANSACTION; — \u6216 BEGIN<\/p>\n
— \u6267\u884c\u4e00\u7cfb\u5217SQL
\nUPDATE study SET age = age + 1 WHERE id = 1;
\nINSERT INTO class_info (cname) VALUES ('\u56db\u73ed');<\/p>\n
— \u63d0\u4ea4\u4e8b\u52a1
\nCOMMIT;<\/p>\n
— \u6216\u56de\u6eda\u4e8b\u52a1
\nROLLBACK;<\/p>\n
— \u8bbe\u7f6e\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b
\nSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
\nSET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
\nSET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;<\/p>\n
— \u67e5\u770b\u5f53\u524d\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b
\nSELECT @@transaction_isolation;<\/p>\n
— \u6f14\u793a\u810f\u8bfb\u573a\u666f（\u9700\u8981\u5728\u4e24\u4e2a\u4f1a\u8bdd\u4e2d\u6267\u884c）
\n— \u4f1a\u8bdd1：\u5f00\u542f\u4e8b\u52a1\u5e76\u4fee\u6539\u6570\u636e\u4f46\u4e0d\u63d0\u4ea4
\nSTART TRANSACTION;
\nUPDATE study SET age = 30 WHERE id = 1;
\n— \u6b64\u65f6 age=30 \u672a\u63d0\u4ea4<\/p>\n
— \u4f1a\u8bdd2：\u8bbe\u7f6e\u9694\u79bb\u7ea7\u522b\u4e3a READ UNCOMMITTED \u5e76\u67e5\u8be2
\nSET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
\nSTART TRANSACTION;
\nSELECT age FROM study WHERE id = 1; — \u53ef\u80fd\u8bfb\u523030（\u810f\u8bfb）
\nCOMMIT;<\/p>\n
— \u4f1a\u8bdd1：\u56de\u6eda
\nROLLBACK; — age\u6062\u590d\u539f\u503c，\u4f1a\u8bdd2\u8bfb\u5230\u768430\u662f\u65e0\u6548\u6570\u636e<\/p>\n
— \u907f\u514d\u810f\u8bfb：\u4f7f\u7528 READ COMMITTED \u6216\u66f4\u9ad8\u9694\u79bb\u7ea7\u522b
\nSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; <\/p>\n
\u5728\u5b9e\u9645\u5f00\u53d1\u4e2d，\u5e94\u6839\u636e\u4e1a\u52a1\u9700\u6c42\u9009\u62e9\u5408\u9002\u7684\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b\u3002\u901a\u5e38\u4f7f\u7528\u9ed8\u8ba4\u7684 REPEATABLE READ \u7ea7\u522b，\u5728\u9700\u8981\u66f4\u9ad8\u4e00\u81f4\u6027\u65f6\u4f7f\u7528 SERIALIZABLE，\u5728\u53ea\u8bfb\u573a\u666f\u4e14\u5bf9\u6570\u636e\u4e00\u81f4\u6027\u8981\u6c42\u4e0d\u9ad8\u65f6\u53ef\u4ee5\u8003\u8651 READ COMMITTED\u3002<\/p>\n
\u5b58\u50a8\u5f15\u64ce（Storage Engine）\u662f MySQL \u4e2d\u8d1f\u8d23\u6570\u636e\u5b58\u50a8\u3001\u7d22\u5f15\u7ba1\u7406\u3001\u4e8b\u52a1\u5904\u7406\u7b49\u5e95\u5c42\u673a\u5236\u7684\u7ec4\u4ef6\u3002\u4e0d\u540c\u7684\u5b58\u50a8\u5f15\u64ce\u5728\u6027\u80fd\u3001\u4e8b\u52a1\u652f\u6301\u3001\u9501\u7c92\u5ea6\u3001\u5d29\u6e83\u6062\u590d\u7b49\u65b9\u9762\u5404\u6709\u5dee\u5f02，\u9009\u62e9\u5408\u9002\u7684\u5b58\u50a8\u5f15\u64ce\u5bf9\u7cfb\u7edf\u6574\u4f53\u8868\u73b0\u81f3\u5173\u91cd\u8981\u3002<\/p>\n
MySQL \u4e2d\u6700\u5e38\u7528\u7684\u5b58\u50a8\u5f15\u64ce\u662f InnoDB \u548c MyISAM\u3002\u5176\u4e2d InnoDB \u662f MySQL 5.5 \u4e4b\u540e\u7684\u9ed8\u8ba4\u5f15\u64ce，\u652f\u6301\u4e8b\u52a1\u3001\u884c\u7ea7\u9501\u548c\u5916\u952e\u7ea6\u675f，\u9002\u5408\u7edd\u5927\u591a\u6570\u4e1a\u52a1\u573a\u666f；MyISAM \u4e0d\u652f\u6301\u4e8b\u52a1，\u4f7f\u7528\u8868\u7ea7\u9501，\u5728\u53ea\u8bfb\u6216\u8bfb\u591a\u5199\u5c11\u7684\u573a\u666f\u4e0b\u67e5\u8be2\u6027\u80fd\u8f83\u597d；MEMORY \u5f15\u64ce\u5c06\u6570\u636e\u5b58\u50a8\u5728\u5185\u5b58\u4e2d，\u8bfb\u5199\u901f\u5ea6\u6781\u5feb，\u4f46\u91cd\u542f\u540e\u6570\u636e\u4f1a\u4e22\u5931，\u9002\u5408\u4e34\u65f6\u8868\u6216\u7f13\u5b58\u7c7b\u6570\u636e\u3002<\/p>\n
InnoDB \u4e0e MyISAM \u7684\u6838\u5fc3\u533a\u522b：<\/p>\n
\u5728\u5b9e\u9645\u5f00\u53d1\u4e2d，\u5efa\u8bae\u4f18\u5148\u4f7f\u7528 InnoDB，\u53ea\u6709\u5728\u660e\u786e\u4e0d\u9700\u8981\u4e8b\u52a1\u4e14\u4ee5\u67e5\u8be2\u4e3a\u4e3b\u7684\u573a\u666f\u4e0b\u624d\u8003\u8651 MyISAM\u3002\u53ef\u4ee5\u901a\u8fc7 SHOW ENGINES \u67e5\u770b\u5f53\u524d MySQL \u652f\u6301\u7684\u6240\u6709\u5f15\u64ce\u53ca\u5176\u7279\u6027\u3002<\/p>\n
— \u67e5\u770b\u6240\u6709\u5b58\u50a8\u5f15\u64ce
\nSHOW ENGINES;<\/p>\n
— \u67e5\u770b\u8868\u7684\u5b58\u50a8\u5f15\u64ce
\nSHOW TABLE STATUS LIKE 'study';<\/p>\n
— \u521b\u5efa\u8868\u65f6\u6307\u5b9a\u5b58\u50a8\u5f15\u64ce
\nCREATE TABLE myisam_table (
\n id INT PRIMARY KEY
\n) ENGINE=MyISAM;<\/p>\n
CREATE TABLE memory_table (
\n id INT PRIMARY KEY
\n) ENGINE=MEMORY;<\/p>\n
— \u4fee\u6539\u8868\u7684\u5b58\u50a8\u5f15\u64ce
\nALTER TABLE study ENGINE = InnoDB;<\/p>\n
— \u67e5\u770b InnoDB \u7f13\u51b2\u6c60\u5927\u5c0f
\nSHOW VARIABLES LIKE 'innodb_buffer_pool_size';<\/p>\n
— \u67e5\u770b InnoDB \u662f\u5426\u5f00\u542f\u72ec\u7acb\u8868\u7a7a\u95f4
\nSHOW VARIABLES LIKE 'innodb_file_per_table'; <\/p>\n
\u7d22\u5f15（Index）\u662f\u6570\u636e\u5e93\u7cfb\u7edf\u4e2d\u7528\u4e8e\u52a0\u901f\u6570\u636e\u68c0\u7d22\u7684\u4e00\u79cd\u6570\u636e\u7ed3\u6784，\u7c7b\u4f3c\u4e8e\u4e66\u7c4d\u7684\u76ee\u5f55\u3002\u901a\u8fc7\u7d22\u5f15，MySQL \u53ef\u4ee5\u5feb\u901f\u5b9a\u4f4d\u5230\u76ee\u6807\u6570\u636e\u884c，\u800c\u65e0\u9700\u626b\u63cf\u6574\u5f20\u8868\u3002\u5408\u7406\u4f7f\u7528\u7d22\u5f15\u80fd\u663e\u8457\u63d0\u5347\u67e5\u8be2\u6027\u80fd，\u4f46\u7d22\u5f15\u4e5f\u4f1a\u5360\u7528\u989d\u5916\u5b58\u50a8\u7a7a\u95f4，\u5e76\u964d\u4f4e\u5199\u5165（INSERT\/UPDATE\/DELETE）\u7684\u901f\u5ea6，\u56e0\u6b64\u9700\u8981\u6743\u8861\u5229\u5f0a\u3002<\/p>\n
\u7d22\u5f15\u7684\u5206\u7c7b：<\/p>\n
\u7d22\u5f15\u4f7f\u7528\u6ce8\u610f\u4e8b\u9879：<\/p>\n
— \u521b\u5efa\u7d22\u5f15
\nCREATE INDEX idx_age ON study(age);
\nCREATE UNIQUE INDEX idx_name ON study(name);
\nCREATE INDEX idx_class_age ON study(class, age); — \u590d\u5408\u7d22\u5f15<\/p>\n
— \u67e5\u770b\u7d22\u5f15
\nSHOW INDEX FROM study;<\/p>\n
— \u5220\u9664\u7d22\u5f15
\nDROP INDEX idx_age ON study;<\/p>\n
— \u4f7f\u7528EXPLAIN\u5206\u6790\u67e5\u8be2
\nEXPLAIN SELECT * FROM study WHERE age > 20;<\/p>\n
— \u5f3a\u5236\u4f7f\u7528\u7d22\u5f15
\nSELECT * FROM study FORCE INDEX (idx_age) WHERE age > 20;<\/p>\n
— \u521b\u5efa\u5168\u6587\u7d22\u5f15
\nCREATE FULLTEXT INDEX idx_name_fulltext ON study(name);<\/p>\n
— \u4f7f\u7528\u5168\u6587\u7d22\u5f15\u67e5\u8be2
\nSELECT * FROM study WHERE MATCH(name) AGAINST('\u5f20' IN NATURAL LANGUAGE MODE); <\/p>\n
SQL \u4f18\u5316\u662f\u6570\u636e\u5e93\u6027\u80fd\u8c03\u4f18\u7684\u6838\u5fc3\u73af\u8282\u3002\u901a\u8fc7\u4f18\u5316 SQL \u8bed\u53e5\u548c\u8868\u7ed3\u6784，\u53ef\u4ee5\u663e\u8457\u51cf\u5c11\u67e5\u8be2\u54cd\u5e94\u65f6\u95f4\u3001\u964d\u4f4e\u6570\u636e\u5e93\u8d1f\u8f7d\u3002\u4e0b\u9762\u4ece\u51e0\u4e2a\u5e38\u89c1\u7ef4\u5ea6\u4ecb\u7ecd\u4f18\u5316\u6280\u5de7\u3002<\/p>\n
\u4f18\u5316\u539f\u5219：<\/p>\n
— \u63d2\u5165\u4f18\u5316：\u6279\u91cf\u63d2\u5165
\nINSERT INTO study (name, age, to_sex, class) VALUES
\n('\u5f20\u4e09', 20, '\u7537', 1),
\n('\u674e\u56db', 21, '\u5973', 2),
\n('\u738b\u4e94', 22, '\u7537', 1);<\/p>\n
— \u4e3b\u952e\u4f18\u5316：\u4f7f\u7528\u81ea\u589e\u4e3b\u952e
\nALTER TABLE study MODIFY id INT AUTO_INCREMENT;<\/p>\n
— ORDER BY\u4f18\u5316：\u4f7f\u7528\u7d22\u5f15\u8986\u76d6
\nCREATE INDEX idx_class_age_name ON study(class, age, name);
\nEXPLAIN SELECT name, age FROM study WHERE class = 1 ORDER BY age;<\/p>\n
— GROUP BY\u4f18\u5316：\u4f7f\u7528\u7d22\u5f15
\nEXPLAIN SELECT class, COUNT(*) FROM study GROUP BY class;<\/p>\n
— LIMIT\u4f18\u5316：\u907f\u514d\u5927\u504f\u79fb\u91cf
\n— \u4e0d\u597d：SELECT * FROM study LIMIT 1000000, 10
\n— \u4f18\u5316：SELECT * FROM study WHERE id > 1000000 LIMIT 10<\/p>\n
— COUNT\u4f18\u5316：\u4f7f\u7528COUNT(1)\u6216COUNT(*)
\nSELECT COUNT(1) FROM study;
\nSELECT COUNT(*) FROM study;<\/p>\n
— UPDATE\u4f18\u5316：\u907f\u514d\u884c\u9501\u5347\u7ea7\u4e3a\u8868\u9501
\n— \u786e\u4fddWHERE\u6761\u4ef6\u4f7f\u7528\u7d22\u5f15\u5217
\nUPDATE study SET age = age + 1 WHERE id = 1; — id\u662f\u4e3b\u952e\u7d22\u5f15<\/p>\n
— \u907f\u514d\u5728\u7d22\u5f15\u5217\u4e0a\u4f7f\u7528\u51fd\u6570（\u4f1a\u5bfc\u81f4\u7d22\u5f15\u5931\u6548）
\n— \u4e0d\u597d：SELECT * FROM study WHERE YEAR(create_time) = 2024;
\n— \u4f18\u5316：SELECT * FROM study WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';<\/p>\n
— \u4f7f\u7528EXPLAIN\u67e5\u770b\u6267\u884c\u8ba1\u5212
\nEXPLAIN SELECT s.name, ci.cname
\nFROM study s
\nINNER JOIN class_info ci ON s.class = ci.cid
\nWHERE s.age > 20; <\/p>\n
\u89c6\u56fe（View）\u662f\u4e00\u4e2a\u865a\u62df\u8868，\u5176\u5185\u5bb9\u7531 SQL \u67e5\u8be2\u5b9a\u4e49\u3002\u89c6\u56fe\u4e0d\u5b58\u50a8\u5b9e\u9645\u6570\u636e，\u800c\u662f\u5728\u67e5\u8be2\u89c6\u56fe\u65f6\u52a8\u6001\u751f\u6210\u7ed3\u679c\u3002\u89c6\u56fe\u53ef\u4ee5\u770b\u4f5c\u662f\u5bf9\u590d\u6742\u67e5\u8be2\u7684\u5c01\u88c5，\u63d0\u4f9b\u4e86\u4e00\u79cd\u5b89\u5168\u3001\u7b80\u6d01\u7684\u6570\u636e\u8bbf\u95ee\u65b9\u5f0f\u3002<\/p>\n
\u89c6\u56fe\u7684\u4f18\u70b9：<\/p>\n
\u89c6\u56fe\u7684\u6ce8\u610f\u4e8b\u9879：<\/p>\n
— \u521b\u5efa\u89c6\u56fe
\nCREATE VIEW student_info_view AS
\nSELECT s.id, s.name, s.age, ci.cname
\nFROM study s
\nLEFT JOIN class_info ci ON s.class = ci.cid;<\/p>\n
— \u67e5\u8be2\u89c6\u56fe
\nSELECT * FROM student_info_view WHERE age > 20;<\/p>\n
— \u521b\u5efa\u5e26\u68c0\u67e5\u9009\u9879\u7684\u89c6\u56fe
\nCREATE VIEW adult_students AS
\nSELECT * FROM study WHERE age >= 18
\nWITH CHECK OPTION;<\/p>\n
— \u4fee\u6539\u89c6\u56fe
\nCREATE OR REPLACE VIEW student_info_view AS
\nSELECT s.id, s.name, s.age, s.to_sex, ci.cname
\nFROM study s
\nLEFT JOIN class_info ci ON s.class = ci.cid;<\/p>\n
— \u67e5\u770b\u89c6\u56fe\u5b9a\u4e49
\nSHOW CREATE VIEW student_info_view;<\/p>\n
— \u5220\u9664\u89c6\u56fe
\nDROP VIEW IF EXISTS student_info_view; <\/p>\n
\u5b58\u50a8\u8fc7\u7a0b（Stored Procedure）\u662f\u4e00\u7ec4\u9884\u7f16\u8bd1\u7684 SQL \u8bed\u53e5\u96c6\u5408，\u5b58\u50a8\u5728\u6570\u636e\u5e93\u670d\u52a1\u5668\u4e2d，\u53ef\u4ee5\u901a\u8fc7\u540d\u79f0\u8c03\u7528\u3002\u5b58\u50a8\u8fc7\u7a0b\u53ef\u4ee5\u63a5\u6536\u8f93\u5165\u53c2\u6570\u3001\u8fd4\u56de\u8f93\u51fa\u53c2\u6570，\u5e76\u652f\u6301\u6d41\u7a0b\u63a7\u5236\u8bed\u53e5（\u5982 IF\u3001CASE\u3001LOOP），\u9002\u5408\u5c01\u88c5\u590d\u6742\u7684\u4e1a\u52a1\u903b\u8f91\u3002<\/p>\n
\u5b58\u50a8\u8fc7\u7a0b\u7684\u4f18\u70b9：<\/p>\n
\u5b58\u50a8\u8fc7\u7a0b\u7684\u53c2\u6570\u7c7b\u578b：<\/p>\n
— \u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b
\nDELIMITER \/\/
\nCREATE PROCEDURE get_student_by_class(IN class_id INT)
\nBEGIN
\n SELECT * FROM study WHERE class = class_id;
\nEND \/\/
\nDELIMITER ;<\/p>\n
— \u8c03\u7528\u5b58\u50a8\u8fc7\u7a0b
\nCALL get_student_by_class(1);<\/p>\n
— \u5e26\u8f93\u51fa\u53c2\u6570\u7684\u5b58\u50a8\u8fc7\u7a0b
\nDELIMITER \/\/
\nCREATE PROCEDURE count_students_by_class(
\n IN class_id INT,
\n OUT student_count INT
\n)
\nBEGIN
\n SELECT COUNT(*) INTO student_count
\n FROM study WHERE class = class_id;
\nEND \/\/
\nDELIMITER ;<\/p>\n
— \u8c03\u7528
\nCALL count_students_by_class(1, @count);
\nSELECT @count;<\/p>\n
— \u5e26\u6d41\u7a0b\u63a7\u5236\u7684\u5b58\u50a8\u8fc7\u7a0b
\nDELIMITER \/\/
\nCREATE PROCEDURE get_student_status(IN student_id INT)
\nBEGIN
\n DECLARE student_age INT;
\n DECLARE status_msg VARCHAR(50);<\/p>\n
SELECT age INTO student_age FROM study WHERE id = student_id;<\/p>\n
IF student_age >= 18 THEN
\n SET status_msg = '\u6210\u5e74';
\n ELSE
\n SET status_msg = '\u672a\u6210\u5e74';
\n END IF;<\/p>\n
SELECT student_id AS id, student_age AS age, status_msg AS status;
\nEND \/\/
\nDELIMITER ;<\/p>\n
— \u8c03\u7528
\nCALL get_student_status(1);<\/p>\n
— \u5220\u9664\u5b58\u50a8\u8fc7\u7a0b
\nDROP PROCEDURE IF EXISTS get_student_by_class; <\/p>\n
\u89e6\u53d1\u5668（Trigger）\u662f\u6570\u636e\u5e93\u4e2d\u7684\u4e00\u79cd\u7279\u6b8a\u5b58\u50a8\u8fc7\u7a0b，\u5b83\u5728\u6307\u5b9a\u7684\u8868\u4e0a\u6267\u884c INSERT\u3001UPDATE\u3001DELETE \u64cd\u4f5c\u65f6\u81ea\u52a8\u89e6\u53d1\u6267\u884c\u3002\u89e6\u53d1\u5668\u5e38\u7528\u4e8e\u81ea\u52a8\u7ef4\u62a4\u6570\u636e\u4e00\u81f4\u6027\u3001\u5ba1\u8ba1\u65e5\u5fd7\u3001\u6570\u636e\u6821\u9a8c\u7b49\u573a\u666f，\u65e0\u9700\u5e94\u7528\u5c42\u663e\u5f0f\u8c03\u7528\u3002<\/p>\n
\u89e6\u53d1\u5668\u7684\u7c7b\u578b：<\/p>\n
\u89e6\u53d1\u5668\u4e2d\u7684\u5173\u952e\u5b57：<\/p>\n
— \u521b\u5efaINSERT\u89e6\u53d1\u5668：\u63d2\u5165\u524d\u6821\u9a8c\u5e74\u9f84，\u8d1f\u6570\u81ea\u52a8\u5f52\u96f6
\nDELIMITER \/\/
\nCREATE TRIGGER before_student_insert
\nBEFORE INSERT ON study
\nFOR EACH ROW
\nBEGIN
\n IF NEW.age < 0 THEN
\n SET NEW.age = 0;
\n END IF;
\nEND \/\/
\nDELIMITER ;<\/p>\n
— \u521b\u5efaUPDATE\u89e6\u53d1\u5668：\u66f4\u65b0\u540e\u8bb0\u5f55\u5ba1\u8ba1\u65e5\u5fd7
\nDELIMITER \/\/
\nCREATE TRIGGER after_student_update
\nAFTER UPDATE ON study
\nFOR EACH ROW
\nBEGIN
\n INSERT INTO student_audit(student_id, old_name, new_name, change_time)
\n VALUES (OLD.id, OLD.name, NEW.name, NOW());
\nEND \/\/
\nDELIMITER ;<\/p>\n
— \u521b\u5efaDELETE\u89e6\u53d1\u5668：\u5220\u9664\u524d\u5907\u4efd\u6570\u636e
\nDELIMITER \/\/
\nCREATE TRIGGER before_student_delete
\nBEFORE DELETE ON study
\nFOR EACH ROW
\nBEGIN
\n INSERT INTO deleted_students_backup
\n SELECT *, NOW() FROM study WHERE id = OLD.id;
\nEND \/\/
\nDELIMITER ;<\/p>\n
— \u67e5\u770b\u89e6\u53d1\u5668
\nSHOW TRIGGERS;<\/p>\n
— \u5220\u9664\u89e6\u53d1\u5668
\nDROP TRIGGER IF EXISTS before_student_insert; <\/p>\n
\u4f7f\u7528\u5efa\u8bae：\u89e6\u53d1\u5668\u867d\u7136\u80fd\u81ea\u52a8\u7ef4\u62a4\u6570\u636e\u903b\u8f91，\u4f46\u8fc7\u5ea6\u4f7f\u7528\u4f1a\u589e\u52a0\u6570\u636e\u5e93\u8d1f\u62c5，\u4e14\u9690\u5f0f\u6267\u884c\u4e0d\u6613\u6392\u67e5\u95ee\u9898\u3002\u5efa\u8bae\u4ec5\u5728\u786e\u5b9e\u9700\u8981\u6570\u636e\u5e93\u5c42\u4fdd\u8bc1\u4e00\u81f4\u6027\u7684\u573a\u666f\u4f7f\u7528，\u5e76\u4fdd\u6301\u89e6\u53d1\u5668\u903b\u8f91\u7b80\u5355\u6e05\u6670\u3002<\/p>\n<\/p>\n
\u9501（Lock）\u662f\u6570\u636e\u5e93\u7528\u4e8e\u63a7\u5236\u5e76\u53d1\u8bbf\u95ee\u3001\u4fdd\u8bc1\u6570\u636e\u4e00\u81f4\u6027\u7684\u91cd\u8981\u673a\u5236\u3002MySQL \u4e2d\u7684\u9501\u6309\u7c92\u5ea6\u53ef\u5206\u4e3a\u5168\u5c40\u9501\u3001\u8868\u7ea7\u9501\u548c\u884c\u7ea7\u9501，\u6309\u8bfb\u5199\u6027\u8d28\u53c8\u53ef\u5206\u4e3a\u5171\u4eab\u9501（\u8bfb\u9501）\u548c\u6392\u4ed6\u9501（\u5199\u9501）\u3002\u5408\u7406\u4f7f\u7528\u9501\u80fd\u907f\u514d\u5e76\u53d1\u4e8b\u52a1\u4e4b\u95f4\u7684\u6570\u636e\u51b2\u7a81，\u4f46\u8fc7\u5ea6\u52a0\u9501\u4e5f\u4f1a\u964d\u4f4e\u7cfb\u7edf\u541e\u5410\u91cf，\u56e0\u6b64\u9700\u8981\u6839\u636e\u4e1a\u52a1\u573a\u666f\u6743\u8861\u3002<\/p>\n
\u9501\u7684\u5206\u7c7b：<\/p>\n
\u9501\u7684\u4f7f\u7528\u6ce8\u610f\u4e8b\u9879：<\/p>\n
— \u5168\u5c40\u9501（\u5907\u4efd\u65f6\u4f7f\u7528）
\nFLUSH TABLES WITH READ LOCK;
\n— \u6267\u884c\u5907\u4efd\u64cd\u4f5c…
\nUNLOCK TABLES;<\/p>\n
— \u8868\u7ea7\u9501
\nLOCK TABLES study READ; — \u8bfb\u9501
\n— \u53ea\u80fd\u8bfb，\u4e0d\u80fd\u5199…
\nUNLOCK TABLES;<\/p>\n
LOCK TABLES study WRITE; — \u5199\u9501
\n— \u53ef\u4ee5\u8bfb\u5199…
\nUNLOCK TABLES;<\/p>\n
— \u884c\u7ea7\u9501（InnoDB）
\nSTART TRANSACTION;
\nSELECT * FROM study WHERE id = 1 FOR UPDATE; — \u6392\u4ed6\u9501
\n— \u5176\u4ed6\u4e8b\u52a1\u4e0d\u80fd\u4fee\u6539id=1\u7684\u8bb0\u5f55…
\nCOMMIT;<\/p>\n
— \u5171\u4eab\u9501（\u8bfb\u9501）
\nSTART TRANSACTION;
\nSELECT * FROM study WHERE id = 1 LOCK IN SHARE MODE;
\n— \u5176\u4ed6\u4e8b\u52a1\u53ef\u4ee5\u8bfb，\u4f46\u4e0d\u80fd\u4fee\u6539id=1\u7684\u8bb0\u5f55…
\nCOMMIT;<\/p>\n
— \u67e5\u770b\u9501\u4fe1\u606f
\nSHOW OPEN TABLES WHERE In_use > 0;
\nSELECT * FROM information_schema.INNODB_LOCKS;
\nSELECT * FROM information_schema.INNODB_LOCK_WAITS;<\/p>\n
— \u67e5\u770b\u5f53\u524d\u662f\u5426\u6709\u6b7b\u9501
\nSHOW ENGINE INNODB STATUS\\\\G <\/p>\n
\u65e5\u5fd7\u662f MySQL \u8fd0\u7ef4\u4e2d\u6392\u67e5\u95ee\u9898\u3001\u76d1\u63a7\u6027\u80fd\u7684\u91cd\u8981\u4f9d\u636e\u3002MySQL \u4e3b\u8981\u5305\u542b\u9519\u8bef\u65e5\u5fd7\u3001\u901a\u7528\u67e5\u8be2\u65e5\u5fd7\u3001\u6162\u67e5\u8be2\u65e5\u5fd7\u548c\u4e8c\u8fdb\u5236\u65e5\u5fd7\u56db\u7c7b，\u4e0b\u9762\u5206\u522b\u6f14\u793a\u5b83\u4eec\u7684\u67e5\u770b\u4e0e\u914d\u7f6e\u65b9\u6cd5\u3002<\/p>\n
— \u67e5\u770b\u9519\u8bef\u65e5\u5fd7\u4f4d\u7f6e
\nSHOW VARIABLES LIKE 'log_error';<\/p>\n
— \u5f00\u542f\u901a\u7528\u67e5\u8be2\u65e5\u5fd7
\nSET GLOBAL general_log = 'ON';
\nSHOW VARIABLES LIKE 'general_log%';<\/p>\n
— \u5f00\u542f\u6162\u67e5\u8be2\u65e5\u5fd7
\nSET GLOBAL slow_query_log = 'ON';
\nSET GLOBAL long_query_time = 2; — \u8d85\u8fc72\u79d2\u7684\u67e5\u8be2
\nSHOW VARIABLES LIKE 'slow_query%';<\/p>\n
— \u67e5\u770b\u4e8c\u8fdb\u5236\u65e5\u5fd7
\nSHOW BINARY LOGS;
\nSHOW BINLOG EVENTS IN 'binlog.000001' LIMIT 10;<\/p>\n
— \u6e05\u7406\u65e5\u5fd7
\nPURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
\nRESET MASTER; — \u5220\u9664\u6240\u6709\u4e8c\u8fdb\u5236\u65e5\u5fd7（\u8c28\u614e\u4f7f\u7528） <\/p>\n
\u65e5\u5fd7\u7c7b\u578b\u8bf4\u660e：<\/p>\n
\u4e3b\u4ece\u590d\u5236（Master-Slave Replication）\u662f MySQL \u9ad8\u53ef\u7528\u548c\u8bfb\u5199\u5206\u79bb\u7684\u5e38\u89c1\u65b9\u6848\u3002\u5176\u6838\u5fc3\u539f\u7406\u662f：\u4e3b\u5e93\u5c06\u6570\u636e\u53d8\u66f4\u5199\u5165\u4e8c\u8fdb\u5236\u65e5\u5fd7（binlog），\u4ece\u5e93\u901a\u8fc7 I\/O \u7ebf\u7a0b\u62c9\u53d6\u5e76\u5199\u5165\u4e2d\u7ee7\u65e5\u5fd7（relay log），\u518d\u7531 SQL \u7ebf\u7a0b\u91cd\u653e\u5b9e\u73b0\u6570\u636e\u540c\u6b65\u3002\u4e0b\u9762\u7ed9\u51fa\u5b8c\u6574\u7684\u914d\u7f6e\u6b65\u9aa4\u3002<\/p>\n
— \u4e3b\u5e93\u914d\u7f6e（my.cnf）
\n— [mysqld]
\n— server-id=1
\n— log-bin=mysql-bin
\n— binlog-format=ROW<\/p>\n
— \u521b\u5efa\u590d\u5236\u7528\u6237
\nCREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
\nGRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';<\/p>\n
— \u67e5\u770b\u4e3b\u5e93\u72b6\u6001
\nSHOW MASTER STATUS;<\/p>\n
— \u4ece\u5e93\u914d\u7f6e（my.cnf）
\n— [mysqld]
\n— server-id=2
\n— relay-log=mysql-relay-bin<\/p>\n
— \u914d\u7f6e\u4ece\u5e93\u8fde\u63a5\u4e3b\u5e93
\nCHANGE MASTER TO
\nMASTER_HOST='master_host',
\nMASTER_USER='repl',
\nMASTER_PASSWORD='repl_password',
\nMASTER_LOG_FILE='mysql-bin.000001',
\nMASTER_LOG_POS=154;<\/p>\n
— \u542f\u52a8\u4ece\u5e93\u590d\u5236
\nSTART SLAVE;<\/p>\n
— \u67e5\u770b\u4ece\u5e93\u72b6\u6001
\nSHOW SLAVE STATUS\\\\G <\/p>\n
\u914d\u7f6e\u8981\u70b9：<\/p>\n
\u5f53\u5355\u8868\u6570\u636e\u91cf\u8fc7\u5927\u3001\u5199\u5165\u5e76\u53d1\u8fc7\u9ad8\u65f6，\u53ef\u4ee5\u901a\u8fc7\u5206\u5e93\u5206\u8868\u6765\u6c34\u5e73\u6269\u5c55\u3002\u5e38\u89c1\u7b56\u7565\u5305\u62ec\u6c34\u5e73\u5206\u8868（\u6309\u67d0\u4e2a\u5b57\u6bb5\u5c06\u6570\u636e\u5206\u6563\u5230\u591a\u5f20\u7ed3\u6784\u76f8\u540c\u7684\u8868）\u548c\u5782\u76f4\u5206\u8868（\u5c06\u5927\u5b57\u6bb5\u62c6\u5206\u5230\u72ec\u7acb\u8868）\u3002\u4e0b\u9762\u7ed9\u51fa\u57fa\u7840\u793a\u4f8b\u3002<\/p>\n
— \u6c34\u5e73\u5206\u8868：\u6309id\u8303\u56f4\u5206\u8868
\nCREATE TABLE study_0 (
\n id INT PRIMARY KEY,
\n name VARCHAR(50),
\n age INT,
\n CHECK (id % 2 = 0)
\n);<\/p>\n
CREATE TABLE study_1 (
\n id INT PRIMARY KEY,
\n name VARCHAR(50),
\n age INT,
\n CHECK (id % 2 = 1)
\n);<\/p>\n
— \u4f7f\u7528UNION ALL\u67e5\u8be2\u6240\u6709\u5206\u8868
\nSELECT * FROM study_0 WHERE age > 20
\nUNION ALL
\nSELECT * FROM study_1 WHERE age > 20;<\/p>\n
— \u5782\u76f4\u5206\u8868：\u5c06\u5927\u5b57\u6bb5\u5206\u79bb
\nCREATE TABLE study_basic (
\n id INT PRIMARY KEY,
\n name VARCHAR(50),
\n age INT
\n);<\/p>\n
CREATE TABLE study_detail (
\n id INT PRIMARY KEY,
\n student_id INT,
\n address TEXT,
\n phone VARCHAR(20),
\n FOREIGN KEY (student_id) REFERENCES study_basic(id)
\n);<\/p>\n
— \u4f7f\u7528MyCat\u5206\u7247\u914d\u7f6e\u793a\u4f8b（\u914d\u7f6e\u6587\u4ef6）
\n— schema.xml: \u5b9a\u4e49\u903b\u8f91\u5e93\u548c\u5206\u7247\u89c4\u5219
\n— rule.xml: \u5b9a\u4e49\u5206\u7247\u7b97\u6cd5
\n— server.xml: \u5b9a\u4e49\u7528\u6237\u548c\u7cfb\u7edf\u53c2\u6570 <\/p>\n
\u5206\u5e93\u5206\u8868\u6ce8\u610f\u4e8b\u9879：<\/p>\n
\u65e5\u5e38\u8fd0\u7ef4\u4e2d，\u9700\u8981\u638c\u63e1\u8fdb\u7a0b\u7ba1\u7406\u3001\u53c2\u6570\u8c03\u4f18\u3001\u5907\u4efd\u6062\u590d\u548c\u8868\u7ef4\u62a4\u7b49\u5e38\u7528\u64cd\u4f5c\u3002\u4e0b\u9762\u6c47\u603b\u4e86\u7cfb\u7edf\u7ba1\u7406\u4e0e\u7ef4\u62a4\u7684\u6838\u5fc3\u547d\u4ee4\u3002<\/p>\n
— \u67e5\u770b\u7cfb\u7edf\u6570\u636e\u5e93
\nSHOW DATABASES LIKE '%schema%';<\/p>\n
— \u67e5\u770b\u8fdb\u7a0b\u5217\u8868
\nSHOW PROCESSLIST;<\/p>\n
— \u6740\u6b7b\u8fdb\u7a0b
\nKILL [process_id];<\/p>\n
— \u67e5\u770b\u53d8\u91cf
\nSHOW VARIABLES LIKE '%buffer%';
\nSHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';<\/p>\n
— \u8bbe\u7f6e\u53d8\u91cf
\nSET GLOBAL innodb_buffer_pool_size = 1073741824; — 1GB<\/p>\n
— \u5907\u4efd\u6570\u636e\u5e93
\n— mysqldump -u root -p test > backup.sql<\/p>\n
— \u6062\u590d\u6570\u636e\u5e93
\n— mysql -u root -p test < backup.sql<\/p>\n
— \u67e5\u770b\u8868\u7a7a\u95f4\u4fe1\u606f
\nSELECT
\n TABLE_SCHEMA,
\n TABLE_NAME,
\n ENGINE,
\n TABLE_ROWS,
\n DATA_LENGTH,
\n INDEX_LENGTH
\nFROM information_schema.TABLES
\nWHERE TABLE_SCHEMA = 'test';<\/p>\n
— \u4f18\u5316\u8868
\nOPTIMIZE TABLE study;<\/p>\n
— \u5206\u6790\u8868
\nANALYZE TABLE study;<\/p>\n
— \u4fee\u590d\u8868
\nREPAIR TABLE study; <\/p>\n
\u7ef4\u62a4\u5efa\u8bae：<\/p>\n
— \u67e5\u770bInnoDB\u72b6\u6001
\nSHOW ENGINE INNODB STATUS\\\\G<\/p>\n
— \u67e5\u770b\u7f13\u51b2\u6c60\u4fe1\u606f
\nSELECT
\n POOL_ID,
\n POOL_SIZE,
\n FREE_BUFFERS,
\n DATABASE_PAGES
\nFROM information_schema.INNODB_BUFFER_POOL_STATS;<\/p>\n
— \u67e5\u770b\u4e8b\u52a1\u4fe1\u606f
\nSELECT * FROM information_schema.INNODB_TRX;<\/p>\n
— \u67e5\u770b\u9501\u4fe1\u606f
\nSELECT * FROM information_schema.INNODB_LOCKS;
\nSELECT * FROM information_schema.INNODB_LOCK_WAITS;<\/p>\n
— \u8bbe\u7f6e\u4e8b\u52a1\u9694\u79bb\u7ea7\u522b
\nSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
\nSET GLOBAL TRANSACTION_ISOLATION = 'REPEATABLE-READ';<\/p>\n
— MVCC\u76f8\u5173\u67e5\u8be2
\n— \u67e5\u770b\u5f53\u524d\u4e8b\u52a1ID
\nSELECT TRX_ID FROM information_schema.INNODB_TRX
\nWHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();<\/p>\n
— \u67e5\u770bundo\u65e5\u5fd7
\nSHOW VARIABLES LIKE 'innodb_undo%';<\/p>\n
— \u67e5\u770bredo\u65e5\u5fd7
\nSHOW VARIABLES LIKE 'innodb_log%'; <\/p>\n
\u4ee5\u4e0a\u8865\u5145\u5185\u5bb9\u6db5\u76d6\u4e86MySQL\u4ece\u57fa\u7840\u5230\u8fdb\u9636\u518d\u5230\u8fd0\u7ef4\u7684\u6838\u5fc3\u6982\u5ff5\u548cSQL\u8bed\u53e5\u3002\u5efa\u8bae\u5728\u5b9e\u9645\u73af\u5883\u4e2d\u9010\u6b65\u5b9e\u8df5\u8fd9\u4e9b\u793a\u4f8b，\u5e76\u7ed3\u5408\u5b98\u65b9\u6587\u6863\u6df1\u5165\u7406\u89e3\u6bcf\u4e2a\u6982\u5ff5\u7684\u539f\u7406\u548c\u5e94\u7528\u573a\u666f\u3002<\/p>\n
\u672c\u6587\u901a\u8fc7 study \u4e0e class_info \u4e24\u5f20\u8868，\u7cfb\u7edf\u6027\u5730\u6f14\u793a\u4e86 MySQL \u57fa\u7840 SQL \u7684\u5b8c\u6574\u8bed\u6cd5\u4f53\u7cfb，\u6db5\u76d6\u4e86\u4ece\u7b80\u5355\u7684\u589e\u5220\u6539\u67e5（CRUD）\u5230\u590d\u6742\u7684\u591a\u8868\u8fde\u63a5\u3001\u5b50\u67e5\u8be2\u3001\u96c6\u5408\u64cd\u4f5c\u4e0e\u6570\u636e\u63a7\u5236\u3002\u5efa\u8bae\u8bfb\u8005\u5728\u672c\u5730\u73af\u5883\u4e2d\u9010\u6761\u6267\u884c\u8fd9\u4e9b SQL，\u89c2\u5bdf\u7ed3\u679c，\u5e76\u5c1d\u8bd5\u4fee\u6539\u6761\u4ef6\u4ee5\u52a0\u6df1\u7406\u89e3\u3002\u638c\u63e1\u8fd9\u4e9b\u57fa\u7840\u662f\u8fdb\u884c\u66f4\u9ad8\u7ea7\u6570\u636e\u5e93\u8bbe\u8ba1\u4e0e\u4f18\u5316\u7684\u524d\u63d0\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u76ee\u5f55
\n\u524d\u8a00
\n\u4e00\u3001\u6570\u636e\u51c6\u5907\u4e0e\u56de\u987e
\n1.1 study \u8868\u7ed3\u6784\u4e0e\u5df2\u6709\u8bed\u53e5\u6574\u7406
\n1.2 \u51c6\u5907\u7b2c\u4e8c\u5f20\u6d4b\u8bd5\u8868（\u7528\u4e8e\u8fde\u8868\u67e5\u8be2）
\n\u4e8c\u3001\u591a\u6761\u4ef6\u67e5\u8be2\u3001\u6a21\u7cca\u67e5\u8be2\u4e0e NULL \u5224\u65ad
\n\u4e09\u3001\u5206\u7ec4\u4e0e HAVING（\u5206\u7ec4\u540e\u8fc7\u6ee4）
\n\u56db\u3001\u8fde\u8868\u67e5\u8be2
\n4.1 \u9690\u5f0f\u5185\u8fde\u63a5（WHERE \u6761\u4ef6，\u4e0d\u63a8\u8350）
\n4.2 \u663e\u5f0f\u5185\u8fde\u63a5 INNER JOIN（\u53ea\u53d6\u4e24\u8fb9\u5339\u914d\u5230\u7684\u6570\u636e）
\n4.3<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[84,114,768],"topic":[],"class_list":["post-94244","post","type-post","status-publish","format-standard","hentry","category-server","tag-android","tag-mysql","tag-sql"],"yoast_head":"\n