9.数据库-查-多表操作

9.数据库-查-多表操作

1.外键

实际开发中,健壮的数据库一定要有很好的参照完整性,为了保证数据的完整性,这里将两表之间数据建立关系,所以要在表中添加外键约束:

1.1什么是外键?

外键是指引用另一个表的一列或多列,被引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的连接。

引入外键后,外键列只能插入参照列存在的值,参照列被参照的值不能直接被删除,这就保证了数据的完整性。

 

1.2 为表创建外键

为表添加外键的格式如下:

alter table 表名 add constraint FK_ID foreign key(外键字段名) PEFERENCES 外表表名(主键字段名);

 

创建 班级 和 学生 表:

CREATE TABLE class(

cid int(4) NOT NULL PRIMARY KEY,

gname VARCHAR(10)

);

 

CREATE TABLE student(

sid int(4) NOT NULL PRIMARY KEY,

sname VARCHAR(10)

);    

 

然后我们为他添加外键约束:

ALTER TABLE student ADD CONSTRAINT FK_ID FOREIGN KEY(sid) REFERENCES class(cid);

你可以用show create table 表名;  语句查看主从关系!

可以看出 sid是student表的外键,并且呢 sid外键依赖于class表中的cid主键,这样两个表就通过外键关联起来了。

 

需要注意的地方:

(1)           建立的外键表必须是InnoDB型,不能是临时表,因为在Mysql中 InnoDB的表才支持外键。

(2)           定义外键名时,不能加引号,如:

constraint ‘FK_ID’ 或 constraint “FK_ID” 都是错误的。

 

多学一招:添加外键约束的参数说明:

如果主表中的数据被删除或修改,从表中对应的数据该怎么办?

很明显也应该被删除~    否则会导致很多垃圾数据。

MYSQL可以在建立外键的时候添加 ON DELETE 或 ON UPDATE

子句来告诉数据库,怎么避免产生垃圾数据。

语法格式如下:

 

Alter table 表名 add constraint FK_ID foreign key(外键字段名) REFERENCES 外表表名(主键字段名);

[ ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT }  ]

 

添加外键约束的参数说明

参数名称

功 能 描 述

CASCADE

删除包括与已删除键值有参照关系的所有记录

SET NULL

修改包含与已删除健值有参照关系的所有记录,使用NULL值替换(前提是不能用于 NOT NULL 的字段!)

NO ACTION

不进行任何工作

RESTRICT

拒绝主表删除或修改外键关联列。

 

1.3删除外键约束

按照业务逻辑需求,需要解除两个表之间的关联关系时,就需要删除外键约束,删除外键约束的语法如下:

alter table 表名 drop foreign key 外键名;

例如:删除student表的外键:

ALTER TABLE student DROP FOREIGN KEY FK_ID;

 

 

 

2.关联关系

  1.根据实体的内容设计数据库,实体间会有各种关联关系。所以根据实体设计的数据表之前也存在着各种关联关系,MySql中有三种:

(1)           多对一

多对一是数据表最常见的一种关系。比如员工和部门之间的关系,一个部门可以有多个员工,而一个员工不能属于多个部门,也就是说部门表中的 “一行” 在员工表可有许多匹配行,但员工表中的一行在部门表中只能有一个匹配项。

所以我们讲外键时,在多对一的表关系中,应该把外键建在多的一方,否则会照成数据冗余。

(2)           多对多

比如学生与课程之间的关系,一个学生可以选择多门课程,当然一门课程可以供多个学生选择,也就是说学生表的一行在课程表中能有许多匹配项,课程表表中的一行在学生表中也有许多匹配项。

(3)           一对一

一对一在生活中最常见,比如人和身份证之间就是一对一的关系,一个人对应一张身份证,一张身份证匹配一个人。

      首先我们要弄懂主从关系,从表需要主表的存在才有意义,身份证需要有人的存在才有意义,因此人是主表,身份证是从表。

要在身份证建立外键。身份证的外键必须是非空唯一的,例如身份证号码是外键,那么就该如此,所以呢 一般都是用 从表的 主键座位外键进行约束。

 

在实际开发中 , 一对一并不常见,因为用这种关系存的话 一般会放在一个表中,就不用约束了。

一对一常见于几个方面:

  1. 分割具有很多列的表
  2. 由于安全原因隔离表的一部分
  3. 保证临时的数据,且可以毫无费力的通过删除来删除这些数据。

 

 

2.添加数据

实际开发,最常见的就是多对一关系,我们上面有一个class  和

Student 表了  我们添加约束:

ALTER TABLE student add CONSTRAINT FK_ID FOREIGN KEY(sid) REFERENCES class(cid);

 

