数据库基础知识---以MySQL为例

一、什么是MySQL

• 数据保存在不同的表中，而不是将所有数据放在一个大仓库内

二、特点

• 开源--免费下载
• 跨平台--可以在多个操作系统进行运行
• 性能好--可以出来大量数据
• 简单--安装配置简单
• 支持多种编程语言--可以与多种编程语言进行无缝集成

三、分类

• DDL--数据定义语言:用来定义数据库对象、数据库、表、列
• DML--数据操作语言:用来对数据库中表的记录进行更新
• DQL--数据查询语言:用来查询数据库中表的记录
• DCL--数据控制记录:用来定义数据库的访问权限、安全级别、创建用户

四、常用数据类型

 大致分为三类：数值、时间/日期、字符/字符串

1.数值

• int
• double

2.时间/日期

• date-- YYYY-MM-DD
• time--HH:MM:SS
• year--YYYY

3.字符/字符串

• varchar--变长字符串
• char--定长字符串 

五、注释

• 单行注释:--空格
• 多行注释:/* */
• #--MySQL特有注解 

六、分类(详细)

1.DDL

• //库
  create database 数据库名：创建数据库
  
  create database 数据库名 character set 字符集：创建一个数据库，并设置其字符集
   
  select database()： 查看正在使用的数据库
  
  use 数据库名：切换到 数据库操作
  
  show databases：查看所有的数据库
  
  show creat database 数据库名：查看指定数据库的定义信息
  
  alter database 数据库名 character set 字符集：修改指定数据库的字符集
  
  drop database 数据库名：永久删除指定数据库
  
  //表
  
  CREATE TABLE 表名(
  
  字段名称1 字段类型（长度）,
  
  字段名称2 字段类型 最后一列不加逗号
  
  )；
  
  CREATE TABLE 新表名 like 旧表名：复制表结构
  
  show tables; 查看根数据库的所有表名
  
  desc 表名 ：查看数据表的结构
  
  drop table 表名； 删除表
  
  drop table if exists 表名； 存在的话就删除,不存在就不执行删除
  
  rename table 旧表名 to 新表名：修改表名
  
  alter table 表明 character set 字符集名：修改表的字符集
  
  alter table 表名 add 字段名称 字段类型：向表中添加列 关键字 ADD(删除列 关键字 DROP)
  
  //列
  alter table 表名 modify 字段名称 字段类型：修改表中列的 数据类型或长度 ，关键字 MODIFY
  
  alter table 表明 change 旧列名 新列名 类型(长度)：修改列名称，关键字 CHANGE
  
  alter table 表明 drop 列名：删除列，关键字 DROP

2.DML

• insert into 表名 values （字段1，字段2...）:插入全部字段
  注意事项
  1.值与字段必须对应 ——个数相同，数据类型相同
  2.值的数据大小，必须在字段的长度范围内
  3.varchar char date 类型，必须使用单引号或双引号
  4.要插入空值，可以忽略该字段，或插入 null
  
  update 表名 set 列名 = 值 where 条件表达式：带条件修改
  
  delete from 表名 where 条件表达式：指定条件删除

3.DQL

• select * from 表名：查询所有数据
  
  select 字段1，字段2 from 表名：查询指定字段数据
  
  别名查询 关键字 AS
  
  select distinct * from 表名：去重关键字 distinct
  
  排序——Order By
  select 字段名 from 表名 [where 字段 = 值 ] order by 字段名[ ASC / DESC]
  ASC 表示升序，默认 ——ascending
  DESC 表示降序——Descending
  
  单例排序——只按照某一个字段排序
  select 字段名 from 表名 [where 字段 = 值 ] order by 字段名[ ASC / DESC]
  
  组合排序——同时对多个字段排序
  select 字段名 from 表名 [where 字段 = 值 ] order by 字段名1[ ASC / DESC] ， 字段名2[ ASC / DESC] ；
  
  聚合函数
  SELECT 聚合函数(字段名) FROM 表名;
  聚合函数类型及作用
  count(字段)	统计指定列不为NULL的记录行数
  sum(字段)	计算指定列的数值和
  min(字段)	计算指定列的最大值
  max(字段)	计算指定列的最小值
  avg(字段)	计算指定列的平均值
  
  分组——GROUP BY
  select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件]
  
  limit关键字
  select 字段1，字段2 ... from 表名 limit 起始行数 ， 返回行数；
  limit offset , length; 关键字可以接受一个 或者两个 为0 或者正整数的参数
  offset 起始行数, 从0开始记数, 如果省略 则默认为 0.
  length 返回的行数
  分页公式 起始索引 = (当前页 - 1) * 每页条数

七、运算符

1.比较运算符

 2.逻辑运算符

