markdown-it demo

clear permalink

```sql
-- UPDATE Syntax
UPDATE *table_name*
SET *column1* = *value1*, *column2* = *value2*, ...
WHERE *condition*;

UPDATE student
SET age = 20
WHERE name = 'Alice';

-- DELETE Syntax
DELETE FROM table_name WHERE condition;
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

-- 刪除 Table
DROP TABLE table_name; 
DROP TABLE Shippers;

-- SELECT Syntax
SELECT column1, column2, ...
FROM table_name;

-- SELECT Syntax
SELECT * FROM mytable WHERE status IN ('Open', 'Pending');

-- SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;

-- INSERT INTO Syntax
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

-- INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

-- CREATE INDEX Syntax
CREATE INDEX index_name
ON table_name (column1, column2, ...);

-- CREATE UNIQUE INDEX Syntax
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

-- ORDER BY Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

-- ALTER TABLE - ADD Column
ALTER TABLE table_name
ADD column_name datatype;
-- example
ALTER TABLE Customers
ADD Email varchar(255) DEFAULT 'example@example.com' NOT NULL;

-- ALTER TABLE - DROP COLUMN
ALTER TABLE table_name
DROP COLUMN column_name;
-- example
ALTER TABLE Customers
DROP COLUMN Email;

-- ALTER TABLE - ALTER/MODIFY COLUMN
-- https://www.w3schools.com/sql/sql_alter.asp
-- SQL Server / MS Access
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
-- My SQL / Oracle (prior version 10G):
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
-- Oracle 10G and later
ALTER TABLE table_name
MODIFY column_name datatype;
-- example
ALTER TABLE my_table
MODIFY record_date CHAR(2) DEFAULT TO_CHAR(SYSDATE, 'DD');

-- SQL COUNT()
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

-- 多行註解
/*
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
*/
```

```sql
-- SQL AUTO INCREMENT Field
CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    City varchar(255) DEFAULT 'Sandnes',
    Age int,
    PRIMARY KEY (Personid)
	);
```

```sql
-- SQL Data Types
https://www.w3schools.com/sql/sql_datatypes.asp

```
#### [The SQL INSERT INTO SELECT Statement](https://www.w3schools.com/sql/sql_insert_into_select.asp)
```sql
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition; 
```

### 新增外鍵
在 SQL 中，您可以使用 `ALTER TABLE` 來更改資料表的結構。如果您想要在 `ALTER COLUMN` 時設定外鍵，您可以使用 `ADD CONSTRAINT` 來設定外鍵。以下是一個範例 SQL 語法：

```
ALTER TABLE table_name
ALTER COLUMN column_name datatype
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_name) REFERENCES foreign_table_name(foreign_column_name);
```

其中，`table_name` 是您想要更改的資料表名稱，`column_name` 是您想要更改的欄位名稱，`datatype` 是您想要設定的資料型態，`fk_constraint_name` 是您想要設定的外鍵名稱，`foreign_table_name` 是參考的資料表名稱，而 `foreign_column_name` 則是參考的欄位名稱。

### 修改外鍵
當您在資料表中使用外鍵時，您可以使用 `ALTER TABLE` 來更改資料表的結構。如果您想要修改已存在的外鍵，您可以使用 `ALTER TABLE` 來刪除現有的外鍵約束，然後再使用 `ADD CONSTRAINT` 來添加新的外鍵約束。以下是一個範例 SQL 語法：

```
ALTER TABLE table_name
DROP CONSTRAINT fk_constraint_name;

ALTER TABLE table_name
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_name) REFERENCES foreign_table_name(foreign_column_name);
```

在這個範例中，`table_name` 是包含您想要修改的外鍵約束的資料表名稱，而 `fk_constraint_name` 則是您想要修改的外鍵約束名稱。第一個 `ALTER TABLE` 語句刪除現有的外鍵約束，而第二個 `ALTER TABLE` 語句則添加了一個新的具有相同名稱的外鍵約束。

## MySQL
```sql
CREATE DATABASE test;
DROP DATABASE test;
USE test;
CREATE TABLE bands (
	id INT NOT NULL AUTO_INCREMENT, 
	name VARCHAR(255) NOT NULL,
	PRIMARY KEY(id)
);
ALTER TABLE bands ADD another_column VARCHAR(255);
DROP TABLE tst_table;
CREATE TABLE albums (
	id INT NOT NULL AUTO_INCREMENT, 
	name VARCHAR(255) NOT NULL,
	release_year INT,
	band_id INT NOT NULL,
	PRIMARY KEY(id),
	FOREIGN KEY(band_id) REFERENCES bands(id)
);
INSERT INTO bands (name) VALUES ('Iron Maiden');
INSERT INTO bands (name) VALUES ('Duece'), ('Avenged Sevenfold'), ('Ankor');
SELECT * FROM bands;
SELECT * FROM bands LIMIT 2;
SELECT name FROM bands;
SELECT id AS 'ID', name AS 'Band Name' FROM bands;
SELECT * FROM bands ORDER BY name;
SELECT * FROM bands ORDER BY name ASC;
SELECT * FROM bands ORDER BY name DESC;
INSERT INTO albums (name, release_year, band_id)
VALUE ('The Number of Beasts', 1985, 1), 
('Power Slave', 1984, 1), 
('Nightmare', 2018, 2),
('Nightmare', 2010, 3),
('Test Album', NULL, 3);
SELECT DISTINCT name FROM albums;
UPDATE albums SET release_year = 1982 WHERE id = 1;
SELECT * FROM albums WHERE name LIKE '%er%';
SELECT * FROM albums WHERE name LIKE '%er%' OR band_id = 2;
SELECT * FROM albums WHERE release_year = 1984 AND band_id = 1;
SELECT * FROM albums WHERE release_year BETWEEN 2000 AND 2018;
SELECT * FROM albums WHERE release_year IS NULL;
DELETE FROM albums WHERE id = 5;
SELECT * FROM bands JOIN albums ON bands.id = albums.band_id;
SELECT * FROM bands INNER JOIN albums ON bands.id = albums.band_id;
SELECT * FROM bands LEFT JOIN albums ON bands.id = albums.band_id;
SELECT * FROM bands RIGHT JOIN albums ON bands.id = albums.band_id;
SELECT AVG(release_year) FROM albums;
SELECT SUM(release_year) FROM albums;
SELECT band_id, COUNT(band_id) FROM albums GROUP BY band_id;

SELECT b.name AS band_name, COUNT(a.id) AS num_albums 
FROM band AS b
LEFT JOIN albums AS a ON b.id = a.band_id
GROUP BY b.id;

SELECT b.name AS band_name, COUNT(a.id) AS num_albums 
FROM band AS b
LEFT JOIN albums AS a ON b.id = a.band_id
WHERE num_albums = 1
GROUP BY b.id;

SELECT b.name AS band_name, COUNT(a.id) AS num_albums 
FROM band AS b
LEFT JOIN albums AS a ON b.id = a.band_id
WHERE b.name = 'Deuce'
GROUP BY b.id
HAVING num_albums = 1;
```

html source debug