mysql高级篇(未完)

已于 2022-05-01 14:39:00 修改
阅读量1.4k 收藏
点赞数 1
分类专栏： mysql复习笔记 文章标签： mysql 数据库
于 2022-04-12 11:22:19 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Badman0726/article/details/124118298
版权

mysql

Linux下的安装

  • 去官网下载安装包地址链接
    在这里插入图片描述

  • 将安装包上传到linux并解压

  • 使用 ll 查看 /tmp是否有较大的权限,如果没有则给其权限

    ll /tmp
chmod -R 777 /tmp

  • 检查是否存在如下依赖包

    rpm -qa | grep libaio
rpm -qa | grep net-tools

    一般安装了图形化界面都会存在

  • 安装mysql

    rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm #mysql5.7没有这个
yum remove mysql-libs #不执行这个的话下面会报错
rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm

  • 查看是否安装成功

    #查看版本号
mysql --version
#检查已安装的包
rpm -qa | grep -i mysql

  • 初始化服务
    为保证数据库目录和文件的所有者为mysql登录用户,如果以root身份运行需执行下面的初始化命名

    mysqld --initialize --user=mysql

    查看生成的初始密码

    cat /car/log/mysqld.log

    在这里插入图片描述

  • 启动mysql

    #查看启动状态
systemctl status mysqld
#启动
systemctl start mysqld
#查看是否开机自启(enable为开机自启)
systemctl list-unit-files | grep mysqld.service
#设置开机自启
systemctl enable mysqld
#进入mysql,密码为初始化密码
mysql -uroot -p 密码
#进入mysql后修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

  • 远程连接数据库
    先关闭linux防火墙或者开放linux的指定端口号

    #关闭防火墙
systemctl stop firewalld
systemctl disable firewalld #永久性关闭防火墙
#开放端口号
firewall-cmd --list-all #查看开放的端口号
firewall-cmd --add-service=http --permanent #添加http服务
firewall-cmd --add-port=3306/tcp --permanent #开放3306端口
#重启防火墙
firewall-cmd --reload

    使用ping命令.看能否ping通linux
    ping通后,在linux中添加远程连接的IP权限

    #使用该数据库
use mysql
#查询所有的IP和用户
select host , user from user;
#修改root用户的IP,不确定的可以使用%代替,这样表示所有都可以连接
update user set host = 'IP' where user = 'root';
#刷新文件后生效
flush privileges;

    用户和权限

    1. 如果只想操作一次数据库，可以这样写
    mysql -h hostname | hostIp - P port -u username -p DatabasesName -e "sql语句"

    -h 主机IP或主机名
    -P 端口号
    -u 用户名
    -p 密码
    DatabaseName 数据库名
    -e sql语句

  • 创建、修改、删除用户(因为相同用户名下不同的host属于不同的用户，所以一般都加host，如果用户名唯一可以不加)

    #创建
create user '用户名' @ '连接权限' identified by '密码';
如：
create user 'sky'@'%' identified by '123456';
create user 'sky'@'localhost' identified by '123456';
#修改
update user set user = '新用户名' where user = '旧用户名' and host = '旧连接权限';
如：
update user set user = 'sky1' where user = 'sky' and host = '%';
#删除
drop user '用户名';
如：
drop user 'sky1';
#增删改后最好刷新一下
FLUSH PRIVILEGES;

  • 用户密码的修改与管理

    #修改当前用户的密码
alter user user() identified by '新密码';
如：
alter user user() identified by '123456';
或者：
set password = '新密码';
如：
set password = '123abc';
#修改其他用户的密码
alter user '用户名'@'连接权限(host)' identified by '新密码';
如
alter user 'sky'@'%' identified by '123abc';
或者
set password for 'sky'@'%'='新密码';
如
set password for 'sky'@'%'='123456789';

  • 权限管理
    （1）授予权限的原则
    ① 只能授予满足需要的最小权限，防止用户干坏事。
    ②创建用户时，可以限制用户的登录主机，一般是限制成指定的IP或者内网IP。
    ③为每个用户设置满足密码复杂度的密码。
    ④定期清理不需要的用户，回收权限或删除用户。
    （2）授予权限

    #在root权限下，或者拥有授权权力的用户下

