???????????????????? EXPLAIN???SELECT??? ????????????????LIMIT 1 ???????ν????? ??Join?????????????????У??????????? ????ORDER BY RAND() ????SELECT * ???????????????ID ???ENUM??????VARCHAR ??PROCEDURE ANALYSE() ??y??? ??????????NOT NULL PreparedStatement ???????? ??IP?????? UNSIGNED INT ????????????? ?????? ????? DELETE ?? INSERT ??? ?С???л???? ????????洢???? ???????????????????Object Relational Mapper?? С??“????”
????1. ????????????????
???????????MySQL???????????????????檔???????????Ч??????????????????MySQL??????????洦???????к?????????????????ε??????Щ???????????????????У???????????????????????ò????????????????????
????????????????????????????????????????????????????????????????Щ?????????MySQL???????檔????????????
????// ??????治????
????$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
????// ??????????
????$today = date("Y-m-d");
????$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
????????????SQL???????? CURDATE() ??MySQL???????????????????????á???????? NOW() ?? RAND() ??????????????????SQL?????????????????棬?????Щ????????????????????????????????????????????????????MySQL????????????????檔
????2. EXPLAIN ??? SELECT ???
??????? EXPLAIN ???????????????MySQL????δ??????SQL????????????????????????????????????????
????EXPLAIN ??????????????????????????????????????????????????α????????????……????????
????3. ??????????????? LIMIT 1
???????????????Щ?????????????????????????????????????????fetch?α??????????????鷵?????????
??????????????￡????? LIMIT 1 ??????????????????????MySQL?????????????????????????????????????????????????????????????????
??????????????????????????????“?й?”????????????????????????????Ч???????????????????Select *?????????Select 1??
????// ???Ч????
????$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
????if (mysql_num_rows($r) > 0) {
????// ...
????}
????// ??Ч????
????$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
????if (mysql_num_rows($r) > 0) {
????// ...
????}
????4. ???????ν?????
????5. ??Join??????????????????????????????
????????????ó????к?? JOIN ?????????????????????Join?????????????????????????MySQL??????????????Join??SQL????????
???????????Щ??????Join????Σ????????????????????磺???????? DECIMAL ??κ???? INT ???Join?????MySQL???????????????????????ЩSTRING?????????????????????????С????????????????п?????????
????6. ???? ORDER BY RAND()
????????????????????????д??????????N???????????????????????????????????????????????????????????????????MySQL???ò?????RAND?????????????CPU???????????????????м??????У????????????????????????limit 1???????￡?????????
?????????????????????????
????-- ??????????
????SELECT last_name FROM actor ORDER BY RAND() LIMIT 1;-- ???: 0.252s
????-- ?????????
????SELECT COUNT(*) FROM actor;
????-- ?????????????????0~count(*)-1??????????rand???????????sql????
????SELECT last_name FROM actor limit rand?? 1;
????7. ???? SELECT *
???????????????????????????????????? ?????????????WEB??????????????????????????????????紫??????
???????????????????????????????????????
????8. ???????????????????
????????????????????????????????ID?????????????????????INT??????????UNSIGN????????????????????AUTO_INCREMENT?????
??????????users?????????????“email”????Σ??????????????????????VARCHAR???????????????????????????????????????У?????????????????????????????????
???????????MySQL?????????￡??????Щ????????????????????Щ????￡?????????????????÷??????????磬?????????….???????????????
?????????????????????????????“??????”??“???”???????????????????????????????????????ɡ????????????????“???”?????磬?и?“?????”???????ID???и?“?γ??”?пγ?ID?????“?????”??“??????”???????????????γ??????????У????ID??γ?ID??“???”?乲??????????
????9. ??? ENUM ?????? VARCHAR
????ENUM??????????????????????????????TINYINT????????????????????????????? ???????????????Щ????б?????????????
????????????????Σ?????“???”??“”??“????”??“??”??“????”?????????Щ??ε?????????????????????????????? ENUM ?????? VARCHAR??
????MySQL??????“????”???????????????????????????????????????????? VARCHAR ???????????????????????? ENUM ???????? PROCEDURE ANALYSE() ?????????????顣
????10. ?? PROCEDURE ANALYSE() ??y???
????PROCEDURE ANALYSE() ???? MySQL ??????????????κ????????????????????Щ???????顣??б????????????????Щ????????????????????Щ???????????????????????????
???????磬???????????? INT ?????????????????????????????????????PROCEDURE ANALYSE()????????????ε??????? MEDIUMINT ???????????????? VARCHAR ??Σ???????????????????????????????? ENUM ????顣??Щ???飬?????????????????????????????ò??????
?????????????Щ?????飬??е???????????????????????Щ???????????????????????????????????????
????11. ?????????? NOT NULL
???????????????????????????? NULL ???????????????????α??? NOT NULL?????????????е????飬?????????
??????????????????“Empty”??“NULL”?ж????????????INT??????0??NULL?????????????????????????????????????NULL????????????? Oracle ??NULL ?? Empty ???????????????)
?????????? NULL ???????????????????????????????б??????????????????? ?????????????????????NULL????????????????????????Щ????￡?????????NULL???
????12. PreparedStatement
????PreparedStatement????洢????????????????????SQL??伯?????????????PreparedStatement??ú????????????????????????????
????PreparedStatement???????Щ?????????????????????????????????“SQL????”??????????????????????????????Щ?????????????????????????????????????????????????????????Щframework????ORM??????????????????Щ??
????????????棬????????????????ú??ε??????????????????????????????????ЩPreparedStatement?????Щ????????MySQL????????Ρ?