超牛牪犇Java之数据库查询PLUS
1.创建外键的第二种方式
创建两个表:
create table student(sid int primary key,
sname varchar(20)
);
create table score(
sid int,
score int
);
给score表添加外键:
alter table score add constraint fk_student_score_sid foreign key(sid) references student (sid);
删除时要使用约束的别名来删除(用drop)
一个表可以有多个外键 需要使用约束名字
注意:约束名(外键名)不能重复
ALTER table score drop foreign key fk_student_score_sid ;
2.表与表之间的关系
1.多对多的关系(利用第三张表来表示关系的)
并且第三张表作为从表 拥有其他两个主表的外键
example:创建老师表 学生表 中间表 并建立多对多的关系
create table teacher(
tid int primary key,
tname varchar(20)
);
create table student(
sid int primary key,
sname varchar(20)
);
create table middle(
tid int,
sid int
);
alter table middle add constraint fk_teacher_middle_tid foreign key (tid) references teacher (tid);
alter table middle add constraint fk_teacher_middle_sid foreign key (sid) references student (sid);
2.一对一关系
不常用 完全可以写成一张表 如果考虑查询效率问题 可以对表进行拆分(但是要拆分有度)
create table A(
name varchar(10),
score int
);
create table B(
name varchar(10),
score int
);
insert into A values('a',10),('b',20),('c',30);
insert into B values('a',10),('b',20),('d',40);
2.1 合并查询:(union关键词)
取两个表的并集(字段名 类型 都要相同)
union all 是把两个表的数据合并到一起
select * from A UNION select * from B;
2.2多表查询
这样查询会产生笛卡尔积(会产生大量的无用数据)
select * from A,B;
如何去除错误数据?
利用两张表的编号相同去除
99查询法
查询学生编号和学生的信息
select student.stuid,score.score from student,score where student.stuid=score.stuid;
别名查询(给表起个别名)
select s.stuid,c.score from student s,score c where s.stuid=c.stuid;
创建一个科目表
create table course(
courseid int,
cname varchar(20)
);
3个表查询:
select s.stuname,c.score,o.cname from student s,score c,course o where s.stuid=c.stuid && c.courseid=o.courseid;
3.连接查询(多表查询)
内连接(inner 可省略 on 后面是去除重复数据的条件)
select * from student s inner join score c on s.stuid=c.stuid;
3表内连接查询
select * from student s join score c on s.stuid=c.stuid join course o on c.courseid=o.courseid;
example:查询表中80分以上学生的信息 姓名 分数 科目
select s.stuname,c.score,o.cname from student s join score c on s.stuid=c.stuid join course o on c.courseid=o.courseid where score>80;
外连接(左外连接 右外连接)
关键词outer 可以省略
左外连接 就是以左边的表为主 会查询出左边表的所有数据 右外连接反之
example:学生和分数表
select * from student left join score on student.stuid=score.stuid;
4.自然连接
关键词 natural join
自动匹配两个表中相同字段的值
要求:字段名和类型相同
5.子查询(嵌套查询)
用员工表和部门表来测试
example:查询工资高于JONES的员工信息
select * from emp where sal>(select sal from emp where ename='jones');
example:查询与SCOTT同一个部门的员工
select * from emp where deptno=(select deptno from emp where ename='scott');
example:工资高于30号部门所有人的员工信息
select * from emp where sal>(select MAX(sal) from emp where deptno=30);
example:查询工作和工资与MARTIN完全相同的员工信息:
方式一:
select * from emp where job=(select job from emp where ename='martin') && sal=(select sal from emp where ename='martin');
方式二:
select * from emp where (sal,job) in (select sal,job from emp where ename='martin');
example:查询有两个以上直接下属的员工信息
(思路:按mgr分组 筛选出两个以上的) select mgr,COUNT(mgr) from emp GROUP BY mgr HAVING COUNT(mgr)>=2;
然后查询员工信息
select * from emp where empno in (select mgr from emp GROUP BY mgr HAVING COUNT(mgr)>=2);
查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
select e.ename,e.sal,d.dname,d.loc from emp e,dept d where d.deptno=e.deptno && e.empno=7788;
example:求各个部门薪水最高的员工所有信息:
(把查询结果当做一张表来连接查询)
SELECT * from emp e1,(select deptno,MAX(sal) m from emp GROUP BY deptno) e2 where e1.sal=e2.m && e1.deptno=e2.deptno;
example:求7369员工编号、姓名、经理编号和经理姓名
自连接查询
要查找的信息在一张表中 但是一次查不出来
要把自己这个表当做两个表来连接查询
select * from emp e1,emp e2 where e1.empno = e2.mgr and e2.empno=7369;