Linxu系统之Mysql数据库练习【完整版】

本文主要是介绍Linxu系统之Mysql数据库练习【完整版】，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

✨博客主页： https://blog.csdn.net/m0_63815035?type=blog

💗《博客内容》：.NET、Java.测试开发、Python、Android、Go、Node、Android前端小程序等相关领域知识
📢博客专栏： https://blog.csdn.net/m0_63815035/category_11954877.html
📢欢迎点赞 👍 收藏 ⭐留言 📝
📢本文为学习笔记资料，如有侵权，请联系我删除，疏漏之处还请指正🙉
📢大厦之成，非一木之材也；大海之阔，非一流之归也✨

Linux 和数据库第一节随堂笔记【day03】

前言：

本文是Mysql练习学习笔记，以案例为主，当然为了复习为编著的，大家可以自行观看

今天主要内容

• 数据库相关介绍
• 数据库(软件)常见类型
• Navicat 工具基本使用
• 常见的数据类型和约束(重点)
• SQL 语句的编写(表和数据)

---

一. 数据库是什么?

• 为什么学习数据库

  • 软件中产生的所有数据, 最终都要存储于数据库当中

  • 测试人员如果想要进行数据查询/数据校验, 就必须掌握对数据库的基本操作

    

• 数据库是什么?

  • 定义: 用于存储数据的软件
  • 特点: 表是数据存储的核心体现

---

二. 数据库常见分类

• 关系型数据库(重点)

  • RDMS：(Relational Database Management System)关系型数据库系统

  • 特征: 以数据表的形式存储数据, 便于数据查询

    • 常见的数据库代表

      Oracle：在大型项目中使用，例如：银行、电信等项目
      MySQL：Web 项目中使用最广泛的关系型数据库
      Microsoft SQL Server：在微软的项目中使用
      SQLite：轻量级数据库，主要应用在移动平台
      

    • 关系型数据库的核心要素

      数据行（一条记录）
      数据列（字段）
      数据表（数据行的集合）
      数据库（数据表的集合，一个数据库中能够有 n 多个数据表）
      

• 非关系型数据库

  • 不以数据表的形式存储数据的数据库类型

---

三. SQL 介绍

SQL：Structured Query Language（结构化查询语言），通过SQL语言可以对数据库进行操作

特点: 所有主流的关系型数据库, 都支持使用SQL语句进行数据查询 !

注意: 虽然 SQL 语言分支很多, 但对于测试人员而言, 我们重点掌握查询操作即可!

DQL：数据查询语言，用于对数据进行查询，例如：select

扩展: 在 MySQL 中，默认对 SQL 语法不区分大小写

---

四. MySQL 介绍

来源: 目前属于 Oracle 旗下产品, 目前只有社区版免费

特点: 开源, 支持多平台(Linux/Windows/macOS), 支持多语言(Java/C/Python…)

注意: 熟悉 SQL 和熟悉 MySQL 不是一回事儿(熟悉 SQL : 熟悉 SQL 语言; 熟悉 MySQL: 用过 MySQL 数据库)

---

五. 数据库的远程连接(重点)

注意: 实际工作中, 数据库是安装在服务器当中的, 如果要直连数据库进行数据校验, 就必须远程连接

连接步骤

1. 获取两个信息: 服务器的 IP 地址; 数据库的账号和密码(找相关人员询问)
2. 在自己电脑上使用数据库连接工具(例如: Navicat), 建立连接, 远程连接数据库
3. 连接过程中需要确认自己电脑和服务器是否能够正常通信(ping 命令)

注意事项:

1. 课上使用的 MySQL 的账号密码虽然是 root 和 123456, 但是和 CentOS 系统的 root 用户没有关系

课程注意:

1. 如果第一次连接成功, 再次使用时, 提示 IP 地址问题, 则需要重新获取虚拟机系统的 IP 地址

2. 使用过程中, 要确保虚拟机系统不会待机或进入休眠状态, 修改方法如下:

   点击电源 -> 点击设置

   

   修改 power -> 空白屏幕(从不)

   

---

六. Navicat 基本使用

数据库连接操作

常规操作

注意: 连接只需要建立一次, 后续使用中, 可以通过'编辑连接', 调整连接选项设置即可!

---

数据库操作

