mysql总结

mysql总结
一、增
insert into table(columnname,…) values(‘value’,…)
二、删
delete from table where columnname = ‘value’
三、改
update table set columnname = ‘value’,… where 条件
四、查
1.单表查询
select * from table
2.多表联查
select a.id,b.name from table1 a , table2 b where a.id = b.id
3.分组查询
1)select columnname from table group by columnname (若直接分组相当过滤掉columnname相同数据，效果类似于distinct)
2)select group_concat(columnname1 separator ‘|’)columnname, columnname2 from table group by columnname2(分组查询，并把对应需要的数据按分隔符合并)

4.过滤相同数据

1)select distinct columnname from table

5.相同字段数据合并与拆分
合并

1)select concat(ifnull(columnname1,’’),ifnull(columnname,’’))aa from table (数据合并，ifnull函数防止合并数据出现null数据，若出现null数据 合并结果为null 所以这里用ifnull)
2)select group_concat(columnname1 separator ‘|’)columnname, columnname2 from table group by columnname2(分组合并)
例子：select group_concat(distinct ifnull(b.param_name,’’),(case when (isnull(b.param_name_value)=1) || (length(trim(b.param_name_value))=0) then ‘’ else ‘(’ end),ifnull(b.param_name_value,’’),(case when (isnull(b.param_name_value)=1) || (length(trim(b.param_name_value))=0) then ‘’ else ‘)’ end) seperator’、’)param_name from t_entrust_param b where entrust_code = ‘{0}’
(表中两字段分组合并为一组过滤相同columnvalue1值， 并判断是否为空两字段合并效果为’param_name(param_name_value)’)

拆分
select SUBSTRING_INDEX(SUBSTRING_INDEX(partake_codes, ‘|’, help_topic_id + 1), ‘|’, -1) AS num
FROM mysql.help_topic a, t_task_list b WHERE help_topic_id < LENGTH(partake_codes) - LENGTH(REPLACE(partake_codes, ‘|’, ‘’)) + 1
(将表中partake_codes字段所有数据拆分出来 )

