数据库设计与操作的重要知识点

(原创) 数据库的重要知识点 😄 （复习用）

---

一、数据库单表设计与查询

• DDL--数据库定义语言

• 数据库操作
  • 查询数据库

    • 查询所有数据库：show databases;

    • 查询当前数据库：select database();

  • 创建数据库

    • 创建数据库：create database [if not exists] 数据库名;

  • 删除数据库

    • 删除数据库：drop database [if exists] 数据库名;

  • 使用数据库

    • 使用数据库：use 数据库名;

---

• 表操作
  • 创建表

     create table 表名
     (
        字段名1 数据类型(长度) [comment '注释'] [约束],
        字段名2 数据类型(长度) [comment '注释'] [约束],
        字段名3 数据类型(长度) [comment '注释'] [约束],
        ...
     ) comment '表注释';
    

  • 删除表

    • 删除表：drop table 表名;

  • 查询表

    • 查询当前数据库所有表：show tables;

    • 查询表结构：desc 表名;

    • 查询表的创建语句：show create table 表名;

  • 修改表

    • 添加字段：alter table 表名 add 字段名 数据类型(长度) [comment '注释'] [约束];

    • 修改字段类型：alter table 表名 modify 字段名 新数据类型(长度) [comment '注释'] [约束];

    • 修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型(长度) [comment '注释'] [约束];

    • 删除字段：alter table 表名 drop column 字段名;

    • 修改表名：rename table 表名 to 新表名;

---

• 约束
  • 主键约束

    • primary key

  • 唯一约束

    • unique

  • 非空约束

    • not null

  • 默认约束

    • default

  • 外键约束

    • foreign key

    • 1.create table 表名
      (
      字段名1 数据类型(长度) [comment '注释'] [约束],
      ...
      [constraint 外键名 foreign key(字段名) references 主表名(主表字段名)]
      ) comment '表注释';

    • 2.alter table 表名 add constraint 外键名 foreign key(字段名) references 主表名(主表字段名);

  • 检查约束

    • check

---

• 数据类型
  • 数值类型 ？？？
  • 字符串类型？？？
  • 日期时间类型 ？？？

参考同级目录下文件MYSQL数据类型.xlsx

注意：数据库名和表名不能包含特殊字符，表名不能包含空格。

• DML--数据库操作语言

• 表操作
  • 添加数据

    • 指定字段添加数据：insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);

    • 全部字段添加数据：insert into 表名 values(值1,值2,值3,...);

    • 批量添加数据(指定字段)：insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...);

    • 批量添加数据(全部字段)：insert into 表名 values(值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...);

  • 修改数据

    • update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3 [where 条件];

  • 删除数据

    • delete from 表名 [where 条件];

数据库获取当前时间函数：now()
数据库获取当前日期函数：curdate()
delete语句不能删除某一个字段的值，如果要删除某一个字段的值，需要使用update语句，将该字段的值设置为null。

---

• DQL--数据库查询语言

• 查询数据
  • 基本查询

    • 查询所有字段：select * from 表名;

    • 查询指定字段：select 字段名1,字段名2,字段名3,... from 表名;

    • 查询别名：select 字段名1 as 别名1,字段名2 as 别名2,字段名3 as 别名3 from 表名; //as可以不写，别名有空格会报错，用'包裹'。

    • 去除重复记录：select distinct 字段名1,字段名2,字段名3,... from 表名;

  • 条件查询

    • select 字段名1,字段名2,字段名3,... from 表名 where 条件;

    • 比较运算符：=,>,<,>=,<=,<>,!=,!>,between and,in,like,is null,is not null,and,or,not

    • 逻辑运算符：and,or,not

    • 模糊查询：like,%代表0个或多个字符，_代表一个字符

    • 范围查询：in,not in

    • 空值查询：is null,is not null

    • 优先级：小括号，not，比较运算符，逻辑运算符，优先级由高到低。

  • 聚合函数

    • count(*) 统计行数

    • sum(字段名) 求和

    • avg(字段名) 求平均值

    • max(字段名) 求最大值

    • min(字段名) 求最小值

    • 1.聚合函数不能直接使用在where后面，需要使用group by分组查询。
      2.null值不参与运算。

  • 分组查询

    • group by 分组字段列表

    • having 分组后条件列表

    • select 字段列表 from 表名 [where 条件] group by 分组字段列表 [having 分组后条件列表];

    • 1.分组后，查询的字段只能是分组字段和聚合函数。
      执行顺序：where>聚合函数>having

    • 2.where和having的区别：

      • 执行时间不同：where是分组之前进行过滤，不满足条件，不参与分组，having是分组之后进行过滤，不满足条件的数据，不会被查询出来。
      • 判断条件不同：where不能使用聚合函数进行判断。having可以使用聚合函数进行判断。

  • 排序查询

    • order by 排序字段列表

    • select 字段列表 from 表名 [where 条件] [group by 分组字段列表] [order by 排序字段列表];

    • 1.排序字段列表可以支持单个字段、多个字段、表达式、函数、别名。

      2.order by 排序字段列表支持倒序排序：desc，正序排序：asc（默认）。

      3.order by 排序字段列表支持多个字段的排序：先按照第1个字段排序，如果第1个字段相同，再按照第2个字段排序，依次类推。

  • 分页查询

    • limit 起始索引,查询条数。

    • select 字段列表 from 表名 limit 起始索引,查询条数;

    • 1.起始索引的索引从0开始。

    • 2.起始索引可以省略，默认从0开始。

    • 3.起始索引可以支持负数，表示倒数第几个。

    • 4.起始索引和查询条数可以同时省略，表示查询所有数据。