注意: 默认的数据库不要随意操作

连接上鼠标右键 -> 新建数据库

如图设置字符集和排序规则

数据库操作注意事项

编辑数据库

数据库修改注意事项

---

数据表操作

表 -> 鼠标右键 -> 新建表

添加一个字段 -> 填写表名 -> 保存

再次添加或修改字段

添加或修改字段注意事项

---

数据操作

打开表

数据修改注意事项

数据常规操作

删除数据注意事项

清空表 : 清理数据保留字段

数据修改后, 常用刷新

---

七. 数据库的备份与还原(Navicat)

测试过程中会产生垃圾数据, 测试结束时一般都需要还原原有数据, 因此需要在测试执行前先备份, 测试结束后执行还原

注意: 如果是测试工作中的备份与还原, 以下步骤对同一个数据库进行操作即可!

备份操作

要备份的数据库上鼠标右键 -> 转储SQL文件

选择保存位置

提示成功关闭即可, 备份完成

生成的文件注意事项

---

还原操作

1. 新建一个与备份数据库设置相同的数据库

   

2. 运行上一步保存的SQL文件即可

   

   找到之前报错的SQL文件

   

   确认路径后, 点击开始

   

   如图提示成功, 关闭即可

   

   进入新建数据库中, 刷新数据, 确认还原成功即可

   

   ---

八. 数据类型与约束(重点)

作用: 为了更加准确存储数据, 保证数据的有效性, 需要结合数据类型和约束来限制数据的存储

数据类型

整数：int，有符号范围（-2147483648 ~2147483647），无符号范围（0 ~ 4294967295）
小数：decimal，例如：decimal(5,2) 表示共存5位数，小数占2位，整数占3位
字符串：varchar，范围（0~65533），例如：varchar(3) 表示最多存3个字符，一个中文或一个字母都占一个字符
日期时间：datetime，范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59），例如：'2020-01-01 12:29:59'

整数类型设置

约束

主键（primary key）：物理上存储的顺序
非空（not null）：此字段不允许填写空值
惟一（unique）：此字段的值不允许重复
默认值（default）：当不填写此值时会使用默认值，如果填写时以填写为准
外键（foreign key）：维护两个表之间的关联关系(现阶段先不讲解, 后边再进行扩展)

主键设置

主键的特点

不为空设置

唯一设置 步骤1: 索引

唯一设置 步骤2: 字段 -> 选择目标字段

唯一设置 步骤3: 索引类型 -> UNIQUE

唯一设置 步骤4: 验证

默认值设置注意事项

---

九. SQL 语言

注意: SQL 语言重点是查询语句, 因此对表和数据的操作语句仅做了解, 主要是为了熟悉SQL语句的编写习惯

查询窗口的使用

新建查询窗口

查询窗口显示

SQL 语句运行方法1: 直接运行

SQL 语句运行方法2: 选中再运行

SQL 语句基本含义

保存查询语句

显示查询语句文件保存位置

注释的使用: 单行注释和多行注释

---

数据表

-- 创表语句
-- 需求0: 创建商品表, 包含商品名称和价格
-- create table 表名(
-- 字段1 类型,
-- 字段2 类型
-- );-- 优化
drop table if exists goods; 
-- drop table : 删表 if exists : 如果存在
-- 如果表存在, 执行删除操作
create table goods(
goodsName varchar(20),
price decimal(4, 2)
);

表已存在提示

需求1: 创建商品表

-- 需求1: 创建商品表, 字段包括 id(主键), 商品名称, 价格, 数量, 公司,备注, 并添加商品
drop table if exists goods;
create table goods(
-- unsigned : 无符号
-- primary key : 主键(不为空)
-- auto_increment : 自动递增
-- 直接替换字段名使用皆可
id int unsigned primary key auto_increment, 
goodsName varchar(20),
price decimal(6,2),
num int,
company varchar(20),
remark varchar(30)
);

数据

增加数据(插入数据)

需求1 添加商品

