SQL（结构化查询语言）

---

SQL(结构化查询语言) 是用于访问和处理数据库的计算机语言

结构化的查询语句
方便我们访问数据库

SQL

• 面向数据库执行查询
• 从数据库中取回数据
• 在数据库中插入记录
• 更新数据库中数据
• 从数据库中删除记录
• 创建新数据库
• 在数据库中创建新表
• 在数据库中创建存储过程
• 在数据库中创建视图
• 设置表、存储过程和视图的权限

SQL是一种ANSI定制的标准
大部分SQL数据库程序会以相似的支持关键字，同时也有自己的扩展

SQL对大小写不敏感

SQL语句后的分号

分号的作用是在数据库系统中分隔每条SQL语句的标准方法，

SQL DML 和 DDL

SQL分两部分：

DML（数据操作语言）
DDL（数据定义语言）

• DML 操作表中数据

  • select 从数据库表中获取数据
  • update 更新数据库表中数据
  • delete 从数据库表中删除数据
  • insert into 向数据库表中插入数据

• DDL操作数据库相关的数据库，表，索引等非数据部分

  • create database -创建新数据库
  • alter database -修改数据库
  • create table -创建新表
  • alter table -修改表
  • drop table -删除表
  • create index -创建索引
  • drop index -删除索引

SQL where字句注意事项：

条件值周围使用的是’单引号’

如果是数值，不需要使用引号

在where中，如果需要更复杂的表达式，需要用”()”来括起来

SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')
AND LastName='Carter'

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照升序对记录进行排序。

如果希望按照降序对记录进行排序，可以使用 DESC 关键字。(升序ASC，降序DESC)

1.以升序显示Company列内容：
SELECT Company, OrderNumber FROM Orders ORDER BY Company 

2.以升序显示Company列内容，以升序显示OrderNumber列内容:
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
注：当有多个列需要排序时，会先按照前面列的要求排序，当前面列有相同值时，再对相同值按照后面列顺序进行排序，（遇到null也是这样）

3.按照降序显示Company内容，按照升序显示OrderNumber内容：
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 table_name (列1, 列2,...) VALUES (值1, 值2,....)
    例：INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

UPDATE语句

用于修改表中行内容

语法：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

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

DELETE语句

用于删除表中的行

语法：

DELETE FROM 表名称 WHERE 列名称 = 值


删除LastName为‘Wilson’的行
DELETE FROM Person WHERE LastName = 'Wilson'

不删除表的情况下，删除表中所有内容
DELETE FROM table_name 
或者
DELETE * FROM table_name 

返回前N条数据

MySQL语法：
SELECT column_name(s) FROM table_name LIMIT number

Oracle：
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number

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

“Persons” 表中选取居住在以 “N” 开始的城市里的人：

SELECT * FROM Persons WHERE City LIKE 'N%'

通过使用 NOT 关键字，从 “Persons” 表中选取居住在不包含 “lon” 的城市里的人：

SELECT * FROM Persons WHERE City NOT LIKE '%lon%'
注："%" 可用于定义通配符（模式中缺少的字母）。

SQL通配符

SQL 通配符必须与 LIKE 运算符一起使用

IN 操作符

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

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

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

BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

NOT BETWEEN … AND

Alias（别名）

AS为关键字

表名的别名：
SELECT column_name(s) FROM table_name AS alias_name
列名的别名：
SELECT column_name AS alias_name FROM table_name

例子：
假设我们有两个表分别是：”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'

不使用别名的 SELECT 语句：

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

所以，使用别名可以是SQL语句更易于阅读和书写

主键

主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。

从两个表Persons，和Orders表中，获取主键一样的几列数据：（结果组成一个结果集）
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons,Orders WHERE Persons.Id_P = Orders.Id_P 

也可以使用Join来

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

下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。

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

例：

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

SQL UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集

语法：

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

注：UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条SELECT语句中的列的顺序必须相同。

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

SELECT…INTO…

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

常用于创建表的备份复件或者用于对记录进行存档。

语法：
将指定列插入新表（也可以是 * 所有的列）

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

例：

SELECT LastName,FirstName
INTO Persons_backup
FROM Persons

