SQL資料定義
基本類型
- char(n)：固定長度的字元串，使用者指定長度。
- varchar(n)：可變長度的字元串，使用者指定最大長度n。等價于全稱character varying。
- Int：整數類型。
- smallInt：小證書類型。
- numeric(p, d)：定點數，精度由使用者指定。這個數有p位數字（加上一個符号位）。其中d位數字在小數點右邊。
- real, double precision：浮點數與雙精度浮點數。
- float(n)：精度至少為n位的浮點數。
基本模式定義
使用 create table 指令定義SQL關系。
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12, 2)m
primary key (dept_name));
上述代碼建立了一個department關系，具有dept_name,building,budget三個屬性，主碼為dept_name。
完整性限制：
- primary key(a1,a2,a3,…,an)：該聲明表示屬性a1,a2,a3,…,an構成關系的主碼。主碼屬性必須非空且唯一。
- foreign key(a1,a2,a3,…,an) references r1：聲明表示關系中任意元組在屬性a1,a2,a3,…,an上的取值必須對應于關系r1中某元組在主碼屬性上的取值。
- not null：一個屬性上的not null限制表明在該屬性上不允許空值。
注：SQL禁止破壞完整性限制的任何資料庫更新。
Insert指令可将資料加載到關系中。例如
Insert into instructor
values(10211, 'Smith', 'Biology', 66000);
delete指令可從關系中删除元組。
delete from student
注：上述指令将會删除student中所有元組。
drop table指令可以在資料庫中删除一個關系
drop table student
注：上述指令将會從資料庫中删除student表。
使用alter table指令可以為已有關系添加屬性。新屬性在關系中所有元組上的屬性的值将為null。
alter table r add A D
r是現有關系的名字，A是待添加屬性的名字，D是将添加屬性的域。而通過
alter table r drop A
則可以從關系中删除屬性。
SQL查詢的基本結構
單關系查詢
select name
from instructor
上面的代碼即為從instructor中查詢所有的name，有時候我們想要删掉重複，可加入關鍵詞distinct，例如
select distinct name
from instructor
select子句還可帶有算術表達式，例如
select ID, name, dept_name, salary*1.1
from instructor
上面這段代碼會傳回一個與insructor一樣的關系，隻是salary屬性是原來的1.1倍，注意原關系并不發生改變。
where子句允許我們隻需選出那些在from子句的結果關系中滿足特定謂詞的元組。例如
select name
from instructor
where dept_name = 'Comp.Sci' and salary > 70000;
**上面這段代碼意為找出所有在Computer Science系并且工資超過70000d的教師的名字。**
多關系查詢
select name, instructor. dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;
上述代碼的意圖是“找出所有教師的姓名，以及他們所在系的名稱和系所在建築的名稱。”
注：1、dept_name屬性既出現在instructor關系中，也出現在department中，關系名被用作字首來說明我們是用的是哪個屬性。2、盡管子句是以select,from,where的次序寫出，但是實際運算的順序是from,where,select。即先求出from選中的關系的笛卡爾積，這個笛卡爾積有大量無效的元組，然後where子句中的位于可用來限制笛卡爾積所建立的組合，最後輸出select子句中指定的屬性。
自然連接配接(natural join)
自然連接配接運算作用于兩個關系，并産生一個關系作為結果。自然連接配接隻考慮那些在兩個關系模式中都出現的屬性上取值相同的元組對。是以對于查詢“大學中所有講授課程的教師，找出他們的姓名以及所講述的所有課程辨別”，我們可以這麼寫
select name, course_id
from instructor natural join teaches
為了避免不必要的相等屬性帶來的危險，我們還可以使用自然連接配接的另一種構造形式
select name, title
from (instructor natural join teaches) join course using(course_id);
注：join…using…運算中需要給定一個屬性名清單，其兩個輸入都必須具有指定名稱的屬性。（using用來指定需要相同的是哪個屬性）
附加的基本運算
更名運算
我們并不總能直接使用屬性名。原因在于首先，from子句中的兩個關系可能存在同名的屬性。其次，如果在select子句中使用了算術表達式，那麼屬性就沒有名字。是以，我們可以使用old-name as new-name的文法來改變結果中屬性的名字
select name as instructor_name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;
重命名還可以把一個長的關系名替換成短的，這樣在查詢的其他地方使用時就會友善。例如
select T.name , S.course_id
from instructor as T, teaches as S
where T.ID = S.ID;
重命名的另一個重要原因是為了适用于需要比較同一個關系中元組的情況。為此我們需要把一個關系與他自身進行笛卡爾積運算，如果不重命名就不可能把一個元組與另一個區分開來。例如我們要查詢“滿足條件的所有教師的名字，他們的工資至少比Biology系的某一個教師的工資要高”
select distinct T.name
from instructor as T, instructor as S
where T.ID = S.ID;
T、S準确的說是被聲明為instructor的别名。在SQL标準中被稱作相關名稱(correlation name)，但也經常被稱為表别名(table alias)，或相關變量(correlation variable)，或者 元組變量(tuple variable)
字元串運算
在字元串上可以使用like操作符實作模式比對：
- 百分号(%)：比對任意子串。
- 下劃線(_)：比對任意一個字元。
注：模式比對是大小寫敏感的。
- 'Intro%'比對任何以Intro打頭的字元串。
- '%Comp%'比對任何包含’Comp’的字元串。
- '___'比對隻含三個字元的字元串。
- '___%'比對至少含三個字元的字元串。
查詢寫法如下：
select dept_name
from department
where building like '%Waston%';
SQL在like比較運算中使用escape關鍵詞來定義轉義字元。例如
like 'ab\%cd%' escape '\'
用來比對所有以’ab%cd’開頭的字元串。
注：SQL同樣也支援not like文法。
排列元組的顯示次序
order by子句可以讓查詢結果按排列順序顯示，例如
select name
from instructor
where dept_name = 'Physics'
order by name;
該段代碼可以按字母序列出Physics系的所有教師名字。
order by語句預設使用升序，要使用降序可以用desc來表示，或者使用asc來明确是升序。假設我們想按salary的降序列出整個instructor關系，如果有工資相同的，則按姓名升序排列，則表示如下：
select *
from instructor
order by salary desc, name asc;
where子句謂語
SQL提供betwenn比較運算符來說明一個值是小于或等于某個值，同時大于或等于某個值。如果我們想找出工資在90000美元和100000美元之間的教師的名字，可以這樣寫：
select name
from instructor
where salary between 90000 and 100000;
類似的也可以使用not between文法
集合運算
并運算
為了找出在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
）;
注：union運算自動去重。若想保留重複則用union all語句。
交運算
為了找出在2009年秋季和2010年春季同時開課的所有課程的集合。代碼如下：
（select course_id
from section
where semester = 'Fall' and year = 2009
）
Intersect
（select course_id
from section
where semester = 'Spring' and year = 2010
）;
同樣的若想保留重複，可以使用Intersect all語句。
差語句
為了找出在2009年秋季開課但沒有在2010年春季開課的所有課程，代碼如下：
（select course_id
from section
where semester = 'Fall' and year = 2009
）
except
（select course_id
from section
where semester = 'Spring' and year = 2010
）;
注：使用except all可以保留重複。
聚集函數
- 平均值： avg
- 最小值：min
- 最大值：max
- 總和：sum
- 計數：count
基本聚集
找出Computer Science系老師的平均工資，代碼如下：
select avg(salary)
from instructor
where dept_name = 'Comp.Sci';
有些時候計算聚集函數是我們想去掉重複元組，可以使用distinct關鍵詞。，若我們想找出2010年春季講授一門課程的老師總數，代碼如下：
select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2010;
分組聚集
group by子句中給出的屬性是用來構造分組的，在group by子句中所有屬性上取值相同的元組将被分在一個組中。例如
select dept_name ,avg(salary), as avg_salary
from instructor
group by dept_name;
以上代碼可以顯示所有部門的平均工資
having子句
having子句類似where，但隻針對group by形成的分組，having字句中可以使用聚集函數。例如
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;
嵌套子查詢
集合成員資格
連接配接詞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
)；
按照SQL實際執行的邏輯順序來了解，where語句是傳回元組（即選擇表中的行），而where語句的判斷該取哪些行的标準，則是判斷這些行（元組）中的屬性是不是能使where語句裡的條件為真。那麼在這段代碼的where語句中，我們是要選出所有的semester屬性為“Fall”，year屬性為2009，并且course_id還要出現在所有在2010年春天開過課的關系中。
集合的比較
查詢滿足條件的所有老師的姓名，他們的工資至少比Biology系某一個教師的工資要高，代碼如下：
select name
from instructor
where salary > some (select salary
from instructor
where dept_name = 'Biology'
);
>some在SQL中表示“至少比某一個大”，當然也有<some,<=some,>=some的用法。同時還有>all的用法，表示“比所有的都大”。
空關系測試
通過exist關鍵詞可以查詢是否為空，例如
select course_id
from section as S
where semester = 'Fall' and year = 2009 and exists(select *
from section as T
where semester = 'Spring' and year = 2010 and S.course_id = T.course_id
);
個人了解這段代碼的意思是從2009年秋天開課的課程中查找是否2010年春天開設的同樣的課程。
重複元素存在性測試
select T.course_id
from course as T
where unique(select R.course_id
from section as R
where T.course_id = R.course_id and
R.year = 2009);
上述代碼是找出所有在2009年最多開設過一次的課程，查詢結果中沒有重複的元組，unique結構将會傳回true，反之傳回false。
from子句中的子查詢
SQL允許在from子句中使用子查詢表達式。因為在任何select-from-where 結構中傳回的結果都是關系，因而可以被插入到另一個select-from-where中任何關系可以出現的位置。例如查詢找出系平均工資超過42000美元的那些系中教師的平均工資
select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name)
as dept_avg(dept_name, avg_salary)
where avg_salary > 42000;
上述代碼中子查詢的結果關系被命名為dept_avg，其屬性名是dept_name和avg_salary。
資料庫的修改
删除
使用下列語句表示删除
delete from r
where P;
delete語句将删除所有滿足p條件的元組。若不加where語句則是删除關系r中的所有元組。
插入
要往關系中插入資料，我們可以指定待插入的元組，或者寫一條查詢語句來生成待插入的元組集合。例如：
Insert into course
values('CS-437', 'Database Systems', 'Comp.Sci', 4)
Insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and tot_cred > 144;
在執行插入之前先執行完select語句是非常重要的。如果在執行select 語句的同時執行插入語句，且在student上沒有主碼限制的話，就會無限插入。
注：若待插入元組中隻給出了模式中的部分屬性的值，那麼其餘屬性的值将為null。
更新
我們使用update語句來更新元組的值。例如
update instructor
set salary = salary *1.05
若隻給工資低于平均數的教師漲工資，則
update instructor
set salary = salry 8 1.03
where salary < (select avg(salary)
from instructor);
SQL還提供case結構，可以利用它在一條update語句中執行多種更新，避免更新次序引發的問題。
update instructor
set salary = case
when salry <= 100000 then salary * 1.05
else salary * 1.03
end
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)