MySQL回顾+新内容

最新推荐文章于 2023-01-17 11:26:38 发布

PPDY小芽

最新推荐文章于 2023-01-17 11:26:38 发布

阅读量99

点赞数 1

分类专栏： MySQL 文章标签： MySQL存储过程 MySQL存储函数事务隔离级别 MySQL视图 MySQL

本文链接：https://blog.csdn.net/PPDY93/article/details/118927725

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

回顾
新内容

回顾

1. 存储过程

procedure

delimiter $$
  create procedure proname()
  begin
  
  end$$
delimiter ;

参数：输入[in]
输出：[out]
输入输出[inout]
call 存储过程名字（）进行调用

2. 存储函数

function
必须return，参数只是输入类型

delimiter $$
  create function funname() returns 类型
  begin
    
    return xxx;
  end$$
delimiter ;

3. 触发器

trigger
当对某个表做更新的时候，要处理的操作
save | update | delete on 某个表 for each row
afetr
before
新插入的数据，属于新数据new.字段名
数据库中原有的数据 old.字段名

delimiter $$
  create trigger tri_name after insert on for each row
  begin
  
  end$$
delimiter ;

4. 循环

while(条件){
循环操作；
}

/*1*/
while 条件 do
  循环操作；
end while;

/*2*/
while(true){
  if(){
    break;
  }
}

/*3*/
loopname:loop
  if 条件 then
    leave loopname;
  end if;
end loop;

/*4*/
repeat
  循环操作
  until 条件
end repeat;

5. 游标

多行数据
游标：cursor for select…
开启 open cur;
关闭 close cur;
抓取 fetch cur into _v1,_v2;
游标的状态 fond / not fond

新内容

1. 视图

view
便于查询
create view viewname as select…
使用视图好处：

       1. 组合查询，降低数据库的使用难度
       2. 数据表结构，屏蔽了
       3. 视图只做查询，不做修改

【单张表创建的视图是执行修改的，但是不推荐】视图是一个虚拟表

REPEATABLE READ ：另一个事务没提交，查询是可以正常运行的，只是看不到另一个事务对数据的更新

1.A会话 
   开启事务
   查询 不能
   查询 能


2.B会话
   开启事务
   添加数据
   不提交
   提交


SERIALIZABLE  效率最低 另一个事务没提交，查询不能执行，必须等待一个事务结束后才能有所操作
1.A会话 
   开启事务
   查询:不能执行的


2.B会话
   开启事务
   添加数据
   不提交
   提交

*/

--1.视图
crete view view_goods as select goodsId,goodsName,goodsNum from goods;

--2.查看数据
select * from view_goods;
select * from goods;
insert into goods values(NULL,'b',1,200,100,NLL);
--3.视图是单表创建的，可以对改视图做增删改的操作，但是不推荐的
insert into view_goods values(3,'c',80);
delete from view_goods where goodsId = 3;

SELECT g.`goodsId`,g.`goodsName`,p.`typeId`,g.`goodsNum` FROM goods g,producttype p WHERE g.`typeId` = p.`typeId`  ;
SELECT * FROM producttype


CREATE VIEW view_goods2 AS SELECT g.`goodsId`,g.`goodsName`,p.typeName,g.`goodsNum` FROM goods g,producttype p WHERE g.`typeId` = p.`typeId`  ;

-- 删除视图
DROP VIEW view_goods2;

SELECT * FROM view_goods2;
-- 视图是多表创建，所有不能删除视图中的数据
DELETE FROM view_goods2 WHERE goodsId=4;
DESC view_goods;

-- 事务  数据完整性  
-- 如果不使用事务会怎么样? 转账
CREATE TABLE account(
  aid INT PRIMARY KEY AUTO_INCREMENT,
  abalance INT
)
INSERT INTO account VALUES(1,10000),(2,5000),(3,2000);
SELECT * FROM account;
-- 1账号 给 2账号 转账 2000 元
UPDATE account SET abalance=abalance-2000 WHERE aid=1;
UPDATE account SET abalance=abalance+2000 WHERE aid=2;
-- start transaction; 开启事务
BEGIN ;  -- 开启事务   提交 commit或  回滚rollback
UPDATE account SET abalance=abalance-2000 WHERE aid=1;
UPDATE account SET abalance=abalance+2000 WHERE aid=2;
COMMIT;

ROLLBACK;
-- 打点事务 7990   7000
START TRANSACTION ;
UPDATE account SET abalance=abalance-10 WHERE aid=1;
SAVEPOINT A;
UPDATE account SET abalance=abalance-1000 WHERE aid=1;
UPDATE account SET abalance=abalance+1000 WHERE aid=2;
ROLLBACK TO A;

UPDATE account SET abalance=10000 WHERE aid=1;
COMMIT;

	幻读	不可重复读	脏读
read uncommited	√	√	√
read commited	√	√	×
repeatable read	√	×	×
serializable	×	×	×

2. 索引

