MySQL快速入门（二）——Java进阶学习第十六天_网站建设

MySQL快速入门（二）——Java进阶学习第十六天

2025-07-22 09:59 点击:0

文档版本	开发工具	测试平台	工程名字	日期	作者	备注
V1.0				2016.05.10	lutianfei	none

SQL多表操作

外键约束

作用：保证数据的完整性。

定义外键约束

可以直接在create语句中定义外键
foreign key 当前表名 (字段名) references 目标表名 (目标表的主键)

创建完语句后，可以直接使用修改语句定义

alter table 表名 add foreign key 当前表名 (字段名) references 目标表名 (目标表的主键);

作用：保证数据的完整性。

例子

alter table emp add foreign key emp(dno) references dept(did); //把dno作为did的外键，且did必须是主键

有一个部门的表，还有一个员工表，

    create database day16;    use day16;    create table dept(        did int primary key auto_increment,        dname varchar(30)    );    create table emp(        eid int primary key auto_increment,        ename varchar(20),        salaly double,        dno int    );    insert into dept values(null,'研发部');    insert into dept values(null,'销售部');    insert into dept values(null,'人事部');    insert into dept values(null,'扯淡部');    insert into dept values(null,'牛宝宝部');    insert into emp values(null,'班长',10000,1);    insert into emp values(null,'美美',10000,2);    insert into emp values(null,'小凤',12000,3);    insert into emp values(null,'如花',14000,2);    insert into emp values(null,'芙蓉',11000,1);    insert into emp values(null,'东东',800,null);    insert into emp values(null,'波波',1000,null);update emp set salaly=2500 where eid = 5;

`数据库的设计`

多表设计中三种实体关系

`一对多表的设计`

一对多:在多方需要添加一个字段，并且和一方主键的类型必须是相同的。

把该字段作为外键指向一方的主键。

建表原则（都是在多方表中维护）：

在多方表中添加一个字段，把该字段作为外键，并且指向一方表中的主键。 将关系的属性及非多方的主标识加入到多方表 多方表的外键是非多方实体的主标识

画图举例部门与员工的关系

`多对多表的设计`

在生活中一个学生可以选择多门课程，每一门课程也可以被多个学生所选择。这些例子在数据库设计中可以使用多对多来完成。

建表原则：

需要创建第三张表，该表中至少有两个字段，作为外键分别指向多对多的双方的主键。 联系的属性及两个实体的主标识形成关系表。 关系表的主键为两个实体主标识的组合 拆开两个一对多的关系，中间创建一个中间表，至少有两个字段。作为外键指向两个多对多关系表的主键。

`一对一表的设计（很少用）`

在生活中，1对1的应用比较少，因为完全可以作为一张表出现。但是有可能公司的业务原因，而需要设计1对1的表结构。例如：一个公司对应一个地址，一个地址也只能对应一个公司。 建表原则：
主键对应
将两个表的主键进行关联 唯一外键对应
在任何一方加一个外键，但是需要设置成唯一（unique），指向另一方的主键

简单购物的网站表结构设计
包含哪些实体？ 用户 订单 商品 分类

`多表查询 —— 笛卡尔积`

笛卡尔积的概念：（了解）

查询的语法 select * from 表A,表B; 返回的结果就是笛卡尔积。

多表查询时，如果不指定表之间的连接条件，则连接变成笛卡尔乘积操作，笛卡尔积的效果如本页所示。

进行笛卡尔积后，查询结果中存在大量无意义的数据，示例中只有A表中的A_ID和B表中A_ID一致的行才有意义，这样我们通过加上WHERE过滤条件得到想要的数据。这种横跨多表的查询操作一般用连接完成。

如果一张表的记录为m，另一张表的记录是n，两个表做交叉连接后，查询结果的数量为m*n条。

`多表查询—内链接`

两个表之间是有联系的，通过一个外键关联 内连接分成两种
普通内连接
前提条件：需要有外键的。 提交关键字 inner join … on select * from dept inner join emp on dept.did = emp.dno; 隐式内连接（用的是最多的）
可以不使用inner join … on关键字 select * from dept,emp where dept.did = emp.dno;

`多表查询—外链接`

左外链接（看左表，把左表所有的数据全部查询出来）

前提条件：需要有外键的。 语法： 使用关键字 left [outer] join … on
select * from dept left outer join emp on dept.did = emp.dno;

右外链接（看右表，把右表所有的数据全部查询出来）

前提条件：需要有外键的。 语法： 使用关键字 right [outer] join … on
select * from dept right join emp on dept.did = emp.dno;

外链接与内连接的关系

`多表查询—子查询`

