SQL对大小写不敏感
SQL多使用单引号引用文本
SQL语句以；作为间隔
SQL - DML (data mangament language):
SELECT; UPDATE; DELETE; INSERT INTO;SQL - DDL (data definition language):
CREATE DATABASE; ALTER DATABASE;
CREATE TABLE; ALTER TABLE; DROP TABLE;
CREATE INDEX; DROP INDEX;DML
SELECT column1, column2 FROM table
SELECT * FROM table
SELECT DISTINCT column FROM table （去重）
SELECT column FROM table WHERE column1 = a (AND/OR column2 = b)
SELECT column FROM table ORDER BY column1, column2 (DESC, ASC)
/operators for example/
| operator | description |
|---|---|
| = | 等于 |
| <> | 不等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| (NOT) BETWEEN | 范围内搜索（数值/文本/日期）/因数据库不同而包含不同 |
| LIKE | 模式内搜索 |
| IN | 定值内搜索 |
/wildcards for example/
| wildcard | description |
|---|---|
| % | 1 or more chars |
| _ | 1 char |
| [charlist] | anyone in the charlist |
| [^charlist]/ [!charlist] | anyone NOT in the charlist |
UPDATE table SET column1 = a, column2 = b
WHERE column3 = c
DELETE FROM table WHERE column = a
DELETE * FROM table
DELETE FROM table
INSERT INTO table (column1, column3) VALUES (a, b)
- DDL
CREATE DATABASE database
CREATE TABLE table(column1 type1, column2 type2)
/types for example/
| type | description |
|---|---|
| integer (size) - int (size) - smallint (size) - tinyint (size) | 整数，size = 位数 |
| decimal (size, d) - numeric (size, d) | 实数，size = 位数，d = 小数位数 |
| char (size) | 固定长度字符串，size = 字符长度 |
| varchar (size) | 可变长度字符串，size = 最大字符长度 |
| date (yyyymmdd) | 日期 |
/restraint for example/ (CREATE/ALTER TABLE)
| restraint | application （ |
description |
|---|---|---|
| NOT NULL | column type NOT NULL | 非空 |
| UNIQUE | UNIQUE (column) | 唯一 |
| - | CONSTRAINT uname UNIQUE (column) | 唯一命名 |
| - | ADD UNIQUE (column) | 唯一ALTER专用 |
| - | ADD CONSTRAINT uname UNIQUE (column) | 唯一ALTER专用 |
| - | DROP INDEX /CONSTRAINT uname | 唯一ALTER专用 |
| PRIMARY KEY | PRIMARY KEY (column) | 主键 |
| - | column type PRIMARY KEY | 主键 |
| - | CONSTRAINT pname PRIMARY KEY (column) | 主键命名 |
| - | ADD PRIMARY KEY (column) | 主键ALTER专用 |
| - | ADD CONSTRAINT pname PRIMARY KEY (column) | 主键ALTER专用 |
| - | DROP PRIMARY KEY | 主键ALTER专用 |
| - | DROP CONSTRINT pname | 主键ALTER专用 |
| FOREIGN KEY | FOREIGN KEY (column) REFERENCES table2 | 与主键关联的外键 |
| - | column type FOREIGN KEY REFERENCES table2 | 与主键关联的外键 |
| - | ADD FOREIGN KEY (column) REFERENCE table2 | 外键ALTER专用 |
| - | ADD CONSTRAINT fname FOREIGN KEY (column) REFERENCE table2 | 外键ALTER专用 |
| - | DROP FOREIGN KEY fname | 外键ALTER专用 |
| - | DROP CONSTRAINT fname | 外键ALTER专用 |
| CHECK | CHECK (column = value) | 定值 - ALTER可用 |
| - | column type CHECK (column = value) | 定值 |
| - | CONSTRAIN cname CHECK (column = value) | 定值命名 - ALTER可用 |
| - | DROP CONSTRAIN cname | 定值ALTER专用 |
| - | DROP CHECK cname | 定值ALTER专用 |
| DEFAULT | DEFAULT dname | 默认值 |
| - | ALTER column SET DEFAULT dname | 默认值ALTER专用 |
| - | ALTER COLUMN column SET DEFAULT dname | 默认值ALTER专用 |
| - | ALTER column DROP DEFAULT | 默认值ALTER专用 |
| - | ALTER COLUMN column DROP DEFAULT | 默认值ALTER专用 |
CREATE INDEX iname ON table (column, DESC /ASC) /允许重复值
CREATE UNIQUE INDEX iname ON table (column, DESC /ASC) /不允许重复值
' DROP INDEX iname ON table
' DROP INDEX table.iname
' DROP INDEX iname
ALTER TABLE table DROP INDEX iname
DROP TABLE table
TRUNCATE TABLE table
ALTER TABLE ADD column type
ALTER TABLE DROP COLUMN column
ALTER TABLE ALTER COLUMN column type
