mysql从入门到精通

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_33862644/article/details/79821134

该篇写的偏理论,点击查看常用指令

phpstudy的mysql目录介绍

1、bin //可执行文件

2、data //数据库

3、lib //扩展库,一般用不到

4、logs //日志

5、share //系统需要的东西,如 编码啥的

6、my.ini //配置文件。linux上是my.cnf

剩余的ini文件也都是配置文件,只是针对的服务器配置不同而已,如 my-smail.ini是针对内存小于64M用的,用法如下:

假如你的服务器配置小于64M,将my-smail.ini文件改名为my.ini,然后将其他的配置文件删除即可

*默认路径:/usr/local/mysql/var

mysql> show variables like 'general_log_file';		#日志文件路径
mysql> show variables like 'log_error';			#错误日志文件路径
mysql> show variables like 'slow_query_log_file';	#慢查询日志文件路径

7、https://www.cnblogs.com/songanwei/p/9167326.html  #使用了哪个配置文件

 

sql语句的书写规则:

1、以分号结尾 可以用delimiter修改

2、不区分大小写

3、#或--注释

位、字节、字符(计量单位)的关系:

 

  字节 字符(多字节)
英文、数字 8 1 1
汉字gbk编码 8 2 1(gbk中2个字节是1个字符)
汉字utf8编码 8 3 1(uf8中3个字节是1个字符)

 

 

 

mysql数据类型介绍:

整数型:

注意:有符号是从负数开始存,无符号是从正数0开始存;超过了最大值以最大值为准

注意:单双精度会四舍五入,和钱沾边的用decimal

字符串

注意:

1、括号里的是长度是字符。select char_length(name) from tp5_user; //该字段中,字符串所占的字符

2、char 读写速度快,因为他在内存中是按快找,缺点 浪费内存,因为存的不够会自动补空格

时间日期

*datetime的默认值是 '1970-01-01 00:00:00'

*timestamp的默认值是0,详见 https://blog.csdn.net/qq_29726869/article/details/81192679

字段的其他修饰关键词:

1、unsigned #无符号

2、auto_increment #自增

3、default 默认值 #给默认值的,如 价格默认0.00。不写 默认为null

4、comment '' #字段说明(注释)

5、not null #不能为空

6、null #可以为空

7、unique #唯一索引(对该列数据进行唯一性效验,插入重复的值会报error:Duplicate entry '123' for key 'user_name')

8、key 或 index #普通索引

9、primary key #主键索引

注意:

1、一般auto_increment 和 primary key是一对,在一起不分离偷笑主键索引才自增

2、全文索引用的是MyISAM引擎,只支持英文

3、外键索引语法:foreign key (b表字段名) references a表(外键字段名)

如 a(外键)表是学生表,b(本)表是成绩表。

作用:a表中如果没有该id,b表插入不进去,保持了数据的一致性,如果想要删除a表中的数据,也是无法清空的,因为b表对a表有依赖性。

注意:数据不一致,俩个表的id数据不一致,就是b表中的id在a表中没有,会报1452;俩个id的数据类型、长度等不一致报1215

3.1、a表的id要和b表的user_id数据类型,有无符号,是否为空等完全一致。

速记:涉及数字加unsigned ,每列必加not null和commet '' 

加索引的2种方式:1、在字段中加;2、所有字段书写完后统一加

SQL语句

 

sql语句中的运算符:

1、 = #2个意思,等于 或 赋值,系统会根据上下文判断    

2、!= <> #不等于

3、< > <= >= #小于 大于 小于等于 大于等于

4、OR #或

5、AND #与

6、BETWEEN ... AND #在...之间(判断2-5范围,与的关系)

7、IN #在...之内(判断2,3,4,5范围,或的关系)

8、NOT IN #不在...之内

sql语句的分类:

线上服务器的安全:

1、不要用root连接

2、默认密码也修改掉(因为人家会用字典一个一个的破)

修改密码:去表中改

3、win的忘记密码:先停掉mysql

使用该命令,就不会查mysql自带的user表了,会给你开个新进程

在打开一个cmd输入mysql进入mysql数据库

在通过sql语句更新,mysql自带的user表里的密码,在刷新权限

