sql²éѯÁ·Ï°
Àý 34 ÕÒ³öÄêÁ䳬¹ýƽ¾ùÄêÁäµÄѧÉúÐÕÃû¡£
SELECT SNAME
from STUDENTS
WHERE AGE £¾
(SELECT AVG(AGE)
from STUDENTS)
Àý 35 ÕÒ³ö¸÷¿Î³ÌµÄƽ¾ù³É¼¨£¬°´¿Î³ÌºÅ·Ö×飬ÇÒֻѡÔñѧÉú³¬¹ý 3 È˵Ŀγ̵ijɼ¨¡££¨ GROUP BY Óë HAVING
GROUP BY ×Ó¾ä°ÑÒ»¸ö±í°´Ä³Ò»Ö¸¶¨ÁУ¨»òһЩÁУ©ÉϵÄÖµÏàµÈµÄÔÔò·Ö×飬ȻºóÔÙ¶Ôÿ×éÊý¾Ý½øÐй涨µÄ²Ù×÷¡£
GROUP BY ×Ó¾ä×ÜÊǸúÔÚ WHERE ×Ó¾äºóÃæ£¬µ± WHERE ×Ó¾äȱʡʱ£¬Ëü¸úÔÚ from ×Ó¾äºóÃæ¡£
HAVING ×Ӿ䳣ÓÃÓÚÔÚ¼ÆËã³ö¾Û¼¯Ö®ºó¶ÔÐеIJéѯ½øÐпØÖÆ¡££©
SELECT CNO, AVG(GRADE), STUDENTS £½ COUNT(*)
from ENROLLS
GROUP BY CNO
HAVING COUNT(*) >= 3
Ïà¹Ø×Ó²éѯ
Àý 37 ²éѯûÓÐÑ¡Èκογ̵ÄѧÉúµÄѧºÅºÍÐÕÃû¡££¨µ±Ò»¸ö×Ó²éÑ¯Éæ¼°µ½Ò»¸öÀ´×ÔÍⲿ²éѯµÄÁÐʱ£¬³ÆÎªÏà¹Ø×Ó²éѯ£¨ Correlated Subquery) ¡£Ïà¹Ø×Ó²éѯҪÓõ½´æÔÚ²âÊÔν´Ê EXISTS ºÍ NOT EXISTS £¬ÒÔ¼° ALL ¡¢ ANY £¨ SOME £©µÈ¡££©
SELECT SNO, SNAME
from STUDENTS
WHERE NOT EXISTS
(SELECT *
from ENROLLS
WHERE ENROLLS.SNO=STUDENTS.SNO)
Àý 38 ²éѯÄÄЩ¿Î³ÌÖ»ÓÐÄÐÉúÑ¡¶Á¡£
SELECT DISTINCT CNAME
from COURSES C
WHERE ' ÄÐ ' £½ ALL
Ïà¹ØÎĵµ£º
Ò»¡¢Ê¹ÓÃSqlConnection¶ÔÏóÁ¬½ÓSQL ServerÊý¾Ý¿â
1¡¢µÇ¼Êý¾Ý¿âµÄ·½Ê½ÎªSQL ServerÑé֤ģʽ
server=·þÎñÆ÷Ãû;database=Êý¾Ý¿âÃû³Æ;uid=Óû§;pwd=ÃÜÂë
2¡¢µÇ¼Êý¾Ý¿âµÄ·½Ê½ÎªWindowsÑé֤ģʽ
server=·þÎñÆ÷Ãû;database=Êý¾Ý¿âÃû³Æ;Integrated Security=SSPI
ÆäÖУ¬Integrated Security¼´Trusted_Connection,È¡Ö ......
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_movefile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_movefile]
GO
/*--ÒÆ¶¯·þÎñÆ÷ÉϵÄÎļþ
²»½èÖú xp_cmdshell ,ÒòΪÕâ¸öÔÚ´ó¶àÊýʱºò¶¼±»½ûÓÃÁË
--×Þ½¨ 2004.08(ÒýÓÃÇë±£Áô´ËÐÅÏ¢)--*/
/*--µ÷ÓÃʾÀý ......
ÔÚ2005ÖÐÓÐͬÒå´ÊÓë¸´ÖÆµÄ¸ÅÄî
ͬÒå´ÊµÄÖ÷Òª×÷ÓÃÊÇ£º
Ò»£ºË޶̶ÔÏóµÄÃû³Æ£¬¼õÉÙ¹¤×÷ÈËÔ±ÊéдµÄʱ¼ä£¬Ìá¸ßЧÂÊ¡£ÎÒÃÇÖªµÀ·ÃÎÊÊý¾Ý¿âÒ»¸ö¶ÔÏóµÄͨ³£×îÈ«µÄ¶ÔÏóÃû³ÆÊÇ£º·þÎñÆ÷Ãû³Æ¡£Êý¾Ý¿âÃû³Æ¡£¼Ü¹¹Ãû³Æ¡£¶ÔÏóÃû³Æ
¶þ£ºÍ¬²½Êý¾Ý¡£ ......
sql isnullº¯ÊýµÄʹÓÃ
ISNULL
ʹÓÃÖ¸¶¨µÄÌæ»»ÖµÌæ»» NULL¡£
Óï·¨
ISNULL ( check_expression , replacement_value )
²ÎÊý
check_expression
½«±»¼ì²éÊÇ·ñΪ NULLµÄ±í´ïʽ¡£check_expression ¿ÉÒÔÊÇÈκÎÀàÐ͵ġ£
replacement_value
ÔÚ check_expression Ϊ NULLʱ½«·µ»ØµÄ±í´ïʽ¡£replacement_value ±ØÐëÓë chec ......
¾¡Á¿ÉÙÓÃIN²Ù×÷·û£¬»ù±¾ÉÏËùÓеÄIN²Ù×÷·û¶¼¿ÉÒÔÓÃEXISTS´úÌæ¡£
²»ÓÃNOT IN²Ù×÷·û£¬¿ÉÒÔÓÃNOT EXISTS»òÕßÍâÁ¬½Ó+Ìæ´ú¡£
OracleÔÚÖ´ÐÐIN×Ó²éѯʱ£¬Ê×ÏÈÖ´ÐÐ×Ó²éѯ£¬½«²éѯ½á¹û·ÅÈëÁÙʱ±íÔÙÖ´ÐÐÖ÷²éѯ¡£¶øEXISTÔòÊÇÊ×Ïȼì²éÖ÷²éѯ£¬È»ºóÔËÐÐ×Ó²éѯֱµ½ÕÒµ½µÚÒ»¸öÆ¥ÅäÏî¡£NOT EXISTS±ÈNOT INЧÂÊÉԸߡ£µ«¾ßÌåÔÚÑ¡ÔñIN»òEXIST² ......