数据库思维导图

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

后端 数据库

• 一、 初步认识数据库
  • 什么是数据库database
    • 定义：大量数据的采集保存并通过计算机加工可以进行高效访问的数据集合
    • 数据库管理系统DBMS
      • 关系型数据库 二维表
        • DB2 IBM
        • SQL Server 微软
        • Oracel 甲骨文
        • mysql 开源
      • 非关系型数据库 键值对
        • Mongo DB
  • sql命令的使用规则
    • 1. 每条命令必须以 ; 结尾
    • 2. sql命令不区分大小写
    • 3. 使用\c来终止当前命令的执行
    • 4. 快速的输入历史语句：pageup↑ pagedown↓
  • SQL语句的结构：关键字+操作对象+;
  • 数据库的分类
    • 关系型数据库
      • 数据存储：将数据保存在不同的二维表中
        • 典型代表：Oracel 甲骨文、 SQL Server 微软、 mysql 开源等
    • 非关系型数据库
      • 数据存储：存储键值对、文档、图像等数据
        • 典型代表：Mongo DB、Redis等
• 二、 数据库的基本操作知识
  • 数据库的基本操作
    • 1. 创建库，可指定字符集CREATE DATABASE 库[CHARACTER SET 某一字符集] 例如：CREATE DATABASE db3 CHARACTER Set utf8;
    • 2. 查看已有的库：SHOW DATABASES;
    • 3. 查看创建库的语句 SHOW CREATE DATABASE 库名;
    • 4. 查看当前所在的库：SELECT DATABASE();
    • 5. 切换库：USE 库名;
    • 6. 查看库中已有的表：SHOW TABLES;
    • 7. 删除库：DROP DATABASE 库名;
    • 8. 库名的命名规则：
      • A. 数字、字母、下划线组成，不能使用纯数字
      • B. 库名的唯一性，不能重名
      • C. 不能使用mysql中的关键字和特殊字符
      • D. 一般使用小写
  • 表的基本操作
    • 创建表：create table 表名( 字段名 数据类型, 字段名 数据类型, .......... 字段名 数据类型, 字段名 数据类型 )[character set 某一字符集];
    • 查看已有表的字符集：show create table 表名;
    • 查看所有表：show tables;
    • 查看表结构：desc 表名;
    • 删除表：drop table 表名;
    • 表命名规则和库命名规则一样
  • 表记录的管理 增删改查 （一条记录，即行的管理）
    • 插入记录insert
      • 插入完整的一条记录：insert into t_name values(值1,值2,值3),(值1,值2,值3); （添加的值的个数与表列字段一样）
      • 插入指定字段的记录：insert into t_name (字段名1，字段名2...) values(值1,值2...),(值1,值2...);
    • 查询select 并输出结果
      • 查看表的所有信息:select * from t_name[ where 条件]
      • 查看表中指定字段的信息:select 字段名1，字段名2 from t_name where 条件;
    • 更新update
      • update T_name set 字段名1=值1，字段名2=值2...where 条件;
    • 删除delete
      • delete from t_name where 条件；
  • 表字段的管理 增删改查（列标题的操作） alter table t_name 执行动作
    • 添加字段时必须指定数据类型， 删除字段时无需指明数据类型
    • 添加字段 add
      • alter table 表名 add 字段名 数据类型; 添加到最后一列
      • alter table 表名 add 字段名 数据类型 first; 添加到第一列
      • alter table 表名 add 字段名 数据类型 after 字段名; 添加到某一字段后面
    • 删除字段 drop
      • alter table 表名 drop 字段名;
    • 修改字段的数据类型 modify
      • alter table 表名 modify 字段名 新数据类型
        • 字段名不变，只修改字段的数据类型
    • 表重命名 rename
      • alter table 表名 rename 新表名；
    • 表字段重命名 change
      • alter table 表名 change 原字段名 新字段名 新字段数据类型；
  • 常用的数据类型
    • 数值类型
      • 整型
        • tinyint 微小整型 1个字节8位 取值范围：0-255 一般用于年龄
          • 有符号:signed -128---127
          • 无符号:unsigned 0-255举例：age tinyint unsigned
        • smallint 小整型 2个字节16位 取值范围：2**16-1→65535
        • mediumint 中整型 8388608
        • int 大整形 4个字节32位 取值范围：2**32-1 42亿多
        • bigint 极大整型 8个字节64位 取值范围：2**64-1
      • 浮点型
        • float 用法：字段名 fioat （m,n）其中m为总位数，（整数位数+小数位数），n为小数位数。 存储空间为4个字节， eg: float (5,2) 取值范围：-999.99至999.99
        • double
        • decimal 用法：字段名 decimal（m,n）限制：最多显示28个有效位数，存储时整数位数与小数位数分开存储。
    • 日期时间类型
      • date
        • yyyy-mm-dd
      • time
        • hh:mm:ss
      • datetime 不赋值返回空值
        • yyyy-mm-dd hh:mm:ss
      • timestamp 不赋值，默认返回系统当前时间
        • yyyy-mm-dd hh:mm:ss
      • 区别
      • 日期时间函数
        • now() 返回服务器当前时间
        • curdate() 返回当前日期
        • curtime() 返回当前时间
        • year(date) 返回指定时间的年份
        • date(date) 返回指定时间的日期
        • time(date) 返回指定时间的时间
      • 日期时间的运算
        • select * from t_name where 字段名 运算符 (时间-interval时间间隔单位) 时间间隔单位：1day | 2hour |1minute | 2year | 3month
    • 字符类型
      • 定长:char(定长) 取值范围：1-255，若不指定宽度，默认宽度为1，存储1个字符。
      • 变长:varchar(定长) 取值范围：1-65535，必须指定宽度，没有默认宽度。
      • text，longtext(4G),blob,longbolb(4G) 专门用来存储大量文本，无需指定宽度。
    • 枚举类型
      • 单选 字段名 enum(值1，值2，.....) 规则：只能选择其中的一个值。 eg:sex enum('男','女','未知')
      • 多选 字段名 set(值1，值2，.....) 规则：可以选择其中的一个或则同时选择多个。 eg:hobby set ('唱歌','跳舞','游泳','篮球')
  • 运算符的比较
    • 数值/字符比较
      • 数值比较：= != > >= < <=
      • 字符比较:= !=
    • 逻辑比较
      • and: 2个或多个条件同时成立
      • or： 任意一个条件成立即可
    • 范围比较
      • where 字段名 between 值1 and 值1 ：值为数字
      • where 字段名 in (值1，值2.....) :值为数字或字符
      • where 字段名 not in (值1，值2.....) :值为数字或字符
    • 匹配空/非空
      • 注意：null~空值只能用 is null 和 not is null去匹配， “”~空字符串用=或！=去匹配。
      • 空：where 字段名 is nmll
      • 非空： where 字段名 is not nmll
    • 模糊比较
      • where 字段名 like ' 表达式'; 表达式：_匹配单个字符，%匹配0到多个字符。
