資料庫系統概念筆記——第三章：SQL

文章目錄

• • 第三章：SQL
  • • 3.2 SQL資料定義
    • • 3.2.1 基本類型
      • 3.2.2 基本模式定義
    • 3.3 SQL查詢的基本結構
    • • 3.3.1 單關系查詢
      • 3.3.2 多關系查詢
      • 3.3.3 自然連接配接
    • 3.4 附加的基本運算
    • • 3.4.1 更名運算
      • 3.4.2 字元串運算
      • 3.4.4 排列元組的顯示次序
      • 3.4.5 where子句謂詞
    • 3.5 集合運算
    • • 3.5.1 并運算
      • 3.5.2 交，差運算
    • 3.6 空值
    • 3.7 聚集函數
    • • 3.7.1 基本聚集
      • 3.7.2 分組聚集
      • 3.7.3 having子句
      • 3.7.4 對空值和布爾值的聚集
    • 3.8 嵌套子查詢
    • 3.8.1 集合成員資格
    • 3.8.2 集合的比較
    • 3.8.3 空關系測試
    • 3.8.6 with子句
    • 3.9 資料庫的修改
    • • 3.9.1 删除
      • 3.9.2 插入
      • 3.9.3 更新

第三章：SQL

3.2 SQL資料定義

3.2.1 基本類型

SQL标準中支援多種固有類型

• char(n)：固定長度的字元串，使用者指定長度n
• varchar(n)：可變長度的字元串，使用者指定最大長度n（注意在Oracle資料庫中寫作varchar2(n)）
• int：整數類型
• smallint：小整數類型
• numeric(p,d)：定點數，這個數有p位數字（加上符号位），其中d位在小數點右邊
• real，double precision：浮點數與雙精度浮點數
• float(n)：精度至少為n位的浮點數

注意點：

1. char類型存儲固定長度的字元串，如果存入字元串的長度不夠，則會以空格補齊
2. varchar類型不會補充空格，根據使用者填入的字元串長度儲存
3. 定點數numeric(3,1)可以精确存儲44.5，但卻不能精确存儲444.5或0.32這樣的數

3.2.2 基本模式定義

使用create table指令定義SQL關系

課本中的大學資料庫的部分建表sql語句（MySQL）

create table classroom
	(building		varchar(15),
	 room_number		varchar(7),
	 capacity		numeric(4,0),
	 primary key (building, room_number)
	);
create table department
	(dept_name		varchar(20), 
	 building		varchar(15), 
	 budget		        numeric(12,2) check (budget > 0),
	 primary key (dept_name)
	);
create table course
	(course_id		varchar(8), 
	 title			varchar(50), 
	 dept_name		varchar(20),
	 credits		numeric(2,0) check (credits > 0),
	 primary key (course_id),
	 foreign key (dept_name) references department(dept_name)
		on delete set null
	);
create table instructor
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 salary			numeric(8,2) check (salary > 29000),
	 primary key (ID),
	 foreign key (dept_name) references department(dept_name)
		on delete set null
	);
           

• primary key(A1,A2,A3…)：表明屬性A1,A2,A3…構成表的主碼，主碼的屬性必須非空且唯一
• foreign key(A1,A2,A3…) references ：聲明表中這些屬性的取值必須和另一個關系上某些屬性的取值對應
• not null：限制不能為空
• check：自定義限制，括号中填限制的内容

SQL禁止破壞完整性限制的任何資料庫更新

使用alert table可以為已有關系增加屬性

alert table r add A D
           

A是待添加屬性的名字，D是屬性的資料類型

如果表中原本就有資料，則原有元組在新添加的屬性上的值全部為null

補充

1.使用constraint關鍵字給限制命名

2.表空間（tablespace）

表空間是資料庫的邏輯劃分，一個表空間隻能屬于一個資料庫，以一個資料庫可以擁有多個表空間

表空間分為系統表空間和使用者表空間

一個表空間可以包含多張表，一張表隻能屬于一個表空間

3.表的屬主Owner

每個資料庫對象都有自己的屬主，表也有自己的屬主。資料庫對象預設屬主是建立該對象的使用者

屬主擁有表的所有權限

屬主為user1

4.修改表的定義alter

格式：

例：

5.删除表中的屬性或限制

删除表中的限制

3.3 SQL查詢的基本結構

3.3.1 單關系查詢

單關系查詢比較簡單，舉幾個簡單的例子即可

1.找出所有教師的名字

select name 
from instructor;
           

2.找出所有教師的系名

select dept_name 
from instructor;
           

3.使用distinct關鍵字查找教師所在的系名

