温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

MySQL的高级部分

发布时间:2020-07-29 11:48:40 来源:网络 阅读:356 作者:原生zzy 栏目:MySQL数据库

1. MySQL的事务

(1)存储引擎的介绍

   介绍:当客户端发送一条SQL语句给服务器时,服务器端通过缓存、语法检查、校验通过之后,然后会通过调用底层的一些软件组织,去从数据库中查询数据,然后将查询到的结果集返回给客户端,而这些底层的软件组织就是存储引擎。
   MySQL的存储引擎:
     - MySQL的核心就是存储引擎,MySQL可以设置多种不同的存储引擎,不同的存储引擎在索引、存储、以及锁的策略上是不同的。
     - Mysql5.5之前,使用的是myisam存储引擎,支持全文搜索,不支持事务。
     - Mysql5.5以后,使用的是innodb存储引擎,支持事务以及行级锁
MySQL的高级部分

(2)MySQL事务的介绍

   介绍:事务是一个操作序列,这些操作要么都做,要么都不做,是一个不能分割的工作单位。在两条或两条以上的SQL语句才能完成的业务时,才需要用事务,因为事务时同步原则,效率比较低。
   事务的ACID特性
     - 原子性:放在同一事务的一组操作时不可分割的
     - 一致性:在事务的执行前后,整体的状态是不变的
     - 隔离性:事务之间是独立存在的,两个不同事务之间互不影响
     - 持久性:事务执行之后,将会永久的影响到数据库。

#例:一个事务操作
BEGIN;
update  t_account set money=money+100 where id =1;
update  t_account set money=money-100 where id =2;
COMMIT;
#一个回滚操作
BEGIN;
update  t_account set money=money+100 where id =1;
update  t_account set money=money-100 where id =2;
COMMIT;

注意:MySQL数据库,dml操作采用的是自动提交

#查看自动提交
show variables like 'autocommit';
#修改自动提交
set autocommit=0;

(3)MySQL事务并发时产生的问题

  脏读:在一个事务的执行范围内,读到了另一事务未提交的数据。
  解决:读已提交,一个数据库只能读到另一个事务提交后的数据。(Oracle默认的事务隔离级别)
  不可重复读:一个事务,在只读范围内,被另一事务修改并提交事务,导致多次读取的数据不一致的问题。
  解决:可重复读(MySQL默认的事务隔离级别)
  虚读:一个事务的只读范围内,被另一个事务删除或者添加数据,导致多次读取的数据不一致的问题。
  解决:串行化:解决所有问题,但是速度十分缓慢,不能使用并发事务。
  注意:查看事务的隔离级别:select @@tx_isolation;

2. MySQL的存储程序

(1)MySQL的存储程序的介绍

   描述:运行与服务器端的程序。
   优点:简化开发,执行效率比较高(在服务器端以通过校验,可直接使用)
   缺点:服务器端保存这些存储程序需要占用磁盘空间;数据迁移时,需要将这些存储程序进行迁移;调试和编写程序在服务器端都不方便
   存储程序的分类:存储过程、存储函数、触发器
   注意:存储程序不能使用事务

(2)存储过程

  介绍:存储过程是在服务器端的一段可执行的代码块。

#修改结束符标志
delimiter  // 
#创建存储过程
create procedure pro_book()
begin 
#sql 
select * from book;
select * from book where bid=3;
end //
#运行
call pro_book()  
#参数的传入
delimiter //
create procedure pro_book02(num int)
begin 
select * from book where bid=num;
end ; //
--调用
call pro_book02(3)
#传出参数
delimiter //
create procedure pro_book03(num int,out v_name varchar(10))
begin 
select bname into v_name from book where bid=num;
end ; //
--调用,这里的@v_name是一个用户变量
call pro_book03(1,@v_name);
select @v_name;
#传入传出参数
delimiter //
create procedure pro_book04(num int)
begin 
select bid into num from book where bid=num;
end ; //
--调用 
set @v_id=3; --给用户变量赋值
call pro_book04(@v_id);
select @v_id;

控制流程语句

