枯枯燥燥mysql

一 .  第一天内容

1.相关参数（必须熟知 ） 

MySQL服务初始密码目录   /var/log/mysqld.log

主配置文件  /etc/my.cnf    

数据库目录   /var/lib/mysql

服务的进程名/端口   mysqld/3306

进程所有者和所属组    mysql/mysql 

数据传输协议    tcp 

错误日志文件名 /var/log/mysqld.log

数据库服务默认的检索目录  /var/lib/mysql-files/

mysql索引目录: /var/lib/mysql/库/表.ibd

修改数据库服务默认的检索目录: 在主配置文件下添加  secure_file_priv='目录位置'

2.连接服务

mysql -h(IP地址) -uroot  -p '密码'

3.修改密码的方法:

线下破解:

    1. 修改配置文件:vim /etc/my.cnf

             [mysqld]

             # mysql策略

     #        validate_password_policy=0 

     #      validate_password_length=6 

      添加命令 skip-grant-tables

          2.   重启服务  直接登陆即可

          3.  修改root密码    

                update  mysql.user set authentication_string=password("密码")   where user="root" and host="localhost";  

         4.保存修改 flush privileges; 

         5.  断开连接    还原修改  把添加的字段注释即可

线上破解:

  1.   把目标主机的 /var/lib/mysql/mysql  文件拷贝到  本地的主配置目录下

  2. 查看mysqld进程 

pstree -p | grep mysqld | head  -1

  3.  杀死进程 后 直接进即可

 kill  -SIGHUP   '进程号'

 4.修改root密码    

mysqladmin  -uroot    -p旧密码   password  新密码

必备命令:

select   version();  查看服务版本

select   user();   查看登录用户

select  database();  查看当前所在库

show   databases;  查看已有库

use  库名;   切换库     

show  tables;   查看已有表

desc  表名;     查看表结构

show index   from   库.表；   查看索引详细信息 

查询命令格式

select  字段名列表  from  库名.表名； #查看表里的所有行

select  字段名列表  from  库名.表名  where   查询条件 ； #只查看与条件匹配的行

select  字段名  as  '更改的名称';  # 定义别名  as

数据拼接   concat()      不显示重复     distinct 

数值比较 符号   =      !=       >       >=       <       <= 

              相等    不相等   大于   大于等于    小于    小于等于

说明符号两边要是数字或数字类型的表头  用符号左边的和右边的做比较

空       is  null      表头下没有数据

非空       is  not null  表头下有数据 

模糊匹配条件   where  字段名   like  ‘表达式’

  统配符号  

  _    表示 1个字符

  %  表示零个或多个字符

正则匹配   

字段名 regexp  '正则表达式'

 ^ 匹配行首       $ 匹配行尾     [] 匹配范围内任意一个

*  前边的表达式出现零次或多      |  或者

逻辑匹配  就是有多个判断条件

逻辑与   and  &&        多个判断条件必须同时成立

逻辑或   or   ||        多个判断条件其中某个条件成立即可

逻辑非   not  !         取反

逻辑与and  的优先级要高于逻辑或 or 

二.    第二天内容

库名是有命名规则？

仅可以使用数字、字母、下划线、不能纯数字

区分字母大小写，

具有唯一性

不可使用指令关键字、特殊字符

建库的基本命令

create  database  库名； 

删库命令

drop   if  exists  database  库名;

导库命令:

mysql -h(IP地址) -uroot  -p '密码'   <  / 库路径

建表命令格式

create  table  库名.表名(

表头名1  数据类型, 

表头名2  数据类型，

)

删除表:

drop  tables  库.表名;

修改表操作:

命令格式： alter  table  库名.表名   操作命令；

添加新表头   add         

删除表头   drop          

修改表头存储数据的 数据类型  modify 

修改表头名  change 

修改表名  rename 

添加在指定位置   在数据类型后面加  first  或  after

复制表   表头和数据复制都复制的

命令格式create  table  库名.表名   select  *  from  库名.表名 ；

复制表   仅仅只复制表结构

命令格式  CREATE  TABLE 库.表   LIKE  库.表

插入行:

 insert into  表名(字段名)  values（插入的数值）;  

修改行:

命令格式： update  库名.表名   set  字段名 =  值 ，  字段名 =  值  where 字段名 =  值  ；

