(5)ʹÓÃTRANCATE TABLEÓï¾äɾ³ý±íÖÐËùÓÐÐС£
Æô¶¯²éѯ·ÖÎöÆ÷Ò»>ÔÚ¡°²éѯ¡±´°¿ÚÖÐÊäÈëÒÔÏ T¡ªSQLÓï¾ä£º USE YGGL
TRANCATE TABLE Salary GO
µ¥»÷¿ì½Ý¹¤¾ßÀ¸µÄÖ´ÐÐͼ±ê£¬Ö´ÐÐÉÏÊöÓï¾ä£¬½«É¾³ýSalary±íÖеÄËùÓÐÐС£
×¢Ò⣺ʵÑéʱһ°ã²»ÇáÒ××öÕâ¸ö²Ù×÷£¬ÒòΪºóÃæʵÑ黹ҪÓõ½ÕâЩÊý¾Ý¡£ÈçÒªÊÔÑé¸ÃÃüÁî
µÄЧ¹û£¬¿É½¨Ò»¸öÁÙʱ±í£¬ÊäÈëÉÙÁ¿Êý¾Ýºó½øÐС£
ʵÑé¶þ Êý¾Ý¿âµÄ²éѯ
Ò»¡¢ÊµÑéÄ¿µÄ
1£®ÕÆÎÕSELECTÓï¾äµÄ»ù±¾Óï·¨£» 2£®ÕÆÎÕ×Ó²éѯµÄ±íʾ£» 3£®ÕÆÎÕÁ¬½Ó²éѯµÄ±íʾ£» 4£®ÕÆÎÕÊý¾Ý»ã×ܵķ½·¨£»
5£®ÕÆÎÕSELECTÓï¾äµÄGROUP BY×Ó¾äµÄ×÷ÓúÍʹÓ÷½·¨£» 6£®ÕÆÎÕSELECTÓï¾äµÄORDER BY×Ó¾äµÄ×÷ÓúÍʹÓ÷½·¨¡£
¶þ¡¢ÊµÑéÄÚÈÝ
1£®SELECTÓï¾äµÄ»ù±¾Ê¹ÓÃ
(1)¶ÔÓÚʵÑé1¸ø³öµÄÊý¾Ý¿â±í½á¹¹£¬²éѯÿ¸ö¹ÍÔ±µÄËùÓÐÊý¾Ý¡£ ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL SEIECT *
FROM Employees GO
¡¾Ë¼¿¼ÓëÁ·Ï°¡¿
ÓÃSELECTÓï¾ä²éѯDepartmentsºÍSalary±íµÄËùÓмǼ¡£ (2)²éѯÿ¸ö¹ÍÔ±µÄµØÖ·ºÍµç»°¡£
ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL
SELECT Address,PhoneNumber FROM Employees GO
¡¾Ë¼¿¼ÓëÁ·Ï°¡¿
ÓÃSELECTÓï¾ä²éѯDepartmentsºÍSalary±íµÄÒ»ÁлòÈô¸ÉÁС£ (3)²éѯEmployeeIDΪ000001µÄ¹ÍÔ±µÄµØÖ·ºÍµç»°¡£ ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL
SELECT Address£¬PhoneNumber 7 FROM Employees
WHERE EmployeelD=¡¯000001¡¯ GO
¡¾Ë¼¿¼ÓëÁ·Ï°¡¿
9
ÓÃSELECTÓï¾ä²éѯDepartmentsºÍSalary±íÖÐÂú×ãÖ¸¶¨Ìõ¼þµÄ1ÁлòÈô¸ÉÁС£
(4)²éѯEmployees±íÖÐÅ®¹ÍÔ±µÄµØÖ·ºÍµç»°£¬Ê¹ÓÃAS×Ӿ佫½á¹ûÖи÷ÁеıêÌâ·Ö±ðÖ¸¶¨ÎªµØÖ·¡¢µç»°¡£
ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL
SELECT Address ASµØÖ·£®PhoneNumber ASµç»° FROM Employees WHERE sex=0
Go
×¢Ò⣺ʹÓÃAS×Ó¾ä¿ÉÖ¸¶¨Ä¿±êÁеıêÌâ¡£ (1) ¼ÆËãÿ¸ö¹ÍÔ±µÄʵ¼ÊÊÕÈë¡£
ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL
SELECT EmployeelD£¬Êµ¼ÊÊÕÈë=InCome¡ªOutCome
FROM Salary G0
(2) ÕÒ³öËùÓÐÐÕÍõµÄ¹ÍÔ±µÄ²¿Ãźš£
ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL
SELECT DepartmentID FROM Employees
WHERE name LIKE¡¯Íõ£¥¡¯ Go
¡¾Ë¼¿¼ÓëÁ·Ï°¡¿ÕÒ³öËùÓÐÆäµØÖ·Öк¬ÓС®¡®ÖÐɽ¡±µÄ¹ÍÔ±µÄºÅÂë¼°²¿Ãźš£ (3) ÕÒ³öËùÓÐÊÕÈëÔÚ2000¡«3000ÔªÖ®¼äµÄ¹ÍÔ±ºÅÂë¡£ ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL
SELECT EmployeelD
FROM Salary
WHERE InCome BETWEEN 2000 AND 3000 GO
¡¾Ë¼¿¼ÓëÁ·Ï°¡¿
ÕÒ³öËùÓÐÔÚ²¿ÃÅ¡®1¡¯»ò¡®2¡¯¹¤×÷µÄ¹ÍÔ±µÄºÅÂë¡£
×¢Ò⣺ÔÚSELECTÓï¾äÖÐLIKE¡¢BETWEEN?AND,£¬IN£¬NOT¼°CONTAINν´ÊµÄ×÷Óᣠ3£® ×Ó²éѯµÄʹÓÃ
(1)²éÕÒÔÚ²ÆÎñ²¿¹¤×÷µÄ¹ÍÔ±µÄÇé¿ö¡£
ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº
USE YGGL SELECT *
FROM Employees £®
WHERE DepartmentlD= ¡£ (SELECT DepartmentlD FROM Departments
WHERE DepartmentName=¡¯²ÆÎñ²¿¡¯)
10
GO
¡¾Ë¼¿¼ÓëÁ·Ï°¡¿
ÓÃ×Ó²éѯµÄ·½·¨²éÕÒËùÓÐÊÕÈëÔÚ2500ÔªÒÔϵĹÍÔ±µÄÇé¿ö¡£ (2)²éÕÒ²ÆÎñ²¿ÄêÁä²»µÍÓÚÑз¢²¿¹ÍÔ±ÄêÁäµÄ¹ÍÔ±µÄÐÕÃû¡£ ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments
WHERE DepartmentName¡¯²ÆÎñ²¿¡¯) AND
Birthday!>ALL(SELECT Birthday FROM Employees
WHERE DepartmentlD IN (SELECT DepartmentlD FROM Departments
WHERE DepartmentName=¡¯Ñз¢²¿¡¯))
GO ¡¾Ë¼¿¼ÓëÁ·Ï°¡¿
ÓÃ×Ó²éѯµÄ·½·¨²éÕÒÑз¢²¿±ÈËùÓвÆÎñ²¿¹ÍÔ±ÊÕÈ붼¸ßµÄ¹ÍÔ±µÄÐÕÃû¡£ (3)²éÕÒ±ÈËùÓвÆÎñ²¿µÄ¹ÍÔ±ÊÕÈ붼¸ßµÄ¹ÍÔ±µÄÐÕÃû¡£ ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL SELECT Name FROM Employees
WHERE EmployeeID IN (SELECT EmployeelD FROM Salary
WHERE InCome> £® ALL ( SELECT InCome FROM Salary
WHERE EmployeelD IN (SELECT EmployeelD FROM Employees
WHERE DepartmentlD= (SELECT DepartmentlD FROM Departments
WHERE DepartmentName=¡¯²ÆÎñ²¿¡¯)))) GO
¡¾Ë¼¿¼ÓëÁ·Ï°¡¿
ÓÃ×Ó²éѯµÄ·½·¨²éÕÒËùÓÐÄêÁä±ÈÑз¢²¿¹ÍÔ±ÄêÁ䶼´óµÄ¹ÍÔ±µÄÐÕÃû¡£ 3£®Á¬½Ó²éѯµÄʹÓÃ
11
(1) ²éѯÿ¸ö¹ÍÔ±µÄÇé¿öÒÔ¼°ÆäнˮµÄÇé¿ö¡£ ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL
SELECT Emploees.*,Salary£®* FROM Employees,Salary
WHERE Elmployees£®EmployeelD=Salary£®EmployeelD GO
¡¾Ë¼¿¼ÓëÁ·Ï°¡¿²éѯÿ¸ö¹ÍÔ±µÄÇé¿öÒÔ¼°Æ乤×÷²¿ÃŵÄÇé¿ö¡£ (2) ²éÕÒ²ÆÎñ²¿ÊÕÈëÔÚ2200ÔªÒÔÉϵĹÍÔ±ÐÕÃû¼°ÆäнˮÏêÇé¡£ ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL
SELECT Name,InCome,OutCome
FROM Employees£¬Salary£¬Departments
WHERE Employees£®EmployeelD=Salary£®EmployeelD AND Employees£®DepartmentlD
=Departments£®DepartmentlD AND DepartmentName=¡¯²ÆÎñ²¿¡¯AND InCome>2000 GO
¡¾Ë¼¿¼ÓëÁ·Ï°¡¿²éѯÑз¢²¿ÔÚ1966ÄêÒÔÇ°³öÉúµÄ¹ÍÔ±ÐÕÃû¼°ÆäнˮÏêÇé¡£ 4£® Êý¾Ý»ã×Ü
(1)Çó²ÆÎñ²¿¹ÍÔ±µÄƽ¾ùÊÕÈë¡£
ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL
SELECT AVG(InCome)AS ¡¯²ÆÎñ²¿Æ½¾ùÊÕÈ롯 FROM Salary
WHERE EmployeelD IN (SELECT EmployeelD FROM Employees
WHERE DepartmentlD= (SELECT DepartmentlD FROM Departments
WHERE DepartmentName=¡¯²ÆÎñ²¿¡¯)) GO
¡¾Ë¼¿¼ÓëÁ·Ï°¡¿²éѯ²ÆÎñ²¿¹ÍÔ±µÄ×î¸ßºÍ×îµÍÊÕÈë¡£ (3) Çó²ÆÎñ²¿¹ÍÔ±µÄƽ¾ùʵ¼ÊÊÕÈë¡£
ÔÚ²éѯ·ÖÎöÆ÷µÄ´°¿ÚÊäÈëÈçϵÄÓï¾ä²¢Ö´ÐУº USE YGGL
SELECT AVG(1nCome-OutCome)AS¡®²ÆÎñ²¿Æ½¾ùʵ¼ÊÊÕÈ롯 FROM Salary
WHERE EmployeelD IN (SELECT EmployeeID FROM Employees
WHERE DepartanentlD = (SELECT DepartmentlD FROM Departments
WHERE DepamnentName=¡¯²ÆÎñ²¿¡¯))
12