關聯式資料庫設計SQL Server和MySQL資料庫-從零開始親手學習資料庫系統

關聯式資料庫設計使用SQL Server和MySQL資料庫-親手實作SQL SERVER的T-SQL和MySQL資料庫,親手實作資料庫交易與鎖定,實作大型資料庫系統交易設計

SQL Server2017 資料庫安裝

安裝下載SQL Server Management 管理工具
https://youtu.be/Sw1A8NtG-2g

我會學些什麼呢

  • SQL Server 2017資料庫設計與實作
  • MySQL資料庫設計與實作
  • 實體關係模型ER Model設計分析

要求

  • 高中數學

說明

  • 循序漸進,從關聯式資料庫設計SQL Server和MySQL資料庫-從零開始親手學習資料庫系統並實作
  • 本課程有關聯式資料庫設計SQL Server和MySQL資料庫
  • 資料庫設計工程師及資料庫工程師,徹底了解資料庫系統
  • 18個小時…..71講座…讓你徹底了解資料庫系統
  • 了解機器學習,深度學習內涵,加強人工智慧專業知識.
  • 成為大數據分析師,深入挖掘商業智慧,與資料大數據工程
  • 這是華人界的知名科學家吳佳諺所推出的線上課程。
  • 所有問題一定可以得到解答
  • 從觀念入門,親手打造手寫辨識,讓你全盤了解資料科學,資料庫商管應用。
  • 初學者到進階使用者,對資料科學有興趣的人,商管財經人士,文法商人員,一般工程師,理工醫,電子電機與資料工程師、資料科學家。
  • 讓自己鍍金,提升自我價值。
  • 關聯式資料庫設計SQL Server和MySQL資料庫為大數據資料科學基礎課程

課程網址[資策會]

課程網址

 

SQL Server2017資料庫安裝

1.在Windows 10安裝SQL Server資料庫

安裝SQLServer2017-SSEI-Dev

2.安裝下載SQL Server Management 管 具
SSMS-Setup-CHT

3.安裝SQL Server Management管 具

關聯式資料庫

  • ‧1.新增資料庫使用者
  • 1-1.在Windows 10新增系統管理者Justinwu
  • 1-2.新增資料庫使用者Justinwu
  • ‧2.資料庫授權使用者
  • ‧3.SQL Server組態管理
  • ‧4.建立資料庫與資料表SQL

SQL資料定義語言DDL

  • create database company
  • go
  • use company
  • create table 員工資料表(
  • 員工編號 int not null,
  • 姓名 char(20) not null ,
  • 性別 char(8) not null,
  • 地址 char(80),
  • 薪水 money,
  • 部門編號 int ,
  • primary key(員工編號)
  • )
  • create table 部門資料表(
  • 部門編號 int not null,
  • 部門 char(20) not null ,
  • 開始管理日期 datetime,
  • 薪水 money,
  • 管理者 int not null,
  • primary key(部門編號)
  • )
  • create table 部門位址資料表(
  • 部門編號 int not null,
  • 位址 char(80) not null ,
  • primary key(部門編號,位址)
  • )
  • create table 計劃資料表(
  • 計劃編號 int not null,
  • 計劃名稱 char(20) not null ,
  • 計劃地點 char(80) ,
  • 部門編號 int not null,
  • primary key(計劃編號),
  • foreign key(部門編號) references 部門資料表(部門編號)
  • )
  • create table 參與資料表(
  • 員工編號 int not null,
  • 計劃編號 int not null ,
  • 參與時數 int ,
  • primary key(員工編號,計劃編號),
  • foreign key(員工編號) references 員工資料表(員工編號),
  • foreign key(計劃編號) references 計劃資料表(計劃編號)
  • )
  • create table 親屬資料表(
  • 員工編號 int not null,
  • 姓名 char(20) not null ,
  • 性別 char(8) ,
  • 地址 char(80)
  • primary key(員工編號,姓名),
  • foreign key(員工編號) references 員工資料表(員工編號)
  • )

