SQL¼¶Áª¸üкͼ¶ÁªÉ¾³ý
alter table ±íÃû
add constraint Ô¼ÊøÃû
foreign key(×Ö¶ÎÃû) references Ö÷±íÃû(×Ö¶ÎÃû)
on delete cascade
Óï·¨£º
Foreign Key
(column[,...n])
references referenced_table_name[(ref_column[,...n])]
[on delete cascade]
[on update cascade]
×¢ÊÍ£º
column:ÁÐÃû
referenced_table_name:Íâ¼ü²Î¿¼µÄÖ÷¼ü±íÃû³Æ
ref_name:Íâ¼üÒª²Î¿¼µÄ±íµÄÖ÷¼üÁÐ
on delete:ɾ³ý¼¶Áª
on update:¸üм¶Áª
±íµÄÍâ¼üÔ¼Êø
SELECT Íâ¼ü±íID=b.fkeyid
,Íâ¼ü±íÃû³Æ=object_name(b.fkeyid)
,Íâ¼üÁÐID=b.fkey
,Íâ¼üÁÐÃû=(SELECT name from syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
,Ö÷¼ü±íID=b.rkeyid
,Ö÷¼ü±íÃû=object_name(b.rkeyid)
,Ö÷¼üÁÐID=b.rkey
,Ö÷¼üÁÐÃû=(SELECT name from syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
from sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
²éѯһ¸ö±íµÄËùÓÐÍâ¼ü:
SELECT Ö÷¼üÁÐID=b.rkey
,Ö÷¼üÁÐÃû=(SELECT name from syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
,Íâ¼ü±íID=b.fkeyid
,Íâ¼ü±íÃû³Æ=object_name(b.fkeyid)
,Íâ¼üÁÐID=b.fkey
,Íâ¼üÁÐÃû=(SELECT name from syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
from sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
and object_name(b.rkeyid)='Òª²éѯµÄ±íÃû'
Ïà¹ØÎĵµ£º
SQL SERVER Êý¾Ý¿âÁ´½Ó·þÎñÆ÷×ܽá
ǰ¶Îʱ¼ä£¬¹«Ë¾µÄÏîÄ¿¿ª·¢Óõ½C/S¡¢B/SÁ½Öּܹ¹¡£CS²¿·ÖÒòΪÊý¾ÝµÄ±£ÃÜÐԺͰ²È«ÐÔ£¬²ÉÓÃSQL SERVER 2000£¨ºóÀ´Ëæ×ÅÈí¼þ°æ±¾Éý¼¶£¬²ÉÓÃSQL Server 2005£©¾ÖÓòÍøÂç¡£B/S²¿·Ö²ÉÓÃOracle9.2Êý¾Ý¿â¡£Á½¸ö²¿·ÖÎïÀí¸ôÀ룬¶¨Ê±Í¨¹ýÍøÂçÇл»Æ÷½øÐÐÍøÂçÇл»ÒÔÍê³ÉÊý¾Ý½»»»¡£
......
DDL´¥·¢Æ÷ DDL Triggers
SQL Server 2005¿ÉÒÔ¾ÍÕû¸ö·þÎñÆ÷»òÊý¾Ý¿âµÄij¸ö·¶Î§ÎªDDLʼþ¶¨Òå´¥·¢Æ÷¡£Ò²¿ÉÒÔΪµ¥¸öDDLÓï¾ä£¨ÀýÈ磺CREAT_TABLE¡¢DROP_TABLEµÈ£©»òÕßΪһ×éÓï¾ä£¨ÀýÈ磺ָ¶¨DDL_DATABASE_LEVEL_EVENTSÏëÒª´¥·¢Æ÷´¥·¢Êý¾Ý¿âËùÓÐDDLʼþ£©¶¨ÒåDDL´¥·¢Æ÷¡£
ÔÚDDL´¥·¢Æ÷ÄÚ²¿£¬¿ÉÒÔͨ¹ý·ÃÎÊeventdata()º¯Êý»ñµÃÓ ......
2.1 ±¾ÕÂÄ¿µÄ
ÔÚ±¾ÕÂÖоÍÔÚ HSQLDB Ö÷Ò³ÂÛ̳»òÓʼþÁбíÖжà´ÎÌá³öµÄÎÊÌâ½øÐнâ´ð£¬Èç¹ûÄã´òËãÔÚÓ¦
ÓóÌÐòÖÐʹÓÃHSQLDB µÄ»°£¬ÄãÓ¦¸ÃÔĶÁһϱ¾Õ¡£
2.2 ¶ÔSQL ±ê×¼µÄÖ§³Ö
1.8.0 °æ±¾µÄHSQLDB Ö§³ÖSQL92¡¢99 ºÍ2003 ±ê×¼¹æ¶¨µÄSQL ·½ÑÔ¡£ÕâÒâζ×ÅHSQLDB
ÖÐÖ§³ÖµÄ±ê×¼ÌØÐÔ£¨ÀýÈç×óÍâÁ¬½Ó£©µÄÓï·¨ÊÇÓɱê×¼Îı¾¹æ¶¨µÄ¡£Ðí¶ ......
OracleÊý¾Ý¿â
SELECT
*
from
(
SELECT
ROWNUM AS NO_ROW
,row_.*
from
(
SELECT
&nb ......
Ò»¡¢ ¼òµ¥²éѯ
¼òµ¥µÄTransact-SQL²éѯֻ°üÀ¨Ñ¡ÔñÁÐ±í¡¢from×Ó¾äºÍWHERE×Ӿ䡣ËüÃÇ·Ö±ð˵Ã÷Ëù²éѯÁС¢²éѯµÄ
±í»òÊÓͼ¡¢ÒÔ¼°ËÑË÷Ìõ¼þµÈ¡£
ÀýÈ磬ÏÂÃæµÄÓï¾ä²éѯtesttable±íÖÐÐÕÃûΪ“ÕÅÈý”µÄnickname×ֶκÍemail×ֶΡ£
SELECT nickname,email
from testtable
WHERE name='ÕÅÈý'
(Ò») Ñ¡ÔñÁбí
Ñ¡ÔñÁбí ......