MySQL学习过程&测试代码

MySQL学习过程【待补充】

1.1 删除 delete 和 truncate

-- delete 和 truncate 的区别
-- delete删除后 表自增不变
-- truncate删除后 同时删除表自增
-- innodb引擎下 delete 后 重启数据库，自增重新计数。 （存在内存中，断电即失  ||  8.0 版本的数据库测试没有重新计数。8.0 之前的数据库计数器失效）
-- myisam 引擎下 继续从上个自增开始。（存在文件中，不会丢失）

CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`value` INT(4) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`value`) VALUES('1'),('1'),('1')

DELETE FROM `test`

TRUNCATE TABLE `test`

1.2 查询操作

-- 查询全部学生
SELECT * FROM student

-- 别名  给结果起别名 可以给字段起名字 也是可以给表起别名
SELECT `id` AS 学号,`name` AS 学生名字 FROM student AS 学生表

-- 函数 Concat（a，b）
SELECT CONCAT('姓名: ',studentName) AS 学生姓名 FROM student


-- ----------------------------------------------------------------------


-- 去重查询  DISTINCT distinct
-- 查询是否有成绩
SELECT * FROM result 
SELECT DISTINCT `studentNum` FROM result  -- 去除重复数据 只显示一条

-- 查询系统版本 -函数
SELECT VERSION()

-- 查询加减乘除 用来计算  -表达式
SELECT 100*3-1 AS 计算结果

-- 查询自增的步长 -变量
SELECT @@auto_increment_increment

-- 学生考试成绩 +1 分查看
SELECT `studentNo`,`studentResult`+ 1 AS '提分后' FROM result
SELECT `id`,`value`+ 1 AS '提分后' FROM test

-- ----------------------------------------------------------------------


-- 逻辑运算符
-- 运算符  					语法						描述
-- and  &&			 a and b |||| a && b  			   逻辑与
-- or ||			 a or b |||| a || b				   逻辑或	
-- not ！			not b ||||  ！b					  逻辑非
-- ----------------------------------------------------------------------
-- 查询区间内数据  BETWEEN  AND 进行区间查询的时候 是包含两边的数据的
SELECT studentNO,studentresult FROM result 
	WHERE studentReslut >=95 AND studentResult <=100
	
SELECT studentNO,studentresult FROM result 
	WHERE studentReslut >=95 && studentResult <=100

SELECT studentNO,studentresult FROM result 
	WHERE studentReslut BETWEEN 95 AND 100
	
	
-- ----------------------------------------------------------------------

-- 模糊查询
-- 通过like 结合 %（代表0 到任意个字符） _（代表一个字符）
SELECT `studentName` FROM student 
	WHERE studentName LIKE '王%'
	
SELECT `studentName` FROM student 
	WHERE studentName LIKE '王_'
	
-- 查询名字中含有 文 字的同学
SELECT `studentName` FROM student 
	WHERE studentName LIKE '%文%'
	
-- 通过in 进行查询
SELECT `studentNO` FROM student 
	WHERE studentNO IN(1001,1002,1003)
	
SELECT `studentNO` FROM student 
	WHERE studentNO IN('安徽','江苏')
	
-- null 和 not null
SELECT studentNO,studentresult FROM result 
	WHERE studentReslut = '' OR studentReslut IS NULL
	
SELECT studentNO,studentresult FROM result 
	WHERE studentReslut IS NOT NULL

-- ----------------------------------------------------------------------
/*  查询关键字的入参顺序
select
from
join
on
where 中不可使用聚合函数
group by
having  对结果集进行筛选，可以使用聚合函数
order by
limit
*/
-- ----------------------------------------------------

1.3 索引

1.3.1 索引的介绍

• 主键索引 （PRIMARY KEY）
  • 唯一的标识，主键不可重复
• 唯一索引 （UNIQUE KEY）
  • 避免重复的列出现，唯一索引可以重复多个列都可以标识为唯一索引
• 常规索引 （KEY/INDEX）
  • 默认的，不写就是默认的索引。使用index，或者key来进行设置
