数据库整理三（表与表之间的关系）；函数

Er图：

方形的表示的实体

椭圆形的表示的是实体类型的属性

菱形表示的是他们之间的关系

1.     表与表之间的关系

一对一、一对多、多对一、多对多

1.1.  多表的意义

一个学生如果有多个成绩，如果只有一个表，如果一个学生有多个成绩，那么存储数据的结构就是如下：

sid	name	address	sex	score
1001	张三	北京昌平	男	99
1001	张三	北京昌平	男	88
1002	李四	北京昌平	男	97

首先不考虑主键的问题，如果按照这样存储数据，那么会出现大量的冗余数据，所以，就需要降score拆分出来，形成一张分数表，这样就能很好的解决数据冗余的问题。

虽然多表可以解决数据冗余的问题，但是在开发中并不是拆分越多的表越好，因为如果表太多，那么我们在查询数据的时候就需要查询很多表，这样开发的成本就比较高。具体的情况需要根据需求确定。

2.     多表查询

多表查询有如下几种：

l  合并结果集：UNION、 UNION ALL

union对查询的结构有去重

union对查询的结果不进行去重|效率较高

l  连接查询

Ø  内连接  [INNER] JOIN ON

Ø  外连接  OUTER JOIN ON

²  左外连接 LEFT [OUTER] JOIN

²  右外连接 RIGHT [OUTER] JOIN

²  全外连接（MySQL不支持）FULL JOIN

Ø  自然连接  NATURAL JOIN

l  子查询

2.1.  合并结果集

1.    作用：UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

2.    合并结果集有两种方式：

l  UNION：去除重复记录，例如：SELECT * FROMt1 UNION SELECT * FROM t2；

l  UNION ALL：不去除重复记录，例如：SELECT* FROM t1 UNION ALL SELECT * FROM t2。

3.    要求：被合并的两个结果：列数、列类型必须相同。

2.2.  连接查询

连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接、自然连接等。通过连接运算符可以实现多个表查询。

2.2.1.    交叉连接

　　使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

注意：在多表查询中，如果两张表中有相同的字段，在使用列时必须指定列所从属的表，例如emp.deptno表示emp表的deptno列，如果没有相同的字段可以不用指定所属的表。

这时就可以指定要查询的列了。

SELECT emp.ename,emp.sal,emp.comm,dept.dname FROM emp,dept WHERE emp.deptno=dept.deptno;

还可以为表指定别名，然后在引用列时使用别名即可。

SELECT e.ename,e.sal,e.comm,d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno;

注意：其中AS是可以省略的（表示给数据库的表明取关键字）

2.2.2.    内连接

       内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

SELECT * FROM student,score WHEREstudent.stuid=score.stuid;

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

注意：INNER可以省略，MySQL默认的连接方式就是内连接，被称为99查询法

注意：不使用WHERE，而是使用ON

内连接的特点：查询结果必须满足条件。例如我们向emp表中插入一条记录：

　　其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“张三”这条记录，因为它不能满足e.deptno=d.deptno这个条件。

2.2.3.    外连接（左连接、右连接）

外连接的特点：查询出的结果存在不满足条件的可能。

2.3.1 左连接

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

注意：OUTER可以省略

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

这么说你可能不太明白，我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

2.3.2　右连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

2.3.  自然连接

大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：

l  两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！

当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

SELECT * FROM emp NATURAL JOIN dept;   内连接 SELECT * FROM emp NATURAL LEFT JOIN dept;   左连接 SELECT * FROM emp NATURAL RIGHT JOIN dept;   右连接

2.4.  子查询

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

l  子查询出现的位置：

Ø  where后，作为条为被查询的一条件的一部分；

Ø  from后，作表；

l  当子查询出现在where后作为条件时，还可以使用如下关键字：

Ø  any

Ø  all

l  子查询结果集的形式：

Ø  单行单列（用于条件）

Ø  单行多列（用于条件）

Ø  多行单列（用于条件）

Ø  多行多列（用于表）

练习：

1.     工资高于JONES的员工。

分析：

查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

第一步：查询JONES的工资

SELECT sal FROM emp WHERE ename='JONES'

第二步：查询高于甘宁工资的员工

SELECT * FROM emp WHERE sal > (${第一步})

结果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES')

2、查询与SCOTT同一个部门的员工。

l  子查询作为条件

l  子查询形式为单行单列

3、工资高于30号部门所有人的员工信息

分析：

SELECT * FROMemp WHERE sal>(

SELECT MAX(sal)FROM emp WHERE deptno=30);

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (${第一步})

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

注意：ALL大于所有

2.5.  自连接:自己连接自己，起别名

求7369员工编号、姓名、经理编号和经理姓名

SELECT e1.empno ,e1.ename,e2.mgr,e2.ename

FROM emp e1, emp e2

WHERE e1.mgr = e2.empno AND e1.empno = 7369;

3.     MySQL中的函数

3.1.  时间日期相关函数

示例：select addtime(‘02:30:30’,‘01:01:01’);          

注意：字符串、时间日期的引号问题

select date_add(entry_date,INTERVAL 2 year)from student; //增加两年

select addtime(time,‘1 1-1 10:09:09’) fromstudent;　//时间戳上增加，注意年后没有-

 

3.2.  字符串相关函数

3.3.  数学相关函数

4.     MySQL数据库的备份与恢复

4.1.  生成SQL脚本导出数据

在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。

mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

注意，mysqldump命令是在Windows控制台下执行，无需登录mysql！！！

4.2.  执行SQL脚本 恢复数据

前提：必须先创建数据库名

执行SQL脚本需要登录mysql，然后进入指定数据库，才可以执行SQL脚本！！！

执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！

SOURCE C:\mydb1.sql

登录到mysql数据库，然后切换下一个数据库。

　　注意，在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！

还可以通过下面的方式来执行脚本文件：

mysql -uroot-p123 mydb1<c:\mydb1.sql

mysql –u用户名 –p密码 数据库<要执行脚本文件路径

注意：这种方式无需登录mysql！在CMD下命令不能加;