MySQLË÷ÒýµÄ²é¿´´´½¨ºÍɾ³ý
1£®Ë÷Òý×÷ÓÃ
ÔÚË÷ÒýÁÐÉÏ£¬³ýÁËÉÏÃæÌáµ½µÄÓÐÐò²éÕÒÖ®Í⣬Êý¾Ý¿âÀûÓø÷ÖÖ¸÷ÑùµÄ¿ìËÙ¶¨Î»¼¼Êõ£¬Äܹ»´ó´óÌá¸ß²éѯЧÂÊ¡£ÌرðÊǵ±Êý¾ÝÁ¿·Ç³£´ó£¬²éѯÉæ¼°¶à¸ö±íʱ£¬Ê¹ÓÃË÷ÒýÍùÍùÄÜʹ²éѯËٶȼӿì³ÉǧÉÏÍò±¶¡£
ÀýÈ磬ÓÐ3¸öδË÷ÒýµÄ±ít1¡¢t2¡¢t3£¬·Ö±ðÖ»°üº¬ÁÐc1¡¢c2¡¢c3£¬Ã¿¸ö±í·Ö±ðº¬ÓÐ1000ÐÐÊý¾Ý×é³É£¬Ö¸Îª1¡«1000µÄÊýÖµ£¬²éÕÒ¶ÔÓ¦ÖµÏàµÈÐеIJéѯÈçÏÂËùʾ¡£
SELECT c1,c2,c3 from t1,t2,t3 WHERE c1=c2 AND c1=c3
´Ë²éѯ½á¹ûÓ¦¸ÃΪ1000ÐУ¬Ã¿Ðаüº¬3¸öÏàµÈµÄÖµ¡£ÔÚÎÞË÷ÒýµÄÇé¿öÏ´¦Àí´Ë²éѯ£¬±ØÐëÑ°ÕÒ3¸ö±íËùÓеÄ×éºÏ£¬ÒÔ±ãµÃ³öÓëWHERE×Ó¾äÏàÅäµÄÄÇЩÐС£¶ø¿ÉÄܵÄ×éºÏÊýĿΪ1000×1000×1000£¨Ê®ÒÚ£©£¬ÏÔÈ»²éѯ½«»á·Ç³£Âý¡£
Èç¹û¶Ôÿ¸ö±í½øÐÐË÷Òý£¬¾ÍÄܼ«´óµØ¼ÓËÙ²éѯ½ø³Ì¡£ÀûÓÃË÷ÒýµÄ²éѯ´¦ÀíÈçÏ¡£
£¨1£©´Ó±ít1ÖÐÑ¡ÔñµÚÒ»ÐУ¬²é¿´´ËÐÐËù°üº¬µÄÊý¾Ý¡£
£¨2£©Ê¹Óñít2ÉϵÄË÷Òý£¬Ö±½Ó¶¨Î»t2ÖÐÓët1µÄֵƥÅäµÄÐС£ÀàËÆ£¬ÀûÓñít3ÉϵÄË÷Òý£¬Ö±½Ó¶¨Î»t3ÖÐÓëÀ´×Ôt1µÄֵƥÅäµÄÐС£
£¨3£©É¨Ãè±ít1µÄÏÂÒ»Ðв¢Öظ´Ç°ÃæµÄ¹ý³Ì£¬Ö±µ½±éÀút1ÖÐËùÓеÄÐС£
ÔÚ´ËÇéÐÎÏ£¬ÈÔÈ»¶Ô±ít1Ö´ÐÐÁËÒ»¸öÍêȫɨÃ裬µ«Äܹ»ÔÚ±ít2ºÍt3ÉϽøÐÐË÷Òý²éÕÒÖ±½ÓÈ¡³öÕâЩ±íÖеÄÐУ¬±ÈδÓÃË÷ÒýʱҪ¿ìÒ»°ÙÍò±¶¡£
ÀûÓÃË÷Òý£¬MySQL¼ÓËÙÁËWHERE×Ó¾äÂú×ãÌõ¼þÐеÄËÑË÷£¬¶øÔÚ¶à±íÁ¬½Ó²éѯʱ£¬ÔÚÖ´ÐÐÁ¬½Óʱ¼Ó¿ìÁËÓëÆäËû±íÖеÄÐÐÆ¥ÅäµÄËٶȡ£
2. ´´½¨Ë÷Òý
ÔÚÖ´ÐÐCREATE TABLEÓï¾äʱ¿ÉÒÔ´´½¨Ë÷Òý£¬Ò²¿ÉÒÔµ¥¶ÀÓÃCREATE INDEX»òALTER TABLEÀ´Îª±íÔö¼ÓË÷Òý¡£
1£®ALTER TABLE
ALTER TABLEÓÃÀ´´´½¨ÆÕͨË÷Òý¡¢UNIQUEË÷Òý»òPRIMARY KEYË÷Òý¡£
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
ÆäÖÐtable_nameÊÇÒªÔö¼ÓË÷ÒýµÄ±íÃû£¬column_listÖ¸³ö¶ÔÄÄЩÁнøÐÐË÷Òý£¬¶àÁÐʱ¸÷ÁÐÖ®¼äÓöººÅ·Ö¸ô¡£Ë÷ÒýÃûindex_name¿ÉÑ¡£¬È±Ê¡Ê±£¬MySQL½«¸ù¾ÝµÚÒ»¸öË÷ÒýÁи³Ò»¸öÃû³Æ¡£ÁíÍ⣬ALTER TABLEÔÊÐíÔÚµ¥¸öÓï¾äÖиü¸Ä¶à¸ö±í£¬Òò´Ë¿ÉÒÔÔÚͬʱ´´½¨¶à¸öË÷Òý¡£
2£®CREATE INDEX
CREATE INDEX¿É¶Ô±íÔö¼ÓÆÕͨË÷Òý»òUNIQUEË÷Òý¡£
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
table_name¡¢index_nameºÍcolumn_list¾ßÓÐÓëALTER TABLEÓï¾äÖÐÏàͬµÄº¬Ò壬Ë÷ÒýÃ
Ïà¹ØÎĵµ£º
1¡¢ÔÚ¿ØÖÆִ̨ÐÐ mysql -u root -p mysql£¬ÏµÍ³ÌáʾÊäÈëÊý¾Ý¿ârootÓû§µÄÃÜÂ룬ÊäÈëÍê³Éºó¼´½øÈëmysql¿ØÖÆ̨£¬Õâ¸öÃüÁîµÄµÚÒ»¸ömysqlÊÇÖ´ÐÐÃüÁµÚ¶þ¸ömysqlÊÇϵͳÊý¾ÝÃû³Æ£¬²»Ò»ÑùµÄ¡£
2¡¢ÔÚmysql¿ØÖÆִ̨ÐÐ GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'MyPassword' WITH GRANT OPTION;
3¡¢ÔÚmysql¿ØÖÆ ......
¸Õ¸Õ°²×°Íêmysqlºó
´ÓcmdÖнøÈëmysqlÊý¾Ý¿â
ÃüÁîÐÐÖÐÊäÈ룺
mysql -uroot -p123
½øÈëmysqlÊý¾Ý¿âÖÐ
ÃüÁîÐÐÖÐÊäÈë:
status;
²é¿´µ±Ç°Êý¾Ý¿âÖеıàÂë
ÈçÏ£º
--------------
mysql Ver 14.12 Distrib 5.1.7-beta, for Win32 (ia32)
Connection id: &nb ......
1¡¢select * from tablename <Ìõ¼þÓï¾ä> limit 100,15
´Ó100Ìõ¼Ç¼ºó¿ªÊ¼(²»°üÀ¨µÚ100Ìõ¼Ç¼)È¡³ö15Ìõ¼Ç¼ (ʵ¼ÊÈ¡³öµÄÊÇµÚ 101-115Ìõ¼Ç¼Êý¾Ý)
2¡¢select * from tablename <Ìõ¼þÓï¾ä> limit 15
È¡³ö Ç°15Ìõ¼Ç¼Êý¾Ý,limit 15Ï൱ÓÚ limit 0,15 ......
1,ÔÚcmdÏÂÓÃmysql¿Í»§»úÁ¬½Ómysql·þÎñÆ÷ php³ÌÐòÔ±Õ¾
´ò¿ªcmdºó,ÊäÈë php³ÌÐòÔ±Õ¾
mysql -h localhost -u root -p
phperz.com
Óï·¨: mysqlµ±È»ÊǸæËßcmd,ÕâÊÇmysqlµÄÃüÁîÁË php³ÌÐòÔ±Ö®¼Ò
²ÎÊý -h ÊÇ·þÎñÆ÷µØÖ·,ÔÚ´ËÎÒÃÇÒÔ±¾»úΪÀýÊäÈëlocalhost
-u ÊÇmysqlµÄÓû§Ãû ÎÒÃÇÓÃmysql µÄĬÈÏÓû§Ãû root phperz ......
°²×°ºó
/etc/init.d/mysql start (stop) ΪÆô¶¯ºÍÍ£Ö¹·þÎñÆ÷
/etc/mysql/ Ö÷ÒªÅäÖÃÎļþËùÔÚλÖà my.cnf
/var/lib/mysql/ ·ÅÖõÄÊÇÊý¾Ý¿â±íÎļþ¼Ð£¬ÕâÀïµÄmysqlÏ൱ÓÚwindowsÏÂmysqlµÄdateÎļþ¼Ð
Æô¶¯mysqlºó£¬ÒÔrootµÇ¼mysql
isher@isher-ubuntu:~$ mysql -u root
>show variables like 'character%'; #Ö´ÐбàÂëÏ ......