您现在的位置是:首页 > 学无止境 > MYSQL网站首页MYSQL 数据库查询

数据库查询

  • 莫愁
  • MYSQL
  • 2018-05-17
简介这次查询包括多种查询,希望对学数据库的你有所帮助。
字数 2925.5

一.单表查询

数据库下载
xkgl.sql (13.08 KB)

1. 查询teacher表中所有教师的姓名和年龄。

命令:select Teachername,year(now())-year(birth) 年龄 from teacher

数据库查询

命令:select * from department

数据库查询

命令:select coursename from course where credit>=4

数据库查询数据库查询

命令:select * from student where ClassID='07010211' and Sex='女'

5. 查询学生姓名中第2个字为“丽”的学生信息。

命令:select * from student where StudentName like '_丽%'

数据库查询

命令:select Teachername,sex from teacher where Profession in('教授','副教授') and year(now())-year(birth)

数据库查询

命令:select * from course limit 0,5

数据库查询

命令:select studentid,grade from grade where courseid='Dp010001'

数据库查询

命令:select studentid,grade from grade where courseid='Dp010001' order BY grade desc,StudentID asc

数据库查询

二.连接查询

1. 查询学生的选课情况,包括学号,姓名,课程号,课程名和成绩。

写法一:

select s.student_no 学号,s.student_name 姓名,c.course_no 课程号,c.course_name 课程名,ch.score 成绩 from student s,course c,choose ch where s.student_no=ch.student_no and c.course_no=ch.course_no

写法二:

select s.student_no 学号,s.student_name 姓名,c.course_no 课程号,c.course_name 课程名,ch.score 成绩 from student s join choose ch on s.student_no=ch.student_no join course c on c.course_no=ch.course_no

数据库查询

写法一:

select s.student_name,c.course_name,ch.score from student s,course c,choose ch,classes cl where ch.course_no=c.course_no and ch.student_no=s.student_no and cl.class_no=s.class_no and cl.department_name='机电工程' and c.course_name='mysql数据库'

写法二:

select s.student_name,c.course_name,ch.score from classes cl join student s on cl.class_no=s.class_no and cl.department_name='机电工程' join choose ch on s.student_no=ch.student_no join course c on c.course_no=ch.course_no and c.course_name='mysql数据库'

数据库查询

写法一:

select cl.department_name 系别,s.student_no 学号,s.student_name 姓名,avg(ch.score) 平均分 from classes cl,student s,choose ch where cl.class_no=s.class_no and s.student_no=ch.student_no GROUP BY s.student_no

写法二:

select cl.department_name 系别,s.student_no 学号,s.student_name 姓名,avg(ch.score) 平均分 from classes cl join student s on cl.class_no=s.class_no join choose ch on s.student_no=ch.student_no GROUP BY s.student_no

数据库查询

select c.CourseName,g.Grade from course c,grade g where c.CourseID=g.CourseID

数据库查询

select distinct t.Teachername,c.CourseName from course c join schedule sc on c.CourseID=sc.CourseID join teacher t on sc.TeacherID=t.TeacherID

数据库查询

select t2.* from teacher t1,teacher t2 where t1.Profession=t2.Profession and t1.Teachername='刘芳' and t2.Teachername!='刘芳'

数据库查询

select t2.* from teacher t1,teacher t2 where t1.Brith<t2.Brith and t1.Teachername='刘芳'

数据库查询

select t.Teachername,CourseID,ClassID from schedule sc right join teacher t on sc.TeacherID=t.TeacherID

数据库查询

select s.StudentID,s.StudentName from student s,schedule sc1,schedule sc2 where s.ClassID=sc1.ClassID and s.ClassID=sc2.ClassID and sc1.CourseID='Dp010001' and sc2.CourseID='Dp010004'

数据库查询

select '学生人数',count(s.StudentID) 人数 from student s

UNION

select '选修人数',count(a.StudentID) 人数 from (select StudentID from grade g group by StudentID) a

数据库查询

select * from teacher where sex='男'

UNION

select * from teacher where sex<>'男' and Profession ='教授'

数据库查询

select count(*) ‘”大学英语”不及格的人数’ from grade g,course c

where c.CourseID=g.CourseID and CourseName='大学英语(一)' and g.Grade<60

数据库查询

select avg(Grade) ‘计算机平均分’ from grade g,student s,class cl,department d

where d.DepartmentID=cl.DepartmentID and s.StudentID=g.StudentID and cl.ClassID=s.ClassID and d.DepartmentName='计算机系'

数据库查询

三.嵌套查询

1. 查询 “计算机 系”的班级信息。

连接查询:

select c.* from class c join department d on c.DepartmentID=d.DepartmentID and d.DepartmentName='计算机系';

嵌套查询:

select c.* from class c where c.DepartmentID=(select d.DepartmentID from department d where d.departmentname='计算机系')

数据库查询

连接查询:

select s.* from student s join class c on s.ClassID=c.ClassID join department d on d.DepartmentID=c.DepartmentID and d.DepartmentName='计算机系'

嵌套查询:

select s.* from student s where s.ClassID in (select c.ClassID from class c where c.DepartmentID=(select d.DepartmentID from department d where d.departmentname='计算机系'))

数据库查询

select * from grade g where g.Grade<(select avg(g1.Grade) from grade g1 where g1.courseid='Dp010001') and g.CourseID='Dp010001'

数据库查询

连接查询:

select s.* from student s join grade g on s.StudentID=g.StudentID where g.CourseID='Dp010001' order by g.Grade asc limit 0,1

嵌套查询:

select s.* from student s where s.StudentID=(select g.StudentID from grade g where g.CourseID='Dp010001' order by g.grade asc limit 0,1) 数据库查询

方法一:

select * from student

where birth

<all(select birth from student where ClassID='Cs010902')

and ClassID='Cs010901'

方法二:

select * from student

where birth

<(select min(birth) from student where ClassID='Cs010902')

and ClassID='Cs010901'

数据库查询

select s.StudentName,s.StudentID from student s

where exists(select * from grade g where g.courseid='Dp010001' and g.studentid=s.studentid)

数据库查询

select (select coursename from course order by credit desc limit 1) as 最高学分课程名 ,(select coursename from course order by credit asc limit 1) as 最低学分课程名 from course limit 1

数据库查询

select StudentID

from grade where CourseID='Dp010001'

and studentid not in (select studentid from grade where courseid='Dp010002')

数据库查询

select s.StudentID,s.StudentName from department d join class c on d.DepartmentID=c.DepartmentID join student s on s.ClassID=c.ClassID join grade g on g.StudentID=s.StudentID where d.DepartmentName='计算机系' group by s.StudentID having count(g.CourseID)>5

数据库查询


转载: 感谢您对莫愁个人博客网站平台的认可,非常欢迎各位朋友分享到个人站长或者朋友圈,但转载请说明文章出处“来源莫愁个人博客 https://www.mochoublog.com/study/23.html”。

文章评论

    • 评论
    人参与,条评论

技术在线

服务时间

周一至周日 12:00-22:00

关闭下雪
关闭背景特效