MySQL_小练习（内外连接、主键设置）
小练习（内外连接、主键设置）
1、创建学生表
create table student(
id int not null auto_increment,
name varchar(20) not null,
sex char(1),
submission_date date,
primary key(id)
)engine=innodb character set utf8;
***********************************************
2、创建年级表
create table grade(
id int not null auto_increment,
stuid int not null,
class varchar(20) not null,
grade int(3),
primary key(id)
)engine=innodb character set utf8;
******************************************************
3、插入数据
insert into student (name,sex,submission_date) values("张三","男","2010-10-10");
insert into student (name,sex,submission_date) values("李四","男","2010-10-10");
insert into student (name,sex,submission_date) values("王五","男","2010-10-10");
insert into student (name,sex,submission_date) values("赵六","男","2010-10-10");
insert into student (name,sex,submission_date) values("孙七","男","2010-10-10");
insert into grade (stuid,class,grade) values(1,"计算机","100");
insert into grade (stuid,class,grade) values(1,"guitar","90");
insert into grade (stuid,class,grade) values(1,"美术","80");
insert into grade (stuid,class,grade) values(2,"美术","80");
insert into grade (stuid,class,grade) values(2,"计算机","70");
insert into grade (stuid,class,grade) values(2,"guitar","60");
insert into grade (stuid,class,grade) values(3,"guitar","50");
4、查询每个学生的总成绩
select sum(grade),stuid from grade group by stuid;
select stuid,avg(grade) from grade group by stuid;
5、多表查询
select a.name,sum(grade) from student a,grade b where a.id = b.stuid group by b.stuid;
6、内连接
select a.name,sum(grade) from student a inner join grade b on a.id = b.stuid group by b.stuid;
左连接右连接
select a.name,sum(grade) from student a left join grade b on a.id = b.stuid group by b.stuid;
select a.name,sum(grade) from student a left join grade b on a.id = b.stuid group by a.id;
(1)内连接：
a表的id在b表的stuid出现，则数据会显示在结果中
(2)左连接：
a表的id在b表的stuid出现的话显示相关b表数据，
若不出现在b表，则相关b表的数据列显示null.
(3)右连接：
b表的stuid在a表的id出现的话显示相关a表数据，
若不出现在a表，则相关a表的数据列显示null
7、limit
显示前几条
select a.name,sum(grade) from student a left join grade b on a.id = b.stuid group by a.id limit 3;
跳过1条，显示1条
select a.name,sum(grade) from student a right join grade b on a.id = b.stuid group by b.stuid order by sum(b.grade) desc limit 1,1;
8、union all和union的区别
Unio nall不会去重：
select id from student union all select stuid from grade;
Unio会去重 ：
select id from student union select stuid from grade;
9、主键
create table a (id int ,
primary key(id)
);
*****************************************************
create table b (id int,
primary key(id),
CONSTRAINT FK_ID FOREIGN KEY(id) REFERENCES a(id)
);
a表的id是b表的外键
a表的字段
insert into a values(1);
delete from b where id =2;
insert into b values(2);
b的字段必须的在a的字段里
10、explain 扫描b表的详细信息
explain select * from b;
11、trucate（删除）
trucate删除索引数据，表还在
delete清空数据，索引和表还在
drop整个删除
