linux系统中mysql索引、DCL和DQL

索引

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