关系型数据库多表复杂查询使用示例

--一: 

創建表格：

use ZYDB

create table ZY0001TB --專門人才基本情況表

(

ID CHAR(10) primary key,--編號

NAME VARCHAR(50),--姓名

SEX CHAR(2) CHECK(SEX='1' OR SEX='0'),--性別男"1"女"0"

BIRTHDAY DATETIME,--出生日期

MONERY INT,--工資

REMARK NTEXT NULL--備註

)

CREATE TABLE ZY0002TB   --專門人才專業特長表

(

ID CHAR(10) PRIMARY KEY,--編號

PROFESSION VARCHAR(50),--專業

PRITIME INT,--專業年限

TITLE VARCHAR(50),--職稱

ENGLEVEL VARCHAR(50) NULL   --英語水準

)

--二:

查询：

--1.查詢編號、姓名、性別、出生日期、工資、要求只選擇工資超過5000的男同志的記錄，以年齡升冪排序；

SELECT ID,NAME,SEX,BIRTHDAY,MONERY 

FROM ZY001TB 

WHERE MONERY>5000 and SEX='1'

ORDER BY BIRTHDAY DESC;

--2.查詢編號、姓名、性別、出生日期、工資、要求只選擇工資超過500，少於3000元的記錄;

SELECT ID,NAME,SEX,BIRTHDAY,MONERY 

FROM ZY001TB 

WHERE MONERY > 500 AND MONERY<3000;

--3.在ZY001TB表中查詢年齡在30-40歲間的職工的姓名，出生日期；

--方法一：

SELECT NAME,BIRTHDAY 

FROM ZY001TB 

WHERE DATEPART(YEAR,(GETDATE()-BIRTHDAY))

BETWEEN 30 AND 40;

--方法二：

SELECT NAME,BIRTHDAY

FROM ZY001TB

WHERE BIRTHDAY BETWEEN CONVERT(NVARCHAR(10),YEAR(GETDATE())-40)+'/1/1' AND CONVERT(NVARCHAR(10),YEAR(GETDATE())-30)+'/12/30';

--4.在ZY001TB表中分別查詢男職工和女職工的工資總和；

--方法一：

SELECT SEX AS '性別',SUM(MONERY) AS '工資總和'

FROM ZY001TB

GROUP BY SEX 

ORDER BY SEX;

--方法二：

SELECT SUM(CASE SEX WHEN '1' THEN MONERY END) AS '男職工工資總和',

       SUM(CASE SEX WHEN '0' THEN MONERY END) AS '女職工工資總和' 

FROM ZY001TB;

--5.在ZY001TB表及ZY002TB表中查詢職稱為副教授的記錄的姓名,專業,職稱

--方法一：

SELECT NAME,PROFESSION,TITLE 

FROM ZY001TB A,ZY002TB B

WHERE A.ID=B.ID AND TITLE='副教授';

--方法二：

SELECT Z1.NAME,Z2.PROFESSION,Z2.TITLE 

FROM ZY001TB Z1  JOIN ZY002TB Z2 ON Z1.ID=Z2.ID 

WHERE Z2.TITLE='副教授'; 

--方法三：

SELECT Z1.NAME,

  'PROFESSION'=(CASE Z2.TITLE WHEN '副教授' THEN Z2.PROFESSION END),

  'TITLE'=(CASE Z2.TITLE WHEN '副教授' THEN Z2.TITLE END)   

FROM ZY001TB Z1 RIGHT JOIN ZY002TB Z2 ON Z1.ID=Z2.ID 

WHERE Z2.ID=(CASE Z2.TITLE WHEN '副教授' THEN Z2.ID END);

       

--6.將專業年限大於20的資料的工資增長500(包括20)；

UPDATE ZY001TB

SET MONERY=MONERY + 500

WHERE ID IN(SELECT ID 

FROM ZY002TB

WHERE PRITIME>20); --將人才表與專業特長表用ID連接起來------可以不用子查詢來做

--7.在ZY001TB中新增一個欄位(ADDRESS VARCHAR(100) NULL)

ALTER TABLE ZY001TB 

ADD ADDRESS VARCHAR(100)  NULL;

--8.在ZY0001TB表中查詢工資最少的人的姓名和工資，工資最多的人的姓名和工資；

--方法一：

SELECT NAME,MONERY 

FROM ZY001TB 

WHERE MONERY=(SELECT MIN(MONERY) FROM ZY001TB) 

OR MONERY=(SELECT MAX(MONERY) FROM ZY001TB);

--方法二： 

SELECT TB.NAME,TB.MONERY  