#给某用户某权限
grant 权限 on 数据库.表 to '用户'@'host';
如：
#给sky用户对db_test下的所有表赋予查和修改的权限
grant select, update on db_test.* to 'sky'@'%';

#给某用户所有权限(不包含给别人授权的权限)
grant all privileges on *.* to '用户名'@'host';

#如果给用户赋予可以授权给别人的权限，需要在后面加上 
WITH GRANT OPTION
#查看权限
SHOW GRANTS;
#查看指定用户的权限
show grants for  '用户名'@'host';

    （3）收回权限

    #给root用户回收权限的权力
grant SYSTEM_USER on *.* to 'root'@'%';
#收回某个用户的某个权限
revoke 权限 on 数据库.表 from '用户名'@'host';
#收回某个用户的所有权限
revoke all privileges on *.* from '用户名'@'host';

    在mysql数据库中有个 名为mysql 的数据库，其中有个表名为 user的表，当使用
    mysql -uroot -p 等命令进行登录的时候，会根据表中的字段值来判断该用户密码host等是否在数据库中存在，如果存在就会登录成功。（其中还有 db表用来判断赋给数据库权限，tables_priv用来判断赋给表权限，columns_priv 用来判断赋给某一列的权限）
    具体表结构可使用 desc 来查看
    5. 角色
    角色就是权限的集合，即可以将一些权限放到这个角色中，当赋予某个用户该角色的时候，该用户便具备了属于该角色的权限。

    #创建角色，一次可以创建多个，用逗号隔开
create role '角色名'@'host';
如：
create role 'manager'@'%';
#赋予角色某些权力
grant 权限 on 数据库.表名 to '角色';
如：
grant select , update on db_test.* to 'manager'@'host';
grant all privileges on *.* to 'boss'@'%';
#查看角色的权限
show grants for '角色名'@'host';
#收回角色的权限
revoke 权限 on db_test.* from '角色名'@'host';
如：
revoke update on db_test.* from 'manager';
#删除某个角色
drop role '角色名';
#将角色赋给某个用户
grant '角色名'@'host' to '用户名'@'host';
如：
grant 'manager'@'%' to 'sky'@'%';
#激活角色
set default role '角色名'@'host' to '用户名'@'host';
如：
set default role 'manager'@'%' to 'sky'@'%';
#查看当前用户下的角色
select current_role();
#创建的用户在表中默认为未被激活，可以设置全局变量让其自动激活
SET GLOBAL activate_all_roles_on_login=ON;
#移除用户的角色
revoke '角色名'@'%' from '用户名'@'%';

    存储引擎

    存储引擎就是指表的类型,存储引擎之前叫表处理器,后改名为存储引擎.它的功能是接受上层传下来的指令,然后对表中的数据进行提取或者写入操作。

    #查看数据库中的引擎
show engines;
#查看默认引擎
select @@default_storage_engine;
#修改默认引擎
set default_storage_engine=引擎名;
如：
set default_storage_engine=MyISAM;
#查看构建的表
show create table 表名;

    常用引擎
    （1） InnoDB引擎（具备外键支持功能的事务存储引擎）
    ① mysql5.5以后默认采用InnoDB引擎
    ②InnoDB是MySQL的默认事务型引擎，它被用来处理大量的短期事务。可以确保事务的完整提交和回滚
    ③除了增加和查询外，还需要更新、删除操作。就优先选择InnoDB.
    ④数据文件结构：表名.frm 存储表的结构，表名.ibd存储数据和索引
    ⑤InnoDB是为处理巨大数据量的最大性能设计。
    ⑥相比较MyISAM，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引
    ⑦MyISAM只缓存索引不缓存真实数据，InnoDB不仅缓存索引还缓存真实数据，对内存要求较高。
    （2）MyISAM引擎（主要的非事务处理存储引擎）
    ①MyISAM提供了大量的特性，包括全文索引、压缩、空间函数等，但不支持事务、行级锁、外键。崩溃后无法安全恢复
    ②mysql5.5之前的默认存储引擎
    ③访问速度快、对事务完整性没有要求或者以SELECT、INSERT 为主的应用
    ④针对数据统计有额外的常数存储。因此cout(*)的查询效率很高
    ⑤数据文件结构：表名.frm 存储表结构 、表名.MYD 存储数据 表名.MYI存储索引
    ⑥主要应用于 只读或者以读为主的业务

    索引

    • 索引，就是一种排好序的快速查找的数据结构。
    • 索引是在存储引擎中实现的，每种存储引擎的索引不一定完全相同，且每种存储引擎不一定支持所有索引类型。
    • 优点：提高了数据检索效率、降低数据库的IO成本、通过常见唯一索引来保证数据库表中每一行数据的唯一性
    • 缺点：创建和维护索引耗费时间、占磁盘空间、降低了更新表的速度

    索引分类：
    MySQL索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引、空间索引等

    • 从功能逻辑来看，分为 普通索引、唯一索引、主键索引、全文索引
    • 从物理实现方式来看，分为 聚簇索引和非聚簇索引
    • 从作用字段个数来看 ，分为 单列索引和联合索引
    #创建索引
