###### tags: `SQL`

SQL Tutorial
===

### 所有東西都能成為資料庫，不論是白紙、筆記本或是任何東西能夠存取的就可以，但電腦是一個容易存取資料的地方跟方式。

### Database Management Systems (DBMS)
大家都是利用DBMS來維護資料庫，不是直接去做的
- 方便大家使用程式去維護
- 簡易去維護大量資訊
- 安全
- 方便存取備份
- 輸入輸出資料
- 同時性
- 可以用軟體介面互動

### CRUD core four main database
- Create
- Read(Retrieve)
- Update
- Delete

### two main type of database
- Relational Database (RDMBS)
oraganize data in to one or more tables
- each table has column and rows
- a unique key identifies each row

Student Table

| Username | Password | Email |
| -------- | -------- | -------- |
| Apple | wwwwwww | [e-mail] |
| Birs | 1234qweaf | [e-mail] |

- Help users to create and maintain a relational database
- MySQL, Oracle, postgreSQL, mariaDB

- Non-Relational (No-SQL/not just SQL)
- Organize data is anything but a traditional table
- key-value stores
- Documents(Json, XML, etc)
- Graphs
- Flexible Tables
- Help users create and maintain a non-relational database
- mongoDB, dynamoDB, apache cassandra, firebase

### Database Queries
- Query for specific information inside database.
- If the DB become more and more complex it becomes more difficult to get specific pieces of information we want.
- Google serach is a query

Column 可以得到Student id 、name、major
row可以得到一個Student所有的東西
就算名字一樣，兩個Jack跟Biology。Student_id還是primary key可以辨別兩者其實是不同的。
- Surrogate - 跟現實無關，僅代表他在DB裡面的位置(No mapping to real world)
- Natural key - 身分證帳號系統，就是用自然跟自己有關的資料做primary key

此外還有 foreign key
他們可以把primary key 用其他的方式重新統整起來，連到另外一個table，把兩個table 建立起關係。
一個Table可以有超過兩個Foreign key。

- Composite key
需要兩個key 去找到專屬的內容，不同部門可能跟同廠商進貨，但就要區別。

- two foreign key combine one table.

#### SQL BASICS
- SQL is a language for ineracting with Relational Database Management System
(Structured Query Language)
- The implementation very between systems
- Not all RDBMS follow SQL standard to a 'T'
- concept are the same but the implementation may vary

- SQL can separate into 4 type
- Data Query Language (DQL)
- used to query the database for information
- Get information that is already stored there
- Data Definition Language (DDL)
- Used for defining database schemas
- Data Control Language(DCL)
- Used for controlling access to the data in the database
- user & permissions management
- Data Manipulation Language (DML)
- Used for inserting, updating and deleting data from database

#### attribute
##### 基本指令
int - whole numbers
decimal(M,N) -- decimal numbers -exact value
varchar(1) -- string of text of length 1
BLOB - Binary large object , store large data
DATE -'YYYY-MM-DD'
TIMESTAMP --YYYY-MM-DD HH:MM:SS" used for recording

##### 建造一個新資料庫
- 創建資料庫
CREATE DATABASE woof

- 創建一個Table
CREATE TABLE student(
student_id INT PRIMARY KEY, ## 這個當作Primary Key
name VARCHAR(20), ## 當作長度可以填寫
major VARCHAR(20) ## 當作長度可以填寫
> Primary key也可以寫在下面
> PRIMARY KEY(student_ID)

);

- 述說表單內容
Describe student;
> 這會顯示這個表單裡面所有的內容物

- 刪除表單
DROP TABLE student;
> 刪除student整個表單

- 增加column
ALTER TABLE student ADD gpa DECIMAL (3,2);

- 刪除column
ALTER TABLE student DROP COLUMN gpa

- 增加資料內容
INSERT INTO student VALUES(1, 'JACK', 'Biology');

> 這樣JACK 就會被加入DATABASE STUDENT 裡面

若不知道major可以這樣打
INSERT INTO student(student_id, name) VALUES(3, 'Claire');

>這樣打就不會出現錯誤，然後Major的地方就會出現NULL

若裡面已經有出現過資料就不能再鍵入一次，不然會出現錯誤
> Duplicate entry '3' for key 'PRIMARY'

- 看到整體資料
SELECT * FROM student;
> 可以看到student 裡面全部的資訊

- 限制寫法(CONSTRAIN)
CREATE TABLE student (
student_id INT AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
major VARCHAR(20) DEFAULT 'undecided',
PRIMARY KEY (student_id)
);

> NOT NULL 代表者不能為NULL
> UNIQUE 代表裡面的數值不能在TABLE中重複
> DEFAULT 表示裡面的值沒有填就直接替代進去
> AUTO_INCREMENT 可以讓你在自己增加Primary KEY數值

