sql 取交集并集差集 - sql中去掉字段的所有空格-sql 特殊字符替换

mysql

#取并集 union 会去重
select * from c_user where `password`='123'
UNION 
select * from c_user where did = 1

#取并集 union 不会去重
select * from c_user where `password`='123'
UNION all
select * from c_user where did = 1

#取交集
SELECT a.username,a.`password` FROM c_user a INNER JOIN c_user_copy1 b ON
a.username=b.username AND a.`password`=b.`password`
#取交集 第二种方式
SELECT a.username,a.password FROM c_user a INNER JOIN c_user_copy1 b USING(username,password)

# 差集 减去
SELECT a.id,a.username, a.password,b.id 
FROM
  c_user a 
  LEFT JOIN c_user_copy1 b 
    ON a.username = b.username 
    AND a.password = b.password 
WHERE b.id IS NULL 

oracle

交集：INTERSECT（适用于两个结果集）

SELECT ID, NAME FROM YANGGB1
INTERSECT
SELECT ID, NAME FROM YANGGB2

差集：MINUS（适用于两个结果集）

SELECT ID, NAME FROM YANGGB1
MINUS
SELECT ID, NAME FROM YANGGB2

并集：UNION或UNION ALL（适用于两个结果集）

-- 不包括重复行，进行默认排序
SELECT ID, NAME FROM YANGGB1
UNION
SELECT ID, NAME FROM YANGGB2
-- 包括重复行，不进行默认排序
SELECT ID, NAME FROM YANGGB1
UNION ALL
SELECT ID, NAME FROM YANGGB2

sql中去掉字段的所有空格

#去除空格
select id,replace(username,' ',''),`password`  from c_user_copy1 where id =2
#字符前的空格，用ltrim(string)
select id,ltrim(username),`password`  from c_user_copy1 where id =2
#字符后的空格，用rtrim(string) 
select id,rtrim(username),`password`  from c_user_copy1 where id =2

https://blog.csdn.net/m0_37450089/article/details/83177541

sql 特殊字符替换

# 替换特殊字符
select ASCII('\n')
select id,replace(username,char(32),''),`password`  from c_user_copy1 where id =2

sql 特殊字符替换

--看ASCII码
print ASCII('a')

--tab 9  回车13  换行10  空格32  ' " , : ; . 
update Inventoty set name=replace(name,char(9),'') 
update Inventoty set name=replace(name,char(10),'') 
update Inventoty set name=replace(name,char(13),'') 
update Inventoty set name=replace(name,char(32),'')
update Inventoty set name=replace(name,'''','')
update Inventoty set name=replace(name,'"','')
update Inventoty set name=replace(name,',','')
update Inventoty set name=replace(name,':','')
update Inventoty set name=replace(name,';','')
update Inventoty set name=replace(name,'.','')

https://www.cnblogs.com/su-king/p/9989116.html