数据库学习小结

1. 数据库理论

1.1. 关系型数据库和非关系型数据库

关系型数据库：Oracle、MySQL和SQL server

非关系型数据库：Redis用于缓存、MongoDB、Memcached用于评论、HBase

1.2. cmd命令启动mysql

mysql 	-u [用户名]	-p [密码]

1.3. SQL（Structure Query Language）结构化查询语言的分类

DDL(data defination language)：创建或删除数据库，表的操作语句。

DML(data manipulation language):对表中数据进行增删改的语句。

DQL(data query language):对表中数据进行查询操作的语句

DCL(data control language):权限管理语句

1.4. 常用数据库字段类型

Int整型

Double浮点型，例:double(4，2)表示最多4位，其中必须有两位小数

Decimal金钱类型，不会出现精度缺失，用法和double类似

Varchar和Char的区别：

Varchar是可变长度字符串类型，Char是固定长度字符串类型。
当实际使用数据长度没有达到预设长度时，Varchar类型会使用实际长度，而Char类型会使用空格自动补全。
因此，在结合实际并合理使用时，Char类型比Varchar类型效率更高。
此外，Oracle开发了一个新的数据类型Varchar2，它将数据库中Varchar可以存储空字符类型改为NULL,Varchar2向后兼容能力更强。

1.5. 软件生命周期

立项----需求分析----设计、编码、测试----发布-----运行维护----淘汰

1.6. 数据库设计过程

系统需求分析阶段----概念结构设计阶段----逻辑结构设计阶段----物理结构设计阶段----数据库实施阶段----数据库运行与维护阶段

1.7. 其他知识

登录命令

   SELECT FROM [表名] WHERE username=’XXX’ AND password=’XXX’;

&&和&的区别 (||和|的区别 )

在进行判断时，若前一条件为false，&&将直接判断整个语句为false，而&还会继续判断下一条件的真假性，从而增加了运算时间，降低了系统效率。所以尽量使用&&进行操作。

数据字典

UNKNOW

序列

MySQL 序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键，
如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。可以使用 AUTO_INCREMENT来实现。

同义词

mysql没提供同义词的功能，oracle有。同义词就是指为一个数据库对象起一个其他名字，这样根据这个名字就可以找到这个数据库对象。创建同义词必须有sysdba权限conn soctt/tiger as sysdba 有DBA权限创建私有，无法查看。

现在为emp表创建一个同义词为myemp

CREATE SYNONYM myemp
FOR emp ;

select * from myemp  无法显示信息

删除

drop SYNONYM myemp

创建公有，可以查看

CREATE public SYNONYM myemp
FOR emp ;

删除

drop public SYNONYM myemp

游标

   UNKNOW

存储过程

    UNKNOW

数据备份

   游标时SQL的一个内存工作区，由系统或用户以变量的形式定义。

2. 数据库操作

2.1. CRUD

2.1.1. 增加

INSERT INTO [表名] VALUES (数据1,数据2,……);           	 #依次对应表中字段

INSERT INTO [表名] (字段1,字段2,……)VALUES (数据1,数据2,……); #对应字段增加数据

INSERT INTO [表名] (字段1,字段N) VALUE (数据1,数据N);   	 #选择字段进行增加

2.1.2. 删除

DELETE FROM [表名];                                  #删除整张表内的数据

DELETE FROM [表名] WHERE [条件];			     #删除表中满足条件的数据

2.1.3. 修改

UPDATE [表名] SET [要修改的字段=修改的内容] WHERE [条件]

2.1.4. 查询

SELECT [被查询的字段] FROM [表名] +查询方式\条件

查询方式\条件：

WHERE 条				             # >,<,=,!=

WHERE [字段] IN(内容1,内容2)                  #在内容1和内容2中二选一

WHERE [字段] BETWEEN [范围1] AND [范围2]      #[范围1,范围2]间选择，闭区间

WHERE NOT [字段] IS NULL                     #判断字段是否为NULL

WHERE  [字段]LIKE  “_毛_”    		    #占位符模糊查 ，可能的结果为羊毛范

WHERE  [字段]LIKE”毛%”           	    #全模糊查询，姓毛    

WHERE  [字段]LIKE”%毛%”        	            #全模糊查询，包含毛  

WHERE  [字段]LIKE”%毛”          		    #全模糊查询，以毛结尾

LIMIT 10,5                                  #分页查询，表示从第10条数据开始，往后显示 5条，数据库中的下标是从0开始的

GROUP BY [字段]    			    #字段是分组的条件，一般与聚合函数联合使用

GROUP BY [字段] HAVING [条件]    	    #HAVING效果与WHERE一致HAVING用在GROUP BY 之后，WHERE用在GROUP BY之前

2.2. 函数

函数主要用与查询语句，添加给查询对象，

