SQL 簡單，複雜查詢，基本函數查詢

use 學生資料庫;drop database 學生資料庫;create database 學生資料庫ON(NAME = 學生資料庫_資料檔案,FILENAME='E:\sql sever 2000\學生資料庫_資料檔案.MDF',SIZE=10,MAXSIZE =50,FILEGROWTH=5)LOG ON (NAME = 學生資料庫_記錄檔,FILENAME='E:\sql sever 2000\學生資料庫_記錄檔.LDF',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)GOCREATE TABLE 學生表(SNO CHAR(4),SN CHAR(8),SEX CHAR(2) NOT NULL,AGE INT NOT NULL,DEPT INT NOT NULL,PRIMARY KEY(SNO),CHECK (AGE BETWEEN 14 AND 45),CHECK (SEX='男'OR SEX='女'))CREATE TABLE 課程表(CNO CHAR(4),CN CHAR(8) NOT NULL,PRIMARY KEY(CNO))CREATE TABLE 學生選課表(SNO CHAR(4),CNO CHAR(4) NOT NULL,GRADE INT NULL,PRIMARY KEY(SNO,CNO),FOREIGN KEY(SNO) REFERENCES 學生表(SNO),FOREIGN KEY(CNO) REFERENCES 課程表(CNO))insert into 學生表 values( 'S1','徐琳', '女', 17,2);insert into 學生表 values( 'S2','李國華','男', 18,6);insert into 學生表 values( 'S3','徐萍', '女', 20, 1);insert into 學生表 values( 'S4','林新明', '男', 23, 6);insert into 學生表 values( 'S5','張家傑', '男', 19, 6) ;insert into 學生表 values( 'S6','張婷', '女', 21, 3);insert into 學生表 values( 'S7','趙大地', '男', 18, 6) ;insert into 學生表 values( 'S8','趙樹林', '男', 19, 3);insert into 課程表 values( 'C1','數學') ;insert into 課程表 values( 'C2','英語');insert into 課程表 values( 'C3','C語言') ;insert into 課程表 values( 'C4','資料庫');insert into 課程表 values( 'C5','政治') ;insert into 課程表 values( 'C6','物理');insert into 課程表 values( 'C7','心理學');insert into 學生選課表 values( 'S1','C1',80); insert into 學生選課表 values( 'S1','C2',85);insert into 學生選課表 values( 'S1','C6',75);insert into 學生選課表 values( 'S1','C4',56);insert into 學生選課表 values( 'S1','C5',90); insert into 學生選課表 values( 'S2','C1',47);insert into 學生選課表 values( 'S2','C3',80); insert into 學生選課表 values( 'S2','C4',75);insert into 學生選課表 values( 'S2','C5',70);insert into 學生選課表 values( 'S6','C1',95);insert into 學生選課表 values( 'S6','C2',80); insert into 學生選課表 values( 'S6','C3',87);insert into 學生選課表 values( 'S3','C1',75);insert into 學生選課表 values( 'S3','C2',70);insert into 學生選課表 values( 'S3','C3',85); insert into 學生選課表 values( 'S3','C4',86);insert into 學生選課表 values( 'S3','C5',90); insert into 學生選課表 values( 'S3','C6',99);insert into 學生選課表 values( 'S4','C1',83); insert into 學生選課表 values( 'S4','C2',85);insert into 學生選課表 values( 'S4','C3',83); insert into 學生選課表 values( 'S5','C2',99);SELECT * FROM 課程表; --*表示表中的所有列SELECT * FROM 課程表;SELECT * FROM 學生選課表;SELECT SNO FROM 學生選課表;SELECT DISTINCT SNO FROM 學生選課表;  --DISTINCT 在結果中消除重複行--WHERE 子句限定查詢條件--WHERE 子句用來限定查詢條件，可用條件運算式，也可以用著鍵字來。/*條件運算式有兩種：1、算術運算式： >,<,=,<>,>=,<=,!>,!<,!=2、邏輯運算式：NOT,AND ,OR關鍵字是：BETWEEN, IN ,NOT IN*/USE 學生資料庫;--從學生中，檢索出分數大於85，小於95之間的學生號及課程號，並且漢字顯示內容SELECT  SNO 學生號,SNO 課程號 FROM 學生選課表  WHERE GRADE>85 AND GRADE <95;--從學生中，檢索出學號分別的S2,S4,S6的學生姓名，年齡，並且漢字顯示內容SELECT SN 學生姓名, AGE 年齡 FROM 學生表 WHERE SNO IN('S2','S4','S6');--多表查詢--1、把多張表的名字寫在FROM 後面，然後用WHERE子句限定表之間的串連條件--2、用JOIN和ON兩個關鍵字，JOIN 用於串連兩張表,ON 用於給出兩張表相連條件--檢索李國華同學所學課程的課程號，課程名和成績，結果標題用中文SELECT 課程表.CNO 課程號,CN 課程名, GRADE 成績 FROM 學生選課表,課程表,學生表 WHERE學生選課表.CNO =課程表.CNO AND 學生表.SNO=學生選課表.SNO AND 學生表.SN='李國華';SELECT 課程表.CNO 課程號,CN 課程名, GRADE 成績 FROM 課程表 JOIN 學生選課表 ON 課程表.CNO =學生選課表.CNO JOIN 學生表 ON 學生表.SNO= 學生選課表.SNOWHERE 學生表.SN='李國華';-------------------------------------自表查詢-----------------------------------檢索所有比徐萍年齡大的學生姓名，年齡，性別以及徐萍的年齡USE 學生資料庫;SELECT 學生表1.SN 比徐萍年齡大的學生名, 學生表1.SEX 性別,學生表1.AGE 年齡,學生表2.AGE 徐萍的年齡FROM 學生表 學生表1, 學生表  學生表2 WHERE 學生表1.AGE>學生表2.AGE AND 學生表2.SN='徐萍';--LIKE --- LIKE 子句必需與指定的符號聯合起來，以構成查詢條件---------1、檢索姓趙的學生所在的系以及所選的課程名SELECT DEPT 系別, CN 課程名 FROM 學生表,課程表,學生選課表WHERE 學生表.SNO=學生選課表.SNO AND 課程表.CNO=學生選課表.CNO AND SN LIKE'張%';SELECT DEPT 系別, CN 課程名 FROM 學生表 JOIN 學生選課表 ON 學生表.SNO=學生選課表.SNO JOIN 課程表 ON 課程表.CNO=學生選課表.CNO WHERE SN LIKE '張%';--檢索學產生績高於90分的學生名，所選的課程名以及成績，結果標題用中文SELECT SN 學生名,CN 課程名,GRADE 成績 FROM 學生表JOIN 學生選課表 ON 學生選課表.SNO=學生表.SNOJOIN 課程表 ON 課程表.CNO=學生選課表.CNOWHERE GRADE LIKE '9%';--------------------複雜查詢---------------------------------用多條SELECT ... FROM ...WHERE 語句實現巢狀查詢--查出與張婷同一個系的學生名SELECT SN FROM 學生表 WHERE DEPT IN(SELECT DEPT FROM 學生表 WHERE SN='張婷');--查出和趙樹林同年齡的學生名，其結果將不包括趙樹林本人。SELECT SN 與趙樹林同年齡的學生名 FROM 學生表 WHERE AGE IN (SELECT AGE FROM 學生表 WHERE  SN='趙樹林') AND SN<>'趙樹林';/*分析：1、用子句找出趙樹林的年齡是19  SELECT AGE FROM 學生表 WHERE SN='趙樹林';2、然後找出年齡為19的學生名  SELECT SN FROM 學生表 WHERE AGE =193、最後用IN關鍵字把以上兩部分聯絡起來，查出和趙樹林同年齡的學生，並用<>運算排隊趙樹林*/--找出同時選學課程號為C3和C5的學生號，學生名和性別SELECT 學生表.SNO 學生號,SN 學生名,SEX 性別 FROM 學生表,學生選課表 WHERE 學生表.SNO= 學生選課表.SNO AND CNO='C3'AND 學生表.SNO IN( SELECT SNO FROM 學生選課表 WHERE CNO='C5');----///////////////////////////------SELECT 學生表.SNO ,SN,SEX FROM 學生表 JOIN 學生選課表 ON 學生表.SNO=學生選課表.SNOWHERE CNO='C3' AND 學生表.SNO IN(SELECT SNO FROM 學生選課表 WHERE CNO='C5');/*分析：1、選學課程號為C5的學生號      2、選學課程號為C3的學生號，學生名和年齡      3、把第一步與第二步進行'與'運算*/--找出選學課程號為C3和課程名為政治的學生號和學生名SELECT SNO,SN FROM 學生表 WHERE SNO  IN(SELECT SNO FROM 學生選課表 WHERE CNO='C3' AND SNOIN(SELECT SNO FROM 課程表,學生選課表 WHERE 課程表.CNO=學生選課表.CNO AND 課程表.CN='政治'));--------///////////---------SELECT 學生表.SNO,SN FROM 學生表 JOIN 學生選課表 ON 學生表.SNO =學生選課表.SNOWHERE CNO ='C3' AND 學生表.SNO IN(SELECT SNO FROM 學生選課表 WHERE CNO IN (SELECT CNO FROM 課程表 WHERE CN='政治'));/* 分析：1、先找出課程名為政治的課程號是C5         2、找出由第一步找出的課程號為C5的學生號 3、找出選學課程號為C3的學生號和學生名         4、對第二步和第三步進行‘與’運算*/----NOT IN-------找出沒有選擇課程號為C3的學生名SELECT SN FROM 學生表 WHERE SNO NOT IN(SELECT SNO FROM 學生選課表 WHERE CNO='C3');/* 分析：1、在學生選課表中，找出課程號是C3的學生號 2、在學生表中，找出學生號不是C3的學生號的學生名         3、把第一步插入到第二步中，即本題結果註：使用IN('C1','C2')這種情況是‘或’的關係，當使用NOT IN('C1','C2')則是‘與’的關係*/----EXISTS, NOT EXISTS 查詢--查詢選修課程號為C4的學生號和學生名，分別用EXISTS 和IN 兩種關鍵字來查詢--1、用EXISTSSELECT SNO ,SN FROM 學生表 WHERE  EXISTS (SELECT * FROM 學生選課表 WHERE 學生選課表.SNO=學生表.SNO AND 學生選課表.CNO ='C4');/*SELECT SNO ,SN FROM 學生表 WHERE  EXISTS (SELECT 學生選課表.SNO ,SN FROM 學生選課表 WHERE 學生選課表.SNO=學生表.SNO AND 學生選課表.CNO ='C4');1、在EXISTS 關鍵字的巢狀查詢中，外圍查詢的資料表與嵌套子句中的資料表之間串連，是通過嵌套子句中列出兩張表串連條件2、在嵌套子句(select * .....)中，跟在SELECT 關鍵字後面是的*而不是列名。*/SELECT SNO ,SN FROM 學生表 WHERE SNO IN(SELECT SNO FROM 學生選課表 WHERE CNO='C4');--找出沒有選擇課程號為C3的學生名SELECT SN FROM 學生表 WHERE SNO NOT IN(SELECT SNO FROM 學生選課表 WHERE CNO='C3');---------------------------------------------------------------SELECT SN FROM 學生表 WHERE NOT EXISTS (SELECT SNO FROM 學生選課表 WHERE  學生選課表.SNO=學生表.SNO AND 學生選課表.CNO='C3');--NOT EXISTS 能夠方便地解決'沒有...','不存在','所有'查詢難題--檢索選學了所有課程的學生名和學生號SELECT SN 學生名, SNO 學生號 FROM 學生表WHERE NOT EXISTS (SELECT * FROM 課程表 WHERE NOT EXISTS (SELECT * FROM 學生選課表 WHERE 學生表.SNO=學生選課表.SNO AND 課程表.CNO=學生選課表.CNO));/*1、檢索課程表中的課程，這些課程在學生選課表中不存在2、如果第一步沒有結果，表示不存在這樣的學生，沒有課程，他們沒有選。即：存在選學了所有課程的學生3、如果第一步有結果，表示存在這樣的學生，有課程，他們沒有選，即：不存在選學了所有課程的學生。*/-----------------------函數查詢----------------------------------------1、檢索學校有多少個學生，多少個系，學生的平均年齡，學生最大年齡，學生最小年齡，結果標題用中文.SELECT COUNT(SNO) 學生總數,COUNT(DISTINCT DEPT) 系別總數,AVG(AGE) 學生平均年齡, MAX(AGE) 學生最大年齡,MIN(AGE) 學生最小年齡 FROM 學生表;--跟在GROUP BY 後面的列名，應該是跟在關鍵字SELECT 後面的列名相同--GROUP BY 後面的列可以是任意多--2、按性別計算學生的平均年齡 SELECT SEX 性別,AVG(AGE) 平均年齡 FROM 學生表 GROUP BY SEX;--按學生學號，性別，年齡來計算學生的平均成績SELECT 學生表.SNO 學號,SEX 性別,AGE 年齡 ,AVG (GRADE)學生平均成績 FROM 學生表,學生選課表 WHERE 學生選課表.SNO= 學生表.SNO GROUP BY 學生表.SNO, SEX,AGE;--找出選修的課程至少四門的學生的姓名，性別，年齡，平均成績，選課門數。結果標題用中文/*HAVING 子句必須與GROUP BY 子句聯合起來使用  HAVING 子句給出分組查詢的條件 HAVING 子句的分組查詢條件必須是統計函數*/SELECT SN 姓名, SEX 性別, AGE 年齡,AVG(GRADE) 平均成績,COUNT(學生選課表.CNO) 選課門數 FROM 學生選課表 JOIN 學生表 ON 學生選課表.SNO=學生表.SNO JOIN  課程表 ON 學生選課表.CNO=課程表.CNOGROUP BY SN,SEX,AGEHAVING COUNT(學生選課表.CNO)>=4;--找出選課在三門以上課程的學生的總成績(不統計不及格的課程)，並列出總成績名次名單。結果標題用中文SELECT SNO 學生號,SUM(GRADE) 總成績 FROM 學生選課表 WHERE GRADE >=60GROUP BY SNOHAVING COUNT(*)>=3ORDER BY 2 DESC;--找出選學課程資料最多的學生SELECT 學生表.SN 學生名 FROM 學生選課表, 學生表 WHERE 學生選課表.SNO=學生表.SNOGROUP BY 學生表.SNHAVING COUNT(*)>=ALL(SELECT COUNT(*) FROM 學生選課表 GROUP BY SNO);--學生選學的課程如果超過了四門課，則每門課加5分，請更新資料庫UPDATE 學生選課表 SET GRADE =GRADE+5 WHERE SNO IN (SELECT SNO FROM 學生選課表 GROUP BY SNO HAVING COUNT(*)>=4);