Java学习Day28：Mysql 第二章：斗气大陆

一共有十一个步骤，最先执行的是FROM操作，最后执行的是LIMIT操作。每个操作都会产生一个虚拟表，该虚拟表作为一个处理的输入，具体执行顺序如下：
(1) FROM:对FROM子句中的左表<left_table>和右表<right_table>执行笛卡儿积，产生虚拟表VT1;
(2) ON: 对虚拟表VT1进行ON筛选，只有那些符合<join_condition>的行才被插入虚拟表VT2;
(3) JOIN: 如果指定了OUTER JOIN(如LEFT OUTER JOIN、RIGHT OUTER JOIN)，那么保留表中未匹配的行作为外部行添加到虚拟表VT2，产生虚拟表VT3。如果FROM子句包含两个以上的表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1~步骤3，直到处理完所有的表;
(4) WHERE: 对虚拟表VT3应用WHERE过滤条件，只有符合<where_condition>的记录才会被插入虚拟表VT4;
(5) GROUP By: 根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5;
(6) CUBE|ROllUP: 对VT5进行CUBE或ROLLUP操作，产生表VT6;
(7) HAVING: 对虚拟表VT6应用HAVING过滤器，只有符合<having_condition>的记录才会被插入到VT7;
(8) SELECT: 执行SELECT操作，选择指定的列，插入到虚拟表VT8中;
(9) DISTINCT: 去除重复数据，产生虚拟表VT9;
(10) ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10;
(11) LIMIT: 取出指定行的记录，产生虚拟表VT11，并返回给查询用户。

9. 数据库设计

9.1 数据库三范式(空间最省)

• • 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的 称为第二范式（2NF） ， 其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就行了

9.1.1第一范式 1NF

1. 概念
   1. 原子性，做到列不可拆分
   2. 第一范式是最基本的范式。数据库表里面字段都是单一属性，不可再分，数据表中每个字段都是不可再分的最小数据单元
2. 例如 中国北京 可以 拆分为 中国 和 北京

9.1.2第二范式 2NF

1. 概念
   1. 在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
   2. 拆分可以，但是都要还和主键相关
2. 例如
   1. 学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
   2. 如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了

9.1.3第三范式 3NF

1. 概念
   1. 消除传递依赖，可通过表中已有数据得到的数据可以优化
   2. 表的信息，如果能被推导出来，就不应该单独的设计一个字段来存放
2. 例如
   1. 利用 价格 和 数量 就能算出总金额，不需要在表中做记录

9.2数据库反三范式

9.2.1概念

1. 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能（按实际情况来反三范式）
2. 浪费存储空间,节省查询时间 (以空间换时间）

9.2.2什么是冗余字段

设计数据库时，某一个字段属于一张表，但它同时出现另一个或多个表中，且完全等同于它在其本来所属表的意义表示

9.2.3反三范式例子

两张表，用户表、订单表，用户表中有字段name，而订单表中也存在字段name。

9.3 总结

1. 尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致
2. 合理加入冗余字段，减少join，让数据库执行性能更好

10.MySql索引

对字段建立索引可以大大提高查询速度

10.1常见的索引分类

1. 主键索引(primary key)

   1. 主键是一种唯一性索引，每个表只能有一个主键，用于标识数据表中的每一条记录
2. 唯一索引(unique)
   1. 唯一索引是指 索引列的所有值只能出现一次
3. 普通索引(index)
   1. 常见的索引，单纯加快数据的访问速度
4. MySql将一个表的索引都保存在同一个索引文件中, 如果对中数据进行增删改操作,MySql都会自动的更新索引.
5. ：

10.1.1 主键索引 （primary key）

一个表可以没有主键，但最多只能有一个主键，且主键值不能包含null

语法格式：即主键的格式

10.1.2唯一索引（unique）

唯一索引可以保证数据记录的唯一性。

语法格式：即唯一的格式

多了一个 create语句创建

create unique index 索引名 on 表名(列名(长度))

10.1.3 普通索引（index）

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBY column）中的数据列创建索引。

1. 语法格式
   1. 使用create index 语句创建: 在已有的表上创建索引

create index 索引名 on 表名(列名[长度])

• 1. 修改表结构添加索引

ALTER TABLE 表名 ADD INDEX 索引名 (列名)

10.2索引性能测试

10.3索引的优缺点和总结

1. 添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
2. 索引的优点
   1. 大大的提高查询速度
   2. 可以显著的减少查询中分组和排序的时间。
3. 索引的缺点
   1. 创建索引和维护索引需要时间，而且数据量越大时间越长
   2. 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

11 MySql视图

11.1视图的概念

1. 视图是一种虚拟表
2. 视图建立在已有表的基础上，视图赖以建立这些表称为基表
3. 向视图提供数据内容的语句为select语句，可以将视图理解为存储起来的select语句
4. 视图向用户提供基表数据的另一种形式

• 视图其实是一个虚拟表，方便我们查询操作！

11.2视图的作用

1. 权限控制时可以使用
   1. 比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用
2. 简化复杂的多表查询
   1. 视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)
   2. 视图主要就是为了简化多表的查询

