---
# System prepended metadata

title: 'tags: SQL'
tags: [工作]

---

###### 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     | A@gmail.com     |
| Birs     | 1234qweaf   | B@gmail.com     |

- 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

![](https://i.imgur.com/8NKeLV8.png)
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
![](https://i.imgur.com/8DK4YvJ.jpg)

- 1. mapping of regular entity types
![](https://i.imgur.com/tIvJu3b.png)

![](https://i.imgur.com/8SwGqqZ.png)

- 2. mapping of weak entity types
 ![](https://i.imgur.com/4935CqM.jpg)
 
- 3. mapping of binary 1:1 relationship types
![](https://i.imgur.com/dq8PIhV.jpg)

- 4. mapping of binary 1:N relationship types
 ![](https://i.imgur.com/18pvQFN.jpg)

- 5. mapping of binary M:N relationship types
![](https://i.imgur.com/h09cwKb.jpg)

![](https://i.imgur.com/0BlTHD1.png)