¼¸¸öɾ³ýÖØ¸´¼Ç¼µÄSQLÓï¾ä
±ÈÈçÏÖÔÚÓÐÒ»ÈËÔ±±í £¨±íÃû£ºpeosons£©
ÈôÏ뽫ÐÕÃû¡¢Éí·ÝÖ¤ºÅ¡¢×¡Ö·ÕâÈý¸ö×Ö¶ÎÍêÈ«ÏàͬµÄ¼Ç¼²éѯ³öÀ´
select p1.* from persons p1,persons p2 where p1.id<>p2.id and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address
¿ÉÒÔʵÏÖÉÏÊöЧ¹û£®
¼¸¸öɾ³ýÖØ¸´¼Ç¼µÄSQLÓï¾ä
1.ÓÃrowid·½·¨
2.ÓÃgroup by·½·¨
3.ÓÃdistinct·½·¨
1¡£ÓÃrowid·½·¨
¾Ý¾Ýoracle´øµÄrowidÊôÐÔ£¬½øÐÐÅжϣ¬ÊÇ·ñ´æÔÚÖØ¸´,Óï¾äÈçÏ£º
²éÊý¾Ý:
select * from table1 a where rowid !=(select max(rowid)
from table1 b where a.name1=b.name1 and a.name2=b.name2......)
ɾÊý¾Ý£º
delete from table1 a where rowid !=(select max(rowid)
from table1 b where a.name1=b.name1 and a.name2=b.name2......)
2.group by·½·¨
²éÊý¾Ý:
¡¡¡¡select count(num), max(name) from student --ÁгöÖØ¸´µÄ¼Ç¼Êý£¬²¢ÁгöËûµÄnameÊôÐÔ
¡¡¡¡group by num
¡¡¡¡having count(num) >1 --°´num·Ö×éºóÕÒ³ö±íÖÐnumÁÐÖØ¸´£¬¼´³öÏÖ´ÎÊý´óÓÚÒ»´Î
ɾÊý¾Ý£º
¡¡¡¡delete from student
¡¡¡¡group by num
¡¡¡¡having count(num) >1
¡¡¡¡ÕâÑùµÄ»°¾Í°ÑËùÓÐÖØ¸´µÄ¶¼É¾³ýÁË¡£
3.ÓÃdistinct·½·¨ -¶ÔÓÚСµÄ±í±È½ÏÓÐÓÃ
create table table_new as select distinct * from table1 minux
truncate table table1;
insert into table1 select * from table_new;
²éѯ¼°É¾³ýÖØ¸´¼Ç¼µÄ·½·¨´óÈ«
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
Ïà¹ØÎĵµ£º
HexString to Binary:
DECLARE @y CHAR(34)
SET @y = '0x499104dc92dd27499da9ad8f56dcb437'
DECLARE @x BINARY(16)
DECLARE @sql NVARCHAR(200)
SET @sql = 'SELECT @x = ' + @y
EXEC sp_executesql
@sql,
N'@x BINARY(16) OUTPUT',
@x OUTPUT
SELECT @x
Binary to HexString:
DECLARE @a VARBINARY(20)
SET ......
ÒªµÃµ½Ä³Ò»ÌìÊÇÐÇÆÚ¼¸£¬ÐèÒªÓõ½ SQL Server ÖеÄÈÕÆÚº¯Êý£ºdatename()¡£ ½ñÌìÊÇÐÇÆÚ¼¸£¬Àý×Ó 1£º 1: set language N'English'
2: select datename(weekday, getdate())
3:
4: Wednesday
½ñÌìÊÇÐÇÆÚ¼¸£¬Àý×Ó 2£º
1: set language N'Simplified Chinese'
......
1.¶àwhere£¬ÉÙhaving
whereÓÃÀ´¹ýÂËÐУ¬havingÓÃÀ´¹ýÂË×é
2.¶àunion all£¬ÉÙunion
unionɾ³ýÁËÖØ¸´µÄÐУ¬Òò´Ë»¨·ÑÁËһЩʱ¼ä
3.¶àExists£¬ÉÙin
ExistsÖ»¼ì²é´æÔÚÐÔ£¬ÐÔÄܱÈinÇ¿ºÜ¶à£¬ÓÐЩÅóÓѲ»»áÓÃExists£¬¾Í¾Ù¸öÀý×Ó
Àý£¬ÏëÒªµÃµ½Óе绰ºÅÂëµÄÈ˵Ļù±¾ÐÅÏ¢£¬table2ÓÐÈßÓàÐÅÏ¢
select * from table1;--(id,n ......
You can user PL SQL Developer in Ubuntu like in Windows:
1.download PL/SQL Developer for linux;
2.Install JDK berfore the next;
3.install the PL/SQL Developer;
4.goto the $PL/SQL Developer,run sh sqldeverloper.sh;
5.type the $JDK;
6.then the PL/SQL Developer will startup. ......
´ÓA±íËæ»úÈ¡2Ìõ¼Ç¼,ÓÃSELECT TOP 10 * from ywle order by newid()
order by Ò»°ãÊǸù¾Ýijһ×Ö¶ÎÅÅÐò,newid()µÄ·µ»ØÖµÊÇuniqueidentifier ,order by newid()Ëæ»úѡȡ¼Ç¼ÊÇÈçºÎ½øÐеÄ
newid()ÔÚɨÃèÿÌõ¼Ç¼µÄʱºò¶¼Éú³ÉÒ»¸öÖµ, ¶øÉú³ÉµÄÖµÊÇËæ»úµÄ, ûÓдóСд˳Ðò. ËùÒÔ×îÖÕ½á¹ûÔÙ°´Õâ¸öÅÅÐò, ÅÅÐòµÄ½á¹ûµ±È»¾ÍÊÇÎÞÐòµ ......