数据库的复习

距大学学完数据库已经很长一段时间，即使当时学的好也已经忘了，再看一遍，打算记录一下，看的课本，主要为了工作，看的Mysql。

数据库管理系统通过调用调用操作系统的进程管理、内存管理、设备管理及文件管理等服务，为数据库用户提供管理、控制数据库中各种数据库对象和数据库文件的接口。

常用的数据模型：层次模型、网状模型、关系模型、面向对象模型

关系型克服了层次数据库横向关联不足的缺点，也避免了网状数据库关联过于复杂的问题。

关系：实际上是一张二维表，表示逻辑结构，而不是物理结构。

 

sql语句的特点：

非过程化的语言：只需说明做什么，不需说明怎么做

是统一的语言：可用于所有用户

是关系型数据库的公共语言

 

sql语言的组成：

数据定义语言：DDL：create，alert、drop

数据操纵语言：DML：insert，update，delete，query

数据控制语言：DCL：数据库的某种特权、事物的时间和效果、数据库实行监视

嵌入式sql语言的使用：

 

为了优化存储空间、便于数据排序、数据表的每一列要求指定数据类型

不建议使用复合主键，他会给表的维护带来不便，建议向表中添加一个没有实际意义的字段作为该表的主键

 

约束分为：表级约束和字段级约束，表级约束是对表的几个字段的约束，并不是对整个表，

约束的形式主要为：

主键约束：一张表只允许设置一个主键

外键约束:mysql有的引擎不支持外键约束

非空约束

唯一约束：一张表可以有多个唯一约束，满足唯一约束的字段可以取null

检查约束：mysql暂不支持

自增约束：一张表里只能有一个

删除约束 ：alter table

 

E-R图

三个要素：实体、关系、属性；矩形实体，椭圆属性，棱形关系；属性是一个最小的不可再分单元

MySql的单进程多线程的架构决定了它属于轻量级的数据库

MySql的体系结构：https://www.cnblogs.com/Survivalist/p/7954977.html

create database bookstore;

创建一个数据库后，会自动生成相应的目录，在目录下有一个db.opt,它记录的是字符集和排序规则等信息

alter database bookstore character set utf8;

show database;

information_schema 其中保存着MySql服务器所维护的所有的其他数据库的信息，mysql 数据库存储了 MySQL的账户信息及Mysql的访问权限。

show create database bookstore;

use bookstore;

drop database bookstore;//这是一个不可恢复的过程。

数据库对象：表、视图、索引、存储过程、函数、触发器

create table 【if not exists】 表名（

字段名  数据类型 【约束】

） 【engine = InnoDB】

设置引擎 engine=存储引擎 默认的是InnoDB

如果使用 if not exists 语句时 系统不会检查已有的表的结构是否与打算新创建的表的结构一致，系统只会查看表名是否存在。

show tables；

AUTO_INCREAMENT 这个字段必须被索引，即定义为primary key 或unique 。而且必须为 ＮＯＴ　ＮＵＬＬ，每个表最多只有一个这样的字段，字段值从1开始。当插入的时候可以插入 null 或者省略字段

 ＤＥＦＡＵＬＴ

ＮＯＴ　ＮＵＬＬ

ＵＮＳＩＧＮＥＤ

ＰＲＩＭＡＲＹ　ＫＥＹ　必须定义为　ｎｏｔ　ｎｕｌｌ　

ＵＮＩＱＵＥ　　唯一索引

ＦＯＲＥＩＧＮ　ＫＥＹ　只有ＩｎｎｏＤＢ　是支持外键机制的，但在实际应用中尽量减少外键的参考

ｕｓｅｒｓ　表

ｄｅｓｃｒｉｂｅ　ｕｓｅｒｓ

添加字段

ａｌｔｅｒ　ｔａｂｌｅ　ｕｓｅｒ　ａｄｄ　ａｄｄｒｅｓｓ　ｖａｒｃｈａｒ（５０）　ｎｏｔ　ｎｕｌｌ　；

ａｌｔｅｒ　ｔａｂｌｅ　表名　ａｄｄ　新字段名　类型　【约束条件】　【ｆｉｒｓｔ｜　ａｆｔｅｒ】

修改数据类型

ａｌｔｅｒ　ｔａｂｌｅ　表名　ｃｈａｎｇｅ　原字段名　新字段名　数据类型

ａｌｔｅｒ　ｔａｂｌｅ　表名　ｍｏｄｉｆｙ　字段名　数据类型

删除字段

ａｌｔｅｒ　ｔａｂｌｅ　ｕｓｅｒｓ　ｄｒｏｐ　ａｄｄｒｅｓｓ