FROM ZY001TB TB

     ,(SELECT MIN(MONERY) MN FROM ZY001TB) TB1

     ,(SELECT MAX(MONERY) MX FROM ZY001TB) TB2

WHERE TB1.MN=TB.MONERY OR TB2.MX=TB.MONERY;

--9.在ZY0001TB表中查詢所有人的平均工資；

SELECT AVG(MONERY) AS '平均工資' 

FROM ZY001TB;

--10.創建一個函數，傳入編號，依“姓名-年齡”格式，取得該編號對應的資料。Ex:dbo.fnGetName(ID)

USE ZYDB

GO

CREATE FUNCTION ST_NA(@BH CHAR(10))

RETURNS TABLE

AS

   RETURN (SELECT B.NAME AS '姓名',(GETDATE()-B.BIRTHDAY) AS '年齡' FROM ZY0001TB B WHERE B.ID=@BH)

GO

SELECT * FROM ST_NA(5)

GO

--11.編寫一個存儲過程,逐行列印出每個人的資料,包括: 編號,姓名,性別,出生日期,專業,職稱,工資現狀

--创建存储过程

IF (EXISTS (SELECT * FROM sys.objects WHERE name = 'pro_message'))

 DROP PROC pro_message

GO

CREATE PROC pro_message

AS

  SELECT TB1.ID AS '編號',

         TB1.NAME AS '姓名',

         TB1.SEX AS '性別',

         TB1.BIRTHDAY AS '出生日期',

         TB2.PROFESSION AS '專業',

         TB2.TITLE AS '職稱',

         TB1.MONERY AS '工資現狀'

  FROM  ZY001TB TB1 JOIN ZY002TB TB2 ON TB1.ID=TB2.ID;

--调用、执行存储过程

EXEC pro_message;

--三：

--1.創建表格：

CREATE TABLE S  --學生表

(

SNO INT PRIMARY KEY,---學號

SNAME VARCHAR(8)--姓名

)

CREATE TABLE C  --課程表

(

CNO INT PRIMARY KEY,--編號

CNAME VARCHAR(50),--名稱

CTEACHER VARCHAR(8)--教師

)

CREATE TABLE SC   --成績表,注：每一個學生一科只有一個成績，因此課程表裡的學號和課程號只能共同出現一次

(

SNO INT FOREIGN KEY(SNO) REFERENCES S(SNO),--學號

CNO INT FOREIGN KEY(CNO) REFERENCES C(CNO),--課程編號

SCGRADE INT--成績

)

--(1)列出每個學生的平均成績和姓名

--方法1

SELECT S.SNAME,AVG(SC.SCGRADE) 

FROM S,SC 

WHERE S.SNO=SC.SNO 

GROUP BY S.SNAME;

--方法2:因為相等連接就是內連接

SELECT SNAME,AVG(SCGRADE) AS '平均成績'

FROM  SC JOIN S ON SC.SNO=S.SNO

GROUP BY SNAME

--(2)列出有2門以上（含2門）課程不及格的學生的姓名

SELECT S.SNAME FROM SC JOIN S ON SC.SNO=S.SNO

WHERE SC.SCGRADE<60 

GROUP BY S.SNAME;--查詢課程不及格的學生及課程數,之所以用GROUP BY因為要計算一個名字的多個CNO的COUNT

--方法一：

SELECT TEMP1.SSNA 

FROM (SELECT COUNT(SC.CNO) COUC,S.SNAME SSNA 

      FROM SC JOIN S ON SC.SNO=S.SNO 

      WHERE SC.SCGRADE<60 GROUP BY S.SNAME) TEMP1 

WHERE TEMP1.COUC>=2; 

--方法二,用HAVING：

SELECT S.SNAME 

FROM SC JOIN S ON SC.SNO=S.SNO 

GROUP BY S.SNAME

HAVING COUNT(CASE WHEN SC.SCGRADE<60 THEN SC.CNO END)>=2;

--(3)列出學過“1”，“2”號課程并且有成績的學生的姓名

SELECT SNO FROM SC WHERE SC.CNO='1'

SELECT SNO FROM SC WHERE SC.CNO='2'

--方法一：

SELECT S.SNAME 

FROM S JOIN SC ON SC.SNO=S.SNO 

WHERE SC.SNO IN(SELECT SNO FROM SC WHERE SC.CNO='1') 

                AND SC.SNO IN(SELECT SNO FROM SC WHERE SC.CNO='2') AND SCGRADE is not null 

                GROUP BY S.SNAME;

--方法二：

SELECT SNAME

FROM S 