注意：if和case的使用。

---

二、数据库多表设计与查询

• 多表之间关系

  • 1.一对多(多对一)关系：员工与职位的关系。
  • 2.多对多关系：如学生与课程的关系。
  • 3.一对一关系：单表拆分为多个子表，子表之间就是一对一关系。

---

注意：如今物理外键在多表设计中已经不适用了，会导致死锁，消耗性能等问题，实际开发中使用逻辑外键代替物理外键。但是物理外键必须会。

---

• 多表查询

  • 1.内连接查询：

    • 隐式内连接：select 字段列表 from 表1,表2 where 条件;

    • 显式内连接：select 字段列表 from 表1 [inner] join 表2 on 条件;

  • 2.外连接查询：

    • 左外连接：select 字段列表 from 表1 left [outer] join 表2 on 条件;

    • 右外连接：select 字段列表 from 表1 right [outer] join 表2 on 条件;

  • 3.子查询：

    • 子查询是嵌套在其他查询中的查询，被嵌套的查询称为子查询。
      子查询可以放在select、from、where、having等关键字后面。
      子查询的执行优先于主查询执行。
      子查询的执行结果可以作为主查询的条件，表，值。

    • 分类：

      • 标量子查询：子查询结果是单个值。

      • 列子查询：子查询结果是一列。

      • 行子查询：子查询结果是一行。

      • 表子查询：子查询结果是多行多列。

  • 4.连接查询的执行顺序：

    • from子句>where子句>group by子句>having子句>select子句>order by子句。

---

三、数据库事务

• 定义

  • 1.事务是数据库一组操作的集合，这组操作要么全部成功，要么全部失败。
  • 2.事务具有4大特性：ACID。

• 事务的四大特性

  • 1.原子性：事务是不可分割的最小操作单位，要么同时成功，要么同时失败。
  • 2.一致性：事务完成时，必须使所有的数据都保持一致状态。
  • 3.隔离性：多个事务之间，相互独立。
  • 4.持久性：事务一旦提交或回滚，它对数据库中的数据改变就是永久性的。

---

• 事务操作

• 开启事务：start transaction 或者 begin;

• 提交：commit;

• 回滚：rollback;

---

四、数据库优化之索引

• 定义

  • 索引是帮助数据库高效获取数据的数据结构。

---

• 优缺点

  • 优点：
    1.提高数据查询的效率，降低数据库的IO成本。
    2.通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗。

  • 缺点：
    1.索引占用存储空间。实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
    2.降低更新表的速度。如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次对表进行访问时，索引都要进行扫描，找到符合条件的数据。

---

• 索引的默认数据结构

  • 1.B+树：多路平衡搜索树
    1.B+树是MySQL数据库索引的一种数据结构。

---

• 索引操作

  • 1.创建索引：create index 索引名 on 表名(字段名);

  • 2.删除索引：drop index 索引名 on 表名;

  • 3.查看索引：show index from 表名;

1.主键字段在建表时自动创建主键索引。
2.唯一约束字段在建表时自动创建唯一索引。

---