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

54 阅读 0 评论 36 点赞

我是靠谱客的博主炙热红牛，最近开发中收集的这篇文章主要介绍数据库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 '和';