MySQL--多表关联查询笔记

zhang.504

于 2024-05-24 14:52:56 发布

阅读量711

点赞数 19

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_65476290/article/details/139174599

版权

今日目标

为什么要进行多表设计
多表的关联查询
数据库函数
数据库日志和备份
视图和索引

多表关联查询

分表的必要性

为什么要设计多张表？不把数据存储在一张表上呢？如果把所有数据放在一张表中，可能存在如下问题

数据重复，冗余
数据不具备唯一性，数据出错
数据不再准确

因此，在开发时，往往要求在设计表时，遵循数据库设计的3NF

要求每个列都不可以再拆分。即数据库表的每一列都是不可分割的原子项，换句话说，每一列都是最小的数据单元，不可再分
在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分（主要针对联合主键而言）。即，表中的所有非主键字段必须完全依赖于主键，不能只依赖于主键的一部分【主键问题】
在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。即，非主键字段之间没有依赖关系，非主键字段必须直接依赖于主键，不能间接依赖。【分表的重要性，也就是外键】

外键：一个独立字段，该字段一般是用来管理当前表和另外一张表的关联的关系：

如部门和员工的关系、老师和学生的关系、学生和课程、老师跟课程

表与表之间的关联关系

如果对应关系是 1: 1，则外键可以放在任意张表
如果对应关系是n:1，主键一定要放在多（N）方
如果是N：N关联关系，一般外键放在任何一方都会存在冗余，所以一般都是使用第三张表来表示这两张表的关联关系

多表关联查询

准备阶段，准备一个部门表和员工表

CREATE TABLE `dept` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `intro` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


CREATE TABLE `emp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int DEFAULT '18',
  `tel` varchar(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `dept_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  -- 在创建数据库的同时，添加外检约束
  FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

多表管理查询

交叉连接（cross join）
内连接（inner join）
外连接（outer join）
- 左外连接
- 右外连接
- 全外连接【MySQL不支持】
自然链接【nature join】
自连接

交叉连接—cross join

直接连接，因为没有指定对应的关联关系，所以会全部匹配，会触发笛卡尔积现象。

select *
from tablename
where ……

select *
from emp, dept;


-- sql98标准下

select *
from emp cross join dept;

在开发中，决定不允许使用交叉连接！！！！

内连接（inner join）

指定了两张或者多张表之间的关联关系，只会正确展示数据（存在非空关联字段不展示）。

select e.*, d.name as deptName, d.intro from emp e, dept d where e.dept_id = d.id;


-- sql98

select e.*, d.*, d.name as deptName
from emp as e inner join dept d on(e.dept_id=d.id);

内容回顾

软件设计，为什么要将数据存储多张表上
多张表，如果存在着关联关系，如何表示这种关联关系——外键（foreign key）
如何设计外键，三种关联关系
多表关联查询——交叉连接（cross join）、内连接（inner join）

注意：内连接无法查询关联字段为空的值。

外连接

外连接是相对于内连接而言的，可以查询到关联字段为空的数据。

左外连接（left outer join）：将join左侧的表的所有数据展示出来，如果没有对应数据，则以null填充

右外连接（right outer join）：将join右侧的表的所有数据展示出来，如果没有对应数据，则以null填充

全外连接（full outer join）: MySQL数据库不支持全外连接查询，oracle支持

自然连接（nature join）

不太建议使用这种连接。自然是通过字段名称相同这个特点进行自动关联外键查询，所以要求设计阶段就要考虑查询问题，对应软件设计要求相当高，开发中并不实用。

自连接

比较特殊的连接，外键会关联本表。

外键在关联当前表！！！