• 三、 mysql 查询入门 （单表查询）
  • distinct ... 作用：不显示字段的重复值 格式：distinct 字段名 <1>只能修饰普通的已经存在的字段 <2>当distinct 同时修饰多个字段时，所有字段的值都相同才能去重。 <3>distinct 不能对任何字段做聚合处理
  • 聚合函数：对表中某个字段的数据进行聚合操作 avg(字段名) →求该字段的平均值 sum(字段名) →求和 max(字段名) →求最大值 min(字段名) →求最小值 count(字段名) →统计该字段记录的个数（count不会统计null，但是会统计“”空字符串） 聚合函数的结果为一个数值，一般与分组、having等结构配合使用，很少单独使用。
  • group by ... 作用：按照某种规则对查询结果进行分组操作 格式：... group by 字段名 注意：<1> 只有分组时（没有聚合、having），先分组→后去重 group by 之后的字段名必须要为select 之后的字段名 <2> 分组聚合同时存在时 先分组再→后聚合（对每组）→再去重 如果select之后的字段名和group by 之后的字段名不一致，则必须对该字段进行聚合处理。
  • having... 作用：对查询结果进行进一步的筛选操作 <1>where 只能操作表中实际存在的字段，having可操作性由聚合函数生成的显示列 <2>分组+聚合+having→铁三角：having语句通常和group by 语句联合使用，过滤由group by 语句返回的记录集
  • order by 作用：给查询结果进行排序 格式：... order by 字段名 升序\降序 升序\降序：升序 ---asc(默认的排序方式)，降序---desc 对查询结果进行排序并不会改变表中记录的原始顺序。
  • limit... #### limit 永远放在sql查询语句的最后写。 作用：限制查询记录的显示条数。 用法1：limt n →显示n条记录（前n条记录） 用法2：limt m,n →m:第m+1条记录开始显示 n:显示n条记录 sql中的分页应用：limit(m-1)*n,n 每页显示n条记录，如何显示第m页的内容。
  • 查询表记录时做数学运算 运算符：+ - * / %
