数据库sql，储存过程，聚合函数，触发器

14.获取当前记录中最高分的记录
		SELECT *  FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    GROUP BY course_name
                    HAVING MAX(score_stu);
                   

--查询学生信息+选择的课程+成绩  (通过(left)关联查询)
SELECT stu_name,course_name,score_stu  
                    FROM  studentinfo stu
                    LEFT  JOIN score sc ON stu.stu_no = sc.stu_no
                    LEFT  JOIN course co ON co.course_id = sc.course_id
                    ;


--查询每个学生的总成绩,按照成绩从高到低排序(sum, group by, order by函数);
SELECT stu_name,
         SUM(score_stu) as '总分'
        
                    FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    GROUP BY stu_name
                     order by  SUM(score_stu) DESC;
                  



--查询出每个学生的选择课程的总学时数（课程表加 学时字段）
ALTER TABLE  course ADD stu_time double NULL;
SELECT stu_name,SUM(stu_time) as '总学时'
                    FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    GROUP BY stu_name;
                  

--统计男生，女生总分数，按照分数排序，递减。
SELECT stu_sex,SUM(score_stu) as '总分'
                    FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    GROUP BY stu_sex;
                    order by  SUM(score_stu) ASC;

--通过出生日期 计算出学生的年龄
SELECT stu_name,(DATE_FORMAT(NOW(),'%Y-%m-%d')-DATE_FORMAT(stu_birth,'%Y-%m-%d')) AS '日期计算年龄'   FROM  studentinfo stu;


//13.按照课程统计成绩的平均分,总分，最高分，最低分，并查询最低分大于等于60分，按照最高分降序排列
SELECT course_name,
          AVG(score_stu) AS '平均分',SUM(score_stu) as '总分',MAX(score_stu) as '最高分',
          MIN(score_stu) as '最低分'
                    FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    GROUP BY course_name
                    HAVING MIN(score_stu)>=60
                    ORDER BY MAX(score_stu) desc;


//12.按照课程统计成绩的平均分,总分，最高分，最低分，并查询最低分大于等于60分
SELECT course_name,
          AVG(score_stu) AS '平均分',SUM(score_stu) as '总分',MAX(score_stu) as '最高分',
          MIN(score_stu) as '最低分'
                    FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    GROUP BY course_name
                    HAVING MIN(score_stu)>=60;

//11.按照课程统计成绩的平均分,总分，最高分，最低分
SELECT course_name,
          AVG(score_stu) AS '平均分',SUM(score_stu) as '总分',MAX(score_stu) as '最高分',
          MIN(score_stu) as '最低分'
                    FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    GROUP BY course_name;
                
                    

//10. 统计张三学生的成绩的平均分,总分，最高分，最低分,考试门数
  SELECT  stu_name,course_name,
          AVG(score_stu) AS '平均分',SUM(score_stu) as '总分',MAX(score_stu) as '最高分',
          MIN(score_stu) as '最低分',COUNT(co.course_id) as '考试门数'
                    FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    WHERE stu_name = "张三";

//9. 查询张三所选科目成绩，姓名，课程名称;
SELECT stu_name,course_name,score_stu  
                    FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    WHERE stu_name = "张三";


//8. 查询没有参加某科目(java、 语文、 数学、)考试的学生信息；
SELECT *   FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id WHERE course_name not in('JAVA');

//7. 查询参加某科目(java、 语文、 数学、)考试的学生姓名;
SELECT stu_name,course_name   FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id WHERE course_name ='JAVA';

//6. 查询某某同学所选科目成绩;
SELECT stu_name,course_name,score_stu FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    WHERE stu_name = "可可";

//5. 查询包含XXX课程的成绩;
SELECT course_name,sc.score_stu   FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id WHERE co.course_name ='JAVA';

//4. 查询姓名中含有'三'的学生;
SELECT *   FROM  studentinfo stu WHERE stu.stu_name LIKE "%三%";

//3. 查询出生年龄 在1998-10-15 到 2012-10-12之间的学生;
SELECT *   FROM  studentinfo stu WHERE stu.stu_birth BETWEEN '1998-10-15' and '2012-10-12';

//2. 查询 学生编号大于1002 姓名为某某的数据;
SELECT *   FROM  studentinfo stu WHERE stu.stu_no>1002;

1. 按字段，别名方式查询数据;
SELECT *   FROM  studentinfo stu WHERE stu_name = '紫薇';

