SQL Server指令彙整與概念整理

2020-09-25 置頂文章

615
0
Database
2020-10-03

指令的彙整：

字串的連結：

1.數字轉字串：SELECT CONVERT(varchar, 1234567890.12)
例如：SELECT CONVERT(varchar, EMPLOYEE_ID) +'_'+ FIRST_NAME as Employees
FROM Employees;
[註]EMPLOYEE_ID為數字欄位

2.日期轉字串：SELECT CONVERT(varchar, getdate(), 1)
例如：SELECT CONVERT(varchar, getdate(), 112) +' ~ '+CONVERT(varchar,HIRE_DATE)
FROM Employees;
[註]1, 112為不同的日期格式

在SQL Server世界中，作為事物/ 物件的資料要如何儲存？

我們通常都會挑選一個特徵作為事物的主鍵(Primary Key) -- 來表示事物的唯一性 ，基本上，是一個沒有實質意義的特徵，但它起了很明顯地標示作用。
例如：身分證字號，嚴格來說並不是一個人本身的特徵，但為了區隔每個人的身分，我們喜歡把它當作一個人的特徵。
所以，我們在找任一個事物的主鍵時，也要以這個觀念來設。

使用資料庫有一個很重要的觀念，就是要去設計一套規則來儲存事物/ 物件，還有思考物件與物件之間的關係，因此我們又稱資料庫，叫作關聯式資料庫。
在資料庫的世界，一個物件就使用一個資料表來表示，所以兩個物件之間的關係，就意味著兩個資料表的關係。我們通常使用外來鍵(Foreign Key) -- 表示此欄位來自(From)另一個資料表，來表示兩者之間的關係。
例如：有一個員工資料表、有一個部門資料表，那要如何使用外來鍵建立彼此關係？以此例，我們可以使用部門編號當作外來鍵，以字面義來說，它就是員工資料表的外來鍵，用來與部門資料表建立起關係。

使用資料表來表現一對多的關係

以邏輯來說，我們要將外來鍵設在多數的一方，例如一個部門就對應到多個員工，多個員工對應到一個部門。(外來鍵來自另一個表的主鍵)
[補充]以此例，我們要先建立部門資料表(主鍵表)，因為員工資料表(外鍵表)有一欄資料來自部門資料表。
[註]含有外鍵欄位的表為外鍵表

--新建資料表

create table dept(--主鍵資料表
	dept_id int primary key,
	dept_name nvarchar(100) not null,
	dept_address nvarchar(100)
)

create table emp(--外鍵資料表
	emp_id int constraint pk_emp_id_haha primary key,
	emp_name nvarchar(20) not null,
	emp_sex nchar(1) not null,
	dept_id int constraint fk_dept_id_heihei foreign key references dept(dept_id)
)

外鍵約束從語法上來看，保證了本物件所關聯的其他物件一定是存在的，一個員工資料列中的部門編號一定會實際參考到部門資料表上相同的部門編號(代表此部門是實質存在的)。

--Check約束：保證物件屬性的取值在合法的範圍之內
--範例：

create table student(
	stu_id int primary key,
	stu_sal int check (stu_sal >= 1000 and stu_sal <= 8000)
)

insert into student values(1,1000)--薪水設值在範圍內
insert into student values(2,8000)--

--default約束：保證物件屬性一定會有一個值。

create table student(
	stu_id int primary key,
	stu_sal int check (stu_sal >= 1000 and stu_sal <= 8000),
	stu_sex nchar(1) default ('男')--設定default值
)

insert into student (stu_id, stu_sal) values (1, 1000)--使用default值
insert into student (stu_id, stu_sal) values (2, 8000)--
insert into student (stu_id,stu_sal) values (3, 5000)--

--唯一(unique)約束：保證了物件屬性的取值不允許重複、但允許其中一個欄位為空。

