mysql自学笔记三

梦魇小爷

已于 2023-04-13 20:33:53 修改

阅读量69

点赞数

文章标签： mysql 笔记数据库

于 2023-04-13 20:32:01 首次发布

本文链接：https://blog.csdn.net/qq_42043319/article/details/130139397

版权

本文介绍了数据库中的联表查询，包括内联、左联、右联和笛卡尔积，以及如何使用去重关键字`DISTINCT`。此外，还探讨了视图的概念和创建、使用方法，自定义函数的创建、调用和删除，以及变量的使用。文章进一步讲解了存储过程的创建、调用和删除，并提到了触发器在数据一致性维护中的作用。最后，讨论了事务的基本概念和ACID特性，以及如何进行事务管理。

摘要由CSDN通过智能技术生成

12、联表查询

#内联：连接两个表，结果是两个表的交集
#左联：以左边表为基准
#右联：以右边表为基准
#笛卡尔积：和内联的结果一样，但是效率比较低，不推荐使用

12.1 内联 inner join

内联：表1 inner join 表2 on 连接条件；（取两个表的共同部分）

#内联： 表1 inner join 表2 on 连接条件；  （取两个表的共同部分）
select * from course inner join teacher on course.T = teacher.T;
select * from student inner join sc on student.S = sc.S;

12.2 左联 left join

左联：表1 left join 表2 on 连接条件；（以表1 为基准）

#左联： 表1 left join 表2 on 连接条件；    （以表1 为基准）
select * from course left join teacher on course.T = teacher.T;
select * from student left join sc on student.S = sc.S;

12.3 右联 right join

右联：表1 right join 表2 on 连接条件；（以表2 为基准）

#右联： 表1 right join 表2 on 连接条件；    （以 表2 为基准）
select * from course right join teacher on course.T = teacher.T;
select * from student right join sc on student.S = sc.S;

12.4 笛卡尔积

笛卡尔积： select * from 表1 ，表2 where 连接条件；（取两个表的共同部分）

#笛卡尔积： select * from 表1 ，表2  where 连接条件；（取两个表的共同部分）
select * from student , sc where student.S = sc.S;

13 、去重 distinct

distinct只能在select语句中使用
distinct必须在所有字段前面
如果有多个字段需要去重，则会对多个字段进行组合去重，即所有字段的数据重复才会被去重

select distinct s from sc;

14、视图 view

为了简化复杂的sql语句提出的概念，视图是一个由多个表导出的虚拟表，不是真实存在的，不需要遵守范式

视图的优缺点：优点：sql语句在网络中更安全，节省流量缺点：没有提升执行效率

14.1 创建视图

create view 视图名字 as (sql语句)

create view myview as (select student.*,c,score from student inner join sc on student.s = sc.s );

14.2 使用视图

select * from myview;

14.3 删除视图 drop

drop view 视图名

drop view myview;

15、自定义函数

15.1 创建函数

创建函数语法：

delimiter // #重新申明 // 为sql语句的结束标志

create function 函数名字（变量名数据类型，变量名2 数据类型，...）

returns 返回值类型

begin

函数体实现

end;

delimiter;

delimiter //
create function myadd (a int , b int)
returns int
begin
    declare c int default 0;    #定义临时变量 c   设置初始值 0
    set c = a + b;
    return c;
end//
delimiter ;

15.2 调用函数

select 函数名（参数）；

select myadd(2,3);

15.3 删除函数

drop function 函数名；

drop function if exists 函数名 # 先判断是否存在再删除

drop function if exists myadd;

16、变量

局部变量：局部有效 declare 变量名数据类型 default 初始值；
会话变量：仅在一次会话中有效 set @变量名 = 值； select @变量名
系统变量（全局变量） show global variables; set @@变量名 select @@变量名

17、三种选择语法

17.1 if 语法

if 条件1 then 执行语句1；

elseif 条件2 then 执行语句2；

else 执行语句3；

end if;

delimiter //
create function myfun(a int)
returns varchar(45)
begin
    declare res varcher(45) default '';
    if (a > 0) then set res = '正数';
    elseif (a = 0) then ser res = '零';
    else set res ='负数';
    end if;
    return res; 
end//
delimiter ;

17.2 case 语法一

