# 基本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圖表
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 主鍵限制
用來保證欄位在資料表中的唯一性，主鍵欄位中的每一筆資料在資料表中都必需是獨一無二的。
Sign in with Wallet
Connect another wallet