SQL入门

发布时间: 2016-11-14

前菜 数据库基本概念

数据库(Database): 是一个以某种有组织的方式存储的数据集合。

表(Table): 某种特性类型数据的结构化清单。

模式(Schema): 关于数据库和表的布局以及特性的信息。

列(Column): 表中的一个字段，所有表由一个或多个列组成。

行(Row): 表中的一个记录。

主键(Primary Key): 表中每一行有一个可以唯一标识自己的记录。

select 语句

检索单列的语句:

-- exp.1
select prod_name from Products;

-- exp.2
SELECT prod_name FROM Products;

-- exp.3
SELECT prod_name
FROM Products;

检索多列语句：

SELECT col_name1, col_name2 FROM tablename;

检索所有列：

select * from tablename;

检索不同值，可以使用distinct语句，应用到实际例子中，比如计算uv:

select distinct user_device from events;

limit限制返回结果，当面对大量数据的时候，没必要全部返回可以增加返回条数的限制：

-- 反回10条
select * from tablename limit 10

排序检索数据

当数据返回后，如果我们希望有序的话，order by派上用场。

select * from tablename order by time;

orderby语句放到了整个select语句后面。

也可以对多列进行排序：

select prod_id, prod_price, prod_name
from Products
order by prod_price, prod_name

这个时候排序遵循的顺序是，第一用price排序，第二用name排序。

也可以按列位进行排序，没必要写明后面的字段名，传入列名的顺序。

select prod_id, prod_price, prod_name
from Products
order by 2, 3

这个时候返回的是按照传入select的第二三列为准的顺序。

然后可以指定排序的方向是倒序，关键字是desc。

SELECT col_name FROM tablename ORDER BY col_name DESC;

SELECT col_name1, col_name2, col_name3 FROM tablename ORDER BY col_name1 DESC, col_name2;

DESC 关键词只应用到直接位于其前面的列名。如果想在多个列上进行降序排序，必须对每一列指定DESC关键词。

where 过滤数据

当包含数据需要精确查找的时候，我们希望可以对数据进行筛选。

SELECT col_name1, col_name2 FROM tablename WHERE col_name1 = 42;

where跟在select语句后面，在where关键字后面指明筛选条件，通过不同的where操作符可以取出不同的数据：

操作符	作用
=	符合该条件
<>	不符合该条件
!=	不等于
< / <=	小于 小于且等于
> / >=	大于 大于且等于
!< !>	不小于 不大于
between	在指定的两个值之间
is null	为null的值
is not null	不为null的值

基本where后面加琢磨好的查询条件，可以满足多数简单的数据需求。

高级数据过滤

where后面跟的是单一的条件，为了更强的控制过滤，sql允许多个where联合调用，如何联合呢？需要提到各种连接操作符。

• and 操作符

SELECT col_name1, col_name2, col_name3
FROM tablename
WHERE col_name1='ABC' AND col_name2 <= 4;

• or 操作符

SELECT col_name1, col_name2, col_name3
FROM tablename
WHERE col_name1='ABC' OR col_name2 <= 4;

SQL在处理or之前，优先处理and

• in 操作符

in操作符用来指定条件的范围，所指定范围中的每个条件都可以进行匹配。

SELECT prod_name, prod_price
FROM Products
WHERE vend_in
IN ('DLL01', 'BSLKJL')
ORDER BY prod_name;

解读例子就能明白in到底在做什么，只要vend_in这个这段里能满足=DLL01或='BSLKJL'的都满足条件。完全可以改写成or的形式：

select prod_name, prod_price
from Products
where vend_in = 'DLL01' OR vend_in = 'BSLKJL'

在需要满足多个合法条件的时候，in操作符可使得语句表达的更清楚。

• not操作符

SELECT prod_name FROM Products WHERE NOT vend_id='DLL01' ORDER BY prod_name

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。实际应用中，可以帮助我们排除一些东西。

LIKE/通配符

前面的所有where查询都精确的指定到了一个值或范围，但我们处理实际需求的时候，可能会全局模糊匹配一些满足条件的语句。

这里需要了解两个概念：

• 通配符 wildcard 用来匹配值的一部分的特殊字符
• 搜索模式 search pattern 由字面值、通配符或两者组合构成的搜索条件

%通配符

%表示任何字符出现任意次数。例如找出所有以Fish起头的产品。

select prod_id, prod_name
from Products
where prod_name LIKE 'Fish%'

例如找出一段文字中包含to be or not:

select prod_id, prod_name
from Products
where prod_intro LIKE '%to be or not%'

_通配符

下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。

select prod_id, prod_name
from Products
where prod_name LIKE '__ inch teddy bear';

[]通配符

方括号通配符用来指定一个字符集，他必须匹配指定位置的一个字符。

例如匹配J或M开头的任意cust_contact:

select cust_contact
from Customers
where cust_contact LIKE '[JM]%'
order by cust_contact

