1.求一个班级数学平均分。
(1). select sum(math) / count(math) as 数学平均分
from student;
(2). select avg(math) as 数学平均分
from student;
(3).select avg(name) as 小明平均分
from student;//0
2.求一个班级总分平均分。
(1).select (sum(chinese)+sum(math)+sum(english)) / count(*)
from student;
(2).select avg(chinese+math+english)
from student;
3.求班级语文最高分和最低分。
select max(name),min(name)
from student;
drop table if exists teacher;
create table teacher(
id int,
name varchar(20),
birthday date
);
insert into teacher(id,name,birthday) values(1,'jack','2011-1-1');
insert into teacher(id,name,birthday) values(2,'marry','2011-2-2');
insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3');
select max(birthday),min(birthday)
from teacher;
4.对订单表中商品归类后，显示每一类商品的总价
select product as 类别名,sum(price) as 商品类别总价
from orders
group by product;
5.查询购买了几类商品，并且每类总价大于100的商品
select product as 类别名,sum(price) as 商品类别总价
from orders
group by product
having sum(price) > 100;
6.where v.s. having区别：
where主要用于行过滤器
having主要用于类别过滤器，通常有having就一定出现group by,但有group by的地方，不一定出现having。hving可以说是针对结果集在进行查询的。
drop table if exists teacher;
create table teacher(
id int primary key auto_increment,
name varchar(20) not null unique,
birthday date
);
insert into teacher(name,birthday) values(NULL,'2011-1-1');
insert into teacher(name,birthday) values('marry','2011-2-2');
insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3');
select max(birthday),min(birthday)
from teacher;
7.删除主键，主键在表中只有一个，要么是一列，要么是多列
alter table teacher drop primary key;
8.一对一关系(方案一)：
drop table if exists card;
drop table if exists person;
create table person(
id int primary key auto_increment,
name varchar(20) not null
);
insert into person(name) values('jack');
insert into person(name) values('marry');
create table card(
id int primary key auto_increment,
location varchar(20) not null,
pid int,
constraint pid_FK foreign key(pid) references person(id)
);
insert into card(location,pid) values('BJ',1);
insert into card(location,pid) values('GZ',2);
insert into card(location,pid) values('CS',NULL);
insert into card(location,pid) values('NJ',3);//出错
//删除person表的某记录
delete from person where name = 'jack';
9.一对一关系(方案二)：
drop table if exists card;
drop table if exists person;
create table person(
id int primary key auto_increment,
name varchar(20) not null
);
insert into person(name) values('jack');
insert into person(name) values('marry');
create table card(
id int primary key auto_increment,
location varchar(20) not null,
constraint id_FK foreign key(id) references person(id)
);
insert into card(location) values('BJ');
insert into card(location) values('GZ');
insert into card(location) values('CS');//出错
insert into card(location) values(NULL);
10.一对多/多对一关系：
drop table if exists employee;
drop table if exists department;
create table department(
id int primary key auto_increment,
name varchar(20) not null
);
insert into department(name) values('软件部');
insert into department(name) values('销售部');
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
did int,
constraint did_FK foreign key(did) references department(id)
);
insert into employee(name,did) values('jack',1);
insert into employee(name,did) values('marry',1);
11.问题？查询"软件部"的所有员工（组合式）
select d.name as 部门名,e.name as 员工名
from department as d,employee as e
where d.name = '软件部';
思考：还有没有其它方法？
分解：
(1)select id from department where name='软