SQL 视图

--视图是对于一段sql的封装
--视图其实就是一张表(真实的，虚拟的表)

 

语句如下：

create view a
as
select *from stuInfo

查询 视图

select *from a;

这样就省去写那些代码的时间了

 

create table region(
id varchar(20) primary key, --主键,区域编号
name varchar(20) not null, --区域名称
pid varchar(20) not null --区域附属编号 0省份
);

insert into region
select 's2309','广东省','0'union
select 's2098','湖南省' ,'0'union
select 's2033','广西省' ,'0'union
select 's2034','永州市' ,'s2098'union
select 's2056','长沙市' ,'s2098'union
select 's2012','广东市' ,'s2309'union
select 's2089','东莞市' ,'s2309' union
select 's2037','怀化市' ,'s2098'

--查询所有的省分
select *from region where pid='0'

结果如下:

 

 

 

---- 查询湖南省下素有的市
select *from region where pid='s2098'

结果如下

 

 

select *from region where pid in(
select id from region where name ='湖南省'
)

结果如下：

 

 

select a.name,count(b.name)
from region a full join region b
on a.id=b.pid where a.name like '%省%'
group by a.name;

结果如下：

 

 

-- 查询性张的人: 张x,张xx,张xxx
select * from stuInfo where stuName like '张%';

-- 查询性张的人: 张xx
select * from stuInfo where stuName like '张__';

-- 查询以丽结尾的人: xx丽
select * from stuInfo where stuName like '%丽';

-- 查询名字带秋的人: 秋xx,x秋x,xx秋
select * from stuInfo where stuName like '%秋%';

 

 

create table changk(
ckid varchar(10) primary key,
ckcs varchar(40) not null,
rea int not null
);

insert into changk
select 'WH1','北京',370 union
select 'WH2','上海',500 union
select 'WH3','广州',200 union
select 'WH4','武汉',400

create table ding(
zhigong varchar(10) not null,
gong varchar(10) not null,
dgdh varchar(50) not null primary key,
tims datetime null
);

insert into ding
select 'E3','S7','OR67','2021-06-23'union
select 'E1','S4','OR73','2021-07-28'union
select 'E5','S4','OR76','2021-05-25'union
select 'E6','S6','OR77','2021-05-26'union
select 'E3','S4','OR79','2021-06-13'union
select 'E1','S2','OR80','2021-08-29'union
select 'E3','S3','OR90','2021-09-01'union
select 'E3','S3','OR91','2021-07-13'

create table gysb(
gysh varchar(10) not null primary key,
gname varchar(50) not null,
address varchar(100) not null
);

insert into gysb
select 'S2','名硕电子公司','苏州'union
select 'S3','振华电子厂','西安'union
select 'S4','华通电子公司','北京'union
select 'S6','607厂','郑州'union
select 'S7','爱华电子厂','北京'

create table zgb(
cgh varchar(10) not null,
zgh varchar(10) primary key not null,
money int not null
);

insert into zgb
select 'WH2','E1',1220union
select 'WH1','E3',1210union
select 'WH2','E4',1250union
select 'WH3','E5',1230union
select 'WH1','E6',1250

--使用SQL语句完成：
--DDL
--1.写出创建上述表的语句(并设定好主外键，以及其他约束)
--命令：

--2.给出插入上述数据的insert语句
--命令：

--单表查询
--3.检索职工关系中的所有信息
--结果：
SELECT *from zgb
--4.检索供应商关系中的所有信息
select *from gysb
--5.检索六月之后的所有订单
select *from ding where MONTH(tims)>5
--6.检索面积大于400的仓库
select *from changk where rea>400
--7.检索哪些职工的工资多于1210
select *from zgb where money>1210
--8.检索仓库是“WH1”或“WH2”并且面积大于400的城市
select ckcs from changk where rea>400 and ckid='WH1'or ckid='WH2'

