临时抱佛脚学SQL,在这边做一些整理。部分实例摘抄自W3School.由于是有PYTHON pandas的基础上看SQL,感觉两者胡同的地方是很多的。
NOTE:关于SQL的函数应用,可以参考博文SQL函数学习笔记;关于SQL语句的执行顺序,可以参考博文SQL语句执行顺序。
1 基本结构
可以把 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 - 删除索引
NOTE:SQL 对大小写不敏感!
2 基本SQL语句
2.1 SELECT语句
SELECT 列名称 FROM 表名称
2.2 SELECT DISTINCT语句
找出表中唯一不同的值并返回。
SELECT DISTINCT 列名称 FROM 表名称
2.3 SQL WHERE
WHERE有条件地从表中取出数据。
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
操作符 | 描述 |
---|---|
= | 等于 |
<> | 不等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
BETWEEN | 在某个范围内 |
LIKE | 搜索某种模式 |
NOTE:SQL 使用单引号来环绕文本值(大部分数据库系统也接受双引号)。如果是数值,请不要使用引号。
2.3.1 LIKE操作符
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
在搜索数据库中的数据时,SQL 通配符可以替代一个或多个字符。在 SQL 中,可使用以下通配符:
通配符 | 描述 |
---|---|
% | 替代一个或多个字符 |
_ | 仅替代一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist]或者[!charlist] | 不在字符列中的任何单一字符 |
% NOTES:(_相似)
- pattern中以xx开始:LIKE 'xx%'
- 以xx结束:LIKE '%xx'
- 带有xx:LIKE '%xx%'
- 不带xx:NOT LIKE '%xx%'
例:表Person
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
从 "Persons" 表中选取居住在包含 "lon" 的城市里的人:
SELECT * FROM Persons
WHERE City LIKE '%lon%'
从 "Persons" 表中选取居住在不包含 "lon" 的城市里的人:
SELECT * FROM Persons
WHERE City NOT LIKE '%lon%'
[charlist] 通配符:
例:
从 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人:
SELECT * FROM Persons
WHERE City LIKE '[ALN]%'
结果:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
2.3.2 IN操作符
IN 操作符允许我们在 WHERE 子句中规定多个值。
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
2.3.3 BETWEEN操作符
操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
NOTE:不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。某些数据库会列出介于 VALUE1 和 VALUE2 之间的人,但不包括 VALUE1 和 VALUE2 ;某些数据库会列出介于 VALUE1 和 VALUE2之间并包括 VALUE1和VALUE2的人;而另一些数据库会列出介于 VALUE1 和 VALUE2之间的人,包括 VALUE1 ,但不包括VALUE2。所以,需要数据库是如何处理 BETWEEN....AND 操作符的!
2.4 AND$OR运算符
如果第一个条件和第二个条件都成立,则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立,则 OR 运算符显示一条记录。
例:表Person如下
执行语句:
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')
AND LastName='Carter'
结果:
LastName | FirstName | Address | City |
---|---|---|---|
Carter | Thomas | Changan Street | Beijing |
Carter | William | Xuanwumen10 | Beijing |
2.5 ORDER BY子句
ORDER BY 语句用于根据指定的列对结果集进行排序,默认按照升序对记录进行排序。如果希望按照降序对记录进行排序,可以使用 DESC 关键字。
例:表Orders
Company | OrderNumber |
---|---|
IBM | 3532 |
W3School | 2356 |
Apple | 4698 |
W3School | 6953 |
执行语句:
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
结果:
Company | OrderNumber |
---|---|
W3School | 2356 |
W3School | 6953 |
IBM | 3532 |
Apple | 4698 |
2.6 INSERT INTO语句(表中插入数据)
INSERT INTO 表名称 VALUES (值1, 值2,....)
也可以指定所要插入数据的列:
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
2.7 UPDATE语句(修改表中数据)
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
修改若干列的例子:表Person
LastName | FirstName | Address | City |
---|---|---|---|
Gates | Bill | Xuanwumen 10 | Beijing |
Wilson | Champs-Elysees |
执行语句:
UPDATE Person SET FirstName='Fred',Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'
结果:
LastName | FirstName | Address | City |
---|---|---|---|
Gates | Bill | Xuanwumen 10 | Beijing |
Wilson | Fred | Zhongshan23 | Nanjing |
2.8 DELETE语句(删除整个表内的行)
DELETE FROM 表名称 WHERE 列名称 = 值
删除所有行:
DELETE FROM 表名
或者
DELETE * FROM 表名
3 SQL高级用法
3.1 TOP子句
TOP 子句用于规定要返回的记录的数目。
3.1.1 SQL Server 的语法
SELECT TOP number|percent column_name(s)
FROM table_name
3.1.2 MySQL 语法
SELECT column_name(s)
FROM table_name
LIMIT number
3.1.3 Oracle语法
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number
3.2 SQL Alias(别名)
通过使用 SQL,可以为列名称和表名称指定别名(Alias)。
表的 SQL Alias 语法:
SELECT column_name(s)
FROM table_name
AS alias_name
列的 SQL Alias 语法:
SELECT column_name AS alias_name
FROM table_name
例:假设我们有两个表分别是:"Persons" 和 "Product_Orders"。我们分别为它们指定别名 "p" 和 "po"。
现在,我们希望列出 "John Adams" 的所有定单。我们可以使用下面的 SELECT 语句:
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'
3.3 SQL JOIN
SQL join 用于根据两个或多个表中的列之间的关系,从这些表中查询数据。这里感觉和pandas的merge()功能高度相似。类比着理解可以轻松很多。
3.3.1 法一:通过主键
数据库中的表可通过键将彼此联系起来。主键(Primary Key)是一个列,在这个列中的每一行的值都是唯一的。在表中,每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下,把表间的数据交叉捆绑在一起。
例:表Person
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
"Id_P" 列是 Persons 表中的的主键。这意味着没有两行能够拥有相同的 Id_P。即使两个人的姓名完全相同,Id_P 也可以区分他们。
表Orders
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
"Id_O" 列是 Orders 表中的的主键,同时,"Orders" 表中的 "Id_P" 列用于引用 "Persons" 表中的人,而无需使用他们的确切姓名。"Id_P" 列把上面的两个表联系了起来。
执行语句:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P
结果:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
3.3.2 法二:使用Join
如果我们希望列出所有人的定购,可以使用下面的 SELECT 语句:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
结果同上。
NOTE:INNER JOIN 与 JOIN 是相同的。
3.3.3 不同的 SQL JOIN
除了我们在上面的例子中使用的 INNER JOIN(内连接),我们还可以使用其他几种连接。
下面列出了您可以使用的 JOIN 类型,以及它们之间的差异。这里和pandas的merge()函数里面,how='(inner/outer/left/right)'参数用法基本上是一致的。
- JOIN: 如果表中有至少一个匹配,则返回行
- LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
SELECT column_name(s)
FROM table_name1 --左连接的表,以它为准
LEFT JOIN table_name2 --被连接的表
ON table_name1.column_name=table_name2.column_name
- RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
- FULL JOIN: 只要其中一个表中存在匹配,就返回行
3.4 SQL UNION 和 UNION ALL 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
NOTE:UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
NOTE:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
3.5 SELECT INTO语句
SELECT INTO 语句从一个表中选取数据,然后把数据插入另一个表中,常用于创建表的备份复件或者用于对记录进行存档。
SELECT */column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename
--后面可以接WHERE、JOIN等
3.6 CREATE语句
3.6.1 CREATE DATABASE 语句(创建数据库)
CREATE DATABASE database_name
3.6.2 CREATE TABLE 语句(创建数据库中的表)
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
数据类型 | 描述 |
---|---|
| 仅容纳整数。在括号内规定数字的最大位数。 |
| 容纳带有小数的数字。 "size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。 |
char(size) | 容纳固定长度的字符串(可容纳字母、数字以及特殊字符)。 在括号中规定字符串的长度。 |
varchar(size) | 容纳可变长度的字符串(可容纳字母、数字以及特殊的字符)。 在括号中规定字符串的最大长度。 |
date(yyyymmdd) | 容纳日期。 |
- SQL约束
约束用于限制加入表的数据的类型。可以在创建表时规定约束(通过 CREATE TABLE 语句),或者在表创建之后也可以(通过 ALTER TABLE 语句)。
SQL约束主要包括以下几种约束:
- NOT NULL:约束强制列不接受 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 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。PRIMARY KEY 拥有自动定义的 UNIQUE 约束。NOET:每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
例:需要命名 UNIQUE 约束,以及为多个列定义 UNIQUE 约束(MySQL / SQL Server / Oracle / MS Access:):
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)
)
例:UNIQUE Constraint on ALTER TABLE
- 当表已被创建时,如需在 "Id_P" 列创建 UNIQUE 约束(MySQL / SQL Server / Oracle / MS Access:):
ALTER TABLE Persons
ADD UNIQUE (Id_P)
2.需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束(MySQL / SQL Server / Oracle / MS Access:):
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 值。每个表都应该有一个主键,并且每个表只能有一个主键。
- FOREIGN KEY:一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。FOREIGN KEY 约束用于预防破坏表之间连接的动作。FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
- CHECK:CHECK 约束用于限制列中的值的范围。如果对单个列定义 CHECK 约束,那么该列只允许特定的值。如果对一个表定义 CHECK 约束,那么此约束会在特定的列中对值进行限制。
- DEFAULT:DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值,那么会将默认值添加到所有的新记录。
3.6.3 CREATE INDEX 语句
CREATE INDEX 语句用于在表中创建索引。在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据。
SQL CREATE INDEX 语法
CREATE INDEX index_name
ON table_name (column_name)
SQL CREATE UNIQUE INDEX 语法
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
3.7 DROP语句
3.7.1 DROP TABLE语句(删除整个表)
DROP TABLE 表名称
3.7.2 DROP DATABASE
DROP DATABASE 数据库名称
3.7.3 TRUNCATE TABLE 语句(删除表内数据)
TRUNCATE TABLE 表名称
3.7.4 DROP INDEX语句
不同数据库用法不同。
3.8 ALTER TABLE语句
在表中添加列:
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
3.9 AUTO INCREMENT 字段
Auto-increment 会在新记录插入表中时生成一个唯一的数字。
3.10 CREATE VIEW语句
CREATE VIEW语句:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
更新视图:
SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
删除视图:
SQL DROP VIEW Syntax
DROP VIEW view_name
3.11 NULL
NOTE:
- 无法比较 NULL 和 0;它们是不等价的。
- 无法使用比较运算符来测试 NULL 值,比如 =, <, 或者 <>。
- 我们必须使用 IS NULL 和 IS NOT NULL 操作符。