MySQL笔记

目录

 修改数据库的默认编码：

数据库的导入/导出：

事务：

存储引擎：

视图：

索引：

范式：

存储过程：

游标：

存储函数：

 触发器：

union：

分页：

变量： 

---

创建数据库：

CREATE DATABASE table_name CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

 使用数据库：

use db_name;

创建表：

CREATE TABLE user(
	uid INT AUTO_INCREMENT,
	username VARCHAR(16) UNIQUE NOT NULL,
	pwd VARCHAR(16) NOT NULL,
	sex ENUM('男','女'),
	email VARCHAR(32),
	pho CHAR(11),
	create_time DATETIME NOT NULL,
	disable ENUM('0','1') DEFAULT '0' NOT NULL,
	PRIMARY KEY(UID)
);

sql调优：

sql调优_蒋劲豪的博客-CSDN博客

修改字段名：

alter table table_name change old_name new_name varchar(32) not null;

给字段添加注释：

alter table user modify column status tinyint comment '用户的状态1:正常，2:限制';

 修改数据库的默认编码：

1.先查看数据库编码：

show variables like "%char%";

 2.修改数据库编码：

# 客户端编码方式
set character_set_client=utf8mb4;
# 建立连接使用的编码
set character_set_connection=utf8mb4;
# 数据库的编码
set character_set_database=utf8mb4;
# 结果集的编码
set character_set_results=utf8mb4;
# 数据库服务器的编码
set character_set_server=utf8mb4;

修改后： 

数据库的导入/导出：

不用登录MySql，直接CMD运行。导出后没有创建数据库的命令，需要自己加上。

mysqldump -uroot -p imau > imau.sql

MySql数据库的导入：

source user.sql;

事务：

set global autocommit=0; 关闭自动提交，全局系统变量重启后会恢复到初始值

事务的四个特性（ACID）：

原子性（Atomicity）：

        事务是最小的工作单元，不可再分。

一致性（Consistency）：

        整个事务中的所有操作，要么全部执行，要么全部取消。

隔离性（Isolation）：

        一个事务的执行不会影响到其他事务的运行。

持久性（Durability）：

         事务成功结束后，数据必须永久持久化到硬盘中。       

案例：用户A向用户B转账100元：体现了：原子性、一致性、持久性

如何开启事务：

start transaction; 开启事务

.... 操作

rollback; 回滚

commit; 提交

savepoint save_point; 设置保存点

rollback save_point; 回到保存点

事务之间的隔离级别：

mysql数据库从第三级开始

第一级别：读未提交（read uncommitted）：

        读取到了对方事务未提交的数据，脏读。

第二级别：读已提交（read committed）：

        在一个事务中，读取到了对方事务提交后的数据，不可重复读，两次读取的结果不一样。解决了脏读问题。

第三级别：可重复读（repeatable read）：

        案例：开启事务，读取结果为10，过一段时间后，即使结果不为10了，读取结果还是10。解决了读已提交问题。存在幻读问题。

第四级别：序列号/串行化读（serializable）：

        解决了所有问题，效率低，需要排队。B事务更新了表后未提交，A事务不能查询同一张表，会阻塞，表锁。

 设置第一级别模式： 

set global transaction isolation level read uncommitted;

MVCC：

存储引擎：

InnoDB：支持事务，服务器崩溃后自动恢复，ACID，MVCC，行级锁，外键，级联删除/更新。默认引擎。

        工作中级联删除用的少，工作中是伪删除。

MyISAM：不支持事务，转换只读表后效率比InnoDB高，压缩表后占用存储空间比InnoDB小。5.1版本之前的默认引擎。

Memory：不支持事务。存储在内存中，长度固定，最快，断电后消失。表级锁，不能包含text和blob字段。

        blob：二进制文件

视图：

创建视图：

create view myview as
select * from student

with check option; 对于update,有with check option,要保证update后,数据要被视图查询出来

索引：

唯一约束的字段会自动添加索引