SQL資料處理語言DML

  • insert into 計劃資料表 (計劃編號,計劃名稱,計劃地點,部門編號) values (1,’3D記憶體計劃’,’台北市南港路2段99號’,1)
  • insert into 參與資料表 (員工編號,計劃編號,參與時數) values (6,1,25)
  • insert into 參與資料表 (員工編號,計劃編號,參與時數) values (6,2,250)
  • insert into 參與資料表 (員工編號,計劃編號,參與時數) values (13,4,350)
  • insert into 參與資料表 (員工編號,計劃編號,參與時數) values (15,1,25)
  • insert into 參與資料表 (員工編號,計劃編號,參與時數) values (15,2,350)
  • ‧5.建立關聯
    • ‧ER Diagram
  • ALTER TABLE 員工資料表 ADD CONSTRAINT depart_no
  • FOREIGN KEY (部門編號)
  • REFERENCES 部門資料表 (部門編號)
  • ON DELETE NO ACTION
  • ON UPDATE NO ACTION
  • ALTER TABLE 部門資料表 ADD CONSTRAINT manager
  • FOREIGN KEY (管理者)
  • REFERENCES 員工資料表 (員工編號)
  • ON DELETE NO ACTION
  • ON UPDATE NO ACTION
  • ALTER TABLE 部門位址資料表 ADD CONSTRAINT depart_loc
  • FOREIGN KEY (部門編號)
  • REFERENCES 部門資料表 (部門編號)
  • ON DELETE NO ACTION
  • ON UPDATE NO ACTION
  • ‧6.關聯式代數合併運算select-Join
  • 代數合併運算將多個資料表欄位取出
  • ‧1.合併運算Join(AXB)

R1 Join 合併條件 R2

合併運算Join R1與R2作乘積運算後,再依合併條件來過濾不合條件的紀錄

選取結合Join-乘積運算

  • INNER JOIN只取回合併資料表中符合合併條件的紀錄資料
  • 交叉合併查詢其查詢結果的紀錄數是兩個資料表記錄數的乘積.一個資料表是9筆資料,一個資料表是8筆資料,交叉合併查詢後的記錄數為8*9=72筆
  • ‧2. INNER JOIN

select 員工資料表.員工編號 ,員工資料表.姓名 ,部門資料表.部門

from 員工資料表 inner join 部門資料表

on 員工資料表.員工編號 = 部門資料表.管理者

  • ‧3. LEFT Outer Join
  • ‧4. RIGHT OUTER JOIN

RIGHT OUTER JOIN(右邊全部參與)

use company

select 員工資料表.員工編號 ,員工資料表.姓名 ,部門資料表.部門

from 員工資料表 RIGHT join 部門資料表

on 員工資料表.員工編號 = 部門資料表.管理者

FULL Outer Join

use company

select 員工資料表.員工編號 ,員工資料表.姓名 ,部門資料表.部門

from 員工資料表 full join 部門資料表

on 員工資料表.員工編號 = 部門資料表.管理者

  • ‧5.卡迪生乘積運算

UNION聯集運算

select 查詢

  • use company;
  • select * from 員工資料表  where 姓名 like ‘吳%’ or 姓名 like ‘賴%’ ;
  • use company;
  • select * from 員工資料表  where (姓名 like ‘吳%’ or 姓名 like ‘賴%’) and 員工編號>7;
  • use company;
  • select * from 員工資料表  where (姓名 like ‘吳%’ or 姓名 like ‘賴%’) and 員工編號>7 order by 員工編號 desc;
  • use company;
  • select count(*) as 總計,emp.部門編號 from 員工資料表  as emp  group by emp.部門編號;
  • select count(*),max(emp.薪水),min(emp.薪水),avg(emp.薪水),emp.部門編號 from 員工資料表  as emp  group by emp.部門編號;
  • use company
  • select count(*),max(emp.薪水),min(emp.薪水),avg(emp.薪水),emp.部門編號 from 員工資料表  as emp  group by emp.部門編號;
  • use company
  • select count(*),max(emp.薪水),min(emp.薪水),avg(emp.薪水),emp.部門編號 from 員工資料表  as emp,部門資料表 as depart  group by emp.部門編號;
  • use company
  • select count(*),max(emp.薪水),min(emp.薪水),avg(emp.薪水),emp.部門編號 from 員工資料表  as emp,部門資料表 as depart  where depart.部門編號 = emp.部門編號  group by emp.部門編號;
  • –多個表格查詢
  • use company;
  • select count(*),max(emp.薪水),min(emp.薪水),avg(emp.薪水),emp.部門編號,sum(emp.薪水) from 員工資料表  as emp,部門資料表 as depart  where depart.部門編號 = emp.部門編號  group by emp.部門編號;
  • use company
  • select count(*),max(emp.薪水),min(emp.薪水),avg(emp.薪水),emp.部門編號 from 員工資料表  as emp,部門資料表 as depart  group by emp.部門編號;
  • use company
  • select count(*),max(emp.薪水),min(emp.薪水),avg(emp.薪水),emp.部門編號 from 員工資料表  as emp,部門資料表 as depart  where depart.部門編號 = emp.部門編號  group by emp.部門編號;
  • –多個表格查詢
  • use company;
  • select count(*),max(emp.薪水),min(emp.薪水),avg(emp.薪水),emp.部門編號,sum(emp.薪水) from 員工資料表  as emp,部門資料表 as depart  where depart.部門編號 = emp.部門編號  group by emp.部門編號;

