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
Ïà¹ØÎĵµ£º
CREATE PROCEDURE dbo.UspOutputData
@tablename sysname
AS
declare @column varchar(1000)
declare @columndata varchar(1000)
declare @sql varchar(4000)
declare @xtype tinyint
declare @name sysname
declare @objectId int
declare @objectname sysname
declare @ident int
set nocount on
......
Ò»°ãÓÃBCPÔÚ´¦ÀíÕâ¸öÊÂÇ飬µ«ÓÐʱҲÐèÒªÒ»Ð©ÌØÊâµÄ´¦Àí£¬ÒÔÏÂÊÇÉú³É±íÖеÄһЩÊý¾Ý£¬´øÓÐwhereÌõ¼þµÄÑ¡ÔñÉú³ÉÊý¾Ý£¬ÊÇÎÒÒ»¸öͬÊÂÐ޸ĵģ¬Ö±½ÓÄùýÀ´ÓÃÁË£º
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Proc proc_insert_where (@tablename varchar(256),@where varchar(256 ......
...
)
Óõ½µÄ¹¦ÄÜÓÐ:
1.Èç¹ûÎÒ¸ü¸ÄÁËѧÉúµÄѧºÅ,ÎÒÏ£ÍûËûµÄ½èÊé¼Ç¼ÈÔÈ»ÓëÕâ¸öѧÉúÏà¹Ø(Ò²¾ÍÊÇͬʱ¸ü¸Ä½èÊé¼Ç¼±íµÄѧºÅ);
2.Èç¹û¸ÃѧÉúÒѾ ......
¾¡Á¿ÉÙÓÃIN²Ù×÷·û£¬»ù±¾ÉÏËùÓеÄIN²Ù×÷·û¶¼¿ÉÒÔÓÃEXISTS´úÌæ¡£
²»ÓÃNOT IN²Ù×÷·û£¬¿ÉÒÔÓÃNOT EXISTS»òÕßÍâÁ¬½Ó+Ìæ´ú¡£
OracleÔÚÖ´ÐÐIN×Ó²éѯʱ£¬Ê×ÏÈÖ´ÐÐ×Ó²éѯ£¬½«²éѯ½á¹û·ÅÈëÁÙʱ±íÔÙÖ´ÐÐÖ÷²éѯ¡£¶øEXISTÔòÊÇÊ×Ïȼì²éÖ÷²éѯ£¬È»ºóÔËÐÐ×Ó²éѯֱµ½ÕÒµ½µÚÒ»¸öÆ¥ÅäÏî¡£NOT EXISTS±ÈNOT INЧÂÊÉԸߡ£µ«¾ßÌåÔÚÑ¡ÔñIN»òEXIST² ......
1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, ÄǾÍÐèҪѡÔñ½»²æ±í(intersection ......