> INSERT INTO student(name, major) VALUES('JACK', 'Biology');

#### UPDATE and DELETE
今天學校的Biology 想改變顯示方式變成Bio可以用下列方式:

----------------
SELECT * FROM Student;

UPDATE Student
SET major ='Bio'
WHERE major = 'Biology';

----------------------------------
若是想透過Primary key或是其他選擇姓名的東西改變也可以這樣做

---------------------------------
SELECT * FROM Student;

UPDATE Student
SET major ='Bio'
WHERE student_id = 4;

---------------------------------

其他方法有把兩個名稱變更成為同一個，下面就是把bio 跟chemistry合併。

-------------------------------------
SELECT * FROM Student;

UPDATE Student
SET major ='Biochemistry'
WHERE major = 'Bio' OR major = 'Chemistry';

------------------------------------------

- DELETE

-----------------------------------------
SELECT * FROM student;

DELETE FROM student
WHERE name = 'TOM' AND major = 'undecided';

----------------------------------------
所有符合TOM 跟Undecided的資料都會被刪除

#### Query

- Select 也可以分成兩行寫

SELECT *
FROM student;

> * 代表拿到所有的資料
> 但若是只想拿特定Column也可以寫另外一個方式

- 選取特定的範圍
- select name, major
FROM student;

> 從 student這個table拿出name 跟 major

- 可以用另類寫法表示他明確來自哪個table
select student.name, student.major
FROM student;

- 也可以將column重新排序一次

- 字幕順向下去排
SELECT student.name, student.major
FROM student
ORDER BY name;

- 反向下去排
SELECT student.name, student.major
FROM student
ORDER BY name DESC;

> 順向就是取代DESC作為ASC

- 就算沒有納進來的東西也可以拿他做因素排序
SELECT student.name, student.major
FROM student
ORDER BY student_id DESC;

- 可以多個當作ORDER 值排序

SELECT student.name, student.major
FROM student
ORDER BY major, student_id;

> 先用major 排序，若有兩個相同的Major就用student_id排

- 只想選擇兩個ROW
SELECT *
FROM student
LIMIT 2;

- 只想顯示 Chemistry的
SELECT *
FROM student
WHERE major = 'CHEMISTRY';

>另外一種寫法
>WHERE major IN ('CHEMISTRY')

> 不想顯示Chemistry的就改下面
> WHERE major <> 'CHEMISTRY';
<> = not equal

>顯示小於3的
>WHERE sutdent_id <=3

## ADVANCED DATABASE SCHEMA

##### 以公司DATABASE為例子
- 創立基本的員工TABLE
CREATE TABLE employee(
emp_id INT PRIMARY KEY,
first_name VARCHAR (40),
last_name VARCHAR(40),
birth_day DATE,
sex VARCHAR(1),
salary INT,
super_id INT,
branch_id INT
);

- Branch TABLE 不同部門
CREATE TABLE branch(
branch_id INT PRIMARY KEY,
branch_name VARCHAR(40),
mgr_id INT,
mgr_start_date DATE,
FOREIGN KEY(mgr_id) REFERENCES employee(emp_id)
ON DELETE SET NULL
);

- FOREIGN KEY 是可以作為其他TABLE 的PRIMARY KEY
- REFERENCE 敘明這東西來自拿個TABLE
- ON DELETE SET NULL 後續說明
- ON DELETE CASCADE

- 若之前沒有設定的Foreign key可以用下列方式處理
- ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch (branch_id)
ON DELETE SET NULL;

ALTER TABLE employee
ADD FOREIGN KEY(super_id)
REFERNENCES employee(emp_id)
ON DELETE SET NULL;

- CLIENT 顧客的聯繫 會跟部門有關
CREATE TABLE client(
client_id INT PRIMARY KEY,
client_name VARCHAR(40),
branch_id INT,
FOREIGN KEY(branch_id) REFERENCES branch(branch_id)
ON DELETE SET NULL
);

- WORKS_WITH 若有兩個PRIMARY KEY且其中都是FOREIGN KEY
- CREATE TABLE works_with(
emp_id INT,
client_id INT,
total_sales INT,
PRIMARY KEY(emp_id, client_id),
FOREIGN KEY(emp_id) REFERENCES employee(emp_id) '
ON DELETE CASCADE
FOREIGN KEY(client_id) REFERENCES client(client_id)
ON DELETE CASCADE
);

- Branch supplier
- CREATE TABLE branch_supplier (
branch_id INT,
supplier_name VARCHAR(40),
supply_type VARCHAR(40),
PRIMARY KEY(branch_id, supplier_name),
FOREIGN KEY(branch_id) FEFERENCES branch(branch_id) ON DELETE CASCADE
);