添加普通索引：

索引底层都是使用B+树

create index emp_sal_index on emp(sal);

添加唯一索引：

alter table user add unique(username);

删除索引：

drop index index_name on table_name;

索引的失效：

通常是字符串以第一个字符来分区，模糊查询，第一个字符%，导致索引失效。

范式：

1NF：

        每一个字段不可再分。

2NF：

        在1NF的基础上，不存在非主属性对候选码的部分函数依赖。

3NF：

        在2NF的基础上，不存在非主属性对候选码的传递函数依赖。

BCNF：

        在3NF的基础上，不存在主属性对候选码的部分函数依赖和传递函数依赖。

存储过程：

存储过程 一个函数

创建存储过程：

delimiter $$ -- 定义存储过程的结束符为$$
create procedure fun1(in name char(20))
begin
    declare age int default 18; -- 局部变量
    set age=20; -- 赋值语句
    update s set sname=name where sno=1;
    select age; -- return
end$$
delimiter ;

变量：

        局部变量：

在存储过程内部或存储过程的参数使用

declare 变量名 [int|bigint|char|varchar|date|time]等

调用存储过程：

call 函数名(参数);

查询所有的存储过程：

select * from information_schema.routines where routine_schema='数据库名';

 查询存储过程的定义：

show create procedure 存储过程名称;

删除存储过程：

drop procedure 存储过程名称;

存储过程的方法：

if函数：

delimiter $$
create procedure get_level(in score int)
begin
	declare level char(20) default '不及格'; -- 局部变量
	if score>=85 then
		set level='优秀';
	elseif score>=60 then
		set level='良好';
    else
        set level='不及格';
	end if;
	select level; -- return
end$$
delimiter ;

存储过程的参数：

in 输入参数

out 输出参数

inout 输入输出参数

案例：

create procedure 存储过程名称([in/out/inout] 参数名 参数类型)

case函数：

类似switch函数

case num
    when 1 then set res=10;
    when 2 then set res=20;
    else set res=30;
end case;

while循环：

while 条件 do
    语句
end while;

 repeat循环：

与do-while相反，满足条件则退出循环

特点：和do-while一样，至少循环一次

repeat
    逻辑代码
    until 条件(没有分号)
end repeat;

loop循环：

leave 类似于break

iterate 类似于continue 

loop
    执行语句
end loop;

 案例：

delimiter $$
create procedure my_loop(in n int)
begin
	declare total int default 0;
	sum:loop
		if n<=0 then
			-- break
			leave sum;
		end if;
		if n%2=1 then
			set n=n-1;
			-- continue
			iterate sum;
		end if;
		set total=total+n;
		set n=n-1;
	end loop sum;
	select total;
end$$
delimiter ;

游标：

游标是基于存储过程的，游标是用来存储查询结果集的数据类型，使用游标可以对结果集进行循环处理。

1、声明游标：

        declare 游标名称 cursor for 查询语句;

2、打开游标

        open 游标名称：

3、获取数据

        fetch 游标名称 into 变量[,变量];

4、关闭游标

        close 游标名称；

delimiter &&
create procedure p11(in uage int)
begin
	declare uname varchar(100);
	declare upro varchar(100);
	-- 声明游标
	declare u_cursor cursor for select name,pro from t_user where age<=uage;
	-- 条件处理程序，报错就关闭游标
	-- declare exit handler for sqlstate '02000' close u_cursor;
	declare exit handler for not found close u_cursor; -- 与上条语句一样
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
		id int primary key auto_increment,
		name varchar(100),
		pro varchar(100)
	);
	-- 打开游标
	open u_cursor;
	while true do
		-- 获取数据
		fetch u_cursor into uname,upro;
		-- 插入数据
		insert into tb_user_pro values(null,uname,upro);
	end while;
	-- 关闭游标
	close u_cursor;
end&&
delimiter ;

条件处理程序：

handler：

