SQL进阶-2-自连接

皮大大

发布于 2021-03-01 08:09:16

1.3K00

代码可运行

文章被收录于专栏：机器学习/数据可视化机器学习/数据可视化

运行总次数：0

代码可运行

SQL进阶-2-自连接

SQL通常在不同的表之间进行连接运算，但是也可以在相同的表中进行运算。macOS中连接数据库：

mysql -u root -p   -- 后续输入密码

基本操作

创建和查看数据库

// 1、创建表
create table user(字段1,
                  字段2,
                  ...,
                  字段n
                  );    // 最后的分号不能忘记

// 2、查看所有的表
show tables;

// 3、查看表的结构
desc user;

// 4、查看创建表的SQL语句
show create table user;

// 5、删除表
drop table user;

// 6、修改表名
alter table user rename to users;   # 表名改为users；to可省略

mysql> create table products (id int primary key auto_increment,name varchar(50) default null, price int default null);

问题

今天在往数据库中的表插入中文数据的时候遇到的一个问题：绝对是编码的问题

解决过程

修改数据库编码

mysql> alter table products  default character set utf8;   -- 直接修改数据库的编码，设置成utf8

虽然修改了整个表的编码，但是name字段的编码还是latin1，不能插入中文，需要修改

之前的编码：

修改具体字段name的编码

关键词是modify

mysql> alter table products modify column name varchar(50) character set utf8;

语法格式为：

mysql>alter table <表名> modify column <字段> <字段类型> character set utf8;   --将字段改成utf8类型

插入新数据

insert into <表名> <字段1，字段2，.....> values <对应的数据>

案例1-可重排、组合

2种组合

有顺序的有序对，用尖括号括起来
无顺序的无序对，使用花括号括起来

需求

SQL自连接生成的结果中相同的数据

select p1.name as name1, p2.name as name2
from products p1, products p2

name_1       name_2
------      ------
苹果         苹果    -- 出现相同的结果，如何去掉
苹果         橘子
苹果         香蕉
橘子         苹果
橘子         橘子
橘子         香蕉
香蕉         苹果
香蕉         橘子
香蕉         香蕉

select p1.name as name1, p2.name as name2
from products p1, products p2
where p1.name <> p2.name   -- 指定两个表的中的2个名字不等

笔记：在SQL中即便是相同的表，只要是被赋予了不同的名称，都应该被当做不同的表来使用，它们只是碰巧存储了相同数据的两个集合表的自连接和不同表间的连接没有任何区别

扩展

想要获取3个以上的元素，通过多个表的连续扩展：

select P1.name as name1, P2.name as name2, P3.name as name3
from Products P1,Products P2, Products P3
where P1.name > P2.name   -- 只与字符顺序比自己靠前的商品进行比较
and P2.name2 > P3.name

案例2-重复行问题

查找重复行

基于单个字段

假设我们想通过name字段（只根据一个字段）来查找重复行，可以使用如下语句：

select name, price, count(*)   -- 分组之后统计每个组的行数
from Products
group by name;

过滤小于1的行：

select name, price,  count(*)
from Products
group by name
having count(*) > 1;  -- 过滤

笔记：根据具有相同值的字段分组，然后只显示大小大于1的组

基于多个字段

有时候会基于多个字段查找重复行

SELECT
    col1, COUNT(col1),
    col2, COUNT(col2),
    ...
FROM
    table_name
GROUP BY
    col1,
    col2,
    ...
HAVING  -- 只有当列的组合重复时，才认为是重复行，用and进行关联
       (COUNT(col1) > 1) AND
       (COUNT(col2) > 1) AND
       ...

删除重复行

通过删除重复行变成如下的结果

Oracle中使用极值函数rowid

delete from Products P1
where rowid < (  -- 小于最大值的全部删除
  select max(P2.rowid)   -- 返回的是价格和名字都相同的商品所在的行
  from Products P2
  where P1.name = P2.name
  and P1.price = P2.price
)

子查询中返回的是价格和名字都相同的商品所在的行
苹果和香蕉没有重复行
橘子中有，返回的是最大值4，小于4的两行（2、3）都会被删除delete

使用非等值连接

delete from Products P1
where exists (  -- 存在于小于最大值的行全部删除delete
  select * from Products P2
  where P1.name = P2.name
  and P1.price = P2.price
  and P1.rowid < P2.rowid
)

mysql中实现删除重复行

参考教程：使用的是delete join语句，保留最大的行记录，小于部分全部删除了

delete P1 from Products P1
inner join Products P2
where P1.id < P2.id   -- 假设数据中存在自增id
and P1.name = P2.name

案例3-局部不一致的列

需求

在同一张表中，可能存在不是很合理的地方：比如，前田夫妇的地址应该是相同的，但是表中不同

SQL实现

查找是同一家人，但是住址不同的记录

自连接和非等值连接的结合

select distinct A1.name, A1.address
from Address A1, Address A2
where A1.family_id = A2.family_id  -- 同一家人
and A1.address <> A2.address;  -- 住址不同

查找价格相等但是名称不同的商品的记录

select distinct P1.name, P1.price
from Products P1, Products P2  -- 同一个表的自连接
where P1.price=P2.price   -- 价格相同但是名称不同
and P1.name <> P2.name  -- 非等值连接

如果查询语句中没有distinct，结果中会出现重复值

非等值连接

使用<、>、<>等进行连接的称之为“非等值连接”。其中，>或者<等比较运算符不仅可以用于数值比较**，还可以用于比较字符串或者日期等的比较**

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020-8-30，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

数据库

登录后参与评论

0 条评论

热度