Mysql程序控制流语句

create database xskc character set=utf8;
use xskc;
create table s(sno char(2) primary key,sname char(10),ssex char(2),sage smallint,ssdept char(4));
insert into s values('01','AAA','女',17,'IS');
insert into s values('02','BBB','男',18,'IS');
insert into s values('03','CCC','女',17,'CS');
insert into s values('04','DDD','男',19,'CS');
insert into s values('05','EEE','男',18,'CS');
insert into s values('06','FFF','女',17,'CS');
create table c(cno char(3) primary key,cname char(12),cpno char(3),credit smallint);
insert into c values('c1','程序设计','c2',2);
insert into c values('c2','高等数学','c2',3);
insert into c values('c3','数据结构','c1',3);
insert into c values('c4','离散数学','',2);
insert into c values('c5','人工智能','c4',2);
create table sc(sno char(2) not null,cno char(3) not null,grade smallint,primary key(sno,cno));
insert into sc values('01','c1',90);
insert into sc values('01','c2',80);
insert into sc values('01','c3',60);
insert into sc values('02','c1',80);
insert into sc values('02','c2',70);
insert into sc values('02','c3',80);
insert into sc values('03','c1',80);
insert into sc values('03','c3',70);
$$

#第一题 1、查询每个学生的姓名，年龄，所在系，并在每个记录行中显示序号
delimiter $$
use xskc$$
set @i=0$$
create function f1() returns int
   begin
     set @i=@i+1;
     return @i;
   end $$

select f1() row,sname,sage,ssdept from s$$
+------+-------+------+--------+
| row  | sname | sage | ssdept |
+------+-------+------+--------+
|    1 | AAA   |   17 | IS     |
|    2 | BBB   |   18 | IS     |
|    3 | CCC   |   17 | CS     |
|    4 | DDD   |   19 | CS     |
|    5 | EEE   |   18 | CS     |
|    6 | FFF   |   17 | CS     |
+------+-------+------+--------+
6 rows in set (0.00 sec)

#第二题 2、定义函数查询某门课程的选课人数
create function f2(name char(10)) returns int
    begin
       return (select count(*) from sc where cno=(select cno from c where cname=name));
    end$$
set @name='查询课程'$$
select f2(@name) sum $$
+------+
| sum  |
+------+
|    3 |
+------+
1 row in set (0.00 sec)


#练习题 3、查询每个学生的姓名，年龄，所在系，并在每个记录行中显示序号，期中序号生成用函数实现
create function f3(cj int) returns char(2)
begin
  declare dj char(2);
  case
    when cj>=90 then set dj='优';
    when cj>=50 then set dj='良';
    when cj>=70 then set dj='中';
    when cj>=60 then set dj='及';
    else set dj='不';
  end case;
return dj;
end$$

select sno,cno,grade,f3(grade) from sc$$
+-----+-----+-------+-----------+
| sno | cno | grade | f3(grade) |
+-----+-----+-------+-----------+
| 01  | c1  |    90 | 优        |
| 01  | c2  |    80 | 良        |
| 01  | c3  |    60 | 良        |
| 02  | c1  |    80 | 良        |
| 02  | c2  |    70 | 良        |
| 02  | c3  |    80 | 良        |
| 03  | c1  |    80 | 良        |
| 03  | c3  |    70 | 良        |
+-----+-----+-------+-----------+
8 rows in set (0.00 sec)