3.通配符--模糊查询

 八、约束

1.作用

• 对表中的数据进行进一步限制，从而保证数据的正确性，有效性，完整性。违反约束的条件,将无法插入到表中

2.常见约束

• 主键 —— primary key

• 语法格式：字段名 字段类型 primary key
  
  创建主键：1.CREATE TABLE 表名(
  
               字段名1 数据类型 ，
  
               字段名2 数据类型 PRIMARY KEY ，
  
               字段名3 数据类型
  
            );
  
            2.CREATE TABLE 表名(
  
               字段名1 数据类型 ，
  
               字段名2 数据类型 ，
   
               字段名2 数据类型 ，
  
               PRIMARY KEY(字段名2)
  
           );
           3.alter table 表名 add primary key(字段名)
  
  删除主键：alter table 表名 drop primary key
  
  主键自增：字段名 数据类型 primary key auto_increment (字段类型必须是 整数类型)
  

• 唯一 —— unique

• 特点：表中的某一列值不能有重复值
  
  字段名 字段类型 unique

• 非空 —— not null

• 特点： 某一列数据不可为空
  
  字段名 字段类型 not null

• 外键 —— foreign key

• 外键指的是在从表中与主表的主键对应的那个字段
  
  注意事项
  1.外键指向的表的字段，要求是primary key 或者是 unique
  2.表的类型的innodb ，这样的表才支持外键
  3.外键字段的类型 要和 主键字段的类型一致（长度可以不同）
  4.外键字段的值，必须在主键字段中出现过，或者为null
  5.一旦建立主外键关系，数据不能随意删除
  6.添加数据时，先添加主表中的数据
  7.删除数据时，先删除从表中的数据
  
  创建外键
  [constraint] [外键约束名称] foreign key(外键字段名) references 主表名(主键字段名)
  
  已有表添加外键
  ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES主表(主 键字段名);
  
  删除外键
  alter table 从表 drop foreign key 外键约束名称

• 默认值——default

• 用来指定某列的默认值
  
  字段名 字段类型 default 默认值

九、事务 

1.什么是

• 事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败

2.回滚

•  即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成 的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

3.基本操作

• start transaction / begin ——开始一个事务
  
  rollback ——回滚全部事务
  
  commit——提交事务，所有的操作生效，无法在回滚

4.四大特性

• 原子性： 事务是一个不可拆分的整体，要么全部成功要么全部失败
• 一致性： 数据的状态，在执行前后保持一致
• 隔离性：事务与事务之间互不影响，执行保持隔离状态
• 持久性：执行成功，对数据库的修改是永久性的

5.产生问题及隔离级别

(1) 产生问题

• 脏读： 一个事务读取到另一个事务尚未提交的数据
• 幻读： 一个事务内，多次查询同一数据时，数据量不一致
• 不可重复读：同一个事务多次读取同一条数据时，数据不一致

(2)隔离级别

6. 表

• 一对一
• 一对多
• 例如：班级和学生，客户与订单
• 建表原则：在从表（多方）创建一个字段，字段作为外键指向主表的（一方）的主键
  • 主：少
  • 从：多
  • 
• 多对多
• 例如：老师和学生，客户与销售
• 建表原则：创建一个中间表，中间表至少两个字段，分别作为外键指向各自一方的主键
• 注意：多对多关系中，多个表之间，中间表就成了从表
• 

7. 多表查询分类

(1)内连接：交集

• 隐式链接
• SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
• 显式链接
• select 字段名 from 左表[ INNER ] JOIN 右表 ON 连接条件 where 条件

(2)外连接

• 左外连接
• 以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据，如果匹配不到, 左表中的数据正常展示, 右边的展示为null.
• SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
• 右外连接
• 以右表为基准, 匹配左边表中的数据,如果匹配的上,就展示匹配到的数据，如果匹配不到, 右表中的数据正常展示, 左边的展示为null.
• SELECT 字段名 FROM 右表 RIGHT [OUTER] JOIN 左表 ON 条件

(3)子查询

• 什么是
• 一条select 查询语句的结果, 作为另一条 select 语句的一部分
• 特点
• 子查询必须放在小括号中
• 子查询一般作为父查询的查询条件使用

十、数据库设计

 1.三范式

• 第一范式

• 原子性，列不能在分
  
  最基本范式
  
  最小数据单元

• 第二范式

• 在第一范式基础上进行跟进，目的是为了让表中的每一列与主键相关

• 第三范式

• 消除传递依赖

十一、MySQL索引

1.分类

• 主键索引
• 唯一索引
• 普通索引 

2.优缺点

• 优点 
• 提高查询速度
• 减少查询中分组与排序的时间
• 缺点
• 咋用一定得储存空间
• 减少创建和维护索引的时间
• 不恰当查询语句会导致索引失效