#if语句
delimiter //
create procedure if_test(score int)
begin 
-- 定义局部变量
declare myLevel varchar(20);
if score>80 then 
set myLevel='A';
elseif score >60 then 
set myLevel='B';
else 
set myLevel='C';
end if;
select myLevel;
end; //
-- 调用
call if_test(70);
#while循环
delimiter //
create procedure while_test()
begin 
declare i int ;
declare sum int ;
set i=1;
set sum =0;
while i<=100 do
set sum=sum+i;
set i=i+1;
end while ;
select sum;
end ;//
call while_test()
#loop循环
delimiter //
create procedure loop_test()
begin 
declare i int ;
declare sum int ;
set i=1;
set sum =0;
-- 起别名
lip:loop 
if i>100 then
-- 离开loop循环
leave lip ;
end if ;
set sum=sum+i;
set i=i+1;
end loop ;
select sum;
end ;//
call loop_test()
#repeat循环
delimiter //
create procedure repeat_test()
begin 
declare i int ;
declare sum int ;
set i=1;
set sum =0;
repeat 
set sum=sum+i;
set i=i+1;
-- 不要加分号
until i>100 
end repeat ;
select sum;
end ;//
call loop_test()

(3)存储函数

  存储在服务器端,有返回值,函数可以作为SQL的一部分进行调用。

**例**:
delimiter //
create function func_01(num int)
-- 返回值类型
returns varchar(20)
deterministic
begin 
declare v_name varchar(20);
select bname into v_name from book where bid =num ;
return v_name;
end ; //
set @v_name=func_01(3);
select @v_name;
-- 作为SQL的一部分调用
select * from book where bname=func_01(3);

函数和存储过程的区别
   - 存储过程有三种参数模式(in、out、inout)实现数据的输入输出,而函数是通过返回值进行数据传递。
   - 关键字不同
   - 存储过程可以作为独立个体执行,函数只能作为SQL的一部分执行。

(4)触发器

   触发器,存储在服务器端,由事件调用,不能传参。
   事件类型:增、删、改
   语法:

create trigger 触发器名
触发时机(after|before) event(update|delete|insert)
on 需要设置触发器的表名 for each row (设置为行级触发器)
begin
一组sql
end;

例:

delimiter //;
-- 创建一个触发器
create trigger tri_test
after delete 
-- 设置为行级别的触发器
on book for each row
begin
insert into book values(old.id,'悲惨数据','zzy');
end;//
注意:在触发器中有两个对象:old、new,old表示删除数据时那条原数据记录,
new表示修改和插入数据时,那条新数据记录。

3. MySQL的表的设计

(1)数据库的三大范式:

   - 1NF:所有字段都是原子性的,不可分割的。
   - 2NF:非主键字段必须与主键相关(每一张表只描述一类事物),而不能与主键部分相关(在联合主键时有效)
   - 3NF:非主键字段必须与主键相关(每一张表只描述一类事物),而不能与主键部分相关(在联合主键时有效)

(2)表的关系:

一一对应

#以人和×××为例
人表:
CREATE TABLE `t_people` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
×××表:
create table t_idcard(
card_number varchar(18) primary key,
create_date date,
p_id int unique,
foreign key (p_id) REFERENCES t_people(id)
)
注意:设计方法:想办法让外键字段同时拥有唯一约束,外键字段在任意的表中都可以

一对多

以部门和员工表为例:
create table t_emp(
eid int PRIMARY KEY,
ename varchar(50) not null,
job varchar(50),
deptno int , 
foreign key (deptno) REFERENCES t_dept(deptno)
)
部门表:
create table t_dept(
deptno int primary key,
deptname varchar(50)
)
注意:设计方法:只需要在多的那个表中增加一个外键约束

多对多
MySQL的高级部分
设计方法:需要找一张中间表,转化成两个一对多的关系

(3)数据库的优化:

  • SQL的优化
    • 在查询时一般不使用 *,因为在查询记录时,一般使用(*),他会将*转换为列名,然后在查询(耗时)
    • 使用 not null /null 对索引进行搜索,会导致索引失效
    • 索引列中使用函数,也会导致索引失效
    • 索引列中进行计算,也会导致索引失效
    • 索引列不要使用not|!=|<>
    • 尽量不要使用or,使用union
    • 索引列中使用like,也会导致索引失效
  • exists 和 in的使用选择
    • exists先执行主查询:如果主查询过滤的比较多,则使用exists
    • in先执行子查询:如果是子查询的过滤比较多,则使用in。
向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI