数据库基础知识

1. 基本概念

• 数据库：指的是以一定方式储存在一起、能为多个用户共享，具有尽可小的冗余度、与应用程序彼此独立的数据集合。
• 数据库管理系统：（ Database Management System，简称 DBMS）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。
• 关系型数据库：现代DBMS使用不同的数据库模型追踪实体、属性和关系。在个人电脑、大型计算机和主机上应用最广泛的数据库管理关系是关系型DBMS。在关系型数据模型中，用二维表格表示数据库中的数据。这些表格成为关系。现在使用度较高的关系型数据库：MYsql，oracle,sql server,DB2,postgreSQL
• 表：在关系数据库中，数据库表是一系列二维数组的集合，用来代表和储存数据对象之间的关系。它由纵向列和横向的行组成。每个表都是一个命名的行的集合，每一行由一组相同命名字段（列就是字段）组成，而且每个字段都有一特定的类型。

例子：

 

2. SQL

• 什么是SQL？

结构化查询语言（ Structural Query Language ,缩写： SQL），是一种特殊目的之编程语言，用于数据库中的标准查询语言, 用于数据库中的标准数据查询语言。不过各种通行的数据库系统在其实践程中都对 SQL规范作了某些编改和扩充。所以，实际上不同数据库系统之间的SQL不能完全相互通用。

• SQL特点

SQL是高级的非过程化编语言，它允许用户在高层数据结构上工作。

以记录项目 〔records〕的合集（ set ）〔项集， record set〕作为操纵对象，  所有 SQL 语句接受项集作为输入，返回提交的项集作为输出。

 在多数情况下，其他编程语言中需要用一大段程序才可实践的一个单独事件，而其在SQL上只需要一个语句就可以被表达出来。这也意味着用SQL上可以写出非常复杂的语句。

SQL操作的，永远都是数据集合。

• SQL语句分为三类

数据操纵语言（DML）Data ManipulationLanguage

INSERT、UPDATE、SELECT、DELETE

数据定义语言（DDL）Data DefinitionLanguage

CREATE、DROP、ALERT（建表、删表、改表）

数据控制语言（DCL）DataControl Language

COMMIT、ROLLBACK、GRANT、REVOKE

• 使用数据介绍（客户信息表）

 

3. 查询概述

• 从数据库中检索数据的过程或命令叫做查询；在SQL 里SELECT 命令用于声明查询。

基本语法：

SELECT 列名 …FROM 表名；

• 返回部分数据（部分列）

查看订单表：

SELECTprod_id , vend_id, prod_price  FROMproducts;

 # 语句含义：查看返回这三个列；SQL 中使用空白字符分割语句（或者分号）；Select 表示查询；prod_id,  vend_id,  prod_price 为列名；From 数据来源表；分号是 SQL 语句的语句结尾符号。

补充知识 :

多字段用逗号分割；

* 号是通配符，表示所有字段 select*；

使用 AS 设置 别名；

SQL 语法中大小写不敏感，大小写敏感需要转义（使用双引号，把字符串转义为字段，单引号为转义为字符）；

DISTINCT去重

• 返回部分数据（部分行）

select* from products limit 5 ;（使用比较少）

#含义：返回 product 在从硬盘上找到的前五条数据（limit返回的数据是不可预的）；

 限制返回条目：

PG(  PostgreSQL 简称 PG)  中限制返回条目使用 limit 关键字；

SQL server 中使用 TOP；

Oracle 使用 whererownum< 5；

select * from product limit 2 offset 3；#含义：返回从第 3 行开始的 2 行

4. SELECT语句执行顺序

例子：

select * from products order by prod_price limit 3；

#返回价格最小的三条数据

怎么理解这个 SQL 在数据库中怎么抽取并处理数据的呢？

 

第一步：执行 FROM 子句， 从 products  表组装数据源的数据

第二步：执行 WHERE  子句， 本例不含有 WHERE 字句

第三步：执行 GROUP BY  子句， 本例不含有 GROUP BY 字句

第四步：执行 HAVING子句， 本例不含有  HAVING 字句

第五步：执行 SELECT，这个阶段是投影的过程，处理 SELECT 子句提到的元 素

第六步：执行 ORDER BY 子句，以订单编号从小到大排列

第七步：执行 LIMIT [ OFFSET ]子子句，返回最前面三条语句

 

• FROM

FROM 才是 SQL SQL 语句执行的第一步，并非 SELECT  。数据库在执行SQL语句的第一步是将数据从硬盘加载到缓冲区中，以便对这些进行操作。

• SELECT

SELECT是在大部分语句执行了之后才执行的。（数据投影）

严格的说是在FROM 和 GROUP BY 之后执行的。理解这一点是非常重要的，这就是你不能在 WHERE中使用在SELECT中设定别名的字段作为判断条件的原因。

• ORDER BY

ORDER BY 默认升序，关键字（ASC），降序排列关键字（DESC）；

ORDER BY 多个字段，执行顺序从左至右；

ORDER BY 多个字段可以使用不同的顺序；

ORDER BY 需要把所有符合条件的数据全部加载至内存然后进行排序，再显示输出，在数据量过大的情况下性能下降非常快。

• LIMIT

限制输出；各具灵活性的限制输出

• WHERE 子句，数据过滤

< 小于；> 大于；= 等于；<= 小于等于；>= 大于等于；!= 不等于

a BETWEEN x AND y  等效于 a >= x AND <= y；

a NOT BETWEEN x AND y  等效于 a< x AND > y；

NULL：expression ISNULL，expression IS NOT NULL

错误： expression = NULL

• 检查单个值

