3 基础概念

3.1 数据库基础概念

数据库本质上是一款面向数据管理软件。我们也可以把它理解为存放数据的仓库，这个仓库是按照一定的存储结构来组织、存储数据的。按照数据库的数据组织形式一般分为三种：层次式数据库、网络式数据库和关系型数据库。一般数据库指的都是关系型数据库，即以简单的二元关系(二维表格形式)形式组织数据的。而二元关系的基础就是关系代数，它是

基于集合，提供了一系列的关系代数操作：并、差、笛卡尔积、选择、投影、交、连接和关系等扩展操作，是一种集合思维的操作语言。关系代数操作以一个或多个关系为输入，结果是有一个新的关系。

3.2 集合相关概念

集合即SET，是个无序的元素的集。集合里常见概念有交、并、差。即假定有个集合叫A，它有元素 1,2，有个集合叫做B，它有元素叫做1,3.那么A和B的交为 1，即两个集合的共有元素的集合。A和B的并，为1,2,3,即两个集合的共有元素的集合1,2,3。A和B的差则为2，即在返回在A集合同时又不在B集合的元素的集合。

注:以下以mysql的语法演示了部分集合操作的特点。

笛卡尔积

笛卡尔积（Cartesian product，CROSS JOIN），数学里的表达式为A×B = {(x,y)|x∈A∧y∈B}，即生成一个新的集合，它里的子元素都同时属于A和B。

假设集合A={c,d}，集合B={3,4,5}，则两个集合的笛卡尔积为{(c, 3), (c, 4), (c, 5), (d, 3), (d, 4), (d, 5)}。

左外连接

左外链接（LEFT OUTER JOIN又简称LEFT JOIN）即以左边的表为主表来关联右边的表。它的特点是关联出来的记录数与前表一致如果匹配不到记录,右边的表以NULL填充。

CREATE TABLE tb_a(id int);
INSERT INTO tb_a VALUES(1);
INSERT INTO tb_a VALUES(2);
INSERT INTO tb_a VALUES(3);

CREATE TABLE tb_b(id int);
INSERT INTO tb_b VALUES(2);
INSERT INTO tb_b VALUES(3);
INSERT INTO tb_b VALUES(4);

SELECT A.id,B.id FROM tb_a A
LEFT JOIN tb_b B
ON A.id = B.id

--结果

id	id1
3	3
2	2
1

右外连接

右外链接（RIGHT OUTER JOIN又简称RIGHT JOIN）即以右边的表为主表来关联右边的表。它的特点是关联出来的记录数与后表一致如果匹配不到记录,左边的表以NULL填充。 

SELECT A.id,B.id FROM tb_a A
RIGHT JOIN tb_b B
ON A.id = B.id

id	id1
2	2
3	3
	4

 

全连接

全连接(FULL OUTER JOIN)结合LEFT OUTER JOIN和RIGHT OUTER JOIN的特点，即前表匹配不到后表时后表补充为NULL，后表匹配不到前表是前表补充为NULL。

SELECT A.id,B.id FROM tb_a A
LEFT JOIN tb_b B
ON A.id = B.id
UNION
SELECT A.id,B.id FROM tb_a A
RIGHT JOIN tb_b B
ON A.id = B.id

id	id1
3	3
2	2
1
	4

内连接

内连接（INNER JOIN）是两个表按照定义的条件去匹配，仅显示能匹配上的记录。记录数小于等于两个表里记录数最小的表的记录。

SELECT A.id,B.id FROM tb_a A
INNER JOIN tb_b B
ON A.id = B.id

id	id1
2	2
3	3

合并

合并（UNION ALL）即两个表里对应字段的数据整合在一起，这里不会剔除重复的数据。

SELECT id FROM tb_a
UNION ALL
SELECT id FROM tb_b

id

1

2

3

2

3

4

去重合并

合并（UNION）即两个表里对应字段的数据整合在一起，这里如果会剔除重复的数据。

SELECT id FROM tb_a
UNION
SELECT id FROM tb_b

id

1

2

3

4