insert 和update

  • use company
  • insert into 親屬資料表 (員工編號,姓名,性別,地址)
  • select 員工編號,姓名,性別,地址 from 員工資料表;
  • use company
  • update 員工資料表  set 薪水=薪水*1.10 where 員工編號 in (8,9,10,11,13,15)

 

SQL SERVER_2017 關聯式資料庫

1.View 檢視

  • Create View 名稱
  • AS
  • 查詢敘述
  • create view HighSalary_View
  • with encryption
  • as
  • select * from 員工資料表
  • where 薪水>80000

2.變數

  • 在SQL SERVER中,變數有純量變數與資料表變數

2-1.變數的生存空間

@@為全域系統變數

@@rowcount,T-SQL所影響的記錄數

@@identify欄位自動編號值,如果沒有則傳回null

@@error傳回T-SQL所產生的錯誤編號

Cast(),和convert()函數

Cast@y4變數由INT整數轉型成char

Convert@y5變數由INT整數轉型成char

3.流程控制結構

  • 布林運算式
  • 選取結構:if
  • 一個選擇的if敘述
  • 有兩種選擇的if敘述
  • 有複合敘述的if敘述
  • 選取結構case
  • while迴圈
  • do…while迴圈
  • Loop敘述
  • 如何來選擇流程前進的方向,我們必須經過測試條件,例如,當條件成立時往左方,當條件不成立時往右方。我們使用布林表示式來測試工作。
  • 布林Boolean代數定義在一個二元素的集合上,即B={true,false},true為真,false為假。我們可以使用這個值的結果來決定我們行進的方向。
  • 當下列菱形四邊形成立true時會執行右方的流程,當下列菱形四邊行的條件不成立false時會執行左方的流程。true和false就是屬於布林代數,這是用在if判別式。
  • 當下列菱形四邊形成立true時會執行右方的流程,當下列菱形四邊行的條件不成立false時會執行下方或右方的流程。True和false就是屬於布林代數,這是運用在迴圈結構。
  • 3-1begin … end 區塊
  • 3-2判斷IF

  • IF 條件
  • 敘述 …
  • [ELSE     IF    條件
  • 敘述….]…
  • [ELSE
  • 敘述…]

  • 我們已經看過if敘述1個或2個的選擇,在此要用巢狀if敘述來撰寫多重選擇決策。例如我們的成績評等,90到100分的為A級,80到89的為B級,70到79的為C級,60到69的為D級,59分以下的為F級。如果我有一位學生分數為82分,他的分數在80到89分之間,則他的等級為B級。在這個例子中有5個等級來做選擇,就叫做多重選擇。

Db_id(‘company’)傳回資料庫company是否存在.是則傳回true,否則傳回false

Object_id(‘員工資料表’),傳回員工資料表是否存在

  • 3-3if.. else if….. else…
  • 3-4CASE多重選擇

  • CASE
  • WHEN 條件  THEN 敘述 …
  • [WHEN 條件 THEN 敘述…]…
  • [ELSE 敘述…]
  • END
  • 3-5迴圈while

  • 語法:
  • While 條件
  • begin
  • 敘述…
  • END
  • 在if敘述中,條件後的敘述只執行一次,而在while敘述中,則可執行一次以上。While敘述的程序圖形中.選取結構和循序結構,都只執行程式敘述一次,如果我們要讓同一行程式重複執行好幾遍則要用迴圈敘述。迴圈敘述可以重複執行某一段程式好幾遍,直到條件的不成立才跳出這個迴圈。迴圈敘述:while、do……while。
  • 3-6處理異常或錯誤使用try…catch…
  • T-SQL錯誤處理是使用TRY/CATCH指令建立的Try區塊和catch區塊組成.
  • 語法:
  • Begin try
  • T-SQL指令
  • End try
  • Begin catch
  • T-SQL指令敘述
  • End catch

