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 '和';