第七章 数据库 附件-MySQL的安装与使用

第7章数据库附件-安装和使用-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的默认端口号是：3306

2.为此端口添加防火墙例外(将MySQL端口号添加到防火墙白名单中，允许连接使用)

3.下一步

1.手动选择默认字符集。

2.字符集：utf8(防止中文乱码)

3.下一步

1.默认服务名：MySQL

2.当系统打开时，允许服务自动启动。

3.将MySQL添加到系统环境变量中

(此操作确保MySQL可以在系统内的任何路径上与命令行连接。)

1.设置MySQL默认用户的密码(默认用户名是root，所以不需要在这里设置用户名，只需设置密码即可。例子

例如123456)

2.再次输入密码(目的：确认输入的密码正确)

3.允许远程连接访问MySQL(如果未选中此选项，则不能从外部连接MySQL)

4.下一步

1.执行(配置)

执行过程.

1.这里全部勾选，配置成功！

2.配置步骤结束

1.3、MySQL 服务-启动/停止/重启

开始菜单

搜索服务

在搜索结果中选择服务。

已安装的MySQL服务默认为：自动和已启动状态。

您可以停止并重新启动服务(暂停功能不使用该场景)。

1.4、MySQL 服务-修改设置

MySQL服务名

鼠标右键-属性

设置启动类型(默认为自动)

1.5、MySQL 配置文件位置

注意：MySQL配置文件的位置非常重要，MySQL的相关设置都与配置文件有关，比如修改默认用户root的密码。

2、客户端

2.1、MySQL 自带命令行客户端的使用

开始菜单

搜索mysql

在搜索结果中选择MySQL 5.5命令行客户端。

输入配置MySQL时设置的密码。

注意

：如果输入完密码， 命令行 窗口 闪退 ，请确认 密码是否正确 或 MySQL 服务是否为开启状态

1. 输入密码后，按 回车键 ，如果显示如图的 mysql ，则为登录 MySQL 成功

1. 可以输入 exit 或 quit 命令，退出 MySQL 命令行客户端

2.2、MySQL 第三方客户端的使用

注意：

1. 由于 MySQL 自带的命令行客户端 易用性 不强，因此在日常工作中多用 第三方 的 图像化客户端 ，例如： Navicat
2. 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;