- 當TABLE剛設置都還是空的，互相關係都還沒建立起來，所以設置內容要有順序

INSERT INTO employee VALUES(100, 'DAVID', 'WALLACE', '1967-11-17','M', '250,000', NULL, NULL)

INSERT INTO VALUES(1, 'Corportte', 100,'2006-02-09');

UPDATE employee
SET branch_id = 1
WHERE emp_id=100

INSERT INTO employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', '110,000', '100', '1')
> 因為前面還沒設定Branch 所以先NULL，現在有了就不NULL了。

- 排序
- 想要把員工依照薪水由高至低排序
SELECT *
FROM employee
ORDER BY salary DESC;

> 這樣薪水就會從高開始到低排序。
- 用性別又用名字排序的話
SELECT *
FROM employee
ORDER BY sex, first_name, last_name;

> 這樣就會先排性別、然後First name 做字母排序，重複會用 Last NAME字母排序
- 找前五名員工
SELECT *
FROM employee
LIMIT 5;
- 找員工全部名稱就好
SELECT first_name, last_name
FROM employee;
- 想從表單裡面拿到First name 跟last name，但又想改掉這兩個表單的列名。
SELECT first_name AS forename, last_name AS surname
FROM employee;
- 想拿到某一個分類的所有資料，可以用DISTINCE
SELECT DISTINCE sex
FROM employee;

> 這樣就會出現

| sex|
| -------- |
| F |
| M |

#### FUNCTIONS

- COUNT
如果想數一個數字，知道裡面所有的項目數量可以使用

- 想數emp_id的總數
SELECT COUNT(emp_id)
FROM employee;

>COUNT(super_id)
8
- 想數1970年後的女性人數
SELECT COUNT(emp_id)
FROM employee
WHERE sex = 'F' AND birth_date > '1971-01-01';

- 平均數計算
- 總體員工的薪資平均數
SELECT AVG(salary)
FROM employee;

- 全部男性員工的薪資平均數
SELECT AVG(salary)
FROM employee
WHERE sex = 'M';

- 總數計算
- 薪資總數
SELECT SUM(salary)
FROM employee;

- 性別總數計算
SELECT COUNT(sex), sex
FROM employee
GROUP BY sex;
>顯示
>
| COUNT(sex) | sex|
| -------- | --|
| 3 | F |
| 6 | M |
- 計算每個員工售出的總價
SELECT SUM(total_sales), emp_id
FROM works_with
GROUP BY emp_id;

- 每個客戶買了多少錢
SELECT SUM(total_sales), clinet_id
FROM works_with
GROUP BY client_id;

- 查找關鍵字
- % = any # characters, _ = one character
- 找到有關LLC單字相關
- SELECT *
FROM client
WHERE client_name LIKE '%LLC';
> 這樣可以找到跟LLC有關係的那些CLIENT
- 查找與Label有關者
SELECT *
FROM branch_supplier
WHERE supplier_name LIKE '% Lable%'
- 查找與十月生日者
SELECT *
FROM employee
WHERE birth_date LIKE '____-10%'

> 這樣就會print出十月出生者，前面四格就是可以填任意年分。
- 找到名稱內有school
SELECT *
FROM client
WHERE client_name LIKE '%school';

#### UNION
- FIND a list of employee and branch names
SELECT first_name
FROM employee
UNION
SELECT branch_name
FROM branch;

> 但是兩者要結合，必須要有相同的資料型態與格子數量，不能上方SELECT兩個下面只看一個，這樣不行。

### JOIN
總共分成四種，一般JOIN、LEFT JOIN 、RIGHT JOIN、FULL JOIN
- COMBINE TWO TABLE TOGETHER
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
JOIN branch
ON employee.emp_id = branch.mgr_id

> 選最上面那三個選項，把Branch裡面選到的東西加到employee裡面。最後是employee的id與branch id相同者才會被顯示出來

| emp_id |first_name|branch_name |
| -------- | -------- | -------- |
| 100 | David | Corporate |
|102 |MICHAEL |Scranton |
|106 |JOSH |Stamford |

- LEFT JOIN V.S. RIGHT JOIN
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
LEFT JOIN branch
ON employee.emp_id = branch.mgr_id

| emp_id |first_name|branch_name |
| -------- | -------- | -------- |
| 100 | David | Corporate |
|102 |MICHAEL |Scranton |
|106 |JOSH |Stamford |
|101 |JAN |NULL |
|103 |ANGELA |NULL |
|104 |KELLY |NULL |
|105 |HUDSON |NULL |

