SQL简介及常用SQL

一、简介

SQL(Structured Query Language)是一种结构化查询语言，包含：数据定义语言(DDL：CREATE、DROP、ALTER)，数据操纵语言(DML：INSERT、UPDATE、DELETE)，数据控制语言(DCL：GRANT、REVOKE)。

二、数据定义语句

数据定义语句可以创建、删除或修改表、视图和索引等。

1、创建表

CREATE TABLE <表名>(
	<列名1> <列数据类型> [列完整性约束],
	<列名2> <列数据类型> [列完整性约束],
	……
	[表级完整性约束]	
)

样例：

创建表并定义主键和外键：

create table Customer(
	id int primary key,
	name varchar(30) not null, 
	phone varchar(11),
	city varchar(50)
);

create table Product(
	id int primary key,
	number varchar(36),
	name varchar(100)
);

create table Orders(
	id bigint primary key,
	amount numeric(10, 2) not null,
	createdate datetime not null,
	customId int not null,
	foreign key(customId) references Customer(id)
);

create table OrderDetail(
	orderId bigint,
	productId int,
	quantity int not null,
	primary key(orderId, productId),
	foreign key(orderId) references Orders(id),
	foreign key(productId) references Product(id)
);

常用完整性约束：
- 主键约束：PRIMARY KEY
- 唯一性约束：UNIQUE
- 非空约束：NOT NULL
- 参照完整性约束(外键)：FOREIGN KEY
- 用户自定义完整性约束：CHECK (约束条件)

2、修改表

ALTER  TABLE  <表名>
   [ADD  <新列名> <数据类型> [列完整性约束]]
   [DROP COLUMN <列名>]
   [MODIFY <列名> <新的数据类型>]
   [ADD CONSTRAINT <表级完整性约束>]
   [DROP CONSTRAINT <表级完整性约束>]];

样例：

增加列：

alter table Product add price numeric(10, 2);

修改列；

alter table Customer modify phone varchar(13) default '';

删除列：

alter table Product drop column price;

3、删除表

DROP TABLE <表名> ［RESTRICT | CASCADE］

说明：

RESTRICT表示要删除的表不能被其他表的约束所引用(CHECK、FOREIGN KEY等)，不能有视图、触发器、存储过程或函数等。

CASCADE表示删除表时将相关的依赖对象一起删除。

样例：

DROP TABLE OrderDetail RESTRICT

4、索引

需要建索引的情况
- 搜索频繁的列上创建索引，可以加快搜索的速度
- 经常用于表之间连接的列上(外键)创建索引，可以加快连接的速度
- 经常需要根据范围进行搜索的列(索引已排序，其指定的范围是连续的)
- 经常需要排序的列
不需要建索引的情况
- 在查询中很少使用的列
- 只有很少数据值的列
- text、iamge等类型的列
- 当修改时的性能远远大于检索时的性能时
创建索引

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>  ON <表名> (<列名>[<升降序>][,<列名>[<升降序>]]…)

如果不指定升降序(ASC/DESC)，默认为升序。

样例：

CREATE UNIQUE INDEX IDX_CUSTOMER_PHONE on Customer(phone);

删除索引

DROP INDEX <索引名> ON <表名>

样例：

DROP INDEX IDX_CUSTOMER_PHONE on Customer;

三、数据操纵语句

数据操纵语句可以增加、删除或修改表中的记录。

1、插入

插入单行

INSERT INTO <表名> [(<列名1>，<列名2>，……，<列名n>)]
VALUES(<列值1>，<列值2>，……，<列值n>) 

列名和列值对应，没有列出的列的值为空。如果省略列名序列，则插入的列值和表定义时列的顺序一致。

样例：

insert into Customer values(100, 'Tom', '18012345678');

insert into Product(id, name) values(1, 'Book');

插入多行

将子查询的结果插入到指定表中：

INSERT INTO <表名> [(<列名1>，<列名2>，……，<列名n>)] 
子查询；

子查询结果与列名序列要对应，如果省略列名序列，则子查询结果必须和指定表中的列一致。

样例：

商品销售总量表：TotalSales(productId, total);

insert into TotalSales(productId, total)
select productId, sum(quantity) from OrderDetail group by productId;

2、删除

删除表中的一行或多行记录：

DELETE FROM <表名> [WHERE <条件>];

样例：

delete from Customer where name = 'Tom';

delete from OrderDetail where orderId in (select orderId from Orders where customId = 100);

3、修改

修改表中一行或多行记录中的某些值：

UPDATE <表名> 
SET <列名>=<表达式>[, <列名>=<表达式>]
[WHERE<条件>];

样例：

update Customer set name = 'Lucy' where phone = '18010012002';

update Product set price = price * 0.9 where id in (
select productId from OrderDetail
group by productId
having sum(quantity) < 10
);

4、事务

在SQL中，事务相关的语句有三条：BEGIN TRANSACTION、COMMIT和ROLLBACK。

BEGIN TRANSACTION

开启事务

COMMIT

提交事务：将事务中所有对数据库的修改写到磁盘上的物理数据库中。

ROLLBACK

事务回滚：事务的执行中发生了某种异常，不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。

四、数据控制语句

数据控制语句可以用来设置或更改数据库用户或角色权限(不同数据库下语句不同)，例如：

允许用户创建表、删除表的权限或所有权限：

GRANT CREATE TABLE TO username;
GRANT CREATE ANY TABLE TO username;

GRANT DROP ANY TABLE TO username;

GRANT sysdba TO username;

收回用户权限：

