SQL Server中常用的SQL語句

文章目錄

• 1、概述
• 2、查詢概述
• 3、單表查詢
• 4、串連查詢
• 5、帶有exists的相互關聯的子查詢
• 6、SQL的集合操作
• 7、插入操作
• 8、刪除操作
• 9、修改操作
• 10、資料定義
• 11、視圖

1、概述名詞

笛卡爾積、主鍵、外鍵

資料完整性

• 實體完整性：主屬性不可為空值，例如選課表中學號和課程號不可為空
• 參照完整性：表中的外鍵取值為空白或參照表中的主鍵
• 使用者定義完整性：取值範圍或非空限制，例如：性別（男女），年齡（0-130）

　　

表串連

• 自然串連：與等值串連（a.id=b.id）相比，串連後的表只有一列id，而不是兩列（a.id和b.id）。
• 半串連：與等值串連（a.id=b.id）相比，串連後的表只有A表的列，被B表“多次匹配”列會顯示為一行。
• 左外串連：left join
• 右外串連：right join
• 全外串連：full join
• 全內串連：inner join

SQL語言的構成

• DDL語言：資料定義，定義基本表、視圖、索引；
• DML語言：資料操縱，查詢、增加、修改、刪除
• DCL語言：許可權

　　

2、查詢概述

查詢包括：單表查詢、串連查詢、帶有exists的相互關聯的子查詢、集合操作四中。select...from常用語句執行過程

select…            ⑤    投影from…              ①    table→記憶體where…             ②    選取元組group…             ③    分組having…            ④    選擇分組[{union|…}         ⑥    查徇結果的集      合運算select…  ]         ①～⑤order by…          ⑦    排序輸出

3、單表查詢

group by 只有出現在group by子句中的屬性，才可出現在select子句中。

用order by子句對查詢結果按照一個或多個列的值進行升/降排列輸出,升序為ASC；降序為desc,空值將作為最大值排序

having 與 where的區別

• where 決定哪些元組被選擇參加運算，作用於關係中的元組
• having 決定哪些分組符合要求，作用於分組

4、串連查詢

串連查詢包括：多表串連查詢、單表串連查詢(自串連)、外串連查詢、巢狀查詢4種

串連條件一

[表名1.] 列名1 比較子  [表名2.]列名2

串連條件二

[表名1.]列名1  between [表名2.]列名2 and [表名2.]列名3

串連條件中的列名稱為串連欄位，對應的串連欄位應是可比的。

執行過程：採用表掃描的方法，在表1中找到第一個元組，然後從頭開始掃描表2，尋找到滿足條件的元組即進行串接並存入結果表中；再繼續掃描表2，依次類推，直到表2末尾。再從表1中取第二個元組，重複上述的操作，直到表1中的元組全部處理完畢。

4.1 單表串連（自串連）

用表別名把一個表定義為兩個不同的表進行串連。

例：尋找至少選修了2號和4號課程的學生的學號

select FIRST.snofrom SC as FIRST, SC as SECOND where FIRST.Sno=SECOND.Sno and FIRST.cno='s2' and SECOND.cno='4'

4.2 外串連查詢

外串連查詢包括：Left join、right join、full join

4.3 巢狀查詢

• 在select … from … where語句結構的where子句中可嵌入一個select語句塊
• 其上層查詢稱為外層查詢或父查詢，其下層查詢稱為內層查詢或子查詢
• SQL語言允許使用多重巢狀查詢
• 在子查詢中不允許使用order by子句
• 巢狀查詢的實現一般是從裡到外，即先進行子查詢，再把其結果用於父查詢作為條件

4.3.1 返回單個值的子查詢

例：求與“劉力”同一個系的學生名，年齡

方法一：select Sname, Sage from student where Sdept = (select sdept from student where  Sname = "劉力");方法二：select FIRST.Sname, FIRST.Sage from Student FIRST, Student SECONDwhere FIRST.Sdept = SECOND.Sdept AND SECOND.Sname = "劉力";

