mysql学习篇-1
韩琳*
MySQL, 数据库,
项目描述
学习篇-1
上传时间
2022.06.25
浏览人数
595人
韩琳*
天津市宁河县
Hot:14397
--插入表数据
INSERT INTO `hanlu101`.`student_info` (`id`, `name`, `age`, `sex`, `faculty`, `classnum`, `grade`) VALUES
('1001', '黄孤', '23', '女', '经管', '6', '19'),
('1002', '杨南', '30', '女', '艺术', '6', '21'),
('1003', '冶李台', '19', '女', '艺术', '5', '21'),
('1004', '胡木', '16', '男', '经管', '6', '19'),
('1005', '杨张', '20', '男', '经管', '7', '19'),
('1006', '甫政马', '21', '女', '工程', '8', '18'),
('1007', '台甫', '21', '女', '工程', '6', '20'),
('1008', '南郭杨', '21', '女', '艺术', '1', '18'),
('1009', '冶司黄', '26', '女', '计算机', '6', '21'),
('1010', '黄东', '24', '男', '艺术', '6', '20'),
('1011', '羊万', '27', '女', '计算机', '5', '21'),
('1012', '尉史甫', '21', '女', '机械', '10', '19'),
('1013', '葛闻司', '29', '女', '计算机', '2', '19'),
('1014', '赵林李', '17', '男', '工程', '8', '16'),
('1015', '方司', '17', '男', '工程', '2', '16'),
('1016', '南黄', '20', '男', '工程', '7', '19'),
('1017', '迟刘', '24', '男', '计算机', '8', '20'),
('1018', '黄高', '19', '女', '机械', '2', '21'),
('1019', '万太', '26', '男', '经管', '8', '20'),
('1020', '叔阳迟', '29', '女', '机械', '5', '16'),
('1021', '郭甫台', '16', '男', '艺术', '7', '20'),
('1022', '甫赵高', '21', '女', '艺术', '7', '21'),
('1023', '羊周濮', '22', '女', '计算机', '6', '16'),
('1024', '林万高', '30', '女', '经管', '5', '16'),
('1025', '闻宋', '24', '女', '计算机', '6', '19'),
#1 查询学生选课表中的全部数据
SELECT * FROM hanlu1008.`student_info`;
#2 查询计算机系学生的姓名、年龄
select name,age from hanlu1008.student_info where faculty = '计算机';
#3.查询成绩在18﹏20分之间的学生的学号、课程号和成绩
select id,classnum,grade from hanlu1008.student_info where grade> 18 and grade <20 ;
#4.查询计算机系年龄在18﹏20之间且性别为“男”的学生的姓名和年龄
select name,age from hanlu1008.student_info where faculty = '计算机' and age> 18 and age <20 and sex = '男';
#5.查询课程号为“7”的课程的最高分数
select max(grade) from hanlu1008.student_info where classnum = '7';
#6.查询计算机系学生的最大年龄和最小年龄
select max(age) from hanlu1008.student_info where faculty = '计算机';
select min(age) from hanlu1008.student_info where faculty = '计算机';
#7.统计每个系的学生人数
select count(id),faculty from hanlu101.student_info group by faculty ;
#8.统计每门课程的选课人数和考试最高分
select max(grade),count(id) from hanlu1008.student_info group by faculty;
#9.将计算机系的成绩按降序显示结果
select grade from hanlu1008.student_info where faculty = '计算机' order by grade desc;
#10.查询选修了课程“7”的学生的姓名和所在系
select name, faculty from hanlu1008.student_info where classnum = '7';
#11.查询成绩在21分以上的学生的姓名、课程号和成绩，并按成绩的降序排列结果
select name,classnum,grade from hanlu101.student_info where grade>20 order by grade desc;
#12.删除成绩小于50分的选课记录
delete grade from hanlu101.student_info where grade<50;
#13.将所有选修了课程“7”的学生的成绩加10分
set sql_safe_updates = 0;
UPDATE `hanlu101`.`student_info` SET grade = grade+10 where classnum = '7';
#14.将计算机系所有男生课程的学生的成绩加10分
update hanlu101.student_info set grade = grade+10 where faculty ='计算机' and sex = '男';
韩琳*