case 变量 when 值1 then 执行语句1;

when 值2 then 执行语句2;

when 值3 then 执行语句3;

end case;

delimiter //
create function myfun(a int)
returns varchar(45)
begin
    declare res varchar(45) default '';
    case a when (10) then set res = '正数';
           when(-10) then set res ='负数';
           when (0) then set res ='零';
    end case;
    return res;
end//
delimiter ;

17.3 case语法二

case when (条件1) then 执行语句1;

when (条件2) then 执行语句2;

when (条件3) then 执行语句3;

end case;

delimiter //
create function myfun(a int)
returns varchar(45)
begin
    declare res varchar(45) default '';
    case when (a>0) then set res ='正数';
         when (a<0) then set res ='负数';
         when (a=0) then set res ='零';
    end case;
    return res;
end//
delimiter ;

19、循环语句

while 循环，不常用的循环 repeat 、loop循环

while 循环条件

执行语句；

end while;

drop function if exists mysum;
delimiter //
create function mysum(n int)
returns int
begin
    declare sum int default 0;
    declare i int default 0;
    while   i <= n
    do
        set res = res + i;
        set i = i+1;
    end while;
    return sum;
end;
delimiter ;

20、存储过程 procedure

存储过程：完成指定功能的sql语句集，存在数据库中，一旦编译，永久有效，有参数，无返回值

可以通过指定参数类型返回数据 IN/INOUT/OUT 默认是IN类型

20.1 创建存储过程

delimiter //

create procedure 存储过程名(参数1 类型，参数2 类型，....)

begin

sql语句集

end//

delimiter ;

delimiter //
create procedure myselect()

begin
    select * from student;
end;
delimiter ;

20.2 使用存储过程

call 存储过程名 ( )

20.3 删除存储过程

drop procedure 存储过程名

drop procedure if exists 存储过程名

函数与存储过程的区别

函数中不可以写多条sql语句，但是存储过程中是可以的

函数有返回值，存储过程中是没有返回值的，只能通过设置参数类型来返回 INOUT/ OUT

21、触发器 trigger

触发器：控制多个表的时候，如果一个表涉及到删除、插入、修改、另外一个表也需要执行一些操作，不然就会有垃圾数据产生，这时候就需要用到触发器

创建触发器的语法：

delimiter //

create trigger 触发器名字

after/before 触发操作

on 表名

for each row

begin

sql语句

end//

delimiter ;

定义一个删除触发器删除student 表的同时，删除sc表中对应的选课信息

delimiter //
create trigger mydel
after delete
on student
begin
    #删除sc表中对应的数据
    delete from sc where S = old.S;
    #Old 表   删除的数据 、 修改之前的数据
    #new表    增加以后的数据 、修改之后的数据
end//
delimiter ;

delete from student where s='07';

定义一个插入的触发器新增加同学，默认选修数学和语文

delimiter //
create trigger myinsert
after insert
on student
begin 
    insert into sc (S,C) values(new.S,'01');
    insert into sc(S,C) values(new.S,'02');
end//
delimiter ;

insert into student (S,Sname) values('22','zys');

定义一个修改的触发器修改某个同学的编号，同时修改sc里面同学的编号

delimiter //
create trigger myupdate
after update
on student 
begin
    update  sc set S = new.S where S = old.S;
end//
delimiter;
update  student set S = 20 where Sname ='zys';

22、事务

最小的执行逻辑单元，是原子操作

一些列sql语句的集合作为一个原子操作，要么都执行成功，要么都执行失败

事务的特性（ACID)

A atom 原子性：事务是最小的工作单位，不可再分，要么都执行，要么都不执行

C consistency 一致性：数据库的完整性约束不能被破坏

I isolusion 隔离性：同时运行的多个事务之间是相互隔离的，互不影响

D durability 持久性：持久保存在数据库中，提交以后数据发生永久改变

事务语句：

开始事务： start transaction

一系列的sql语句

提交commit(提交到数据库保存) /回滚rollback（回到开始事务之前的数据状态）

create table bank(
name varchar(45),
money int not null,
check(money >=0)); 

insert into bank values('张三' ,100);
insert into bank values('李四' ,100);

start transaction
update bank set money = money+50 where name = '张三';
update bank set money = money-50 where name = '李四';
commit;