> 就算不在 branch 裡面的，但只要在employee Tabl裡面(視作左邊)都會顯示，但最右邊就會出現NULL。

##### 若使用RIGHT
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
RIGHT JOIN branch
ON employee.emp_id = branch.mgr_id
| emp_id |first_name|branch_name |
| -------- | -------- | -------- |
| 100 | David | Corporate |
|102 |MICHAEL |Scranton |
|106 |JOSH |Stamford |
|NULL |NULL |BUFFALO |

##### FULL JOIN
MYSQL無法做出FULL JOIN，但其實這個是可以做的，就是把左右的TABLE共同結合起來。

### NESTED Queries

-- find names of all employ who sold over 30K to single client

SELECT employee.first_name, employee.last_name
FROM employee
WHERE employee.emp_id IN (

SELECT works_with.emp_id
FROM works_with
WHERE works_with.total_sales>30000

);

> this will print employee who is get from bigger than 30k, and get their information and name.

- FIND all Clients handle by Michael branch

SELECT Client.client_name
FROM Client
WHERE Client.branch_id = (
SELECT branch.branch_id
FROM branch
WHERE branch.mgr_id = 102;
LIMIT 1

);

> LIMIT 1 make sure only 1 branch he management

#### ON DELETE

- DELETE

CREATE TABLE branch(
branch_id INT PRIMARY KEY,
branch_name VARCHAR(40),
mgr_id INT,
mgr_start_date DATE,
FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

DELETE FROM employee
WHERE emp_id =102;

SELECT * FROM branch;

> 當初建置的時候就有寫，確定就set NULL ，這樣就可以在其中一個表單刪除的時候可以連動。

- CASCADE
CREATE TABLE branch_supplier(
branch_id INT,
supplier_name VARCHAR(40),
supply_type VARCHAR(40),
PRIMARY KEY(branch_id, supplier_name)
FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);

DELETE FROM branch
WHERE branch =2;

Select *
FROM branch_supplier;

> on delete cascade 這個會刪除所有跟有2有關的。
> Cascade是會把表整個刪掉
> 但on delete set null 只會把有關的位置變成NULL

#### Trigger

>> 創建新的TABLE，專門放建置的訊息

CREATE TABLE trigger_test(
message VARCHAR(100)
);

>> .
DELIMITER $$
CREATE
TRIGGER my_trigger BEFORE INSERT
ON employee
FOR EACH ROW BEGN
INSERT INTO trigger_test VALUES('added new employee');
END$$
DELIMITER ;

>> Delimiter 從$$ 改回;
>> 因為)後面有;怕SQL認為trigger寫完了，但其實還沒，所以先改成$$，整個處理完之後再弄回來。
>> 每次增加新的人員的時候，都會顯示added new employee

- 第二種trigger

DELIMITER $$
CREATE
TRIGGER my_trigger BEFORE INSERT
ON employee
FOR EACH ROW BEGIN
IF NEW.sex = 'M' THEN
INSERT INTO trigger_test Values('Added male employee');
ELSEIF NEW.sex = 'F' THEN
INSERT INTO trigger_test VALUES('Added female');
ELSE
INSERT INTO trigger_test VALUES ('added other employee');
END IF;
DELIMITER ;

SELECT * FROM trigger_test;

>> 你可以在after之前trigger 或是before 都可以調整的

#### Entity Relationship DIAGRAM (ER Diagram)
- ENTITY
An object we want to model & store information about

- attibute
Specific pieces of information about an entity

- Primary Key
An attribute that uniquely identify an entry in the database table

- Composite attribute
- An attribute that can be brokent up into sub-attribute
- First name
- Last name

- Multi-valued attribut
- An attribute that can have more than one value
- like club

- Derived attribute
- An attribute that can be derived from the other attributes
- if GPA >3.5 is an honor, honor is the attribute derived from GPA.

- Multiple Entity
- YOU cna define more than one entity in the diagram

- Relationship
- Define a relationship between two entities

- Total Participation
- All members must participate in the relationship
- Partial relationship
- not all student need to take a class
- Total relationship
- ALl the class need at least one student to take.

- Relationship attribute
- grade
- grade is a relationship attribute between student and the class

- Relationship Cardinality
The number of instances of an entity from a relation that can be associated with the relation.

1 student to 1 class
a student take N class
N sutdent take M class

- Weak entity
- An entity that cannot be uniquely identified by its attributes alone
- a exam can't exist without a class

- Identifying relationship
- A relationship that serves to uniquly identify the weak entity
- like exam_id

#### ER diagram to schema

- 1. mapping of regular entity types


- 2. mapping of weak entity types

- 3. mapping of binary 1:1 relationship types

- 4. mapping of binary 1:N relationship types

- 5. mapping of binary M:N relationship types