一、数据库的发展：
萌芽期：文件管理
第一代：层次数据库、网络数据库
第二代：SQL、关系型数据库
第三代：面向对象的DBMS(OODBMS)、对象关系的DBMS(ORDBMS)
二、关系模型和SQL
安装
service restart mariadb, 重启mariadb服务
yum install mariadb-server, 安装
systemctl start mariadb.service, 启动
ss -tanl, 查看端口号
systemctl enable mariadb.service,开机启动
mysql_secure_installation,安全设置
mysql -u root -p < test1.sql, 导入测试脚本
mysql -u root -p, 数据库密码登录
创建用户并授权
grant all on . to ‘user’@’%’ identified by ‘password’;
授权所有的权限(all),所有库的所有表(.),给(to) user用户,@ 所有地址上(’%’) identified ‘password’
flush privileges; 刷新权限,一定记住,省去不必要的麻烦
mysql>select host,user,password from user;查看授权信息
mysql>select user(); 查看当前登录的用户
mysql>select database(); 查看当前数据库
三、SQL语句:
结构化查询语言 Structured Query Language
一、SQL分为：
| 分类 | 含义 |
|---|---|
| ①DDL数据定义语言 | 负责数据库定义、数据库对象定义。由create，alter，drop |
| ②DML数据操作语言 | 负责绝对数据库对象的操作，CRUD增删改查 |
| ③DCL数据控制语言 | 负责数据库权限访问控制，grant和revoke两个指令组成 |
| ④TCL事务控制语言 | 负责处理acid事物，支持commit、rollback指令 |
1.DCL控制语言
①授权grant
grant all on employees.* to ‘user’@’%’ identified by password;
②撤销revoke
revoke all on . from user;
2.DDL数据定义语言
删除用户
drop user ammmap 删除用户(慎用!删除就删除了)
删除数据库
drop database if exists db_name;删除数据库,`不存在则警告
创建数据库
creat database if not exist test character set utf8mb4 collate
utf8mb4_general_ci;
creat database if not exist test character set utf8mb4;
| 关键字 | 含义 |
|---|---|
| character | 指定字符集 |
| collate | 指定字符集的校对规则,用来做字符串的比较的,例如a,A谁大? |
创建用户
creat user dev@ip identified by 'password’
创建表
mysql是行存数据库,数据是一行行存储的,列必须固定有多少列
| 关键字 | 含义 |
|---|---|
| 行,row | 也称为record记录,元组 |
| 基数 | 元组、行的个数 |
| 列,column | 也称字段field,属性 |
| 维数 | 指的是关系中属性的个数,也就是列的个数 |
| 域 | 字段的取值范围是domain |
| 操作 | 含义 |
|---|---|
| DESC table_name | 查看表的设置信息 |
| DESC table_name 'column_name’ | 查看表中的某一列的设置信息 |
候选键:
关系中,能唯一标识一条元组的属性或属性集合,称为候选键
习惯性的多会多加一个id的候选键
候选键是一个属性或者属性集合,这个集合下面的数据都不重复
主键(primary key):
表中一列或者多列组成 key,也就是通过这一个或者多个列能唯一的表示的一条记录。也就是被选择的候选键
主键的列不包含空值null。主键往往设置为整型、长整型，可以自增auto_increament字段
表中可以没有主键,但是一般表设计中,往往都会有主键,以避免记录重复。
外键(foreign key):
当一个关系中的某个属性或属性集合与另一个关系(也可以是自身)的候选键匹配时,就成这个属性或者这个属性集合为外键
索引index
为了快速检索用的。空间换时间,显著提高查询效率
可以对一列或者多列字段设定索引
| 索引 | 含义 |
|---|---|
| 主键索引 | 主键会自动建立主键索引，主键本身就是为了快速定位唯一记录的 |
| 唯一索引 | 表中的索引列组成的索引必须唯一，可以为空,非空必须唯一 |
| 普通索引 | 没有唯一性的要求，就是键了一个字典的目录而已 |
在mysql中,innoDB和nyisam的索引数据结构可以使用hash和Btree,默认使用btree
索引可以提高查询所读,却影响增删改的效率。
约束
为了保证数据库的完整性，数据模型还必须支持完整性约束。
| 约束 | 含义 |
|---|---|
| 域约束 | 限定了表中字段的取值范围 |
| 实体完整性 | primay key约束定义了主键,就定义了主键约束 |
| 引用完整性(外键约束) | 外键定义中, 可以不是引用另一个张表的主键, 但是往往实际只会关注引用主键。 |
外键对于增删改的规则要求：
| 规则 | 含义 |
|---|---|
| 插入规则 | 不需要指定。外键列插入的值必须是表a中存在的主键值 |
| 更新规则 | 定义外键约束时指定规则即可 |
| 删除规则 | 定义外键约束时指定规则即可 |
定义外键数据的操作：
| 级别 | 含义 |
|---|---|
| CASCADE | 级联，从父表删除或更新行,会自动删除字表中匹配的行 |
| SETNULL | 从父表删除或更新行,会设置字表中对应的外键为Null, |
| RESTRICT | 如果父表删除主键,如果字表引用了,则拒绝父表的删除和更新 |
| NOACTION | 拒绝对父表的删除和更新操作 |
外键约束,是为了保证数据完整性、一致性、杜绝数据冗余、数据错误
实体联系E-R
数据库建立需要收集用户需求，设计符合企业要求的数据模型，而构建这种
模型需要方法，这种方法需要称为E-R实体-联系建模，也出现了一种建模语言
UML–统一建模语言
实体间联系的类型：
| 关系 | 解释 |
|---|---|
| 一对多 | 一个部门多个员工。员工外键，部门主键 |
| 多对多 | 多个部门多个员工。建立第三张表，联合主键 |
| 一对一 | 一个部门,一个员工 |
视图:
也称为虚表,看起来像表。
视图的作用：
- 简化操作，将复杂查询SQL语句定义为视图，可以简化查询
- 数据安全，视图可以只显示真实表的部分列，或计算后的结果，隐藏真实表
的数据
数据类型:
| 数据类型 | 含义 |
|---|---|
| tinyint | 1字节,带符号的范围是-128到127,无符号范围是0-255 |
| bool | 或者boolean就是tinyint,0表示假,1表示真 |
| smallint | 2字节,带符号的范围是-32768 - 32767,无符号是0-65535 |
| int | 整型, 四字节,和ingeter类型相同 |
| bigint | 长整型,8字节,无符号最大 2**32 |
| float | 单精度浮点数精确到大约7位小数 |
| double | 双精度浮点数精确到大约15位小数 |
| Data | 日期,支持1000-01-01到9999-12-31 |
| DATATIME | 日期,支持1010-01-01 00:00:00 到9999-12-31 13:59:59 |
| TIMESTAMP | 时间戳,范围是1970 - 2037 |
| char | 固定长度,右边填充空格已达到长度要求,范围0-255 |
| varchar | 变长字符串, M表示最大列长度。 |
| text | 大文本,最大长度为65535个字符 |
| BLOB | 大字节,最大长度65535字节的blob列 |
关系操作:
关系就是二维表,关系操作就是对表的操作
- 选择(selection):从关系中选择出满足给定条件的元组(行)
- 投影(projection):选出若干属性组成新的关系(表)
- 连接(join):将不同的两个关系连接成一个关系
3.DML数据操作语言
①insert增加: 注意缺省值和自动递增的主键啥的
insert into tab_name(col_name) values(values);增加一行内容
insert into tab_name(col_name) values(values) on duplicate key update col_name = value; 如果主键或者伪意见冲突就执行update后边的内容
insert ignore into tab_name(col_name) values(values);忽视警告
②update更新:
update tab_name set col_name=value where key=?
更新一定要加条件,千万注意
③delete删除:
delete tab_name where condition
删除一定要加条件
④select查找:
首先查询一个库中有几张表
select count(*) tables, table_schema from information_schema.tables where table_schema=‘test’
show tables; 查看当前库中的所有表;
WHERE字句:
=、<>、<、>、<=、>=、
where id between a and b 注意是闭区间
like是模糊匹配
where id in (10016,10001,10002)
and、or：
| 语句 | 含义 |
|---|---|
| order by字句 | 可以选择多个参考进行排序 dest降序 |
| group by | 注意分组和不分组的聚合情况 |
| having | 字句用来过滤分组、聚合过的结果 |
| distinct | 不返回重复记录 |
聚合函数
| 语句 | 含义 |
|---|---|
| count | 返回记录中记录的数目,如果指定列,则返回非null值的行数 |
| count(distinct expr) | 返回不重复的非null 值的行数 |
| avg([distict] expr) | 返回平均值或者不同值的平均值 |
| min(expr), max(expr) | 返回最小值最大值 |
子查询(嵌套查询)
查询语句可以嵌套,内部查询就是子查询
子查询必须在一组小括号内
子查询不能使用order by
子查询,如果from的是一个查询的得到的结果,注意使用as alias别名
这样select 之后就可以指定alise from查询了,还有起别名是个好习惯
连接john:
| 连接 | 含义 |
|---|---|
| 交叉连接 cross join(可以省略) | select * from a cross join b; |
| 内连接 inner join(可以省略为john) | select * from a join b; |
| 等值连接 on | select * from a join b on a.id = b.id; |
| 自然连接, natural join, 是特殊的等值连接 | select * from a natural b; |
| 左连接 left join,配合on使用 | select * from a left join b on a.id = b.id; |
| 右连接 right join,配合on使用 | select * from a right join b on a.id = b.id; |
总结
- 内连接: 获取两个表中字段匹配关系的记录
- 左连接: 获取左表所有记录,即使右表没有对应匹配的记录
- 右连接:获取右表所有记录,即使坐标没有对应匹配的记录
自连接:自己和自己连接
正则表达式
select * from test where name REGEXP ‘^A’;
使用REGEXP操作符进行正则表达式进行匹配。和python几乎通用
4.TCL事务控制语言
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}
select @@global.tx_isolation 查看隔离级别
select @@tx_isolation 查看隔离级别
事务即要做的一系列操作；
具有事务的数据库必须具有下面四个属性(acid):
| 属性 | 解释 |
|---|---|
| 原子性atomicity | 一个事务是不可分割的工作范围,要么成功要么失败回退，不会结束在中间的某个环节。 |
| 一致性consistency | 事务开始之前和结束之后,数据库的完整性约束没被坏。 |
| 隔离性isolation | 数据库允许多个并发事务同时对数据读写和修改的能力。隔离性可以防止多个事务并发执行由于交叉执行而导致数据的不一致。 |
| 持久性durability | 事务处理结束后，对数据的修改是永久性的。 |
隔离性带来的问题:
- 1.更新丢失,已被数据库修复
- 2.脏读
- 3.不可重复读
- 4.幻读
隔离级别:
| 等级 | 含义 |
|---|---|
| read uncommitted | 读未提交,未解决脏读 |
| read committed | 读以提交,解决脏读,Oricle的默认隔离等级。A和B,A修改的这条数据,只有A提交,B才能读到提交过的这条数据,无论B是否提交了 |
| repeatable read | 可重复读,解决不可重复读。A和B,A无论是否修改提交,B读到的都是一样的数据,除非B提交之后再读。是快照读,而且A事务修改的数据也会被上锁,其他事务不能修改这条记录，读也读不到当前的，只能读取到快照的数据。 |
| serializable | 可串行化,所有问题全部解决。A事务结束再进行B事务 |
理解幻读和可重复读的区别：
幻读和不可重复读都是指的一个事务范围内的操作受到其他事务的影响了。
只不过幻读是重点在插入和删除，不可重复读重点在修改
锁机制可以避免不可重复读,因为当事务A准备处理一条数据时,可以将当前的原子操作进行上锁,这样这条数据别人都碰不到了,但是只是针对的当前的这条或者多条数据,其他的数据并没有被上锁,也就意味着其他的数据还是可以被修改,插入,删除,所以也就此可以理解不可重复读读取的是当前对上锁的这条或者多条数据
而幻读是读取的一条或者多条数据的上下文,不可以通过锁来控制,除非是一把类似gil的大锁,我操作,比人就不可以进行事务了,但是这样就是串行,效率极低
不可重复读是针对的某一行或者某一个值,小范围的上锁就解决了，行锁
幻读针对的是一系列值,所以不能上锁,只能通过serializable隔离级别控制，表锁
不可重复读导致的问题是:不可重复读的读取到的信息都是别的事务已经提交过的,在避免脏读的基础上,我并没有做出改进,仅仅只是避免了脏读。
可重复读在于保证了一个事务不会修改已经由另一个事务读取但未提交的数据
对于隔离等级的理解
1、Serializable (串行化)：最严格的级别，事务串行执行，资源消耗最大;
2、REPEATABLE READ(重复读) ：保证了一个事务不会修改已经由另一个事务读取但未提交(回滚)的数据, 避免了“脏读取”和“不可重复读取”的情况，但不能避免“幻读”，但是带来了更多的性能损失。
3、READ COMMITTED (提交读)：大多数主流数据库的默认事务等级，保证了一个事务不会读到另一个并行事务已修改但未提交的数据，避免了“脏读取”，但不能避免“幻读”和“不可重复读取”。该级别适用于大多数系统。
4、Read Uncommitted(未提交读)：
事务中的修改，即使没有提交，其他事务也可以看得到，会导致“脏读”、“幻读”和“不可重复读取”。
在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。
行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。
最后对于隔离性的理解：
1.更新丢失,
2.脏读,一个事务读取到另外一个事务为提交的数据
3.不可重复读,就是前后两次读取的单条记录不一样
4.幻读,前后读取的数据集不一样
对于四个等级的理解:
ru:解决了更新丢失
rc:解决了脏读,只读取到了以提交的那次修改。因为一个事务操作一条数据时,会对这条数据上锁,行级锁,单条记录别人只能读取这条记录被上锁前的数据和已提交的这个记录,所以还会出现不可重复读。而且其他数据由于没上锁的缘故，比的事务都可以操作提交，所以可能出现幻读。
rr:解决了脏读,因为如果当前事务不提交,读取的另外一个事务操作的那条记录永远都是上锁前的快照读, 那个事务操作外的其他记录因为没上锁,可能别的其他的事务操作了,依然是可以读取到的,所以仍然可能出现幻读的现象。
s：实现了纯粹的串行，一个一个事务排着队等待执行，表级锁
对于mysql，set autocommit=1的话,每次操作完一条语句都会自动提交,可以启两个会话，set sutocommit=1,然后设置set transaction isolation level read commit,通过修改提交一个row,理解不同的隔离等级。
常见数据库的事务隔离
| 数据库 | 默认隔离等级 |
|---|---|
| mysql | 可重复读 repeatable read |
| oracle | 读提交 read committed |
| sqlserver | 读提交 read committed |
| DB2 | 读提交 read committed |
| postgresql: | 读提交 read committed |
数据库和数据仓库的区别:
OLTP: 联机事务处理 on-line transaction processing
OLAP: 联机分析处理 on-line analytical processing
分别对应行存储和列存储
707
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
