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)
