mysql基础查询的使用
创建数据库
student表格
create table student( id int PRIMARY KEY COMMENT 'id主键', `name` varchar(20) COMMENT '名称', gender TINYINT(2) COMMENT '性别 1男 2女 3保密', age TINYINT UNSIGNED COMMENT '年龄', birthday date COMMENT '出生日期', createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间' on update CURRENT_TIMESTAMP, isdel bit(1) DEFAULT 0 COMMENT '是否删除 0未删除 1已删除 默认未删除' );
成绩表格score:
create table score ( id int PRIMARY KEY COMMENT 'id主键', sid int COMMENT '学生id', course VARCHAR(20) COMMENT '课程名称', score DECIMAL(4,1) COMMENT '成绩' );
因为下面插入数据没默认写id的值，所以id为自增的列。
添加自增：
alter table student MODIFY column id int auto_increment COMMENT 'id 主键'; alter table score MODIFY column id int auto_increment COMMENT 'id 主键';
初始化数据
数据下载地址：https://qyboke.lanzoue.com/iuhpB0n0rfij
score表：
student表格：
查询学生张三的所有成绩：
SELECT s.name,sc.course,sc.score from student s ,score sc where s.id = sc.sid and s.name = '李云';
查询平均成绩大于60的学生信息：
SELECT DISTINCT s.* from student s ,score sc where s.id = sc.sid and sc.score > 60;
查询有成绩的学生信息:
SELECT DISTINCT s.* FROM student s right JOIN score sc on s.id = sc.sid;
查询不及格的学生信息：
SELECT DISTINCT s.* ,sc.course, sc.score FROM student s right JOIN score sc on s.id = sc.sid where sc.score < 60;
查询各科成绩最高分、最低分和平均分:
SELECT DISTINCT score.course, max(score) over(PARTITION by score.course) '最高分', min(score) over(PARTITION by score.course) '最低分', avg(score) over(PARTITION by score.course) '平均分' FROM score
查询各科成绩第一名的记录:
SELECT DISTINCT sc.course, max(score) over(PARTITION by sc.course) '最高分' FROM score sc left JOIN student s on sc.sid = s.id
查询男生、女生人数:
SELECT DISTINCT student.gender, count(student.gender) over(PARTITION by student.gender) from student
查询 1990 年出生的学生名单：
SELECT * from student where YEAR(birthday) = 1990;
查询平均成绩大于等于 70 的所有学生的姓名、出生日期和平均成绩:
SELECT DISTINCT s.name,s.birthday,avg(sc.score) over(PARTITION by s.`name`) from score sc left JOIN student s on sc.sid = s.id
查询mbatis 成绩低于60的学生信息:
SELECT s.*,sc.course,sc.score FROM score sc LEFT JOIN student s on sc.sid = s.id where sc.course = 'mybatis' and sc.score < 60;
查询学生成绩前三名的记录:
SELECT ROW_NUMBER() over() as '成绩总分排名', sco.name,sco.allSco FROM ( SELECT DISTINCT s.`name` ,sum(sc.score) over(PARTITION by s.`name`) 'allSco' from score sc LEFT JOIN student s on sc.sid = s.id ORDER BY allSco desc LIMIT 3 ) as sco
查询学生信息及年龄:
SELECT stu.name,stu.birthday,(YEAR(CURRENT_DATE) - YEAR(stu.birthday)) '年龄' from student stu
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:
SELECT DISTINCT s.`name` ,sum(sc.score) over(PARTITION by s.`name`) 'allScore',avg(sc.score) over(PARTITION by s.name) '平均成绩' from score sc LEFT JOIN student s on sc.sid = s.id ORDER BY allScore desc
查询未成年的学生信息:
SELECT stu.name, stu.birthday, (year(CURRENT_DATE) - year(birthday)) 'age' from student stu where (year(CURRENT_DATE) - year(birthday)) < 18
查询姓张的 学生信息:
SELECT * FROM student where name like '张%'
查询本月过生日的学生信息：
SELECT * FROM student where MONTH(CURRENT_DATE) = MONTH(birthday)
查询本周过生日的学生信息
SELECT * FROM student WHERE WEEKOFYEAR(CURRENT_DATE) = WEEKOFYEAR(birthday)
按各科成绩进行排序，并显示排名
SELECT stu.name , sc.course ,sc.score, RANK() over(PARTITION by sc.course ORDER BY sc.score desc) from score sc LEFT JOIN student stu on sc.sid = stu.id
