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
......
Ò»¡¢Ê¹ÓÃSqlConnection¶ÔÏóÁ¬½ÓSQL ServerÊý¾Ý¿â
1¡¢µÇ¼Êý¾Ý¿âµÄ·½Ê½ÎªSQL ServerÑé֤ģʽ
server=·þÎñÆ÷Ãû;database=Êý¾Ý¿âÃû³Æ;uid=Óû§;pwd=ÃÜÂë
2¡¢µÇ¼Êý¾Ý¿âµÄ·½Ê½ÎªWindowsÑé֤ģʽ
server=·þÎñÆ÷Ãû;database=Êý¾Ý¿âÃû³Æ;Integrated Security=SSPI
ÆäÖУ¬Integrated Security¼´Trusted_Connection,È¡Ö ......
sql isnullº¯ÊýµÄʹÓÃ
ISNULL
ʹÓÃÖ¸¶¨µÄÌæ»»ÖµÌæ»» NULL¡£
Óï·¨
ISNULL ( check_expression , replacement_value )
²ÎÊý
check_expression
½«±»¼ì²éÊÇ·ñΪ NULLµÄ±í´ïʽ¡£check_expression ¿ÉÒÔÊÇÈκÎÀàÐ͵ġ£
replacement_value
ÔÚ check_expression Ϊ NULLʱ½«·µ»ØµÄ±í´ïʽ¡£replacement_value ±ØÐëÓë chec ......
¿ÉÒÔ¶¨ÒåÒ»¸öÎÞÂÛºÎʱÓÃINSERTÓï¾äÏò±íÖвåÈëÊý¾Ýʱ¶¼»áÖ´ÐеĴ¥·¢Æ÷¡£
¡¡¡¡µ±´¥·¢INSERT´¥·¢Æ÷ʱ£¬ÐµÄÊý¾ÝÐоͻᱻ²åÈëµ½´¥·¢Æ÷±íºÍinserted±íÖС£inserted±íÊÇÒ»¸öÂß¼±í£¬Ëü°üº¬ÁËÒѾ²åÈëµÄÊý¾ÝÐеÄÒ»¸ö¸±±¾¡£inserted±í°üº¬ÁËINSERTÓï¾äÖÐÒѼǼµÄ²åÈ붯×÷¡£inserted±í»¹ÔÊÐíÒýÓÃÓɳõʼ»¯INSERTÓï¾ä¶ø²úÉúµÄÈÕÖ¾Êý¾Ý ......