-- 需求1 添加商品
-- 查询一次(方便观察表中字段)
select * from goods;-- 添加1条数据
-- 主键列是自动增长，插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准
-- 语法 insert into 表名 values(...)
insert into goods values
(0, '战神笔记本', 6000.00, 100, '某东', '战神在手, 天下我有!');
-- 注意: 插入的数据个数与字段数必须匹配, 数据类型也需要对应-- 插入多条数据
insert into goods values
(0, '小新笔记本', 5000.00, 100, '某东', '小新小新, 蜡笔小新!'),
(0, '外星人笔记本', 9999.00, 100, '某宝', '外星人上位, 战神渣渣!');
-- 注意: 插入多条数据, 每条数据使用逗点分隔即可-- 扩展: 插入指定字段数据(了解)
-- insert into 表名(字段名) values(字段对应值)
insert into goods(goodsName) values('惠普游侠5');

---

修改数据

需求2: 修改商品数据1条

-- 需求2: 修改商品数据1条, 删除1条数据
-- 修改
-- 先查询所有数据, 便于观察数据内容
select * from goods;-- 补充惠普电脑的数据
update goods set price=4500.00,num=50,company='并夕夕' where id=4;-- 注意: 修改数据时, 为保证修改准确性, 务必要给出限定条件(where)
update goods set price=4500.00,num=50,company='并夕夕';

修改数据不指定条件时的效果

Linux 和数据库第二节随堂笔记【day04】

今日内容介绍

• SQL 语句-查询操作

---

安排如下: 以2人为单位, 结对完成SQL语句查询训练, 目的: 除了自己能的写的出来, 也要能看懂别的人的

---

SQL 语句

一. 删除数据

需求2: 删除一条数据

-- 删除一条数据
-- 先查询所有数据
select * from goods;-- 语法格式 : delete from 表名 where 条件
-- 注意: 如果要删除执行数据, 务必给出限定条件, 否则会删除所有数据!
delete from goods where id = 4;

扩展1

-- 扩展1: 逻辑删除(了解)
-- 对于一些重要数据(例如: 商品数据只是库存为零, 不能删除商品数据),不能直接删除, 此时使用逻辑删除
-- 逻辑删除:通过特定字段值来标记该数据为删除状态而已
-- 实现步骤: 1) 增加特定字段(数据值1为未删除0为删除) 2) 对于要进行逻辑删除的数据,该字段赋值为0即可
-- 增加字段: alter table 表名 add 字段名 类型;
-- 增加标记字段
alter table goods add isdelete int;
-- 更新表内所有数据该字段的值
update goods set isdelete = 1;
-- 标记目标数据为是删除状态(修改该字段值为0)
update goods set isdelete = 0 where id = 2; 
-- 查询未删除的数据
select * from goods where isdelete = 1;

扩展2

-- 扩展2: 3种删除数据方法(所有数据)
-- delete from 表名 : 清空表数据(只清空数据,保留结构), 但不会重置主键计数
-- truncate table 表名 : 清空表数据(只清空数据,保留结构), 并会重置主键计数(截断表)
-- drop table 表名 : 删除表, 包括表结构和数据delete from goods;
truncate table goods;
drop table goods;-- 删除速度 : drop > truncate > delete

---

二. 查询数据

1) 基本查询

需求3 : 查询所有/查询指定字段/起别名/去重

-- 需求3: 准备商品数据, 查询所有数据, 查询部分字段, 起字段别名, 去重
-- 准备数据
-- 查询所有数据 : select * from 表名;
select * from goods;
-- 查询部分字段
-- 只需要在查询所有数据的基础上, 修改*的位置, 多个字段逗点隔开
select goodsName,price from goods;
-- 起别名 : 使用 as 关键字, 后跟别名即可
select goodsName as '商品名称',price as '价格' from goods;
select goodsName '商品名称',price '价格' from goods; -- 熟练之后 as 关键字可以省略
select goodsName 商品名称,price 价格 from goods; -- 中文别名引号可以省略-- 去重 : 根据所给字段, 将字段中数据相同数据合并保留一个
-- 需求 : 当前表当中一共有几家公司
select distinct(company) from goods;

2) 条件查询

需求4 : 比较运算符/逻辑运算符

-- 需求4: 查询价格等于30并且出自并夕夕的所有商品信息
-- 条件查询 : where 条件
select * from goods;
select * from goods where price = 30; -- 比较运算符
select * from goods where price = 30 and company = '并夕夕'; -- 逻辑运算符 and : 并列条件
-- 注意 : 在 where 关键后侧的字符串数据, 必须给引号, 否则会报错
-- select * from goods where price = 30 and company = 并夕夕;

