iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 13
2
Software Development

以資料庫為開發核心,利用通用 API 玩轉後端資料存取的概念與實作系列 第 13

Day13:常用的 SQL 語法簡介 I

結構化查詢語言(Structured Query Language,SQL),是一種資料庫程式語言,用於資料庫中的標準資料查詢語言,IBM公司最早使用在其開發的資料庫系統中。1986年10月,美國國家標準學會(ANSI)對SQL進行規範後,以此作為關聯式資料庫管理系統的標準語言(ANSI X3. 135-1986),1987年得到國際標準組織的支援下成為國際標準。不過各種通行的資料庫系統在其實踐過程中都對SQL規範作了某些修改和擴充。所以,實際上不同資料庫系統之間的SQL不能完全相互通用。本文會說明常用且標準的通用SQL語法,再撰寫SQL語法時應儘量依詢ANSI-92標準,讓資料庫的轉換難度降低,進而才能支撐我所強調主張的「以資料庫為開發核心」的開發理念。

SQL語法主要包含3個部分:

「資料定義語言」(DDL : Data Definition Language)
「資料操縱語言」(DML : Data Manipulation Language)
「資料控制語言」(DCL : Data Control Language)

本文主要的重點是前2個部份,並以一些完整的實際案例,讓各位能通過實作,熟悉基礎的SQL語法。SQL 的資料定義語言 (DDL) 部分主要的功能是創建、維護或刪除表格。我們也可以定義索引(鍵),規定表之間的連結,以及施加表間的約束。

SQL 中最重要的 DDL 語句:

‧CREATE DATABASE -創建新資料庫
‧ALTER DATABASE -修改資料庫
‧CREATE TABLE - 創建新表
‧ALTER TABLE - 變更(改變)資料庫表
‧DROP TABLE - 刪除表
‧CREATE INDEX - 創建索引(搜索鍵)
‧DROP INDEX - 刪除索引

SQL 也包含用於更新、插入和刪除記錄的語法。查詢/更新/插入/刪除 指令構成了 SQL 的 DML 部分:

‧SELECT - 從資料庫表中獲取資料
‧UPDATE - 更新資料庫表中的資料
‧DELETE - 從資料庫表中刪除資料
‧INSERT INTO - 向資料庫表中插入資料

接下來,我們就實際的來操作一下常用的SQL
建立新資料庫

語法:CREATE DATABASE database-name
實例:CREATE DATABASE SampleDB

刪除資料庫

語法:DROP DATABASE database-name
實例:DROP DATABASE SampleDB

新增資料表以在前面的系列文中實作,此處不在贅述。

刪除表(Table)

語法:DROP TABLE tabname
實例:DROP TABLE MEMBER

增加一個欄位

語法:alter table tabname add column col type
實例:alter table member add newfield varchar(20) default ''

增刪主鍵

添加主鍵: alter table tabname add primary key(col);
刪除主鍵: alter table tabname drop primary key(col);

選取資料

select * from member where memberid >= '0' and memberid <= '0000000100'
select * from member where memberid like '%10%'

新增資料

insert into member (memberid,membername) values('m099', 'michael chen');
insert into member(memberid,membername)
select custid,coprcname from bascustomer where custid like 'm%'

刪除資料

delete from member where memberid > '0000000900';

更新資料

update member set memberlavel = '00' where memberid <= '0000000100;

排序

select * from member order by memberid;

計算筆數

select count(*) as totalcount from member;

合計

select productid,sum(quantity) as sumvalue from opoorder1_d group by productid;

求平均值

select billno,avg(totalamount) as avgvalue from opoorder1_m group by billno;

取最大值

select custid,max(totalamount) as maxvalue from opoorder1_m group by custid;

取最小值

select custid,min(billdate) as maxvalue from opoorder1_m group by custid;

UNION 及 LEFT JOIN
union [all] union 運算子通過組合其他兩個結果表(例如 table1 和 table2)並消去表中任何重複行而派生出一個結果表。當 all 隨 union 一起使用時(即 union all),不消除重複行。

select 1 as flag,a.billno,b.billdate,a.subaqmount
from opoorder1_d a left join opoorder1_m b on a.billno = b.billno
union all
select 2 as flag,a.billno,b.billdate,a.subaqmount
from opoorder2_d a left join opoorder2_m b on a.billno = b.billno;

子查詢

select * from member where memberid
IN (select custid from bascustomer where custid like 'M0%')

select * from member where memberid IN ('M001','M009','M012')

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

動態SQL語法執行

declare @start int,@end int 
@sql nvarchar(600) 
set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’ 
exec sp_executesql @sql

資料庫取回分頁資料

/* 選擇從10到15的記錄 */
select top 5 * from (select top 15 * from table order by id asc) table_別名 order by id desc

以上是最基礎的SQL語法,基本上所有的資料庫基礎語法都差不多。剛開始學習時,只要能不斷的用心練習、熟悉這些語法,就可以解決絕大多數的需求。20/80 法則還蠻是用這個狀況,也就是說,開發企業資訊系統時,80% 都是一般難度的 issue,運用基礎的語法大部分都可以解決。但是 20% 是較進階的問題,例如 MRP 展算、月加權成本計算等,除了必須精通 Domain 外,也必須用到一些較進階的語法才能解決(如遞迴)。反正一口吃不成胖子,千里之行、始於足下。總是要慢慢的積累。今天先說到這邊,明天會說明一些較進階的SQL語法,感謝您的收看,我們明天見。


上一篇
Day12:常用的資料庫資料型態
下一篇
Day14:常用的 SQL 語法簡介 II
系列文
以資料庫為開發核心,利用通用 API 玩轉後端資料存取的概念與實作30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言