• 全文索引 （FULL TEXT）
  • 在特定的数据库引擎下才有，比如MyISAM
  • 快速定位数据

1.3.2 索引的使用

• 在创建的时候 给字段加索引
• 在创建完毕后，增加索引

-- 显示所有的索引信息
show index from student

-- 增加索引  `studentName`(`studentName`)--- 索引名（列名）
alter table school.`student` add fulltext index `studentName`(`studentName`)

-- explain 分析sql执行的状况
explain select * from student；-- 常规索引，非全文索引

explain select * from student where match(studentName) against('王')

1.3.3测试索引

-- 创建表
CREATE TABLE `app_user`(
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
	`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
	`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
	`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男；1：女）',
	`password` VARCHAR(50) DEFAULT '' COMMENT '密码',
	`age` VARCHAR(50) DEFAULT '' COMMENT '年龄',
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
	`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- 插入100万 的数据
delimiter $$  -- 写函数之前必写的标识符
create function mock_data()
return int
begin
	declare num int default 1000000;
	declare i int default 0;
	where i<num do
		-- 插入语句
		insert into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
		values (concat('用户',i),'123123@qq.com',concat('18',floor(rand()*(999999999-100000000)+100000000)),floor(rand()*2),uuid(),floor(rand()*2)*100))
		set i =i+1;
	end where
end;

创建函数的报错

-- 报错
-- This function has none of DETERMINISTIC, NO SQL, 
-- or READS SQL DATA in its declaration and binary logging is enabled 
-- (you *might* want to use the less safe log_bin_trust_function_creators variable)
-- 解决方式
SET GLOBAL log_bin_trust_function_creators=TRUE;

运行函数

-- 创建函数后 选择运行函数
SELECT mock_data();
-- 删除函数 mock_data();也是可以删除存储过程 
DROP  FUNCTION mock_data

创建索引进行查询测试

SELECT * FROM app_user WHERE `name`='用户99999'
SELECT * FROM app_user WHERE `name`='用户99989'

EXPLAIN SELECT * FROM app_user WHERE `name`='用户99999' -- 查询了 993799 条数据  耗时 0.006 sec

-- 创建索引 id_表名_字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_uer_name ON `app_user`(`name`) -- 创建耗时 4.005 sec


-- 查询表的索引
SHOW INDEX FROM app_user

-- 索引后测试查询
EXPLAIN SELECT * FROM app_user WHERE `name`='用户99999' -- 查询了 1 条数据  耗时 0.003 sec

1.3.4索引原则

• 索引不是越多越好
• 不用对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

索引的数据结构

Hash 类型的索引

Betree：InnoDB的默认索引类型是为Betree

1.3.5事务

1.3.5.1数据库事务实测

-- mysql 是自动开启事务自动提交的 
SET autocommit=0 -- 关闭自动提交

-- 标记事务开始
START TRANSACTION 
	UPDATE category SET pid=pid-500 WHERE categoryid = 2;
	UPDATE category SET pid=pid+500 WHERE categoryid = 3;
COMMIT -- 提交
ROLLBACK -- 回滚
-- 事务结束
SET autocommit=1 -- 开启自动提交

SAVEPOINT -- 事务中间的保存点
ROLLBACK TO SAVEPOINT -- 回滚到某个保存点
RELEASE SAVEPOINT -- 删除保存点

1.3.5.2事务的隔离级别

在MySQL数据库中查看当前事务的隔离级别： select@@tx_isolation; （我测试是没有测试成的）

• RU （Read Uncommitted：读取未提交 [ 脏读 ] ）
  • 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）；
• RC （Read Committed：读取提交内容）
  • 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果；
• RR （Repeatable Read：可重复读 [ 幻读 ] ）
  • 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。
    简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
    InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题
• Serializable （可串行化）
  • 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

不同隔离级别导致问题的情形示例

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

不同隔离级别，可能导致的问题

① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
　　② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
　　③ Read committed (读已提交)：可避免脏读的发生。
　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。