• 四、 mysql 查询进阶 （多表查询）
  • 多表无链接查询
    • 无WHERE条件的笛卡尔积方式 格式：select 字段名列表 from 表名列表 ; 字段名的书写格式-----对于单表：只写字段名；对于多表：表名.字段名； 字段名列表中的字段所涉及的表一定包含在表名列表中。 举例：select * from t1,t2; select t1.field , t2.field from t1,t2;
    • 有WHERE条件的普通多表查询 格式：select 字段名列表 from 表名列表 WHERE 条件；（多表之间必须有关联） <1>多表可以是不同的表，也可以是相同的表。 <2>多表之间的关联关系使用where 条件来表达。 <3>在笛卡尔积方式的基础上使用where 条件对表中的记录进行过滤。 <4>显示结果：只显示匹配成功的记录。
  • 多表连接查询
    • 将具有关联关系的多个表连接起来进行查询操作，多表之间必须有关联
    • 内连接查询
      • 语法：select 字段名列表 from t1 inner join t2 on 连接条件； select 字段名列表 from t1 inner join t2 连接条件 inner join t3 连接条件。...；
    • 外连接查询
      • 左连接查询
        • 以左表为主显示查询结果 语法：select 字段名列表 from t1 left join t2 on 连接条件； select 字段名列表 from t1 leftr join t2 连接条件 left join t3 连接条件。...；
      • 右连接查询
        • 以右表为主显示查询结果 语法：select 字段名列表 from t1 right join t2 on 连接条件； select 字段名列表 from t1 right join t2 连接条件 right join t3 连接条件。...；
  • 嵌套查询(子查询)
    • 定义：把内层的查询结果作为外层的查询条件 外层的查询块称为外层查询或父查询，内层的查询块称为内查询或子查询。 sql 语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。 语法格式：select 字段名列表 from 表名 where 条件（select ...from ...where...）
    • 带有比较运算符的子查询
      • 指父查询与子查询之间用比较运算符进行连接，当用户确切的知道内查询返回的是单值时，可以用= != > >= < <= 等比较运算符。
    • 带有in 谓词的子查询
      • 嵌套查询中，子查询的结果往往是一个集合，in是最经常用到的谓词。 where field in (值1，值2...值n);值为数字或字符，可以为一个或多个。
    • 带有any 或 all 谓词的子查询
      • 当子查询结果为多 值，且外层查询涉及到与子查询结果中的某个值或所有值进行比较时，使用all 或 any,使用all 或any 谓词时必须同时使用比较运算符。
    • 带有exists 谓词的子查询 (了解即可)
  • 集合查询
    • 定义：select 语句的查询结果是记录的集合，所以多个select 语句的结果可以进行集合操作。
    • 集合并，交，差操作
• 五、 mysql 索引
  • 约束
    • 定义：对表中的字段（字段值）进行一定的约束限制，以保证数据的完整性、一致性、有效性。
  • 索引 <1>定义：对数据库中表的一列或多列的值进行排序的一种结构（排序使用的算法：Btree） <2>优点与缺点： 优点：加快数据的检索速度 缺点：1、占用物理的存储空间，当表中的数据更新时，索引需要动态维护，占用系统资源，降低数据维护的速度。
  • 主键索引 primary key
  • 外键索引 foreign key
  • 唯一索引 unique
  • 普通索引 INDEX
  • 默认约束 default
