526互联

MySQL2（连接查询，索引，事务，视图，存储过程，触发器）

发布时间 2023-03-30 16:53:09作者: 鲍小鱼儿

连接查询

多表关联查询

  • 嵌套查询

    查询张三所在班级的名称

    • select name from class where id = (select cid from student where name = '张三')

      嵌套查询就是将两个 SQL 进行组合查询

  • 连接查询

    • 内连接

      笛卡尔积

      select s.id sid,s.name sname,c.name cname from student s,class c where cid = c.id;

    • 外连接

      • 左连接：左表所有数据和右表满足条件的数据组成的结果

        select * from student left join class on student.id in (1,2);

      • 右连接：右表所有数据和左表满足条件的数据组成的结果

        select * from student right join class on student.id = 2;

select a.id aid,a.name aname,c.name cname from account a,course c,account_course ac where
a.id = ac.aid and c.id = ac.cid and a.id = 11;

select a.name from account a,course c,account_course ac where
a.id = ac.aid and c.id = ac.cid and c.name = 'Java'

多对多查询，需要通过中间表将两张业务表进行关联

索引

索引是一种特殊的数据库结构，可以用来快速查询数据表中的特定记录，索引是提升数据库性能的重要方式，主要是提高查询的速度。

普通索引：不需要任何限制条件的索引，可以在任意数据类型上创建

唯一索引：索引的值必须唯一，比如主键索引

全文索引：只能创建在 text、char、varchar 数据类型上，针对文本类型数据的索引

单列索引：只对应一个字段的索引

多列索引：在一张表的多个字段上添加索引，由多个字段共同构成索引结构

空间索引：只能建立在空间数据类型（经纬度），InnoDB 不支持空间索引

GIS

索引的设计原则：

  • 出现在 where 语句中的列，select 后面的列不需要加
  • 索引的值，尽量唯一，效率更高
  • 不要添加过多的索引，维护成本很高

1、添加索引

create index in_name on student(name)

2、删除索引

drop index in_name on student

事务

将多条 SQL 看成是一个整体，要么全部执行，要么一条都不执行

事务的特性：

  • 原子性：多条 SQL 是一个整体，不可再分割
  • 一致性：SQL 执行前后，数据库的数据值保持一致
  • 隔离性：一个事务的执行不能被其他事务所干扰
  • 持久性：一个事务一旦提交，数据库中数据的改变是永久性的

事务的操作

1、开启事务

start transaction;

2、回滚

rollback;

3、提交

commit;

start transaction;

update user set money = 500 where id = 1;
update user set money = 1500 where id = 2;

commit;

视图

数据库中一张虚拟的表，允许不同用户或应用程序以不同的方式查看同一张表中的数据

创建视图

create view view_common as select id,name from user;
create view view_all as select * from user;

使用视图

select * from view_common;

删除视图

drop view view_all;
drop view view_common;

触发器

提前定义好一系列的操作，可以在对指定表进行插入、更新、删除的同时自动执行这些操作

create trigger t_afterinset_on_tab1
    after insert on tab1
    for each row
    begin
        insert into tab2(tab2_id) values(new.tab1_id);
    end;

create trigger t_afterdelete_on_tab1
    after delete on tab1
    for each row
    begin
        delete from tab2 where tab2_id = old.tab1_id;
    end;

删除触发器

drop trigger t_afterdelete_on_tab1;
drop trigger t_afterinset_on_tab1;

存储过程

存储过程是提前定义好了一组 SQL 语句的集合，存储在数据库中，用户可以直接调用这些存储过程，类似于 Java 的方法调用。

create procedure add_name(in target int)
begin
    declare name varchar(20);
    if target = 1 then
        set name = 'MySQL';
        else
        set name = 'Java';
    end if;
    insert into user(name) values(name);
end;

call add_name(2);

create procedure count_of_user(out count_num int)
begin
    select count(id) into count_num from user;
end;

call count_of_user(@count_num);
select @count_num;

删除存储过程

drop procedure add_name;
drop procedure count_of_user;