SQL进阶-2-自连接

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

1	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可省略

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

问题

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

解决过程

修改数据库编码

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

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

之前的编码：

修改具体字段name的编码

关键词是modify

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

语法格式为：

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

插入新数据

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

案例1-可重排、组合

2种组合

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

需求

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

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

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

1
2
3

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字段（只根据一个字段）来查找重复行，可以使用如下语句：

1
2
3

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，结果中会出现重复值

非等值连接

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