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 | |
---|---|---|---|---|---|
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; --全连接