添加约束条件

ａｌｔｅｒ　ｔａｂｌｅ　ｕｓｅｒｓ　ａｄｄ　ｃｏｎｓｔｒａｉｎｔ　ｐｈｏｎｅ＿ｕｎｉｑｕｅ　ｕｎｉｑｕｅ　（ｔｅｌｅｐｈｏｎｅ）

ａｌｔｅｒ　ｔａｂｌｅ　表名　ａｄｄ　ｃｏｎｓｔｒａｉｎｔ　约束名　约束累类型　（字段名）

删除主键约束

ａｌｔｅｒ　ｔａｂｌｅ　表名　ｄｒｏｐ　ｐｒｉｍａｒｙ　ｋｅｙ　｜ｆｏｒｅｉｇｎ　ｋｅｙ｜ｉｎｄｅｘ

ａｌｔｅｒ　ｔａｂｌｅ　表名　ｄｅｆａｕｌｔ　ｃｈａｒａｃｔｅｒ＝新的字符集

修改表名

ａｌｔｅｒ　ｔａｂｌｅ　表名　ｒｅｎａｍｅ　ｔｏ　新表名

ｒｅｎａｍｅ　ｔａｂｌｅ　表名　　ｔｏ　新表名

ｄｒｏｐ　ｔａｂｌｅ　【ｉｆ　ｅｘｓｉｔｓ】　表名

与其他数据库管理系统不同，ＭｙＳＱｌ　提供了插件式的存储引擎（ｐｌｕｇｇａｂｌｅ）。存储引擎是基于表的。同一个数据库，不同的表，存储引擎可以不同；同一个数据库表在不同的场合可以应用于不同的存储引擎。

在ｏｒａｃｌｅ　和ｓｑｌＳｅｒｖｅｒ　等数据库中只有一中存储引擎，所有的数据存储管理机制都一样，但ＭｙＳＱｌ　提供了多种。常用的是ＩｎｎｏＤＢ　和　ＭｙＩＳＡＭ　存储引擎。具体的介绍，就是上面的连接了。

Mysql 的基本数据类型

 

整数类型：TinyInt SAMLLINT MEDIUMINT INT(INTEGER) BIGINT

int(8) 表示当数值宽度小于8位时在数字面前填满宽度，如果在数字位数不够时需要用0填充，则可以使用关键字 zerofill，但是在插入的整数位大于指定的显示宽度时，将按照整数的实际值进行存储。

 

小数类型：FLOAT(单精度) DOUBLE（双精度） DECIMAL（定点小数） （M,D）M表示该数值一共可显示M位数字，D表示该数值小数点后的位数，不指定时，浮点数按照实际数值存储，而decimal 默认的的整数位10，小数位0。定点数在Mysql内部以字符串的形式存储，比浮点数更精确，适合用来表示货币等精度高的数据，浮点数存在误差等问题。

 

字符串类型： CHAR VARCHAR TINYTEXT TEXT MEDIUMTEXT LONGTEXT

char(n) 定长字符串，数据在存储时会删除尾空格符，varchar（n）变长字符串会保留尾空格符

 

日期时间类型：DATE TIME DATETIME TIMESTAMP YEAR

date yyyy-mm-dd

time: hh:mm:ss

datatime: yyyy-mm-dd hh:mm:ss

timestamp:yyyy-mm-dd hh:mm:ss  ；它与时区有关，如果一个表里定义了两个timestamp类型，第一个默认的是当前时间，第二个默认的是全0；set time_zone='+10:00' 设置时间为东10区；

year : 两位70-99 表示：1970-1999；01-69:2001-2069；四位：1901-2155

 

复合类型：ENUM SET

enum 只须取某一个值，可以为null，最多65535个，而set 允许取得多个值，最多64个

二进制类型：用的比较少

 

比较运算符：between and         in      like   regexp      is null        is not null

字符集的更改：在配置文件中更改，永远有效，如果使用命令，只在当前的连接中有效。

 

insert into table select ( name1,name2....) from **

 

replace 和 insert 用法很像，但是insert当插入的主键，唯一冲突时报错，但是replace的默认操作时删除原先的进行覆盖。

delete from **

truncate 和 delete 很像，但是得了特对于 自增型字段，它是接着上一个的，而truncate是初始值开始的。

查询语句：

在查询的时候其实不建议使用 * 的，这样通常会降低查询速度。

select sqrt(25) as 平方根

select distinct u_id from orders;

order by 是根据查询结果的一个字段或多个字段进行排序，默认是升序