使用sp_addmessage系統預存程序新增錯誤訊息

Raiserror(錯誤編號,嚴重等級,錯誤狀態)

使用throw指令敘述來丟出例外,用來取代raiserror()函數

4.預存Stored Procedure程序

預存程序Stored Procedure和預存函數Stored function

預存程序

CREATE PROCEDURE 名稱

[@參數1 資料型態,

@參數2 資料型態,,]

[with {recompile|encryption|recompile,encryption}]

AS

Procedure程序程式碼

5.預設函數

  • 我們可以把複雜的敘述區塊包含在函數裡,需要時再呼叫函式,就可以把這複雜的區塊載入。這樣可以讓我們加快軟體的開發,並使撰寫程式簡單多了。
  • 在SQL SERVER中已經定義好的函數,這些函數都是SQL SERVER事先已經寫好只要我們呼叫他就可載入我們程式執行。
  • 我們也可以自己定義函數,這就稱為使用者自訂函數。
  • 當我們要使用函數時,只要在呼叫函數就可以了。
  • myfunction();
  • 函數可以有回傳return,而程序則沒有回傳
  • 我們可以呼叫預儲程序,使用exec.
  • exec myprocedure();

預存程序

CREATE PROCEDURE 名稱

[@參數,,,,]

AS

BEGIN

Procedure程序程式碼

….

多個敘述

END

使用SQL script。「CREATE FUNCTION」的語法另外包含「RETURNS」與「RETURN」兩個關鍵字。

建立Stored functions的基本內容:

函數

CREATE function 名稱

([@參數名稱  參數型態,,,,])

returns 回傳值型態

[with(encryption|schemabinding|encryption,schemabinding)]

AS

BEGIN

function程序程式碼

….

多個敘述

return 回傳值;

END

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type

[ = default ] [ READONLY ] }

[ ,…n ]

]

)

RETURNS return_data_type

[ WITH <function_option> [ ,…n ] ]

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

[ ; ]

6.資料表指標cursor

  • T-SQL的指標cursor可以使用在預存程序或觸發程序來處理結果集合中的每一筆記錄.
  • Declare 宣告與定義一個新的資料指標
  • Open執行資料指標定義的select指令來開啟與建立資料指標
  • Fetch從資料指標取出一筆資料
  • Close關閉資料指標
  • Deallocate刪除資料指標和釋放系統資源
  • 宣告資料指標:
  • Declare 資料指標名稱 cursor
  • [local|global]
  • [forward_only|scroll]
  • [fast_forward|static|keyset|dynamic]
  • [read_only|scroll_locks|optimistic]
  • For select 敘述

  • Local:區域資料指標只能在宣告的程序中使用
  • GLOBAL:全域資料指標可以在目前連線的程序中使用
  • FORWARD_ONLY:只能循序讀取,不能回頭捲動,這是預設值
  • SCROLL:可以前後讀取記錄,當指定STATIC,KEYSET或DYNAMIC資料指標種類時,預設是SCROLL.
  • FAST_FORWARD一種單向唯讀的資料指標
  • STATIC:使用暫存資料表儲存記錄資料,支援捲動
  • KEYSET:只有將唯一鍵值欄存入暫存資料表
  • DYNAMIC:直接動態從來源資料表取得記錄資料,所以資料能夠動態更新支援捲動
  • NEXT:預設移動方式,如果第一次執行,就是讀取第一筆資料,如果是FORWARD_ONLY究只能使用NEXT
  • PRIOR:讀取上一筆記錄
  • FIRST:將資料指標移動到第一筆記錄
  • LAST:將資料指標移到最後一筆記錄
  • ABSOLUTE n:讀取從頭算起第n筆記錄
  • RELATIVE n:讀取從目前資料指標位置起算第n筆記錄.

