第七章 数据库 附件-MySQL的安装与使用
一、MySQL安装与使用
1、服务端
1.1、服务端-安装步骤
1. 安装文件 mysql-5.5.62-winx64.msi 上,鼠标右键
2. 安装
准备安装 ......
1. 下一步
1. 同意安装协议2. 下一步
1. 典型安装注意:为了统一教学演示路径,MySQL 推荐默认安装到 C 盘下。(安装完的文件路径不要再次修改,例如:将文件夹名字修改为中文或者变动文件存放位置)
1. 安装
安装过程 ......
1. 确认 启动 MySQL 配置向导 选项为勾选状态2. 安装步骤完成
1.2、服务端-配置步骤
1. 下一步
1. 详细配置2. 下一步
1. 开发者机器(设备类型)2. 下一步
1. 多功能数据库 (数据库类型)2. 下一步
1. 下一步
1. 决策支持(连接数量)2. 下一步
1. MySQL 默认的端口号为: 33062. 为此端口添加防火墙例外(添加 MySQL 端口号到防火墙的白名单,允许被连接使用)3. 下一步
1. 手动选择默认字符集2. 字符集: utf8 (防止中文乱码)3. 下一步
1. 默认服务名: MySQL2. 允许服务随系统开机自动启动3. 添加 MySQL 到系统环境变量(该操作确保可以在系统内的任意路径下,都可以使用 命令行 连接 MySQL)
1. 设置 MySQL 默认用户的密码 (默认用户名为 root ,此处不需要设置用户名,直接设置密码即可。例如: 123456 )2. 再次输入密码(目的:确认输入的密码无误)3. 允许远程连接访问 MySQL (如果不勾选此项,则从外部无法连接 MySQL)4. 下一步
1. 执行(配置)
执行过程 ......
1. 此处全部为 勾选状态 ,才为配置成功!2. 配置步骤结束
1.3、MySQL 服务-启动/停止/重启
- 开始 菜单
- 搜索 服务
- 搜索结果中选择 服务
![](https://img2020.cnblogs.com/blog/948931/202112/948931-20211205154114647-228968619.png)
- 安装完的 MySQL 服务默认为: 自动 和 已启动 状态
- 可以 停止 服务, 重启动 服务(暂停功能没有使用场景)
1.4、MySQL 服务-修改设置
- MySQL 服务名上
- 鼠标右键 -> 属性
![](https://img2020.cnblogs.com/blog/948931/202112/948931-20211205153745626-1146132279.png)
- 设置 启动类型 (默认为 自动 )
1.5、MySQL 配置文件位置
注意:MySQL 配置文件的位置很重要,MySQL 的相关设置都和配置文件有关,例如:修改默认用户 root 的密码
2、客户端
2.1、MySQL 自带命令行客户端的使用
- 开始 菜单
- 搜索 mysql
- 搜索结果中选择 MySQL 5.5 Command Line Client
- 输入配置 MySQL 时设置的密码
注意:如果输入完密码, 命令行 窗口 闪退 ,请确认 密码是否正确 或 MySQL 服务是否为开启状态
- 输入密码后,按 回车键 ,如果显示如图的 mysql> ,则为登录 MySQL 成功
- 可以输入 exit 或 quit 命令,退出 MySQL 命令行客户端
2.2、MySQL 第三方客户端的使用
- 由于 MySQL 自带的命令行客户端 易用性 不强,因此在日常工作中多用 第三方 的 图像化客户端 ,例如: Navicat
- Navicat 客户端的安装与激活步骤,请参考 Navicat 12 Windows版本的安装与激活步骤 文档,此处不再赘述。
二、MySQL内置函数
1、字符串函数
-
拼接字符串concat(str1,str2...)
select concat(12,34,'ab');
-
包含字符个数length(str)
select length('abc');
-
截取字符串
-
left(str,len)返回字符串str的左端len个字符
-
right(str,len)返回字符串str的右端len个字符
-
substring(str,pos,len)返回字符串str的位置pos起len个字符
select substring('abc123',2,3);
-
去除空格
-
ltrim(str)返回删除了左空格的字符串str
-
rtrim(str)返回删除了右空格的字符串str
select ltrim(' bar ');
-
大小写转换,函数如下
-
lower(str)
-
upper(str)
select lower('aBcD');
2、数字函数
- 求四舍五入值round(n,d),n表示原数,d表示小数位置,默认为0
select round(1.6);
- 求x的y次幂pow(x,y)
select pow(2,3);
- 获取圆周率PI()
select PI();
- 随机数rand(),值为0-1.0的浮点数
select rand();
3、日期时间函数
- 当前日期current_date()
select current_date();
- 当前时间current_time()
select current_time();
- 当前日期时间now()
select now();
- 日期格式化date_format(date,format)
- 参数format可选值如下
%Y 获取年,返回完整年份
%y 获取年,返回简写年份
%m 获取月,返回月份
%d 获取日,返回天值
%H 获取时,返回24进制的小时数
%h 获取时,返回12进制的小时数
%i 获取分,返回分钟数
%s 获取秒,返回秒数
- 例:将使用-拼接的日期转换为使用空格拼接
select date_format('2016-12-21','%Y %m %d');
4、流程控制函数
- case语法:等值判断
- 说明:当值等于某个比较值的时候,对应的结果会被返回;如果所有的比较值都不相等则返回else的结果;如 果没有else并且所有比较值都不相等则返回null
case 值 when 比较值1 then 结果1 when 比较值2 then 结果2 ... else 结果 end
例:
select case 1 when 1 then 'one' when 2 then 'two' else 'zero' end as result;
5、自定义函数
创建
- 语法如下
delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter ;
- 说明:delimiter用于设置分割符,默认为分号
- 在“ SQL 语句”部分编写的语句需要以分号结尾,此时回车会直接执行,所以要创建存储过程前需要指定其它 符号作为分割符,此处使用//,也可以使用其它字符
示例
- 要求:创建函数my_trim,用于删除字符串左右两侧的空格
- step1:设置分割符
delimiter $$
- step2:创建函数
create function my_trim(str varchar(100)) returns varchar(100)
begin
return ltrim(rtrim(str));
end
$$
- step3:还原分割符
delimiter ;
使用自定义函数
select ' abc ',my_trim(' abc ')
三、MySQL存储过程
存储过程,也翻译为存储程序,是一条或者多条 SQL 语句的集合
1、创建
- 语法如下
delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//
delimiter ;
- 说明:delimiter 用于设置分割符,默认为分号
- 在“ SQL 语句”部分编写的语句需要以分号结尾,此时回车会直接执行,所以要创建存储过程前需要指定其它 符号作为分割符,此处使用//,也可以使用其它字符
2、示例
- 要求:创建查询过程,查询学生信息
- step1:设置分割符
delimiter //
- step2:创建存储过程
create procedure proc_stu()
begin
select * from students;
end
//
- step3:还原分割符
delimiter ;
3、调用
- 语法如下
call 存储过程(参数列表);
调用存储过程proc_stu
call proc_stu();
- 存储过程和函数都是为了可重复的执行操作数据库的 SQL 语句的集合。
- 存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 SQL 语句,不需要重复编译
- 减少网络交互,减少网络访问流量
四、事务
1、为什么要有事务
-
事务广泛的运用于订单系统、银行系统等多种场景
-
例如:A用户和B用户是银行的储户,现在A要给B转账500元,那么需要做以下几件事:
- 1. 检查A的账户余额>500元;
- 2. A 账户中扣除500元;
- 3. B 账户中增加500元;
- 正常的流程走下来,A账户扣了500,B账户加了500,皆大欢喜。那如果A账户扣了钱之后,系统出故障了 呢?A白白损失了500,而B也没有收到本该属于他的500。以上的案例中,隐藏着一个前提条件:A扣钱和B 加钱,要么同时成功,要么同时失败。事务的需求就在于此
- 所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。例如, 银行转帐工作:从一个帐号扣款并使另一个帐号增款,这两个操作要么都执行,要么都不执行。所以,应该把 他们看成一个事务。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性
2、事务命令
- 要求:表的引擎类型必须是 innodb 类型才可以使用事务,这是 MySQL 表的默认引擎
- 查看表的创建语句,可以看到 engine=innodb
show create table students;
- 修改数据的命令会触发事务,包括insert、update、delete
- 开启事务,命令如下:
- 开启事务后执行修改命令,变更会维护到本地缓存中,而不维护到物理表中
begin;
- 提交事务,命令如下
- 将缓存中的数据变更维护到物理表中
commit;
- 回滚事务,命令如下:
- 放弃缓存中变更的数据
rollback;
3、提交事务
- 为了演示效果,需要打开两个命令行窗口,使用同一个数据库,操作同一张表
step1:查询
- 命令行1和命令行2:查询学生信息
select * from students where name in('大乔','小乔');
step2:修改数据
- 命令行1:开启事务,修改数据
begin;
update students set age=age-5 where name='大乔';
update students set age=age+5 where name='小乔';
- 命令行1:查询数据,发现数据已经变化
select * from students where name in('大乔','小乔');
step3:查询
- 命令行2:查询数据,发现数据没有变化
select * from students where name in('大乔','小乔');
step4:提交
- 命令行1:完成提交
commit;
step5:查询
- 命令行2:查询数据,发现数据已经变化
select * from students where name in('大乔','小乔');
4、回滚事务
- 为了演示效果,需要打开两个命令行窗口,使用同一个数据库,操作同一张表
step1:查询
- 命令行1和命令行2:查询学生信息
select * from students where name in('大乔','小乔');
step2:修改数据
- 命令行1:开启事务,修改数据
begin;
update students set age=age-5 where name='大乔';
update students set age=age+5 where nama='小乔';
- 命令行1:查询数据,发现数据已经变化
select * from students where name in('大乔','小乔');
step3:查询
- 命令行2:查询数据,发现数据没有变化
select * from students where name in('大乔','小乔');
step4:回滚
- 命令行1:完成回滚
rollback;
step5:查询
- 命令行1:查询数据,发现数据恢复为开启事务前的状态
select * from students where name in('大乔','小乔');
五、视图
- 对于复杂的查询,在多个地方被使用,如果需求发生了改变,需要更改sql语句,则需要在多个地方进行修 改,维护起来非常麻烦
- 解决:定义视图
- 视图本质就是对查询的封装
- 定义视图,建议以 v_ 开头
语法:
create view 视图名称 as select 语句;
- 例:创建视图,查询学生对应的成绩信息
create view v_stu_score_course as
select
stu.*,cs.courseNo,cs.name courseName,sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on cs.courseNo = sc.courseNo
- 使用:视图的用途就是查询
select * from v_stu_score_course;
- 查看视图:查看表会将所有的视图也列出来
show tables;
- 删除视图
drop view 视图名称;
例:
drop view v_stu_score_course;
六、用户密码
1、修改密码
- 使用root登录,修改mysql数据库的user表
- 使用password()函数进行密码加密
- 注意修改完成后需要刷新权限
use mysql;
update user set password=password('新密码') where user='用户名';
例:
update user set password=password('123') where user='root';
刷新权限:flush privileges;
2、忘记root账号密码怎么办
1、配置mysql登录时不需要密码,修改配置文件
- Centos中:配置文件位置为/data/server/mysql/my.cnf
- Windows中:配置文件位置为C:\Program Files (x86)\MySQL\MySQL Server 5.1\my.ini
修改,找到mysqld,在它的下一行,添加skip-grant-tables
[mysqld]
skip-grant-tables
2、重启mysql,免密码登录,修改mysql数据库的user表
use mysql;
update user set password=password('新密码') where user='用户名';
例:
update user set password=password('123') where user='root';
刷新权限:flush privileges;
3、还原配置文件,把刚才添加的skip-grant-tables删除,重启
七、MySQL日志
记录 MySQL 所有的操作日志(包括增删改查),不过它会耗费数据库5%-10的性能,所以一般没特别需要时不打开此功能,一般在查找问题时才打开,完成后及时关闭。
1、客户端连接 MySQL
2、查询日志是否开启: show variables like 'general%';
3、打开日志: set global general_log = 1;
4、打开对应目录下的日志文件,执行增删改查等sql语句后,观察日志文件的变化
注意:不需要使用日志时及时关闭日志: set global general_log = 0;