/**
1)连接层:客户端和服务器端建立连接,客户端发送SQL至服务器端;
2)SQL层:对SQL语句进行查询处理;
3)存储引擎层:与数据库文件打交道,负责数据的存储和读取。
1)InnoDB存储引擎:它是MySQL 5.5版本之后默认的存储引擎,最大的特点是支持事务、行级锁定、外键约束等。
2)MyISAM存储引擎:在MySQL 5.5版本之前是默认的存储引擎,不支持事务,也不支持外键,最大的特点是速度快,占用资源少。
由于 MySQL 数据库体积小、速度快、总体拥有成本低、开放源代码,其有着广泛的应用,一般中小型网站的开发都选择 MySQL 作为网站数据库。由于其社区版的性能卓越,因此搭配 PHP 和 Apache 服务器可组成良好的开发环境。
支持多线程,充分利用 CPU 资源
优化的 SQL 查询算法,有效地提高查询速度。
既能够作为一个单独的应用程序应用在客户端服务器网络环境中,也能够作为一个库而嵌入其他的软件中。
提供 TCP/IP、ODBC 和 JDBC 等多种数据库连接途径。
支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
支持多种存储引擎。
MySQL 5.6 是比较稳定的(GA)发布系列,只针对漏洞修复重新发布,不增加会影响稳定性的新功能。
**/
--使用下面的语句可以修改当前数据库临时的默认存储引擎
SET default_storage_engine=myisam
SET default_storage_engine=innodb
--查看库中引擎
show ENGINES
--查看慢查询设置
SHOW VARIABLES LIKE 'SLOW_QUERY%'
set global slow_query_log = on;
set global long_query_time=5
show global variables like 'long_query_time'
show variables like '%quer%';
mysqldumpslow -t 10 /data/mysql/mysql-slow.log
explain
select sleep(10)
--备份
mysqldump -u root -p esi xueke > D:xueke.sql
select * from mysql.user
/**
--主从复制
(1)主数据库进行增删改操作后,相应操作记录的语句(比如 create database test)会记录到binlog日志文件中(binlog日志文件一般和数据库data文件夹在一起)。
(2)从数据库会请求主数据库的binlog日志文件,获取到新的操作语句,然后在自己的从数据库上自动执行相同的操作语句,进而实现主从的同步。
详细主从复制过程
(1)Master开启bin-log功能,binlog日志文件用于记录数据库的增删改操作。
(2)需要开启三个线程,Master:I/O线程;Slave:I/O线程,SQL线程。
(3)Slave start;通过I/O线程连接Master,并且请求某个bin-log,position之后的内容。
(4)Master服务器收到Slave I/O线程发过来的日志请求信息,然后Master I/O线程将bin-log内容、position返回给Slave IO线程。
(5)Slave服务器收到bin-log日志内容,将bin-log日志内容写入到relay-log中继日志,创建一个master.info文件,该文件记录master IP、用户名、密码、master bin-log名称、bin-log position。
(6)Slave已经开启了sql线程,由sql线程实时监测relay-log日志内容是否有更新,如果有更新,则解析文件中的sql语句,并在Slave数据库中执行相同的操作语句。
注:可以通过show slave status \G 来查看具体的中继日志路径以及连接的master的其他信息。
**/
--特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高,资源消耗要小。
/**
BEFORE和AFTER参数指定了触发执行的时间,在事件之前或是之后。
FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器,也就是说触发器的触发频率是针对每一行数据触发一次。
①INSERT型触发器:插入某一行时激活触发器,可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中,相当与一系列的INSERT操作);
②UPDATE型触发器:更改某一行时激活触发器,可能通过UPDATE语句触发;
③DELETE型触发器:删除某一行时激活触发器,可能通过DELETE、REPLACE语句触发。
如果不需要某个触发器时一定要将这个触发器删除,以免造成意外操作,这很关键。
**/
--创建触发器
create TRIGGER test_trg
AFTER delete
ON xueke for EACH ROW
BEGIN
delete from 05xueke where researchfield = old.researchfield;
end
--调用触发器
delete from xueke where researchfield = 'PHYSICS';
--删除触发器
drop TRIGGER if EXISTS test_trg;
--查看触发器
SHOW TRIGGERS
--查看所有触发器
SELECT * FROM information_schema.triggers
--创建函数
CREATE FUNCTION test_f()
RETURNS varchar(20)
RETURN
(select researchfield from xueke where researchField = 'CHEMISTRY');
--调用函数
select test_f();
--删除函数
drop FUNCTION if EXISTS test_f
--查看所有用户
SELECT * from mysql.user
--查看用户
select * from mysql.user where USER = 'jack';
--删除用户
drop USER 'jack'@'localhost_3306'
--创建用户
CREATE USER 'alice'@'localhost_3306' IDENTIFIED BY '123'
--修改用户
RENAME USER alice@'localhost_3306' to 'jack'@'localhost_3306'
--修改用户密码
SET PASSWORD FOR 'jack'@'localhost_3306' = PASSWORD('123456');
--授予权限
GRANT SELECT,INSERT on *.*
TO 'jack'@'localhost_3306'
IDENTIFIED BY '123456'
WITH GRANT OPTION
--授予所有权限
GRANT ALL PRIVILEGES ON db_csv.*
TO 'jack'@'localhost_3306'
IDENTIFIED BY '123456';
--刷新用户权限
FLUSH PRIVILEGES
--查看该用户所有权限
SHOW GRANTS FOR 'jack'@'localhost_3306'
--收回用户权限
REVOKE ALL on *.* from 'jack'@'localhost_3306'
GRANT ALL ON *.* to 'jack'@'localhost_3306'
#REVOKE DELETE on *.* from 'jack'@'localhost_3306'
#grant, revoke 用户权限后,该用户只有重新连接 MySQL 数据库,权限才能生效。
-- 表分区
create table tab_name(id int,name varchar(20),datetime TIMESTAMP)
partition by list(TO_DAYS(datetime))(partition p1 VALUES in(TO_DAYS('2021-01-01')),PARTITION p2 VALUES in (TO_DAYS('2021-01-02')));
-- 添加一个表分区
alter table tab_name add PARTITION(PARTITION p3 VALUES in (TO_DAYS('2021-01-03')));
-- 查看表分局状态,和分区名
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='subject_analysis_dayair_aport_university_bk';
-- range
create table emp
(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date,salary int)
partition by range(salary)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than maxvalue
);
-- list
create table emp
(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date not null,salary int)
partition by list(deptno)
(
partition p1 values in (10),
partition p2 values in (20),
partition p3 values in (30)
);
-- hash
create table emp
(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date not null,salary int)
partition by hash(year(birthdate))
partitions 4;
--key
create table emp
(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date not null,salary int)
partition by key(birthdate)
partitions 4;
-- 分区表操作
alter table emp drop partition p1,p2;
alter table emp add partition (partition p4 values in (40));
alter table emp add partition (partition p4 values less than (40));
--分解分区
alter table emp reorganize partition p1 into
(partition p1 values less than (10),partition p4 values less than (40));
--合并分区
alter table emp reorganize partition p1,p2 into
(partition p1 values less than (10), partition p2 values less than (20)); -- 不会丢失数据
--重新定义hash 分区
alter table emp partition by hash(deptno) partitions 7;
--删除所有分区
alter table emp removepartitioning;
--删除后重建分区
alter table emp rebuild partition p1,p2;
--优化分区
alter table emp optimize partition p1,p2;
--分析分区
alter table emp analyze partition p1,p2;
--修复分区
alter table emp repairpartition p1,p2;
--检查分区
alter table emp check partition p1,p2;