MySQL²éѯ±íÄÚÖØ¸´¼Ç¼
MySQL²éѯ±íÄÚÖØ¸´¼Ç¼
²éѯ¼°É¾³ýÖØ¸´¼Ç¼µÄ·½·¨
(Ò»)
1¡¢²éÕÒ±íÖжàÓàµÄÖØ¸´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖØ¸´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´Åжϣ¬Ö»ÁôÓÐrowid×îСµÄ¼Ç¼
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
3¡¢²éÕÒ±íÖжàÓàµÄÖØ¸´¼Ç¼£¨¶à¸ö×ֶΣ©
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4¡¢É¾³ý±íÖжàÓàµÄÖØ¸´¼Ç¼£¨¶à¸ö×ֶΣ©£¬Ö»ÁôÓÐrowid×îСµÄ¼Ç¼
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5¡¢²éÕÒ±íÖжàÓàµÄÖØ¸´¼Ç¼£¨¶à¸ö×ֶΣ©£¬²»°üº¬rowid×îСµÄ¼Ç¼
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
(¶þ)
±È·½Ëµ
ÔÚA±íÖдæÔÚÒ»¸ö×ֶΓname”£¬
¶øÇÒ²»Í¬¼Ç¼֮¼äµÄ“name”ÖµÓпÉÄÜ»áÏàͬ£¬
ÏÖÔÚ¾ÍÊÇÐèÒª²éѯ³öÔڸñíÖеĸ÷¼Ç¼֮¼ä£¬“name”Öµ´æÔÚÖØ¸´µÄÏ
Select Name,Count(*) from A Group By Name Having Count(*) > 1
Èç¹û»¹²éÐÔ±ðÒ²Ïàͬ´óÔòÈçÏÂ:
Select Name,sex,Count(*) from A Group By Name,sex Having Count(*) > 1
(Èý)
·½·¨Ò»
declare @max integer,@id integer
declare cur_rows cursor local for select Ö÷×Ö¶Î,count(*) from ±íÃû group by Ö÷×Ö¶Î having count(*) >£» 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from ±íÃû where Ö÷×Ö¶Î = @id
fetch cur_rows into @id,@max
end
close c
Ïà¹ØÎĵµ£º
Ò»¡¢´´½¨Óû§µÄÃüÁî
mysql> create user 'username'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.06 sec)
¶þ¡¢¸øÐ¼ÓÓû§ÊÚÓèȨÏÞ
mysql> grant all privileges on test.* to 'username'@'localhost';
Query OK, 0 rows affected (0.00 sec)
˵Ã÷£ºÕâÌõÃüÁîÊÚÓè username ¶ÔÊý¾Ý¿â te ......
½â¾ö·½·¨£º
1¡£ ¸Ä±í·¨¡£
¿ÉÄÜÊÇÄãµÄÕʺŲ»ÔÊÐí´ÓÔ¶³ÌµÇ½£¬Ö»ÄÜÔÚlocalhost¡£Õâ¸öʱºòÖ»ÒªÔÚlocalhostµÄÄÇ̨µçÄÔ£¬µÇÈëmysqlºó£¬¸ü¸Ä "mysql" Êý¾Ý¿âÀïµÄ "user" ±íÀïµÄ "host" Ï´Ó"localhost"¸Ä³Æ"%"
mysql -u root -p vmware mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mys ......
ǰ¼¸Ì칫˾ÈÃÎÒ×öCÓïÑÔºÍMySQLÊý¾Ý¿âµÄ½»»¥²Ù×÷£¬·ÑÁ˾ÅÅ£¶þ»¢Ö®Á¦Ö®ºó×ÜËã×ö³öÀ´ÁË£¬½ñÌìÌØµØ°ÑÔ´´úÂëÌù³öÀ´¹©´ó¼Òѧϰ£»ÈçÓÐÈËÏëÒª´ò°üÏîÄ¿¿ÉÒÔ¼ÓÎÒQQ260990509,×¢Ã÷¼¼Êõ½»Á÷£¬¼´¿É»ñµÃ´ò°üÖ®ºóµÄÏîÄ¿£¡
#if defined(_WIN32) || defined(_WIN64) //ΪÁËÖ§³Öwindowsƽ̨ÉϵıàÒë
#include <windows.h>
#end ......
ÔÚ[mysqld] ÏÂÌí¼Ó²ÎÊýÈçÏÂ
#´íÎóÈÕÖ¾Êä³ö·¾¶(ʹÓÃ/×÷Ϊ·Ö¸î¶ø²»ÊÇ\)
log-error="your error-log path"
log="Your mysql log path"
log-show-queries="Your slowquery path"
#É趨³¬Ê±Ê±¼ä ´Ë´¦ÉèÖÃΪÁ½Ãë
long_query_time=2
Ok save my.ini
next
#Í£Ö¹mysql ·þÎñ
net s ......
¡¡MySQLµÄ×Ô´øÒýÇæ¿Ï¶¨Êǹ»ÓÃÁË£¬µ«ÊÇÔÚÓÐЩÇé¿öÏ£¬ÆäËûµÄÒýÇæ¿ÉÄÜÒª±ÈÊÖÍ·ËùÓøüÊʺÏÍê³ÉÈÎÎñ¡£Èç¹ûÔ¸ÒâµÄ»°£¬ÄãÉõÖÁ¿ÉÒÔʹÓÃMySQL++ APIÀ´´´½¨×Ô¼ºµÄÊý¾Ý¿âÒýÇæ£¬¾ÍÏñ´ò´©Æø¸××°ÉÏ×Ô¼ºµÄ»¯ÓÍÆ÷¡£ÏÖÔÚÈÃÎÒÃÇÀ´¿´¿´Äã¸ÃÈçºÎÑ¡ÔñÒýÇæ£¬ÒÔ¼°ÈçºÎÔÚ¿ÉÓÃÒýÇæÖ®¼äÇл»¡£
¡¡¡¡
¡¡¡¡Ñ¡ÔñÄãµÄÒýÇæ
¡¡¡¡
¡¡¡¡ÄãÄÜÓõÄÊý¾Ý¿ ......