系統變數@@FETCH_STATUS和@@CURSOR_ROWS

  • @@FETCH_STATUS:0成功執行FETCH指令
  • @@FETCH_STATUS:-1,因為已到最後一筆資料,所以執行FETCH指令失敗
  • @@FETCH_STATUS:-2,因為資料已被刪除,所以執行FETCH指令失敗
  • @@CURSOR_ROWS:n,傳回最近開啟資料指標結果集的記錄數
  • @@CURSOR_ROWS:0,沒有任何記錄數
  • @@CURSOR_ROWS:-1,因為是動態資料指標DYNAMIC,所以記錄數也會變動

資料表指標cursor

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [ ,…n ] ] } ]

[;]

Transact-SQL Extended Syntax

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR select_statement

[ FOR UPDATE [ OF column_name [ ,…n ] ] ]

[;]

7.Trigger觸發

  • DML trigger:每一個資料表都可以設定Trigger觸發,當INSERT,UPDATE或DELETE該資料表時,都會產生Trigger觸發.每一個資料表可以建立這些trigger
  • DDL觸發:可以回應DDL指令
  • After觸發程序:當執行insert,update和delete指令且資料已經改變,所觸發和執行的觸發程序
  • INSTEAD OF觸發:這是在資料改變前,可以驗證資料或取代原本需要執行的操作

語法:

create trigger 資料表名稱.trigger名稱

on 資料庫名稱.資料表名稱

[with encryption]

(for|after|instead of)

{[insert],[update],[delete]}

as

begin

敘述;

end;

8.交易與鎖定

  • Commit:為認可交易,表示交易中的所有資料庫單元操作,真正將更改寫入資料庫
  • Rollback:回復交易,如果交易尚未認可,則可以取消交易.
  • Begin tran:標示開始執行一個交易,它是交易起點

巢狀交易

  • 在begin tran建立的交易中,擁有其它begin tran建立的交易
  • SQL Server提供@@trancount變數取得目前是位在巢狀交易的哪一層
  • Commit tran:只有當@@trancount系統變數為1時,執行commit tran才會真的認可交易
  • Rollback tran:不論在那一層執行rollback tran回復交易,都是回復整個巢狀交易,@@trancount變數也變0

MySQL資料庫安裝

1.在Windows 10安裝MySQL資料庫

2.在Mac安裝MySQL資料庫

安裝MySQL資料庫  在終端機使 MySQL  安裝MySQL Workbench  連接MySQL Workbench

MySQL資料庫

1.View 檢視

2.Prepared 敘述

3.交易

4.預存Stored Routine

預存Stored Routine包含預存程序Stored Procedure和預存函數Stored function

預存程序

CREATE PROCEDURE 名稱  ([參數,,,,])

Procedure程序程式碼

刪除預存程序

DROP Procedure [IF EXISTS] 名稱

呼叫程序

CALL 程序名稱

預存函數Stored function

CREATE Function 名稱  ([參數,,,,])

returns 回傳型態

function程式碼

Drop function [If exists] 名稱

5.預設函數

6.Stored Routines的變數與流程

  • 6-1宣告與使用變數
  • 6-2判斷IF
  • 6-3CASE多重選擇
  • 6-4迴圈while,REPEAT
  • 6-5CURSORS指標和Handlers處理異常

對一個查詢結果的每一筆紀錄執行特定的工作,我們宣告一個cursor來代表一個查詢的結果.

語法:

宣告cursor指標名稱

Declare cursor名稱  cursor for 查詢敘述

打開指標名稱

Open cursor名稱

從已經開啟的指標名稱中讀取一筆紀錄的資料到指定的變數中

FETCH CURSOR名稱  into 變數名稱[,….]

關閉指標

close cursor名稱

7.Trigger觸發

  • 每一個資料表都可以設定Trigger觸發,當INSERT,UPDATE或DELETE該資料表時,都會產生Trigger觸發.
  • 每一個資料表可以建立六種trigger,分別是Before trigger,Before update,Before delete,After insert,After update和after delete.
  • 語法: create trigger 資料表名稱.trigger名稱 before update on 資料庫名稱.資料表名稱 for each row
  • begin
  • 敘述;
  • end;
  • 使用drop來刪除trigger drop trigger 資料庫.trigger名稱;

課程網址