单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引:索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列
索引的优点
（1）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
（2）可以大大加快数据的查询速度，这也是创建索引的主要原因。
（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。
（4）在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。
索引的缺点
（1）创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
（2）索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
创建索引的语法
ALTER TABLE student ADD INDEX(s_name );
alter table tabnae add index (字段名);
explain select语句;分析sql执行的效率
索引失效

1>全职匹配

2>最佳左前缀法则：如果索引多列，要遵守最左前缀法则，指的是查询从索引最左前列开始并且不跳过索引中的列。

3>不在索引列上做任何操作（计算，函数，（自动or手动）类型转换），会导致索引失效而转向全表扫描补再索引列上做任何操作(计算，函数，（自动or手动）类型转换)，会导致索引失效而转向全表扫描。如以下代码

EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

索引列上使用了表达式，如where substr(a,1,3)=‘hhh’ where a=a+1 表达式是一大忌讳，再简单mysql也不认。有时候数据量不大，一般可以查出来，如果数据量大，不能查询出，此时可以再程序中筛选。

4 >存储引擎不能使用索引中范围条件右边的列

5>尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *

6>mysql在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描

7>is null，is not null 也无法使用索引

8> like以通配符(’%abc…’)mysql索引失效会变成全表扫描的操作

9> 字符串不加单引号索引失效

10>少用or，用它来连接时会索引失效

3. 事务隔离级别

SELECT * FROM student;

EXPLAIN SELECT * FROM student WHERE s_no='s001';


-- 创建索引
ALTER TABLE student ADD INDEX(s_name );
EXPLAIN SELECT * FROM student WHERE  s_address LIKE '宿舍';

EXPLAIN SELECT * FROM student WHERE  s_phone LIKE '130%';

EXPLAIN SELECT student.*,(SELECT grade.`g_name` FROM grade WHERE grade.`g_id`=student.`grade_id`) gname FROM student ;

 EXPLAIN SELECT * FROM student s INNER JOIN grade  g ON s.`grade_id`=g.`g_id`;
 
 SELECT * FROM subjects;
 /*
CREATE TABLE `subjects` (
  `sub_id` int(11) NOT NULL AUTO_INCREMENT,
  `sub_name` varchar(50) NOT NULL,
  `sub_class_hour` int(11) DEFAULT NULL,
  `grade_id` int(11) NOT NULL,
  PRIMARY KEY (`sub_id`),
  UNIQUE KEY `sub_name` (`sub_name`),
  KEY `fk_grade_subjects` (`grade_id`),
  KEY `sub_name_2` (`sub_name`,`sub_class_hour`),
  CONSTRAINT `fk_grade_subjects` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`g_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8


 */
 SHOW CREATE TABLE subjects;
 
 INSERT INTO subjects VALUES(NULL,'MySQL深入',60,2);
ALTER TABLE subjects ADD INDEX(sub_name,sub_class_hour);-- 无意义的
ALTER TABLE subjects DROP INDEX sub_name_2;

SELECT * FROM subjects;
-- sub_name 是唯一
EXPLAIN SELECT * FROM subjects WHERE sub_name ='MySQL深入' AND sub_class_hour=65;
/*
CREATE TABLE `student` (
  `s_no` varchar(20) NOT NULL,
  `s_name` varchar(20) NOT NULL,
  `s_login_pwd` varchar(50) NOT NULL,
  `s_sex` char(2) DEFAULT '男',
  `s_phone` varchar(20) NOT NULL,
  `s_address` varchar(20) NOT NULL,
  `s_born` date NOT NULL,
  `s_email` varchar(50) DEFAULT NULL,
  `grade_id` int(11) NOT NULL,
  PRIMARY KEY (`s_no`),
  UNIQUE KEY `s_phone` (`s_phone`),
  UNIQUE KEY `s_email` (`s_email`),
  KEY `fk_grade_student` (`grade_id`),
  KEY `s_name` (`s_name`),
  KEY `s_name_2` (`s_name`,`s_born`),
  CONSTRAINT `fk_grade_student` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`g_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/
SHOW CREATE TABLE student;
ALTER TABLE student ADD INDEX(s_name,s_born);
-- range
EXPLAIN  SELECT * FROM student WHERE s_name LIKE 'a%' AND s_born='1994-1-1';
 -- ref
EXPLAIN SELECT * FROM student  WHERE s_name ='张三'; -- name
EXPLAIN SELECT * FROM student  WHERE s_name ='张三' AND s_born='1999-9-9'; -- 组合索引

PPDY小芽

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL回顾+新内容

文章目录回顾1. 存储过程2. 存储函数3. 触发器4. 循环5. 游标新内容1. 视图回顾1. 存储过程proceduredelimiter $$ create procedure proname() begin end$$delimiter ;参数：输入[in]输出：[out]输入输出[inout]call 存储过程名字（）进行调用2. 存储函数function必须return，参数只是输入类型delimiter $$ create function
复制链接

扫一扫