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 ;
最后发现是值的指代问题。
日进有功