mysql-知识总结

测试开发知识积累

已于 2022-07-18 20:21:23 修改

阅读量487

点赞数

分类专栏：技术类文章标签： mysql 数据库服务器

于 2020-03-18 22:21:04 首次发布

本文链接：https://blog.csdn.net/qq_17200461/article/details/104953914

版权

技术类专栏收录该内容

94 篇文章 3 订阅

订阅专栏

简介

mysql是一种关系型数据库。
数据库是一种特殊的文件，能存储在硬盘上，使用RDBMS软件可操作数据库文件。
RDBMS是关系型数据库管理系统，是一种软件，包括客户端和服务器端。
可在RDBMS客户端通过sql语句控制mysql数据库
在这里插入图片描述

数据库排名

在这里插入图片描述

备份数据

mysqldump -uroot -p 数据库名 > yxx.sql
在这里插入图片描述

恢复数据

1、连接mysql，创建新的数据库
2、退出连接，执行：mysql -uroot -p 新数据库名 < yxx.sql
在这里插入图片描述
或者用source命令

数据完整性

一个数据库是一个完整的业务单元，可以包含多张表，数据被存储在表中。
在表中为了更加准确的存储数据，保证数据正确有效，可以在建表的时候为表添加一些强制性验证，包括数字类型和约束。

数据类型

1、常用数据类型
整数：int 、bit
小数: decimal
字符串：varchar、char
日期时间：date 、time、datetime
枚举类型：enum
2、常用数据类型说明
decimal 表示浮点数，如decimal(5,2)：表示共存5位数，小数占2位
char 表示固定长度的字符串，如char(3), 如果只存’ab’会自动不存一个空格’ab ‘
varchar表示可变长度的字符串，如varchar(3), 如果存’ab’就是’ab’
text 表示存大文本，当字符大于4000时推荐使用
对图片、视频等文件不存储在数据库中，而是上传到某个服务，然后在表中存储这个文件的保存路径。

约束

主键：primary key 物理上存储的顺序
not null: 不为空
unique:唯一
default:默认值
foreign key:外键。外键约束可以保证数据的有效性，但是在对数据进行增删改查时会降低数据库的性能。所有不推荐使用。建议在业务层进行逻辑控制。

连接数据库

mysql -h s -u s -p
在这里插入图片描述

数据库操作

1、查看数据库
在这里插入图片描述
2、新建数据库

3、查看数据库新建语句

4、查看当前使用的数据库
select database();

5、删除数据库

6、使用数据库

数据库表结构操作

1、查看表
在这里插入图片描述
2、创建数据库表