4、linux的忘记密码

  • 修改MySQL的配置文件(默认为/etc/my.cnf),在[mysqld]下添加一行skip-grant-tables

  • 保存配置文件后,重启MySQL服务 service mysqld restart

  • 再次进入MySQL命令行 mysql -uroot -p,输入密码时直接回车,就会进入MySQL数据库了,这个时候按照常规流程修改root密码即可。

    依次输入:

    >use mysql;    更改数据库

    >UPDATE user SET PASSORD =password("passwd") WHERE USER= 'root';  重设密码

    >flush privileges;  刷新MySQL的系统权限相关表,以防止更改后拒绝访问;或或者重启MySQL服务器

  • 密码修改完毕后,再按照步骤1中的流程,删掉配置文件中的那行,并且重启MySQL服务,新密码就生效了。

5、限制登录ip

需求:如果代码和数据库不在同一个服务器中才需要改,小项目不需要改;或只允许自己的电脑连

查看mysql自带的数据库中的user表

干掉后2条,登录就要加-h 而且只能是127.0.0.1才能访问

如果直接删掉不管用,就将root用户的host改成自己电脑的ip

普通用户的创建授权及权限回收

 

创建用户:

注意:

1、和权限相关的,每次做完都要刷新权限

2、%不安全,因为他对所有的ip都放行

给用户权限:

撤销权限:

查看权限:

删除用户:

 

表的存储引擎

怎么理解这个引擎呢??

将mysql理解成显示器,引擎可以理解为cpu。因为mysql是开源的,这些引擎有好多都不是官方的,而是开源爱好者提供的。

为啥要弄这么多引擎啊??

不同的引擎,是解决不同的场景,是解决不同场景遇到问题的。

如 csv 对应的excel入库 用这个快还好

表锁:每次写操作都会锁整张表,这整张表就不能进行任何读、写操作了

行锁:每次写操作都会锁整行,这行记录就不能进行任何读、写操作了

sql_mode的模式

 

往MySQL数据库中插入一组数据时,出错了!数据库无情了给我报了个错误:ERROR 1365(22012):Division by 0;意思是说:你不可以往数据库中插入一个 除数为0的运算的结果。于是乎去谷歌了一番,总算是明白了其中的原因:是因为MySQL的sql_mode 模式限制着一些所谓的‘不合法’的操作。

解析

这个sql_mode,简而言之就是:它定义了你MySQL应该支持的sql语法,对数据的校验等等。。

一、如何查看当前数据库使用的sql_mode:

mysql> select @@sql_mode;  

如下是我的数据库当前的模式:

 

二、sql_mode值的含义:

ONLY_FULL_GROUP_BY:

对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么将认为这个SQL是不合法的,因为列不在GROUP BY从句中

STRICT_TRANS_TABLES:

在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做任何限制

NO_ZERO_IN_DATE:

在严格模式,不接受月或日部分为0的日期。如果使用IGNORE选项,我们为类似的日期插入'0000-00-00'。在非严格模式,可以接受该日期,但会生成警告。

NO_ZERO_DATE:

在严格模式,不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式,可以接受该日期,但会生成警告

ERROR_FOR_DIVISION_BY_ZERO:

在严格模式,在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL。

NO_AUTO_CREATE_USER

防止GRANT自动创建新用户,除非还指定了密码。

NO_ENGINE_SUBSTITUTION:

如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

另外还有一些,这里仅对我本地当前值做解释分析。。。。。

 

三、据说是MySQL5.0以上版本支持三种sql_mode模式:ANSI、TRADITIONAL和STRICT_TRANS_TABLES。

1、ANSI(默认)模式:宽松模式,更改语法和行为,使其更符合标准SQL。对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告。对于本文开头中提到的错误,可以先把sql_mode设置为ANSI模式,这样便可以插入数据,而对于除数为0的结果的字段值,数据库将会用NULL值代替。

将当前数据库模式设置为ANSI模式:

mysql> set @@sql_mode=ANSI;

2、TRADITIONAL模式:严格模式,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报error错误,而不仅仅是警告。用于事物时,会进行事物的回滚。 注释:一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎,这种方式不是你想要的,因为出现错误前进行的数据更改不会“滚动”,结果是更新“只进行了一部分”。

将当前数据库模式设置为TRADITIONAL模式:

mysql> set @@sql_mode=TRADITIONAL; 

3、STRICT_TRANS_TABLES模式:严格模式,进行数据的严格校验,错误数据不能插入,报error错误。如果不能将给定的值插入到事务表中,则放弃该语句。对于非事务表,如果值出现在单行语句或多行语句的第1行,则放弃该语句。

