<\/p>\n
\u5728 UPDATE, DELETE \u5f8c\u63a5 ->fetch() \u4e4b\u985e\u7684\u5c31\u6703\u767c\u751f\u9019\u500b\u932f\u8aa4<\/p>\n
--<\/p>\n
--<\/p>\n
SELECT Users_id, ipv4 \r\nFROM \r\n(select id,users_id,ipv4 from UserLog order by id desc) as temp \r\ngroup by users_id \r\norder by id desc;\r\n<\/pre>\n\u5728 UserLog \u8cc7\u6599\u8868\u7d00\u9304\u4e86\u4f7f\u7528\u8005 id \u4ee5\u53ca\u7db2\u8def ip \uff0c\u9019\u662f\u4e00\u500b\u5f88\u5e38\u7528\u7684\u61c9\u7528\uff0c\u5982\u679c\u8981\u53d6\u51fa\u67d0 id \u6700\u5f8c\u767b\u5165\u7684 ip \u4f4d\u5740\uff0c\u5728\u540c\u6642\u4f7f\u7528 Group by Order by \u4e4b\u4e0b\u5c31\u6703\u5f97\u5230\u932f\u8aa4\u7684\u8cc7\u6599\u3002<\/p>\n
\u8981\u6ce8\u610f\u7684\u662f\uff0c\u4e26\u4e0d\u662f\u932f\u8aa4\u7684\u8a9e\u6cd5\u800c\u662f\u5f97\u5230\u7684\u8cc7\u6599\u6211\u5011\u60f3\u50cf\u4e2d\u7684\u8cc7\u6599\uff0c\u5982\u679c\u4f60\u6709\u8a8d\u771f\u67e5\u9a57\u904e\u7684\u8a71\u3002<\/p>\n
JOIN \u7684\u5834\u5408<\/strong><\/p>\n
\u76f4\u63a5 JOIN \u5012\u5e8f\u6392\u5e8f\u7684\u8cc7\u6599\u5373\u53ef<\/p>\n
select Users.id, ipv4 \r\nfrom Users \r\nJOIN \r\n(select id,Users_id,ipv4 from UserLog order by id desc) `UserLog` \r\nON \r\nUsers.id=UserLog.Users_id<\/pre>\n--<\/p>\n
MariaDB 5.5 \u7684\u8166\u6b98 bug - \u5b50\u67e5\u8a62 ORDER BY \u7121\u6548<\/strong><\/p>\n
SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC<\/pre>\n\u4e0a\u9762\u9019\u6a23\u53ef\u4ee5\u5f97\u5230\u4ee5 id \u7684\u5012\u5e8f\u6392\u5e8f\uff0c\u9019\u500b\u6c92\u554f\u984c\uff0c\u5982\u679c\u6539\u6210\u5b50\u67e5\u8a0a\u6703\u5982\u4f55\uff1f<\/p>\n
select * from \r\n(SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC) `a`<\/pre>\n\u4f60\u5c31\u6703\u5f97\u5230\u4e00\u500b\u6c92\u6709\u6392\u904e\u5e8f\u7684\u8cc7\u6599 ...<\/p>\n
\u70ba\u4ec0\u9ebc\u6703\u8aaa\u9019\u500b\u662f bug \uff0c\u56e0\u70ba\u4e00\u4f86\u00a0MySQL 5.1 \u6c92\u9019\u500b\u554f\u984c\uff0c\u4e8c\u4f86\u5728\u00a0MariaDB \u5b98\u7db2\u63d0\u4f9b\u4e86\u4e00\u500b\u9ed1\u79d1\u6280\u89e3\u6c7a\u9019\u500b\u554f\u984c<\/p>\n
\n
- mysql - order by inside subquery<\/a><\/li>\n
- Why is ORDER BY in a FROM Subquery Ignored?<\/a><\/li>\n<\/ul>\n
select * from \r\n(SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC \r\nLIMIT 18446744073709551615) `a`<\/pre>\n\u90a3\u4e00\u4e32 LIMIT \u4e0d\u662f\u9ed1\u79d1\u6280\uff1f\u4ec0\u9ebc\u624d\u53eb\u505a\u9ed1\u79d1\u6280 ...<\/p>\n
--<\/p>\n
#1093 You can't specify target table for update in FROM clause<\/h2>\n
MySQL \u66f4\u65b0\u6216\u522a\u9664\u5982\u679c\u4f7f\u7528\u5b50\u67e5\u8a62\uff0c\u9047\u5230\u5b50\u67e5\u8a62\u4e5f\u4f7f\u7528\u5230\u66f4\u65b0\u3001\u522a\u9664\u8cc7\u6599\u8868\u6642\u5c31\u6703\u767c\u751f\u9019\u500b\u932f\u8aa4\u3002<\/p>\n
\u6bd4\u8f03\u7c21\u55ae\u7684\u8655\u7406\u65b9\u5f0f\u662f\uff0c\u5728\u5b50\u67e5\u8a62\u4f7f\u7528 AS \u898f\u907f\u8cc7\u6599\u8868\u540d\u7a31<\/p>\n
SELECT\u00a0* FROM AAA<\/pre>\n\u6539\u6210<\/p>\n
SELECT * FROM ( SELECT * FROM AAA) AS AliasAAA<\/pre>\n\u66f4\u65b0\u6848\u4f8b<\/strong><\/p>\n
UPDATE Booking SET ClassPoint =0 WHERE id = ( SELECT id\r\nFROM (\r\nSELECT id\r\nFROM Booking\r\nWHERE Classroom_id = '123'\r\n) AS B )<\/pre>\n\u522a\u9664\u6848\u4f8b<\/strong><\/p>\n
\u9996\u5148\u7de8\u8f2f\u6311\u9078\u689d\u4ef6<\/p>\n
SELECT Users.id \r\nFROM Users \r\nJOIN UserLog ON Users.id=UserLog.Users_id \r\nWHERE \r\n UserIdentity_Code='Free'\r\nAND \r\n IPv4='211.20.170.215'\r\nAND \r\n DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 7 DAY), '%Y%m%d') >= DATE_FORMAT( UserLog.Create_Time, '%Y%m%d') \r\nGROUP BY Users.id<\/pre>\n\u5c07\u9069\u5408\u5c0d\u8c61\u5957\u7528\u522a\u9664\uff0c\u56e0\u70ba\u76f8\u540c\u8cc7\u6599\u8868\u4e5f\u767c\u751f\u4e86 #1093 \u932f\u8aa4<\/p>\n
DELETE FROM Users \r\nWHERE id \r\nIN ( \r\nSELECT AliasUsers.id \r\nFROM (\r\nSELECT * FROM Users) AS AliasUsers \r\nJOIN UserLog ON AliasUsers.id=UserLog.Users_id \r\nWHERE \r\n UserIdentity_Code='Free' \r\nAND \r\n IPv4='211.20.170.215' \r\nAND \r\n DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 7 DAY), '%Y%m%d') >= DATE_FORMAT( UserLog.Create_Time, '%Y%m%d') \r\nGROUP BY AliasUsers.id\r\n)<\/pre>\n--<\/p>\n
\u5efa\u7acb\u8de8\u8cc7\u6599\u5eab\u5916\u9375<\/h2>\n
\u56e0\u70ba phpMyAdmin \u7121\u6cd5\u57f7\u884c\u9019\u500b\u64cd\u4f5c\uff0c\u53ea\u597d\u81ea\u5df1\u624b\u5de5\u8f38\u5165\u4e86...<\/p>\n
ALTER TABLE `Albums`.`Supervisor` ADD FOREIGN KEY ( `Member_id` ) REFERENCES `Member`.`Member` (\r\n`id`\r\n) ON DELETE CASCADE ON UPDATE CASCADE ;<\/pre>\n\u00a0#1005 - Can't create table<\/strong>
\nPercona-XtraDB, Supports transactions, row-level locking, and foreign keys<\/strong><\/p>\n\u5982\u679c\u5916\u9375\u95dc\u806f\u52d5\u4f5c\u8a2d\u5b9a\u70ba SET NULL \u5c31\u6709\u53ef\u80fd\u6703\u51fa\u73fe\u9019\u500b\u932f\u8aa4\uff0c\u6539\u70ba\u5176\u4ed6\u52d5\u4f5c\u8a66\u8a66<\/p>\n
--<\/p>\n
\u65b0\u589e FOREIGN KEY\u00a0\u767c\u751f\u00a0#1452 - Cannot add or update a child row: a foreign key constraint fails \u932f\u8aa4<\/h2>\n
\u53c3\u8003\uff1a<\/p>\n
\n
- phpMyAdmin \u5efa\u7acb\u95dc\u806f\u5f0f\u8cc7\u6599\u8868 \u6b21\u8981\u7fa4\u7d44<\/a><\/li>\n
- Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails<\/a><\/li>\n<\/ul>\n
\u4f7f\u7528 Foreign Key \u95dc\u9375<\/p>\n
\n
- \u4e3b\u9375\u5fc5\u9808\u70ba\u7d22\u5f15<\/li>\n
- \u5916\u90e8\u9375\u5fc5\u9808\u70ba Primary Key<\/li>\n
- \u52d5\u4f5c\u8acb\u8a2d\u5b9a\u70ba\u00a0CASCADE \u00a0\u8b93\u95dc\u806f\u8cc7\u6599\u96a8\u8457\u522a\u9664\u3001\u4fee\u6539\u4e00\u4f75\u66f4\u52d5<\/li>\n
- \u5982\u679c\u767c\u751f #1452 \u932f\u8aa4\uff0c\u61c9\u8a72\u662f\u8cc7\u6599\u8868\u6709\u5c0d\u61c9\u932f\u8aa4\u7684\u8cc7\u6599\uff0c\u5fc5\u9808\u8981\u6b63\u78ba\u624d\u80fd\u9806\u5229\u5efa\u7acb\u6216\u4fee\u6539<\/li>\n<\/ul>\n
--<\/p>\n
MySQL \u7684\u65e5\u671f\u3001\u6642\u9593\u5207\u5272\u8a08\u7b97<\/h2>\n
\u8cc7\u6599\u8868\u6709\u4e00\u500b time\u00a0\u6b04\u4f4d\uff0c\u578b\u614b\u70ba\u00a0timestamp<\/p>\n
\u9700\u6c42\uff1a\u986f\u793a\u5c0f\u6642\u5340\u9593\u4ee5\u53ca\u8a72\u5c0f\u6642\u5340\u9593\u7684\u6578\u503c Data \u5e73\u5747\u503c\uff0c\u5e73\u5747\u503c\u53d6\u5c0f\u6578\u9ede 0 \u4f4d<\/p>\n
SELECT DATE_FORMAT(time, '%Y\/%m\/%d %H') D, FORMAT(AVG(`Data`),0) as T\r\nFROM `Table`\r\nGROUP BY MONTH(time),DAY(time),HOUR(time)<\/pre>\n--<\/p>\n
UNION \u7684 ORDER BY, \u00a0LIMIT \u53ca offset<\/h2>\n
UNION \u53ef\u5c07\u591a\u689d\u986f\u793a\u76f8\u540c\u6b04\u4f4d\u7684 SQL \u8a9e\u53e5\u4e32\u9023\uff0c\u5efa\u8b70\u5c07\u500b\u5225 SQL Command \u4f7f\u7528 () \u5305\u4f4f<\/p>\n
( SELECT * FROM T1 LIMIT 0, 10 )\r\nUNION\r\n( SELECT * FROM T2 LIMIT 0, 10 )<\/pre>\nUNION \u7684\u7e3d LIMIT \u96d6\u7136\u53ef\u4ee5\u5957\u904e\u4e0b\u9762\u8a9e\u6cd5\u5be6\u73fe\uff0c\u4e0d\u904e\u548c\u60f3\u50cf\u4e2d\u7684\u5206\u9801\u662f\u4e0d\u4e00\u6a23\u7684\u3002<\/p>\n
\u5982\u679c\u5169\u689d Query \u90fd\u7522\u751f\u4e86 10 \u689d\u7d50\u679c\uff0c\u90a3\u6700\u5f8c\u7684 LIMIT \u5c31\u53ea\u6703\u986f\u793a\u524d 10 \u7b46<\/span><\/p>\n
( SELECT * FROM T1 LIMIT 0, 10 )\r\nUNION\r\n( SELECT * FROM T2 LIMIT 0, 10 )\r\nLIMIT 0, 10<\/pre>\n\u4e5f\u53ef\u4ee5\u5c0d\u6700\u5f8c\u7d50\u679c\u9032\u884c\u6392\u5e8f\u6216\u5176\u4ed6\u5224\u65b7<\/p>\n
( SELECT * FROM T1 LIMIT 0, 10 )\r\nUNION\r\n( SELECT * FROM T2 LIMIT 0, 10 )\r\nORDER BY Column DESC<\/pre>\n--<\/p>\n
\u5b50\u67e5\u8a62<\/h2>\n
\n
- >=ANY \u5927\u4e8e\u7b49\u4e8e\u5b50\u67e5\u8be2\u4e2d\u7684\u67d0\u4e2a\u503c<\/li>\n
- <=ANY \u5c0f\u4e8e\u7b49\u4e8e\u5b50\u67e5\u8be2\u4e2d\u7684\u67d0\u4e2a\u503c<\/li>\n
- =ANY \u7b49\u4e8e\u5b50\u67e5\u8be2\u4e2d\u7684\u67d0\u4e2a\u503c<\/li>\n
- !=ANY\u6216<>ANY \u4e0d\u7b49\u4e8e\u5b50\u67e5\u8be2\u4e2d\u7684\u67d0\u4e2a\u503c<\/li>\n
- >ALL \u5927\u4e8e\u5b50\u67e5\u8be2\u4e2d\u7684\u6240\u6709\u503c<\/li>\n
- >=ALL \u5927\u4e8e\u7b49\u4e8e\u5b50\u67e5\u8be2\u4e2d\u7684\u6240\u6709\u503c<\/li>\n
- <=ALL \u5c0f\u4e8e\u7b49\u4e8e\u5b50\u67e5\u8be2\u4e2d\u7684\u6240\u6709\u503c<\/li>\n
- =ALL \u7b49\u4e8e\u5b50\u67e5\u8be2\u4e2d\u7684\u6240\u6709\u503c<\/li>\n
- !=ALL\u6216<>ALL \u4e0d\u7b49\u4e8e\u5b50\u67e5\u8be2\u4e2d\u7684\u6240\u6709\u503c<\/li>\n<\/ul>\n
\u61c9\u7528\uff1a\u7559\u8a00\u56de\u8986<\/p>\n
SELECT * FROM \u8a0a\u606f WHERE sid=ANY ( SELECT \u56de\u8986sid FROM \u8a0a\u606f WHERE \u56de\u8986sid<>'' ) ORDER BY sid DESC LIMIT 0, 1<\/pre>\n--<\/p>\n
\u5f9e\u73fe\u6709\u8cc7\u6599\u8907\u88fd INSERT INTO SELECT<\/h2>\n
\n
- sql - MySQL: How to copy rows, but change a few fields? - Stack Overflow<\/a><\/li>\n
- [MYSQL] \u628aSELECT\u7684\u7d50\u679cINSERT\u5230\u8868\u4e2d<\/a><\/li>\n<\/ul>\n
INSERT INTO `Media_File` ( create_date,source_filename,create_user,media_filename,\u4f86\u6e90\u5a92\u9ad4\u8cc7\u8a0a,\u7522\u54c1\u9023\u7d50)\r\nSELECT '20131218100600', source_filename,create_user,media_filename,\u4f86\u6e90\u5a92\u9ad4\u8cc7\u8a0a,'166'\r\nFROM `Media_File` WHERE sid=110724<\/pre>\n--<\/p>\n
MySQL \u63d2\u5165\u91cd\u8907\u552f\u4e00\u503c\u932f\u8aa4\u89e3\u6c7a<\/h2>\n
\n
- mysql\u9632\u6b62\u91cd\u590d\u63d2\u5165\u8bb0\u5f55\u65b9\u6cd5\u603b\u7ed3<\/a><\/li>\n
- MySQL\u5f53\u6279\u91cf\u63d2\u5165\u9047\u4e0a\u552f\u4e00\u7d22\u5f15<\/a><\/li>\n<\/ul>\n
INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com', '99999', '9999');<\/pre>\n--<\/p>\n
#1194 - Table 'tablename' is marked as crashed and should be repaired<\/h2>\n
MyISAM \u8cc7\u6599\u8868\u6bc0\u640d\u4fee\u5fa9<\/p>\n