mysql -- 练习3

创建表stu01 ，并插入数据

drop table if exists  stu01;
create table stu01(
studentno int(3) UNSIGNED zerofill primary key,
name VARCHAR(10),
sex VARCHAR(3),
age TINYINT ,
hometown VARCHAR(20),
class VARCHAR(10),
card VARCHAR(30)
);

insert into stu01 values 
('1','王昭君','女',20,'北京','1班','31162100000910018'),
('2','诸葛亮','男',18,'上海','2班','87235958214647020'),
('3','刘玄德', '男',24,'南京','1班','98230764940128343'),
('4','白起','男',22,'安徽','4班','72898231636783201'),
('5','大乔','女',19,'河南','3班','12872396253622358'),
('6','孙尚香','女',18,'河北','1班','32276853291927386'),
('7','百里玄策','男',20,'山西','2班','69283154237972492'),
('8','小乔','女',18,'河南','3班',NULL),
('9','百里守约','男',22,'河南','2班',''),
('10','妲己','女',19,'陕西','1班','77538912786375823'),
('11','李白','男',24,'山东','3班','13467923823989730'),
('12','刘禅','男',18,'陕西','4班','13729023873820233');

创建表stu02， 并插入数据

drop table if exists stu02;
create table stu02(
id  int primary key ,
courseno  int(2) UNSIGNED zerofill ,
studentno int(3) UNSIGNED zerofill ,
score int 
);

insert into stu02 values
(1,  1, 1, 91),
(2,  5, 2, 85),
(3,  3, 1, 74),
(4,  1, 2, 91),
(5,  4, 3, 82),
(6,  1, 4, 93),
(7,  2, 1, 91),
(8,  1, 6, 85),
(9,  5, 1, 78),
(10, 1, 5, 82),
(11, 3, 5, 83),
(12, 1, 3, 89),
(13, 4, 1, 71);

创建表stu03， 并插入数据

drop  table if exists stu03;
create table stu03(
courseno int(2) UNSIGNED zerofill primary key,
name varchar(30)
);

insert into stu03 values 
('1', '数据库'),
('2', 'linux'),
('3', '系统测试'),
('4', '单元测试'),
('5', '离散数学'),
('6', '高数'),
('7', '英语'),
('8', '物理');

1.查询班级学生的平均年龄

select avg(age) from stu01;

2.查询大于平均年龄的学生

select * from stu01 where  age>(select avg(age) from stu01);

3.查询王昭君数据库的成绩，要求显示成绩

select * from stu02 where studentno = (select studentno from stu01 where name = '王昭君') 
and courseno = (select courseno from stu03 where name = '数据库' );

4.查询18岁学生的成绩，要求显示成绩

-- 4.查询18岁学生的成绩，要求显示成绩
/* 查询年龄为18的学号*/   select studentno from stu01 where age = '18';
/*查询年龄为18的行为学生成绩*/select score from stu02 where studentno IN (002,006,008,012);

SELECT score from stu02 where studentno  in (select studentno from stu01 where age = 18);

5.查询男生中年龄最大的信息 

	/*查询最大的年龄*/  select max(age) from stu01;
select * from stu01 where  sex = '男' and age = (select max(age) from stu01);

select * from stu01 where (sex,age) = (select sex,age from stu01 where sex ='男' and age = (select max(age) from stu01 where sex = '男') ); /*这种如果最大年龄不止一个不能使用*/

select * from stu01 where (sex,age) = (select sex,age from stu01 where sex ='男' order by age desc limit 1);

 6.查询数据库和系统测试的课程成绩

select courseno from stu03 where name in ('数据库','系统测试'); /*一个变量等于号只能取一个值  多个值用in*/
select * from stu02 where stu02.courseno in(select courseno from stu03 where name in ('数据库','系统测试'));/*这种课程名没显示看起来不直观*/

select * from stu02 INNER JOIN (select courseno from stu03 where name in ('数据库','系统测试')) as k
on stu02.courseno = k.courseno;/*加上了课程号没有课程名*/

 

select * from stu02 inner JOIN stu03 on stu03.courseno = stu02.courseno and stu03.name in ('数据库','系统测试');
/*这种有课程名很明显看出*/