-
bedin; --啟動(dòng)事務(wù) insert into 表名 values (); --向表中插入數(shù)據(jù) rollback; --回滾事務(wù)查看全部
-
序列號(hào)<br> 使用場(chǎng)景：主鍵、業(yè)務(wù)主鍵<br> 生成方法：各個(gè)數(shù)據(jù)庫都有各自的支持，如下圖，要能保證唯一性就可以認(rèn)為是一個(gè)正確的序列號(hào)。當(dāng)然，也可以人為的來生成，不過唯一性就需要自己來保證了。所以，講者建議使用系統(tǒng)提供的方式來生成。需要注意的是，使用MySQL系統(tǒng)本身的序列化方式存在產(chǎn)生空洞的情況。查看全部
-
select user_name, case when c.id=1 then 'arms' when c.id=2 then 'clothing' when c.id=3 then 'shoe' end as equipment, coalesce(case when c.id=1 then arms end, case when c.id=2 then clothing end, case when c.id=3 then shoe end) as eq_name from user1 a join user1_equipment b on a.id=b.user_id cross join tb_sequence c where c.id<=3 order by user_name;查看全部
-
列轉(zhuǎn)行3 select user_name, case when c.id=1 then 'arms' when c.id=2 then 'clothing' when c.id=3 then 'shoe' end as equipment, coalesce(case when c.id=1 then arms end, case when c.id=2 then clothing end, case when c.id=3 then shoe end) as eq_name from user1 a join user1_equipment b on a.id=b.user_id cross join tb_sequence c where c.id<=3 order by user_name;查看全部
-
列轉(zhuǎn)行2 select user_name,'arms' as equipment, arms from user1 a join user1_equipment b on a.id=user_id union all select user_name,'clothing' as equipment, clothing from user1 a join user1_equipment b on a.id=user_id; union all select user_name,'shoe' as equipment, shoe from user1 a join user1_equipment b on a.id=user_id; 使用union all連接轉(zhuǎn)換： select user_name,'arms' as equipment, arms from user1 a join user1_equipment b on a.id=user_id union all select user_name,'clothing' as equipment, clothing from user1 a join user1_equipment b on a.id=user_id union all select user_name,'shoe' as equipment, shoe from user1 a join user1_equipment b on a.id=user_id查看全部
-
實(shí)現(xiàn)如圖的列轉(zhuǎn)行的方法： 1.使用union all 將結(jié)果集 合并 SELECT user_name,'arms' as equipment, arms FROM user1 a JOIN user1_equipment b ON a.id=b.user_id UNION ALL SELECT user_name,'clothing' AS equipment,clothing FROM user1 a JOIN user1_equipment b ON a.id=b.user_id UNION ALL SELECT user_name,'shoe' AS equipment,shoe FROM user1 a JOIN user1_equipment b ON a.id=b.user_id查看全部
-
創(chuàng)建系列表： create table tb_sequence(id int auto_increment not null,primary key(id)); 產(chǎn)生系列好： insert into tb_sequence values(),(),(),(),(),();查看全部
-
向表中添加字段：alter table 表名 add column 字段名 字段類型; 如：alter table test1 add column mobile varchar(100); 使用序列化方法轉(zhuǎn)換： select user_name, replace(substring(substring_index(mobile,','a.id),char_length(substring_index(mobile,',',a.id-1))+1),',','') as mobile --截取字符串 from tb_sequence as a cross join( select user_name, concat(mobile,',') as mobile, --在mobile結(jié)尾增加逗號(hào) length(mobile)-length(replace(mobile,',',''))+1 as size --通過mobile總長(zhǎng)度-排除逗號(hào)后的長(zhǎng)度+1計(jì)算得出總段數(shù) from user1 as b ) as b on a.id<=b.size查看全部
-
2.使用case語句進(jìn)行行列轉(zhuǎn)換<br> select sum(case when user_name='孫悟空' then kills end) as '孫悟空',<br> sum(case when user_name='豬八戒' then kills end) as '豬八戒',<br> sum(case when user_name='沙僧' then kills end) as '沙僧'<br> from user1 a join user_kills b on a.id=b.user_id;查看全部
-
select * from ( select sum(kills) as '孫悟空' from user1 a join user_kills b on a.id = b.use_id and a.user_name='孫悟空' ) a cross join ( select sum(kills) as '豬八戒' from user1 a join user_kills b on a.id = b.use_id and a.user_name='豬八戒' ) b cross join( select sum(kills) as '沙僧' from user1 a join user_kills b on a.id = b.use_id and a.user_name='沙僧' ) c查看全部
-
sql開發(fā)技巧之行轉(zhuǎn)列的應(yīng)用場(chǎng)景和基礎(chǔ)數(shù)據(jù)查看全部
-
行轉(zhuǎn)列場(chǎng)景：如報(bào)表統(tǒng)計(jì)，匯總顯示等查看全部
-
使用序列表方式列轉(zhuǎn)行查看全部
-
union列轉(zhuǎn)行查看全部
-
sql生成訂單號(hào)的一種方式查看全部
舉報(bào)
0/150
提交
取消