此时表student 和 class 之间是多对一的关系。外键列只能插入参照列存在的值,所以如果要吧数据添加到student【添加学生】的话,要先添加class【班级】的表,你可以试一下先添加学生,会报错的.

 

 

 

  1. 删除数据

删除数据你得先删除从表的数据 才能删除主表的数据,因为他们是关联的。例如:软件1班 不开了,你得先删除软件1班的学生,才能把软件1班删除。

实际开发中不需要这样干,你可以查询到一班的学生,使他们的班级为null,软件1班没参照了,自然可以把软件1班 删除。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3.连接查询

在实际开发中,一般都是把每个实体的所有信息存放在一个表中,当两个或多个表中存在相同意义的字段时,便可以通过这些字段对不同的表进行连接查询,连接查询包括交叉查询、内连接查询、外连接查询、下面针对连接查询讲解:

  1. 1.     交叉查询

交叉查询返回的结果是被连接的两个表的笛卡尔积,也就是表1 符合条件的行数 * 表2符合条件的行数。例如department 有4个部门,employee有4个员工,那么交叉连接的结果就有 4*4=16条数据。

交叉连接的语法格式如下:

SELECT * FROM 表1 CROSS JOIN 表2;

其中:  CROSS JOIN 用于连接两个要查询的表,通过该语句可以查询量表中全部数据组合。

先创建department【部门】表 和  employee【雇员】表:

CREATE TABLE department(

did int(4) NOT NULL PRIMARY KEY,

dname varchar(36)

);


CREATE TABLE employee(

id int(4) NOT NULL PRIMARY KEY,

name VARCHAR(36),

age int(2),

did int(4) NOT null

);


INSERT INTO department(did,dname)VALUES(1,'网络部');

INSERT INTO department(did,dname)VALUES(2,'媒体部');

INSERT INTO department(did,dname)VALUES(3,'研发部');

INSERT INTO department(did,dname)VALUES(5,'人事部');

 

INSERT INTO employee (id,name,age,did)VALUES(1,'王强',20,1);

INSERT INTO employee (id,name,age,did)VALUES(2,'李红',22,1);

INSERT INTO employee (id,name,age,did)VALUES(3,'赵四',20,2);

INSERT INTO employee (id,name,age,did)VALUES(4,'恒娟',20,4);

创建了两个 和 插入了数据↑

现在我们用交叉连接试一下:

SELECT * FROM department CROSS JOIN employee;

我们可以看到,结果是部门 和 雇员表的结合,实际开发不会这样,但是会结合数据条件进行目的信息查询。

 

 

 

  1. 2.      内连接

内连接(Inner Join)又叫简单连接或自然连接,是一种常见的连接查询,内连接用运算符对两个表的数据进行比较,并列出与连接条件匹配的数据行,组成新的记录,换句话说:

-内连接只有满足条件的记录才能出现查询结果中,语法格式如下:

SELECT 查询字段 FROM 表1[INNER] JOIN 表2 ON 表1.关系字段=表2关系字段;

其中:

INNER JOIN 用于连接两个表 ON来指定连接条件,其中 INNER 可以省略:

 

例1 在department表 和 employee表 之间用内连接查询:

SELECT employee.name,department.dname FROM department JOIN employee

ON department.did = employee.did;

 

从查询结果可以看出,只有 department.did 和 department.did 一样的时候,才会被显示。

 

例2: 还可以用WHERE条件语句来写:【其实就是 ON 换成  WHERE】

SELECT employee.name,department.dname FROM department,employee WHERE department.did = employee.did;

虽然用 ON或 用WHERE 查询出来的结果是一样的,但是用WHERE 可以 不用加 JOIN 直接在FROM 后 用逗号分隔表,然后直接WHERE 添加条件即可。

 

如果在一个连接查询中,涉及的两个表是同一个表,这种查询称为自连接查询,自连接查询是一个特殊的内查询。

例如:查询李红所在的部门中还有其他什么人:【别名 方法】

SELECT p1.* FROM employee p1 JOIN employee p2 ON p1.did = p2.did WHERE p2.name='李红';

 

如果你看不懂 ,那么尝试把 p1 去掉:

SELECT * FROM employee p1 JOIN employee p2 ON p1.did = p2.did WHERE p2.name='李红';

 

                

这题一定要懂啊! 主要还是  他自连接会有两个表. 【自连接一定要别名来查询】

 

 

  1. 3.      外连接

内连接只会查询包含符合查询条件 和 连接条件的数据,但是很多时候都包含很多没必要的数据,所以:      外连接查询、分为 左连接查询 和 右连接查询:

外连接查询格式语法如下:

SELECT 所查字段 FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2