在sql语言中，select…from…语为一个查询块，将一个查询块嵌套在另一个查询块中作为条件称为嵌套查询,也称为子查询。

外层的查询块称为父查询，内层的查询块称为子查询。

语法：select * from table where 条件 > (select * from table where 条件)

any 任意 all 全部 >any 大于结果的最小值 >all 大于结果的最大值

`基础练习`

create table dept(    did int primary key auto_increment,    dname varchar(30));create table emp(    eid int primary key auto_increment,    ename varchar(20),    salaly double,    dno int);//查看所有人所属的部门名称和员工名称?    //方法一：    select dept.dname,emp.ename from dept,emp where dept.did = emp.dno;    //方法二：    select d.dname,e.ename from dept d,emp e where d.did = e.dno;//统计每个部门的人数(按照部门名称统计，分组group by  count)    select d.dname,count(*) from dept d,emp e where d.did = e.dno group by d.dname;//统计部门的平均工资（按部门名称统计 ，分组group by  avg）    select d.dname,avg(salaly) from dept d,emp e where d.did = e.dno group by d.dname;//统计部门的平均工资大于公司平均工资的部门（子查询）    * 公司的平均工资        select avg(salaly) from emp;    * 部门的平均工资        select d.dname,avg(e.salaly) as sa from dept d,emp e where d.did = e.dno group by d.dname having sa > (select avg(salaly) from emp);

`应用练习`

表结构

DROP TABLE dept;--部门表CREATE TABLE dept(    deptno int  PRIMARY KEY,    dname VARCHAR(14) , --部门名称    loc VARCHAR(13)  ---部门地址    ) ;CREATE TABLE dept(    deptno int  PRIMARY KEY,    dname VARCHAR(14) ,     loc VARCHAR(13)     ) ;    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');    INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');    INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');    INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');drop table emp;--员工表CREATE TABLE emp(    empno int  PRIMARY KEY, --员工编号    ename VARCHAR(10), ---员工姓名    job VARCHAR(9), --员工工作    mgr int, ----员工直属领导编号    hiredate DATE, ----入职时间    sal double, ---工资    comm double, --奖金    deptno int  REFERENCES dept);  --关联dept表CREATE TABLE emp(    empno int  PRIMARY KEY,    ename VARCHAR(10),    job VARCHAR(9),    mgr int,     hiredate DATE,     sal double,    comm double,    deptno int ,    foreign key emp(deptno) REFERENCES dept(deptno));  INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);CREATE TABLE salgrade(     grade int,--等级    losal double, --最低工资    hisal double ); --最高工次CREATE TABLE salgrade(     grade int,    losal double,     hisal double );INSERT INTO SALGRADE VALUES (1,700,1200);INSERT INTO SALGRADE VALUES (2,1201,1400);INSERT INTO SALGRADE VALUES (3,1401,2000);INSERT INTO SALGRADE VALUES (4,2001,3000);INSERT INTO SALGRADE VALUES (5,3001,9999);

1、返回拥有员工的部门名、部门号。

select d.deptno,d.dname from dept d,emp e where d.deptno = e.deptno group by d.deptno;

2、工资水平多于smith的员工信息。

select * from emp e where e.sal > (select em.sal from emp em where em.ename = ‘smith’ );

3、返回员工和所属经理的姓名。

select e.ename,em.ename from emp e,emp em where e.mgr = em.empno and em.job = ‘MANAGER’;

4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。

select e.ename,em.ename from emp e,emp em where e.mgr = em.empno and em.job = ‘MANAGER’ and e.hiredate < em.hiredate;

5、返回员工姓名及其所在的部门名称。

select d.dname,e.ename from dept d,emp e where d.deptno = e.deptno;

6、返回从事clerk工作的员工姓名和所在部门名称。

select d.dname,e.ename,e.job from dept d,emp e where d.deptno = e.deptno and job = ‘clerk’;

7、返回部门号及其本部门的最低工资。

select d.deptno,min(e.sal) from dept d,emp e where d.deptno = e.deptno group by d.deptno;

8、返回销售部(sales)所有员工的姓名。

select * from dept d,emp e where d.deptno = e.deptno and d.dname = ‘sales’;

9、返回工资水平多于平均工资的员工。

select * from emp e where e.sal > (select avg(sal) from emp)；

10、返回与SCOTT从事相同工作的员工。

select * from emp e where e.job = (select job from emp em where em.ename = ‘SCOTT’);

11、返回与30部门员工工资水平相同的员工姓名与工资。

select * from emp e where e.sal in (select e.sal from dept d,emp e where d.deptno = e.deptno and d.deptno = 30);