易布莱特
易布莱特
5月前 · 4 人阅读

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;

收藏 0
select score where emp 查询 stud
评论 ( 0 )