sql查询的技巧

sql grop by的本质理解 

 

可以发现 group by 是取了 order by 结果的首行！ 

非本表字段的筛选查询

 查询A表的结果，但筛选条件是B表的筛选条件，其中A表的主键是B表的外键，表结构如下：

A表：

CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

B表：

CREATE TABLE `B` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL COMMENT 'A表的主键',
  PRIMARY KEY (`id`)
)

A: B: 

 内连接查询为：

 

 其中，exists表示：A中存在的id至少在B中user_id中出现一次，应用场景为：

查询A表，但筛选条件有B表的字段，如：查询居住在四川成都的用户：

SELECT	* FROM	A 
WHERE
	EXISTS (SELECT * 	FROM	B WHERE	A.id = B.user_id and  B.address="四川成都")

去重重复的数据

场景：假设ps_basic_product_sku表中，sku_id字段应该是唯一的，但目前存在部分重复的sku_id,

删除重复多余的sku_id,那么sql应该怎么写？

//先按sku_id分组查询，可以查询出要保留的数据
SELECT id FROM ps_basic_product_sku bb WHERE  id NOT IN (
        SELECT max(id) FROM ps_basic_product_sku cc WHERE GROUP BY sku_id
 ) 

再删除或者更新(坑)

delete ps_basic_product_sku 
where id in(
SELECT id FROM ps_basic_product_sku bb WHERE  id NOT IN (
        SELECT max(id) FROM ps_basic_product_sku cc WHERE GROUP BY sku_id
 ) 
)
执行将报错：You can't specify target table 'ps_basic_product_sku' for update in FROM clause
原因：从自己表里查，有对自己表进行删除或者更新，容易导致死锁，所以不让这样搞

 上面的解决办法：加临时表

delete ps_basic_product_sku 
where id in(
    select * from(
        SELECT id FROM ps_basic_product_sku bb WHERE  id NOT IN (
            SELECT max(id) FROM ps_basic_product_sku cc WHERE GROUP BY sku_id
         ) 
    ) aaa
)
加了select * from （ ） 相当于形成了临时表 这个时候就可以进行更新或者删除操作了

 知识点1：

mysql默认不区分大小写，这意味着表名、列名或者使用字符串时，大小写不会影响查询的结果，但如果你需要在 MySQL 中区分大小写，可以在创建表时指定字符集为区分大小写的字符集，例如 utf8_bin，如下：

 知识点2：

mysql中 join和 inner join 有区别吗？没区别，记忆为 mysql中可以省略写inner