数据库-MySQL连表查询及原理

一.DQL

1.多表查询\连表查询

1.1笛卡尔积：

1.1.1什么是笛卡尔积

没有连接条件的表关系的返回值结果【表之间数据组合的结果】

1.1.2.多表查询会产生笛卡尔积：

假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

1.1.3.实际运行中

表与表之间要避免笛卡尔积 ------想要解决可以添加连表条件(使用关联条件)

1.1.4注意

如果关联N张表，至少需要是n-1个连接条件

ON 连表条件

1.1.5sql中笛卡尔积语法

方式一：

select 字段 from 表1,表2[,表N];

方式二：

select 字段 from 表1 join 表2 [join 表N];

2.内连接

语法：

方式一：

select 字段 from 表1 inner join 表2 on 连接条件;

方式二：

select 字段 from 表1 join 表2 on 连接条件;

方式三：

select 字段 from 表1, 表2 [where 关联条件];

【不写left和right就是内连接】

3.左连接

如果左表中不存在右表数据那么右表数据用 null填充

语法：

select 列 from 主表 left join 从表 on 连接条件;

4.右连接

右链接以右边的表为基准,展示右表所有数据,在左边没有匹配上的数据使用null填充

select 列 from 从表 right join 主表 on 连接条件;

RIGHT JOIN

5.自连接就是内连接

6.子查询：

内层结果是外层的条件

多个字段要用括号括起来

二.DMI

1.新增

语法：

方式一：

insert into A(colum1,colum2,...)values(value1,value2,...)

方式二：

insert into A values(value1,value2,...)

INSERT INTO 表名 (字段名) VALSE (值)；

例如：

2.修改

语法：

UPDATE 表名 Set 字段名 = 值 WHERE 条件

注意：

如果省略了where子句，则全表的数据都会被修改。注意:没有FROM

3.删除

语法：

//删除一条数据 DELETE FROM 表名 WHERE 表名 //删除一个表 //不添加条件就是删除表 DELETE FROM 表名 DROP 表名

DELETE 不会充值自增列

MYISAM 重启数据库，不会重置自增列

INNODB 重启数据库，

TRUNCATE 重置自增序列

注意：

如果省略了where子句，则全表的数据都会被删除

三.备份恢复

指令

工具

复制data文件夹

四.函数

1.聚合函数：

count(列名称) // 统计行

max(列名称) // 最大值

min(列名称) // 最小值

sum(列名称) // 求和统计

avg(列名称) // 求平均数

2.cast函数

CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

语法如下：

cast(值 as 类型)

cast('123' as SIGNED) 将123转换为int类型，返回值为整型类型

select cast(salePrice as decimal)---->数据类型 ----Java中的数据类型

3.convert函数

对于简单类型转换，CONVERT()函数和CAST()函数的功能相同，只是语法不同。CAST()函数一般更容易使用，其功能也更简单。

语法如下：

select convert(expr ,type)

select convert('123',signed)

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

二进制，同带binary前缀的效果 : BINARY

字符型，可带参数 : CHAR()

日期 : DATE

时间: TIME

日期时间型 : DATETIME

浮点数 : DECIMAL

整数 : SIGNED

无符号整数 : UNSIGNED

4.decimal函数

定义数据类型为DECIMAL的列，

语法：

column decimal(P,D)

在上面的语法中：

P是表示有效数字数的精度。 P范围为1〜65。

D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。

DECIMAL(P，D)表示列可以存储D位小数的P位数。十进制列的实际范围取决于精度和刻度。

综上，可以将上述小数转换为整数：

select cast('123.4' as decimal(P,D))

5.ifnull函数

语法：

IFNULL(expression_1,expression_2);

注意:

①如果expression_1不为NULL，则IFNULL函数返回expression_1

②否则返回expression_2的结果。

执行流程

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

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

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

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

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

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

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

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

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

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

子查询的执行流程：

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

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

数据库设计三范式：

【表设计的依据】三种规范

1.表中的列都是不可拆分，列\字段都已经是最小粒度

2.第二范式(满足第一范式的前提下满足自己)：【内键】表中的行是唯一的

3.第三范式(满足一二范式的前提下满足自己)：【外键】表中不能出现冗余数据，一张表只描述一个事件

要反三范式，保证性能和规范

阿里规范中：连表数不能超过三张(要保证查询效率)