-- 创建第一个视图，包含序号，姓名，身份证倒数第二位，身份证前两位，班级,git账号
CREATE view v_student as 
SELECT s.id,
s.`name`,
LEFT(RIGHT(creditcard,2),1) sex,
LEFT(creditcard,2) province,
s.classid,
RIGHT(LEFT(creditcard,10),4) y,
RIGHT(LEFT(creditcard,12),2) m,
RIGHT(LEFT(creditcard,14),2) d,
(CASE WHEN LEFT(s.giturl,5)='http:' THEN RIGHT(giturl,LENGTH(giturl)-23)
WHEN LEFT(s.giturl,18)='https://gitee.com/' THEN RIGHT(giturl,LENGTH(giturl)-18) ELSE RIGHT(giturl,LENGTH(giturl)-24) END) giturl
from student s
WHERE 1=1;


-- 创建第二个视图，包含序号，姓名，性别，省份，班级
CREATE view v_student1 as 
SELECT v.id,v.`name`,
(CASE WHEN v.sex%2=0 THEN '女' ELSE '男' END) sex,
(CASE WHEN v.province='42' THEN '湖北省' ELSE '河北省' END) province,
-- 时间格式化
-- DATE_FORMAT(v.age,'%y-%m-%d') age
(CASE WHEN DATE_FORMAT(NOW(),'%m')-v.m>0 THEN DATE_FORMAT(NOW(),'%Y')-v.y
WHEN DATE_FORMAT(NOW(),'%m')-v.m=0 AND DATE_FORMAT(NOW(),'%d')-v.d>0 
THEN DATE_FORMAT(NOW(),'%Y')-v.y ELSE DATE_FORMAT(NOW(),'%Y')-v.y-1 END) age,
v.classid,
v.giturl acount
FROM v_student v
WHERE 1=1
ORDER BY v.sex,v.id ASC;

-- a=null和a is null 的区别
select s.`name`
FROM student s
WHERE s.classid = null

-- 求平均年龄
select AVG(age)
from v_student1
WHERE 1=1;
-- 最大年龄的男生是谁
SELECT vs1.`name`,vs1.age
from v_student1 vs1
WHERE vs1.age=(select MAX(vs.age) FROM v_student1 vs WHERE vs.sex='男');

-- 最大年龄的女生是谁
SELECT vs2.`name`,vs2.age
from v_student1 vs2
WHERE vs2.age=(select MAX(vs.age) FROM v_student1 vs WHERE vs.sex='女') AND vs2.sex='女';



-- 新建函数，给学生名，返回该生的教师名字
CREATE FUNCTION fun1(sname VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
DECLARE tname VARCHAR(50);
SET tname=(SELECT t.`name`
FROM teacher t,class c,student s
WHERE s.classid=c.id
AND c.teacherid=t.id
AND s.`name`=sname);

RETURN tname;
END;
SELECT fun1('丁云') tname;

-- 新建函数：给学生名，返回平均成绩。
CREATE FUNCTION fun2(sname VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
-- DECLARE scoreavg DOUBLE(10);
-- SET scoreavg=(()/2);
RETURN
(SELECT (s.javascore+s.sqlscore)/2
FROM score s
WHERE s.`name`=sname);
END;

SELECT fun2('张三');
-- 新建函数：给账号名和密码，返回是否验证成功。
CREATE FUNCTION fun3(username VARCHAR(50),pas varchar(50))
RETURNS VARCHAR(50)
BEGIN
DECLARE v varchar(50);
SET v = (SELECT s.`name`
FROM student s
WHERE s.`name`= username
AND s.`password`= pas
);
IF v IS NOT NULL THEN
RETURN '验证成功';
ELSEIF v IS NULL THEN
RETURN '验证失败';
END IF;
END;

SELECT fun3('丁云','12')
-- 新建函数：没有参数，随机取一个同学的名字。
CREATE FUNCTION fun4()
RETURNS VARCHAR(50)
BEGIN
DECLARE sname varchar(50);
SET sname=(SELECT s.`name` FROM student s WHERE s.id=CAST(CEILING(RAND()*16) AS DECIMAL));
RETURN sname;
END;

SELECT fun4();