ON 表1.关系字段=表2.关系字段 WHERE 条件;

语法和内连接相似,只不过使用的是 LEFT JOIN 、 RIGHT JOIN 关键字,其中关键字左边的表被称为左表,关键字右边的表被称为右表。

 

左、右 查询的结果是不一致的:

(1)LEFT JOIN (左连接) 返回包括左表中所有的记录和右表中符合条件的记录。

(2)RIGHT JOIN (右连接) 返回包括右表中所有的记录和左表中符合条件的记录。

 

  1. LEFT JOIN 左连接

左连接如果左表某条记录不存在,那么在右表中显示为空(NULL)

例: 在department表 和 employee表 之间使用左连接查询:

SELECT department.did,department.dname,employee.name FROM department

LEFT JOIN employee ON department.did = employee.did;

结果:

did dname    name


1     网络部   王强

1     网络部   李红

2     媒体部   赵四

3     研发部   NULL

5     人事部   NULL

在结果可以看出,研发部 和 人事部 在 employee【雇员】表中 根本没人在其中。

 

  1. RIGHT JOIN 右连接

和左连接正好相反:右连接如果右表某条记录不存在,那么在右表中显示为空(NULL)

例: 在department表 和 employee表 之间使用右连接查询:

SELECT department.did,department.dname,employee.name FROM department

RIGHT JOIN employee ON department.did=employee.did;

结果:

did dname    name


1     网络部   王强

1     网络部   李红

2     媒体部   赵四

NULL NULL   恒娟

可以看出 是和左连接相反的 ,但是查询的数据是:

employee【雇员】表中 只有恒娟是没在 department【部门】表中的。

                                                                         

4.复合条件连接查询

复合条件连接查询是在连接查询的过程中,通过添加过滤条件来限制查询结果,使得查询更加精确。

例:

在 部门表 和 雇员表中用内连接查询,并且查询结果按照年龄从小排到大:

SELECT employee.name,employee.age,department.dname FROM department JOIN employee

ON employee.did=department.did ORDER BY age ;

 

直接在最后加上   所以他就会从按照年龄小排到大了。

 

 

 

 

4.子查询

1.带IN关键字的子查询

带IN关键字进行子查询时,内层查询语句仅返回一个数据列,这个数据列中的值将供外层查询语句进行比较操作。

 

例: 查询存在年龄20岁的员工的部门   【即 20岁员工 所在的 部门】:

SELECT * FROM department WHERE did IN(SELECT did FROM employee WHERE age=20);

你可以分开看  先看括号的子查询 返回括号返回值出来 在配合外层查询。

 

还可以用 NOT IN 呢 , NOT IN 这里就不多说了。

 

 

2.带EXISTS 关键字的子查询

EXISTS 后面可以是任意一个子查询,这个子查询作用相当于测试,他不产生任何数据,他只返回 TURE / FALSE ,当返回 TRUE时,外层的查询才会执行。

 

例: 如果 雇员表中存在年龄大于21 的员工,若存在,则打印整个表,否则不打印:

SELECT * FROM employee WHERE EXISTS(SELECT did FROM  employee WHERE age > 21);

其中: 因为子查询有数据返回 所以会返回 TRUE 然后 整个表达式为真 所以打印了employee 的表。

 

  1. 3.      带ANY关键字的子查询

ANY关键字表示满足任意一个条件,它允许创建一个表达式对子查询的返回值列表进行比较,只要满足内层子查询的任意一个比较条件,就返回一个结果作为外层条件。

例:使用带ANY关键字的子查询 查询满足条件的部门:

SELECT * FROM department WHERE did > ANY (SELECT did FROM employee);

其实就是说 department 的 did 要是 大于 employee中的did【其中一个】,就显示。

 

  1. 4.      带ALL关键字的子查询

All关键字和ANY关键字有点相似,只不过带ALL关键字的子查询返回的结果需同时满足所有内层查询条件、

例:使用带ALL关键字的子查询,查询满足条件的部门:

SELECT * FROM department WHERE did > ALL(SELECT did FROM employee);

它的意思就是说department表中的 did ,如果大于 employee中的did【全部】就显示。

 

  1. 5.      带比较运算符的子查询

前面讲的 ANY 和  ALL 都用了 > 比较运算符,子查询还可以用其他的比较运算符,例如: < 、 >= 、 = 、 != 等…

 

例:用带比较运算符的子查询,查询赵四是哪个部门的员工,SQL语句如下:

SELECT * FROM department WHERE did =  (SELECT did FROM employee WHERE employee.name = '赵四');

 

所以 自己去多尝试即可:

 

 

 

完。

上一篇:Oracle高级子查询


下一篇:数据库子查询-select后面语句