6.复杂查询例子全集：
1)select
t1.task_code,t2.report_code,t2.entrust_dept,
(select item_name from b_item where item_code = t2.item_code) item_name,
(select group_concat(sample_name separator ‘、’) from t_sample where entrust_code = t2.entrust_code) sample_name,
(case t2.entrust_type when 1 then ‘送样检测’ when 2 then ‘见证取样’ when 3 then ‘抽样检测’ when 4 then ‘现场检测’ else ‘’ end) entrust_type_name,
date_format(t2.entrust_date,’%Y年%m月%d日’)entrust_date,t2.entrust_code,t2.project_name,
(select use_position from t_sample where entrust_code = t2.entrust_code limit 0,1) project_position,
CONCAT_WS(’、’,t2.entrust_people,t2.entrust_phone) sample_people,
(select sample_code_e from t_sample where entrust_code = t2.entrust_code limit 0,1) sample_code_e,
ifnull(t2.witness_dept,’’)witness_dept,
ifnull(t2.witness,’’)witness,
(select model from t_sample where entrust_code = t2.entrust_code limit 0,1) model,
(select sample_num from t_sample where entrust_code = t2.entrust_code limit 0,1) sample_num,
(select factory from t_sample where entrust_code = t2.entrust_code limit 0,1) factory,
(select sample_desc from t_sample where entrust_code = t2.entrust_code limit 0,1) sample_desc,
(select group_concat(concat(file_name1)separator ‘;’) from(select distinct file_name1 from t_entrust_method m,b_parameter_method n where m.method_id = n.method_id and m.entrust_code = ‘{1}’)t) methods,
(select GROUP_CONCAT(distinct IFNULL(b.param_name,’’),(case when (ISNULL(b.param_name_value)=1) || (LENGTH(trim(b.param_name_value))=0) then ‘’ else ‘(’ end),IFNULL(b.param_name_value,’’),(case when (ISNULL(b.param_name_value)=1) || (LENGTH(trim(b.param_name_value))=0) then ‘’ else ‘)’ end) SEPARATOR ‘、’)param_name from t_entrust_param b where entrust_code = t2.entrust_code) check_item,
(select group_concat(concat(uc)separator ‘;’) from (select DISTINCT uc from (select trim(file_name1) uc from t_entrust_judge m,b_parameter_judge n where m.judge_id = n.judge_id and m.entrust_code = ‘{1}’) as a where uc is not null and uc not in(’’) UNION SELECT distinct method_value from t_entrust_method where entrust_code = ‘{1}’ and method_value != ‘’)t ) judge_name,
(select group_concat(concat(dev_name,’(’,dev_code,’)’) separator ‘、’) from dev_used where entrust_code = t2.entrust_code)dev_name,
(select address from glq11_04_001_0 where task_code = t1.task_code) address,
(select ifnull(test_env1,’’) from bg_lqcjl where task_code = t1.task_code) test_env1,
(select ifnull(test_env2,’’) from bg_lqcjl where task_code = t1.task_code) test_env2,
(select concat((case when locate(’~’,test_env1) > 0 then ‘(’ else ‘’ end),ifnull(test_env1,’’),(case when locate(’~’,test_env1) > 0 then ‘)’ else ‘’ end),‘℃/’,(case when locate(’~’,test_env1) > 0 then ‘(’ else ‘’ end),ifnull(test_env2,’’),(case when locate(’~’,test_env1) > 0 then ‘)’ else ‘’ end),’%RH’) from bg_lqcjl where task_code = t1.task_code) test_env,
t1.test_result,
t2.flag,
concat_ws(’、’,t1.main_name,REPLACE(t1.partake_names,’|’,’、’)) main_name,
(case when locate(‘1900’,t1.distribute_date) > 0 then ‘年 月 日’ else date_format(t1.distribute_date, ‘%Y年%m月%d日’) end) distribute_date,
(case when locate(‘1900’,t1.ratify_date) > 0 then ‘年 月 日’ else date_format(t1.ratify_date, ‘%Y年%m月%d日’) end) ratify_date,
‘江西省公路工程检测中心’ test_unit,
t1.remark,
(case when locate(‘1900’,t1.file_date) > 0 then ‘年 月 日’ else date_format(t1.file_date, ‘%Y年%m月%d日’) end) file_date,
t1.verify_name,
t1.ratify_name,
t1.main_name pic_main_name,
t3.*
from t_task_list t1,t_entrust_list t2,bg_lqcjl t3
where t1.entrust_code = t2.entrust_code and t1.task_code = t3.task_code and t1.task_code = ‘{0}’

2)select dev_code, GROUP_CONCAT(file_name separator ‘|’)file_name,GROUP_CONCAT(date separator ‘|’)date
from (select b.department_code,a.dev_code, a.file_name, SUBSTRING_INDEX(SUBSTRING_INDEX(a.file_name,’’,2),’’,-1)date from b_file_dev_mgr a,dev_all b
where a.dev_code = b.dev_code and (b.department_code=’{0}’ or b.admin_code = ‘{0}’) and a.type =1 and (a.file_name like ‘%校准证书%’ or a.file_name like ‘%校准确认%’) )tt GROUP BY dev_code

总结
作为从事一年程序员，个人观点：数据库中crud操作中，增删改操作基本固定，提升效率可以用唯一索引，批量插入可以选择insert into values、insert into select类似这些语句。然后就是就是就是查询，如何高效的查询出业务需求的数据方便业务逻辑的书写是我们经常和主要会遇到的问题。（一般对于复杂的查询我一般会选择先写from table where 优先过滤数据 在考虑 select 的写法）对于复杂的业务逻辑也可以选择事务，触发器，存储过程，不过学会这些之前，先学好上诉总结内容。