CREATE TABLE `board` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `intro` text,
  `parent_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

查询所有的顶级板块

select * from board where parent_id is null;

已知一个子版块的名称叫做“Java相关”，问该子版块的父板块名称是什么

select name from board where id = (select parent_id from board where name = "Java相关");

注意：在上面的查询中，将查询的结果作为条件，进行重新查询，这种查询叫做子查询。

select name from board where id = (select parent_id from board where name = "Java相关");

子查询

将一次查询的结果作为条件或者表重新参与查询，被成为子查询。

select name from board where id = (select parent_id from board where name = "Java相关");

外键约束

外键，作为两表或者多张表之间的关联关系，因此数据必须符合相关数据的规则，如不能把员工分配到不存在的部门！！！！

但是我们不做任何约束，数据可以成功插入的，一旦插入，数据才是会出错，所以RDMMS提供外检约束。

如何添加外键约束alter table 表名 add constraint 约束名约束类型(要约束的列名) alter table emp add [constraint] [外键名称] REFERENCES foreign key (dept_id) references dept(id) [on delete {nothing|restrict|set null|cascade} on update {nothing|restrict|set null|cascade};

alter table 表名 add constraint 约束名 约束类型(要约束的列名)
 
alter table emp add  [constraint] [外键名称] REFERENCES foreign key (dept_id) references dept(id) [on delete {nothing|restrict|set null|cascade} on update {nothing|restrict|set null|cascade};

情况1，如果两表都已经创建了，可以通过alter指令添加外键

情况2，创建包的时候，添加外键：

CREATE TABLE `emp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int DEFAULT '18',
  `tel` varchar(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `dept_id` int DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_ibfk_1` (`dept_id`),
  KEY `i_emp_name` (`name`),
  -- 添加外键约束
  CONSTRAINT FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
)

联合查询

union 如何查询到重复数据，只会展示一份
union all 如何查询到重复数据，则全部展示

联合查询只能使用在表结构一致的情况，进行查询。

会将多次查询的结果合并在一起。

select * from emp where id < 10 union select * from emp where id > 20;


-- id,name 是下面两张表共有的字段，所以可以联合查询

select id, name from emp where id < 10 union select id, name from dept;

select * from emp where true union select * from emp where id > 20;
select * from emp where true union all select * from emp where id > 20;

SQL函数

SQL提供了大量好用函数，通过函数，我们可以实现很多功能。

聚合函数

SQL很多聚合函数

count

max

min

avg

sum

数值型函数

函数名称	作用
ABS	求绝对值
SQRT	求平方根
POW 和 POWER	两个函数的功能相同，返回参数的幂次方
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号

字符串函数

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CHAR_LENGTH	计算字符串长度函数，返回字符串的字节长度，注意两者的区别
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT(str,pos,len,newstr)	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT(str,len)	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE(s，s1，s2)	字符串替换函数，返回替换后的新字符串
SUBSTRING(s，n，len)	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
STRCMP(expr1,expr2)	比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1则相反
LOCATE(substr,str [,pos])	返回第一次出现子串的位置
INSTR(str,substr)	返回第一次出现子串的位置

日期和时间函数

函数名称	作用
CURDATE() <br>CURRENT_DATE()<br/>CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME()<br/>CURRENT_TIME()<br/>CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW	返回当前系统的日期和时间值
SYSDATE	返回当前系统的日期和时间值
DATE	获取指定日期时间的日期部分
TIME	获取指定日期时间的时间部分
MONTH	获取指定日期中的月份
MONTHNAME	获取指定曰期对应的月份的英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
YEAR	获取年份，返回值范围是 1970〜2069
DAYOFWEEK	获取指定日期对应的一周的索引位置值，也就是星期数，注意周日是开始日，为1
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1<br/>〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH 和 DAY	两个函数作用相同，获取指定日期是一个月中是第几天，返回值范围是1~31
DATEDIFF(expr1,expr2)	返回两个日期之间的相差天数，如<br>SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
TIME_TO_SEC	将时间参数转换为秒数，是指将传入的时间转换成距离当天00:00:00的秒数，00:00:00为基数，等于 0 秒

流程控制函数

函数名称	作用
IF(expr,v1,v2)	判断，流程控制，当expr = true时返回 v1，当expr = false、null 、 0时返回v2
IFNULL(v1,v2)	判断是否为空，如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2
CASE	搜索语句

流程控制函数示例：

1、使用IF()函数进行条件判断

mysql>  SELECT IF(12,2,3),
 ->    IF(1<2,'yes ','no'),
 ->    IF(STRCMP('test','test1'),'no','yes');
+------------+---------------------+---------------------------------------+
| IF(12,2,3) | IF(1<2,'yes ','no') | IF(STRCMP('test','test1'),'no','yes') |
+------------+---------------------+---------------------------------------+
|      2 | yes         | no                  |
+------------+---------------------+---------------------------------------+
1 row in set (0.00 sec)


2、分别显示emp表有奖金和没奖金的员工信息。

mysql> select ename,comm,if(comm is null,'没奖金,呵呵','有奖金,嘻嘻') 备注 from emp;
+-----------+-------+------------------+
| ename   | comm | 备注       |
+-----------+-------+------------------+
| 甘宁   |  NULL | 没奖金,呵呵   |
| 黛绮丝  |  3000 | 有奖金,嘻嘻   |
| 殷天正  |  5000 | 有奖金,嘻嘻   |
| 刘备   |  NULL | 没奖金,呵呵   |
| 谢逊   | 14000 | 有奖金,嘻嘻   |
| 关羽   |  NULL | 没奖金,呵呵   |
| 张飞   |  NULL | 没奖金,呵呵   |
| 诸葛亮  |  NULL | 没奖金,呵呵   |
| 曾阿牛  |  NULL | 没奖金,呵呵   |
| 韦一笑  |   0 | 有奖金,嘻嘻   |
| 周泰   |  NULL | 没奖金,呵呵   |
| 程普   |  NULL | 没奖金,呵呵   |
| 庞统   |  NULL | 没奖金,呵呵   |
| 黄盖   |  NULL | 没奖金,呵呵   |
| 张三   | 50000 | 有奖金,嘻嘻   |
+-----------+-------+------------------+
15 rows in set (0.00 sec)


3、使用IFNULL()函数进行条件判断

mysql>  SELECT IFNULL(1,2), IFNULL(NULL,10), IFNULL(1/0, 'wrong');
+-------------+-----------------+----------------------+
| IFNULL(1,2) | IFNULL(NULL,10) | IFNULL(1/0, 'wrong') |
+-------------+-----------------+----------------------+
|      1 |        10 | wrong        |
+-------------+-----------------+----------------------+
1 row in set, 1 warning (0.00 sec)

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为
NULL 则返回第一个参数的值。
IFNULL() 函数语法格式为：
IFNULL(expression, alt_value)

4、使用CASE value WHEN语句执行分支操作

CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END

将 <表达式> 的值 逐一和 每个 when 跟的 <值> 进行比较
如果跟某个<值>想等，则执行它后面的 <操作> ，如果所有 when 的值都不匹配，则执行 else 的操作
如果 when 的值都不匹配，且没写 else，则会报错

SELECT name,dept_id,
CASE
        dept_id 
        WHEN 0 THEN
        "实习生" 
        WHEN 1 THEN
        "销售部" 
        WHEN 2 THEN
        "信息部" 
        WHEN 2 THEN
        "财务部" ELSE "没有部门" 
    END AS "部门" 
FROM
    emp;

mysql> SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| two                            |
+------------------------------------------------------------+
1 row in set (0.00 sec)

5、使用CASE WHEN语句执行分支操作

mysql> SELECT CASE WHEN 1<0 THEN 'true' ELSE 'false' END;
+--------------------------------------------+
| CASE WHEN 1<0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| false                   |
+--------------------------------------------+
1 row in set (0.00 sec)


6、查询emp表员工工资
要求：部门号为20，显示工资为1.2倍
部门号为30，显示工资为1.3倍
其他部门，显示原工资。

mysql> select deptno,sai 原工资,
 -> case deptno
 -> when 20 then sai*1.2
 -> when 30 then sai*1.3
 -> else sai
 -> end as 显示工资 from emp;                        
               
+--------+-----------+--------------+
| deptno | 原工资  | 显示工资   |
+--------+-----------+--------------+
|   20 |    8000 |    9600.0 |
|   30 |   16000 |    20800.0 |
|   30 |   12500 |    16250.0 |
|   20 |   29750 |    35700.0 |
|   30 |   12500 |    16250.0 |
|   30 |   28500 |    37050.0 |
|   10 |   24500 |     24500 |
|   20 |   30000 |    36000.0 |
|   10 |   50000 |     50000 |
|   30 |   15000 |    19500.0 |
|   20 |   11000 |    13200.0 |
|   30 |    9500 |    12350.0 |
|   20 |   30000 |    36000.0 |
|   10 |   13000 |     13000 |
|   50 |   80000 |     80000 |
+--------+-----------+--------------+
15 rows in set (0.00 sec)



7、查询员工工资情况
要求：工资大于20000，显示A
工资大于15000，显示B
工资大于10000，显示C
否则，显示D

mysql> select ename,sai,
 -> case
 -> when sai>20000 then 'A'
 -> when sai>15000 then 'B'
 -> when sai>10000 then 'C'
 -> else 'D'
 -> end as '工资级别' from emp;
+-----------+-------+--------------+
| ename   | sai  | 工资级别   |
+-----------+-------+--------------+
| 甘宁   |  8000 | D      |
| 黛绮丝  | 16000 | B      |
| 殷天正  | 12500 | C      |
| 刘备   | 29750 | A      |
| 谢逊   | 12500 | C      |
| 关羽   | 28500 | A      |
| 张飞   | 24500 | A      |
| 诸葛亮  | 30000 | A      |
| 曾阿牛  | 50000 | A      |
| 韦一笑  | 15000 | C      |
| 周泰   | 11000 | C      |
| 程普   |  9500 | D      |
| 庞统   | 30000 | A      |
| 黄盖   | 13000 | C      |
| 张三   | 80000 | A      |
+-----------+-------+--------------+
15 rows in set (0.00 sec)

作业：

一、多表关联查询

1.创建student和score表
CREATE  TABLE student (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY ,
name  VARCHAR(20)  NOT NULL ,
sex  VARCHAR(4) ,
birth  YEAR,
department  VARCHAR(20) ,
address  VARCHAR(50)
);
创建score表。SQL代码如下：
CREATE  TABLE score (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
stu_id  INT(10)  NOT NULL ,
c_name  VARCHAR(20) ,
grade  INT(10)
);
2.为student表和score表增加记录
向student表插入记录的INSERT语句如下：
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

3.查询student表的所有记录
4.查询student表的第2条到4条记录
5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
6.从student表中查询计算机系和英语系的学生的信息
7.从student表中查询年龄18~22岁的学生信息
8.从student表中查询每个院系有多少人
9.从score表中查询每个科目的最高分
10.查询李四的考试科目（c_name）和考试成绩（grade）
11.用连接的方式查询所有学生的信息和考试信息
12.计算每个学生的总成绩
13.计算每个考试科目的平均成绩
14.查询计算机成绩低于95的学生信息
15.查询同时参加计算机和英语考试的学生的信息
16.将计算机考试成绩按从高到低进行排序
17.从student表和score表中查询出学生的学号，然后合并查询结果
18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