mysql sex_MySQL 数据查询

--查询学生表的所有字段

SELECT * fromstudent;--查询学生表的学号，姓名，性别字段

SELECT sNo,sName,sex fromstudent;--查询查询学生表的学号，姓名，性别字段，并用 as 定义字段别名

SELECT sNo as '学号',sName as '姓名',sex as '性别' FROMstudent;--查询学生表的所有女生信息

SELECT *from student WHERE sex='女';--查询课程表中学分大于4的课程所有信息

SELECT *FROM course WHERE credit>4;--查询学生表中在1995年出生的学生信息

select *FROM student where birthday>='1995-1-1' AND birthday<='1995-12-31';--查询学生表中姓杨的信息

SELECT *FROM student where sName LIKE '杨%';--查询学生表中姓杨的姓名为两个字的学生信息

SELECT *FROM student where sName LIKE '杨_';--查询学生表中姓杨的姓名为三个字的学生信息

SELECT *FROM student where sName LIKE '杨__';--查询学生表中姓名的第二个字为伟和先的同学信息

select *FROM student where sName LIKE '_伟%' OR sName LIKE '_先%';--查询学生表中在1995年出生的学生信息

SELECT *FROM student where birthday BETWEEN '1995-1-1' AND '1995-12-31';--查询成绩表中成绩在60到89之间的信息

SELECT *FROM score WHERE grade BETWEEN 60 and 89;--查询学生表学号为 1308013101，1309122503，1312054904 的学生信息

SELECT *FROM student WHERE sNo in('1308013101','1309122503','1312054904');--查询课程表中标记为空的信息

SELECT *FROM course where remark is NULL;--查询学生表中男生的信息并按生日降序排序

SELECT *FROM student WHERE sex='男' ORDER BY birthday DESC;--查询学生表所有信息并按性别降序，学号升序排列

SELECT *FROM student ORDER BY sex DESC,sNo;--查询学生表信息并按生日降序，取前五个记录

SELECT *FROM student ORDER BY birthday DESCLIMIT5;--查询成绩表中当课程号为01001的学号，课程号，成绩信息，并以成绩降序排列

SELECT sNo,cNo,grade FROM score WHERE cNo='01001' ORDER BY grade DESCLIMIT1,3;--查询成绩表中选修的学号 并用DISTINCT去重

SELECT DISTINCT sNo FROMscore;--用内连接 INNER JOIN 来查询信息

SELECT student.sNo,sName,sex,cNo,grade FROM student inner JOIN score ON student.sNo=score.sNowhere sex='女';SELECT student.sNo,sName,sex,cNo,grade FROM student,score where student.sNo=score.sNo and sex='女';--查询学号为1308013101 的信息

select S.sNo,sName,sex,C.cNo,cName,grade FROM student S INNER JOIN score G ON S.sNo=G.sNoINNER JOIN course C on C.cNo=G.cNowhere S.sNo='1308013101';SELECT S.sNo,sName,sex,C.cNo,cName,grade fromstudent S ,course C,score Gwhere S.sNo=G.sNo and G.cNo=C.cNo and S.sNo='1308013101';--查询学号为1308013101的最高分，最低分及平均分和总分信息

SELECT max(grade) as '最高分',min(grade) as '最低分' ,avg(grade) as '平均分',sum(grade) as '总分' FROM score WHERE sNo='1308013101';--查询学生表男生人数

SELECT count(*) as '男生人数' from student where sex='男';--查询成绩表中已选修课程的人数并用DISTINCT去重

select count(DISTINCT sNo)as '已选修课程学生人数' fromscore;--分组统计男、女生人数

SELECT sex as '性别',count(*) as '学生人数' from student GROUP BYsex;--分组统计不同班级人数

SELECT deptName AS '班级',count(*) as '学生人数' from student GROUP BYdeptName;--5分组查询学生的学号、姓名、选修课程数及平均分

SELECT student.sNo as '学号',sName as '姓名', count(*)as '选修课程数',avg(grade) as '平均分' from score INNER JOIN student on score.sNo=student.sNoGROUP BYstudent.sNo;--用嵌套查询学号为1308013101的同学在同一个班级的学生信息

SELECT *FROM student where deptName=(SELECT deptName from student where sNo='1308013101');--查询课程编号为01001的课程，且成绩超过该课程平均分的学生的学号、姓名、所属班级、课程编号、课程名称及成绩

select student.sNo,sName,deptName,course.cNo,cName,grade FROMstudent,score,coursewhere student.sNo=score.sNo and score.cNo=course.cNoAND course.cNo='01001'and grade>(SELECT avg(grade)FROMscore,coursewhere course.cNo=score.cNo and course.cNo='01001');--创建新数据表，查询网络131的学生记录，将查询结果插入到新数据表中

CREATE TABLEtempStudent(

stuNochar(10) not null PRIMARY KEY,

StuNameVARCHAR(20)not NULL,

sexchar(1),

deptNamevarchar(30) not null);INSERT intotempStudent(stuNo,stuName,sex,deptName)SELECT sNo,sName,sex,deptName from student where deptName='网络131';--将数据结构 课程成绩统一减去5

update score SET grade=grade-5

where cNo=(SELECT cNO from course WHERE cName='数据结构');--查询更新完的成绩表

select *from score where cNo=(SELECT cNO from course WHERE cName='数据结构');--删除机电131的学生的成绩记录

delete fromscorewhere sNo in (SELECT sNo from student where deptName='机电131');--查询 删除后的机电131成绩记录

SELECT *from score where sNo in (SELECT sNo from student where deptName='机电131');