CREATE TABLE `wonderful_video` (
`id`  bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键' ,
`student_id`  bigint(20) unsigned NOT NULL COMMENT '学生ID' ,
`online_class_id`  bigint(20) unsigned NOT NULL COMMENT 'onlineClassId' ,
`video_url`  varchar(200) CHARACTER SET utf8 DEFAULT '' COMMENT '精彩视频下载URL' ,
`first_icon_url`  varchar(200) CHARACTER SET utf8 DEFAULT '' COMMENT '精彩视频封面url' ,
`create_time`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,
`update_time`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间' ,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_student_id` (`student_id`) USING BTREE ,
UNIQUE INDEX `uk_idx_online_class_id` (`online_class_id`) USING BTREE COMMENT '唯一索引',
INDEX `idx_studentId_time` (`student_id`, `create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='试听课精彩视频数据表';

说明：
（1）TINYINT 存储占1个字节，带符号的范围是-128到127。无符号的范围是0到255。
TINYINT[(M)] [UNSIGNED] [ZEROFILL] M默认为4，UNSIGNED表示无符号，默认是有符号的，ZEROFILL表示位数不够时用0填充
（2）INT[(M)] [UNSIGNED] [ZEROFILL] M默认为11
INT 存储占4个字节，带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295
普通大小的整数。
（3）VARCAHER[(M)]，M表示字符个数不管是中文、英文还是其他特殊字符
(4)BIGINT[(M)] [UNSIGNED] [ZEROFILL] M默认为20
大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。
(5)
BIT[M]
位字段类型，M表示每个值的位数，范围从1到64，如果M被忽略，默认为1

3、查看数据库表新建语句
show create table st_data
在这里插入图片描述
3、查看表结构

4、数据库表新增字段 (add)
alter table api_info add relate_script_id INT(11) unsigned default null comment ‘关联场景id’;

5、删除表字段（drop）
alter table api_info drop column relate_case_info_id;

6、更改数据库表字段属性 (modify)
alter table api_info modify relate_script_id INT(11) unsigned default null comment '关联场景id’;
在这里插入图片描述
6、更改数据库表字段名 (change)
ALTER TABLE 【表名】 CHANGE 【老字段名】【新字段名】【…属性】

7、更改表名
rename table 老表名 to 新表名

8、删除数据库表
在这里插入图片描述

数据库的增删改查

1、查找数据
在这里插入图片描述
2、全量插入数据

说明：a. 如果有id自增，可以给id对应的值传default、0、null或者大于当前id值的值
b.如果字段类型是枚举值，只能传枚举值或者枚举值对应的下标。比如genert = [‘男’，‘女’] ，可传‘男或者女’，或者1，2.枚举的下标从1开始。

3、部分插入
在这里插入图片描述
说明：1、部分插入需要指定字段名2、必填项必须插入
4、多行插入

5、更改数据

6、删除数据

7、清空表
delect from a;

比较运算符查询

< 、>、 =、 <=、 >=、 !=
在这里插入图片描述

逻辑运算符查询

and 、
or、
not
在这里插入图片描述

模糊查询

1、like,
rlike
2、模糊查询的效率比较低
3、like 使用‘%’替换一个或者多个字符 ,使用"_"替换一个字符。
在这里插入图片描述
4、rlike 跟正则表达式
. * ^ $等等

范围查询

in,
not in ,
between…and …,
not between… and …
在这里插入图片描述

判断空

is null
is not null

排序

order by id asc
order by id desc
可以根据多个字段进行排序
在这里插入图片描述

聚合

max()
min()
count()
sum()
avg()
round() 四舍五入
在这里插入图片描述

分组

group by
常和聚合一起使用。
在这里插入图片描述

having

having字句可以让我们筛选分组之后的各种数据，
where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。
而having子句在聚合后对组记录进行筛选。
我的理解就是真实表中没有此数据，这些数据是通过一些函数产生的。
having 是对分组的结果进行条件判断
在这里插入图片描述

另外，从结果集中找数据也可以用having

三、 having单独使用，与where类似
eg: 查询单笔订单充值金额大于1000的
SELECT regagent,amount FROM cy_pay_ok having amount>1000 ;
SELECT regagent,amount FROM cy_pay_ok where amount>1000 ;
两个查询结果一样

分页

一、初始记录行的偏移量是 0，
二、第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
三、limit 5 查前5个数据，LIMIT n 等价于 LIMIT 0,n。
四、limit 5,5 ，获取第五个值
五、为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
六、为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

关联查询

在这里插入图片描述

左连接

在这里插入图片描述
显示部分字段

右连接

在这里插入图片描述

内连接

mysql 默认是内连接
在这里插入图片描述

自关联

学生、成绩、课程表，要求查询001课程比002课程成绩高的所有学生的学号
Student(S#,Sname)
Cource(C#,Cname)
SC(S#,C#,score)
Teacher(T#,Tname)

注意：四张表只有SC表有用

select a.s# 
from sc as a
join sc as b
on a.s#=b.s#
where a.c# ='001'
and b.c#='002'
and a.score > b.score

或者

select a.S#
from SC a ,SC b
where a.S#=b.S#
and a.C#='001'
and b.C#='002'
and a.score>b.score

别名

as
说明：起了别名就用别名，不可用原表名

去重

distinct

显示数据库服务的时间

在这里插入图片描述

查看当前版本

在这里插入图片描述

我（where）哥(group by)是(select,having)偶(order by )像

sql执行顺序：W（where）->G（Group）->S（Select）->H（Having）->O（Order）

退出连接

quit 或者exit 或者ctrl+d

事务

事务是一个操作序列，要么全执行，要么不执行，它是不可分割的单位。

事务特征（ACID）

原子性：事务是不可分割的最小单元，要么都执行成功，要么都不执行不成功
一致性：中间服务挂掉不会出现数据错误。通过commit保证
隔离性：一个事务所做的修改在最终提交之前，对其他事务是不可见的
持久性：一旦事务提交，其所做的修改会永久保存到数据库中

开启事务：begin;或者start transcation；
提交事务：commit;
回滚事务：rollback
在这里插入图片描述

索引

· 索引是一张表，该表保存了主键和索引字段，并指向实体表记录。索引是一种特殊的文件，特殊的数据结构，它包含着对数据表中所有记录的引用指针。
· 通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。
· 索引应用在sql查询语句的条件(一般作为 WHERE 子句的条件)

使用索引的优缺点

优点：提高搜索速度（好比一本书的目录，可以加快数据查询速度）
缺点：索引文件会占用磁盘空间；降低更新表的速度，因为INSERT、UPDATE和DELETE时，不仅要保存数据，还要保存索引文件

单列索引和组合索引

单列索引即一个索引只包含一列
组合索引即一个索引包含多列

索引分类

普通索引
主键索引
唯一索引
全文索引
组合索引

创建索引语法

第一种：创建索引
create 【unique|fulltext】【index|key】 index_name on my_table (col_name(【length】))【asc | desc】

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引
2.index和key为同义词，两者作用相同，用来指定创建索引
3.col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择
4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度,char,varchar不需要指定索引
6.asc或desc指定升序或降序的索引值存储
在这里插入图片描述
第二种：修改表结构时添加索引
ALTER table tableName ADD【unique|fulltext】【index|key】【index_name】(col_name【length】)【asc|desc】

第三种：创建表时指定索引
CREATE TABLE table_name（col_name data type）
【unique|fulltext】【index|key】【index_name】(col_name【length】)【asc|desc】

普通索引

最基本的索引，它没有任何限制
索引列不允许有null

唯一索引

索引列的值必须唯一，如果是组合索引，则列值的组合必须唯一。
允许有空值

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
一般是在建表的时候同时创建主键索引：
CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) NOT NULL ,
PRIMARY KEY (id)
);

组合索引

指在多个字段上创建的索引，
只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
使用组合索引时遵循最左前缀集合

全文索引

· 主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
· fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。
· fulltext索引配合match against操作使用，而不是一般的where语句加like。
· 它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。
· 在数据量较大时候，先将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
· like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

删除索引

在这里插入图片描述

查看索引

在这里插入图片描述

使用索引注意事项

使用索引时，有以下一些技巧和注意事项：
1.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
2.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4.like语句操作
一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算
这将导致索引失效而进行全表扫描，例如
SELECT * FROM table_name WHERE YEAR(column_name)<2017;

6.不使用not in和<>操作

建索引的几大原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

mysql主从

1、主从同步可以使得数据从一个数据库服务器复制到另一台服务器。在复制数据时，一个服务器充当主服务器，其它服务器充当从服务器。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，通过配置文件，可以指定复制所有的数据库
2、使用主从同步的好处：
数据备份
读写分离
负载均衡
3、mysql服务器之间的主从同步是基于二进制日志机制。主服务器使用二进制日志记录数据库的变动情况。从服务器读取和执行该二进制文件来保证和主服务器的数据一致。
在这里插入图片描述
4、配置主从同步的方法
a.主服务器必须开启二进制日志机制和配置一个独立的ID；
b.在每个从服务器上配置一个唯一的ID，创建一个专门用来同步主服务器上数据的账号；
c.在开始复制进程前，在主服务器上记录二进制文件的位置。
d.在开始复制数据库前，如果数据库中已有数据，就必须先创建一个数据快照。
e.配置从服务器要连接的主服务器ip和登录授权，二进制文件名和位置。

创建用户

命令:CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;
说明：
username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
在这里插入图片描述

授权

1、命令:GRANT privileges ON databasename.tablename TO ‘username’@‘host’
2、说明:
privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*
例子:
GRANT SELECT, INSERT ON test.user TO ‘pig’@‘%’;
GRANT ALL ON . TO ‘pig’@‘%’;
GRANT ALL ON maindataplus.* TO ‘pig’@‘%’;
注意:
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO ‘username’@‘host’ WITH GRANT OPTION;

设置与更改用户密码

命令:SET PASSWORD FOR ‘username’@‘host’ = PASSWORD(‘newpassword’);

如果是当前登陆用户:
SET PASSWORD = PASSWORD(“newpassword”);
例子:
SET PASSWORD FOR ‘pig’@‘%’ = PASSWORD(“123456”);

撤销用户权限

命令:REVOKE privilege ON databasename.tablename FROM ‘username’@‘host’;
说明:
privilege, databasename, tablename：同授权部分

例子:
REVOKE SELECT ON . FROM ‘pig’@‘%’;
注意:
假如你在给用户’pig’@‘%‘授权的时候是这样的（或类似的）：GRANT SELECT ON test.user TO ‘pig’@’%’，则在使用REVOKE SELECT ON . FROM ‘pig’@‘%’;命令并不能撤销该用户对test数据库中user表的SELECT 操作。相反，如果授权使用的是GRANT SELECT ON . TO ‘pig’@‘%’;则REVOKE SELECT ON test.user FROM ‘pig’@‘%’;命令也不能撤销该用户对test数据库中user表的Select权限。
具体信息可以用命令SHOW GRANTS FOR ‘pig’@‘%’; 查看。

删除用户

mysql> drop user ‘yxx’@‘127.0.0.1’;
Query OK, 0 rows affected (0.00 sec)

查看mysql配置信息

show variables;
在这里插入图片描述

最大连接数

mysql的最大连接数默认是100, 最大可以达到16384。
查看最大连接数：show variables like ‘max_connections’;
在这里插入图片描述
设置最大连接数：set global max_connections=200；

存储引擎

1、在MySQL 5.1之前的版本中，默认的存储引擎是MyISAM，从MySQL 5.5之后的版本中，默认的存储引擎变更为InnoDB。
2、MySQL将数据以不同的技术将数据存储在文件或者内存中，这种技术称之为存储引擎
3、MySQL支持的存储引擎主要有以下五种：MyISAM/InnoDB/Memory/CSV/Archive
4、处理方式1：并发控制处理：共享锁（读操作）和排他锁（写操作）
5、锁颗粒：表锁（开销最小锁策略）和行锁（开销最大锁策略）
6、处理方式2：事务：事务用于保证数据库的完整性
事务的特性：原子性、一致性、隔离性和持久性
7、索引和外键均可保证数据的相关性、一致性和特性性
8、不同存储引擎的特点是不一样的
在这里插入图片描述

InnoDB（因noDb）存储引擎

· 特点：行级锁、事务安全（ACID兼容）、支持外键、不支持FULLTEXT类型的索引(5.6.4以后版本开始支持FULLTEXT类型的索引)。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。
· InnoDB是为处理巨大量时拥有最大性能而设计的。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的
InnoDB存储引擎存储限制为64TB，支持事务安全和索引，支持行锁和外键，不支持数据压缩

MyISAM（米撒姆）存储引擎

· 特点：表级锁、不支持事务和全文索引，适合一些CMS内容管理系统作为后台数据库使用，但是使用大并发、重负荷生产系统上，表锁结构的特性就显得力不从心

CSV存储引擎

CSV不支持索引，是一种文本形式的存储方式，黑洞引擎一般写入的数据会消逝，一般用于数据复制过程中的中继

创建引擎

create table t1(
sa varchar(20)
) ENGINE=MyISAM;

修改引擎

（1）在MySQ配置文件里面修改
（2）修改存储引擎方法：
alter table t1 engine=innodb;

查看MySQL支持的引擎

mysql> show engines;

其它

数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右
一次读磁盘IO的时间约等于5+4.17 = 9ms左右，
一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次磁盘IO的时间可以执行40万条指令，
数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

视图

视图是虚拟表，虚拟表中的内容由查询定义。
视图的作用类似于筛选，可以来自当前或其它数据库的一个或多个表，或者其它视图。
视图存储在数据库中的是查询的SQL 语句
视图表中并不保存数据，这些数据在使用视图时才动态生成
视图的行和列来自由定义视图的查询所引用的表
对数据库表的增删改会影响视图，对视图的增删改也会影响数据库

使用视图的原因：
第一安全，可以隐藏掉一些隐私数据；
第二简化查询，可以在视图上查询视图里的列
使用视图的好处：
第一：简化用户操作
例如，定义了若干张表连接的视图，就将表与表之间的连接操作对用户隐藏起来了。换句话说，用户所作的只是对一个虚表的简单查询，而这个虚表是怎样得来的，用户无需了解。
第二：使用户能从多个角度看待同一份数据
第三：对重构数据库提供了一定程度的逻辑独立性
在关许数据库中，数据库的重构造往往是不可避免的。重构数据库最常见的是将一个基本表“垂直”地分成多个基本表。例如：将学生关系Student（Sno，Sname，Ssex，Sage，Sdept），
分为SX（Sno，Sname，Sage）和SY（Sno，Ssex，Sdept）两个关系。这时原表Student为SX表和SY表自然连接的结果。如果建立一个视图Student：

CREATE VIEW Student（Sno，Sname，Ssex，Sage，Sdept）AS SELECT SX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.Sdept FROM SX，SY WHERE SX.Sno=SY.Sno；

 这样尽管数据库的逻辑结构改变了（变为SX和SY两个表了），但应用程序不必修改，因为新建立的视图定义为用户原来的关系，使用户的外模式保持不变，用户的应用程序通过视图仍然能够查找数据。

第四：对机密数据有保护作用

查看权限

SELECT SELECT_priv,create_view_priv from mysql.user WHERE user=‘root’

SELECT_priv：查询的权限
create_view_priv：创建的权限
在这里插入图片描述

创建视图语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

OR REPLACE：如果视图已存在则替换
select_statement是一个查询语句，这个查询语句可从表或其它的视图中查询。
db_name：表示在某个数据库下创建视图，默认是当前数据库
数据库不能包含相同名称的表和视图
示例：

CREATE VIEW V_VIEW2(ID, NAME, SEX, AGE,DEPARTMENT,POS,EXPERENCE) AS SELECT a.ID, a.NAME, a.SEX, a.AGE,a.DEPARTMENT,b.POS,b.EXPERENCE FROM learning.t_employee a,learning.t_employee_detail b WHERE a.ID=b.ID;

查看视图表结构

DESCRIBE V_VIEW2

查看视图状态

show TABLE status LIKE 'V_VIEW2'

查看视图创建语句

show CREATE view V_VIEW2

修改视图

ALTER VIEW  V_VIEW1(ID, NAME) AS SELECT ID, NAME  FROM learning.t_employee;  
SELECT * FROM learning.v_view1

更新视图
在MySQL中，更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据，所以通过视图更新时，都是转换到基本表来更新。
更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

UPDATE V_VIEW2 SET POS='高级工程师' WHERE NAME='天天'

不可更新的视图：
某些视图是可更新的。也就是说，可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含下述结构中的任何一种，那么它就是不可更新的：

· 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
· DISTINCT
· GROUP BY
· HAVING
· UNION或UNION ALL
· 位于选择列表中的子查询
· Join
· FROM子句中的不可更新视图
· WHERE子句中的子查询，引用FROM子句中的表。
· 仅引用文字值（在该情况下，没有要更新的基本表）。
· ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

Red Hat 4.8.5-16 安装 Mysql 5.6.36

1 检查、删除已安装MySQL

# rpm 方式
[root@vultr ~]# rpm -qa | grep -i mysql
1) 如果有rpm包安装的MySQL，使用以下命令删除:
[root@vultr ~]# rpm -e --nodeps 包名
2) 查找之前老版本MySQL的目录，文件并且删除
[root@vultr ~]# find / -name mysql
[root@vultr ~]# rm -rf 目录
[root@vultr ~]# rm -rf /etc/my.cnf   # 需要手动删除my.cnf文件
3) 再次查找机器是否安装了MySQL
[root@vultr ~]# rpm -qa | grep -i mysql

2 检查、删除冲突软件

# mariadb
rpm -qa|grep mariadb
rpm -e --nodeps

3 检查、关闭进程

[root@BJHTYD-Hope-222-39 ~]# ps -ef|grep mysql
root     27147 22596  0 14:47 pts/5    00:00:00 grep --color=auto mysqld
root     35193     1  0 May30 ?        00:01:29 bin/mysqld --user=root

4 安装

[root@BJHTYD-Hope-222-39 weixx]# tar -xvf MySQL-5.6.36-1.linux_glibc2.5.x86_64.rpm-bundle.tar
MySQL-devel-5.6.36-1.linux_glibc2.5.x86_64.rpm
MySQL-server-5.6.36-1.linux_glibc2.5.x86_64.rpm
MySQL-shared-5.6.36-1.linux_glibc2.5.x86_64.rpm
MySQL-shared-compat-5.6.36-1.linux_glibc2.5.x86_64.rpm
MySQL-client-5.6.36-1.linux_glibc2.5.x86_64.rpm
MySQL-test-5.6.36-1.linux_glibc2.5.x86_64.rpm
MySQL-embedded-5.6.36-1.linux_glibc2.5.x86_64.rpm

[root@BJHTYD-Hope-222-39 weixx]# rpm -ivh MySQL*.rpm
warning: MySQL-client-5.6.36-1.linux_glibc2.5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:MySQL-devel-5.6.36-1.linux_glibc2################################# [ 14%]
   2:MySQL-client-5.6.36-1.linux_glibc################################# [ 29%]
   3:MySQL-test-5.6.36-1.linux_glibc2.################################# [ 43%]
   4:MySQL-embedded-5.6.36-1.linux_gli################################# [ 57%]
   5:MySQL-shared-compat-5.6.36-1.linu################################# [ 71%]
   6:MySQL-shared-5.6.36-1.linux_glibc################################# [ 86%]
   7:MySQL-server-5.6.36-1.linux_glibc################################# [100%]
2022-05-31 15:58:18 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-05-31 15:58:18 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2022-05-31 15:58:18 0 [Note] /usr/sbin/mysqld (mysqld 5.6.36) starting as process 32440 ...
2022-05-31 15:58:18 32440 [Note] InnoDB: Using atomics to ref count buffer pool pages
2022-05-31 15:58:18 32440 [Note] InnoDB: The InnoDB memory heap is disabled
2022-05-31 15:58:18 32440 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-05-31 15:58:18 32440 [Note] InnoDB: Memory barrier is not used
2022-05-31 15:58:18 32440 [Note] InnoDB: Compressed tables use zlib 1.2.3
2022-05-31 15:58:18 32440 [Note] InnoDB: Using Linux native AIO
2022-05-31 15:58:18 32440 [Note] InnoDB: Using CPU crc32 instructions
2022-05-31 15:58:18 32440 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2022-05-31 15:58:18 32440 [Note] InnoDB: Completed initialization of buffer pool
2022-05-31 15:58:18 32440 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2022-05-31 15:58:18 32440 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2022-05-31 15:58:18 32440 [Note] InnoDB: Database physically writes the file full: wait...
2022-05-31 15:58:18 32440 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2022-05-31 15:58:18 32440 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2022-05-31 15:58:19 32440 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2022-05-31 15:58:19 32440 [Warning] InnoDB: New log files created, LSN=45781
2022-05-31 15:58:19 32440 [Note] InnoDB: Doublewrite buffer not found: creating new
2022-05-31 15:58:19 32440 [Note] InnoDB: Doublewrite buffer created
2022-05-31 15:58:19 32440 [Note] InnoDB: 128 rollback segment(s) are active.
2022-05-31 15:58:19 32440 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-05-31 15:58:19 32440 [Note] InnoDB: Foreign key constraint system tables created
2022-05-31 15:58:19 32440 [Note] InnoDB: Creating tablespace and datafile system tables.
2022-05-31 15:58:19 32440 [Note] InnoDB: Tablespace and datafile system tables created.
2022-05-31 15:58:19 32440 [Note] InnoDB: Waiting for purge to start
2022-05-31 15:58:19 32440 [Note] InnoDB: 5.6.36 started; log sequence number 0
A random root password has been set. You will find it in '/root/.mysql_secret'.
2022-05-31 15:58:20 32440 [Note] Binlog end
2022-05-31 15:58:20 32440 [Note] InnoDB: FTS optimize thread exiting.
2022-05-31 15:58:20 32440 [Note] InnoDB: Starting shutdown...
2022-05-31 15:58:22 32440 [Note] InnoDB: Shutdown completed; log sequence number 1625977


2022-05-31 15:58:22 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-05-31 15:58:22 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2022-05-31 15:58:22 0 [Note] /usr/sbin/mysqld (mysqld 5.6.36) starting as process 32495 ...
2022-05-31 15:58:22 32495 [Note] InnoDB: Using atomics to ref count buffer pool pages
2022-05-31 15:58:22 32495 [Note] InnoDB: The InnoDB memory heap is disabled
2022-05-31 15:58:22 32495 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-05-31 15:58:22 32495 [Note] InnoDB: Memory barrier is not used
2022-05-31 15:58:22 32495 [Note] InnoDB: Compressed tables use zlib 1.2.3
2022-05-31 15:58:22 32495 [Note] InnoDB: Using Linux native AIO
2022-05-31 15:58:22 32495 [Note] InnoDB: Using CPU crc32 instructions
2022-05-31 15:58:22 32495 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2022-05-31 15:58:22 32495 [Note] InnoDB: Completed initialization of buffer pool
2022-05-31 15:58:22 32495 [Note] InnoDB: Highest supported file format is Barracuda.
2022-05-31 15:58:22 32495 [Note] InnoDB: 128 rollback segment(s) are active.
2022-05-31 15:58:22 32495 [Note] InnoDB: Waiting for purge to start
2022-05-31 15:58:22 32495 [Note] InnoDB: 5.6.36 started; log sequence number 1625977
2022-05-31 15:58:22 32495 [Note] Binlog end
2022-05-31 15:58:22 32495 [Note] InnoDB: FTS optimize thread exiting.
2022-05-31 15:58:22 32495 [Note] InnoDB: Starting shutdown...
2022-05-31 15:58:24 32495 [Note] InnoDB: Shutdown completed; log sequence number 1625987




A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

  /usr/bin/mysql_secure_installation

which will also give you the option of removing the test database.
This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file /usr/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/my-new.cnf,
please compare it with your file and take the changes you need.

# 服务状态
service mysql start
service mysql status
service mysql stop
service mysql restart

# 查看初始密码
[root@BJHTYD-Hope-222-39 weixx]# cat /root/.mysql_secret | grep password
# The random password set for the root user at Tue May 31 15:17:22 2022 (local time): T2tZvqCvajQwhm3A
# 设置密码为123456
mysql -pf6SdIT5Q92X9YCcp -uroot
SET PASSWORD = PASSWORD('123456');    

# 远程登陆用户设置 
mysql -uroot -p123456
use mysql;
select host,user,password from user;
update user set password=password('123456') where user='root';
update user set host='%' where user='root' and host='localhost';
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;

5 附录

# 通过rpm安装的mysql找不到my.cnf解决方法
cd /usr/share/mysql
cp my-default.cnf /etc/my.cnf
# 添加忽略大小写配置
lower_case_table_names=1

# 查看并指定数据存储路径
show variables like '%datadir%';
select @@datadir;

修改 datadir,socket和pid_file三个路径
修改mysql配置文件中的datadir和socket: vim /etc/my.cnf
datadir=/dev/mysqldata/mysql
socket=/dev/mysqldata/mysql/mysql.socket
修改: vim /etc/init.d/mysqld
get_mysql_option datadir "/dev/mysqldata/mysql" mysql
datadir="$result"
get_mysql_option socket "$datadir/mysql.socket" mysql
socketfile="$result"

修改后
mysql> show variables like '%datadir%';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /dev/mysqldata/mysql/ |
+---------------+-----------------------+
1 row in set (0.00 sec)



# 相关路径
a.数据库目录： /var/lib/mysql/
b.配置文件： /usr/share/mysql
c.相关命令： /usr/bin
d.启动脚本： /etc/rc.d/init.d/

# 导入数据
进入mysql
source sqlFile;


# 开启慢sql
set global slow_query_log='ON';
show variables like "slow_query_log";
show variables like '%slow%';


mysql> show variables like '%slow%';
+---------------------------+--------------------------------------------+
| Variable_name             | Value                                      |
+---------------------------+--------------------------------------------+
| log_slow_admin_statements | OFF                                        |
| log_slow_slave_statements | OFF                                        |
| slow_launch_time          | 2                                          |
| slow_query_log            | ON                                         |
| slow_query_log_file       | /var/lib/mysql/BJHTYD-Hope-222-39-slow.log |
+---------------------------+--------------------------------------------+
5 rows in set (0.01 sec)

6 遇到的问题

ERROR 1114 (HY000) : The table 'test1' is full
磁盘空间满了，修改数据存储目录

1.找一个存储空间多的路径：df -h 
2.新建：/dev/mysqldata/
3.拷贝之前数据到新路径下：cp -r  /var/lib/mysql /dev/mysqldata/
4.修改mysql配置文件中的datadir和socket: vim /etc/my.cnf
datadir=/dev/mysqldata/mysql
socket=/dev/mysqldata/mysql/mysql.socket
5.修改: vim /etc/init.d/mysqld
get_mysql_option datadir "/dev/mysqldata/mysql" mysql
datadir="$result"
get_mysql_option socket "$datadir/mysql.socket" mysql
socketfile="$result"
6.重启

ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/tmp/mysql.sock‘ (2)
-- 解决办法
[root@lfjd-dcjdtrcc-11-123-32-197 mysql]# ln -s /dev/mysqldata/mysql/mysql.socket /var/lib/mysql/mysql.sock