计算字段

接下来内容需要啃啃了。如果要说计算字段用途的话，比如前端程序希望从数据库返回特定的组合结果的话，可能要用到计算字段，返回格式化好的数据，比返回生硬的数据记录优雅。

比如数据结构存的是姓名 学校，程序希望返回谁的学校，可以利用计算字段返回组合结果。当然我们可以直接在程序里完成这个转意，但是在SQL语句内完成转换和格式化，比交给客户端处理快得多。

拼接字段

SELECT Concat(stu_name, '的', school) FROM students ORDER BY stu_name;

返回：

李狗蛋     的       洋葱学校
王小二     的       洋葱学校

Concat()实现了将两列字段拼接。也有另一种方法，使用||:

select stu_name || '的' || school from students
order by stu_name;

从前面的输出可以看到，拼接出字段就像真实的记录一样返回。这个新计算出的列没有名字，只是一个值，我们可以给这个值起别名，让他像是其它列一样有名字。举个例子，不用关心RTRIM()是什么， 拼接出字段用as后面的值作为别名返回：

SELECT
RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

执行算数计算

计算字段也可以作为算数表达式返回，举个例子

SELECT
    prod_id, quantity, item_price, quantity*item_price
AS expended_price
FROM OrderItems
WHERE order_num=20008;

expended_price作为quantity*item_price的结果返回。这里的运算符还可以有：

操作符	说明
+	加
-	减
*	乘
/	除

SQL函数处理数据

SQL和编程语言一样，其实也有一堆内置函数，比如刚刚的RTRIM()就是一个内置函数。

文本处理函数

函数	说明
left()	返回字符串左边的字符
length()	返回字符串长度
lower()	返回转换为小写
ltrim()	返回去掉字符串左边的空格
right()	返回去掉字符串右边的字符
rtrim()	返回去掉字符串右边的空格
upper()	将字符串转换为大写

日期和时间处理函数

不同数据库的时间处理函数不一样，举个例子，都用起来差不多：

SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2012;

数值处理函数

函数	说明
abs()	返回一个数的绝对值
cos()	返回一个角度的余弦
exp()	返回一个数的指数
pi()	返回圆周率
sin()	返回一个角度的正弦
sqrt()	返回一个数的平方根
tan()	返回一个角度的正切

汇总数据

真正分析的过程中，可能需要计算uv，计算平均值，取出最活跃用户等需求。这个过程叫 汇总数据。SQL提供了专门的函数去完成这些操作。

终于祭出SQL最耗费性能的聚集函数（aggregate function。

函数	说明
avg()	返回某列的平均值
count()	返回某列的行数
max()	返回某列的最大值
min()	返回某列的最小值
sum()	返回某列之和

• avg()

-- avg() function
SELECT AVG(prod_price) AS avg_price FROM Products;

SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

• count()

-- COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值NULL还是非空值 比如算个活跃
SELECT COUNT(*) AS num_cust FROM Customers;

-- COUNT(column_name)对特定列中具有值的行进行计数，忽略NULL值。
SELECT COUNT(cust_email) AS num_cust FROM Customers;

• max() / min()

SELECT MAX(prod_price) AS max_price FROM Products;
SELECT MIN(prod_price) AS max_price FROM Products;

• sum()

-- 返回特定列总和
SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;

SELECT SUM(quantity) FROM OrderItems;+

• distinct 聚集不同值

SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

组合聚集函数，来个综合实例：

SELECT COUNT(*) AS num_items, MIN(prod_price) AS min_price, MAX(prod_price) AS max_price, AVG(prod_price) AS price_avg FROM Products;

select count(*) as activity_users, min(ability) as low_ability_one, max(ability) as high_ability_one, avg(ability) as avg_ability from eventsv4;

分组数据(group by / having)

当需要针对数据做分组的时候，比如做用户群体划分，可能会用到SQL的分组数据。

下面介绍如何分组数据，以便汇总表内容的子集，涉及两个新select语句的子语句: group by 和 having。

分组是使用group by建立的:

select vend_id, count(*) as num_prods
from Products
group by vend_id;

-- 返回
vend_id num_prods
------- ---------
BRS01   3
DLL01   4

GRUOP BY子句指示数据库系统按vend_id排序并分组数据。 GROUP BY子句指示数据库系统分组数据，然后对每个组而不是整个结果集进行聚集。意思是每个分组都单独进行COUNT()计算。整理流有点像是找出vend_id -> 找出一共几种vend_id -> 针对每一种做count()。

我们可以接着改写使用having可以过滤分组，流有点像是找出vend_id -> 找出一共几种vend_id -> 针对每一种做count() -> 过滤。

select vend_id, count(*) as num_prods
from Products
group by vend_id
having count(*) > 3

-- 返回
vend_id num_prods
------- ---------
DLL01   4

where是在分组前进行过滤，having是在分组后进行过滤。两者后面的表达式一样，也都可以再跟着order by。