SELECT  prod_name ,prod_price  FROM Products

WHERE  prod_price < 10;

#含义：查询所有价格小于 10 的商品

把上个例子中的 order by limit 3 复制过来：

SELECT prod_name,  prod_priceFROM Products

WHERE  prod_price < 10       order by prod_price limit 3 ;

#执行顺序解释

• WHERE 子句的具体执行逻辑：

u 物品价格小于 10 在数据库里面是怎么执行的？

u 在人处理数据的过程中，是逐条记录比较，程序实现是怎样的？

• 本质上，数据库进行了逐条扫描数据，并与 10 做比对，符合条件返回 True， 不符合返回 False

• 多个 WHERE 子句

例：

 价格大于 10 并且是来自 供应商 ‘BRS01 ’的商品

Where prod_price<10 andvend_id='BRS01'

u 逻辑运算 : 与 、或非

 AND  ,OR , NOT 

优先级：AND> OR

 

5. 子查询

• IN 操作符： IN 可以与子查询一起工作

例子：列出订购物品 RGAN01 的所有顾客

不使用子查询：不使用子查询需要两个SQL语句

 select * from orders

Where order_num=20007 or order_number=20008

使用子查询：

SELECT  cust_id FROM OrdersWHERE order_num  IN (20007,20008);

（IN里面的都是用or）

• LIKE 关键字

 LIKE 是一种模糊匹配， 最常见的通配符是 % ，表示任意数量的字符。

select * from products where vend_id like 'BR%' ;

# %代表通配符，只要满足以BR开头的，%必须和like一起出现

 

6. 分组聚合

简单的说，聚合函数是按照一定规则将多行数据汇总成一行的函数。对数据进行聚合前 ，还可以按照特定的列将数据进行分组(Groupby)再聚合,然后按照再次给定的条件进行筛选 (Having仅用于groupby)，(where处理不了groupby之后，having可以处理groupby之后)，having处理select里面出现的聚合。

• 聚合

函数：

AVG  均值；

COUNT非空行数（空就是NULL）；

MAX 最大值；

MIN 最小值；

SUM 求合；

应用聚合函数后，只返回一条记录。

• 分组（可以理解为去重，但可以在里面加入聚合）

如果聚合函数所得到的结果无法按照特定值进行分组，那聚合函数的作用就没那么强了。在SQL中，使用 Groupby对聚合函数汇总的值进行分组。（分组聚合一般一起使用，更强大）

• 分组聚合范例：

SELECT order_num,

avg(item_price ) AS avg_price,

count( item_price ) AS cnt_price ,

max( item_price )  ASmax_price  ,

min( item_price ) AS min_price ,

sum(quantity * item_price ) AS order_price

FROM orderitems

WHERE quantity > 50

GROUP BY  order_num

HAVING COUNT(ITEM_PRICE) > 2

#实际上的顺序select在group by …having之后，但是select放在最前面

• 其他函数：可以应用在SQL语句中的函数

可以分为四类：

     数值处理（也称为数学函数）：avg

     字符处理：拼接、切割、拆分

     日期处理：各种日期类型

     类型转换：不同数据库使用的类型转换不一样（查手册）

• 谓词

谓词就是返回值为布尔值的函数。

例如：LIKE;  BETWEEN ;IS NULL ; ISNOT NULL ;IN; EXISTS

SELECT * FROMOrders

WHERE order_num  IN（

Select ORDER_NUM

From orderitems where prod_id='BR01'

）;

等价于

SELECT  * FROM Ordersas t1                 #读到order

WHERE EXISTS

(  #20005  2012/5/1    1000000001

 

 SELECT 1 FROM orderitemsas t2       #读到oderirems,实际上把from里面的每一行记录逐条放到where里面进行判断，返回一个TRUE/FALSE

Where t1.order_num=t2.order_num  #这里t1.order_num=20005

And t2.prod_id='BR01'

);

#t1,t2为表的i别名，可以直接用as

 

7. 表的几种 JOIN

为什么使用 JOIN？希望将数据分开存放，避免过多的重复数据；避免数据变更的时候，需要更新太多数据。

• Inner join产生的结果集中，是A和B的交集

SELECT*FROM Table A

INNER JOIN TableB

ON TableA.name=TableB.name

例子：

SELECTvend_name , prod_name , prod_price FROM Vendors

innerjoin  Products ON

Vendors.vend_id=Products.vend_id

• Full outer join

产生 A和B的并集。但是需要注意，对于没有匹配的记录，则会以 null null做为值。

SELECT* FROM  TableA

FULLOUTER JOIN  TableB

ON  TableA.name = TableB.name

例子：

SELECTCustomers.cust_id, Orders.order_num

FROMCustomers  FULL OUTER JOIN Orders

ONCustomers.cust_id Customers.cust_id Customers.cust_id Customers.cust_idCustomers.cust_id = Orders.cust_id;

• Left outer join

产生表A的完全集，而B表中匹配的则有值，表中匹配的则有值，没有匹配的则以 nullnull 值取代。

SELECT* FROM TableA

LEFT OUTER JOINTableB

ON TableA.name = TableB.nameand 1=0; #可以多个条件

 

例子：

SELECT Customers.cust_id,Orders.order_num

FROM Customers LEFT OUTER JOIN Orders

ON Customers.cust_id=Orders.cust_id;

• Cross join

这种join没有办法用文式图表示，因为其就是把表A和表B的数据进行一个N*M的组合，即笛卡儿积。

SELECT*FROM TableA

CROSS JOIN TableB;

例子：

SELECTvend_name,prod_name,prod_price

FROM Vendors cross join Products;