select distinct dept_name 
from instructor;
           

4.在select中使用算數表達式

select name,salary*1.1 
from instructor;
           

5.找出所有在Comp. Sci.系且工資超過70000美元的老師的姓名

select name 
from instructor 
where dept_name = 'Comp. Sci.' and salary > 70000;
           

3.3.2 多關系查詢

1.找出所有教師的姓名，所在系，和系所在的建築

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

3.3.3 自然連接配接

1.找出教師的姓名和他們所教的課程辨別

select name,course_id 
from instructor natural join teaches;
           

2.找出教師的姓名和他們所教的課程名稱（三張表自然連接配接）

select name,title 
from instructor natural join teaches natural join course;
           

3.4 附加的基本運算

3.4.1 更名運算

更名運算使用as關鍵字

例

select T.name,S.course_id 
from instructor as T,teaches as S 
where T.ID = S.ID;
           

3.4.2 字元串運算

1.字元串的比對

• %：比對任意字元
• _：比對一個字元

找出所在建築名稱中包含a的所有系名

select dept_name 
from department 
where building like '%a%';
           

3.4.4 排列元組的顯示次序

1.order by，asc，desc

select name,salary 
from instructor 
order by name asc,salary desc;
           

3.4.5 where子句謂詞

1.使用between and簡化查詢

select name 
from instructor 
where salary between 90000 and 100000;
           

3.5 集合運算

3.5.1 并運算

找出在2009年秋季開課，或者在2010年春季開課的課程（使用并運算）

(select course_id 
 from section 
 where semester = 'Fall' and year = 2009) 
 union 
 (select course_id 
 from section 
 where semester = 'Spring' and year = 2010);
           

3.5.2 交，差運算

MySQL中不支援交和差運算運算，使用intersect和except會報錯

3.6 空值

• 如果算數表達式的任意輸入為空，則該算數表達式(+ , - , * , /等)的結果也為空
• 1 < null的結果為unknown
• and：true and unknown的結果為unknown，false and unknown的結果為false，unknown and unknown的結果是unknown
• or：true or unknown的結果是true，false or unknown的結果是unknown，unknown or unknown的結果是unknown
• not：not unknown的結果是unknown

select name 
from instructor 
where salary is null;
           

3.7 聚集函數

3.7.1 基本聚集

1.找出曆史系教師的平均工資

select avg(salary) 
from instructor 
where dept_name = 'History';
           

2.找出在2010年春季教授一門課程的教師總數

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

3.7.2 分組聚集

1.找出每個系的平均工資

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

3.7.3 having子句

1.找出每個系的平均工資且平均工資大于4000的系名

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

3.7.4 對空值和布爾值的聚集

除了count(*)以外的所有聚集函數均忽略輸入集合中的空值

3.8 嵌套子查詢

3.8.1 集合成員資格

• 連接配接詞in可以用于測試元組是否是集合中的成員
• 連接配接詞not in用于測試元組中是否不是集合中的成員

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

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

例：找出在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);
           

3.8.2 集合的比較

1.>some表示至少比某一個大

例：找出至少比Biology系的某一個教師工資要高的教師姓名

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

2.SQL也允許使用<some,<=some,>=some,=some,<>some等的使用

3.與some類似，<all,<=all,>=all,=all,<>all的功能類似

找出平均工資大于所有系平均工資的系

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

3.8.3 空關系測試

使用exists，當子查詢非空時傳回true，子查詢為空時傳回false

例：找出選修了Biology系開設的所有課程的學生

select S.ID,S.name 
from student as S 
where not exists(
(select course_id 
from course 
where dept_name='Biology') 
except 
(select T.course_id 
from takes as T 
where S.ID = T.ID));
           

exists的執行邏輯和原理要求完全掌握

3.8.6 with子句

使用with子句可以建立一張臨時表

例：找出具有最大運算的系

with max_budget(value) as 
(select max(budget) 
from department) 
select budget 
from department 
where department.budget = max_budget.value;
           

3.9 資料庫的修改

3.9.1 删除

删除語句的基本文法格式

delete 
from 表名 
where 條件
           

沒有where時預設删除整張表

3.9.2 插入

1.最基本的插入語句

insert into course 
values('CS-437','Database Systems','Comp. Sci. ',4);
           

2.可以在insert語句中指定要插入的屬性

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

3.9.3 更新

1.一般的update語句

update 表名 
set xxx 
where xxx;
           

2.更新的update語句

update table_name
set xxx = case
	when pred1 then result1
	when pred2 then result2
	...
	else result0
end;
           

3.例：給工資超過100000美元的教師漲3%的工資，其餘教師漲5%

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