1. Å×ÀÌºí¿¡ index °É±â
¸¸µé±â : alter table Å×À̺í¸í add index(Çʵå¸í)
¿¹ : alter table ASADAL_MEMBER_MEMBER add index(admin_reg)
È®ÀÎ : show index from ASADAL_MEMBER_MEMBER
2. unix ŸÀÓ °ªÀ¸·Î µé¾î°£ date ´«À¸·Î È®ÀÎÇÒ ¼ö ÀÖ´Â ³¯Â¥·Î ¹Ù²ã¼ º¸±â
SELECT from_unixtime(service_start) FROM Å×À̺í¸í
3. Áߺ¹µÇ´Â °Í Ä«¿îÆ®°¡ 2 ÀÌ»óÀÎ °Í ã±â
SELECT count(*), domain_name FROM `RNWDN` GROUP BY domain_name having count(*) > 1;
4. Áߺ¹µÇ´Â °Í Ä«¿îÆ®°¡ ³ôÀº ¼øÀ¸·Î º¸±â
SELECT count(*), domain AS domain FROM `Hosting` GROUP BY domain ORDER BY count(*) DESC
5. DB Ãß°¡ °ü·Ã
(1) user Ãß°¡ : insert into user (host, user, password) values ('localhost','domainemart',password('domain'));
(2) DB Ãß°¡ : insert into db values ('%','domainemart','domainemart','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
(3) DB »ý¼º : create database domainemart;
(4) ¾÷µ¥ÀÌÆ® : flush privileges;
6. ³»¿ë Áß ÀϺΠµ¥ÀÌÅÍ º¯°æÇϱâ
update Å×À̺í¸í set vir = REPLACE (vir, '¿ø·¡ ³»¿ë', 'º¯°æÇÒ ³»¿ë');
=> ¿¹) update httpd set vir = REPLACE(vir, '211.233.39.186', '211.115.110.23');
7. ³»¿ë º¯°æ ½Ã ¿ø·¡ ÀÖ´ø ³»¿ë¿¡ ³»¿ë Ãß°¡Çϱâ
UPDATE Å×À̺í¸í SET Çʵå¸í = 'º¯°æÇÒ µ¥ÀÌÅÍ', ³»¿ëÃß°¡ÇÒÇʵå¸í = CONCAT(³»¿ëÃß°¡ÇÒÇʵå¸í, "\nÃß°¡ÇÒ³»¿ë") where Á¶°Ç
=> ¿¹) UPDATE Hosting SET server = 'ww38', memo = CONCAT(memo, "\n2004.08.26 ww30->ww38·Î ÅëÇÕ") WHERE server = 'ww30' AND serverdomain = 'asadal.net';
8. DB Å×À̺í ÆÄÀÏ·Î µ¥ÀÌÅÍ ÀúÀå ¶Ç´Â µ¥ÀÌÅÍ ÀÔ·Â
(1) ÀúÀå : select * into outfile 'file_name' freom table_name;
(2) ÀÔ·Â : load data infile 'file_name' into table table_name;
9. DB ³»¿ë Áß¿¡ ÀÖ´Â ¹®ÀÚ ¹Ù²Ù±â
UPDATE table_name SET col_name = REPLACE(col_name, '¿ø·¡¹®ÀÚ', 'º¯°æÇÒ¹®ÀÚ')
11. UNIXTIME ·Î µÈ ÀϽ÷Πgroup by Çϱâ
SELECT from_unixtime( start_date, '%Y-%m' ) AS date, count( * ) FROM `Transaction` AND io_price = '0' GROUP BY date