删除行及空行:

delete   from  库.表  where  筛选条件；

delete   from  库.表；

delete  from  库.表 where  筛选条件=空字符   or   筛选条件  is null;  

数据类型

整型类型

tinyint    0~255

smallint  0~65535

int   0~2^32-1

浮点类型

m  总个数， d 小数位

float(m,d)  单精度   

double(m,d)   双精度

字符类型

char(字符个数)   定长类型   最多255个字符

varchar(字符个数)  变长类型   最多65532个字符

枚举类型

单选    enum(值1，值2，值3 ....)

多选    set(值1，值2，值3 ....)

日期时间类型  表头存储与日期时间格式的数据

年        year       YYYY     2021

日期    date        YYYYMMDD    20211104

时间    time       HHMMSS         143358   

日期时间（既有日期又有时间）  

datetime   或  timestamp       YYYYMMDDHHMMSS     20211104143648

timestamp与 datetime区别：

datetime存储范围大  timestamp范围小

timestamp类型      不赋值 使用系统时间自动赋值

datetime类型         不赋值  使用null  赋值 

数据导入格式：

load   data  infile   "/检索目录/文件名"   into  table    库名.表名      fields  terminated by  "文件中列的间隔符号"      lines   terminated by   "\n"  ;

数据导出命令格式：

select   字段名列表 from  库.表   where  条件     into  outfile   "/检索命令名/文件名"  fields  terminated by  "符号"  lines     terminated by  "符号" ；

三.   第三天内容

字段约束

1、 PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。

2、NOT NULL ：非空，用于保证该字段的值不能为空。

3、DEFAULT：默认值，用于保证该字段有默认值。

4、UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可以为空。

5、FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值

在从表添加外键约束，用于引用主表中某些的值

6.auto_incremen:   插入记录时，如果不给字段赋值,字段会通过自加1的计算结果赋值

创建主键 （2种语法格式 要记住）

create  table  库.表（ 字段名 类型  primary key  , 字段名 类型 , ..... ）;

create  table  库.表（ 字段名 类型 , 字段名 类型 , primary key（字段名） ）;

删除主键命令格式

 alter  table   库.表   drop primary  key ;

添加主键命令格式

alter  table  库.表  add  primary key(表头名);

创建外键命令格式

create table   库.表(表头列表 , 

foreign key(表头名)   #指定外键

references 库.表(表头名)   #指定参考的表头名

on update  cascade   #同步更新

on  delete  cascade  #同步删除

)engine=innodb;

查看表的外键 ，并获取外键名称

show create  table  库.表  \G

删除外键 (通过外键名称 删除表头的外键设置)

alter table 库.表 drop FOREIGN KEY   外键名;

已有表里添加外键

alter table 库.表 add  foreign key(表头名)  references 库.表(表头名)   on update cascade  on delete cascade;

复合主键  

1.表中的多个表头一起做主键 

2.复合主键的约束方式: 多条记录 主键字段的值不允许同时相同

外键

1.核心思想：保证数据的一致性

2.插入记录时，字段值在另一个表字段值范围内选择。

3.表存储引擎必须是innodb   注意: 参考的表不能删除

mysql索引

Mysql索引主要有三种结构：Btree、B+Tree 、Hash 

索引的优点：

1.提高MySQL的检索速度

2.减小了服务器需要扫描的数据量

3.帮助服务器避免排序和临时表

4.将随机IO变成顺序IO

索引的缺点：

1.提高了查询速度，同时却会降低更新表的速度

2.更新表时，MySQL不仅要保存数据，还要保存索引文件。

3.建立索引会占用磁盘空间的索引文件。

4.如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

索引的分类：

普通索引 (index)  :

不应用任何限制条件的索引，该索引可以在任何数据类型中创建。

创建普通索引命令格式(index) :

CREATE TABLE  库.表(

字段列表 ,

INDEX(字段名) ,

INDEX(字段名) ,

);

查看索引详细信息语句:

show index   from   库.表；

Key_name:    #索引名

Index_type:   #排队算法

explain命令 :   可以查看执行的查询select语句， 是否使用到索引做查询了

添加索引:

CREATE  INDEX  索引名  ON  库.表(字段名)；

