---
# System prepended metadata

title: 基本sql

---

# 基本sql-(實作面)


### 表的架構
1. table (可以變更的實體表
2. viewTable(table 與其他表的關聯，只有顯示查詢作用)

### 基本操作

1. 查詢 - 
``` sql
select * from table WHERE column_name operator value;
```
> 註: * 為全選(全部欄位的意思), 也可以只查特定欄位

2. 更新 - 
``` sql
update table set value = new value WHERE column_name operator value; 

```

3. 新增 - 
``` sql
insert table (col, col2) Values('colValue', 'col2Value')
```

4. 刪除 - 
``` sql
DELETE FROM table_name
WHERE column_name operator value;
```


5. 複雜join圖表

![螢幕擷取畫面 2023-11-18 191259](https://hackmd.io/_uploads/S1Siaf8NT.png)


6. group by (分類群組)
``` sql
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
```

> 注意group by 後可以選擇的欄位必須group by 否則只能使用max, sum , count 等等來選擇欄位，因為group by 一個群組可能有多個必須指定否則無法知道選取的資料。

7. order by (排序)

``` sql
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
```
> asc 遞增(由小到大) desc 遞減(由大到小)


8. 建立表
```
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);
```

### 表的建構內容

1. datatype



| Exact numerics | Approximate numerics | Date and time |
| -------- | -------- | -------- |
| bigint, numeric, bit , smallint, decimal,smallmoney , int,  tinyint, money   | float, real     | date, datetimeoffset,datetime2,smalldatetime,datetime,time   |


| Character strings | Unicode character strings | Binary strings |
| -------- | -------- | -------- |
| char,varchar,text     | nchar,nvarchar,ntext     | binary,varbinary,image     |



| Other data types | 
| -------- |
| cursor,rowversion,hierarchyid,uniqueidentifier,sql_variant,xml,Spatial Geometry Types,Spatial Geography Types,table     | 


**常用 int, decimal, float, char,varchar,nchar,nvarchar,datetime**


[ 分辨 char varchar nchar nvarchar](https://ithelp.ithome.com.tw/articles/10213922#:~:text=%E8%AE%8A%E9%95%B7(%E6%9C%89var)%3A,%E6%9C%83%E5%9C%A8%E5%BE%8C%E9%9D%A2%E5%A1%AB%E5%85%85%E7%A9%BA%E6%A0%BC%E3%80%82)


    1. n, 不確定是否都是英文數字
    2. var, 存資料沒有固定長度

2. PRIMARY KEY 主鍵限制
    用來保證欄位在資料表中的唯一性，主鍵欄位中的每一筆資料在資料表中都必需是獨一無二的。










