SQL笔记

DML 和 DDL

可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。
SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。
查询和更新指令构成了 SQL 的 DML 部分：

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。SQL 中最重要的 DDL 语句:

CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

select

SELECT 列名称 FROM 表名称
SELECT * FROM 表名称
SELECT LastName,FirstName FROM Persons

DISTINCT

用于返回唯一不同的值，即去重。

SELECT DISTINCT Company FROM Orders 

where

1. 操作符 描述
2. = 等于
3. <> 不等于
4. > 大于
5. < 小于
6. >= 大于等于
7. <= 小于等于
8. BETWEEN 在某个范围内

9. LIKE 搜索某种模式

   SELECT * FROM Persons WHERE City=’Beijing’
   SELECT * FROM Persons WHERE Year>1965
   SELECT * FROM Persons WHERE FirstName=’Thomas’ AND LastName=’Carter’
   SELECT * FROM Persons WHERE (FirstName=’Thomas’ OR FirstName=’William’) AND LastName=’Carter’

ORDER BY

根据指定的列对结果集进行排序，默认升序排序（ASC）。使用 DESC 关键字降序。

SELECT Company, OrderNumber FROM Orders ORDER BY Company
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

INSERT INTO

INSERT INTO 表名称 VALUES (值1, 值2,....)
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

Update

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' 
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'

DELETE

DELETE 语句用于删除表中的行。
DELETE FROM 表名称 WHERE 列名称 = 值
DELETE FROM Person WHERE LastName = ‘Wilson’

//删除所有行
DELETE FROM table_name  
DELETE * FROM table_name

TOP

SELECT column_name(s) FROM table_name WHERE ROWNUM <= number
SELECT * FROM Persons WHERE ROWNUM <= 5

SELECT TOP 2 * FROM Persons             //选择头两条记录，即前两行
SELECT TOP 50 PERCENT * FROM Persons    //从上面的 "Persons" 表中选取 50% 的记录。

LIKE

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

//从上面的 "Persons" 表中选取居住在以 "N" 开始的城市里的人："%" 可用于定义通配符（模式中缺少的字母）。
SELECT * FROM Persons WHERE City LIKE 'N%'  

//从 "Persons" 表中选取居住在以 "g" 结尾的城市里的人：
SELECT * FROM Persons WHERE City LIKE '%g'

//从 "Persons" 表中选取居住在包含 "lon" 的城市里的人：
SELECT * FROM Persons WHERE City LIKE '%lon%'

//从 "Persons" 表中选取居住在不包含 "lon" 的城市里的人：
SELECT * FROM Persons WHERE City NOT LIKE '%lon%'

通配符

必须与 LIKE 运算符一起使用。可以替代一个或多个字符。

通配符 描述

% 替代一个或多个字符

_ 仅替代一个字符

[charlist] 字符列中的任何单一字符

[^charlist]或者[!charlist] 不在字符列中的任何单一字符

//第一个字符之后是 "eorge" 的人：
SELECT * FROM Persons WHERE FirstName LIKE '_eorge'

SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'

//以 "A" 或 "L" 或 "N" 开头的人：
SELECT * FROM Persons WHERE City LIKE '[ALN]%'

SELECT * FROM Persons WHERE City LIKE '[!ALN]%'

IN

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

//姓氏为 Adams 和 Carter 的人
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

BETWEEN

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

//由上到下
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'

SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'

Alias

为列名称和表名称指定别名（Alias）。

//对表：
SELECT column_name(s) FROM table_name AS alias_name
//对列
SELECT column_name AS alias_name FROM table_name

SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Adams' AND p.FirstName='John'

JOIN

从两个表中获取数据。

1、 INNER JOIN

INNER JOIN 与 JOIN 是相同的，在表中只要存在至少一个匹配时，INNER JOIN 关键字返回行，即（两表匹配的行）。

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName

2、 LEFT JOIN

只要存在匹配，就从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行，即（左表 + 两表匹配的行）。

SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName

3、RIGHT JOIN

与上一点相反。

4、FULL JOIN

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行，即（左表 并 右表）

SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName

UNION

