2021-02-28 SQL server实践记录---3Select

举例用相关表

CangKu[cangkuid, cangkuhao, chengshi, mianji, chuangjiansj]

cangkuid	cangkuhao	chengshi	mianji	chuangjiansj
1	wh1	北京	900	2020-08-01 00:00:00.000
2	wh2	Null	600	2020-08-02 00:00:00.000
3	wh3	杭州	700	2020-08-03 00:00:00.000
4	wh4	余杭	1000	2020-08-04 00:00:00.000
5	wh5	青岛	500	2020-08-05 00:00:00.000
6	wh6	余杭	400	2021-02-21 00:00:00.000

DingGouDan[dinggoudid, gongyingsh, zhigonghao, dinggourq, jine]

dinggoudid	gongyingsh	zhigonghao	dinggoudh	dinggourq	jine
6	s3	zg11	or6	2019-10-07 00:00:00.000	4600
7	s4	zg13	or7	2019-01-27 00:00:00.000	4100
8	s1	zg15	or8	2019-09-27 00:00:00.000	4500
12	s5	zg10	or12	2019-03-04 00:00:00.000	7645
16	s2	zg18	or16	2018-09-26 00:00:00.000	1623
18	s2	zg18	or18	2018-11-28 00:00:00.000	7894
19	s4	zg15	or19	2018-08-16 00:00:00.000	4668
20	s2	zg16	or20	2019-11-24 00:00:00.000	6852
21	s5	zg16	or21	2018-07-18 00:00:00.000	4665
22	s2	zg16	or22	2017-11-17 00:00:00.000	8520
24	s5	zg01	or1	2020-08-02 00:00:00.000	5000
25	s1	zg02	or2	2020-08-01 00:00:00.000	8700
26	s1	zg01	or3	2019-10-13 00:00:00.000	5400
27	s5	zg02	or4	2019-10-10 00:00:00.000	5200
32	s5	zg04	or9	2019-08-14 00:00:00.000	4812
33	s2	zg08	0r10	2019-07-13 00:00:00.000	4613
36	s5	zg09	or13	2019-04-07 00:00:00.000	2846
37	s2	zg07	or14	2019-03-05 00:00:00.000	5195
38	s4	zg07	or15	2018-05-09 00:00:00.000	8946
40	s5	zg05	or17	2018-04-19 00:00:00.000	7645
47	s2	zg18	or24	2017-08-28 00:00:00.000	2541

GongYingShang[GongYingSID , GongYingSH, GongYingSMC, ChengShi, LianXiDH, Email]

GongYingSID	GongYingSH	GongYingSMC	ChengShi	LianXiDH	Email
1	s1	青岛三元电子厂	青岛	0532-836698754	shangyuan@163.com
2	s2	济南轻工业集团	济南	0531-83698754	jinan@126.com
3	s3	济南电子原件有限公司	济南	0531-83698754	jinansddd@126.com
4	s4	上海天桥厂	上海	83698684	shanghai@sina.com
5	s5	青岛天桥厂	青岛	83698684	qingdaones@sina.com

ZhiGong[ZhiGongID, ZhiGongHao, CangKuHao, XingMing, XingBie, GongZi]

ZhiGongID	ZhiGongHao	CangKuHao	XingMing	XingBie	GongZi
1	zg01	wh1	刘岗	女	1250
2	zg02	wh1	林安平	NULL	1310
3	zg03	wh2	张可	女	2250
4	zg04	wh1	吴平台	男	1850
5	zg05	wh3	王峰	男	1350
6	zg06	wh2	吴艳平	女	1500
7	zg07	wh4	张亮	男	1650
8	zg08	wh2	刘或生	男	1750
9	zg09	wh3	王静	女	1250
10	zg10	wh3	李明	男	1825
11	zg11	wh4	鄙平	男	1890
12	zg12	wh4	李宁	女	1459
13	zg13	wh2	吴忠成	男	2150
14	zg14	wh4	周岗	男	1660
15	zg15	wh1	林森	NULL	1910
16	zg16	wh2	陈璐	女	2155
17	zg17	wh4	Baobo	女	3250
18	zg18	wh4	Angela	女	3335

各种Select语句

select * from zhigong;
select * from zhigong where zhigongid = '1';
select * from zhigong where gongzi > 2000 and cangkuhao != 'wh2';
--运算符优先级	() → Not、+、- → *、/ → 比较运算符 → And → Or
--谓词
select * from zhigong where zhigonghao = 'zg1' or zhigonghao = 'zg2';	--or
select * from zhigong where zhigonghao in ('zg1','zg2','zg3')			--in
select * from zhigong where zhigonghao not in ('zg1','zg2','zg3')		--not
select * from zhigong where xingming like '%艳%';						--模糊 like, '%'-0个或者多个通配，'_'-一个通配，'[]'-指定范围，'[^]'-不属于指定范围，如[^a-z]
select * from zhigong where xingbie is null;							--查询空值
select * from zhigong where gongzi between 1800 and 3000;				--between...and 包含边界（>=、<=）
--消除重复 distinct
select distinct gongzi from zhigong;				--只查询到gongzi，并且只有不同的值
select * from zhigong where cangkuhao > (select cangkuhao from cangku where chengshi = '北京');
select * from zhigong order by gongzi desc;			--降序-desc，升序-asc

--count(), sum(), avg(), max(), min()
select 	max(gongzi) as 最大工资, min(gongzi) as 最小工资, sum(gongzi) as 工资总和, avg(gongzi) as 平均工资, count(*) as 职工人数 from zhigong;
--compute by必须有order by配合使用
select * from zhigong order by cangkuhao
	compute avg(gongzi), max(gongzi), min(gongzi), sum(gongzi) by cangkuhao;	//有点尴尬，版本问题没查出来