需求5 : 模糊查询

-- 需求5: 查询全部一次性口罩的商品信息
-- 查询所有数据
select * from goods;
-- 模糊查询 : like (字符: % 匹配任意的多个字符)
-- 语句格式 : where 字段 like '%信息'
select * from goods where remark like '%一次性口罩'; -- 以一次性口罩结尾, 前面内容不限制
-- 由于目标信息可能出现在中间部分
-- 注意 : 模糊查询时要注意条件设定, 避免遗漏数据
select * from goods where remark like '%一次性口罩%'; -- 提取内容中包含目标信息的数据-- 扩展: 查询公司来源是 x宝 的商品信息内容
-- like ( _ : 匹配任意单个字符)
select * from goods where company like '_宝';

需求6 : 范围查询

-- 需求6: 查询所有价格在30-100的商品信息 
-- 查询所有数据
select * from goods;
-- 条件查询-范围查询 : between 起始值 and 结束值 : 表示一个连续的范围
select * from goods where price between 30 and 100;
-- 注意: 范围应该从小到大
-- select * from goods where price between 100 and 30; -- 查询不出数据-- 扩展: 以下数据出自于 某东 和 x宝 商品信息
-- 条件查询-范围查询 : in (条件1, 条件2, ...): 表示在某个不连续范围内
select * from goods where company in ('某东', 'x宝');

需求7 : 空判断

-- 需求7: 查询没有描述信息的商品信息
-- 查询所有数据
select * from goods;
-- 判断空 : is null 
-- 注意 : null 与 '' 不是一码事; null : 空; '': 空字符(例如:空格/制表符(tab))
select * from goods where remark is null;-- 扩展 : 查询以下数据中描述信息不为空的所有商品数据
select * from goods where remark is not null;  -- not : 表示对条件取反; not null : 双重否定表示肯定

需求8 : 排序

-- 需求8: 查询所有商品信息, 按照价格从大到小排序, 价格相同时, 按照数量少到多排序
-- 查询所有数据
select * from goods;
-- 排序 : order by 字段名 asc(升序)/desc(降序) 
select * from goods order by price desc;
-- 需求实现
select * from goods order by price desc, count asc;
-- 扩展: 
select * from goods order by price; -- 默认情况下 order by 是以升序进行排列
select * from goods order by price desc, count; -- 因此 asc 可以省略

需求9 : 聚合函数

-- 需求9: 查询以下信息: 商品信息总条数; 最高商品价格; 最低商品价格; 商品平均价格; 一次性口罩的总数量
-- 查询所有数据
select * from goods;
-- 聚合函数 : SQL 提前准备还一些方法, 具备一定的作用
-- 数据总数 : count()
select count(*) from goods;
select count(remark) from goods; -- 注意: 统计数据总数时, 推荐使用 * (按照数据最多的那一列统计总数) 
-- 最大值 : max()
select max(price) from goods;
-- 最小值 : min()
select min(price) from goods;
-- 平均值 : avg()
select avg(price) from goods;
-- 求和 : sum()
select * from goods where remark like '%一次性口罩%'; -- 先获取所有一次口罩的数据
select sum(count) from goods where remark like '%一次性口罩%'; -- 需求实现

需求10 : 分组

-- 需求10: 查询每家公司的商品信息数量
-- 查询所有数据
select * from goods;
-- 分组 : group by 字段 : 可以根据给出的字段数据进行数据分组
-- 注意 : 一般情况下, 分组要配合聚合函数一起使用, 目的是对分组后的数据进行进一步统计
select company 公司名称,count(*) from goods group by company;

-- 分组后再次进行条件筛选
-- 扩展 : 查询某东和x宝最贵商品的价格
-- 先按照公司分组
select company 公司名称 from goods group by company;
-- 注意 : 如果想在分组后再次进行条件筛选, 可以使用 having 关键字
-- 把并夕夕的数据排除掉
select company 公司名称 from goods group by company having company != '并夕夕';
-- 获取公司商品最贵价格
select company 公司名称, max(price) from goods group by company having company != '并夕夕';

需求 11 : 分页查询