--9.找出仓库面积在400到600的仓库
select *from changk where rea between 400 and 600

--10.找出名中包含 “厂”的所有供应商的名

select gname as '供应商名' from gysb where gysb.gname like '%厂%'

--11.找出不在西安的供应商
select *from gysb where not address='西安'

--12.找出不在北京的仓库
select *from changk where changk.ckcs!='北京'

--13.按工资降序排列出所有职工的信息
select *from zgb order by money desc;

--14.先按仓库号升序排列，再按工资降序排列
select *from zgb order by cgh , money desc

--15.在仓库表中统计一下有几个仓库
select COUNT(*)as '仓库数' from changk

-- 116.在职工表中统计一下有几个仓库

 

--17.求总的仓库面积
select SUM(rea)as'总面积' from changk

--18.每个职工的订单数
select zhigong as'职工号',COUNT(*)as'订单数' from ding group by ding.zhigong

--19.订单数大于3的职工

select zhigong as'职工号',COUNT(*)as'订单数' from ding group by ding.zhigong having COUNT(*)>3

--20.找出在面积大于400的仓库中工作的职工
select a.zgh from zgb a join changk b on a.cgh=b.ckid where rea>400

 

--21.找出在北京工作的职工和他们的工资情况
select zgh,money from zgb a join changk b on a.cgh=b.ckid where ckcs='北京'

--22.找出工资大于1215的职工和他们所在的城市
select ckcs,zgh from zgb a join changk b on a.cgh=b.ckid where money>1215

--23.哪些城市至少有一个订单（从仓库角度考虑）
select distinct a.ckcs from changk a join zgb b
on a.ckid =b.cgh join ding c
on b.zgh=c.zhigong group by a.ckcs having COUNT(*)>=1

select distinct ckcs from ding join zgb on ding.zhigong=zgb.zgh join changk on zgb.cgh=changk.ckid

--24.找出没有任何订单的城市
select c.ckcs from ding a join zgb b
on a.zhigong=b.zgh full join changk c
on b.cgh=c.ckid group by c.ckcs having COUNT(*)=1

select ckcs from changk except select distinct ckcs from ding join zgb on ding.zhigong=zgb.zgh join changk on zgb.cgh=changk.ckid

--25.找出和E4有同样工资的所有职工
select *from zgb where money=(
select money from zgb where zgb.zgh='E4'
)

--26.找出仓库面积大于400的仓库的所有职工

select *from zgb where zgb.cgh=(
select changk.ckid from changk where rea>400
)

--27.找出供应商在西安的职工和他们的工资情况
select address,money from gysb a join ding b on a.gysh=b.gong join zgb c
on b.zhigong=c.zgh where address='西安' group by address,money

--28.找出不在北京仓库里工作的职工
select zgh,money from zgb a join changk b
on a.cgh=b.ckid
except select zgh,money from
zgb join changk c on zgb.cgh=c.ckid
where c.ckcs='北京'

--29.找出不在北京仓库里工作的职工
select zgh ,money from zgb join changk on zgb.cgh=changk.ckid where changk.ckcs='北京'

--30.求广州和上海仓库职工的总工资
select SUM(money) as '总工资' from zgb join changk on zgb.cgh=changk.ckid

--31.求所有职工工资都大于1210的仓库的平均面积
select AVG(rea) from changk join zgb on changk.ckid=zgb.cgh where money>1210

select AVG(rea)as'平均面积' from
(select cgh from zgb except select cgh from zgb where money<1210)
a join changk on a.cgh=changk.ckid

--32.求上海仓库中职工的最高工资
select MAX(money)as'最高工资' from zgb join changk on zgb.cgh=changk.ckid where ckcs='上海'

--33.订单数大于或等于2的职工的工资
select a.money from (select zgb .zgh,money from zgb join ding on zgb.zgh=ding.zhigong
group by zgb.zgh,money having COUNT(*)>=2)a