用于合并两个或多个 SELECT 语句的结果集。UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

//可重复
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

SELECT INTO

从一个表中选取数据，然后把数据插入另一个表中，常用于创建表的备份复件或者用于对记录进行存档。

SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename

SELECT * INTO Persons IN 'Backup.mdb' FROM Persons
SELECT LastName,FirstName INTO Persons_backup FROM Persons
SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Beijing'

//从一个以上的表中选取数据
SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P

CREATE

CREATE DATABASE

CREATE DATABASE database_name

CREATE TABLE

CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

integer(size)
int(size)
smallint(size)
tinyint(size)
仅容纳整数。在括号内规定数字的最大位数。

decimal(size,d)
numeric(size,d)
容纳带有小数的数字。
“size” 规定数字的最大位数。”d” 规定小数点右侧的最大位数。

char(size)
容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。
在括号中规定字符串的长度。

varchar(size)
容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。
在括号中规定字符串的最大长度。

date(yyyymmdd) 容纳日期。

Constraints

1、NOT NULL

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

2、UNIQUE

创建表时约束：

CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)

已创建表后约束：

ALTER TABLE Persons ADD UNIQUE (Id_P)

ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

撤销约束：

ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

1. UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
2. PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
3. 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

3、PRIMARY KEY

与unique类似：
创建时：

CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
)

添加：

ALTER TABLE Persons ADD PRIMARY KEY (Id_P)

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

撤销：

ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

4、FOREIGN KEY

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
创建时：

CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)

修改：

ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

撤销：

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

5、CHECK

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

创建时：

CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)

修改时：

ALTER TABLE Persons
ADD CHECK (Id_P>0)

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

撤销：

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

6、DEFAULT

创建时：

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)

修改时：

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

撤销：

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

CREATE INDEX

CREATE INDEX index_name
ON table_name (column_name)

//唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE INDEX PersonIndex
ON Person (LastName) 

//以降序索引某个列中的值
CREATE INDEX PersonIndex
ON Person (LastName DESC) 

//索引不止一个列
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

DROP

DROP INDEX index_name

DROP TABLE 表名称

DROP DATABASE 数据库名称

//仅仅删除表格中的数据
TRUNCATE TABLE 表名称

ALTER

ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name 
DROP COLUMN column_name

ALTER TABLE table_name
ALTER COLUMN column_name datatype

ALTER TABLE Persons
ADD Birthday date

ALTER TABLE Persons
ALTER COLUMN Birthday year

ALTER TABLE Person
DROP COLUMN Birthday

AUTO INCREMENT

会在新记录插入表中时生成一个唯一的数字。

//创建名为 seq_person 的序列对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能 
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

//该函数从 seq_person 序列中取回下一个值）
INSERT INTO Persons (P_Id,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')

VIEW

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

创建：

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products) 

查询：

SELECT * FROM [Products Above Average Price]

创建：

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName 

查询：

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

……

DATE

NULL

无法比较 NULL 和 0；它们是不等价的,IS NULL 和 IS NOT NULL 操作符。

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

//如果 "UnitsOnOrder" 是 NULL，则不利于计算，因此如果值是 NULL 则 NUL() 返回 0。
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) FROM Products

HAVING

一般用在分组中，规定每组应符合的条件。

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

函数

SELECT function(列) FROM 表

avg()

SELECT AVG(column_name) FROM table_name

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

COUNT()

SELECT COUNT(column_name) FROM table_name

SELECT COUNT(*) FROM table_name

SELECT COUNT(DISTINCT column_name) FROM table_name

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Carter'

FIRST、 LAST、MAX、MIN、SUM

SELECT FIRST(column_name) FROM table_name
SELECT LAST(column_name) FROM table_name
SELECT MAX(column_name) FROM table_name
SELECT MIN(column_name) FROM table_name
SELECT SUM(column_name) FROM table_name

GROUP BY

根据一个或多个列对结果集进行分组。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

UCASE\LCASE\LEN

//把字段的值转换为大写。
SELECT UCASE(column_name) FROM table_name
SELECT LCASE(column_name) FROM table_name
SELECT LEN(column_name) FROM table_name

FORMAT()

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products