SQL进阶-2-自连接
SQL
通常在不同的表之间进行连接运算,但是也可以在相同的表中进行运算。macOS
中连接数据库:
1 | mysql -u root -p -- 后续输入密码 |
基本操作
创建和查看数据库
1 | // 1、创建表 |
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 | select p1.name as name1, p2.name as name2 |
1 | name_1 name_2 |
1 | select p1.name as name1, p2.name as name2 |
笔记:在SQL中即便是相同的表,只要是被赋予了不同的名称,都应该被当做不同的表来使用,它们只是碰巧存储了相同数据的两个集合
表的自连接和不同表间的连接没有任何区别
扩展
想要获取3个以上的元素,通过多个表的连续扩展:
1 | select P1.name as name1, P2.name as name2, P3.name as name3 |
案例2-重复行问题
查找重复行
基于单个字段
假设我们想通过name字段(只根据一个字段)来查找重复行,可以使用如下语句:
1 | select name, price, count(*) -- 分组之后统计每个组的行数 |
过滤小于1的行:
1 | select name, price, count(*) |
笔记:根据具有相同值的字段分组,然后只显示大小大于1的组
基于多个字段
有时候会基于多个字段查找重复行
1 | SELECT |
删除重复行
通过删除重复行变成如下的结果
Oracle中使用极值函数rowid
1 | delete from Products P1 |
- 子查询中返回的是价格和名字都相同的商品所在的行
- 苹果和香蕉没有重复行
- 橘子中有,返回的是最大值4,小于4的两行(2、3)都会被删除delete
使用非等值连接
1 | delete from Products P1 |
mysql中实现删除重复行
参考教程:使用的是delete join语句,保留最大的行记录,小于部分全部删除了
1 | delete P1 from Products P1 |
案例3-局部不一致的列
需求
在同一张表中,可能存在不是很合理的地方:比如,前田夫妇的地址应该是相同的,但是表中 不同
SQL实现
- 查找是同一家人,但是住址不同的记录
自连接和非等值连接的结合
1 | select distinct A1.name, A1.address |
- 查找价格相等但是名称不同的商品的记录
1 | select distinct P1.name, P1.price |
如果查询语句中没有distinct,结果中会出现重复值
非等值连接
使用<、>、<>
等进行连接的称之为“非等值连接”。其中,>或者<等比较运算符不仅可以用于数值比较**,还可以用于比较字符串或者日期等的比较**