SQL

终于有时间继续玩阿里的数据挖掘比赛，第二赛季限定工具，不得不先重拾一下数据库。以下笔记总结自网络教程。

---

SQL简介

• SQL指结构化查询语言
• SQL是一种ANSI的标准计算机语言，存在不同的版本，但不同版本都支持一些共同的关键词。

SQL 语法

• 一个数据库通常包含一个或多个表。
• SQL 对大小写不敏感

• 某些数据库系统要求在每条 SQL 命令的末端使用分号

• SQL DML 和 DDL

  把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)

  • 查询和更新指令构成了 SQL 的 DML 部分：

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

  • SQL 中最重要的 DDL 语句:

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

SELECT 语句

    SELECT column_name1,column_name2 FROM table_name
    SELECT * FROM table_name

SELECT DISTINCT 语句

关键词 DISTINCT 用于返回唯一不同的值。

SELECT DISTINCT column_name FROM table_name

WHERE 子句

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。

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

AND & OR 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

SELECT * FROM table_name WHERE column_name1=‘XXX‘ AND column_name2=‘XXX‘

SELECT * FROM table_name WHERE (column_name1=‘XXX‘ OR column_name2=‘XXX‘) AND column_name3=‘XXX‘

ORDER BY 子句

ORDER BY 语句用于根据指定的列对结果集进行排序，默认按照升序对记录进行排序，如果希望按照降序对记录进行排序，可以使用 DESC 关键字。

SELECT column_name1, column_name2 FROM table_name ORDER BY column_name1 [DESC]

INSERT INTO 语句

INSERT INTO 语句用于向表格中插入新的行

INSERT INTO 表名称 VALUES (值1, 值2,....)

也可以只在指定列插入数据，没插入数据的列留空：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

UPDATE 语句

Update 语句用于修改表中的数据。

UPDATE 表名称 SET 列名称1 = 新值1，列名称2 = 新值2 WHERE 列名称 = 某值

DELETE 语句

DELETE 语句用于删除表中的行

DELETE FROM 表名称 WHERE 列名称 = 值

TOP 子句

TOP 子句用于规定要返回的记录的数目。

• SQL Server 的语法：

  SELECT TOP number|percent column_name(s) FROM table_name
  

• MySQL 语法

  SELECT column_name(s) FROM table_name LIMIT number
  

LIKE 操作符

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

举例：

SELECT * FROM Persons
WHERE City LIKE ‘N%‘ —— "%" 可用于定义通配符（模式中缺少的字母）

通配符

% 替代一个或多个字符

_ 仅替代一个字符

IN 操作符

IN 操作符允许我们在 WHERE 子句中规定多个值。

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

BETWEEN 操作符

在 WHERE 子句中使用，作用是选取介于两个值之间的数据范围。

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

JOIN

join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

SELECT table1.column1, table1.column2, table2.column
FROM table1 INNER JOIN table2
ON table1.column = table2.column

• 除了 INNER JOIN（内连接），我们还可以使用其他几种连接。下面列出了可以使用的 JOIN 类型，以及它们之间的差异。

  JOIN: 如果表中有至少一个匹配，则返回行(INNER JOIN 与 JOIN 是相同的。)
  LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
  RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
  FULL JOIN: 只要其中一个表中存在匹配，就返回行
  

UNION 和 UNION ALL 操作符

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

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL：

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

按列拼接两条select语句的结果

SELECT INTO 语句

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

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

或者只把希望的列插入新表：

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

CREATE DATABASE 语句

CREATE DATABASE database_name

CREATE TABLE 语句

CREATE TABLE 语句用于创建数据库中的表。

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

数据类型（data_type）规定了列可容纳何种数据类型。下面的表格包含了SQL中最常用的数据类型：

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

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

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

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

date(yyyymmdd) 容纳日期。

NOT NULL 约束

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

PRIMARY KEY 约束

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

下面的 SQL 在 “Persons” 表创建时在 “Id_P” 列创建 PRIMARY KEY 约束：

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

FOREIGN KEY 约束

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

CHECK 约束

CHECK 约束用于限制列中的值的范围。