使用子查询

SELECT语句是SQL的查询。我们迄今为止所看到的所有SELECT语句都是简单查询,即从单个数据库表中检索数据的单条语句。

SQL还允许创建子查询(subquery),即嵌套在其他查询中的查询。

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (
    SELECT cust_id
    FROM Order
    WHERE order_num IN (
            SELECT order_num
            FROM OrderItems
            WHERE prod_id = 'RGN01'
    )
);

子查询常用于WHERE子句的IN操作符中,以及用来填充计算列。涉及多条件组合的情况下，使用子查询可以省些事。

联结表 join表

SQL最强大的功能之一就是可以在数据查询执行中联结表，跨表直接查询。在实际的表设计中，会将数据分解到不同的表中存储，一旦需要聚集散落在各个表中的数据时，就需要join表解决了。

等值联结

创建联结非常简单,指定要联结的所有表以及关联它们的方式即可。请看下面的例子:

select vend_name, prod_name, prod_price
from Vendors, Products
where Vendors._id = Products.vend_id

我们来看这段代码。SELECT语句与前面所有语句一样指定要检索的列。这里最大的差别是所指定的两列(prod_name和prod_price)在一个表 中,而第三列(vend_name)在另一个表中。

内联结

select vend_name, prod_name, prod_price
from Vendors
INNER JOIN Products
ON Vendors, vend_id = Products.vend_id

联结多个表

SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

高级联结

我们已经知道如何使用列别名，SQL除了允许对列名和计算字段使用别名，还可以给表起别名。

select cust_name, cust_contact
from Customers as C, Orders as O, OrderItems as OI
where C.cust_id = O.cust_id
and OI.order_num = O.order_num
and prod_id = 'RGANOI';

以上所有联结或等值联结都是SQL中简单的联结使用。其实其他三种联结方式：

• 自联结 self-join
• 自然联结 natural join
• 外联结 outer join

使用表别名主要原因是select语句中不止一次引用相同的表，自联结通常作为外部语句,用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的,但许多DBMS处理联结远比处理子 查询快得多。应该试一下两种方法,以确定哪一种的性能更好。

组合查询 UNION

UNION可以组合多个select

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

在用UNION组合查询时,只能使用一条ORDER BY子句,它必须位于最后一条SELECT语句之后。对于结果 集,不存在用一种方式排序一部分,而又用另一种方式排序另一部分的情况,因此不允许使用多条ORDER BY子句。

插入数据 insert

插入完整行：

INSERT INTO Customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

插入部分行；

INSERT INTO Customers(cust_id,
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA');

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

插入检索出的数据：

INSERT
SELECT
INTO Customers(cust_id,
FROM CustNew;

从一个表复制到另一个表：

CREATE TABLE CustCopy AS
SELECT * FROM Customers;

更新和删除数据

使用update set更新一条数据：

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1008642'

删除数据：

DELETE FROM Customers
WHERE cust_id = '100012';

视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。 视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

存储过程

存储过程可以理解为SQL的批处理。

创建存储过程：

-- orcacle版本的存储过程
CREATE PROCEDURE MailingListCount (
ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount := v_rows;
END;

不同数据库的存储过程不太一样，按需理解。

事物处理 transaction processing

使用事务处理(transaction processing),通过确保成批的SQL操作要么完全执行,要么完全不执行,来维护数据库的完整性。

在执行SQL的时候，加上事物处理标记：

-- sql server
BEGIN TRANSACTION
COMMIT TRANSACTION

SQL的ROLLBACK命令用来回退(撤销)SQL语句,请看下面的语句:

DELETE FROM Orders;
ROLLBACK;

约束 索引 触发器

约束(constraint)

管理如何插入或处理数据库数据的规则。

CREATE TABLE Vendors
(
vend_id
vend_name
vend_address
vend_city
vend_state
vend_zip
vend_country
);
CHAR(10)
CHAR(50)
CHAR(50)
CHAR(50)
CHAR(5)
CHAR(10)
CHAR(50)
NOT NULL PRIMARY KEY,
NOT NULL,
NULL,
NULL,
NULL,
NULL,
NULL)

在此例子中,给表的vend_id列定义添加关键字PRIMARY KEY,使其成为主键。

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

这里定义相同的列为主键,但使用的是CONSTRAINT语法。此语法也可以用于CREATE TABLE和ALTER TABLE语句。

索引(Index)

总之我知道没有索引MongoDB肯定是活不下去。

CREATE INDEX prod_name_ind
ON Products (prod_name);

触发器

触发器是特殊的存储过程,它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT、UPDATE和DELETE操作(或组合)相关联。

与存储过程不一样(存储过程只是简单的存储SQL语句),触发器与单个的表相关联

触发器内的代码具有以下数据的访问权:

• INSERT操作中的所有新数据;
• UPDATE操作中的所有新数据和旧数据;
• DELETE操作中删除的数据。

CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;