-- 需求11: 查询当前表当中第5-10行的所有数据
-- 查询所有数据
select * from goods;
-- 分页查询 : limit 起始索引, 数据行数
-- 索引 : 在计算机当中, 不同于人类的自然计数从 1 开始, 计算机的计数起始值是 0 开始(索引)
-- 起始索引: 实际数据顺序 - 1 即为其对应的索引值
select * from goods limit 4, 6;-- 扩展 : 如果获取是当前数据中的第1行数据时, 起始索引可以省略
-- 获取当前数据中的第1行数据
-- select * from goods limit 0, 1;
select * from goods limit 1;

-- 分页查询公式的使用
-- 扩展 : 分页查询公式的应用
-- 条件 : 每页显示 m 条数据，求：显示第 n 页的数据
-- 公式 : (n-1)*m, m : 查看指定页面数据
-- 假设 : 当前数据中, 每页显示 3 条数据, 查看第 2 页数据内容
select * from goods limit 3, 3;

---

SQL 查询作业安排

• 课上的后9个案例, 脱离笔记, 独立完成一遍!
• 完成作业1之后, 完成课下作业2(晚自习发)
• 作业完成后, 提交在线文档, 以小组形式, 进行互审
• 互审过程中记录问题, 完成互审后, 联络老师进行问题沟通

总结

Linux 和数据库第三节随堂笔记【day05】

连接查询

需求1: 内连接

-- 需求1: 查询所有存在商品分类的商品信息
-- 查询商品数据
select * from goods;
select * from category;
-- 在查询中, 需要将两张表存在对应关系的数据全部显示出来时, 需要使用连接查询-内连接
-- 内连接 : 显示两张表中存在对应关系的数据, 无对应关系的数据不显示
-- 语法 : 表1 inner join 表2 on 表1.字段 = 表2.字段
select * from goods inner join category on goods.typeId = category.typeId;

连接查询共通知识点

-- 连接查询共用知识点
-- 对连接后的表进行字段显示限制
-- 注意: 需要由对应的 表名.字段 的方式实现(防止表与表之间存在相同的字段名)
select goods.goodsName, category.cateName from goods inner join category on goods.typeId = category.typeId;
-- 表名.* : 代表显示该表的所有字段
select goods.*, category.cateName from goods inner join category on goods.typeId = category.typeId;-- 连接查询中, 往往会给表名起别名, 目的: 1) 缩短表名 2) 给表单独创建空间(了解) 
select go.goodsName, ca.cateName from goods go inner join category ca on go.typeId = ca.typeId;

需求2: 左连接

-- 需求2: 查询所有商品信息,包含商品分类
-- 查询所有商品
select * from goods;
select * from category;
-- 左连接 : 以 left join 关键为界, 关键字左侧的表的信息要全部显示出来, 而关键字右侧表的信息, 有对应的部分显示, 无对应的部分以 null 占位填充即可
-- 语法 : 表1 left join 表2 on 表1.字段 = 表2.字段
select * from goods go left join category ca on go.typeId = ca.typeId;

-- 需求3: 查询所有商品分类及其对应的商品的信息
-- 左连接
select * from category ca left join goods go on ca.typeId = go.typeId;

需求2: 右连接

-- 需求2: 查询所有商品信息,包含商品分类
-- 查询所有商品
select * from goods;
select * from category;
-- 注意 :  右连接主要解决 3 张及以上表进行连接查询时常见
-- 右连接 : 以 right join 关键为界, 关键字右侧的表的信息要全部显示出来, 而关键字左侧表的信息, 有对应的部分显示, 无对应的部分以 null 占位填充即可
-- 语法 : 表1 right join 表2 on 表1.字段 = 表2.字段
select * from category ca right join goods go on ca.typeId = go.typeId;

-- 需求3: 查询所有商品分类及其对应的商品的信息
-- 右连接
select * from goods go right join category ca on go.typeId = ca.typeId;

内连接和左右连接的小结

连接查询的实质