WHERE SNO IN(SELECT SNO 

          FROM SC 

          WHERE SNO IN(SELECT SNO FROM SC WHERE SC.CNO='1') 

                      AND SNO IN(SELECT SNO FROM SC WHERE SC.CNO='2') AND SCGRADE is not null 

                      GROUP BY SNO);                       

--方法三：

SELECT DISTINCT S.SNAME 

FROM S JOIN SC ON S.SNO=SC.SNO,

     (SELECT SNO FROM SC WHERE SC.CNO='1') TB1 

     JOIN (SELECT SNO FROM SC WHERE SC.CNO='2') TB2 ON TB1.SNO=TB2.SNO

WHERE S.SNO=TB1.SNO AND S.SNO=TB2.SNO AND SC.SCGRADE is not null;

--方法四： SC.SNO不能相加使用 

SELECT S.SNAME 

FROM S JOIN SC ON SC.SNO=S.SNO AND SC.SCGRADE is not null  

GROUP BY S.SNAME

HAVING SUM(CASE SC.CNO WHEN 1 THEN SC.SNO END) = SUM(CASE SC.CNO WHEN 2 THEN SC.SNO END);

--(4)列出“1”號課程成績高於“2”號課程成績的學生的姓名，“1”號課程成績，“2”號課程成績和兩門課的平均成績

SELECT S.SNO,SC.SCGRADE FROM S JOIN SC ON S.SNO=SC.SNO WHERE SC.CNO=1

SELECT S.SNO,SC.SCGRADE FROM S JOIN SC ON S.SNO=SC.SNO WHERE SC.CNO=2

--方法一：

SELECT S.SNAME,

  T1.SE AS '1號課程成績',

  T2.SE AS '2號課程成績',

  (T1.SE+T2.SE)/2 AS '兩門課的平均成績'

FROM (SELECT S.SNO SO,SC.SCGRADE SE FROM S JOIN SC ON S.SNO=SC.SNO WHERE SC.CNO=1) T1 

JOIN (SELECT S.SNO SO,SC.SCGRADE SE FROM S JOIN SC ON S.SNO=SC.SNO WHERE SC.CNO=2) T2 

ON T1.SO=T2.SO 

     JOIN S ON T1.SO=S.SNO AND T2.SO=S.SNO

WHERE T1.SE>T2.SE 

--方法二：用自連接來實現多記錄比較

SELECT DISTINCT S.SNAME 

FROM S JOIN SC ON S.SNO=SC.SNO JOIN S AS ST ON ST.SNO=SC.SNO 

WHERE (SELECT SC.SCGRADE FROM S JOIN SC ON S.SNO=SC.SNO WHERE SC.CNO=1 AND S.SNO=ST.SNO)>

      (SELECT SC.SCGRADE FROM S JOIN SC ON S.SNO=SC.SNO WHERE SC.CNO=2 AND S.SNO=ST.SNO);

--方法三：

SELECT S.SNAME AS '學生姓名',

  TEMP1.SE AS '1號課程成績',

  TEMP2.SE AS '2號課程成績',

  (TEMP1.SE+TEMP2.SE)/2 AS '1和2號課程平均成績'

FROM (SELECT SC.SNO SO,SC.SCGRADE SE FROM SC WHERE SC.CNO=1) TEMP1,

     (SELECT SC.SNO SO,SC.SCGRADE SE FROM SC WHERE SC.CNO=2) TEMP2,

     S  

WHERE TEMP1.SO=TEMP2.SO AND (S.SNO=TEMP1.SO AND S.SNO=TEMP2.SO) AND TEMP1.SE>TEMP2.SE;

--方法四：

SELECT S.SNAME AS '學生姓名',

       SUM(CASE SC.CNO WHEN 1 THEN SC.SCGRADE END) AS '1號課程成績',

       SUM(CASE SC.CNO WHEN 2 THEN SC.SCGRADE END) AS '2號課程成績',

       (SUM(CASE SC.CNO WHEN 1 THEN SC.SCGRADE END)+

       SUM(CASE SC.CNO WHEN 2 THEN SC.SCGRADE END))/2 

       AS '兩門課的平均成績'

FROM SC JOIN S ON SC.SNO=S.SNO 

GROUP BY S.SNO,S.SNAME 

HAVING SUM(CASE SC.CNO WHEN 1 THEN SC.SCGRADE END)>SUM(CASE SC.CNO WHEN 2 THEN SC.SCGRADE END);

--2.設計利用SQL語句計算從1到100中偶數的加總：

--方法一：

DECLARE @i int,@count int

SET @i=0

SET @count=0

WHILE @i<=100

BEGIN

SET @count=@count+@i;

SET @i=@i+2

END

