创建外键的第二种关系
CREATE table student(
sid int primary key,
sname varchar(20)
);
create table score(
sid int,
score int
);
添加外键
alter table score add constraint fk_student_score_sid foreign key (sid) REFERENCES student(sid);
-- 删除时要使用约束的别名来删除
一个表可以有多个外键需要使用约束名字
约束名不能重复
alter table score drop foreign key fk_student_score_sid;
表和表之间的关系
一对一 不常用
一对多
多对多(利用第三张张表表示关系 第三张表作为从表 拥有其他两个主表的外键)
create table student(
sid int primary key,
sname varchar(20)
);
create table teacher(
tid int primary key,
tname varchar(20)
);
create table dsf(
sid int,
tid int
);
alter table dsf add constraint fk_teacher_dsf_tid foreign key(tid) REFERENCES teacher(tid);
alter table dsf add constraint fk_student_dsf_sid foreign key(sid) REFERENCES student(sid);
合并查询
union取两个表的并集(字段名 类型相同)
union all 把两个表的数据合并到一起
select * from A union all select * from B;
多表查询
这样查询会产生笛卡尔积(产生大量的无用数据)
A(a,b) B(0,1,2)
select * from A,B;
学生编号和分数
去除错误数据 利用两张表的编号相同去除
select * from student,score where student.stuid=score.stuid;
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.stuid,
c.score,
o.cname
FROM
student s,
score c,
course o
WHERE
s.stuid = c.stuid
AND c.courseid = o.courseid;
连接查询(多表)
内连接(inner 可以省略)
on后面是去除重复数据的条件
select * from student s inner join score c on s.stuid=c.stuid;
select * from student s join score c on s.stuid=c.stuid join course o on c.courseid=o.courseid;
查询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
c.score > 80;
外连接 左外连接 右外连接
关键字outer可以省略
学生和分数表
左外连接以左边的表为主 查询出左边的表的所有数据
select * from student left join score on student.stuid=score.stuid;
自然连接 关键字natural
自动匹配两个表中相同字段的值
要求字段名和类型相同
select * from student natural join score ;
子查询(嵌套查询)
员工表和部门表测试
查询工资高于jones的员工信息
select * from emp where sal>(select sal from emp where ename='jones');
查询与Scott同一个部门的员工
select * from emp where deptno=(select deptno from emp where ename='scott');
查询工资高于30号部门所有人的信息
select * from emp where sal>(select max(sal) from emp where deptno=30);
查询工作和工资与martin完全相同的
1.
select * from emp where(sal,job) in (select job,sal from emp where ename='martin');
2.
select * from emp where job=(select job from emp where ename='martin')
and sal=(select sal from emp where ename='martin');
有2个以上直接下属的员工信息
select * from emp where empno in(select mgr from emp group by mgr having count(mgr) >2);
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 e.empno=7788 and d.deptno=e.deptno;
自连接查询
查找的东子在一张表中 把自己这张表单过两个表来连接查询
求7369员工编号、姓名、经理姓名
select e.empno,e.ename,p.ename from emp e,emp p where p.empno=7369 and e.empno=p.mgr;
求各个部门薪水最高的员工所有信息
把查询出来的结果当做一张表
select deptno,max(sal) from emp group by deptno ;
select * from emp e,(select deptno,max(sal) msal from emp group by deptno ) p
where e.sal=p.msal and e.deptno=p.deptno;
作业
create table employee(
num int,
name varchar(20),
gender varchar(20),
age int,
departmentno varchar(20)
);
create table wage(
no int,
amount int
);
create table attend(
num int,
no int,
attendance int,
attendance2 int,
attendance3 int,
attendance4 int
);
查询工资金额为8000的职工工号和姓名。
SELECT
e.num,
e. NAME
FROM
wage w
JOIN attend a ON w. NO = a. NO
JOIN employee e ON a.num = e.num
WHERE
w.amount = 8000;
查询职工chenggang的出勤率。
SELECT
a.attendance
FROM
attend a
JOIN employee e ON a.num = e.num
WHERE
e. NAME = 'chenggang';
查询职工chenggang的工资。
SELECT
w.amount
FROM
wage w
JOIN attend a ON w. NO = a. NO
JOIN employee e ON a.num = e.num
WHERE
e. NAME = 'chenggang';
查询3次出勤率为0的职工姓名和工号。(错误 之后改)
select num from attend group by num having(count(attendance)) ;
select e.num， e.name from employee e
where (select count(*) from attend a
where a.num=e.num and attendance=0 or attendance2=0 orattendance3=0 orattendance4=0 ) = 3;
查询出勤率(attendance第一个月)为10 并且工资小于11000的员工信息
SELECT
*
FROM
employee e
JOIN attend a ON e.num = a.num
JOIN wage w ON a. NO = w. NO
WHERE
a.attendance = 10 and w.amount<11000
;