mysql基础思维导图模板_ProcessOn思维导图、流程图

mysql安装.tar.gz

1、下载mysql安装包

2、安装rzsz并上传到home文件夹中

使用yum安装yum -y install lrzsz

rz 上传命令，从本地上传到CentOS sz 下载命令，从CentOS下载到本地

3、创建文件夹

mkdir /usr/local/mysql

4、解压并复制

tar -xvf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz/* /usr/local/mysql

5、创建一个mysql用户和组

groupadd mysql useradd -r -g mysql -s /bin/false mysql

6.将位置更改为MySQL安装目录的顶级目录，通常是 /usr/local/mysql：如果目录不是这样的，请修改

mv mysql-5.7.30-linux-glibc2.12-x86_64/* ./

rm -rf mysql-5.7.30-linux-glibc2.12-x86_64

7.初始化数据目录

8.启动服务

bin/mysqld_safe --user=mysql &

登陆mysql

1、mysql -uroot -p123456

2、指定IP地址和端口号登录MySQL数据库命令格式为： mysql -h 114.119.37.98 -ujwollse -pmf_jwollse_2019 -P 3306

mysql编码格式

修改编码

临时生效set names gbk;

改配置文件[mysql] default-character-set=utf8

备份还原

导出数据库结构

mysqldump -u root -p --default-character-set=utf8 --databases test mysql &gt; db_backup.sql

mysqldump -u root -p --default-character-set=utf8 --all-databases &gt; db_backup.sql 所有库

mysqldump -u root -p dbname table1 table2 &gt; db_backup.sql 多个指定表

导出数据

mysqldump　-t　数据库名　-uroot　-p　&gt;　xxx.sql

还原

mysql -uroot -p db &lt; D:/c.sql

变量

局部变量

一般用declare声明，可以用default来说明默认值

用set赋值

会话变量

查看show session variables like &apos;△%&apos;;

指定变量名set @@session.变量名=某个值;

查看指定select @@session.变量名;

全局变量

show global variables；【语法同session】

用户变量

set @变量名=1;

存储过程

改变分隔符:delimiter $$

语法 create procedure △() begin... end

三种参数

in输入参数：必须在proc前指定，不能被返回

out输出参数：内部改变，可以返回

inout参数：可在调用时指定，并可以修改返回

create procedure (in v_int int) begin ... end

流程控制

条件控制

if：if ... elseif .. else ... end if;

case: case △ when ... then ... else ... end case;

ifnull(exp1,exp2)函数

循环控制

while：while △ do ... end while;

repeat： repeat △ utile 条件【退出条件】 end repeat

loop: loop名字： loop内容 if 条件 then leave loop loop名字; end if;end loop

定义条件和处理

declare continue handler for sqlstate &apos;错误代码值&apos; set 变量=△

存储过程

管理

查看proc:show procedure status where db=&apos;&apos;;

查看当前库下的proc列表:select specificaname from mysql.proc；

查看内容：show create procedure proc名字；

删除proc: drop procedure if exists 存储过程名；

函数

show variables like &apos;%fun%&apos;； set global △=1; 开启功能

创建语法

create function 函数名([参数列表]) returns 数据类型 begin sql语句; return 值; end;

删除

drop function if exists ..；

视图

修改/创建语法

CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];

with check option；更新视图的数据必须先满足视图条件才能更新到基础表

有聚合函数、distionct、group by、having、join等不可更新

触发器

语法

create trigger trigger_name trigger_time trigger_event on table_name for each row begin -- 需要执行的sql操作语句 end //trigger_time:before/after ; trigger_event: update/delete/insert

注意

update 可用**old**访问旧数据，**new**访问新数据 delete 可用**old**访问旧数据 insert 可用**new**访问新数据

管理

show triggers; # 查看所有触发器

drop trigger 触发器名;

show create trigger 触发器名; # 查看创建指定的触发器语句

锁

概念

使各种共享资源在被并发访问变得有序所设计的一种规则

MyISAM采用表级锁，innoDB支持行级锁和表级锁

MyISAM表锁

读锁（共享锁）

针对同一份数据，多个读操作可以同时进行而不会互相影响。

加锁lock table tablename read; 解锁 unlock tables;

写锁（排它锁）

当前写操作没有完成前，它会阻断其他写锁和读锁。

加锁lock table tablename write; 解锁 unlock tables;

事务

概念

一个事务是一个连续的一组数据库操作

4个条件（ACID）

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务处理

BEGIN/START TRANSACTION 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

直接用 SET 来改变 MySQL 的自动提交模式:SET AUTOCOMMIT=0 禁止自动提交

commit and chain提交并启动新事务

慢查询

概念

mysql记录下查询超过指定时间的语句

show variables like &apos;%long%&apos;

show status语句

show status like &apos;uptime%&apos;/&apos;com_select&apos;/&apos;connections&apos;/&apos;slow-quries&apos;[慢查询时间界限]

启动慢查询

临时开启

set global slow_query_log = on;

启动开启

以安全模式启动mysql

/usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

永久设置慢查询日志开启

修改my.cnf，log_show_queries、log_query_time、log_queries_not_using_indexes等参数

索引

概念

索引是一种与表有关的数据结构

分类

主键索引

alter table tbl_name add PRIMARY KEY(column);

唯一索引

create unique index idx_name on tbl_name(column); //创建

索引所在列可为null，但不能为空字符串

普通索引

create index idx_name on tbl_name(column);

全文索引

全文索引fulltext只有myisam支持，只可以从char、varchar、text列中创建,fulltext(column1,column2)；

data下文件后缀

.frm 表的结构；.myd 数据；.myi 索引文件

创建索引条件

在where条件中经常使用到的；该字段的变化不会太频繁

通过show profile分析sql

select @@have_profiling; 查看是否支持

set profiling=1; 开启

show profiles; 查看当前的SQL的queryID

show profiles for query queryID 分析

mysql优化

show variables like &apos;%partition%&apos;;查看是否支持 select查询：当查询/更新等一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。

表的分析、检查、优化

在执行期间将对表进行lock write锁定，不要在繁忙time执行

分析analyze table tbl_name;

检查check table tbl_name;

优化optimize table tbl_name;

show table status;获取表信息，rows【innodb结构的是估算的】

表分区

类型

Range分区

基于属于一个给定连续区间的列值，把多行分配给分区。

partition by range (store_id) ( partition p0 values less than (6), partition p1 values less than (11), partition p2 values less than (16), partition p3 values less than (21)， partition p3 values less than maxvalue );

List分区

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

partition by list(store_id) partition pNorth values in (3,5,6,9,17), partition pEast values in (1,2,10,11,19,20), partition pWest values in (4,12,13,14,18), partition pCentral values in (7,8,15,16) )；

hash分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。

partition by hash(store_id) partitions 4；

keys分区

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

partition by linear key (col1) partitions 3;

操作

增加表分区

PARTITION p1 VALUES LESS THAN (MAXVALUE) 这句要去掉，才可以增加分区。 ALTER TABLE sale_data ADD PARTITION (PARTITION s20100402 VALUES LESS THAN (20100403));

删除表分区

ALTER TABLE sale_data DROP PARTITION s20100406 ;

mysql内存管理与优化

myisam

key_buffer_size;索引缓存空间大小 read_buffer_size read_rnd_buffer_size

innodb

innodb_buffer_poor_size

innodb_log_buffer_size 日志缓存

max_connections最大连接数

mysql权限管理

查看db的用户 select host,user,password from user;

赋权 grant select/all on *.* to zy@localhost identified by &apos;123&apos; with grant option;

删除权限 rovoke select on *.* from zy@localhost;

删除用户及权限 drop user &apos;zy&apos;@&apos;localhost&apos;;

修改账号pwd set password for zy@localhost=password(&apos;new&apos;)；

mysql定时维护

开启event：set global event_scheduler=1; 查看event状态：show variables like &apos;%sche%&apos;;

创建定时器

drop event if exists timer_smile; CREATE EVENT IF NOT EXISTS timer_smile ON SCHEDULE EVERY 1 SECOND -- 某个时间执行 -- ON SCHEDULE AT TIMESTAMP &apos;2018-09-17 18:16:00&apos; -- 五天后执行 -- ON SCHEDULE AT CURRENT_TIMESTAMP+INTERVAL 5 DAY -- 可以设置是否立即生效 -- 设置定时任务创建后不立即开启 -- DISABLE 表示的是关闭 , 默认是开启的 ON COMPLETION NOT PRESERVE DISABLE DO INSERT `test`(`name`,`type`)VALUES(CURRENT_TIMESTAMP,&apos;1&apos;);

开启定时器任务

ALTER EVENT eventName ON COMPLETION PRESERVE ENABLE;