【数据库】04_语法

多表查询

拼接查询比较慢。

一共四种：内联、左联、右联、笛卡尔积。

内联：表1 inner join 表2 on 连接条件

左联：表1 left join 表2 on 连接条件

右联：表1 right join 表2 on 连接条件

笛卡尔积：select * from 表1，表2 where 表1.属性=表2.属性

例子：

查询每名同学的信息、选修的课程和分数
select * from table1 inner join table2 on table1.s=table2.s;
select table1.* col1,col2 from table1 inner join table2 on table1.s=table2.s;

左联就是以左边的表作为基准。

笛卡尔积取的是两个表的交集，排列组合。但是效率极低。

总结：

内联：不含空结果，取的是两个条件的交集。

左联：以左侧表1为基础，匹配行，如果没有相应的，属性为空

笛卡尔积：先排列组合，再按照条件取交集，写法简单，但是效率低。

练习

查询成绩高于60分的学生信息
select student.*,C,score from student 
inner join sc on student.S=sc.S where score>60;
 
查询每个学生的总成绩以及学生信息
select student.*,sum(score) from student 
inner join sc on student.S=sc.S group by student.S;
每个就是分组
 
查询总成绩大于200的学生信息
select student.*,sum(score) total from student 
inner join sc on student.S=sc.S group by student.S having total>200;
 
查询总成绩最低的学生信息
select student.*,sum(score) total from student 
inner join sc on student.S=sc.S group by student.S 
order by total asc limit 0,1;

有些户数据库支持top语句。

查询选修了张三老师课程的学生信息
select student.* from student
inner join sc on sc.S=student.S
inner join course on course.C=sc.C
inner join teacher on teacher.C=course.C
where Tname='张三';

先联合表，然后再去加条件，最后看查看什么。

去重

去掉重复。

去掉单列重复或者多列组合重复。

多列组合重复就是两个12，而21这种不算。

语法：select distinct 列名 from 表名

例如select distinct a,b from table，例如两个列都有05，那么不会去重，除非是两个0505

函数

function

创建函数：create function 函数名(参数1 类型，参数2，类型...)

函数类型写在参数之后。

返回值：returns 返回值类型

函数体：

begin函数内容end

定义变量：declare 变量名 类型 default

设置值：set 变量名=值

分隔符：重定义结束符(分隔符)，告诉编译器，暂时以此为分隔符，不编译后面的。

# 定义函数 求和
delimiter //
create function mysum(a int, b int)
returns int
begin
    declare c int default 0;
    set c=a+b;
    return c;
end//
delimiter ;

函数讲解

首先告诉函数从delimiter开始，然后会有一个结尾。

最后一句意思是：把分隔符换回来，换成分号。

调用函数

select 函数名(变量列表...)

select mysum(2,4);

删除函数

直接删除和判断是否存在再删除

drop function 函数名。

drop function if exists 函数名;

变量

局部变量、会话变量、全局变量

局部变量

c int

set c

一般都是在函数里定义：函数名 类型;

会话变量

@变量名

set @a = mysum(2,4);

select @a

全局变量

一直存在的，也叫系统变量。超级权限root才能操作。

用户不能创建全局变量，只能查看

show global variables;

select @@global.performance_schema;

常用的还是会话和局部。

查看变量

select 变量名。

选择

if选择

if(a>b) then 执行语句；可以是多条执行语句

else if (a=b) then 执行语句;

else 执行语句

end if

例子：

delimiter //
create function myfun(a int)
return varchar(45)
begin
    declare c varchar(45) default '';
    if(a>0)then set c= '大于0';
    elseif(a=0) then set c='等于0';
    else set c='小于0'
    end if;
    return c;
end //
delimiter ;

注：都有分号。 在右面会显示function，类似于schema和view。

case选择

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

                         when 值 then 执行语句;

                         when 值 then 执行语句;

end case

2、case when 条件1 then 执行语句;

                when 条件2 then 执行语句;

                when 条件3 then 执行语句;

end case

delimiter //
create function myfun(a int)
return varchar(45)
begin
    declare c varchar(45) default '';
    case a when 0 then set c='等于0';
           when 10 then set c='等于10';
    end case;
    return c;
end //
delimiter ;
select myfunction(10);

delimiter //
create function myfun(a int)
returns varchar(45)
begin
    declare c varchar(45) default '';
    case a when (a>0) then set c='大于0';
           when (a=0) then set c='等于0';
           when (a<0) then set c='小于0';
    end case;
    return c;
end //
delimiter ;
select myfunction(10);

循环

while循环

while 条件

do

        多条可执行语句;

end while;

delimiter //
create function myfun(a int)
returns int
begin
    declare sum int default 0;
    declare i int default 0;
    while i<=n;
    do
        set sum=sum+i;
        set i=i+1;
    end while;
    return sum;
end //
delimiter ;
select myfunction(10);

其他循环：sql没有for循环，有repeat和loop循环。

存储过程

存储过程：stored procedure，是在大型数据库系统中，一组为了完成特定功能的sql语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

函数里没有sql语句

存储过程是数据库中一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

优点：

1. 减少网络流量：存储过程直接在服务器端运行，减少了与客户机的交互。
2. 增强了代码的重用性和共享性
3. 加快系统运行速度
4. 使用灵活

存储过程：procedure

创建存储过程：

delimiter //

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

begin

执行语句

end //

delimiter；

存储过程没有返回值，可以通过参数传递，参数可以定义为：in/out/inout三种，不定义的默认输入参数alter

执行存储过程：call 存储过程(参数列表);

删除存储过程：drop procedure 存储过程名字

delimiter //
create procedure mypro()
begin
    select * from student;
end //
delimiter ;
call mypro();
drop procedure mypro;

函数和存储过程的区别：

1. 函数是操作数据的，是在select语句中使用的，函数不允许有sql语句，存储过程中允许有sql语句。
2. 存储过程没有返回值，可以通过参数返回，但是函数有返回值。

练习

青蛙爬井，深10m，白天向上爬5幂，夜晚下滑4米，问多少天爬出来？

delimiter //
create function frogwell(high int)
returns int
begin
    declare x int default 0;
    declare d int default 0;
    while 1
    do
        set d=d+1;
        set x=x+5;
        if (x>=high) then return d;
        end if;
        set x=x-4;
    end while;
    return d;
end //
delimiter ;
select frogwell(10);

查询数据量比较大时需要分页，写一个分页的存储过程，查student表，输入参数当前是第几个

delimiter //
create procedure mypro(page int,count int)
begin
    declare offset int default 0;
    set offset=(page-1)*count;
    select * from student limit offset,count;
end //
delimiter ;
call mypro;