Linux下的MySQL

最新推荐文章于 2024-05-06 10:23:39 发布

南山程序猿

最新推荐文章于 2024-05-06 10:23:39 发布

阅读量1.3k

点赞数 1

分类专栏： MySQL 文章标签： linux

本文链接：https://blog.csdn.net/palpitate_TAN/article/details/121861392

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL

1、排序查询

特点：

1、用order by进行排序查询，其默认为升序
2、order by 子句可以支持单个字段、多个字段、表达式、函数、别名
3、order by 子句一般都是放在查询语句之后，limit子句除外

#查询部门编号大于等于90的员工信息，an入职时间的先后进行排序
select * from employees where department_id >=90 ORDER BY hiredate ASC;
#按年薪的高低显示员工的信息
select salary*12*(1+IFNULL(commission_pct,0)) 年薪 from employees ORDER BY 年薪 DESC;
#查询员工信息，先按工资排序，再按员工编号排序
select * from employees ORDER BY salary DESC,employee_id asc;

2、函数

1、拼接字符串（concat）
select CONCAT(last_name,'_',first_name) 姓名 from employees;

#2、判断字节（中文三个字节，英文一个，如果是GBK编码，中文为俩个字节）长度length
select LENGTH(last_name) 姓长度 from employees;

#3、设置大小写 UPPER('tan')设置字符为大写，LOWER('SONG')设置字符为小写
select UPPER('tan') 大写,LOWER('SONG') 小写,LOWER('CHANG') 小写; 
#将姓变大写，名边小写,然后拼接
select CONCAT(UPPER(last_name),'+',LOWER(first_name)) 姓名 from employees;

#4、SUBSTR(str FROM pos FOR len) 注：索引从1开始
#截取从指定索引处指定字符长度的字符
select SUBSTR('细节源于生活',1,2) 截取;
#截取从指定索引处后面所有字符
select SUBSTR('细节源于生活',2) 截取;
#员工表中姓名中首字符大写，其他字符小写，然后用_拼接
select CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER((SUBSTR(last_name,2)))) from employees;

#5、instr 返回字符串中某一个值第一次出现的索引位置，如果没有则返回零
SELECT INSTR('马尔斯勇士奖 from team seven 蒋小鱼','蒋小鱼') as 索引值;

#6、trim 去掉前后空格or指定字符
SELECT LENGTH(TRIM('     赵子武    '));
SELECT TRIM('d' FROM 'dddddddd快上车dddddddd') as 去d;

#7、用指定的字符实现左or右填充指定长度（RPAD(str,len,padstr)）
SELECT lpad('蒋小鱼',6,'沈鸽与') as 填充;

#8、替换replace
select REPLACE('沈鸽爱上蒋小鱼','蒋小鱼','文章') as 替换;

2、数学函数

#1、round 四舍五入(先按绝对值进行求值，然后再赋值)
select ROUND(-1.55);
#2、ceil 向上取整数，返回<=该参数的最大整数
SELECT CEIL(-2.58);(该值取整不分正负号)
#floor向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99); 
#truncate 截断
select TRUNCATE(16.9999,3);
#mod取余
select mod(10,-5);
select 10%3;

3、Linux上删除、安装MySQL

1）首先查看是否安装过MySQL

rpm -qa | grep -i mysql
使用删除命令，有时候rm -rf 不会删除所有记录，再使用该语句进行彻底删除残存文件
sudo rpm -e MySQL-client-5.5.48-1.linux2.6.x86_64

2）没有消息才是好消息

在这里插入图片描述

这个错误原因是安装了旧版本的GPG keys造成的，在命令后边加上

--nodeps --force

在这里插入图片描述

3）MySQL设置开机自启动

注意：进入到安装MySQL的前一个目录下即可
1 vim /etc/rc.local
2 添加service mysqld start

4）Windows中进入到mysql命令行

1、以管理员的身份启动
在这里插入图片描述

2、找到mysql.exe启动程序所在包，并且通过命令行进入

在这里插入图片描述

3）查看创建的数据库文件保存在哪里

在这里插入图片描述

4）Linux远程登录Navicat
在这里插入图片描述

1，查看防火墙状态
systemctl status firewalld.service、简略firewall-cmd --state
2，开启防火墙
systemctl start firewalld.service
3，关闭防火墙
systemctl stop firewalld.service
4，禁用防火墙、开机关闭
systemctl disable firewalld.service
5,查看开放端口号
firewall-cmd --zone=public --list-ports
6，开放3306端口号
firewall-cmd --zone=public --add-port=3306/tcp --permanent
7，防火墙开机自动开启
systemctl encble firewalld.service

安装步骤;

//yum上下载
1、wget https://dev.mysql.com/get/mysql80-community-release-el8-1.noarc	
2、yum install mysql80-community-release-el8-1.noarch.rpm
4，yum repolist enabled | grep "mysql.*-community.*"
5，yum module disable mysql
6，yum install mysql-community-server
7，/bin/systemctl start mysqld.service
   service mysqld status  
8，grep 'temporary password' /var/log/mysqld.log //查看初始密码root@localhost:后面的所有字符都是密码root@localhost: pIyidcUu4l?;
9,mysql -u root -p //登录
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root_21root';
mysql> set global validate_password.length=1;
mysql> set global validate_password.policy=0;//更改密码长度
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '密码';//设置root账户密码
mysql> use mysql;
mysql> select user,plugin from user where user='root';
mysql> update user set host = '%' where user ='root';
mysql> flush privileges;
mysql> select host from user where user='root';
mysql> GRANT ALL ON *.* TO 'root'@'%';
mysql> flush privileges;
[root@LinuxSong MySQL]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@LinuxSong MySQL]#  firewall-cmd --reload
success

