数据库笔记-4

今日内容介绍

全天内容无需立马掌握

• MySQL 的高级功能应用

---

数据库设计

ER模型

定义: E 代表实体(数据表), R 代表联系(数据表之间对应的字段)


关系常见分类

• 一对一

• 一对多

• 多对多

---

外键

如果一个实体的某个字段指向另一个实体的主键，就称为外键。
被指向的实体，称之为主实体（主表），也叫父实体（父表）。
负责指向的实体，称之为从实体（从表），也叫子实体（子表）
作用: 对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填
写成功，如果不存在则填写失败并报错

-- 主表
drop table if exists class;
create table class(
id int unsigned primary key auto_increment,
name varchar(10)
);

-- 从表
drop table if exists stu;
create table stu(
name varchar(10),
class_id int unsigned,
-- stu 表的 class_id 指向 class 表的 id, class_id 是 stu 表的外键
-- foreign key(自己的字段名) references 目标表名(目标表的主键)
foreign key(class_id) references class(id)
);

扩展1 : 对应存在表添加外键

-- 扩展1 : 对于已经存在的表添加外键
-- alter table 从表名 add foreign key (从表字段) references 主表名(主表主键);
alter table stu add foreign key (class_id) references class(id);

扩展2 : 查看和删除外键

-- 扩展2 : 查看外键和删除外键
-- 查看外键
-- show create table 表名
show create table stu;
-- CREATE TABLE `stu` (
--   `name` varchar(10) DEFAULT NULL,
--   `class_id` int(10) unsigned DEFAULT NULL,
--   KEY `class_id` (`class_id`),
--   CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 删除外键
-- alter table stu drop foreign key 外键名称
alter table stu drop foreign key stu_ibfk_1;

利用 Navicat 查看外键

设计表


查看外键


结论 : 由于设置外键会极大降低对数据修改效率, 因此在实际工作中遇到使用外键的几率比较低

---

索引

定义: 类似于图书中的目录, 能够起到快速检索数据的作用
作用: 对于大量数据进行查询效率优化时, 可以采取添加索引的策略

-- 索引应用检验案例

-- 开启时间监测
set profiling=1;

-- 查询示例数据 num = 10000 的值
select * from test_index where num = 10000;

-- 查看运行时间
show profiles;

-- 添加索引
-- create index 索引名称 on 表名(目标字段)
create index num_index on test_index(num);

-- 再次执行查询数据操作
select * from test_index where num = 10000;

-- 再次查看运行时间
show profiles;


扩展1: 查看索引

-- 扩展1 : 查看索引
-- show index from 表名
show index from test_index;


扩展2 : 创表时添加

-- 扩展2 : 创表时添加
create table create_index(
	id int primary key,
	name varchar(10) unique,  -- unique : 设置端唯一值
	age int,
	key(age) -- 指定添加索引方法
);
-- 查看索引
show index from create_index;

扩展3 : 删除索引

-- 扩展3 : 删除索引
-- drop index 索引名称 on 表名;
drop index age on create_index;


结论 : 索引在使用的时候能提高查询效率, 但是在进行插入/更新/删除时, 索引会造成障碍, 因此需要在执行这些操作前, 先移除索引, 操作结束后, 重新添加索引即可

---

命令行中操作 MySQL

前提: 要进行操作系统环境中, 存在 MySQL 环境

登录 MySQL

mysql -u数据库用户名 -p数据库密码
例如:
mysql -uroot -p123456


数据库操作

查看所有数据库 : show databases;
使用数据库 : use 数据库名称;
查看当前使用的数据库 : select database();
创建数据库 : create database 数据库名称 charset=utf8;
删除数据库 : drop database 数据库名称;

查看所有数据库


使用数据库


查看当前使用的数据库名称


创建数据库


删除数据库


---

数据表操作

查看数据库中的所有数据表 : show tables;
查看表结构 : desc 表名;
查看创表语句 : show create table 表名;
注意 : 进入到数据库之后, 所有的 SQL 查询语句, 均可以正常使用!

查看所有数据表


查看表字段信息


查看创表语句


---

命令行和图形化页面操作步骤对比

连接数据库


查看数据库


选择数据库


新建查询


查看所有表


查看表结构


编写 SQL 查询语句


结论 : 命令行使用是为了在没有图形化工具的情况下, 应急使用操作数据库的手段

---

存储过程

定义 : 可以叫存储程序, 通过一定的代码逻辑, 将一句或多久SQL语句进行封装, 通过调用存储过程, 快速实现其内部封装SQL语句操作
用途 : 可以用来向数据库中, 快速插入大量测试数据时使用

基本语法格式

delimiter // -- 取消默认结尾标识符 ; 的作用
create procedure 存储过程名(参数列表)
begin -- 代码逻辑的开始
	SQL语句
end //
delimiter ; -- 还原默认结尾标识符 ; 的作用

调用方法

-- 在查询窗口中
call 存储过程名();

语法实现

-- 修改句尾标识符为'//'
delimiter //
-- 如果存在 test 存储过程则删除
drop procedure if exists test;
-- 创建无参数的存储过程 test
create procedure test()                 	
begin
		-- 声明变量 i
		declare i int; 
		-- 变量初始化赋值为 0
    set i = 0;
		-- 设置循环条件: 当 i 大于 10 时跳出 while 循环
    while i < 10000 do
				-- 往 datatest 表插入数据
        insert into datatest values (null, i);
				-- 循环一次, i 加一
        set i = i + 1; 
		-- 结束 while 循环
    end while;
		-- 查看 datatest 表数据
    select * from datatest; 
-- 结束存储过程定义语句
end//
-- 恢复句尾标识符为';'
delimiter ;		

---

事务

定义 : 所谓事务可以称之一个操作序列, 一系列操作要么都执行, 要么就不执行. 对于数据库来讲, 对于数据的操作行为, 要么都实现, 要么都不实现, 最终需要确保写入到数据库的数据的一致性(原子性)!

事务实现案例

前提: 数据表的数据引擎类型必须是 InnoDB (可以通过查看创表语句来确认)


-- 注意 : 出发事务操作一般是由修改数据操作产生(插入数据insert/更新数据update/删除数据delete) 

-- 开启事务
begin;
-- 提交事务
commit;
-- 回滚事务
rollback;

案例实现步骤

1. 开启两个终端窗口(A窗口操作/B窗口查询)
2. A 窗口 begin 开启事务, 执行修改数据操作
3. B 窗口查询数据(此时B看不到A的修改结果)
4. A 提交事务 commit
5. B 再次查看(可以见到A的修改结果)
6. A 再次开启事务, 执行修改数据操作
7. A 执行回滚事务 rollback
8. B 查看不到A的修改操作结果

---

视图

场景 : 能够封装 SQL 语句, 以类似于表的形式存在

-- 创建视图语法
-- 注意: 视图命名一般以 v_视图名称 形式实现
-- create view 视图名称 as select 语句;

create view v_goods as select goodsName 商品名称, price 价格, num 数量, company 公司 from goods;

-- 视图的用法: 当成表查询使用即可
select * from v_goods;

-- 复杂 SQL 语句视图封装
select go.goodsName, ca.cateName from goods go inner join category ca on go.typeId = ca.typeId;

-- 封装连接查询语句时, 如果存在重名字段名称, 需要通过别名进行修改
create view v_goods_cate as select go.*, ca.id 序号, ca.typeId 类型, ca.cateName from goods go inner join category ca on go.typeId = ca.typeId;

select * from v_goods_cate;

-- 删除视图语句
-- drop view 视图名称
drop view v_goods_cate;

注意 : 视图可以起到隐藏真实数据表内容的作用

视图名称以 v 开头的原因


---

修改 MySQL 数据库密码

场景 : 遗忘数据库密码时使用

1. 修改数据库配置文件, 使之登录不需要密码

此操作需要具备服务器 root 账户权限

• 查找配置文件并修改

切换 root 用户
su - 
定位配置文件位置
locate my.cnf
使用 vi 工具打开配置文件
vi /etc/my.cnf
在文件内容 [mysqld] 下方添加此内容, 保存后退出
skip-grant-tables

切换 root 用户


查看和获取 MySQL 配置文件


添加内容 跳过密码登录


保存文件修改内容退出


确认配置文件内容已经修改


• 重新启动 MySQL 服务

systemctl restart mysqld
重启完成可以通过查看状态命令进行验证
systemctl status mysqld

重启和查看 mysqld 服务


• 重新登录 MySQL 查看是否需要密码进行登录

确认不需要密码可以登录 MySQL


2. 登录数据库, 修改数据库账户密码

注意 : 本步骤为 SQL 语句, 需要在 mysql > 状态下执行

use mysql; -- 选择 mysql 数据库
-- 更新密码
-- 注意 : authentication_string 字段名需要根据 MySQL 版本就行对应修改
-- update user set authentication_string=password('新密码') where user = '用户名';
update user set authentication_string=password('123') where user = 'root';
-- 刷新权限
flush privileges;

3. 还原配置文件设置, 使之登录需要密码

还原配置文件设置与添加设置步骤基本相同

• 查找配置文件并修改

切换 root 用户
su - 
定位配置文件位置
locate my.cnf
使用 vi 工具打开配置文件
vi /etc/my.cnf
使用 # 注释 [mysqld] 下方内容, 保存后退出
# skip-grant-tables

注释配置文件内容


• 重新启动 MySQL 服务

systemctl restart mysqld
重启完成可以通过查看状态命令进行验证
systemctl status mysqld

• 重新使用修改后的密码登录 MySQL 即可

---

MySQL 日志的获取

注意 : MySQL 自带日志功能, 但是开启日志功能, 极其消耗数据库性能, 因此默认情况下是不开启的

-- 查看日志功能是否开启
show variables like 'general%';

-- 开启操作
set global general_log = 1;

-- 关闭操作
set global general_log = 0;

-- 注意: 日志功能使用完成, 需要记得马上关闭, 避免影响数据库性能!!!!!!

查看日志功能是否开启


开启日志功能


获取日志文件内容(注意: 要从服务器端获取)


关闭日志功能