#隐式创建
即在声明主键约束、唯一性约束、外键约束的字段上时，会自动添加相关的索引
如：
CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);

CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT employee_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);
# 显示创建
INDEX 索引名(索引字段)
或
#联合索引
INDEX 索引名(索引字段,索引字段...)
如：
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT employee_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id),
INDEX ind_empname(emp_name)
);
#查看索引
show create table 表名;
或
show index from 表名 ;
#在创建表后添加索引
alter table 表名 add index 索引名(字段名)
或
create index 索引名 on 表名(字段名)
#删除索引
ALTER TABLE 表名 DROP INDEX 索引名
如:
ALTER TABLE emp DROP INDEX ind_empname
或
DROP INDEX 索引名 ON 表名
如：
DROP INDEX emp_name ON emp
#隐藏索引
如果一个索引被设置为隐藏索引，该索引就会不起作用，
可通过将一个索引设置为隐藏索引来判断该索引有没有用(即逻辑删除)
#添加隐藏索引
在创建索引的时候，在其后面加上 invisible 即可
如：
INDEX 索引名(字段名) invisible;
#修改索引的可见性 , invisible为不可见，visible为可见
ALTER TABLE 表名 ALTER INDEX 索引名 invisible/visible

适合创建索引的情况：

  • 字段的数值有唯一性的限制
  • 频繁作为WHERE查询条件的字段
  • 经常GROUP　BY　和　ORDER　BY　的列
  • UPDATE、DELETE　的　WHERE　条件列
  • 使用DISTINCT去重时
  • 多表JOIN连接操作时，连接的字段可以加上索引
  • 使用列的类型小的，如 TINYINT、SMALLINT　等
  • 使用字符串前缀创建索引
  • 区分度高、散列性高的列适合作为索引
  • 使用最频繁的列放到联合索引左侧
  • 多个字段都要创建索引的时候，联合索引优于单值索引

不适合创建索引的情况：

  • 在where中使用不到的字段
  • 数据量小的表
  • 有大量重复数据的列
  • 避免对经常更新的表创建过多的索引
  • 不建议用无序的值作为索引
  • 删除不再使用或者很少使用的索引
  • 不要定义冗余或重复的索引

数据库设计规范

范式

  1. 简介：在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。
  2. 包括哪些：目前关系型数据库有六种常见范式，按照级别来从高到低为：第一范式、第二范式、第三范式、巴斯-科德范式(BCNF)、第四范式、第五范式(又叫完美范式).范式设计越高阶冗余度就越低，满足最低要求的范式是第一范式(1NF)，一般来说，最多也就遵循到BCNF,普遍是第三范式，有时候为了提高性能，还需要破坏范式规则，即反规范化。
    在这里插入图片描述
  3. 键和属性
    ① 超键：唯一标识元组(即一行数据)的属性集
    ②候选键：如果超键不包括多余的属性，这个超键就是候选键
    ③主键：从候选键中选择一个作为主键
    ④外键：如果数据表A中的某属性集不是A的主键而是另一个数据表B中的主键，则这个属性集就是数据表 A 的外键
    ⑤主属性：候选键中的属性
    ⑥非主属性：除了主属性外的属性
  4. 第一范式 ： 主要确保数据表中每个字段的值都必须具有原子性，也就是不可再次拆分的。
  5. 第二范式： 在第一范式的基础上，满足数据表里的每一条数据记录都是可以唯一标识的，且所有非主键字段都必须完全依赖主键，不能只依赖主键的一部分。
  6. 第三范式：在第二范式的基础上，确保表中的每一个非主键字段和主键字段直接相关，也就是说要求数据表中的所有非主键字段不能依赖于其他非主键字段
  7. 反范式化：增加数据冗余，提高查询效率(就是不按照范式规则来)
  8. 巴斯范式(BCNF)：在第三范式的基础上进行改进，BCNF没有加入新的设计规范，只是加强了设计要求，使数据冗余度更小，可以说是修正的第三范式(若一个关系达到了第三范式，且只有一个候选键，或者候选键都是单属性，则该关系就达到了BC范式)
  9. 第四范式：在满足BC范式的基础上消除了非平凡(非平凡多值依赖就是一个主键对应一个非主键的多个值)且非函数依赖的多值依赖
  10. 第五范式，域见范式：知道就行

