mysql内连接查询练习_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整个删除