MySQL实训3_MySQL 实训篇

一、创建student、achievement表

1. create table student(id int unsigned auto_increment primary key,name varchar(30),sex varchar(3) not null default 'nan');

2. create table achievement(id int unsigned auto_increment primary key,result int(3));

3. insert into student(name,sex)

select 'zaho si','nan' from dual union

select 'zaho hong','nv' from dual union

select 'wang hong','nv' from dual union

select 'wang er','nan' from dual union

select 'zaho si','nan' from dual union

select 'feng yi','nan' from dual union

select 'feng yi','nv' from dual;

4. insert into achievement(result)

select 80 from dual union

select 90 from dual union

select 80 from dual union

select 50 from dual union

select 80 from dual union

select 60 from dual union

select 100 from dual;

二、聚合用函数(count，sum，max，min，avg等)的使用

1. 平均成绩： select avg(result) as avg_achievement from achievement;

2. 最好成绩： select max(result) max_achievement from achievement;

3. 'wang'姓人列表：select * from student where name like "wang%";

4. 'wang'姓人数：select count(name) wang from student where name like 'wang%';

注：使用like模糊查询时，%匹配0或多个字符，_匹配单个字符

三、分组聚合查询

1. 男女各多少人： select sex,count(sex) from student group by sex;

四、多表查询

1. 列出成绩大于等于80的学生信息：

①select student.id,student.name,student.sex,achievement.result from student,achievement where student.id=achievement.id and achievement.result >= 80;

② select student.id,name,sex,result from student join achievement on student.id=achievement.id and result >= 80;

(注：内连接强制要求两个表必须存在公共列，带来便利性的同时也带来了不灵活。内连接可以自行指定连接列和连接条件。)

2. 列出成绩最好的学生姓名：

select name,max(result) from student left join achievement on student.id=achievement.id;