多表查询&数据操作（增删改）&备份与恢复

西红柿味儿柿子
70 阅读5分钟

多表查询（DQL）

当我们需要查询多个表或者查询一个表中存在某种关系的两列数据时，我们可以使用多表查询。连接查询就是直接查询一个或者两个表中符合某一个条件的所有数据，而联合查询得到的数据就是将两次查询的结果连在一张表中呈现出来。

笛卡尔积

多表查询时，如果不写关联关系，则查询结果为两张表的乘积
假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}
  • 避免笛卡尔积
在WHERE加入有效的连接条件---->**等值连接**
  • 注意

链接N张表，至少需要N-个链接条件

内连接查询

  • 隐式内连接

     隐式查询实际上类似直接使用select * from 表 where 条件字段=条件值，只不过区别是隐式查询需要查询的表有两个、后面跟的条件分别位于两个不同的表；但相同的是两者都有关键字where。

csharp
复制代码
select 需要显示的字段 from 表1，表2 [where 表1列=表2列];

image.png

  • 显示内连接

    在显示内连接查询中，使用的关键字为[inner] join……on……,但在我们使用的过程中inner可以省略不码。 （1）基本格式

csharp
复制代码
select 需要显示的数据 from 表1 [inner] join 表2 on 表1列字段=表2列字段；

image.png

  • 连接多张表

语法和显示连接一样，只是在连接一张表后继续连接另一表

image.png

外连接查询

在显示外连接中，不管是左外连接还是右外连接，它们使用的共同关键字为……join ……on……。

语法格式：

SELECT 需要显示的字段

FROM 表1 LEFT/RIGHT OUTER JOIN 表2

ON (表1.列 = 表2.co列)];
  • 左外连接查询
概念：
查询出JOIN左边表的全部数据查询出来,
JOIN右边的表不匹配的数据使用NULL来填充数据.

image.png

image.png

  • 右外连接查询
概念：
查询出JOIN右边表的全部数据查询出来,
JOIN左边的表不匹配的数据使用NULL来填充数据

image.png

image.png

自连接

所谓自连接查询，它就是先将一张表通过取别名的方式看成两张表，再通过内连接查询或外连接查询的一个过程。就是一个内连接.

  • 使用场景

在一张表中用字段去划分层级

  • 语法：

就是一个内连接，分为隐式和显示两种

image.png

子查询（嵌套查询）

一个查询语句嵌套在另一个查询语句中，内层查询的结果可以作为外层查询条件。
一般嵌套在WHEREHU或者FEOM字句中

  • 单行单例子查询 image.png
  • 单行多例子查询

用And来连接，

image.png

SELECT 
 * 
FROM
 product
WHERE
	( dir_id, cutoff )= (
	SELECT
		dir_id,
		cutoff 
	FROM
		product 
	WHERE
	productName = '罗技MX1100')

数据操作（DML）

新增数据

使用INSERT INTO 表名 (字段名1,字段名2，...) VALUES （值,值,...);

INSERT INTO product(productName,salePrice) VALUES ('赤影疾锋',16888);

image.png

删除数据

DELETE FROM 表名 WHERE 条件;

DELETE语句不会重置自增列，
TRUNCATE，清空命令，会重置自增列
MYISAM 重启数据库，不会重置自增列，存在于磁盘。INNODB会重启数据库，会重置自曾列，存在于内存

修改数据

使用UPDATE 表名 SET 字段名1=值1 ，字段名2=值2 WHERE 条件；
注意：如果没有WHERE条件，就会改变整张表的数据

UPDATE emp SET `name` = '幻阙歌',`age` = 17888 WHERE id =7;

数据的备份与恢复

CMD进入C:\Program Files\MySQL\MySQL Server 5.7\bin进入命令行模式

  • mysqldump备份

mysqldump -u账户 -p密码 数据库名称>脚本文件存储地

  • MySQL恢复

mysql -u账户 -p密码 数据库名称< 脚本文件存储地址

复制DSTE

函数

聚集函数

  1. count
  2. sum
  3. avg
  4. max
  5. min

类型转换

  • CAST（）函数&DECIMAL函数

SELECT CAST(salePrice AS DECIMAL(8,2)) FROM product;

  • CONVERT（）&DECIMAL函数

SELECT CONVERT(salePrice , DECIMAL(8,2)) FROM product;（效果和cast一样）

  • IFNULL

如果查出来的值部位nULL，就返回查出来的值，如果为NULL，就返回给定的值

SELECT IFNULL(productName,'这是填充的值') FROM product;

image.png

执行流程

FROM -> 决定从哪一个表查询

JOIN -> 决定跟哪一个表关联

ON -> 决定关联表的共同条件

WHERE -> 决定从表中哪一些开始查询（条件过滤，学会善用条件过滤）

GROUP BY -> 决定按照什么分组进行组内查询，一般跟聚集函数联合使用

SELECT -> 决定显示哪一些字段

HAVING -> 在组内条件过滤，一般使用SELECT语句中的聚集函数作为过滤条件

DISTINCT -> 根据select中的字段决定显示哪一些不重复的字段

ORDER BY -> 决定显示的排序规则

LIMIT -> 决定每一页显示多少条件记录

子查询的执行流程：

从最里面（深）的查询语句开始执行，逐级向外执行，直到最后一层，

子查询一般是用在where语句和from语句中，执行流程满足上面的执行流程

数据库设计的三范式 NF

第一范式

  • 列都是不可再分，第一范式的目标是确保每列的原子性，每列都是不可再分的最小数据单元
  • 身高体重是两个属性，违反第一范式，不能划分为同一个列
  • 符合第一范式的设计

第二范式

  • 首先满足第一范式，并且表中非主键列不存在对主键不依赖或者部分依赖，确保每个列都和主键相关。一般因为是存在多个主键，或者存在复合主键，因此需要拆表
  • 存在复合主键（学号，学科），而学科学分却只依赖分部主键-学科，不符合第二范式
  • 第二范式的正确示范

第三范式

  • 满足第二范式，并且表中的列不存在对非主键列的传递依赖，每列都和主键列直接相关，而不是间接相关
  • 在成绩表里，爱好是依赖学生的，学生又是依赖主键ID，存在传递依赖应该提取出学生的个人信息为表。
  • 符合第三范式的规范

avatar
文章
阅读
粉丝