SQL常见语句总结

SQL笔记

sql关键字不区分大小写，在某些设置中，表和列名称区分大小写

基础

查找 SELECT

SELECT 列名称 FROM 表名称

去重 DISTINCT

SELECT DISTINCT 列名 FROM 表名
放在要去重的列前面

WHERE子句

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

操作符	描述
=	等于
<>或者!=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式
AND 和 OR 运算符	基于一个以上的条件对记录进行过滤

SELECT * FROM Persons WHERE City=‘Beijing’
文本值用单引号，数值不用引号

AND 和 OR 运算符

AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤。
AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。
如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

查找名字

查找学校是北京大学的两种方法
select device_id,university from user_profile where university=‘北京大学’;
SELECT device_id,university FROM user_profile WHERE university LIKE ‘%北京大学%’

ORDER BY 子句

ORDER BY 语句用于对结果集进行排序。
默认升序，降序用DESC关键字
SELECT Company, OrderNumber FROM Orders ORDER BY Company
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

插入 INSERT INTO……VALUES()

INSERT INTO 表名称 VALUES (值1, 值2,…)
INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)
插入一行
INSERT INTO Persons VALUES (‘Gates’, ‘Bill’, ‘Xuanwumen 10’, ‘Beijing’)
插入对应列
INSERT INTO Persons (LastName, Address) VALUES (‘Wilson’, ‘Champs-Elysees’)

修改更新 UPDATE……SET……WHERE……

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
更新某一行中的一个列
我们为 lastname 是 “Wilson” 的人添加 firstname：
UPDATE Person SET FirstName = ‘Fred’ WHERE LastName = ‘Wilson’
更新某一行中的若干列
我们会修改地址（address），并添加城市名称（city）：

UPDATE Person SET Address = ‘Zhongshan 23’, City = ‘Nanjing’ WHERE LastName = ‘Wilson’

删除 DELETE

DELETE FROM 表名称 WHERE 列名称 = 值
删除某行
DELETE FROM Person WHERE LastName = ‘Wilson’
删除所有行
DELETE * FROM table_name

高级

查询前几行

MYSQL用limit限制

select device_id from user_profile limit 2

SQL用TOP

从上面的 “Persons” 表中选取头两条记录。
SELECT TOP 2 * FROM Persons
从上面的 “Persons” 表中选取 50% 的记录。
SELECT TOP 50 PERCENT * FROM Persons

Oracle用ROWNUM

SELECT *
FROM Persons
WHERE ROWNUM <= 5

like模糊查询

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
从 “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%’

not关键字

使用 NOT 关键字，我们可以从 “Persons” 表中选取居住在不包含 “lon” 的城市里的人：
SELECT * FROM Persons
WHERE City NOT LIKE ‘%lon%’

SQL通配符

通配符	描述
%	代表零个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	不在字符列中的任何单一字符

_通配符

从 “Persons” 表中选取名字的第一个字符之后是 “eorge” 的人：
SELECT * FROM Persons
WHERE FirstName LIKE ‘_eorge’
从 “Persons” 表中选取的这条记录的姓氏以 “C” 开头，然后是一个任意字符，然后是 “r”，然后是一个任意字符，然后是 “er”：
SELECT * FROM Persons
WHERE LastName LIKE ‘C_r_er’

[charlist] 通配符

从上面的 “Persons” 表中选取居住的城市以 “A” 或 “L” 或 “N” 开头的人：
SELECT * FROM Persons
WHERE City LIKE ‘[ALN]%’
从上面的 “Persons” 表中选取居住的城市不以 “A” 或 “L” 或 “N” 开头的人：
SELECT * FROM Persons
WHERE City LIKE ‘[!ALN]%’

IN操作符(判断是否在这个范围)

IN 操作符允许我们在 WHERE 子句中规定多个值。
从上表中选取姓氏为 Adams 和 Carter 的人：
SELECT * FROM Persons
WHERE LastName IN ('Adams ','Carter ')

BETWEEN操作符(BETWEEN……AND)

BETWEEN 操作符在 WHERE 子句中使用，作用是选取介于两个值之间的数据范围。
以字母顺序显示介于 “Adams”（包括）和 “Carter”（不包括）之间的人，请使用下面的 SQL：
SELECT * FROM Persons
WHERE LastName
BETWEEN ‘Adams’ AND ‘Carter’
第一个为Adams，第三个Carter，最后只显示第一个和第二个，BETWEEN……AND是左闭右开

NOT操作符

例子显示范围之外的人，使用 NOT 操作符：
SELECT * FROM Persons
WHERE LastName
NOT BETWEEN ‘Adams’ AND ‘Carter’