-- 需求 : 显示所有价格低于 120 的商品信息, 包含其对应商品分类
select * from goods;
select * from category;
-- 注意 : 
-- 1. 连接操作实质: 只是将分布于多张表的数据, 通过连接的方式进行整理, 形成数据源
-- 2. 连接操作完成后, 之前的所有的查询语法, 都可以继续使用
-- 3. 具体连接方法的选择, 要以具体需求为准
-- 内连接 : 如果有商品没有分类对应信息, 不会被显示出来
-- 注意 : 如果使用内连接, 会造成数据缺失, 查询结果错误
select * from goods go 
inner join category ca on go.typeId = ca.typeId
where go.price < 120;-- 左连接
select * from goods go 
left join category ca on go.typeId = ca.typeId
where go.price < 120;

自关联的介绍

特征 : 只有 1 张表, 表中最少存在两列字段具备对应关系;
原理 : 通过起别名的方式, 将 1 张表变为 2 张表, 通过对应字段的对应关系, 进行条件比对, 实现连接查询

需求4: 自关联

-- 需求4: 查询河南省所有的市
-- 查询所有数据
select * from areas;
-- 自关联 : 
-- 1) 通过起别名的方式, 将一表变俩表
-- 2) 通过 表1.字段 = 表2.字段 实现表间关联
select * from areas a1 
inner join areas a2 on a1.aid = a2.pid
where a1.atitle = '河南省';

需求5 : 自关联

-- 需求5: 查询河南省的所有的市和区
-- 自关联
select * from areas;
-- 
select * from areas a1 
inner join areas a2 on a1.aid = a2.pid
left join areas a3 on a2.aid = a3.pid
-- inner join areas a3 on a2.aid = a3.pid
where a1.atitle = '河南省';

子查询的介绍

定义 : 在一条查询语句当中, 利用另一条语句作为条件或数据源, 充当条件或数据源的语句称之为子查询语句.
注意 : 子查询语句在使用时, 务必使用括号括起来 

需求6 : 子查询-充当条件

-- 需求6: 查询价格高于平均价的商品信息
-- 查询所有数据
select * from goods;
-- 查询平均价格
select avg(price) from goods; -- 68.33
-- 需求实现
select * from goods where price > 68.33;
-- 优化实现 - 子查询 : 在 where 关键字后边的子查询语句是充当条件的
select * from goods where price > (select avg(price) from goods);

需求7 : 子查询-充当数据源

-- 需求7: 查询所有来自并夕夕的商品信息, 包含商品分类
-- 查询所有数据
select * from goods;
select * from category;
-- 并夕夕商品信息
select * from goods where company = '并夕夕';
-- 需求实现 - 子查询 : 在 from 和连接查询关键字后边的子查询语句时充当数据源的
select * from category ca 
inner join (select * from goods where company = '并夕夕') a on ca.typeId = a.typeId;

扩展1 : 子查询的分类

根据子查询语句返回的结果形式体现, 子查询共分为 4 类:
子查询返回的结果是一个值(一行一列), 称之为: 标量子查询
子查询返回的结果是一列数据(一列多行), 称之为: 列子查询
子查询返回的结果是一行数据(一行多列), 称之为: 行子查询
子查询返回的结果是多行多列(相当于一张表), 称之为: 表级子查询

扩展2 : 子查询关键字

-- 需求 : 查询在25-100之间的商品的价格
select price from goods where price between 25 and 100;
-- in : 范围
-- select * from goods where price in (25, 30, 77, 30, 72,25);
select * from goods where price in (select price from goods where price between 25 and 100);
-- some/any : 任意一个
select * from goods where price = some(select price from goods where price between 25 and 100);
select * from goods where price = any(select price from goods where price between 25 and 100);
-- all : 全部(= all : 等于所有; != 或 <> all : 不等于所有)
select * from goods where price = all(select price from goods where price between 25 and 100);
select * from goods where price != all(select price from goods where price between 25 and 100);
select * from goods where price <> all(select price from goods where price between 25 and 100);

Linux 和数据库第四节随堂笔记【day05】

今日内容介绍

全天内容无需立马掌握

• 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-- 声明变量 ideclare i int; -- 变量初始化赋值为 0set 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;-- 注意: 日志功能使用完成, 需要记得马上关闭, 避免影响数据库性能!!!!!!

查看日志功能是否开启

开启日志功能

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

关闭日志功能

今天这篇文章就到这里了，大厦之成，非一木之材也；大海之阔，非一流之归也。感谢大家观看本文

这篇关于Linxu系统之Mysql数据库练习【完整版】的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---