下面的 SQL 在 “Persons” 表创建时为 “Id_P” 列创建 CHECK 约束。CHECK 约束规定 “Id_P” 列必须只包含大于 0 的整数。

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 约束用于向列中插入默认值

下面的 SQL 在 “Persons” 表创建时为 “City” 列创建 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 INDEX 语句

CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

您可以在表中创建索引，以便更加快速高效地查询数据。
用户无法看到索引，它们只能被用来加速搜索/查询。
注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

• 在表上创建一个简单的索引。允许使用重复的值：

  CREATE INDEX index_name
  ON table_name (column_name) 可以有多个column
  

• 在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

  CREATE UNIQUE INDEX index_name
  ON table_name (column_name1，column_name2)
  

DROP

DROP 语句，可以轻松地删除索引、表和数据库。

• 使用 DROP INDEX 命令删除表格中的索引。

  DROP INDEX index_name ON table_name
  

• DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）

  DROP TABLE 表名称
  

• DROP DATABASE 语句用于删除数据库：

  DROP DATABASE 数据库名称
  

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
  

Date 函数

NULL 值

NULL 值是遗漏的未知数据。
默认地，表的列可以存放 NULL 值。
IS NULL 和 IS NOT NULL 操作符。

SQL 服务器 - RDBMS

现代的 SQL 服务器构建在 RDBMS 之上。

• DBMS - 数据库管理系统（Database Management System）

数据库管理系统是一种可以访问数据库中数据的计算机程序。
DBMS 使我们有能力在数据库中提取、修改或者存贮信息。
不同的 DBMS 提供不同的函数供查询、提交以及修改数据。

• RDBMS - 关系数据库管理系统（Relational Database Management System）

关系数据库管理系统 (RDBMS) 也是一种数据库管理系统，其数据库是根据数据间的关系来组织和访问数据的。
20 世纪 70 年代初，IBM 公司发明了 RDBMS。
RDBMS 是 SQL 的基础，也是所有现代数据库系统诸如 Oracle、SQL Server、IBM DB2、Sybase、MySQL 以及 Microsoft Access 的基础。

SQL 函数

SQL 拥有很多可用于计数和计算的内建函数。

内建 SQL 函数的语法是：

SELECT function(列) FROM 表

• AVG 函数

AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

SELECT AVG(column_name) FROM table_name

• COUNT() 函数

COUNT() 函数返回匹配指定条件的行数，NULL 不计入。

SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) 语法返回表中的记录数：

SELECT COUNT(*) FROM table_name

COUNT(DISTINCT column_name) 语法返回指定列的不同值的数目：

SELECT COUNT(DISTINCT column_name) FROM table_name

• FIRST() 函数

FIRST() 函数返回指定的字段中第一个记录的值。

SELECT FIRST(column_name) FROM table_name

• LAST() 函数

LAST() 函数返回指定的字段中最后一个记录的值。

SELECT LAST(column_name) FROM table_name

• MAX() 函数、MIN() 函数

返回一列中的最大、小值。NULL 值不包括在计算中。

SELECT MIN(column_name) FROM table_name

• SUM() 函数

SUM 函数返回数值列的总数（总额）。

SELECT SUM(column_name) FROM table_name

• GROUP BY 语句

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

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

• HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

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

例子：

现在我们希望查找客户 “Bush” 或 “Adams” 拥有超过 1500 的订单总金额。

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

• UCASE() 函数、LCASE() 函数

UCASE 函数把字段的值转换为大写。

LCASE 函数把字段的值转换为小写。

SELECT LCASE(column_name) FROM table_name

• MID() 函数

MID 函数用于从文本字段中提取字符。

SELECT MID(column_name,start[,length]) FROM table_name

• LEN() 函数

LEN 函数返回文本字段中值的长度。

SELECT LEN(column_name) FROM table_name

• ROUND() 函数

ROUND 函数用于把数值字段舍入为指定的小数位数。

SELECT ROUND(column_name,decimals) FROM table_name

decimals规定要返回的小数位数。

• NOW() 函数

NOW 函数返回当前的日期和时间。

SELECT NOW() FROM table_name

• FORMAT() 函数

FORMAT 函数用于对字段的显示进行格式化。

SELECT FORMAT(column_name,format) FROM table_name

format规定格式

SQL

标签：数据库