mysql实验三单表和多表查询_数据库实验三（单表查询）

实验三:

select sno,sname

from student;//(1)查询全体学生的学号和姓名

select *

from student;//(2)查询全体学生的详细记录

select sname,sage,sdept

from student where sdept='MA';//(3)查询软件学院的学生姓名、年龄、系别

select distinct sno

from sc;//(4)查询所有选修过课程的学生学号(不重复)

select distinct sno

from sc

where grade<60;//(5)查询考试不及格的学生学号(不重复)

select ssex,sage,sdept

from student

where sdept not in('CS','MA');//(6)查询不是软件学院、计算机系的学生性别、年龄、系别

select sno,sname,sdept,sage

from student

where sage>=18 and sage<=20;//(7)查询年龄18－20岁的学生学号、姓名、系别、年龄；

select *

from student

where sname like '刘%';//(8)查询姓刘的学生情况

select *

from student

where sname like '刘%'or sname like '李%';//(9)查询姓刘或姓李的学生情况

select *

from student

where sname like '刘_';//(10)查询姓刘且名字为两个字的学生情况

select sname

from student

where sage<31;//(11)查询1983年以后出生的学生姓名。

create table studentgrade

(

sno char(8) PRimary key,

mathgrade tinyint,

englishgrade tinyint,

chinesegrade tinyint

)

go

insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95001',85,95,74)

insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95002',86,91,70)

insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95003',80,92,71)

insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95004',81,91,75)

insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95005',87,97,78)

insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95006',81,97,70)

insert into studentgrade(sno,mathgrade,englishgrade,chinesegrade) values( '95007',85,95,70)

select sum(mathgrade+englishgrade+chinesegrade) as sumgrades

from studentgrade group by sno;(12)创建表 studentgrade(sno,mathgrade,englishigrade,chinesegrade)

计算学生各科总成绩并赋予别名

select (year(getdate())-student.sage+1)

from student

where sdept='MA';//(13)利用内部函数 year()查找软件学院学生的出生年份

select sname + '年龄为'+cast(sage as char(2))+'岁'

from student;

Select sname + ‘年龄为’+cast(sage as char(2))+’岁’

From student//(14)利用字符转换函数实现字符联接。

select*

from student order by sdept,sage desc;//(15)查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。

select count(*)

from student;//查询总人数

select count(distinct sno)

from sc;//查询选修了课程的学生人数

select count(*),avg(grade)as avggrade

from student ,sc

where student.sno=sc.sno and sc.cno='1';//(18)查询选修了7号课程的学生总人数和平均成绩

select max(grade) as maxgrade

from sc

where cno='2';//(19)查询选修6号课程学生的最好成绩

select sdept,count(*)

from student group by sdept;//(20)查询每个系的系名及学生人数。

select cno,count(*),avg(grade) as avggrade

from sc group by cno;//(21)查找每门课的选修人数及平均成绩

select *

from course

where cpno is null;//(22)查找没有先修课的课程情况