例：从多个表中选取数据（例子中用的是inner join）

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P

CREATE DATABASE 语句

语法：

CREATE DATABASE database_name

CREATE TABLE

语法：

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

例：

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

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

Constraints

约束用于限制加入表的数据的类型

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）

主要有下面几种约束：

- NOT NULL  # NOT NULL约束强制列不接受NULL值,这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
- UNIQUE    # UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
- PRIMARY KEY 每张表只有一个主键约束，且主键不能为null值
**注**：每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束
- FOREIGN KEY
- CHECK
- DEFAULT

NOT NULL例子 :

CREATE TABLE Persons
(
Id_P int NOT NULL,  # Id_P 列值不能为空
LastName varchar(255) NOT NULL,  # LastName列值不能为空
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

UNIQUE约束 on create table

Oracle语法:
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命名？

如果需要命名 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,和LastName做unique约束，并将unique约束命名为uc_PersonID，

在Alter table时的unique

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

ALTER TABLE Persons ADD UNIQUE (Id_P)

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束：

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

撤销 UNIQUE 约束

Oracle：

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

撤销掉一列(没有命名)的unique怎么操作？

Primary Key

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 约束用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

CHECK约束

创建多个check约束时：

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

DEFAULT约束

DEFAULT 约束用于向列中插入默认值

如果没有规定其他的值，那么会将默认值添加到所有的新记录

Oracle：

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

删除DEFAULT约束：

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

CREATE INDEX

在表中创建索引，以便更加快速高效地查询数据（用户无法看到索引）

注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

CREATE INDEX 语法

在表上创建一个简单的索引。允许使用重复的值:
CREATE INDEX index_name
ON table_name (column_name)  # "column_name" 为需要索引的列

CREATE UNIQUE INDEX 语法

在表上创建一个唯一的索引。唯一的索引意味着该列不能拥有相同的索引值
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

1.创建一个简单的索引，名为 “PersonIndex”，在 Person 表的 LastName 列

create index PersonIndex on Person (LastName)

2.创建以降序索引某列的值，（使用DESC）

create index PearsonIndex on Person (LastName DESC)

3.若创建索引不止一列，在括号中列出所需列的名称，用逗号隔开：

create index PersonIndex on Person (LastName, FirstName)

DROP (删除索引，表及数据库)

Oracle:

# 删除索引：	
drop index index_name
# 删除表	
drop table table_name (会删除表的结构，属性以及索引)
# 删除数据库
drop database database_name

仅清空表中数据，不删除表：

truncate table table_name # 和hbase使用方法一样

ALTER

ALTER TABLE 语句用于在已有的表中添加、修改或删除列

alter table table_name add column_name data_type

删除表中的列：

alter table table_name drop column column_name
# 并不是所有的数据库系统都支持在数据库中删除列的方式

改变表中列的数据类型：

alter table table_name alter column column_name data_type

auto increment字段

auto-increment 会在新记录插入表中时生成一个唯一数字

Oracle：

# oracle通过sequence创建auto-increment字段（该对象生成数字序列）
create sequence seq_person
minvalue 1
start with 1
increment by 1
cache 10

上面内容，创建名为seq_person的序列对象，以1起始并以1递增，通过缓存10个值以提高性能

在Persons表中插入新记录，必须使用nextval函数（该函数从定义的seq_person序列中获取下一个值）：

insert into Persons (P_Id,FirstName,LastName) Values (seq_person.nextval, 'Lars', 'Monsen')

上面的SQL语句，会在“Persons”表中插入一条新记录。‘P_Id’的值来自seq_person序列的下一个数字

VIEW(视图)

视图是一个结果集，视图的字段时一个或多个表的字段组成的

SQL CREATE VIEW语法：

create view view_name as
select column_name(s) from table_name where [condition]

注：视图总显示最近的数据

create view [Current Product List] as select ProductID,ProductName from Products where Discontinued=No

更新视图：

create / replace view view_name as 
select column_name(s) from table_name
where condition

例：

我们向“Current Product List”视图中添加“Category”列，语句：

create view [Current Product List] as select
ProductID,ProductName,Category from Products
where Disconinued=No

删除视图：

drop view view_name