常见的聚合函数有(函数不止这几种）:

SELECT COUNT(ename) FROM emp

COUNT(DISTINCT [字段])          #输出字段中包含的数据个数 ，DISTINCT用于去重

SUM(DISTINCT [字段])             #输出字段中数据的总数

AVG(DISTINCT [字段])            #输出字段中数据的平均数

MAX(DISTINCT [字段])             #输出字段中数据的最大值

MIN(DISTINCT [字段])             #输出字段中数据的最小值

2.3. 约束

NOT NULL  							#作用同英文意思

DEFUALT

UNIQUE

AUTO_INCREMENT

PRIMARY KEY

FOREIGN KEY

CONSTRAINT  [ pk_[副表名]_[主表名] ] FOREIGN KEY ([主表的外键] ) REFERENCES  [副表名]  ( [副表主键] ) 

2.4. 多表关系

一对一：一个外键和一个主键相对应，比如：一个学生只有一个学号。

一对多：副表中的主键可以对应多个外键，一个主键只能对应一个外键，例如：一个学院有多个学生。

多对多：一个外键可以对应副表中的多个主键，一个主键也可以对应多个外键，所以要有一张中间表用来储存有关系的主外键，例如师生关系。

2.5. 多表连接查询

内连接查询：显示两个表中所有有关联的数据。

SELECT * FROM [表1] INEER  JOIN[表2] ON [条件];

外连接查询：左外连接：以左表为基准，将右表的数据填入到左表的结果中。左表的数据全部展示。

SELECT * FROM [表1(左表)] LEFT  OUTER  JOIN[表2(右表)] ON [条件];

右外连接：以右表为基准，将左表的数据填入到右表的结果中。右表的数据全部展示。

SELECT * FROM [表1(右表)] RIGHT  OUTER  JOIN[表2(左表)] ON [条件];

（图片源自网络，侵权请联系作者删除）

2.6. 子查询

即查询的嵌套。通过前一次查询得到的结果作为后一次查询的条件来实现查询的嵌套。

例：SELECT*FROM course WHERE id = (SELECT id FROM student WHERE NAME=’杨洋’);

2.7. 视图

基于SQL语句的结果集的可视化的表。个人理解为“语句快捷键”。

CREATE VIEW [视图名(自取)] AS

SELECT [字段名]

FROM 表名

WHERE 条件

2.8 索引

索引用于快速找出在某个列中有一特定值的行，通过避免全表查询，使数据库更高效的查询。但是，创建过多索引会占用过多空间，当我们进行CRUD（增删改）操作时，索引也需要动态维护，因此降低了数据的维护效率，提高了维护成本。因此，我们可以通过分库分表的方法，将需要增删改的表和索引查询的表分开操作。

索引分为聚集索引和非聚集索引，聚集索引物理地址连续，非聚集索引逻辑地址连续但物理地址不连续，一个表只有一个聚集索引，但可以有多个非聚集索引，但通常不超过8个，否则会严重影响维护效率。

索引分为单例索引、组合索引、全文索引、空间索引。单例索引分为普通索引（INDEX）加速查询，唯一索引（unique或primary key主键索引）。索引还分为hash类型索引和B+tree类型索引两大类：hash类型索引单条查询速度快，范围查询速度慢；B+tree类型索引层数越多，数据量呈指数增长。使用B+tree索引时，索引字段要尽量的小，B+tree索引具有最左匹配特性（即从左往右匹配）。

不同的存储引擎对索引类型的支持也不一样，比如innoDB支持B+tree索引，不支持hash索引。

（图片源自网络，侵权请联系作者删除）

索引分类
1.普通索引index :加速查找
2.唯一索引 主键索引：primary key ：加速查找+约束（不为空且唯一） 唯一索引：unique：加速查找+约束 （唯一）
3.联合索引 -primary key(id,name):联合主键索引 -unique(id,name):联合唯一索引 -index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用

2.9. 触发器

监视某种情况，并触发某种操作，触发器经常用于加强数据的完整性约束和业务规则等

触发器创建语法四要素：

1.监视地点(table)

2.监视事件(insert/update/delete)

3.触发时间(after/before)

4.触发事件(insert/update/delete)

CREATE TRIGGER[触发器名]

[触发时间]             #after/before

[监视事件]             #insert/update/delete

ON [触发地点(表名)]    

FOR EACH ROW

[触发事件]             #insert/update/delete

事务控制语句：

MySQL
事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

· 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

· 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

· 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

·
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

·
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

·
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read
uncommitted）、读提交（read committed）、可重复读（repeatable
read）和串行化（Serializable）。

· 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT
操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET
AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

· BEGIN 或 START TRANSACTION 显式地开启一个事务；

· COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT
会提交事务，并使已对数据库进行的所有修改成为永久性的；

· ROLLBACK 也可以使用 ROLLBACK
WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

· SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个
SAVEPOINT；

· RELEASE SAVEPOINT identifier
删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

· ROLLBACK TO identifier 把事务回滚到标记点；

· SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ
UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

· BEGIN 开始一个事务

· ROLLBACK 事务回滚

· COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

· SET AUTOCOMMIT=0 禁止自动提交

· SET AUTOCOMMIT=1 开启自动提交

学习总结 | 数据库 | 2019/7/15