create table student(
	stu_id int primary key,
	stu_sal int check (stu_sal >= 1000 and stu_sal <= 8000),
	stu_sex nchar(1) default ('男'),
	stu_name nvarchar(200) unique
)

insert into student values (1, 1000,'女','Lucy')
insert into student (stu_id, stu_sal,stu_name) values (2, 8000,'Yuta')
insert into student values (3, 5000,'女','Pei')
insert into student values (4, 5000,'女',null)--唯一鍵允許為空

--如何設定主鍵(Primary Key)、唯一鍵(Unique)？
主鍵要設在沒有實際意義的屬性上，通常是編號；唯一鍵要設在屬性為不能重複的。

create table student(
	stu_id int primary key,
	stu_name nvarchar(50) unique not null,--允許多資料列設成唯一鍵
	stu_email nvarchar(50) unique not null,--
	stu_address nvarchar(50)
)

指令：drop table student;
代表刪除student資料表。

使用資料表來表現多對多的關係(必須透過單獨的另一個資料表來表示)：

例如：一個老師可以教導多個班級、一個班級可以有多個授課老師(這種雙向的關係)。
多對多關係必須透過另一個資料表(relationship)來表現班級(class)與老師(teacher)的關係。

圖一

圖二

圖三

--建立圖三realationship(class_teacher_mapping)資料表指令：
create table relationship(
	class_id int constraint FK_class_id foreign key references class(class_id),
	teacher_id int constraint FK_teacher_id foreign key references teacher(teacher_id),
	subject_name nvarchar(20) not null,
    constraint PK_classID_teacherID_subName primary key(class_id,teacher_id,subject_name)--(方法1:建表同時加組合主鍵)
)


insert into relationship values (2,1002,'SSH');
insert into relationship values (1,1001,'C');
insert into relationship values (1,1001,'JAVA');
insert into relationship values (2,1001,'C');
insert into relationship values (3,1001,'SQL');
insert into relationship values (4,1002,'Oracle');


--設置複合主鍵(方法2:先建表後才加組合主鍵)

ALTER TABLE relationship ADD PRIMARY KEY (class_id,teacher_id,subject_name);

[重點]

所有資料表都必須有主鍵，class_table的主鍵：class_id/ teacher_table的主鍵：teacher_id；
而在relationship_table，是用來表示班級與老師之間多對多的關係，因此在這裡要設置外來鍵(來自另兩個資料表的主鍵：class_id, teacher_id)，在這裡因為預設一個老師能夠教導一個班級多門課程，所以我們必須複合class_id, teacher_id, subject_name這三個欄為主鍵，代表這三欄組合在一起，但不能有重複的資料出現，如圖三。
外來鍵不一定來自另一個資料表的主鍵，也可能來自同一個資料表的主鍵。
刪除問題：先刪外鍵表還是主鍵表？答案是先刪外鍵表，再刪主鍵表；如果反過來，則會導致外鍵表中的資料參考失敗。

排除運算時遇到null的問題，例如：我們要計算年薪+獎金的運算，但在資料表中獎金欄位有null值，該如何解決？
```
select ename, sal * 12 + isnull(comm, 0) "ANNUAL(COMM)" from emp;


--isnull(comm, 0)代表若comm為null，就return 0；否則就return comm的值。
--ename姓名欄位
--sal薪資欄位
--comm獎金欄位
```
參考資料來源：《郝斌數據庫Sql Server 2005》自學視頻
----------------------------------------------------------------------------
郝斌的題外話補充，我覺得講得很好，所以補充在這裡。
如何學習一個知識？
1. 為什麼需要***
2. 什麼是***
3. 怎麼使用***(最簡單的)
4. 使用***注意的問題
5. ***的應用領域
6. ***的優缺點
在設計程式時，若有三處編寫大量且重複的程式碼，就必須想辦法簡化，用一個方法去實現。

如有敘述錯誤，還請不吝嗇留言指教，thanks!

SQL Server

回首頁