linux系统中mysql索引、DCL和DQL

发布时间:2023年12月21日

索引

index(key):索引作用优化查询速度
索引是一种特殊的文件(InnoDB存储引擎数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。通常情况下,主键索引(聚簇索引)查询只会查一次,而非主键索引(非聚簇索引)需要回表查询多次。

普通索引:即一个索引只包含单个列,一个表可以有多个单列索引
唯一索引:索引列的值必须唯一,但允许有空值
复合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并
聚簇索引(也叫聚集索引、主键索引):并不是一种单独的索引类型,而是一种数据存储方式。具体细节取决于不同的实现,InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和 数据行。
非聚簇索引:不是聚簇索引,就是非聚簇索引

从存储结构:BTree索引(B-Tree或B+Tree索引),Hash索引,full-index全文索引
从应用层次:普通索引,唯一索引,复合索引。
从数据的物理顺序与键值的逻辑(索引)顺序关系: 聚簇索引,非聚簇索引。


index 索引名称(索引字段)    //建表的时候独立一行声明,主键自带索引
例:create table 表名(ip char(150),index 索引名称(索引字段));


create index 索引名称 on 表名(索引字段);	//单独创建索引
drop index 索引名称 on 表名;		 //删除index索引
alter table 表名 drop index 索引名称;   //删除index索引

DQL查询

单条件查询

select * from 表名;
select * from 表名 where 条件     //查询某个表中的符合条件的数据,条件:等于、小于、大于、小于等于、大于等于

explain select * from 表名 where 字段="xxx"   //查看查询这个字段时候的解释

is null

select * from 表名 where 字段名 is null;    //字段内容为空的数据
select * from 表名 where 字段名 is not null; //字段内容不为空的数据
select * from 表名 where 字段名='';    //字段内容为无的数据


NULL说明:
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作,结果仍为空。
4、比较时使用关键字用“is null”和“is not null”。
5、排序时比其他数据都小,所以NULL值总是排在最前。

between and

select * from 表名 where 字段 between 数值1 and 数值2;    //查询两个数值之间的数据
select * from 表名 where 字段 not between 数值1 and 数值2; //查询不在两个数值之间的数据

多条件查询

select * from 表名 where 条件一 and 条件二;     //多条件查询

定义显示格式

select concat(字段名,'连接语句', 字段名) from 表名;   //连接显示两个字段

避免重复

select distinct 字段名 from 表名;    //查询时避免重复

查询时重命名

select 字段 as 重命名字段名 from 表名    //查询时显示重命名字段名

四则运算

select 显示字段 字段名*14 FROM 表名;     //字段内容*14显示

in集合查询

select 显示字段 from 表名 where 条件1=4000 or 条件2=5000;     //查询满足条件一和条件二数据
	
select 显示字段 from 表名 where 条件 in (4000,5000,6000,9000);   //查询满足条件的数据

select 显示字段 from 表名 where 条件 not in (4000,5000,6000,9000);//查询不满足条件的数据

排序查询

select 显示字符 from 表名 order by 排序字符;  //查询后升序显示数据

select 显示字符 from 表名 order by 排序字符 desc;  //查询后降序显示数据

select * from 表名 order by 排序1 desc,排序2 desc;   //查询后先按降序显示排序1,在按降序显示排序二

select * from 表名 order by 排序字符 desc limit 5;	//限制查询五条数据,从0开始
select * from 表名 order by 排序字符 desc limit 2,5;	//限制查询五条数据,从2开始

desc降序
asc升序,默认为升序

分组查询

group by和group_concat()函数一起使用

select 显示字段,group_concat(按照字段纳入分组) from 表名 group by 需要分组的字段;

模糊查询

%  所有字符

select * from 表名 where 字段名 like '%20%';   //匹配字段名中前方所有20后方所有的数据

正则查询

select * from 表名 where 字段名 regexp '72+';    //+前导符,前面的数字出现1次-无数次

select * from 表名 where 字段名 regexp '^ali';   //^行首定位符

select * from 表名 where 字段名 regexp 'yun$';   //$行尾定位符

select * from 表名 where 字段名 regexp 'm{1,8}';    //m字符出现1次到8次

函数

count()    //显示多少条数据
例:select count(*) from 表名;    //查看表中有多少条数据

max()      //最大值
例:select 显示字段 from 表名 where 筛选最大值的字段=(select max(取最大值的字段) from 表名);       //拿取某个字段最大的数值并和其他字段一起显示

min()      //最小值
例:select 显示字段 from 表名 where 筛选最小值的字段=(select min(取最小值的字段) from 表名);       //拿取某个字段最小的数值并和其他字段一起显示

avg()      //平均值
例:select avg(字段) from 表名;     //查看表中某个字段的平均值

sum()      //总和
例:select sum(字段) from 表名;     //查看表中某个字段的总和

database() //数据库
例:select database();   //查看当前所在库

user()     //用户
例:select user();    //查看当前登录用户

now()      //时间
例:select now();    //查看当前时间

password() //密码

DCL控制

安全控制

确保MySQL运行用户为一般用户
groupadd mysql && useradd -M -s /sbin/nologin -g mysql mysql //创建mysql用户和组

vim /etc/my.cnf     //mysql的配置文件
user = mysql

注意点:改变拥有者和所属组对于mysql的安装目录
修改默认端口
vim /etc/my.cnf
port = 3306 false     //修改mysql默认端口 
port = 10086 true
开启mysql二进制日志
在误删除数据的情况下,可以通过二进制日志恢复到某个时间点

vim /etc/my.cnf
log_bin = othername   //开启mysql二进制日志
删除空口令账号
vim /etc/my.cnf       
skip-grant-tables = 1. --改成 "#skip-grant-tables = 1"  //禁用匿名账号

//删除空口令用户
mysql> SELECT User,Host,authentication_string from mysql.user;

mysql> drop user 'newrain'@'localhost';	      //这是删除空口令账户
禁止root账户远程访问(允许某个网段访问)
mysql> create user 'root'@'10.0.11.%' identified by "123";
遇到的问题

MySQL: ERROR 1040: Too many connections

使用mysql的时候,经常会遇到上述问题,
一种原因是访问量确实很高, MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力,
另外一种原因是MySQL配置文件中max_connections值过小, 这时就需要调整当前最大连接数

修改mysql配置文件my.cnf,在[mysqld]段中添加或修改max_connections值:
vim /etc/my.cnf  
max_connections=256    //设置最大连接数

权限

授予权限
grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by '密码' with_option参数];

权限列表
all:所有权限(不包括授权权限)dml dql dcl ddl
select:查询
update:更新
delete:删除
					
				
数据库.表名
*.*:所有库下所有表
web.*:web库下所有表
web.stu:web库喜爱stu表
赋予某个用户某个字段权限
				
客户端主机
%:所有主机
192.168.10.%:192.168.10.0这个网段的所有主机
192.168.10.250:指定主机
localhost:指定主机

flush privileges;  //刷新权限,使用设置生效
查看权限
查看自己的权限:show grants;

查看别人的权限:show grants for 用户名@'%';
回收权限
revoke 权限列表 on 数据库名 from 用户名@'客户端主机';
版本问题
删除用户时
5.6之前,先revoke all privilege 在drop user
5.7之后,drop user

授权时
8.0版本授权时候,先创建需要授权的用户
5.7版本授权时候,不需要创建用户,在授权时候会自动创建用户

授权时候的%
5.7版本之前,%不包含localhost
5.7版本之后,%包含localhost
文章来源:https://blog.csdn.net/qq_59207739/article/details/135104653
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。