MySQL初步学习（一）

MySQL初步学习（一）

一 MySQL的初步认识

二 MySQL的下载与安装

三 MySQL的基本操作

1 数据库的相关操作

1.1 数据库的创建与使用

create database 数据库1 [character set utf8] [collate utf8_unicode_ci];
use 库名1;

1.2 数据库的删除

drop database 库名;

1.3 数据库的修改

1.3.1 修改数据库的编码
alter database 库名1 character set 新编码名;

1.4 数据库的查询

1.4.1 查看当前mysql服务下的所有数据库
show databases;
1.4.2 查看当前正在使用的数据库
select database();
1.4.3 查看库的创建信息
show create database 库名1;

2 数据表的相关操作

2.1 表的创建

create table 表名(
字段1 字段类型1 [修饰量],
字段2 字段类型2 [修饰量],
···
)[ENGINE=InnoDB] [CHARSET=utf8mb4];

2.2 表的删除

drop table 表名;

2.3 表的修改

2.3.1 对已存在的表重命名
rename table 旧表名 to 新表名;
2.3.2 对已存在的表添加字段信息
alter table 表名 add 新字段1 字段类型1 [修饰量];
2.3.3 删除表中某个字段
alter table 表名 drop 字段1;
2.3.4 修改表中某一字段
alter table 表名 change 旧字段 新字段 新字段类型 [新修饰量]; //1
alter table 表名 modify 要修改的字段名 新字段类型 [新修饰量]; //2

2.4 表的查询

2.4.1 查询当前库下的所有表
show tables;
2.4.2 查询表结构
desc 表名;
2.4.3 查询表的创建信息
show create table 表名;
2.4.4 查询表的所有字段的所有信息
show full columns from 表名

2.5 表的其他操作

2.5.1 给表添加注释，可参考文章

alter table 表名 comment='表的注释';

2.5.2 MySQL：ERROR 1366 (HY000)错误解决

3 数据的相关操作

3.1 添加数据

1》字段与值一一对应
insert into 表名(字段1,字段2,···) values(值1,值2,···);
2》值的顺序和数量，应与全表一致
insert into 表名 values(值1,值2,···);
3》最灵活
insert into 表名 set 字段1=值1,字段2=值2,···;

3.2 删除数据

1》按条件删除
delete from 表名 where 条件;
2》全删，不会重置序列
delete from 表名 where 条件=true; 或 delete from 表名;
3》全删，效率比delete高，会重置序列
truncate table 表名;

3.3 修改数据

1》当条件为true时，对应字段会被全改为唯一值
update 表名 set 字段1=值1,字段2=值2,··· where 条件;

3.4 查询数据

1》全查
select * from 表名;
2》按条件查
select * from 表名 where 条件;

四 MySQL的查询

1》排序
select * from 表名 order by 字段1 desc; //降序
select * from 表名 order by 字段1 asc; //升序

2》聚合函数（5个）

select max(字段1) from 表名;  //查询表中某个字段的最大值
select mix(字段1) from 表名;  //查询表中某个字段的最小值
select avg(字段1) from 表名;  //查询表中某个字段的平均值
select sum(字段1) from 表名;  //查询表中某个字段的值的和
select count(字段1) from 表名;  //查询表中某个字段值非空的个数，字段为 * 时，查询记录条数

3》分页查询
select * from 表名 limit (pageNo-1)*pageSize; //pageNo，为第几页；pageSize，为一页几条记录

4》模糊查询
select * from 表名 where 字段 like '%ab_c%'; // % ，多位； _ ，一位

5》分组查询
select 字段1 from 表名 order by 字段1; //对字段1的类容进行分组

6》常用时间函数

select curdate();  //获取当前日期：2020-03-08
select curtime();  //获取当前时间：14：17：21
selec now();  //获取当前日期+时间：2020-03-08 14：17：21
select date_format(now(), '%Y/%m/%d--%H:%i:%s');  //时间格式化函数，%y为2位年

7》union与union all
当多条查询语句的返回结果的表结构一致时，可用union或union all将他们合并，其中union会去除重复数据，union all不会去除重复数据。

8》多表查询

//语法1
select * from 表1,表2,··· where 关联条件1,关联条件2,···;
//语法2
//内连接
select * from 表1 inner join 表2 on 关联条件;
//外连接
select * from 表1 left join 表2 on 关联条件;  //左连接
select * from 表1 right join 表2 on 关联条件;  //右连接

9》别名
可以给要查询的表起别名，之后在SQL语句中要用该别名代替该表名
可以给要查询的字段起别名，在查询出来的表中会展示该别名
select t1.字段1 as 展示1 from 表1 as t1; //as可以省略