十二、MySQL视图

1.什么是

• 一种虚拟表 

2.作用

• 控制权限 
• 比如说，有几个列可以运行用户查询，其他列不允许运行;我们可以开通视图，只查询特定的列，这样起到权限控制作用
• 简化多表查询
• 比如说，我们要进行一次复杂的查询，我们可以构建一张视图，用户只要查询视图就可以获取相关信息

3.视图和表的区别

• 删除视图，表不受影响，删除表，视图也将不存在

十三、MySQL优化

• select语句中避免使用*

• -- 不推荐 
  SELECT * FROM employees; 
  
  -- 推荐
  SELECT id, name, position FROM employees; 

• 只需要一行数据的时候使用limit 1

• -- 不推荐（可能返回多行） 
  SELECT name FROM employees WHERE position = 'Manager';
  
  -- 推荐（确保只返回一行）
  SELECT name FROM employees WHERE position = 'Manager' LIMIT 1;

• 将where中用的比较频繁的字段建立索引

• -- 假设`position`字段在`WHERE`子句中经常使用
  CREATE INDEX idx_position ON employees(position);

• 减少使用join查询次数

• -- 不推荐（多个JOIN） 
  SELECT e.name,  d.department_name   
  FROM employees e 
  JOIN departments d ON e.department_id  = d.id  
  JOIN projects p ON e.project_id  = p.id; 
  
  -- 推荐（减少JOIN，分步查询）
  SELECT name FROM employees WHERE department_id = 1;
  SELECT department_name FROM departments WHERE id = 1;

• 减少子查询的次数

• -- 不推荐（使用子查询）
  SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE department_name = 'Sales');
  
  -- 推荐（用JOIN替代子查询） 
  SELECT e.name   
  FROM employees e 
  JOIN departments d ON e.department_id  = d.id  
  WHERE d.department_name  = 'Sales';

• 对大数据量的查询，使用分页查询、分区表、分表

• -- 不推荐（一次性查询所有数据）
  SELECT * FROM employees; 
  
  -- 推荐（分页查询）
  SELECT * FROM employees LIMIT 10 OFFSET 0; -- 查询第一页，每页10条

• 使用动态SQL，避免生成多余复杂的语句，防止SQL注入

• -- 不推荐（拼接SQL，易注入）
  SELECT * FROM employees WHERE name = 'O'Reilly'; -- 危险！ 
  
  -- 推荐（使用参数化查询）
  PREPARE stmt FROM 'SELECT * FROM employees WHERE name = ?';
  SET @name = 'O\'Reilly'; 
  EXECUTE stmt USING @name; 

• 优化数据类型：根据实际情况选择合适的数据类型，例如，对于整数类型，可以根据范围选择适当的类型，避免使用不必要的大整数类型。

• -- 不推荐（使用过大类型）
  CREATE TABLE employees (id BIGINT, age INT);
  
  -- 推荐（选择合适类型）
  CREATE TABLE employees (id INT, age TINYINT); 

• 垂直分区：如果一个表过于庞大，可以将其拆分成多个表，每个表只包含相关的列，这样可以减少表的大小和查询的复杂度。

• -- 假设按年份分区
  CREATE TABLE employees_2020 (id INT, name VARCHAR(50), ...); 
  CREATE TABLE employees_2021 (id INT, name VARCHAR(50), ...); 
  
  -- 查询时根据年份选择相应的表
  SELECT * FROM employees_2021 WHERE name = 'John'; 

• 水平分区：将数据分布到多个表中，根据某个字段的值进行分区，例如按照时间范围或地区进行分区。这样可以提高查询的局部性和并行处理能力。

• -- 假设按年份分区
  CREATE TABLE employees_2020 (id INT, name VARCHAR(50), ...); 
  CREATE TABLE employees_2021 (id INT, name VARCHAR(50), ...); 
  
  -- 查询时根据年份选择相应的表
  SELECT * FROM employees_2021 WHERE name = 'John'; 

• 定期优化表：定期执行 OPTIMIZE TABLE 命令来优化表的结构，整理数据和索引，提高查询性能。

• -- 定期执行 
  OPTIMIZE TABLE employees; 

• 缓存查询结果：对于频繁查询的结果，可以使用缓存来提高性能，例如在应用程序中使用缓存框架或在 MySQL 中使用查询缓存。

• -- 在应用程序层面使用缓存（伪代码）
  if (cache.get('employee_list')  == null) {
      result = db.query('SELECT  * FROM employees'); 
      cache.set('employee_list',  result, 60); // 缓存60秒
  } else { 
      result = cache.get('employee_list'); 
  }