操作系统与网络 2019-4-4

1.MySQL

1.1 练习题第五题

• 问题：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
• 1.首先考虑，这是要关联两个表的操作

select student.snum, student.sname, count(sc.cnum), sum(sc.score) from student
left join sc on student.snum = sc.snum
group by student.snum;

• 问题：查询所有有成绩的SQL
• 2.这是将student作为基准进行的表关联

select student.snum, student.sname, count(sc.cnum), sum(sc.score) from student 
right join sc on student.snum = sc.snum
group by student.snum;

• 问题：查询所有(包括有成绩和无成绩)的SQL
• 3.这个是先左连再右连；

select student.snum,student.sname,count(sc.cnum),sum(sc.score) from
student left join sc on student.Snum = sc.Snum group by student.snum
union all
select student.snum,student.sname,count(sc.cnum),sum(sc.score) from
student right join sc on student.Snum = sc.Snum group by student.snum having student.snum is null;

1.2 练习第六题

• 1.查询“李”姓老师的数量

select count(teacher.tnum) from teacher where teacher.tname like '李%';

1.3 查询学过"张三"老师授课的同学的信息

• 1.首先查询“张三”老师的编号

select teacher.tnum from teacher where teacher.tname like '张三';

• 2.其次找“张三”老师所交的课程

select course.cnum from course where course.tnum = (select teacher.tnum from teacher where teacher.tname like '张三');

• 3.找到“张三”老师教的课程对应的学生的编号

select sc.snum from sc where sc.cnum = (select course.cnum from course where course.tnum = (select teacher.tnum from teacher where teacher.tname like '张三'));

• 4.找到学生里的信息

select * from student where student.snum in (select sc.snum from sc where sc.cnum = (select course.cnum from course where course.tnum = (select teacher.tnum from teacher where teacher.tname like '张三')));

• 5.使用关联表来进行选择信息

select * from student inner join sc on student.snum = sc.snum 
left join course on sc.cnum = course.cnum
left join teacher on course.tnum = teacher.tnum
where teacher.tname = '张三';

1.4 查询没学过"张三"老师授课的同学的信息

• 1.将上面那道题的条件修改一下即可

select * from student where student.snum not in 
(select * from student inner join sc on student.snum = sc.snum 
left join course on sc.cnum = course.cnum
left join teacher on course.tnum = teacher.tnum
where teacher.tname = '张三');

1.5 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select * from student where student.snum in(select snum from sc where cnum = '01')
and student.snum in(select snum from sc where cnum = '02');

1.6 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select * from student where student.snum in(select snum from sc where cnum = '01')
and student.snum not in(select snum from sc where cnum = '02');

2.视图

2.1 作用

• 1.简化SQL语句；
• 2.保护数据；

2.2 临时组成的一个表

2.3 视图要满足表的规范

2.4 视图插入删除只能影响一个基表

2.5 创建视图

create view ViewSLAM as (
select sname, ssex from student
);

2.6 查询视图

select * from ViewSLAM;

2.7 给视图插入值

insert into ViewSLAM value('aa', '女');

2.8 删除aa这条数据

delete from student where sname = 'aa';

2.9 在创建一个视图

create view ViewSLAM1 as (
select student.*,sc.Cnum,sc.score from student inner join sc );
select * from ViewSLAM1;

2.10 视图插入删除 只能影响一个 基表 不能修改

insert into ViewLele value('01','aa','1234-09-09','nv','01','123');
delete from ViewLele1 where snum = '01';

3.函数

3.1 声明一个函数

• 1.声明一个函数；
• 2.形参是变量名在前，类型在后；返回值在声明中用的是returns，在定义中用的是return；

delimiter \\
create function ProcLele(a int,b int)
returns int
BEGIN
	declare c int default 0;
    set c = a+b;
    return c;
END \\
delimiter ;

• 2.调用这个函数；

select ProcLele(1,2);

3.2 定义会话变量

• 1.表示一个客户端和一个服务器相关联的时候，这时设置的变量就叫做会话变量
• 2.定义一个会话变量

set @aa = 100；

• 3.显示会话变量

select @aa;

• 4.使用一个函数声明会话变量

set @bb = ProcLele(1, 3);

3.3 定义if函数

• 1.在使用else if时需要将其合在一起；

drop function if exists ProcLeleIf;        # 判断函数存在删除
delimiter \\
create function ProcLeleIf(a int,b int)
returns int
BEGIN
	declare c int default 0;
    if a > b
		then set c = a+b; 
	elseif a < b
		then set c = b-a;
	else
		set c = 100;
	end if;
        
	return c;
END \\
delimiter ;

• 2.调用函数

select ProcLeleIf(3,3);

3.4 定义case函数

• 1.类似于switch case；

drop function if exists ProcLeleCase; 
delimiter \\
create function ProcLeleCase(a int)
returns int
BEGIN
	declare c int default 0;
	
    case 
		when a = 1 then set c = a*100;
		when a = 2 then set c = a*1000;
       when a = 3 then set c = a*10;
	end case;
    
	return c;
END \\
delimiter ;

• 2.也可以使用 case a

drop function if exists ProcLeleCase; 
delimiter \\
create function ProcLeleCase(a int)
returns int
BEGIN
	declare c int default 0;

	case a+1
		when 2 then set c = a*100;
		when 3 then set c = a*1000;
        when 4 then set c = a*10;
	end case;
    
	return c;
END \\
delimiter ;

• 3.调用函数

select ProcLeleCase(1);

3.5 定义while语句

• 1.定义一个简单的while语句

drop function if exists ProcLeleWhile; 
delimiter \\
create function ProcLeleWhile(a int)
returns int
BEGIN
	declare c int default 0;
	
	while a < 10
    do
		set c = a+c;
        set a = a+1; 
    end while;
   
	return c;
END \\
delimiter ;

• 2.定义一个有退出条件的while语句

drop function if exists ProcLeleWhile; 
delimiter \\
create function ProcLeleWhile(a int)
returns int
BEGIN
	declare c int default 0;
	
	while a < 10
    do
		set c = a+c;
        set a = a+1; 
        if a = 5
        	then return a;
    end while;
   
	return c;
END \\
delimiter ;

• 3.定义一个leave语句退出循环

drop function if exists ProcLeleWhile; 
delimiter \\
create function ProcLeleWhile(a int)
returns int
BEGIN
	declare c int default 0;
	
    AAA : BEGIN
    #######################
	while a < 10
    do
		set c = a+c;
        set a = a+1;
        
        if a = 5
			then leave AAA;
		end if;
        
    end while;
    #######################
    set c = 12333;
    END AAA;
   
	return c;
END \\
delimiter ;