五 MySQL的视图与存储过程

5.1 视图

1》视图就是在真实表的基础上，构建一张虚表

create view 视图名 as
select * from 表1,表2,··· where 关联条件1,关联条件2,···;

2》视图的操作与表的操作基本一致，只要将有table的地方改为view；对视图的增删改，会影响到相关表

5.2 存储过程

1》存储过程的创建（入参前的 in 已省略）

delimiter //
create procedure 存储过程名(入参1,入参2,···,out 出参1,out 出参2,···)
begin
	代码块;
end //
delimiter ;

2》删除存储过程
drop procedure 存储过程名;

3》调用存储过程

call 存储过程名(入参1,入参2,···,@出参1,@出参2,···);  //出参可以随意起名
select @出参1,@出参2,···;  //查询出参

4》代码块中的常用语法

//1、定义代码块中的临时变量
declare 变量1 类型 [default 初始值];

//2、给变量赋值
set 变量1=值;

//3、if代码块1
if 变量1>0 then
    代码块;
end if;

//3、if代码块2
if 变量1>0 then
    代码块;
else
    代码块;
end if;

//3、if代码块3
if 变量1>0 then
    代码块;
else if 变量2>0 then
    代码块;
    ···
else
    代码块;
end if;

//4、case分支选择结构，类似switch
case 变量
    when 变量可能值1 then
        代码块;
    when 变量可能值2 then
        代码块;
    ···
    else
        代码块;
end case;

//5、while循环
declare 控制量k int default 0;
while k<界限 do
    代码块;
    set k=k+1;
end while;

//6、loop循环
declare 控制量k int default 0;
循环名:loop
    代码块;
    set k=k+1;
    if k>界限 then
        leave 循环名;
    end if;
end loop;

六 MySQL的事务

6.1 事务

1》定义：多组操作，要么一起完成，要么一起失败，这种特性就叫做事务性(?)。

2》事务的4特性：原子性，一致性，隔离性，持久性

3》mysql中的隔离级别（由低到高）：
read-uncommited < read-commited < repeatable-read < serializable
同一时间段，只能有一种隔离级别，隔离级别越低，数据库的性能越好，越高越差。

4》隔离级别引起的问题：
脏读（read-uncommited 引起）：
虚读与幻读（read-commited 引起）：
不可重复读（repeatable-read 引起）：

5》隔离级别的相关操作

//1、查看隔离级别
select @@tx_isolation;  //旧版
select @@transaction_isolation;  //新版

//2、修改隔离级别，需要重新登录命令行或第三方工具
set global transaction isolation level read-uncommited;

//3、开启事务
start transaction;

//4、回滚事务
rollback;

//5、提交事务
commit;

七 MySQL的备份与恢复

1》无需第三方工具
（1）备份
win+r >> cmd >> mysqldump -uroot -p 库名>备份后的SQL脚本名（带全路径及文件类型）
此方式备份的脚本，只备份了数据库里的数据，未备份数据库
（2）还原
进入mysql命令行 >> 创建并选择数据库 >> source 备份后的SQL脚本名（带全路径及文件类型）

2》需要第三方工具
（1）备份
选中库后直接右键备份
（2）还原
加载备份SQL脚本，直接全选执行

八 MySQL其他常见操作

1 MySQL服务的登录密码修改

1，选择mysql数据库：use mysql;
2，查询用户的信息：select host,user,password from mysql.user;
3，修改密码
update user set password=password('新密码') WHERE User='root'; --旧版
update user set authentication_string=PASSWORD('新密码') where user='root'; --新版
4，刷新权限：flush privileges;
5，退出重登。

2 MySQL初次安装后修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

3 MySQL用户权限设置

参考文献1
参考文献2

4 MySQL远程访问权限控制

1、查询当前mysql数据库用户与主机的匹配情况：
select user,host from user;

2、赋予root角色能够通过所有主机（ip）访问当前数据库的权限；

# 赋予root角色能够通过所有主机（ip）访问当前数据库的权限
grant all privaleges on *.* to 'root'@'%' identified by 'password';


# 刷新权限信息
# 这一步一定要做，不然无法成功！ 
# 这句表示从mysql数据库的grant表中重新加载权限数据
# 因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。
flush privileges;

# 由于MySQL8.0+的修改
# 先要创建用户，因为grant不能创建用户了，密码格式也做了修改，采用原生是为了方便可视化工具连接
create user 'root'@'%' identified with mysql_native_password by '密码';

# grant 后面不能接密码，会报语法错误
grant all privileges on *.* to 'root'@'%';

# 刷新权限
flush privileges;