desc 是降序，asc是升序

 order by id,date desc //id 升序 date 降序

limit [start,] count; start default 0;

条件查询：只能能在相同数据类型之间进行比较，使用字符串时必须使用单引号。

where 条件查询：常与 not and or 一起，优先级就是这个顺序。 where price 【not】 between 20 and 40; is [not] null;

模糊查询： 【not】like // %:代表任意多个字符，_  ：代表任意一个字符；

分组查询： count（列名）会忽略 null的行。像 sum()、avg()、max（）、min（）；group by ..... //having 适合group by 结合的， where 语句的作用对象是基本表或者视图，而 having作用的对象是组，即一个在分组之前进行过滤，一个是在分组之后进行过滤。

SELECT dept,COUNT(user_name) count_tmp FROM ec_uses GROUP BY dept HAVING count_tmp>1;

值得注意的是having后面跟的条件判断的字段必须是聚合函数返回的结果，否则sql会报错。（unknow coloum " " in having clause）

 

表的连接：内连接、外连接、交叉连接

内连接：挑选出符合连接条件的数据，如果数据不满足连接条件，则将其丢弃

外连接：有主从表之分，因此分为左外连接、右外连接、全外连接；匹配的保存，没有匹配的，主表的行还是保存，其他以null补充。

自连接：

交叉连接：7，4  7*4=28 笛卡儿积 又where的时候，是笛卡儿积减去不满足where条件的语句。

子查询：在select 的语句中包含了另一个select语句，返回单行的是=；返回多行的是 where <列名>【not】in 、where 【not】exists（只是返回逻辑的真假，没有列名的）、where id > any|all|some（some 与any 同义）

联合查询：合并两个或者多个查询语句的结果集；select 语句1 union 【all】 select 语句2

 

视图。在现实生活中，我觉得并不是经常被使用，我觉得可能是因为他的修改删除的改动的局限性。视图是一张虚拟的表，并不像表和索引那样需要占用内存存储空间，视图中保存的仅仅是一条select语句。对视图的更新，删除，插入，不能同时修改多个表，只能修改一个表，因此必须保证插入和更新的列属于同一个表，这就限制了他的使用，对于一个用户来说，这就限制了他的使用，因为用户是不需要知道数据库的结构的。

create view view_name 

as select .... from ... (where)

describe(=des) view_name

show create view view_name

drop view view_name;

 

触发器：其实接触的也很少，一个原因是是mysql的存储引擎只有InnoDB可以保证他的原子性，但是大部分的不能保证他的原子性，还有的就是触发器实现的逻辑，我们可以在前端，中间层，服务端，过滤掉，这样的逻辑更常见，进简单。这也就是我的理解，所以触发器也不是经常被使用的。

delimiter $$

create trigger course_insert_before_trigger before|after insert|update|delete

on course for each low

begin

if(new.up_limit=60||new.up_limit=80)then

set new.up_limit=new.up_limit; 

else insert into mytable valuses(0);

end if;

end

$$

delimiter;

 

事务：start transaction .......commit

使用 start transaction 命令就会隐式的关闭自动提交（相当于 set autocommit=0）

可以隐式的调用commit命令的语句有：数据定义语句DDl：create、alter、drop；权限管理和账户管理：grant、revoke、set password、create user、drop user、rename user; 锁语句：lock tables、unlock tables；

rename table、truncate table；

事务回滚：可以撤销未提交的事务所做的各种修改；

事物的四大特性：ACID

原子性（atomicity）：每个事务是不可分割的单元

一致性（consistency）：数据库能够处于一致性的状态

隔离性（isolation）：各个事务不能相互干扰

持久性（durability）：事务一旦被提交，其改变是永久性的，不能在被撤销。

 

事务的隔离级别：越往下隔离级别越高，并发性就越差

read uncommitted(读取未提交的数据)

read committed

repeatable read(可重复度)

serializable

 

 

数据问题：

脏读：一个事务读取另一个事务未提交的数据；

不可重复读：在同一个事务中，两条相同的查询语句的结果不一致。

幻读：当前事务读不到其他事务已提交的修改（我怎么感觉不可重复读和幻读矛盾呢）

不可重复读和幻读的区别：https://blog.****.net/v123411739/article/details/39298127

幻读是对应新的数据，不可重复读对应已存在的数据，这只是我的理解，但还是觉得矛盾，你想都到新的数据，你又想两次读到结果一样，我觉得还是矛盾，mysql的默认隔离级别是：repeatble read、 而orcal的默认级别是：read committed；

上图来自：https://www.cnblogs.com/xll1025/p/6429157.html