数据表

  1. 数据表的个数越少越好
  2. 数据表中的字段个数越少越好
  3. 数据表中联合主键的字段个数越少越好
  4. 使用主键和外键越多越好

事务

四大特性: ACID
A ： 原子性(atomicity)：指事务是不可分割的工作单位，在一个事务中的操作(如：增删改查等)，要么全成功，要么全失败。
C：一致性(consistency)：指事务执行前后，数据从一个合法性状态转化为另一个合法性状态,合法状态就是自己制定的约束,满足这个状态数据就是一致的,否则就是不一致,如果事务中的一个操作执行失败,系统就会撤销当前的整个事务返回到操作这个事务之前的状态.(如:A给B转钱的时候,规定金额为负数就是不合法的,所以当转账的金额超过自己的余额为负的时候,就是不合法的,就不会成功)
I ：隔离性(isolation) ：指一个事务执行时，不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不会互相干扰
D：持久性(durability)：指事务一旦被提交，对数据库中数据的改变是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

sky~
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MYsql高级进阶篇哦
11-10
MYsql高级进阶篇哦
mysql库host用%_浅谈Mysql连接数据库时host和user的匹配规则
weixin_36197581的博客
01-19 1247
--连接数据库时，host和user的匹配规则--host和user的匹配规则如下：--是host为明确的最先匹配，host带%模糊的时候最后匹配，但host为''(空)位于%之后才匹配--相同的host时候，比较user为明确的最先匹配，user为''(空)最后匹配--相同的host和user时，排序是不确定的When multiple matches are possible, the ser...
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
尚硅谷MySQL高级_笔记
10-05
尚硅谷MySQL高级的视频学习笔记，由本人整理
mysql高级笔记.zip
11-07
mysql
MySQL高级.zip
08-10
主键自动建立唯一索引 频繁作为查询条件的字段应该建立索引 查询中与其他表关联的字段，外键关系建立索引 频繁更新的字段不适合创建索引 -- 因为每次更新不单单是更新了记录还会更新索引 Where条件里用不到的字段不...
【MySQL精通之路】管理和实用程序
Anakki的博客
05-19 177
8 mysql_migrate_keyring——keyring密钥迁移实用程序。2 innocchecksum——离线InnoDB文件校验和实用程序。7 mysql_config_editor——mysql配置工具。9 mysqlbinlog——用于处理二进制日志文件的实用程序。6 myisampack——生成压缩的只读MyISAM表。4 myisamchk——MyISAM表格维护实用程序。5 myisamlog——显示MyISAM日志文件内容。3 myisam_ftdump——显示全文索引信息。
MySQL--增、删、改、查，
weixin_65476290的博客
05-15 994
数据库的概述、发展、现状、历史、分类MySQL关系型数据库、架构（C/S）window系统安装MySQL数据库Linux系统【选学】数据库对象——数据库（database）show、create、drop命令数据库对象——表（table）数据类型数据库表的约束表结构的调整（alter）数据库授权和权限管理（grant）数据库的编码情况【编码一致，建议统一采用utf8mb4】
mysql高版本导入低版本Unknown collation: utf8mb4_0900_ai_ci
susu1083018911的博客
05-15 180
mysql高版本导入低版本Unknown collation: utf8mb4_0900_ai_ci
js遇到需要正则匹配来修改img标签+清除行内样式
阿乐的博客
05-14 330
遇到特殊的情况就是自带样式，那么方法一就不能用了，因为只会算是从原有字符串上面多加字符串。这个正则表达式就是匹配所有的。//踩坑完毕，可以直接使用。第二行代码按自己需要改改。
一条sql语句在mysql中是怎么执行的
m0_59925573的博客
05-17 658
连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。
【Golang】 MySQL 在 Go 语言驱动程序中常用参数
tr6666的博客
05-15 322
在 Go 语言中，我们使用数据库驱动程序来连接和操作数据库。对于 MySQL，我们通常使用 go-sql-driver/mysql 驱动程序。在使用这个驱动程序时，我们需要提供一个数据源名称（DSN），它是一个包含数据库连接信息的字符串。在这个字符串中，我们可以设置很多参数来控制驱动程序的行为。本文将介绍一些常用的参数。在 MySQL 的 Go 语言驱动程序中，我们可以通过设置 DSN 中的参数来控制驱动程序的行为。这些参数可以影响到连接的建立，数据的读写，以及数据的解析等各个方面。
Mysql利用AI生成问题完成目标
最新发布
kkaagf的博客
05-20 273
帮我创建2个表student与score表，要求student表有id,createDate,userName,phone,age,sex,introduce，帮我创建一个修改score表scoreName的触发器，当修改scoreName的时候触发，判断修改的scoreName是否是数学，如果是数学就改成(天书)。帮我根据两个DDL创建一个插入信息的存储过程，先插入10条学生表信息，再根据插入的学生表信息插入100条对应studentId的score成绩，所有信息随机生成，学生表的自增序号从6开始。
Mysql-事务
2302_77099705的博客
05-17 331
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。例如银行转账。默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
MySQL-InnoDB数据存储结构
我姓谭却弹不死你的博客
05-13 878
MySQL-InnoDB数据存储结构
数据库基础MySql
2301_78085386的博客
05-14 763
●数据(结构)定义语言DDL(Data Definition Language)，是用于创 建和修改数据库表结构的语言。●常用的语句：create ,alter,drop,rename。
Mysqls事物基本概念
u012014505的博客
05-14 286
MVCC机制通过在每行数据表记录的后面保存两个隐藏的列来实现的，一个列用来保存行的创建版本号，另外一个保存行的过期版本号，每当有新的事务执行时，版本号就会自动递增，事务开始时刻的版本号作为事务的版本号，用于和查询到的每行记录的版本号作对比。本质上，MVCC机制保存了数据库中数据在某个时间点上的数据快照，意味着同个读操作的事务，按照相同的条件查询数据，无论查询多少次，结果都是一样的，也意味着不同的事务在同一个时刻看到同一个表的数据可能是不同的。InnoDB中的MVCC原理。
MYSQL库管理---1.默认系统库 2.创建/删除/查看库
睡不醒的每天
05-14 210
2、其对象都保存在内存中，在磁盘上找不到对应的物理存在。用户也无法在该库下创建对象，只能。
【MySQL精通之路】存储引擎-配置
Anakki的博客
05-19 397
例如，在主从复制中，也许源服务器使用InnoDB表是为了最大限度地提高安全性，但副本服务器使用其他存储引擎是为了提高速度，而牺牲了持久性或并发性。单个存储引擎会为其管理的表创建所需的任何其他文件。或匹配但是停用的存储引擎，MySQL将使用默认存储引擎创建一个表。如果所需的引擎不可用，此设置将产生错误而不是警告，并且不会创建或更改表。您可以使用--default-storage-engine指定默认引擎，也可以在。如果省略ENGINE选项，则使用默认存储引擎。中设置默认存储引擎选项。无法使用默认存储引擎，
宋红康mysql高级篇课件
08-01
宋红康MySQL高级篇课件是一套以MySQL数据库为主题的高级教学材料。本课件由宋红康编写，旨在提供深入理解和学习MySQL高级特性的知识。下面是对该课件的简介： 该课件分为多个章节，内容涵盖了MySQL的高级应用和技巧...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值