分组查询
注意选择的分组需要有实际意义，本例的含义为“查询不同分组的工资情况”

select cangkuhao, 
	   max(gongzi) as maxgongzi, 
	   min(gongzi) as mingongzi,
	   sum(gongzi) as sumgongzi,
	   avg(gongzi) as avggongzi,
	   count(*) as numzhigong
  from zhigong
 group by CangKuHao;

本例分组查询结果如下：

--查询工资比职工01这个人多的工资总体情况，并且根据仓库号分组（带有条件）
select cangkuhao,max(gongzi),min(gongzi),sum(gongzi),count(*)
  from zhigong
 where gongzi > (select gongzi from zhigong where zhigonghao = 'zg01')
 group by cangkuhao;
--带有Having的分组查询。分组之前要用where，分组后用Having
--本例显示不同仓库的平均工资、工资总和、最大工资、最小工资和职工人数，并且查询结果质保函平均工资大于1760小于2000的.
select cangkuhao, 
	   max(gongzi) as maxgongzi, 
	   min(gongzi) as mingongzi,
	   sum(gongzi) as sumgongzi,
	   avg(gongzi) as avggongzi,
	   count(*) as numzhigong
  from zhigong
  group by CangKuHao
  having avg(gongzi) > 1760 and avg(gongzi) < 2000;

谓词与量词（exists, any, all）

--in和exists
select * from cangku where cangkuhao not in (select cangkuhao from zhigong);	//in
select * from cangku where not exists (select * from zhigong where cangkuhao = cangku.cangkuhao );	
--any
select * from zhigong where gongzi >= any(select gongzi from zhigong where cangkuhao = 'wh2');
--等同于
select * from zhigong where gongzi >= (select min(gongzi) from zhigong where cangkuhao = 'wh2');
--all
select * from zhigong where gongzi >= all(select gongzi from zhigong where cangkuhao = 'wh1');
--等同于
select * from zhigong where gongzi >= (select max(gongzi) from zhigong where cangkuhao = 'wh1');

top

select top 3 * from zhigong order by gongzi desc;			--工资前3的职工信息
select top 40 percent * from dinggoudan order by jine asc;	--金额前40%的订单信息
--查询工资第二高的工资
select (select distinct gongzi from zhigong order by gongzi limit 1,1) as 2stSZ;
--into
select * into zhigong_back from zhigong;					--强迫症  drop table zhigong_back;

集合的并和交运算
要求两个查询结果具有相同的字段个数，并且对应的字段的值要出自同一个值域（相同的数据类型和取值范围）

--并（union）
select * from cangku where chengshi = '北京'
union
select * from cangku where chengshi = '余杭';
--交（intersect）
select cangkuhao from cangku
intersect
select cangkuhao from zhigong;

连接查询
笛卡尔积
关系R（4行5列）和关系S（6行7列）
那么R和S做笛卡尔积后形成T（24行12列）的新关系。
以表zhigong（18行6列）、表cangku（6行5列）为例

select * from zhigong,cangku		--铸就是笛卡尔积

序号	ZhiGongID	ZhiGongHao	CangKuHao	XingMing	XingBie	GongZi	CangKuID	CangKuHao	ChengShi	MianJi	ChuangJianSJ
01	1	zg01	wh1	刘岗	女	1250	1	wh1	北京	900	2020-08-01 00:00:00.000
02	2	zg02	wh1	林安平	NULL	1310	1	wh1	北京	900	2020-08-01 00:00:00.000
03	3	zg03	wh2	张可	女	2250	1	wh1	北京	900	2020-08-01 00:00:00.000
04	4	zg04	wh1	吴平台	男	1850	1	wh1	北京	900	2020-08-01 00:00:00.000
…	…	…	…	…	…	…	…	…	…	…	…
108	18	zg18	wh4	Angela	女	3335	6	wh6	余杭	400	2021-02-21 00:00:00.000

这是从两张表中显示的所有字段，所以字段有重复，并且产生了108条记录，cangku表6条（行），zhigong表18条（行）
连接查询就是

select * from zhigong,cangku where zhigong.cangkuhao = cangku.cangkuhao		--就这？

内连接、左连接、右连接、全连接
显示面积大于600的仓库号、职工号、城市、面积和职工信息
1）内连接 - - - 只有满足连接条件的记录才会被查询出来

--内连接
select a.cangkuhao, zhigonghao, chengshi, mianji, gongzi from cangku a inner join zhigong b on b.cangkuhao = a.cangkuhao and mianji > 600;		--超连接
select a.cangkuhao, zhigonghao, chengshi, mianji, gongzi from cangku a, zhigong b where b.cangkuhao = a.cangkuhao and mianji > 600;		--普通连接

2）左连接 - - - 除了显示满足条件的记录之外，第一张表中不满足条件的记录也会被查询出来。这时不满足条件的记录中zhigonghao，gongzi为空

select a.cangkuhao, zhigonghao, chengshi, mianji, gongzi from cangku a left join zhigong b on b.cangkuhao = a.cangkuhao and mianji > 600;		--左连接

3）右连接 - - - 除了显示满足条件的记录外，第二张表中不满足条件的记录会被查询出来

select a.cangkuhao, zhigonghao, chengshi, mianji, gongzi from cangku a right join zhigong b on b.cangkuhao = a.cangkuhao and mianji > 600;		--右连接

4）全连接

select a.cangkuhao, zhigonghao, chengshi, mianji, gongzi from cangku a full join zhigong b on b.cangkuhao = a.cangkuhao and mianji > 600;		--全连接