数据库系统

发表于 更新于 分类于
本文字数： 2.6k 阅读时长 ≈ 9 分钟

前言

记录一些数据库的基础和重要知识点。

数据库基本操作

创立表

  • create

    1
    2
    3
    4
    5
    6
    create table student
    (
      sno int primary key not null,
      sname varchar(30) not null,
      ssex varchar(2)
    )

表中数据 - 增删改

  • insert

    1
    2
    insert into student values(1,'Sammy','f')
    insert into student(sno,sname) values('2','Tom')

  • update

    1
    2
    3
    update student
    set sname = 'Teddy'
    where sno = 1

  • delete

    • 删除特定值
    1
    2
    delete from student
    where sname = 'Tom'
    • 删除所有行
    1
    2
    3
    4
    -- 第一种
    delete from student
    -- 第二种
    delete * from student

删除表

  • drop

    1
    drop table student

修改表

  • alter

    • 增加列

      1
      2
      -- 增添一列班级
      alter table student add sclass varchar(255)

    • 删除列

      1
      alter table student drop sclass

    • 改变列数据类型

      1
      alter table student alter sclass int

视图

  • 创建视图

    1
    2
    3
    4
    create view stu_view as
    select sno,sname
    from student
    where sname = 'Ted'

  • 撤销视图

    1
    drop view stu_view

授权GRANT

1
grant 权限 on 数据库对象 to 用户

  • 授权普通用户DML操作

    1
    2
    3
    4
    grant select on testdb.* to common_user@'%'
    grant insert on testdb.* to common_user@'%'
    grant update on testdb.* to common_user@'%'
    grant delete on testdb.* to common_user@'%'

  • 授权开发人员等DDL操作

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    -- grant 创建、修改、删除 MySQL 数据表结构权限。
    grant create on testdb.* to developer@'192.168.0.%';
    grant alter on testdb.* to developer@'192.168.0.%';
    grant drop on testdb.* to developer@'192.168.0.%';

    -- grant 操作 MySQL 外键权限。
    grant references on testdb.* to developer@'192.168.0.%';

    -- grant 操作 MySQL 临时表权限。
    grant create temporary tables on testdb.* to developer@'192.168.0.%';

drop VS delete VS truncate

  • drop（丢弃数据）: drop table 表名 ，直接将表都删除掉，在删除表的时候使用。

  • truncate （清空数据）: truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长id又从1开始，在清空表中数据的时候使用。

  • delete（删除数据） : delete from 表名 where 列名=值，删除某一列的数据，如果不加 where 子句和truncate table 表名作用类似。

truncate 和不带 where 子句的 delete、以及 drop 都会删除表内的数据。

truncate 和 delete 只删除数据不删除表的结构（定义）

执行drop语句，此表的结构也会删除，也就是执行 drop 之后对应的表不复存在。

内连接 VS 外连接

索引

定义

索引就像是书的目录一样，为了提高数据的查询效率。

缺点：

  • 创建索引和维护索引需要耗费时间，随着数据量的增加而增加

  • 占用物理空间

  • 对表进行增删改查需要动态维护索引，降低了数据的维护速度

创建的原则：

  • 在最频繁使用的、用以缩小查询范围的字段上建立
  • 在频繁使用的、需要排序的字段上建立

不适合创建索引的情况：

  • 对于查询中很少涉及的列或重复值较多的列
  • 对于一些特殊数据类型，如文本（text）etc…

创建及删除

1
2
3
4
-- create
create index index_stu on student(sname)
-- drop
alter table student drop index index_stu on student

分类

  • 数据结构

    • 树索引(O(log(n)))
    • Hash索引

  • 物理存储

    • 聚集索引
    • 非聚集索引

  • 逻辑

    • 普通索引
    • 唯一索引
    • 主键索引
    • 联合索引
    • 全文索引

聚簇索引

是对磁盘上实际数据、重新组织、以按指定的一个或多个列的、值排序的算法。

特点：

  • 存储数据的顺序与索引的顺序一致
  • 主键会默认创建聚簇索引
  • 一张表只允许一个

聚簇索引 VS 非聚簇索引

  • 聚簇索引的叶子节点就是数据节点
  • 非聚簇索引的叶子结点仍是索引节点，只不过有指向对应数据块的指针

数据库存储过程

数据库隔离级别

  • READ_UNCOMMITTED
  • READ_COMMITTED
  • REPEATABLE_READ
  • SERIALIZABLE

MySQL默认：

考什么保证实现

B+数

定义

  • B+树是基于B树和叶子结点顺序访问指针进行实现，拥有B树的平衡性，并通过顺序访问指针来提高区间查询的性能
  • 一个节点的key值从左到右非递减排列，if key has key i and key i+1 and not null，then key i <= key <= key i+1
  • 查找操作，在根结点进行二分查找，找到一个key所在指针，递归查找，直到查找到叶子结点，在叶子结点进行二分查找
  • 插入、删除会破坏树的平衡性，所以在插入删除后需要做一个分裂、合并、旋转等来维护树的平衡性

优点

  • B+只有叶子节点才存储数据，B每个节点都存储，考虑到IO性能

查寻性能的优化

  1. 减少请求的数据量
    • 只返回必要的列，少用select *
    • 返回必要的行，使用limit
    • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别是要在查寻的数据经常被重复查询时
  2. 减少服务端扫描的行数
    • 使用索引来覆盖查询
----------到结尾啦！！ Hoohoo----------