SQL笔记 - 基础

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