[資料庫系統]第5講 基本SQL知識點整理

第1節 SQL查詢語言概覽

• 資料定義語言（Data-Definition Language，DDL）：SQL DDL提供定義關系模式、删除關系以及修改關系模式的指令。
• **資料操縱語言（Data-Manipulation Language，DML）：**SQL DML提供從資料庫中查詢資訊，以及在資料庫中插入元組、删除元組、修改元組的能力。
• 完整性： SQL DDL包括定義完整性限制的指令，儲存在資料庫中的資料必須滿足所定義的完整性限制。破壞完整性限制的更新是不被允許的。
• **視圖定義：**SQL DDL包括定義視圖的指令。
• **事務控制：**SQL包括定義事務的開始和結束的指令。
• 嵌入式SQL和動态SQL
• 授權(authorization)：SQL DDL包括定義對關系和視圖的通路權限的指令。

SQL如何實際建立一個關系模式結構？

create tabel department
( dept_name varchar(20),
  building varchar(15),
  budget numeric(12,2),
  primary key (dept_name));
           

為什麼要定義完整性限制？

因為完整性限制保證授權使用者對資料庫所做的修改不會破壞資料的一緻性，可以防止對資料的意外破壞。

create database到底做了什麼？

1. 産生了一個資料庫（空倉庫，僅包括系統資料字典）
2. 初始庫小，資料增長需要時才增大庫空間
3. 同時，還産生了一個日志存放的空倉庫（備份回複用）
4. 還涉及到實體設計工作：庫放在何位置、庫大小、庫增量。而且日志倉庫位置可用與資料倉庫位置不同(保證安全)！

第2節 SQL資料定義

資料庫中的關系集合必須由資料定義語言（DDL）指定給系統。SQL的DDL不僅能夠定義一組關系，還能夠定義每個關系的資訊。

3.2.1 基本類型

• char(n)：固定長度的字元串，使用者指定長度n
• varchar(n)：可變長度的字元串，使用者指定最大長度n
• int(n)：整數類型（和機器相關的相關的整數類型的子集）
• numeric(p, d)：定點數，精度由使用者指定。這個數有p位數字（加上一個符号位），其中d位數字在小數點右邊。
• real，double precision：浮點數與雙精度浮點數，精度與機器相關。
• float(n)：精度至少位n位的浮點數

3.2.2 基本模式定義

create tabel department
( dept_name varchar(20),
  building varchar(15),
  budget numeric(12,2),
  primary key (dept_name));
           

SQL支援許多不同的完整性限制

• primary key： primary key聲明表示屬性Aj1，Aj2，…，Ajm構成關系的主碼。主碼屬性必須非空且唯一，也就是說沒有一個元組在主碼屬性上取空值，關系中也沒有兩個元組在主碼屬性上取值相同。
• foreign key reference： foreign key聲明表示關系中任意元組在屬性(Ak1, Ak2, …, Akn)上的取值必須對應于關系s中某元組在主碼屬性上的取值。
• **not null：**一個屬性上的not null限制表明在該屬性上不允許空值。

第3節 SQL查詢的基本結構

3.3.1 單關系查詢

select name
from instructor;
           

删除重複

select distinct dept_name
from instructor;
           

SQL允許我們使用關鍵詞all來顯式指明不去除重複：

select all dept_name
from instructor;
           

where 子句允許我們隻選出那些在from 子句的結果關系中滿足特定謂詞的元組：

select name
from instuctor
where depat_name = ‘Comp.Sci’ and salaty > 70000;
           

Where子語句在關系代數操作上的作用?

1. 關系記錄的篩選
2. 兩關系間的連接配接

自然連接配接Natural join與迪卡兒積Χ兩點最大不同

1. 僅包含符合連接配接條件的元組
2. 連接配接屬性僅出現一次

3.3.2 多關系查詢

select name, instructor.dept_name, building 
from instructor, department
where instructor.dept_name = department.dept_name;
           