聚合函数

//常见的聚合函数有哪些？

//1、求个数/记录数/项目数等：count()
select count(stu.stu_no) from studentinfo stu; --包括空值
select count(*) from studentinfo; --不包括空值

//视图

CREATE VIEW v_co_stu_sc
      as 
   SELECT stu.stu_no,stu_name,stu_sex,stu_age,stu_birth,co.course_id,co.course_name,sc.score_id,score_stu 
                    FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    WHERE stu_name = "可可";

SELECT * FROM v_co_stu_sc;

CREATE VIEW v_co_stu_sc1 as
SELECT course_name,
          AVG(score_stu) AS '平均分',SUM(score_stu) as '总分',MAX(score_stu) as '最高分',
          MIN(score_stu) as '最低分'
                    FROM  studentinfo stu
                    INNER JOIN score sc ON stu.stu_no = sc.stu_no
                    INNER JOIN course co ON co.course_id = sc.course_id
                    GROUP BY course_name;

触发器

//触发器。


CREATE DATABASE record DEFAULT CHARACTER set utf8 collate utf8_general_ci;
CREATE TABLE daily_record(
      record_id int  not null auto_increment PRIMARY key,
      record_content VARCHAR(100) NOT null
)CHARSET = utf8,ENGINE = INNODB;


DROP TRIGGER tr_select;
-- 创建一个触发器
CREATE TRIGGER tr_select BEFORE INSERT ON studentinfo for EACH ROW
  BEGIN
     IF DAYOFWEEK(NOW())  in (1,5)
     THEN INSERT daily_record(record_content) VALUES("这是工作日");
     ELSE
      INSERT daily_record(record_content) VALUES("这是周末");
     END IF;
END;

INSERT into studentinfo VALUES(null,'次优','女','27','1997-05-06');
SELECT * FROM studentinfo;
SELECT * FROM daily_record;


//通过触发器插入一条数据
ALTER TABLE daily_record ADD INSERTdata VARCHAR(100) NULL;
DROP TRIGGER tr_insert;
-- 创建一个触发器
CREATE TRIGGER tr_insert  after INSERT ON studentinfo for EACH ROW
  BEGIN
DECLARE i int(1);
     IF DAYOFWEEK(NOW())  in (1,5)
     THEN 
      INSERT into daily_record VALUES(null,"这是工作日，正常执行",CONCAT("id：",new.stu_no,"  名字:",new.stu_name,"  性别：",new.stu_sex,'  年龄:',new.stu_age,'  生日:',new.stu_birth));
     
ELSE
      INSERT into daily_record VALUES(null,"这是周末，不能插入数据",CONCAT("id：",new.stu_no,"  名字:",new.stu_name,"  性别：",new.stu_sex,'  年龄:',new.stu_age,'  生日:',new.stu_birth));
      
     END IF;
END;

INSERT into studentinfo VALUES(null,'鄙人111','男','35','1990-04-06');
SELECT * FROM studentinfo;
SELECT * FROM daily_record;

存储过程

//方式一
CREATE PROCEDURE pro_sum(in a int, in b int, OUT result int)
   begin

    set result = a+b;
   end;

 set @a = 5;
 set @b = 7;

 call pro_sum(@a,@b,@result);

SELECT @result;

//方式二
CREATE PROCEDURE pro_sum2(in a1 int, in b1 int, OUT result1 int)
   begin
     if 
        a1 is null then set a1 = 7;
     end if; 
     if 
        b1 is null then set a1 = 9;
     end if;
   
    set result1 = a1+b1;
   end;

 call pro_sum(@a,@b,@result);

SELECT @result;

 //嵌套循环
DROP PROCEDURE pro_loop;
CREATE PROCEDURE pro_loop()
BEGIN
  declare  countnum INT;
   DECLARE  randnum int;
   DECLARE  i int;
   set countnum = 0;
  ks:LOOP
      SET randnum = ROUND(rand()*100); 
      SELECT randnum as 随机数;
      IF
        countnum <> 10 THEN  set countnum = countnum+1;
      ELSE
       leave ks;
      end if;
     end LOOP;      
 END;
CALL pro_loop();

//函数
CREATE FUNCTION fc_sum(num1 int,num2 int) RETURNS  int
  begin
    DECLARE result int;
    set result = num1+num2;
    RETURN result;
  end;

SELECT fc_sum(23,56) as '和';