删除索引 : 

命令格式   DROP  INDEX   索引名   ON  库.表；

唯一索引 (unique):

使用UNIQUE参数可以设置唯一索引。

全文索引 (FULLTEXT):

使用FULLTEXT参数可以设置索引为全文索引。

只能创建在CHAR、VARCHAR或者TEXT类型的字段上。

单列索引:

条件只需要保证该索引值对应一个字段即可只对应一个字段的索引。

多列索引:

多个字段上创建一个索引,对应的多个字段，用户可以通过这几个字段进行查询

mysql用户管理: 

用户授权命令格式:

GRANT 权限列表 ON 库名 TO 用户名@"客户端地址"   IDENTIFIED  BY  “密码”    WITH GRANT OPTION;

查看已有的访问权限

 show grants for   用户名@"客户端地址"

给已有追加权限

grant 权限  on  *.* to   用户名@"客户端地址"

权限撤销 ： 删除已有授权用户的权限:

REVOKE 权限列表 ON 库名 FROM  用户名@"客户端地址";

权限列表  ： 就是添加的用户对指定的库名具有的访问权限表示方式

ALL        表示所有权限  （表示所有命令）

USAGE    表示无权限  （除了查看之外啥权限都没有 ， 看的命令show    desc ）

SELECT,UPDATE,INSERT          表示只有个别权限

SELECT, UPDATE (字段1,字段N)   表示权限仅对指定字段有访问权限

库名  ：添加的使用户对服务器上的那些库有访问权限 可以表示的方式有：

*.*          表示 所有库所有表

库名.*    表示一个库下的所有表

库名.表名   表是一张表

客户端地址：

%                  表示网络中的所有主机

localhost          表示数据库服务器本机

* . * . * . *         指定主机

密码  ： 添加的用户连接数据服务时使用的密码 ，要符合数据库服务的密码策略

WITH GRANT OPTION ：    让添加的用户也可以使用grant命令再添加用户，但用户本身要对mysql库有insert的权限

添加密码:

grant   权限   on *.* to    用户名@"客户端地址";    identified by  "密码";

修改已有授权用户的连接密码

set password for 用户名@"客户端地址"=password("新密码");

添加用户:

赋予权限的时候  会自动生成用户

用户改名：

rename user 原用户名 to  现用户名 ;  

查看用户:

select  字段  from  库.表;

删除添加的用户:

drop  user   用户名@"客户端地址"; 

授权库mysql库的使用：（ 保存grant命令的执行结果。）

使用到了4张表 分别存储不同的授权信息

user表                           #保存已有的授权用户及权限 （全局权限    权限all   库名 *.*）

db表                #保存已有授权用户对数据库的访问权限

tables_priv表       #记录已有授权用户对表的访问权限

columns_priv表      #保存已有授权用户对字段的访问权限

四.   第四天内容

1.select 命令里使用函数做查询的格式

第一种格式

SELECT  函数(表头名)  FROM  库名.表名；

第二种格式

SELECT  函数(函数())  ；

第三种格式：单独把函数的执行结果输出

SELECT  函数() ;

常用函数的使用：

作用:处理字符或字符类型的字段

字符函数的使用:

LENGTH(str)                       返字符串长度，以字节为单位

CHAR_LENGTH(str)         返回字符串长度，以字符为单位

UPPER(str)和UCASE(str)  将字符串中的字母全部转换成大写

LOWER(str)和LCASE(str) 将str中的字母全部转换成小写

SUBSTR(s, start,end)            从s的start位置开始取出到end长度的子串

INSTR(str,str1)                      返回str1参数，在str参数内的位置

TRIM(s)                                  返回字符串s删除了两边空格之后的字符串

数学函数的使用:

作用:处理数据或数值类型的字段

ABS(x)                                   返回x的绝对值

PI()                                         返回圆周率π，默认显示6位小数

MOD(x,y)                              返回x被y除后的余数 

CEIL(x)、CEILING(x)         返回不小于x的最小整数 (x 是小数)