REVOKE CREATE TABLE FROM username

五、查询

查询(检索)操作是对已存在的表或视图进行数据检索，不会改变数据本身。

SELECT [ALL|DISTINCT] <列名或表达式> [别名1] [，<列名或表达式> [别名2]]…
FROM <表名或视图名> [表别名1] [，<表名或视图名> [表别名2]]…
[WHERE <条件表达式1>]
[GROUP BY <列名1> [HAVING <条件表达式2>]]
[ORDER BY <列名2>] [ASC|DESC] 

1、DISTINCT

DISTINCT的作用范围是所有目标列，在SELECT子句中只出现一次。

SELECT DISTINCT name FROM Customer;

2、别名

在查询时可以为查询列设置别名。

SELECT number 商品编码, name 商品名称 FROM  Product;

3、表达式或常量

SELECT语句中可以包含算术运算表达式(+ - * /)或常量，表达式的运算对象为常量或列。

SELECT id, customId, amount*0.8 FROM Orders;

select '电话：', phone from Customer;

4、聚合函数

SQL提供的聚合函数如下：

COUNT( * )：统计表中数据行数；
COUNT(< 列名>)：统计表中某一列值的个数；
SUM(< 列名>)：计算某一列值的总和；
AVG(< 列名>)：计算某一列值的平均数；
MAX(< 列名>)：计算某一列中的最大值；
MIN(< 列名>)：计算某一列中的最小值。

其中，除了COUNT(*)外，其它聚合函数在计算时会忽略空值。

在<列名>前加DISTINCT保留字，可以将查询结果中的列去掉重复值后再做计算。

样例：

SELECT COUNT(*) FROM Customer;

SELECT COUNT(DISTINCT name) FROM  Customer;

5、条件查询

WHERE子句中常用的查询条件有：

普通比较：<、<=、>、>=、=、!=、 < >、!>、!<
条件连接：AND、OR、NOT
字符匹配：LIKE、NOT LIKE
```
  <列名> [NOT] LIKE <字符串常数> [ESCAPE <转义字符>]
```
[NOT] LIKE的比较值中可以包含通配符，%表示任意长度的字符串，_表示单个字符。如果查询的字符串本身包含%或_，则要使用ESCAPE <转义字符>对通配符转义：
```
  SELECT name, phone FROM Customer WHERE name LIKE '王\_' ESCAPE '\'; 
```
集合：IN、NOT IN
空值：IS NULL、IS NOT NULL
范围：BETWEEN A AND B、NOT BETWEEN A AND B

6、分组查询

GROUP BY

使用GROUP BY子句可以将数据按某一列或多列值按值相同的原则分组，分组后可以对每组数据进行指定的操作；分组查询通常和聚合函数一起使用。

使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和聚合函数。

SELECT productId, SUM(quantity) 
FROM OrderDetail 
GROUP BY productId;

HAVING

使用HAVING子句可以对分组后的结果进行过滤，选择满足条件的组。

SELECT productId, MAX(quantity), MIN(quantity) FROM OrderDetail
GROUP BY productId
HAVING COUNT(*)>5 ;

7、排序查询

使用ORDER BY子句可以将查询结果按指定的列值顺序排列，默认为升序。

SELECT * FROM Product ORDER BY name, number DESC;

ORDER BY子句只能用于对最终查询结果排序，不能对中间结果排序；因此ORDER BY子句只能出现在最后。

8、连接查询

表间连接

select Customer.id, name, Orders.id, amount, createdate from Customer, Orders
where Customer.id = Orders.customId and city='ShangHai';

内连接

select Customer.id, name, Orders.id, amount, createdate 
from Customer INNER JOIN Orders on Customer.id = Orders.customId 
where city='ShangHai';

自连接

表与自身进行的连接：

select D.* from Customer C, Customer D
where C.city = D.city and C.name = 'Tom';

外连接

外连接与普通连接不同，普通连接只查询出满足连接条件的数据；外连接以指定表为连接主体，将主体表中不满足连接条件的数据也会查询出来。
- 左外连接
```
  LEFT JOIN …… ON ……
```
- 右外连接
```
  RIGHT JOIN …… ON ……
```

9、嵌套查询

一个SELECT - FROM - WHERE语句构成一个查询块。将一个查询块嵌套在另一个查询块的WHERE或HAVING子句的条件表达式中的查询，称为嵌套查询。

其中子查询中不能使用ORDER BY语句。

SELECT name, phone FROM Customer 
WHERE id = (SELECT customId FROM Orders WHERE id=1);

10、集合查询

进个集合操作的各查询结果必须是相容的，即列数相同且对应的属性列的数据类型也相同。

并集(UNION)

SELECT * FROM Customer WHERE city='BeiJing'
UNION
SELECT * FROM Customer WHERE city='ShangHai';

交集(INTERSECT)

SELECT * FROM Product WHERE name='Banana' 
INTERSECT
SELECT * FROM Product WHERE price>10;

差集(EXCEPT)

SELECT * FROM Customer WHERE city='BeiJing'
EXCEPT
SELECT * FROM Customer WHERE name like 'To%'; 

六、常用SQL

工作中常用SQL：

1、创建备份表

CREATE TABLE IF NOT EXISTS Product_backup like Product;
INSERT INTO Product_backup (select * from Product WHERE id in (100, 101, 102));

2、不存在时才插入数据

INSERT INTO Customer (id, name, phone) 
(select 505, 'Albert', '17112349876' from DUAL 
where not exists (select 1 from Customer where ID = 505));

参考资料：

《数据库原理》