3.3 常见数据库对象

3.3.1 表

表是存储数据的逻辑单位，这里的表即是我们在EXCEL里看的表格,是个二维的表格，有行(row)和列（column）组成.

行是是表里的一个组成部分，即对表的横向描述。一个表可能有成千上万行，也可能有1行或者为空的行即只有表的定义而没有一条记录。同理列也可以这样。

字段是表的基本单位是，这里又叫做属性或者特征，即每一列的统一名字。如name,id_card对应的含义为名字、身份证号。

关系型数据是先模式语言，即需要先定义好表的模式，就像无规矩不成方圆一样，需要先给每列的数据名称、类型、每列的关系，谁是唯一标识、有没有外键引用标识做好定义。下面我们来讲讲模式。

3.3.2 模式

模式（Schema）即数据库对象的集合的统称，一般包括表、视图、索引、函数、存储过程、同义词等。一个Schema对应一个用户，用户相当于Schema的管理员，它负责创建、更新、删除里面的对象。

3.3.3 视图

视图是对若干张按照一定业务含义关联在一起的表(也可以是一张表)的引用，可以看做是一张虚拟的表。

视图一般用作对底层数据的封装，如果你不想用户看到底层关联的表或者用户只是想访问某些表的部分字段也可以创建个视图。

-- 创建个视图，以部门表和员工表关联，返回部门名和员工名

CREATE VIEW V_VIEW AS
SELECT dname,ename
FROM dept A
JOIN emp B
ON A.deptno = B.deptno

3.3.4 触发器

触发器顾名思义即因为触发而做的动作，这里主要应用到表这种数据对象上。常见的触发器分别应用在INSERT、UPDATE、DELETE这三类场景下。

-- step1 创建个记录插入的日志表
CREATE TABLE insertlog(
id int(10) NOT NULL AUTO_INCREMENT,
name varchar(100),
primary key(id)
);

-- step2 创建触发器,处罚行为基于INSERT
CREATE OR REPLACE TRIGGER tri_insertdemo AFTER INSERT
ON dept FOR EACH ROW
INSERT INTO insertlog(name) SELECT dname from DEPT;

-- step3 验证触发器效果
INSERT INTO dept()VALUES(100,'demo','demo');
SELECT* FROM insertlog;

/*
id  name
1   ACCOUNTING
2   RESEARCH
3   SALES
4   OPERATIONS
5   demo
*/

3.3.5 函数

函数是为了为了解决某个特定问题的程序代码的封装。一般分为入参和出参，即传入参数和输出参数。函数按照用途可分为：

基本函数（length、replace、concat、abs、power、case、ifnull等）

聚合函数（max、min、sum、avg等）

分析函数(row_number、rank等)

自定义函数（自己定义的函数如：myFunc）

CREATE FUNCTION func_deptno(name VARCHAR(15)) RETURNS INT
BEGIN
    DECLARE r INT;
    SELECT DEPTNO FROM DEPT WHERE DNAME=name INTO r;
    RETURN r;
END;

SELECT func_deptno('SALES');
-- 返回部门编号为30

3.3.6 存储过程

一组按照业务需求编写的SQL指令集合，只经过一次在数据库里编译后不需要再编译。存储过程按照用途可以分：系统存储过程、自定义存储过程。

--创建一个按照输入的参数插入数据到dept的存储过程

CREATE procedure sp_insertdept(IN no int,IN name VARCHAR(10),IN loc VARCHAR(10))
BEGIN
INSERT INTO dept VALUES(no,name,loc);
END
--调用存储过程后会发现该记录会被插入到dept表里。
call sp_insertdept(300,'demoinsert','where');

3.4结构化查询语言

SQL，结构化查询语言，即对结构化数据进行定义、操作、事务操作等类的语言统称。一般包括DDL(数据定义语言)、DML（数据操作语言）、DCL（数据控制语言）、DTL（数据事务语言）

3.4.1 数据定义语言

数据定义语言，DDL用于定义、修改、删除数据库里的对象。（如表、索引、触发器等）。常见的有

CREATE