删除MySQL
yum remove -y mysql
//找到残存MySQL文件，然后在通过rm -rf进行一一删除
find / -name mysql

4、MySQL进阶

1）主从复制

1、主要配置文件

1）二进制日志log-bin=mysql-bin

2）错误日志log-error（默认关闭）

3）查询日志log（默认关闭，开启会降低mysql整体性能，记录日志需要消耗系统资源）

2、数据文件

1）俩系统：Windows下用于data存储数据库文件

2）frm文件：存放数据表结构

3）myd文件：存放表数据

4）myi文件：存放表索引

2）存储引擎

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁：即使操作一条记录也会锁住整个表，不适合高并发	行锁：操作时只锁某一行，不对其它行有影响，适合高并发
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求高，且内存大小对性能有决定性影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

3）JOIN编写、SQL性能下降

索引：底层进行排序

1、SQL性能下降

原因：查询语句烂、索引（单值、复合）失效、关联查询太多join（执行时间长）、服务器调优及各个参数设置（缓冲、线程数等）
SQL执行顺序
-

2、JOIN七种SQL编写*

左外连接：代表左表拥有全部数据，并且和右边表的全部匹配行
```
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
```

在这里插入图片描述

右外连接：代表右表拥有全部数据，并且和右边表的全部匹配行

select * from tbl_emp a right join tbl_dept b on a.deptIdeptId = b.id;

在这里插入图片描述

内连接：代表只连接俩表之间的公有数据

select * from tbl_emp a inner join tbl_dept b on a.deptIdeptId = b.id;

在这里插入图片描述

右连接：只显示右表独有数据，不显示公有数据，且左表数据为空，右外连接得到了右表，但是右表里还包含了一部分左右表都具有相同数据的区域，这时需要将这部分相同数据去掉，去除的条件就是A.key IS NULL

select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;

在这里插入图片描述

左连接：只显示左表独有数据，就是在左外连接的基础上进行的，左外连接得到了左表，但是左表里还包含了一部分左右表都具有相同数据的区域，这时需要将这部分相同数据去掉，去除的条件就是B.key IS NULL

select * from tbl_emp a left join tbl_dept b on a.depteptId = b.id where b.id is null;

在这里插入图片描述

全连接：显示所有数据，且MySQL不支持FULL OUTER JOIN写法，所以使用UNION

select  * from tbl_emp a left join tbl_dept b on a.ddeptId = b.id union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

在这里插入图片描述

全外连接：表与表之间的公有数据不显示，只显示表之间的独有数据

select  * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null union select * from tbl_emmp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;

在这里插入图片描述

4）、索引

1、索引是什么

**定义：**索引是帮助MySQL高效获取数据的数据结构，可以得到索引的本质（排好序的快速查找数据结构）

**目的：**提高查询效率

功能：查找快、排好序

**结论：**数据本身之外，数据库还维护这一行满足特定查找算法的数据结构，一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

**优势：**提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序成本，降低CPU的消耗（主要反应于硬件层次）

劣势：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是要占用空间
虽然索引大大提高了数据查询速度，同时却会降低更新表的速度，如对表进行添加、删除、和修改。因为更新表时，MySQL不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段
索引只是提高效率的一个因素，如果MySQL有大数据量的表，还需花费大量的时间建立最优秀的索引，或者优化查询

2、索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引（一张表建索引不超过五个）
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列
基本语法：
- 创建索引：CREATE [UNIQUE] INDEX indexName(索引名) ON (表名)mytable((列名)columnname(length));
  
  ALTER (表名)mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
- 删除索引：DROP INDEX [indexName] ON mytable;
- 查看索引：SHOW INDEX FROM table_name
- 使用ALTER命令添加索引的四种方式：
  - 主键索引：ALTER TABLE (表名)tbl_name ADD PRIMARY KEY （(列名)columnname_list）；该句添加一个主键，意味索引值必须唯一，且不能为NULL；
  - 唯一索引：ALTER TABLE (表名)tbl_name ADD UNIQUE (索引名)index_name（(列名)columnname_list）；giant语句创建的索引值必须是唯一的(除了NULL外，NULL可能会出现多次)
  - 普通索引：ALTER TABLE (表名)tbl_name ADD INDEX (索引名)index_name（(列名)columnname_list）；添加普通索引，索引值可出现多次
  - 全文索引：ALTER TABLE (表名)tbl_name ADD FULLTEXT(索引名)index_name（(列名)columnname_list）；该语句指定了索引为FULLTEXT，用于全文索引

3、索引结构

BTree索引–>检索原理：
哪些情况适合创建索引：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引
- 单键or组合索引选择问题，建议在高并发情况下创建组合索引
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度（索引与group by排序息息相关）
- 查询中统计或者分组的字段
哪些情况不适合建立索引：
- 表记录太少
- 经常增、删、改的表
- 频繁更新的字段不适合创建索引，因为每次更新不仅是更新了记录，同时还回更行索引值，造成索引紊乱
- Where条件里用不到的字段不创建索引
- 数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据建立索引。注意：如果某个数据列包含许多重复内容，建立索引就没有太大的实际效果。（公式：表中不同数据内容的数量除以总数，算出来的值越接近1，那么它建立索引查询的效率就更高）
性能分析
MySQL Query Optimizer（查询优化分析器）
MySQL常见瓶颈：
- CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态
Explain：
- 怎么玩：1、Explain+SQL语句，如：explain select * from tbl_dept;
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LV0iZ7Rf-1639128447861)(C:\Users\Dell7591\AppData\Roaming\Typora\typora-user-images\image-20211210154747597.png)]
名词字段解释：
- id：ID相同的时候，执行顺序是由上往下的