将当前数据库模式设置为STRICT_TRANS_TABLES模式:

mysql> set @@sql_mode=STRICT_TRANS_TABLES;  

没有最好与最坏的模式,只有最合适的模式。需要根据自己的实际情况去选择那个最适合的模式!!!

另外说一点,这里的更改数据库模式都是session级别的,一次性,关了再开就不算数了!!!

也可以通过配置文件设置:vim /etc/my.cnf
在my.cnf(my.ini)添加如下配置:
[mysqld]
sql_mode='你想要的模式'

DTL事物控制语言

 

一条sql语句就是一个事务。事务可以保证一组sql语句,要么都成功,要么都失败。默认自动提交

注意:执行sql语句的时候,会默认提交(就给数据写到硬盘中了)。修改完是临时生效

多个事务,同时并发执行的例子:多人同时转账。

数据库编程--面向过程函数【存储过程】

 

把多条sql语句,写到一个函数中(它是一条或多条sql语句的集合)

作用:sql语句的复用(php中代码复用)。先定义,后调用

1、将sql结尾的;改成其他符号(防止冲突,将存储过程提前结束。因为你存储过程中也会用到分号)

delimiter ///    #将sql结束符改成///

2、定义存储过程

语法:create procedure 相当于php的 public function

查看存储过程

3、使用存储过程

4、不想要了,直接删除

 

视图的创建删除及使用场景

 

主要作用:涉及到安全方面

场景1:别的公司想调用咱们的用户,就可以给他创建一个视图。他只能调用这个视图(调用我们想给他暴露的数据)

场景2:cto不想让开发知道更多的用户信息

场景3:新手不会写复杂的sql(如 连表啊,子查询啊),老司机就给他个视图就ok了,因为视图的后面就是sql语句

视图和存储过程的区别

1、存储过程:中可以是各种sql语句,是方便我们自己使用

2、视图:中只能是select,是方便我们给外人使用,会生成一张表

总结:相当于是as后面得sql结果存到了test_view表中(show tables;就能看出来),然后使用视图的时候查test_view表

创建视图:

注意:as后面是sql语句,写不写where条件都可以。test_view(这里的字段名叫啥无所谓,带不能用''括起来,个数要和as后面输出的对上)

查看视图:

使用视图:

删除视图:

 

触发器

对某个(文章)表做了某些操作,它会触发另一些操作(统计表+1)

场景1:文章表每发送一篇文章,统计表中+1(创建一个触发器),删掉一篇文章-1(在创建一个触发器)

总结:相当于php框架中的钩子

1、准备数据,创建文章表和统计表

mysql> create table article(
    -> id int unsigned not null auto_increment primary key,
    -> title varchar(20) not null
    -> );
mysql> create table total_num(
    -> id int unsigned not null auto_increment primary key,
    -> type tinyint unsigned not null comment '1统计文章,2统计用户',
    -> num int unsigned not null
    -> );

先插入2条数据,不然他统计没法+1

insert into total_num (type,num) values(1,0);
insert into total_num (type,num) values(2,0);

2、创建增加的触发器

 


 
往article表中插入数据
insert into article (title) values('woshi_title');

查看total_num统计表

 

3、创建删除的触发器

mysql> create trigger delete_total_num after delete on article for each row
    -> begin
    -> update total_num set num=num-1 where type=1;
    -> end///

删除文章表的数据,触发删除触发器

delete from article where id=1;

 

4、查看触发器:

 

5、删除触发器:

 

mysql中的日志

 

MySQL中的日志包括:错误日志、二进制日志、通用查询日志、慢查询日志等等。

这里主要介绍下通用查询日志

通用查询日志记录建立的客户端连接和执行的语句

在学习通用日志查询时,需要知道几个数据库中的常用命令

1、 show variables like '%version%';

效果图如下:

上述命令,显示当前数据库中与版本号相关的东西。

2、show variables like '%general%';

 

可以查看,当前的通用日志查询是否开启,如果general_log的值为ON则为开启,为OFF则为关闭(默认情况下是关闭的)。

 

3、 show variables like '%log_output%';    //查看当前日志输出的格式

 

可以是FILE(存储在数数据库的数据文件中的hostname.log),

 

也可以是TABLE(存储在数据库中的mysql.general_log)

 