-- 创建个部门表字段有部门编号、部门名称、部门所在位置，并定义部门编号为主键。

create table dept(
    deptno int unsigned auto_increment COMMENT '部门编号',
    dname varchar(15) COMMENT '部门名称',
    loc varchar(50)  COMMENT '部门所在位置',
primary key(deptno)
) COMMENT='部门表';

ALTER

-- 修改部门表新增字段部门规模deptscale
ALTER TABLE dept ADD COLUMN deptscale int comment '部门规模';

RENAME/MODIFY

-- 修改字段deptscale的名字为deptsize，这里mysql的用法需要将字段类型和注释名一同追加上。
ALTER TABLE dept change deptscale deptsize int comment '部门规模';
-- 修改字段deptsize的数据类型、默认值、注释
ALTER TABLE dept modify COLUMN deptsize smallint DEFAULT NULL COMMENT '部门大小';

DROP

-- 修改部门表删除字段deptscale
ALTER TABLE dept DROP COLUMN deptscale;
-- 删除整个部门表，数据和表注释都将删除
DROP TABLE dept;

 

TRUNCATE

TRUNCATE TABLE dept;

3.4.2 数据操作语言

DML 数据操作语言，主要是数据库里的对象进行增、删、改、查操作。

 

INSERT

--往部门表里插入4条记录。
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept(deptno, dname,loc)values(40,'OPERATIONS','BOSTON')

SELECT

-

--查询部门表的部门编号的所有记录
SELECT deptno from dept;

UPDATE

--将部门表里的所有部门标号都更新为50
UPDATE dept SET deptno=50;
--将部门编号为40的更新为50
UPDATE dept SET deptno=50 WHERE deptno=40;

DELETE

--删除部门表部门编号为50的记录
DELETE FROM dept WHERE deptno=50;
--删除部门表所有记录
DELETE FROM dept

 

3.4.3 数据控制语言

dcl数据控制语言，这里主要是针对用权限的管理。常见关键字有grant（赋权）、revoke(收回).

 

grant

--赋予test用户能访问localhost（本地服务器）里的demodb里的对象。
grant select on demodb.* to test@'localhost'

revoke

--收回用户test在localhost里的所有的数据库访问权限
revoke all on *.* from test@localhost;

 

3.4.4 事务控制语言

DTL是事务控制语言。什么是事务(Transaction)，事务一个最小的不可再分的工作单元。现实生活中最容易联想到到的例子就是银行转账的这个场景。比如初始时A总共有1000块，B总共有500块。

在10：10时A转账给B  500块。

在10:20 时B 转账给A 200块。

那么最终A卡里还有700块，B用户卡里又800块，简单说A用户和B用户的账在整个事务里是对的上的，不存在A、B用户的钱变的不合理的情况。两个用户的总额仍是1500.

这是转账正常的情况，也是事务所保障的情况。如果把初始状态到10点20之前作为一个事务，如果用户想回滚，那该怎么还原当时的情况呢？显然我们要把500块还给A，那就是A又是1000块了，而B还是500块。

如果第一个事务不回滚，比如在10点15时提交了事务，你们A用户的余额是500，B的余额是1000.而且一旦在第二个时间点提交了，A、B的余额是在没有转账行为的情况下是固定的了，不在变化了。所有事务一般有如下的特点：

原子性

　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

 

原子性（Atomicity）：即事务的操作要么成功要么回滚，不会有其它的状态。

一致性（Consistency）；即事务之前和之后的都处于可核对的一致状态，比如总额相等等。

隔离性（Isolation）：当一个事务在执行时，其它的事务不能对它进行打断，除非当前事务结束了才能开始新的事务。

持久性（Durability）：即事务一旦提交了，数据都被保存为修改的状态，即使极端的情况，如正好掉电了或者数据库挂了，数据也能在来电后或者数据库起来之后恢复为事务提交后的值。

COMMIT

-- 插入一条记录并提交后提交
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
COMMIT;

ROLLBACK

-- 插入一条记录并提交后回滚
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
ROLLBACK;