起别名（Alias）关键字 as

表、列都可以写别名

select device_id as user_infors_example from user_profile limit 0,2;
这里主要是用到了 起别名关键字 as 以及组合限制查询 limit 索引,个数
其中as可以省略,索引为0可以省略

假设我们有两个表分别是：“Persons” 和 “Product_Orders”。我们分别为它们指定别名 “p” 和 “po”。现在，我们希望列出 “John Adams” 的所有定单。
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName=‘Adams’ AND p.FirstName=‘John’

JOIN联表查询

join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
例如通过主键（唯一）将两个不同的表联系起来。
两个表，一个Person表，一个Orders表
Person表包括Id_P、LastName、FirstName、Address、City
Orders表包括Id_O、OrderNo、Id_P
通过Id_P、Id_O联系

JOIN类型	描述
JOIN或者INNER JOIN（内连接）	在表中存在至少一个匹配时，INNER JOIN 关键字返回行
LEFT JOIN（左连接）	即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN（右链接）	即使左表中没有匹配，也从右表返回所有的行
FULL JOIN	只要其中一个表中存在匹配，就返回行

普通方法（引用两个表）

我们可以通过引用两个表的方式，从两个表中获取数据：
谁订购了产品，并且他们订购了什么产品？
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P

使用 Join/INNER JOIN

表中存在至少一个匹配时，INNER JOIN 关键字返回行
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

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

使用LEFT JOIN

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
注：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。

使用RIGHT JOIN

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

FULL JOIN 关键字

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

注：在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
就是把两个表中的Persons.LastName, Persons.FirstName, Orders.OrderNo全部显示出来

UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
注：UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

UNION（无重复值）

默认地，UNION 操作符选取不同的值。
列出所有在中国和美国的不同的雇员名：
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

UNION ALL（有重复值）

允许重复的值，使用 UNION ALL。
列出在中国和美国的所有的雇员：
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

SELECT……INTO（创建表的备份附件）

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename
IN 子句可用于向另一个数据库中拷贝表：
SELECT *
INTO Persons IN ‘Backup.mdb’
FROM Persons
希望拷贝某些域，可以在 SELECT 语句后列出这些域：
SELECT LastName,FirstName
INTO Persons_backup
FROM Persons
也可以通过where查询后的结果或者连接表写入新表

数据库操作

创建数据库CREATE DATABASE

CREATE DATABASE

创建数据库表CREATE TABLE

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
…
)

数据类型	描述
integer(size)、int(size)、smallint(size)、tinyint(size)	仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d)、numeric(size,d)	容纳带有小数的数字。“size” 规定数字的最大位数。“d” 规定小数点右侧的最大位数。
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。
date(yyyymmdd)	容纳日期。

创建名为 “Person” 的表。
该表包含 5 个列，列名分别是：“Id_P”、“LastName”、“FirstName”、“Address” 以及 “City”：

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

可使用 INSERT INTO 语句向空表写入数据。

SQL 约束

约束用于限制加入表的数据的类型。
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

约束类型	描述
NOT NULL	非空
UNIQUE	唯一
PRIMARY KEY	主键
FOREIGN KEY	外键
CHECK	限制列中的值的范围
DEFAULT	默认值

NOT NULL 约束（非空）

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
下面的 SQL 语句强制 “Id_P” 列和 “LastName” 列不接受 NULL 值：

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

UNIQUE 约束（唯一约束）

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

建表同时加唯一约束

下面的 SQL 在 “Persons” 表创建时在 “Id_P” 列创建 UNIQUE 约束：
MySQL:

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

SQL Server、Oracle、 MS Access:

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

命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束，使用下面的 SQL 语法：

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)
)

建表后再添加唯一约束

当表已被创建时，如需在 “Id_P” 列创建 UNIQUE 约束，使用下列 SQL：

ALTER TABLE Persons
ADD UNIQUE (Id_P)

命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，使用下面的 SQL 语法：

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

撤销UNIQUE约束

MySQL:

ALTER TABLE Persons
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

PRIMARY KEY 约束（主键）

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

MySQL:（在最后一行写）

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

SQL Server / Oracle / MS Access:（在当前行最后面写）

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

FOREIGN KEY 约束（外键）

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

下面的 SQL 在 “Orders” 表创建时为 “Id_P” 列创建 FOREIGN KEY：
MySQL：

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

SQL Server / Oracle / MS Access:

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

CHECK 约束

CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
下面的 SQL 在 “Persons” 表创建时为 “Id_P” 列创建 CHECK 约束。CHECK 约束规定 “Id_P” 列必须只包含大于 0 的整数。
MySQL：

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

DEFAULT 约束（默认值）

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'
)