问题:如何开启MySQL通用查询日志,以及如何设置要输出的通用日志输出格式呢?

开启通用日志查询: set global general_log=on;

关闭通用日志查询: set globalgeneral_log=off;

设置通用日志输出为表方式: set globallog_output=’TABLE’;

设置通用日志输出为文件方式: set globallog_output=’FILE’;

设置通用日志输出为表和文件方式:set global log_output=’FILE,TABLE’;

注意:上述命令只对当前生效,当MySQL重启失效,如果要永久生效,需要配置my.cnf

日志输出的效果图如下:

记录到mysql.general_log表中的数据如下(从连接客户端开始执行的所有指令):

 

记录到本地中的.log中的格式如下(找不到,不知道这日志生成在哪了):

 

 

my.cnf文件的配置如下:

 

general_log=1  #为1表示开启通用日志查询,值为0表示关闭通用日志查询

log_output=FILE,TABLE#设置通用日志的输出格式为文件和表

4、在MySQL中有一个变量专门记录当前慢查询语句的个数

输入命令:show global status like ‘%slow%’;

 

 

 

 

 

慢查询日志、优化

 

慢查询日志:记录所有执行时间超过long_query_time秒的所有查询或者不使用索引的查询

该日志可以写入文件或者数据库表,如果对性能要求高的话,建议写文件。超过1秒的就算慢查询了。

一般来说,慢查询发生在大表(比如:一个表的数据量有几百万),且查询条件的字段没有建立索引,此时,要匹配查询条件的字段会进行全表扫描,耗时查过long_query_time,则为慢查询语句。

1、首先查看慢查询日志是否开启

第1种:超过long_query_time时间的慢查询

注意:平常不要开,只有分析的时候才开(因为开启后sql语句需要往日志里写,也要耗时间)。

set global slow_query_log=on;   //打开慢查询日志,临时生效,永久生效需要写在my.cnf中

set long_query_time=0.2;    //设置为200ms算慢查询。注意:设置这个参数的时候不要加global

第2种:不使用索引的慢查询

注意:如果只是将log_queries_not_using_indexes设置为ON,而将slow_query_log设置为OFF,此时该设置也不会生效,即该设置生效的前提是slow_query_log的值设置为ON),一般在性能调优的时候会暂时开启。

总结:俩种方式加一起,看下图这4个就够了

2、实际在学习过程中,如何得知设置的慢查询是有效的

很简单,我们可以手动产生一条慢查询语句,

比如,如果我们的慢查询log_query_time的值设置为1,则我们可以执行如下语句:

select sleep(1);

该条语句即是慢查询语句,之后,便可以在相应的日志输出文件或表中去查看是否有该条语句。

3、看日志,找出执行时间超过200ms的慢查询sql

 

慢查询的日志记录myql.slow_log表中,格式如下:

 

慢查询的日志记录到hostname.log文件中,格式如下:

 

 

可以看到,不管是表还是文件,都具体记录了:

 

是那条语句导致慢查询(sql_text),

该慢查询语句的查询时间(query_time),

锁表时间(Lock_time),

以及扫描过的行数(rows_examined)等信息。

4、分析慢的原因

思路1:使用explain sql语句; 查看详细信息做对比;

思路2:

mysql> set  profiling=on;    //打开性能

执行一遍慢的sql语句

然后输入 show profiles; 查看执行过程

思路:不知道怎么优化,直接给时间长的粘百度上,看别人咋解决的

案例1:如果执行过程中缓存锁(xxxxx query cache lock)太耗时,很有可能缓存出问题了,这时候就给缓存关掉

案例2:不要在字段或字段的值上做运算(这样也索引会失效)

 

starting //开启

checking permissions //检测权限

opening tables    //打开表

system lock    //表锁、行锁的那个锁

init //初始化

optimizing    //

statistics    //

preparing    //预编译(这时候就体验出存储过程了,存储过程会提前预编译好,直接调用执行即可)

executing    //编译完执行

sending data    //搜索数据并返回(发送给我)

end    //结束

query end    //

closing tables    //关闭表

freeing items    //

logging slow query    //如果发现该条sql慢,会记录到慢日志中

cleaning up    //

对应了:数据类型是字符串,查询时不加引号,索引会失效

 

数据库缓存

缓存默认是开启的

看缓存命中率

 

展开阅读全文

没有更多推荐了,返回首页