FLOOR(x）                           返回不大于x的最大整数 (x 是小数)

ROUND(x)                             返回最接近于x的整数，即对x进行四舍五入 (x 是小数)

ROUND(x,y)                          返回最接近x的数，其值保留到小数点后面y位，若y为负 值，则将保留到x到小数点左边y位 (x 是小数)

聚集函数的使用  sum()  avg()  min()  max()  count()

作用：数据统计命令 ，输出的值只有1个

avg(字段名)                               //计算平均值

sum(字段名)                              //求和

min(字段名)                              //获取最小值

max(字段名)                             //获取最大值

count(字段名)                           //统计字段值个数

日期时间函数的使用  ： 

使用mysql服务自带的命令 获取系统日期和时间

now()                                     系统日期时间

 curdate()                                             系统日期

curtime()                                              系统时间

year(now())                                         系统年份

 month(now())                            系统月份

day(now())                                        系统天数

数学计算的使用  +   -     *   /     % 

符号两边的 字段的是数值类型的

字段  数字符号 =  条件

流程控制函数 ： 查询表记录事可以加判断语句

 if语句 语法格式

语法：

if(条件,v1,v2) 如果条件是TRUE则返回v1，否则返回v2

ifnull(v1,v2)  如果v1不为NULL，则返回v1，否则返回v2 

case语句 语法格式  (可以有多个判断添加)

如果字段名等于某个值，则返回对应位置then后面的结果，

如果与所有值都不相等，则返回else后面的结果

语法格式1

CASE 字段名              

WHEN 值1 THEN 结果 

WHEN 值2 THEN 结果  

WHEN 值3 THEN 结果 

ELSE 结果  

END    as    '别名'

from  表

查询结果处理

分析出sql语句怎么写的思路：

第1步： 确定使用的命令   （对数据做处理使用那个命令  select   update   insert  delete ）

第2步： 确定数据在那个表里   （可以确定  from  后的 表名）

第3步： 确定处理的是什么数据  （就知道了 表头名  了）

第4步： 确定处理条件   （就知道 where 后边该怎么写了 ）

查询结果处理： 对用select 命令查找到的数据再做处理

语法格式

 select    字段名列表  from   库.表   where  筛选条件    分组|排序|过滤|分页 ;

分组 :  对查找到数据做分组处理(表头值相同为一组，只显示一次)

查询命令    group by   字段名

排序  把查找到的数据排队 ，用来排队的字段数据类型应该是 数值类型

order by   字段名   [asc];     从小到大排序（升序）默认的排序方式

order by   字段名   desc;   从大到小排序（降序）

运算符 和 统计函数的区别

运算符                横着计算

sum()统计函数         竖着计算

过滤数据 having  作用对select 查询到的数据 再次做筛选 使用的命令是having

select  字段名列表  from   库.表  where   条件     having   筛选条件;

分页 limit 

作用:限制查询结果显示行数（默认显示全部的查询结果） 

命令格式1  (只显示查询结果的头几行)

select  字段名列表  from   库.表  where   条件  limit 数字；

命令格式2 （显示查询结果指定范围内的行）  取值指定范围规则: 含尾去头   例如 ： limit   3, 5;      从查询结果的第4行开始显示，共显示5行

select  字段名列表  from   库.表  limit 数字1，数字2；

select  字段名列表  from   库.表  where   条件  limit  数字1，数字2；

二、连接查询

连接查询也叫多表查询 常用于查询字段来自于多张表

通过不同连接方式把多张表重新组成一张新表对数据做处理

连接查询分类

按功能分类

内连接

1.等值连接：使用相等判断做连接条件

2.非等值连接：连接条件不是相等判断

3.自连接：  自己连接自己，把1张表当做2张表（需要给表定义别名）

语法格式 

SELECT  字段列表

FROM  表1  别名

INNER JOIN  表2  别名  ON 连接条件  INNER JOIN  表3  别名  ON 连接条件

[WHERE 筛选条件]

[GROUP BY 分组]

[HAVING 分组后筛选]

[ORDER BY 排序列表]

外连接

左外连接   LEFT JOIN      

左边表的记录全都显示出来 右边的表只显示与条件匹配记录，右边表比左边表少的记录使用NULL匹配

右外连接 RIGHT JOIN  

右边表的记录全都显示出来 左边的表只显示与条件匹配记录，左表比右边表少的记录使用NULL 匹配

全外连接(mysql不支持，可以使用UNION实现相同的效果) ：合并查询结果

交叉连接