###### tags: `sql`
# SQL語法:簡易table操作(course1)
1. **Create**
新增一個表格
``` sql=
CREATE TABLE celebs (
id INTEGER,
name TEXT,
age INTEGER
);
```
celebs: 表格名稱
2. **Insert**
在表格中新增一列
``` sql=
INSERT INTO celebs (id, name, age)
VALUES (1, 'Justin Bieber', 22);
INSERT INTO celebs (id, name, age)
VALUES (2, 'Beyonce Knowles', 33);
INSERT INTO celebs (id, name, age)
VALUES (3, 'Jeremy Lin', 26);
INSERT INTO celebs (id, name, age)
VALUES (4, 'Taylor Swift', 26);
```
3. **Select**
``` sql=
SELECT * FROM celebs;
SELECT name FROM celebs; #選取name這一行
---
SELECT column1, column2...
FROM table_name;
```
*: all columns
4. **Alter**
``` sql=
ALTER TABLE celebs
ADD COLUMN twitter_handle TEXT;
```
**twitter_handle**: the name of the new column being added
TEXT: data type for the new column
5. **Update**
``` sql=
UPDATE celebs
SET twitter_handle = '@taylorswift13'
WHERE id = 4;
```
**twitter_handle**: the name of the column that is going to be updated
**@taylorswift13**: the new value that is going to be inserted into the **twitter_handle** column
<font color="#900C3F">SET</font> : 後面接要插入的物件
6. **Delete**
``` sql=
DELETE FROM celebs
WHERE twitter_handle IS NULL;
```
<font color="#900C3F">DELETE FROM</font> : 執行刪除從table某列的動作
<font color="#900C3F">WHERE</font> : 選哪一列
<font color="#900C3F">IS NULL</font> : a condition in SQL that returns true when the value is NULL and false otherwise.
* **Constraints**
``` sql=
CREATE TABLE celebs (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
date_of_birth TEXT NOT NULL,
date_of_death TEXT DEFAULT 'Not Applicable'
);
```
<font color="#900C3F">PRIMARY KEY</font> : 可當作每一列的編號，如果要新增一列已有編號的ROW，則違反限制且不可加入。
<font color="#900C3F">UNIQUE</font> : 近似於PRIMARY KEY，但可以有很多行。
<font color="#900C3F">NOT NULL</font> : 加入的column必須有value，不可為NULL。
<font color="#900C3F">DEFAULT</font> : columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.
| Syntax | meaning |
| -------- | -------- |
| CREATE TABLE | creates a new table|
| INSERT INTO | adds a new row |
| SELECT | queries data from a table |
| ALTER TABLE| changes an existing table |
| UPDATE | edits a row |
| DELETE FROM | deletes rows |
Sign in with Wallet
Connect another wallet