declare [continue|exit] handler for condition_value [,condition_value]... statement;
    -- exit是break
condition_value：
        sqlstate sqlstate_value -- 02000的sqlstate代码
        sqlwarning -- 所有01开头的sqlstate代码的简写
        not found -- 所有02开头的sqlstate代码的简写
        sqlexception -- 所有没有被SQLwarning或not found捕获的sqlstate代码的简写
         -- 状态码

存储函数：

create function 存储函数名(只能是in方式的 参数)
returns 返回值类型 [characteristic...]
begin
    -- 语句
    return 变量;
end;
-- characteristic说明：
    -- deterministic：相同的输入参数总是产生相同的结果
    -- not sql：不包含sql语句
    -- reads sql data：包含读取数据的语句，但不包含写入数据的语句

存储函数案例：

delimiter &&
create function fun(n int)
returns int deterministic
begin
	declare total int default 0;
	while n>0 do
		set total=total+n;
		set n=n-1;
	end while;
	return total;
end&&
delimiter ;

 存储函数的调用：

select fun(10);

 触发器：

在insert/update/delete语句之前或之后，自动触发的函数

行级/语句级触发器，myql数据库只支持行级触发器

案例表：

create table tb_user(
	id int auto_increment primary key,
	name char(20),
	phone char(11),
	email char(20),
	pro char(20)
);

创建触发器：

create trigger trigger_name
[before|after] [insert|update|delete]
on tbl_name for each row -- 行级触发器
begin
    trigger_stmt;
end;

创建insert触发器案例：

delimiter &&
create trigger tb_user_insert_trigger
	after insert on tb_user for each row
begin
	insert into user_logs(id,operation,perate_time,operate_id,operate_params) values
	(null,'insert',now(),new.id,concat('插入的数据内容为：id=',new.id,',
    name=',new.name,',phone=',new.phone,',email=',new.email,'pro=',new.pro));
end&&
delimiter ;

创建update触发器案例：

delimiter &&
create trigger tb_user_update_trigger
	after update on tb_user for each row
begin
	insert into user_logs(id,operation,perate_time,operate_id,operate_params) values
	(null,'update',now(),new.id,
	concat('更新之前的数据为：id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',pro=',old.pro,
	    ' | 更新之后的数据为：id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',pro=',new.pro
	));
end&&
delimiter ;

创建delete触发器案例：

delimiter &&
create trigger tb_user_delete_trigger
	after delete on tb_user for each row
begin
	insert into user_logs(id,operation,perate_time,operate_id,operate_params) values
	(null,'delete',now(),old.id,
	concat('删除之前的数据为：id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',pro=',old.pro));
end&&
delimiter ;

 查看触发器：

show triggers;

查看触发器：

show triggers;

 删除触发器：

drop trigger [schema_name.]trigger_name;
-- 如果没有指定schema_name，则为当前默认数据库

union：

特点：可以把两张不相干的表整合

案例：查询岗位是salesman和manager的员工：

select emp_id from emp where job like 'salesman'
union
select mgr_id from emp where job='manager';

分页：

案例：每页有10行，假设我要查询第二页

select * from user limit (2-1)*10,10;

变量： 

        系统变量：

系统变量是mysql提供的，不是用户自定义的

global 全局变量

session 会话变量

show global variables; 查看所有全局变量

show global variables like 'auto%' 模糊匹配查询变量

sellect @@系统变量名 查询指定变量

set global 系统变量名=值; 设置全局系统变量;

        用户自定义变量：

set @变量名=值

select 值 into @变量名 from 表名; 表的查询结果作为值赋值

select @变量名; 自定义变量的使用

一、MVCC

多版本并发控制机制

（一）Undo Log版本链

执行udpate和delete时，将操作记录存储在Undo Log中，每条Undo Log都有roll_pointer字段，roll_pointer指针字段指向上一次的操作记录，通过roll_pointer生成版本链；

（二）Readview读视图

对比roll_pointer版本指针，返回一个值