sql1复习笔记11

sql1复习笔记11

1.查询练习-子查询

查询和zng3hua同性别的所有同学的sname

select ssex from student where sname='zng3hua';
select sname from student where ssex=(select ssex from student where sname='zng3hua');

2.查询练习-子查询

查询和李军同性别并且同班

select sname from student where ssex=(select ssex from student where sname='zng3hua') and class=(select class from student where sname='zng3hua');

3.查询练习-子查询

查询所有选修计算机导论课程的男同学的成绩表

select cno from course where cname='计算机导论';
select * from score where cno=(select cno from course where cname='计算机导论'); 
select * from score where cno=(select cno from course where cname='计算机导论') and sno in (select sno from student where ssex='nan'); 

4.查询练习-按等级查询

假设使用如下命令建立了一个grade表
查询所有同学的sno、cno和rank序列

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');

select sno,cno,grade from score,grade where degree between low and upp;

sql是从上到下逐层做数据的。

5.连接查询-内连接、左连接、右连接

sql的四种连接查询
内连接、外连接（左连接、右连接）、完全外连接。

1. 内连接
   inner join 或者join

2. 外连接
   （1）左连接 left join 或者 left outer join
   （2）右连接 right join 或者 right outer join

3. 完全外连接 full join 或者 full outer join

4. 内连接

create database testJoin;
use testJoin;
create table person(
	id int, 
	name varchar(20),
	cardId int
);
create table card(
	id int,
	name varchar(20)
);
insert into card values(1,'饭卡'),(2,'建行卡'),(3,'农行卡'),(4,'工商卡'),(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;
//内连接两个表，相当于把两个表里有关系的数据查询出，一定会有一个数据相等对应。
select * from person join card on person.cardId=card.id;
//去掉inner效果差不多

2. 外连接

select * from person left join card on person.cardId=card.id;
select * from person left outer join card on person.cardId=card.id;
//把左边的person表的数据全部查询，右边有就出、没有就补null。
select * from card right join person on person.cardId=card.id;
select * from card right outer join person on person.cardId=card.id;
//把右边的person表的数据全部查询，左边有就出、没有就补null。

3. 完全外连接

select * from person full join card on person.cardId=card.id;

mysql 不支持全外连接。