模糊查询

-- SELECT id FROM article WHERE title LIKE '%标%'

更高效的模糊查询方式

--SELECT DISTINCT id FROM article WHERE LOCATE('标', title)> 0

-- SELECT * FROM fb_record
-- WHERE 1= 1
-- AND
-- ((type = 'card' AND dependentId IN (SELECT DISTINCT id FROM card WHERE LOCATE('标', title)> 0))
-- OR
-- (type = 'article' AND dependentId IN (SELECT DISTINCT id FROM article WHERE LOCATE('标', title)> 0))
-- OR
-- (type = 'product' AND dependentId IN (SELECT DISTINCT id FROM product WHERE LOCATE('标', title)> 0))
-- OR
-- (type = 'video' AND dependentId IN (SELECT DISTINCT id FROM video WHERE LOCATE('标', title)> 0)))
-- order by id desc

联合查询,并显示出重复的id

SELECT v.id FROM card v LEFT JOIN (SELECT dependentId FROM fb_record WHERE type = 'card')t
ON v.id = t.dependentId WHERE v.parentId IS NOT NULL GROUP BY v.id having count(*)>1

过滤重复的数据并且统计出总数

SELECT COUNT(DISTINCT company_name) FROM hc_company

查询出重复的数据

Select company_name From hc_company Group By company_name Having Count(*)>1

删除表里面的重复信息

-- DELETE FROM hc_company WHERE id NOT IN (
-- SELECT
-- dt.minId
-- FROM
-- (
-- SELECT
-- MIN(id) AS minId
-- FROM
-- hc_company
-- GROUP BY
-- company_name
-- ) dt
-- )

当表里面数据有10W时,这时候得使用limit会更好

SELECT COUNT(h.company_name) FROM (SELECT * from hc_company ORDER BY id LIMIT 0, 10) h LEFT JOIN aqc_company a ON a.company_name = h.company_name

A/B 两张表, 找出B里面有,但是A里面没有的数据

SELECT tb1.company_name FROM tb_company tb1 WHERE tb1.company_name NOT IN (SELECT tb2.company_name FROM tb_company2 tb2)