SQL-MICK基础

/*Select语句完整的执行顺序：
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、使用order by对结果集进行排序。
8、select 集合输出。*/
create database
create table shohin

(shohin_id char(4) not null,
shohin_mei varchar(100) not null,
shohin_bunrui varchar(32) not null,
hanbai_tanka int,
shiire_tanka int,
torokubi datetime,
primary key(shohin_id)
)

select * from shohin
--insert into 美 [?n?s?t ??ntu], values 美 [‘vælj?z] 值( value的名词复数
insert into shohin values (‘0001‘, ‘t恤衫‘, ‘衣服‘, ‘1000‘, ‘500‘, ‘2009-09-20‘)
insert into shohin values (‘0002‘, ‘打孔器‘, ‘办公用品‘, ‘500‘, ‘320‘, ‘2009-09-20‘)
insert into shohin values (‘0003‘, ‘运动t恤‘, ‘衣服‘, ‘4000‘, ‘2800‘, null)
insert into shohin values (‘0004‘, ‘菜刀‘, ‘厨房用具‘, ‘3000‘, ‘2800‘, ‘2009-09-20‘)
insert into shohin values (‘0005‘, ‘高压锅‘, ‘厨房用具‘, ‘6800‘, ‘5000‘, ‘2009-01-15‘)
insert into shohin values (‘0006‘, ‘叉子‘, ‘厨房用具‘, ‘500‘, null , ‘2009-09-20‘)
insert into shohin values (‘0007‘, ‘擦菜板‘, ‘厨房用具‘, ‘880‘, ‘790‘, ‘2008-04-28‘)
insert into shohin values (‘0008‘, ‘圆珠笔‘, ‘办公用具‘, ‘100‘, null , ‘2009-11-11‘)
select * from shohin
select shohin_id, shohin_mei, shiire_tanka from shohin
select shohin_id as 商品编号, shohin_mei as 商品名称, shiire_tanka as 进货价格 from shohin
--distinct 美 [d??st??kt] 不同的 删除重复行
select distinct shohin_bunrui from shohin
select distinct shohin_bunrui, torokubi from shohin
----where
select shohin_bunrui, shiire_tanka from shohin where shohin_bunrui=‘衣服‘
-----算术运算符和比较运算符+-*/,= <> >= > <= <
select * from shohin
----列*2
select shohin_id, shohin_mei, hanbai_tanka *2 as "hanbai_tanka_*2" from shohin
---列大于1000的记录
select shohin_mei, shiire_tanka from shohin where shiire_tanka>1000
----日期之前的记录
select shohin_mei, torokubi from shohin where torokubi<‘2009-09-27‘
----列、列比较
select shohin_mei, hanbai_tanka, shiire_tanka from shohin where hanbai_tanka - shiire_tanka >=500
----选取列为空记录
select * from shohin where shiire_tanka is null
----逻辑运算符
--not
select shohin_mei, shiire_tanka from shohin where not shiire_tanka>1000
---and(并且) ,or(或者)运算符
select shohin_mei, shohin_bunrui, shiire_tanka from shohin
where shohin_bunrui=‘厨房用具‘ and shiire_tanka >1000
select shohin_mei, shohin_bunrui, shiire_tanka from shohin
where shiire_tanka >=500 and shiire_tanka <=4000
---true 真值 false 假值
----售价9折利润大于100
select * from shohin
where (hanbai_tanka *0.9 - shiire_tanka) >100
---聚合与排序--count[ka?nt]行数,sum求和,avg平均值,max最大值,min最小值

select count(shohin_mei) as 商品总数 from shohin where shiire_tanka is not null
select sum(shiire_tanka) from shohin
select avg(shiire_tanka) from shohin
select max(shiire_tanka) from shohin
select min(shiire_tanka) from shohin
--商品类别行数
select count(distinct shohin_bunrui) from shohin
---分组group by 按商品类别统计商品分类数量
select shohin_bunrui, count(*) as 数量 from shohin group by shohin_bunrui
---having
select shohin_bunrui as ‘衣服‘, count(*) as ‘行数‘ from shohin
group by shohin_bunrui
having count(*)=2

select shohin_bunrui as ‘类别‘, count(*) as ‘数量‘from shohin
group by shohin_bunrui
having shohin_bunrui=‘衣服‘

----order by [??:rd?(r)] 排序 desc(dscendent下降),asc(ascendent上升)
select * from shohin
select shohin_id,shohin_mei,hanbai_tanka,shiire_tanka from shohin
order by hanbai_tanka
--不在select也可order by ,聚合函数order by
select shohin_bunrui as ‘商品名称‘, hanbai_tanka as ‘售价‘,
shiire_tanka as ‘进价‘ from shohin order by shohin_id;
select shohin_bunrui as ‘类别‘, count(*) as ‘数量‘from shohin
group by shohin_bunrui order by count(*);

----表复制insert into select,default[d??f?lt] 缺省
Insert into Table2(field1,field2,...) select value1,value2,... from Table1
create table shohincopy
(shohin_id char(4) not null,
shohin_mei varchar(100),
shohin_bunrui varchar(32),
hanbai_tanka int,
shiire_tanka int,
torokubi datetime,
primary key (shohin_id)
)
insert into shohincopy(shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka)
select shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka from shohin;
select * from shohincopy;

----数据删除delete [di?lit]
delete from shohincopy
delete from shohincopy where hanbai_tanka >4000
----数据更新update
update shohincopy set torokubi = ‘2009-10-10‘;
update shohincopy set hanbai_tanka = hanbai_tanka * 10
where shohin_bunrui = ‘厨房用具‘;
select * from shohincopy
where shohin_bunrui = ‘厨房用具‘;
update shohincopy set torokubi = ‘2009-10-10‘,
hanbai_tanka = hanbai_tanka * 10
where shohin_bunrui = ‘厨房用具‘;

----事务:begin[?beɡ?n]开始 ,transaction[træn?sæk??n]事务,commit 提交;
begin transaction
update shohincopy set hanbai_tanka=hanbai_tanka - 1000 where shohin_mei =‘t恤衫‘
update shohincopy set hanbai_tanka=hanbai_tanka + 1000 where shohin_mei = ‘t恤衫‘
commit;
--事务回滚 rollback[?ro?lbæk]
begin transaction
update shohincopy set hanbai_tanka=hanbai_tanka - 1000 where shohin_mei =‘t恤衫‘
update shohincopy set hanbai_tanka=hanbai_tanka + 1000 where shohin_mei = ‘t恤衫‘
rollback;

----视图、子查询、关联子查询
select * from shohin
select shohin_bunrui, sum(hanbai_tanka) as ‘售价和‘, sum(shiire_tanka) as ‘进价和‘
from shohin group by shohin_bunrui
--view[vju]视图
create view shohinsum(shohin_bunrui, cnt_shohin)
as
select shohin_bunrui, count(*) from shohin
group by shohin_bunrui;
select shohin_bunrui as ‘商品类别‘,cnt_shohin as ‘数量‘ from shohinsum
--删除视图
drop view shohinsum
--cascade[kæ?sked]关联
drop view shohinsum cascade

----子查询
select shohin_bunrui as ‘商品类别‘, cnt_shohin as ‘数量‘
from(select shohin_bunrui,count(*) as cnt_shohin from shohin
group by shohin_bunrui
)
as shohinsum;
--多层子查询
略
----标量子查询
--销售单价高于平均单价
select shohin_id, shohin_mei, hanbai_tanka from shohin
where hanbai_tanka > (select avg(hanbai_tanka)from shohin)
--having
select shohin_bunrui, avg(hanbai_tanka) from shohin
group by shohin_bunrui
having avg(hanbai_tanka) > (select avg(hanbai_tanka) from shohin);
----关联子查询
select shohin_id, shohin_mei,hanbai_tanka from shohin as s1
where hanbai_tanka > (select avg(hanbai_tanka)from shohin as s2
where s1.shohin_bunrui = s2.shohin_bunrui
group by shohin_bunrui);
--练习题
select * from shohin
--建立视图包含商品名称，销售单价大于500，日期=2009-09-20
create view viewrenshu5_1(shohin_mei, hanbai_tanka, torokubi)
as
select shohin_mei, hanbai_tanka, torokubi from shohin
where hanbai_tanka >= 500 and torokubi =‘2009-09-20‘
group by shohin_mei, hanbai_tanka, torokubi;
select * from viewrenshu5_1
----函数、谓词、CASE表达式
--numeric [nu:‘mer?k]数值型sample样品math数学
create table sampleMath(m numeric (10,3),
n int,
p int
)

select * from sampleMath
begin transaction
insert into sampleMath(m,n,p) values (500 , 0, null)
insert into sampleMath(m,n,p) values (-180, 0, null)
insert into sampleMath(m,n,p) values (null, 0, null)
insert into sampleMath(m,n,p) values (null, 0, null)
insert into sampleMath(m,n,p) values (null, 5, 2)
insert into sampleMath(m,n,p) values (null, 4, 3)
insert into sampleMath(m,n,p) values (8, null, 3)
insert into sampleMath(m,n,p) values (2.27, 1, null)
insert into sampleMath(m,n,p) values (5.555, 2, null)
insert into sampleMath(m,n,p) values (null, 1, null)
insert into sampleMath(m,n,p) values (8.76, null, null)
commit
--ABS绝对值
select abs(m) as abs_col from sampleMath
--MOD求余modulo[‘m?dj??lo?]系数,ft sql sever不支持
--四舍五入round[ra?nd]
select m, n, round(m, n) as round_col from sampleMath
----字符串函数
create table sampleStr
(str1 varchar(40),
str2 varchar(40),
str3 varchar(40)
)
begin transaction
insert into sampleStr(str1, str2, str3) values (‘多‘, ‘情‘,‘的‘ )
insert into sampleStr(str1, str2, str3) values (null, null, null)
insert into sampleStr(str1, str2, str3) values (‘土‘, ‘地‘, ‘。‘)
insert into sampleStr(str1, str2, str3) values (‘aaa‘, null, null)
insert into sampleStr(str1, str2, str3) values (‘@#$%$‘, null, null)
insert into sampleStr(str1,str2, str3) values (‘abc‘, null, null)
insert into sampleStr(str1, str2, str3) values (‘aBC‘, null, null)
insert into sampleStr(str1, str2, str3) values (‘小‘, ‘淘‘, ‘气‘)
insert into sampleStr(str1, str2, str3) values (‘abcdef‘, ‘abc‘, ‘abc‘)
insert into sampleStr(str1, str2, str3) values (‘df‘, ‘bddd‘, ‘wewer‘)
commit
delete from sampleStr
select * from sampleStr
--||拼接
select str1, str2, str3, str1+str2+str3 as str_commit from sampleStr
--字符串长度length[le?θ]长度
select str1, len(str1) as len_str from sampleStr
----lower[?lo??(r)]小写 大写upper [??p?]
select str1, lower(str1) as low_str from sampleStr
select str1, upper(str1) as up_str from sampleStr
----时间函数current_timestamp,current[?k?:r?nt]现在的,timestamp[‘ta?mstæmp]时间戳
--获取当前日期and时间
select current_timestamp
begin transaction
select datepart(year,getdate())
select datepart(month, getdate())
select datepart(day, getdate())
select datepart(hour, getdate())
select datepart(minute, getdate())
select datepart(second, getdate())
commit
----CAST[kæst]类型转换
--字符转数字
select cast(‘0001‘ as int) as int_col
--字符转日期
select cast(‘2009-12-14‘ as datetime) as datetime_col
----谓词 like,bentween[b??twin]在...之间 ,is null,is not null,in, exists
--like
create table samplelike
(strcol varchar(6) not null primary key (strcol))
begin transaction
insert into samplelike(strcol) values(‘abcddd‘)
insert into samplelike(strcol) values(‘dddabc‘)
insert into samplelike(strcol) values(‘abdddc‘)
insert into samplelike(strcol) values(‘abcdd‘)
insert into samplelike(strcol) values(‘ddabc‘)
insert into samplelike(strcol) values(‘abddc‘)
commit
select * from samplelike where strcol like ‘%ddd‘
select * from samplelike where strcol like ‘%ddd%‘
select * from samplelike where strcol like ‘ddd%‘
--bentween[b??twin]在...之间
select shohin_mei, hanbai_tanka from shohin
where hanbai_tanka between 100 and 1000;
select shohin_mei, shiire_tanka from shohin
where shiire_tanka is null;
--in,or
select shohin_mei,shiire_tanka from shohin
where shiire_tanka = 320
or shiire_tanka = 500
or shiire_tanka = 5000;
select shohin_mei,shiire_tanka from shohin
where shiire_tanka in (320, 500, 5000);
----in和子查询
create table tenposhohin
(tenpo_id char(4) not null,
tenpo_mei varchar(200) not null,
shohin_id char(4) not null,
suryo int)
drop table tenposhohin
select * from tenposhohin
begin transaction
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000a‘, ‘北京‘, ‘0001‘,30)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000a‘, ‘北京‘, ‘0002‘,50)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000a‘, ‘北京‘, ‘0003‘,15)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0004‘,30)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0005‘,120)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0006‘,20)
insert into tenmposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0007‘,40)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0003‘,20)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0004‘,50)
insert into tenmposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0005‘,90)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0007‘,70)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000d‘, ‘家乡‘, ‘0001‘,100)
commit;
select shohin_mei,hanbai_tanka from shohin
select * from shohin
where shohin_id in (select shohin_id from tenposhohin
where tenpo_id = ‘000c‘);
----exist[iɡ?zists]
select shohin_mei,hanbai_tanka from shohin as s
where exists (select * from tenposhohin as TS
where TS.tenpo_id = ‘000c‘
and TS.tenpo_id = S.shohin_id
);
--not exist
select shohin_mei,hanbai_tanka from shohin as s
where not exists (select * from tenposhohin as TS
where TS.tenpo_id = ‘000c‘
and TS.tenpo_id = S.shohin_id
);
----case[kes]表达式把…装入箱
--搜索表达式
select shohin_mei,
case when shohin_bunrui = ‘衣服‘
then ‘a‘ + shohin_bunrui
when shohin_bunrui = ‘办公用品‘
then ‘b‘ + shohin_bunrui
when shohin_bunrui = ‘厨房用具‘
then ‘c‘+ shohin_bunrui
else null
end as abc_shohin_bunrui
from shohin
order by abc_shohin_bunrui;
----表的加法union
create table shohin2
(shohin_id char(4) not null,
shohin_mei varchar(100) not null,
shohin_bunrui varchar(32) not null,
hanbai_tanka int,
shiire_tanka int,
torokubi datetime,
primary key(shohin_id)
)
select * from shohin2
begin transaction
insert into shohin2 values (‘0001‘, ‘t恤衫‘, ‘衣服‘, ‘1000‘, ‘500‘, ‘2008-09-20‘)
insert into shohin2 values (‘0002‘, ‘打孔器‘, ‘办公用品‘, ‘500‘, ‘320‘, ‘2009-09-20‘)
insert into shohin2 values (‘0003‘, ‘运动t恤‘, ‘衣服‘, ‘4000‘, ‘2800‘, null)
insert into shohin2 values (‘0004‘, ‘菜刀‘, ‘厨房用具‘, ‘3000‘, ‘2800‘, ‘2009-09-20‘)
insert into shohin2 values (‘0009‘, ‘手套‘, ‘衣服‘, ‘800‘, ‘500‘, null)
insert into shohin2 values (‘0010‘, ‘水壶‘, ‘厨房用具‘, ‘2000‘, ‘1700‘ , ‘2009-09-20‘)
commit;
--表表union
select shohin_id, shohin_mei, shohin_bunrui from shohin
union
select shohin_id, shohin_mei, shohin_bunrui from shohin2;
-- 加order by
select shohin_id, shohin_mei, shohin_bunrui from shohin
union
select shohin_id, shohin_mei, shohin_bunrui from shohin2
where shohin_bunrui = ‘厨房用具‘
order by shohin_id
--union all
select shohin_id, shohin_mei, shohin_bunrui from shohin
union all
select shohin_id, shohin_mei, shohin_bunrui from shohin2
order by shohin_bunrui;
--选取公共部分intersect[??nt?r?sekt]交叉
select shohin_id, shohin_mei, shohin_bunrui from shohin
intersect
select shohin_id, shohin_mei, shohin_bunrui from shohin2
order by shohin_id
--记录减法except[?k?s?pt]把...除外
select shohin_id,shohin_mei from shohin
except
select shohin_id, shohin_mei from shohin2
order by shohin_id;
----谓词 like,bentween[b??twin]在...之间 ,is null,is not null,in, exists
--like
create table samplelike
(strcol varchar(6) not null primary key (strcol))
begin transaction
insert into samplelike(strcol) values(‘abcddd‘)
insert into samplelike(strcol) values(‘dddabc‘)
insert into samplelike(strcol) values(‘abdddc‘)
insert into samplelike(strcol) values(‘abcdd‘)
insert into samplelike(strcol) values(‘ddabc‘)
insert into samplelike(strcol) values(‘abddc‘)
commit
select * from samplelike where strcol like ‘%ddd‘
select * from samplelike where strcol like ‘%ddd%‘
select * from samplelike where strcol like ‘ddd%‘
--bentween[b??twin]在...之间
select shohin_mei, hanbai_tanka from shohin
where hanbai_tanka between 100 and 1000;
select shohin_mei, shiire_tanka from shohin
where shiire_tanka is null;
--in,or
select shohin_mei,shiire_tanka from shohin
where shiire_tanka = 320
or shiire_tanka = 500
or shiire_tanka = 5000;
select shohin_mei,shiire_tanka from shohin
where shiire_tanka in (320, 500, 5000);
----in和子查询
create table tenposhohin
(tenpo_id char(4) not null,
tenpo_mei varchar(200) not null,
shohin_id char(4) not null,
suryo int)
drop table tenposhohin
select * from tenposhohin
begin transaction
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000a‘, ‘北京‘, ‘0001‘,30)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000a‘, ‘北京‘, ‘0002‘,50)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000a‘, ‘北京‘, ‘0003‘,15)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0004‘,30)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0005‘,120)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0006‘,20)
insert into tenmposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0007‘,40)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0003‘,20)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0004‘,50)
insert into tenmposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0005‘,90)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0007‘,70)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000d‘, ‘家乡‘, ‘0001‘,100)
commit;
select shohin_mei,hanbai_tanka from shohin
select * from shohin
where shohin_id in (select shohin_id from tenposhohin
where tenpo_id = ‘000c‘);
----exist[iɡ?zists]
select shohin_mei,hanbai_tanka from shohin as s
where exists (select * from tenposhohin as TS
where TS.tenpo_id = ‘000c‘
and TS.tenpo_id = S.shohin_id
);
--not exist
select shohin_mei,hanbai_tanka from shohin as s
where not exists (select * from tenposhohin as TS
where TS.tenpo_id = ‘000c‘
and TS.tenpo_id = S.shohin_id
);
----case[kes]表达式把…装入箱
--搜索表达式
select shohin_mei,
case when shohin_bunrui = ‘衣服‘
then ‘a‘ + shohin_bunrui
when shohin_bunrui = ‘办公用品‘
then ‘b‘ + shohin_bunrui
when shohin_bunrui = ‘厨房用具‘
then ‘c‘+ shohin_bunrui
else null
end as abc_shohin_bunrui
from shohin
order by abc_shohin_bunrui;
--选取公共部分intersect[??nt?r?sekt]交叉
select shohin_id, shohin_mei, shohin_bunrui from shohin
intersect
select shohin_id, shohin_mei, shohin_bunrui from shohin2
order by shohin_id
--记录减法except[?k?s?pt]把...除外
select shohin_id,shohin_mei from shohin
except
select shohin_id, shohin_mei from shohin2
order by shohin_id;
----谓词 like,bentween[b??twin]在...之间 ,is null,is not null,in, exists
--like
create table samplelike
(strcol varchar(6) not null primary key (strcol))
begin transaction
insert into samplelike(strcol) values(‘abcddd‘)
insert into samplelike(strcol) values(‘dddabc‘)
insert into samplelike(strcol) values(‘abdddc‘)
insert into samplelike(strcol) values(‘abcdd‘)
insert into samplelike(strcol) values(‘ddabc‘)
insert into samplelike(strcol) values(‘abddc‘)
commit
select * from samplelike where strcol like ‘%ddd‘
select * from samplelike where strcol like ‘%ddd%‘
select * from samplelike where strcol like ‘ddd%‘
--bentween[b??twin]在...之间
select shohin_mei, hanbai_tanka from shohin
where hanbai_tanka between 100 and 1000;
select shohin_mei, shiire_tanka from shohin
where shiire_tanka is null;
--in,or
select shohin_mei,shiire_tanka from shohin
where shiire_tanka = 320
or shiire_tanka = 500
or shiire_tanka = 5000;
select shohin_mei,shiire_tanka from shohin
where shiire_tanka in (320, 500, 5000);
----in和子查询
create table tenposhohin
(tenpo_id char(4) not null,
tenpo_mei varchar(200) not null,
shohin_id char(4) not null,
suryo int)
drop table tenposhohin
select * from tenposhohin
begin transaction
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000a‘, ‘北京‘, ‘0001‘,30)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000a‘, ‘北京‘, ‘0002‘,50)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000a‘, ‘北京‘, ‘0003‘,15)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0004‘,30)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0005‘,120)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0006‘,20)
insert into tenmposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000b‘, ‘上海‘, ‘0007‘,40)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0003‘,20)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0004‘,50)
insert into tenmposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0005‘,90)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000c‘, ‘成都‘, ‘0007‘,70)
insert into tenposhohin(tenpo_id, tenpo_mei, shohin_id, suryo)
values (‘000d‘, ‘家乡‘, ‘0001‘,100)
commit;
select shohin_mei,hanbai_tanka from shohin
select * from shohin
where shohin_id in (select shohin_id from tenposhohin
where tenpo_id = ‘000c‘);
----exist[iɡ?zists]
select shohin_mei,hanbai_tanka from shohin as s
where exists (select * from tenposhohin as TS
where TS.tenpo_id = ‘000c‘
and TS.tenpo_id = S.shohin_id
);
--not exist
select shohin_mei,hanbai_tanka from shohin as s
where not exists (select * from tenposhohin as TS
where TS.tenpo_id = ‘000c‘
and TS.tenpo_id = S.shohin_id
);
----case[kes]表达式把…装入箱
--搜索表达式
select shohin_mei,
case when shohin_bunrui = ‘衣服‘
then ‘a‘ + shohin_bunrui
when shohin_bunrui = ‘办公用品‘
then ‘b‘ + shohin_bunrui
when shohin_bunrui = ‘厨房用具‘
then ‘c‘+ shohin_bunrui
else null
end as abc_shohin_bunrui
from shohin
order by abc_shohin_bunrui;
----窗口函数
--1.聚合sum,avg,count,max,min
--2.rank[ræ?k]排列,dense(密集)_rank,row(排、列)_number
--partition[pɑ:r?t??n]区分
--<窗口函数> over (partition by <列1> order by <列2>)
select shohin_mei, shohin_bunrui, hanbai_tanka,
rank () over (partition by shohin_bunrui
order by hanbai_tanka) as ranking
from shohin;
--比较rank dense_rank row_number
select shohin_mei, shohin_bunrui, hanbai_tanka,
rank () over (order by hanbai_tanka) as ranking,
dense_rank () over (order by hanbai_tanka) as dense_ranking,
row_number () over (order by hanbai_tanka) as row_num
from shohin;
--两个order by
select shohin_mei, shohin_bunrui, hanbai_tanka,
rank () over (order by hanbai_tanka) as ranking
from shohin
order by ranking;
----grouping运算符(rollup汇总套件,cube[kjub]立方体,grouping sets )
--rollup
select shohin_bunrui, sum (hanbai_tanka) as sum_tanka from shohin
group by shohin_bunrui with rollup;
--cube
select shohin_bunrui, sum(hanbai_tanka) as sum_tanka from shohin
group by shohin_bunrui with cube;
--grouping分辨null值
select grouping (shohin_bunrui) as shohin_bunrui,
grouping (torokubi) as torokubi,
sum (hanbai_tanka) as sum_tanka from shohin
group by shohin_bunrui, torokubi with rollup ;
--grouping过程插入字符串
select case when grouping(shohin_bunrui) = 1
then ‘商品种类 合计‘
else shohin_bunrui end as shohin_bunrui,
case when grouping(torokubi) = 1
then ‘登记日期 合计‘
else torokubi end as torokubi,
sum(hanbai_tanka) as sum_tanka
from shohin
group by shohin_bunrui, torokubi with cube ;
--cube数据积木(DB2)
select case when grouping(shohin_bunrui) = 1
then ‘商品种类 合计‘
else shohin_bunrui end as shoin_bunrui,
case when grouping(torokubi) = 1
then ‘登记日期 合计‘
else case (torokubi as varchar(16)) end as torokubi,
sum(hanbai_tanka) as sum_tanka
from shohin
group by shohin_bunrui, torokubi with cube;
--grouping sets取出期望积木(DB2)
select case when grouping(shohin_bunrui) = 1
then ‘商品种类 合计‘
else shohin_bunrui end as shoin_bunrui,
case when grouping(torokubi) = 1
then ‘登记日期 合计‘
else case (torokubi as varchar(16)) end as torokubi,
sum(hanbai_tanka) as sum_tanka
from shohin
group by grouping sets (shohin_bunrui, torokubi );