sql脚本练习

　　多写sql语句，才能对数据库操作更加熟练。

create database springbootdemo;
use springbootdemo;
create table user; // 这个脚本会提示table中没有row；

　　sql中的distinct和group by语句的使用

// distinct前面无字段，后面多字段时，会拼接形成key，num-name;
select distinct num,name from iam.product;
// group by 前面为字段本身，或者函数，而不应该有其他字段，如name
SELECT  count(121),name  FROM   iam.product group by  num LIMIT 0, 50000    
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'iam.product.name' which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by    0.0020 sec

　　复杂一点的语句：

delete from stock   
where (org_id,material_id,state) in  
(SELECT * from (select org_id,material_id, state 
from stock WHERE state = 1 group by org_id,material_id,state having count(*) > 1) as a )

　　这个包含子查询，联表查询，group by查询的的语句，能够表达我的意思，但是却运行不同，不知道什么问题。

   SELECT
        farm.CODE,
        farm.NAME as name,
        farm.she_id as wf_farm_she_id,
        farm.fan_actual_count,
        farm.fan_running_count,
        farm.manager_name,
        farm.status,
        wfa.update_time as update_time,
        wfa.create_time as create_time,
        wfa.create_user as create_user,
        (select count(*) from md_user as mu where wf_farm.CODE =mu.USER_ID) as update_user
    FROM
        wf_farm as farm
        LEFT JOIN wf_fan_archives as wfa ON farm.code = wfa.WF_FARM_CODE 
        WHERE
        farm.MARK_FOR_DELETE = 0
        group by farm.CODE ;