通常來說，一個SQL查詢的含義可以了解如下：

1. 為from子句中列出的關系産生笛卡爾積
2. 在步驟1的結果上應用where子句中指定的謂詞
3. 對于步驟2結果中的每個元組，輸出select子句中指定的屬性

第4節 SQL的資料查詢能力

4.1 聚集函數

聚集函數是以值的一個集合（集或多重集）為輸入、傳回單個值的函數。SQL提供了五個固有聚集函數：

• 平均值：avg
• 最小值：min
• 最大值：max
• 總和：sum
• 計數：count

SQL查詢能力很強：

1. 實作了基本代數運算
2. 靈活的表間連接配接方式
3. 實作了代數運算複合(下面的嵌套子查詢)
4. 靈活的where條件
5. 聚集函數等常用函數
6. 嵌入式和動态SQL

案例

• 僅計算一個系的平均工資

select avg (salary)
from instructor
where dept_name= 'Comp. Sci.';
           

• 計數前先去除重複元組

select count (distinct ID)
from teaches
where semester ='Spring' and year=2010;
           

• *代表選擇所有屬性

select count (*)
from course;
           

• 第1個為平均工資顯示部門名，第2個用于指定計算範圍(分組)

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;
           

• 限定輸出哪些平均工資(結果篩選)

select dept_name, avg (salary)
from instructor
group by dept_name
having avg (salary) > 42000;
           

4.2 嵌套子查詢

什麼是SQL嵌套子查詢？

• 子查詢是嵌套在另一個查詢中的select-from-where表達式。子查詢嵌套在where子句中，通常用于對集合的成員資格、集合的比較以及集合的基數進行檢查。

4.2.1 集合成員資格

SQL允許測試元組在關系中的成員資格。連接配接詞in測試元組是否是集合中的成員，集合是由select子句産生的一組值構成的。連接配接詞not in則測試元組是否不是集合中的成員。

找出在2009年秋季，但不在2010年春季同時開課的所有課程

select distinct course_id
from section
where semester = ’Fall’ and year= 2009 and course_id  not in (select course_id    
                    from section
                    where semester = ’Spring’ and year= 2010);

           

4.2.2 集合的比較

查出這些老師的姓名，他的工資要比Biology系某教師工資高

select name
from instructor
where salary > some (select salary
                  from instructor
                  where dept_name ='Biology');
           

找出平均工資最高的系

select dept_name
from instructor
group by dept_name
having avg(salary) >= all(select avg(salary)
                          								from instuctor
                          								group by depat_name);
           

4.2.3 空關系測試

找出在2009年秋季和2010年春季同時開課的所有課程

select course_id
from section as S
where semester='Fall' and year=2009 and
exist (select *
                from section as T
                where semester='Sring' amd year=2010 and
								S.course_id=T.course_id);	
           

4.2.4 屬性的别名

select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_slary
           from instructor
           group by depat_name)   as B
where avg_salary >42000;
           

4.2.5 表的别名

select dept_name,
           (select count(*)
             from instructor
             where department.dept_name=instructor.dept_name)
             as num_instructors
from department;
           

4.3 資料庫的修改

4.3.1 删除資料（可利用嵌套子句）

delete from instructor
where dept_name= ’Finance’;

delete from instructor
where dept_name in (select dept_name 
                    from department 
                    where building = ’Watson’);
           

4.3.2 插入資料

insert into course (course_id, title, dept_name, credits)
 values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);
           

insert into student
 values (’3003’, ’Green’, ’Finance’, null);
           

insert into instuctor 
 								select ID, name, dept_name, 18000 
 								from student
 								where dept_name = ‘Music’ and tot_cred > 144;
           

4.3.3 更新資料

update instructor
set salary = salary * 1.03
where salary > 100000;
update instructor
set salary = case
							when salary <= 100000 then salary * 1.05
              else salary * 1.03
              end;
           

第5節 SQL支援的表間連接配接方式