2023-07-26 16:46 已编辑兰州交通大学 Java 发布于甘肃

关注

mysql表之间的关系

1.合并结果集

先创建表，如下：

create table tb_idcard(
    id int primary key auto_increment,
    code varchar(18)
);

insert into tb_idcard(code) value ('152221198711020624');
insert into tb_idcard(code) value ('152220119900815031');
select * from tb_idcard;

alt

create table tb_person(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    sex varchar(8),
    card_id int,
    foreign key (card_id) references tb_idcard(id)
);

insert into tb_person(name, age, sex, card_id) VALUES ('rose',22,'女',1);
insert into tb_person(name, age, sex, card_id) VALUES ('jack',23,'男',2);
select * from tb_person;

alt

合并结果集：

作用：合并结果集就是把两个select语句的查询结果合并到一起！(跟水平拆分刚好相反)

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

参考链接：https://blog.csdn.net/yjltx1234csdn/article/details/105228648

合并结果集有两种方式：

#UNION：去除重复记录。

#UNION ALL：不去除重复记录。

select * from tb_idcard union select name,age from tb_person ;

alt

select * from tb_idcard union all select id,age from tb_person ;

alt

select * from tb_idcard union all select * from tb_idcard;

alt

select * from tb_idcard union  select * from tb_idcard;

alt

2.连接查询、

参考连接：（可以查看）https://blog.csdn.net/qq_34107571/article/details/77249320

多表查询有如下几种：
合并结果集；UNION 、  UNION ALL  了解
连接查询
   内连接  [INNER] JOIN  ON 
   外连接  OUTER JOIN ON
   左外连接 LEFT [OUTER] JOIN
   右外连接 RIGHT [OUTER] JOIN
   全外连接（MySQL不支持）FULL JOIN
   自然连接  NATURAL JOIN
子查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

 连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

2.1 内连接

分为等值连接和非等值连接。。

特点：返回的数据集合只包含两个表中都存在的数据行，可以避免数据冗余和错误。但它也有一定的局限性，即只能连接两个表。

#SQL99标准的内连接为：
SELECT * 
FROM emp e 
INNER JOIN dept d 
ON e.deptno=d.deptno;

select * from tb_person as p inner join tb_idcard as i on p.card_id = i.id;

2.2 外连接

外连接是一种可以返回一个表中所有数据和另一个表中匹配数据的连接方式。

可以返回一个表中所有数据和另一个表中匹配的数据，但它也有缺点，即可能会返回大量的数据行，导致数据冗余和错误。

2.2.1 左连接（左外连接）

将左表中的所有数据和右表中匹配的数据连接在一起，并且返回右表中没有匹配的数据。

select * from tb_person as p left outer join tb_idcard as ti on ti.id = p.card_id;
select * from tb_person as p left  join tb_idcard as ti on ti.id = p.card_id;

2.2.2 右连接（右外连接）

与左外连接类似，只是将左表和右表的顺序调换了一下。

select * from tb_person as p right outer join tb_idcard as ti on ti.id = p.card_id;
select * from tb_person as p right join tb_idcard as ti on ti.id = p.card_id;

2.2.3 全外连接--mysql不支持

指将左表所有记录与右表所有记录进行连接，返回的结果除内连接的结果，还有左表与右表不符合条件的记录，并在左表与右表相应列中填NULL。

3.自然连接

指自动将表中相同名称的列进行记录匹配

SELECT [column_names | *]   
FROM table_name1   
NATURAL JOIN table_name2;

4.自连接

自己连接自己，起别名

select p1.*,p2.* from tb_person as p1 ,tb_person as p2 where p1.card_id=p2.id;

5.子查询

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

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

子查询出现的位置： where后，作为被查询条件的一部分； from后，作表；

当子查询出现在where后作为条件时，还可以使用如下关键字：(很少用) any all

select * from tb_person where card_id>(select id from tb_idcard where id=1);

上述的各种连接不懂的话，可以看一下这个图：

alt

6.表之间的关系

6.1 一对一关系

例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：
在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；
给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

6.2 一对多

比如在购买商品的时候，一个人可以对应多个订单。

6.3 多对多

例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。

7.MySQL中的函数

7.1日期函数

（数值类型、日期类型、字符串类型）,如果实际需求要某种格式(字符串,数值,日期)的类型,那么我们一定要相对应的类型.不要使用字符串代替.

譬如日期类型的字段,最好不要用字符串varchar, '1998-01-01' '01/04/1998' ‘1998年2月1日’,否则无法做统计

CURRENT_DATE () 当前日期
CURRENT_TIME () 当前时间
CURRENT_TIMESTAMP ()  当前时间戳


DATE (datetime) 返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) 在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type ) 在date2上减去一个时间
DATEDIFF (date1 ,date2 )  两个日期差
NOW() 当前时间
YEAR|Month|Day(datetime ) 年月日

7.2字符串函数

CHARSET(str)  返回字串字符集
CONCAT (string2  [,... ]) 连接字串
INSTR (string ,substring )  返回substring在string中出现的位置,没有返回0
UCASE (string2 )  转换成大写
LCASE (string2 )  转换成小写
LEFT (string2 ,length ) 从string2中的左边起取length个字符
LENGTH (string )  string长度
REPLACE (str ,search_str ,replace_str ) 在str中用replace_str替换search_str
STRCMP (string1 ,string2 )  逐字符比较两字串大小,
SUBSTRING (str , position  [,length ])  从str的position开始,取length个字符
LTRIM (string2 ) 
RTRIM (string2 )  
trim  去除前端空格或后端空格

7.3数学函数

ABS (number2 )  #绝对值
BIN (decimal_number ) #十进制转二进制
CEILING (number2 )  向上取整
CONV(number2,from_base,to_base) 进制转换
FLOOR (number2 )  向下取整
FORMAT (number,decimal_places ) 保留小数位数
HEX (DecimalNumber )  转十六进制
LEAST (number , number2  [,..]) 求最小值
MOD (numerator ,denominator ) 求余
RAND([seed])  RAND([seed])