MySQL数据库--加强篇

数据约束

• 什么是数据的约束

对用户操作表的数据进行约束（约束用户对数据表的操作）

• 默认值（default）

作用：当用户对使用默认值的字段 不插入值的时候，就使用默认值。
注意：插入null时，依旧插入null，

• 非空(not null)

作用：限制字段必须插入数据
注意：1.非空字段，不能不赋值
2.不能赋值null

• 唯一(unique)

作用：对字段的值不能重复
注意：1. 唯一的字段可以插入null值
2.唯一的字段可以插入多个null值

• 主键(非空+唯一 primary key)

作用：非空与唯一相结合
注意：1. 通常情况下，每张表都会设置一个主键的字段，用于标记表中的每条记录的唯一性
2.设计主键的时候，不要选取表中，包含业务含义的字段，给每张表设置一个没有业务含义的字段，通常为id字段

• 自增长(auto_increment)

作用：自动的递增（zerofill）int(4) zerofill----0001
注意：从0开始，所以插入的第一个数为1

• 外键(constraint 名称(当前表名_参照表名_fk) foreign key(参照表+ID)) references 参考表（参考字段）

  
  image.png

image.png

作用：作用于两张表，解决数据冗余高的问题：独立出一张表
注意：1.在插入数据的时候两张表没有必要的联系，所以使用外键使独立表约束原
2.参考独立表的id
3.被约束的表称为副表，约束别人的表成为主表，外键设置在主表上，通常作用与id

• 级联操作（前提有外键）

  
  image.png

on update cascade 修改
on delete cascade 删除

数据库的设计

• 需求分析

需求分析师--原始需求--抽取业务模型

• 需求设计

概要设计：抽取实体<业务模型-->实体模型>，数据库设计<业务模型/实体模型-->数据模型>
详细设计：属性，方法之类的详细设计

• 三大范式
  • 设计原则：

    建议设计的表尽量遵守三大范式
    * 第一范式：要求表的每个字段必须是不可分割的独立单元
    * 第二范式：在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖
    * 第三范式：在第二范式的基础上，要求在每张表的除主键以外的其他字段，都和主键有直接的依赖关系（降低数据冗余）---加入外键

关联查询（多表查询）

多表查询规则：
三步：
1.确定查询哪些表
2.确定查询哪些字段
3.表与表之间的连接关系（规律：连接条件数量=表数量-1）

• 交叉连接查询

不推荐： 差生笛卡尔乘积现象，有些是重复记录

• 内连接查询

  
  image.png

• 左【外】连接

概念：
使用左边的表的数据去匹配右边表的数据，如果符合连接条件的结果，则显示，如果不符合连接条件的结果，则显示null
注意：左外连接；左边的数据一定会全部显示

image.png

• 右【外】连接

概念：
使用右边的表的数据去匹配左边表的数据，如果符合连接条件的结果，则显示，如果不符合连接条件的结果，则显示null
注意：右外连接；右边的数据一定会全部显示

image.png

总结：

左外与右外连接其实是一样的只是个人喜好；

image.png

• 自连接查询（用在分类别）

虚拟一张表

存储过程

• 什么是存储过程

存储过程，带有逻辑的sql语句。（即sql的编程）

• 存储过程的特点

执行效率非常快（存储过程是在数据库的服务器端执行的）
移植性很差（不同数据库的存储过程是不能移植的）

• 存储过程的语法

声明存储过程的结束符
存储过程的名称（参数列表）
开始
sql语句+流程控制
结束 结束符
call 存储过程名称（参数列表）

image.png

• 带有输入参数的存储过程

image.png

• 删除存储过程
  DROP PROCEDURE 存储名称;

MySQL的变量

• 全局变量（内置变量）

MySQL数据库内置的变量（所有连接都起作用）

• 查看所有全局变量 show variables
• 查看某个全局变量 select @@变量名
• 修改某个全局变量 set 变量名=新值
• character_set_client : MySQL 服务器的接受数据的编码
• character_set_results ： MySQL服务器输出数据的编码

• 会话变量

只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量会全部丢失！

• 定义会话变量：set @变量=值
• 查看会话变量： set @变量

• 局部变量

在存储过程中使用的变量叫局部变量，只要存储过程执行完毕，局部变量就丢失

触发器

• 作用

当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成
添加：
create trigger 触发器名称 after insert on 数据表名 for each row;
insert into 修改器字段名 values("需要添加的信息（例如：员工添加了一条信息）")
修改：
create trigger 触发器名称 after update on 数据表名 for each row;
insert into 修改器字段名 values("需要添加的信息（例如：员工修改了一条信息）")
删除：
create trigger 触发器名称 after delete on 数据表名 for each row;
insert into 修改器字段名 values("需要添加的信息（例如：员工删除了一条信息）")

MySQL的权限

• 权限账户

只拥有部分权限
例如：只能操作某个数据库的某张数据表的数据

• 如何修改MySQL的密码

password md5加密函数（单向加密）
select password("root");

• 修改密码

update user set password=password('123456') where user='root';
然后重启MySQL

• 分配权限账户

crant 权限 on 数据库.表 to '帐户名'@'账户类型' identified by '密码';
crant inseret on 数据库.表 to '自定义'@'%' identified by '密码';

• 权限：select insert delete update drop create all
• 账户类型：
  %：远程或本地均可
  localhost：本地
  192.168.1.1：固定的IP地址

MySQL的备份和还原

• 备份：

mysqldump -u root -p 数据库名 > C:/**.sql

• 还原

mysql -u root -p 数据库名<地址