11.3视图的使用

1. 语法格式

create view 视图名 [column_list] as select语句;

• 1. view：表示视图
  2. column_list：可选参数，指定视图中各个属性的名称，默认情况下，与select语句中查询的属性相同
  3. as：表示视图要执行的操作
  4. select语句：向视图提供数据内容

1. 视图的创建

#1. 先编写查询语句 #查询所有商品 和 商品的对应分类信息 SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`; #2.基于上面的查询语句,创建一张视图 CREATE VIEW products_category_view AS SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;

1. 视图的查询

   1. select * from 视图名

SELECT * FROM products_category_view;

视图的使用案例

• 查询鞋服分类下最贵的商品全部信息
• 常规

SELECT MAX(price) AS pricemax FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid` WHERE c.`cname` = '鞋服'

• 视图

SELECT * FROM p_c_view pcv WHERE pcv.cname = '鞋服' AND pcv.price = (SELECT MAX(price) FROM p_c_view WHERE cname = '鞋服')

11.4视图与表的区别

1. 视图建立在表的基础上，表示存储数据库中的数据，而视图至是作为一个数据的展示
2. 通过视图不能改变数据（一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许更新）
3. 删除视图，表不受影响，删除表，视图也将不存在

12 MySql存储过程（了解）：

为什么不推荐使用存储过程？_为什么不建议使用存储过程-CSDN博客

• 存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制。

数据准备

创建一个商品表，订单表

需求：编写一个存储过程，查询所有的商品数据

12.1方式1

1. 创建——语法格式

1. 调用——语法格式

1. 例子

12.2方式2

1. IN 输入参数：表示调用者存储过程传入值

• 根据id查询

12.3方式3

1. 变量赋值

SET @变量名=值

1. OUT输出参数：表示存储过程向调用者传出值

OUT 变量名 数据类型

13. 触发器（了解）

当执行一条sql语句时，这条sql语句的执行会自动触发执行其他语句 即“连锁反应”

13.1四要素

1. 监视地点（table）———那一张表
2. 监视事件（insert/update/delete） ————触发条件(增删改)
3. 触发时间（before/after）————触发条件前后
4. 触发事件（insert/update/delete）——增删改

13.2创建触发器

1. delimiter $ ——定义结束符
2. create trigger Trigger_Name ——触发器名，一个数据库名字不能相同
3. before/after （触发条件） insert on orders——指定触发的时机,和要监听的表
4. for each row ——行触发器，固定写法
5. begin——开始
   1. 执行的操作
6. end$ ——结束标记
   1. 注意 end和结束标记没有空格
   2. 后面也不允许有分号

14 DCL(数据控制语言)

14.1创建用户

create uesr '用户名'@'主机名' identified by '密码'

1. 用户名
   1. 创建的新用户,登录名称
2. 主机名
   1. 指定该用户在哪个主机上可以登陆，本地用户可用 localhost如果想让该用户可以 从任意远程主机登陆，可以使用通配符 %
3. 密码
4. 例如

-- 创建 admin1 用户，只能在 localhost -- 这个服务器登录 mysql 服务器，密码为 12345 CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';

14.2用户授权

语法格式

grant 权限1，权限2... on 数据库名.表名 to '用户名'@'主机名'

1. 权限
   1. 授予用户的权限，如： create、alter、select、insert、update等
   2. 授权所有则使用 ALL
2. ON 用来指定权限针对那些库和表，*.* 表示所有数据库所有表
3. TO 赋给那个用户

14.3查看权限

语法格式

SHOW GRANTS FOR '用户名'@'主机名';

例如查看root用户的权限

SHOW GRANTS FOR 'root'@'localhost';

GRANT ALL PRIVILEGES 是表示所有权限

14.4删除用户

语法格式

DROP USER '用户名'@'主机名';

14.5查询用户

SELECT * FROM USER;

15命令行备份

语法格式

mysqldump -u 用户名 -p 密码 数据库 > 文件路