按等级查询
create table grade(
low int(3),
upp int(3),
grade char(1)
);
insert into grade values(90,100,‘A’);
insert into grade values(80,89,‘B’);
insert into grade values(70,79,‘C’);
insert into grade values(60,69,‘D’);
insert into grade values(0,59,‘E’);
查询所有学生的sno,cno和grade列
select sno,cno,grade from score,grade where degree between low and upp;
连接查询
内连接
inner join 或者join
外连接
左连接:left join 或 left outer join(效果一样)
右连接:right join 或 right outer join(效果一样)
完全外连接:full join 或 full outer join
新建数据库
create database testJoin
创建两个表:
person表
id
name
cardId
card表
id
name
create table person(
id int,
name varchar(20),
cardId int
);
create table card(
id int,
name varchar(20)
);
insert into card values(1,‘饭卡’);
insert into card values(2,‘建行卡’);
insert into card values(3,‘农行卡’);
insert into card values(4,‘工商卡’);
insert into card values(5,‘邮政卡’);
insert into person values(1,‘张三’,1);
insert into person values(2,‘李四’,3);
insert into person values(3,‘王五’,6);——未创建外键
inner join查询
select * from person inner join card on person.cardId=card.id;
——内连接查询,其实就是两张表中的数据,通过某个字段相等,查询出相关记录数据
left join 查询(左外连接)
select * from person left join card on person.cardId=card.id;
左外连接查询——会把左边表中的所有数据提取出来,而右边表中的数据如果有相等则显示,没有则补空
right join查询(右外连接)
select * from person right join card on person.cardId=card.id;
右外连接查询——会把右边表中的所有数据提取出来,而左边表中的数据如果有相等则显示,没有则补空
mysql不支持full join