• 六、 mysql的约束实例
  • 约束类型
  • 主键约束 primary key
    • 主键约束(Primary Key Constraint)要求主键列的数据唯一，并且不允许为空，主键能够唯一地标识表中的一条记录。
    • 单字段主键约束： 列名 数据类型 primary key; 或者 [constraint <约束名>] primary key(列名)； 多字段联合主键（复合主键）：primary key (字段1,字段2,.....字段n);
    • 通过修改表为列添加主键： create table book_info( book_id int, book_name varchar(20) not null); alter table book_info modify book_id int primary key; alter table book_info add primary key (book_id); alter table book_info add constraint pk_id primary key(book_id);
    • 删除主键：alter table book_info drop primary key;
  • 非空约束 not null
    • null :字段值可以为空 not null :字段值不可以为空
    • 添加非空约束的语法： 列名 数据类型 not null
    • 创建表时添加非空约束： create table book_info( book_id int, book_name varchar(20) not null );
    • 通过修改表时添加非空约束：create table book_info( book_id int, book_name varchar(20) ); alter table book_info modify book_name varchar(20) not null;
    • 删除非空约束： alter table book_info modify book_name varchar(20) ;
  • 唯一约束 uique
    • 唯—约束(Unique Constraint)要求该列唯一，允许为空，唯一约束可以确保一列或者几列不出现重复值。
    • 定义完列之后直接指定唯一约束：列名 数据类型 unique； 定义完所有列之后指定唯一约束：constraint <约束名>unique(列名)；
    • 创建表时添加唯一约束： create table book_info( book_id int, book_name varchar(20) unique not null );
    • 通过修改表为列添加唯一约束： create table book_info( book_id int, book_name varchar(20) not null); alter table book_info modify book_id int unique; alter table book_info add unique (book_name); alter table book_info add constraint uk_bname unique (book_name);
    • 删除唯一约束：alter table book_info drop index book_name ; alter table book_info drop key book_name;
  • 默认约束 default
    • 默认约束(Default Constraint)指定某列的默认值。
    • 语法：列名 数据类型 default 默认值，
    • 创建表时添加唯一约束： create table book_info( book_id int primary key, press varchar (20) default ‘工业出版社’）；
    • 修改表时添加默认约束：alter table book_info modify press varchar(20) default '工业出版社'，
    • 删除默认约束：alter table book_info modify press varchar(20),
  • 外键约束 foreign key
    • 外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。 外键对应的是参照完整性，一个表的外键可以为空值，若不为空值。则每一个外键值必须等于另一个表中主键的某个值。
    • 语法：[constraint<外键约束名> ] foreign key (列名) references <主表名>(主键)
    • 创将表时添加外键约束： create tabel bookcategory( category_id int primary key, category varchar(20), parent_id int ) create table bookinfo( book_id int primary key, book_category_id int, constraint fk_cid foreign key(book_category_id ) references bookcategory(category_id) );
    • 修改表时添加外键约束： alter table bookinfo add foreign key (book_category_id) references bookcategory (category_id)
    • 删除外键约束：alter tabek bookinfo drop foreign key fk_cid;
• 七、 mysql 进阶
  • 存储引擎
    • 定义：处理表的处理器 种类：innodb myisam memory ......
    • 基本操作
      • 查看所有的存储引擎： show engines;
      • 查看已有表的存储引擎： show create table 表名;
      • 创建表时指定存储引擎：create table 表名 (.....) engine =myisam;
      • 已有表指定存储引擎： alter table 表名 engine = InnoDB;
  • 锁
    • 锁的目的：mysql 自动加锁，解决客户端并发访问的冲突问题
    • 锁的分类
      • 读锁：用法 共享锁，加锁后别人不能更改表记录，但是可以进行查询操作， select 语句时自动加读锁。
      • 写锁：用法 互斥锁/排他锁 加锁后别人不能更改表记录，也不能进行查询操作。 insert / delete /update 语句时自动加写锁。
    • 锁粒度
      • 表级锁 myisam 存储引擎支持表级锁 myisam 存储引擎的特点：1、支持表级锁 2、独享表空间 执行查询操作多的表使用 myisam 存储引擎。
      • 行级锁 innodb 存储引擎支持行级锁 innodb 存储引擎的特点：1、支持行级锁 2、共享表空间 3、支持外键 事务操作 执行写操作多的表格使用 innodb 存储引擎。
  • mysql 调优
    • 1. 选择合适的存储引擎： 读操作多时使用 myisam存储引擎，写操作多时使用innodb 存储引擎
    • 2. 创建索引 在 select/where /order by 常涉及到的字段建立索引
    • 3. sql 语句的优化
      • a. WHERE子句中尽量避免使用不等于 != ,否则将放弃索引进行全表扫描。
      • b. 尽量避免NULL值的判断，否则放弃索引进行全表扫描。
      • c. 尽量表面OR连接条件，否则放弃索引进行全表扫描。
      • d. 模糊查询尽量避免使用前置%，否则进行全表扫描。
      • e. 尽量避免使用IN 或 NOT IN ,否则全表扫描。
      • f. 尽量避免使用select * from ....,用具体字段来代表 * ，不要返回用不到的任何字段。
  • 事务与事务回滚
    • 事务的定义：一件事情从开始发生到结束的整个过程。 事务的作用：确保数据的一致性。
    • 事务的应用： 开启事务：begin; 准备进行的sql 操作：【一条或多条sql语句，（此时自动提交功能autocommit将被禁用）】 终止事务：commit; 事务正常进行，完成时提交数据库，终止此事事务。 rollback; 事务进行发生异常，回滚，回到原始状态，终止此事务。