4.3.2 返回一組值的子查詢

例：求選修“C6”課程且成績超過90分的學生

方法一：select * from student where sno IN (select sno from SC where Cno="C6" AND Grade>90);方法二（串連查詢 ）：select student.*from student，SCwhere Student.Sno=SC.Sno AND Cno="C6" AND Grade>90;

例：求比電腦系中某一學生年齡小的其他系的學生

方法一：select * from student where    sdept!="CS" AND      sage < ANY (select Sage from Student where Sdept="CS");方法二：select  *from Studentwhere      Sdept!=’CS’ AND     Sage < (select MAX(Sage) from Student where Sdept="CS");

4.3.3 多重子查詢

例：求D01部門中工資與國貿系中任意職工相同的職工姓名和工資

表結構：Teacher(tno, tname, salary, dno)Department(dno, dname)查詢語句：select Tname，Salaryfrom Teacherwhere     Dno = "D01" AND     salary IN(        select salary from  teacher        where Dno =（select DNO from department where  Dname="國貿")    );

例：求工資介於“張三”與“裡司”兩個之間的職工

select *from teacherwhere    Salary >= (select MIN(Salary) from teacher where Tname IN ("張三", "裡司")) AND      Salary <= (select MAX(Salary) from teacher where Tname IN ("張三", "裡司");

4.3.4 在from語句中使用子查詢，對查詢結果定義表名及列名

例：求平均成績超過80分的學號及平均成績

select Sno, avg_Gfrom (select Sno, avg(Grade) from SC group by Sno) AS RA(Sno, avg_G)where avg_G > 80;

AS RA(Sno, avg_G)，為查詢作為定義表名（RA）和列名（Sno, avg_G）

5、帶有exists的相互關聯的子查詢

• 不相互關聯的子查詢：子查詢的查詢條件不依賴於父查詢的稱為不相互關聯的子查詢。
• 相互關聯的子查詢：子查詢的查詢條件依賴於外層父查詢的某個屬性值的稱為相互關聯的子查詢，帶exists 的子查詢就是相互關聯的子查詢
• exists表示存在量詞，帶有exists的子查詢不返回任何記錄的資料，只返回邏輯值“True” 或“False”

例：求所有選修了“C1”課程的學生名。

不相互關聯的子查詢：    select Sname from student where sno IN ( select sno from SC where Cno = "C1" ); 相互關聯的子查詢select Sname from studentwhere exists (select * from SC where student.sno=SC.sno AND Cno = "C1" );

相互關聯的子查詢執行過程：先在外層查詢中取student表的第一個元組（記錄），用該記錄的相關的屬性值（在內層where子句中給定的）處理內層查詢，若外層的where子句返回‘TRUE’值，則此元組送入結果的表中。然後再取下一個元組；重複上述過程直到外層表的記錄全部遍曆一次為止。

• 不關心子查詢的具體內容，因此用 select *
• exists + 子查詢用來判斷該子查詢是否返回元組
• 當子查詢的結果集非空時，exists 為“True”；當子查詢的結果集為空白時，exists為“False”。
• not exists ：若子查詢結果為空白，返回“TRUE”值，否則返回“FALSE”

例：查詢選修了所有課程的學生的姓名（續）

select Snamefrom studentwhere not exists (    select *     from Course    where not exists (        select * from SC where         student.sno=SC.sno AND Course.Cno=SC.Cno    ));

例：查詢至少選修了S1所選的全部課程的學生名

select Snamefrom studentwhere not exists(    select *    from SC SCX    where         SCX.sno="s1" AND         not exists (            select *            from SC SCY            where student.sno=SCY.sno AND SCX.Cno=SCY.Cno        ));

6、SQL的集合操作

• 屬性個數必須一致、對應的類型必須一致
• 屬性名稱可以不一致， 最終結果集採用第一個結果的屬性名稱
• 預設為自動去除重複元組，除非顯式說明ALL
• order by放在整個語句的最後

6.1 “並”操作，例：查詢電腦系的學生或者年齡不大於19歲的學生，並按年齡倒排序。

select * from student where Sdept="CS"UNIONselect * from student where AGE <= 19order by AGE desc

6.2 “交”操作，例：查詢電腦系的學生並且年齡不大於19歲的學生，並按年齡倒排序。

(select * from student where Sdept = "CS") INTERSECT(select * from student where AGE <= 19)order by AGE desc

6.3 “差”操作，例：查詢選修課程1但沒有選修課程2的學生。

select Sname, Sdeptfrom studentwhere sno IN (    (select sno from SC where Cno="1")    EXCEPT    (select sno from SC where Cno="2")) 

7、插入操作

格式：insert  into  表名[(列名1,…)]  values  (列值1,…)

插入一已知元組的全部列值

insert into student values("2003001", "陳冬", 18, "男", "電商", "管理學院", "徐州");

插入一已知元組的部分列值

insert into SC(Sno，Cno) values ("2003001", "C003");

插入子查詢的結果例：設關係S_G(Sno,avg_G),把平均成績大於80的男生的學號及平均成績存入S_G中

insert into S_G(sno，avg_G) (    select sno, avg(GRADE)    from SC    where Sno IN (select Sno from Student where SEX="男")    group  by  Sno    having  avg(GRADE) > 80);

8、刪除操作

格式： delete from 表名 [where 條件];

• 只能對整個元組操作，不能只刪除某些屬性上的值
• 只能對一個關係（表）起作用，若要從多個關係（表）中刪除元組，則必須對每個關係分別執行刪除命令

9、修改操作

update語句一次只能操作一個表。

格式1：update 表名 [別名]set 列名 = 運算式, ...[where 條件];格式2：update 表名 [別名]set (列名, ...) = (子查詢)[where 條件];

例：工種為SALESMEN的職工的工資改為工種平均工資的110%

update EMPLOYEEset Salary = (select 1.1 * avg(Salary) from EMPLOYEE where JOB="SALESMEN")where JOB="SALESMEN"; 

例：將所有學生的年齡增加1歲

update student set Sage=Sage+1; 

10、資料定義建立課程表

create table SC (    sno CHAR(6) not null,    Cno CHAR(6) not null,    Grade smallint default null)primary key (sno,Cno)foreign key (sno) references student(sno)foreign key (Cno) references Course(Cno)check (Grade between 0 AND 100);

常用的索引:唯一索引和聚簇索引唯一索引

• 對於已含重複值的屬性列不能建UNIQUE索引
• 對某個列建立UNIQUE索引後，插入新記錄時DBMS會自動檢查新記錄在該列上是否取了重複值。這相當於增加了一個UNIQUE約束

create UNIQUE INDEX  Stusno ON Student(Sno ASC);

聚簇索引

建立聚簇索引後，基表中資料也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項目順序與表中記錄的物理順序一致

create CLUSTER INDEX Stusname ON Student(Sname);

在Student表的Sname（姓名）列上建立一個聚簇索引，而且Student表中的記錄將按照Sname值的升序存放。Sql server中的表示方式create clustered index。某些DMBS不支援聚簇索引，所以用前一定要查使用說明。

• 在一個基本表上最多隻能建立一個聚簇索引
• 聚簇索引的用途：對於某些類型的查詢，可以提高查詢效率
• 聚簇索引的適用範圍：很少對基表進行增刪操作；很少對其中的變長列進行修改操作

刪除索引

刪除索引時，系統會從資料字典中刪去有關該索引的描述。

DROP INDEX [表名.]<索引名>;

例：刪除Student表的Stusname索引

DROP INDEX Student.Stusname;

11、視圖

例：建立電商系學生的視圖

create view ec_studentas select sno, sname, age from student where dept="ec"

刪除視圖

DROP VIEW <視圖名>

一個視圖被刪除後，由此視圖匯出的其他視圖也將失效，使用者應該使用DROP VIEW語句將他們一一刪除