PRINT @count

--方法二：

DECLARE @num INT 

DECLARE @total int

SET @num=100

SET @total=0

WHILE(@num>0)

BEGIN

SET @total=@total+@num

SET @num=@num-2

END

PRINT @total

--方法三：

SELECT SUM(number) 

FROM MASTER..spt_values 

WHERE number %2=0 and TYPE='p' AND number<=100

--3.用一條SQL語句將三個記錄插入到數據表中，數據表中有三列：圖書編號、書名、出版社.

--方法一：

INSERT INTO C(CNO,CNAME,CTEACHER)

SELECT 7,'JAVA','韋老師'

UNION ALL 

SELECT 9,'c#','黃老師'

UNION ALL 

SELECT 10,'ASP','李老師';

--方法二：

INSERT INTO C(CNO,CNAME,CTEACHER) 

VALUES(7,'JAVA','韋老師'),(9,'c#','黃老師'),(10,'ASP','李老師');

/*

4.有如下三張表：

學生表：學號、姓名。

選課表：學號、課程號、成績

課程表：課程號、課程名

*/

--要求用嵌套查詢找出選修“數據庫原理”的課程的學生

SELECT SNAME 

FROM S 

JOIN SC ON S.SNO=SC.SNO 

WHERE SC.CNO IN

     (

SELECT CNO 

FROM C 

WHERE C.CNO=SC.CNO AND C.CNAME='數據庫原理'

     );

--5.利用上述三張表找出既選修課程編號為“0001”，又選修過課程編號為“0002”的學生姓名

SELECT SNAME 

FROM S JOIN SC ON S.SNO=SC.SNO

WHERE SC.CNO=1 AND SC.CNO=2;

SELECT S.SNAME

FROM S JOIN SC ON S.SNO=SC.SNO

WHERE S.SNO IN(SELECT SC.SNO FROM SC WHERE SC.CNO=1 AND SC.CNO=2 GROUP BY SC.SNO);

/*有兩個表： TableX有三個欄位Code、 Name、 Age、 其中Code為主鍵； 

TableY有三個欄位Code、 Class、Score, 其中Code + Class 為主鍵。兩表記錄如下： 

Code Name Age Code Class Score 

97001 張三 22 97001 數學 80 

97002 趙四 21 97002 電腦 59 

97003 張飛 20 97003 電腦 60 

97004 李五 22 97004 數學 55

*/

--(1)李五的年齡記錄錯了，應該是21，請寫SQL，根據主鍵進行更新

UPDATE TableX 

SET Age=21 

WHERE Code=97004;

--(2)請寫SQL，刪除TableX中沒有考試成績的學生記錄，請使用not in條件

DELETE 

FROM TableX JOIN TableY ON  TableX.Code=TableY.Code 

WHERE TableX.Code NOT IN(SELECT TableY.Code FROM TableY WHERE TableY.Score IS NULL);

/*

7.有三個表： 

學生表：student  欄位:stuID ,chinesename , sex ,scholarship(獎學金) 

成績表：grade    欄位:graID , stuID , courceID ,grade 

課程表：cource   欄位:courceID , courceName, xuefen 

*/

--求：查詢分數都在90分以上的學生的stuID, chinesename

SELECT S.stuID,S.chinesename 

FROM student S JOIN grade G ON S.stuID=G.stuID 

WHERE S.stuID IN (SELECT grade.stuID FROM grade WHERE grade.grade>90);

/*

8.有如下的一個表，

RACEDATE RESULT 

2009-7-16 勝 

2009-7-16 勝 

2009-7-16 負 

2009-7-17 勝 

2009-7-17 負 

2009-7-17 勝 

2009-7-17 負  

要求輸出的結果為： 

RACEDATE 勝 負 

2009-7-16 2 1 

2009-7-17 2 2 

*/

--創建表並插入數據

CREATE TABLE TABLE1 

(

RACEDATE CHAR(20),

RESULT CHAR(4)

)

INSERT INTO TABLE1 VALUES('2009-7-17','勝');

--方法一：

SELECT RACEDATE,

       SUM(CASE WHEN RESULT='勝' THEN 1 ELSE 0 END) AS '勝',

       SUM(CASE WHEN RESULT='負' THEN 1 ELSE 0 END) AS '負'

FROM TABLE1 

GROUP BY RACEDATE;

--方法二：

SELECT RACEDATE

       ,COUNT(CASE RESULT WHEN '勝' THEN RESULT END) AS '勝'

       ,COUNT(CASE RESULT WHEN '負' THEN RESULT END) AS '負' 

FROM TABLE1

GROUP BY RACEDATE;