常用 SQL

2023.03.16
SQL

Database

CREATE DATABASE

CREATE DATABASE database_name;

USE

USE database_name;

Data Types

Numeric Data

Integer

Float

Money

Date and Time

Character strings

Non-Unicode / Unicode
char(n) / nchar(n)	長度固定 1 <= n <= 8000 / 1 <= n <= 4000
varchar(max) / nvarchar(max)	可變長度 1 <= max <= 8000 / 1 <= max <= 4000

Boolean

CREATE TABLE

CREATE TABLE table_name (
  column_name_1 data_type_1,
  column_name_2 data_type_2,
  column_name_3 data_type_3,
  ···
);

MODIFY

改 column 長度

ALTER TABLE table_name MODIFY COLUMN column_name data_type(new_length);

Key

Primary Key

Unique Key

Index Key

INSERT INTO … ON DUPLICATE KEY UPDATE …

當有 key 重複時，執行 UPDATE，否則 INSERT

INSERT INTO table_name (column_name_1, column_name_2, column_name_3, …)
VALUES (‘value_1’, ‘value_2’, ‘value_3’, …)
ON DUPLICATE KEY UPDATE column_name_1 = ‘value_1’, ‘column_name_2’ = ‘value_2’, …;

引號

單引號

單引號的跳脫字元：「‘‘」（連續兩個單引號）

Ref

https://www.fooish.com/sql/

Last Updated on 2023/08/16 by A1go