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相同的时候,执行顺序是由上往下的