use lianxi0720go--创建部门表create table bumen(    bcode int primary key,--部门编号    bname varchar(20),    --部门名称    bceo varchar(20),     --部门负责人    btel varchar(20),     --部门电话)go--创建人员表create table renyuan(    code int primary key identity(10001,1),--人员编号,自增长用identity,从10001开始,每次加1    name varchar(20),                      --人员姓名    sex varchar(20),                       --人员性别    age int,                               --人员年龄    bc int                                 --部门编号)go--设置好外键关系之后来插入数据--先插入部门的数据insert into bumen values(1001,‘财务部‘,‘张三‘,1234567)insert into bumen values(1002,‘销售部‘,‘李四‘,2345678)insert into bumen values(1003,‘人事部‘,‘王五‘,3456789)insert into bumen values(1004,‘技术部‘,‘赵六‘,4567890)go--查看所有信息select * from bumen--插入人员表的信息insert into renyuan values(‘张三‘,‘男‘,33,1001)insert into renyuan values(‘李四‘,‘女‘,27,1002)insert into renyuan values(‘王五‘,‘男‘,25,1003)insert into renyuan values(‘赵六‘,‘女‘,24,1004)insert into renyuan values(‘王祖蓝‘,‘男‘,38,1001)insert into renyuan values(‘马蓉‘,‘女‘,33,1002)insert into renyuan values(‘王宝强‘,‘男‘,36,1003)insert into renyuan values(‘杨颖‘,‘女‘,28,1004)insert into renyuan values(‘郑凯‘,‘男‘,29,1001)insert into renyuan values(‘范冰冰‘,‘女‘,40,1002)insert into renyuan values(‘张伟‘,‘男‘,30,1003)insert into renyuan values(‘蔡依林‘,‘女‘,37,1004)go--查看人员表所有信息select * from renyuan --查询年龄最大的认得部门名称select MAX(age) from renyuanselect bc from renyuan where age=40select bname from bumen where bcode=1002--子查询,查询出的结果不是唯一值的时候把等号改成inselect bname from bumen where bcode=(select bc from renyuan where code=(select code from renyuan where age=(select MAX(age) from renyuan)))--按照升序把年龄排序后的前三个人的所有信息select top 3 * from renyuan order by age--按照升序把年龄排序后的第6,7,8个人的所有信息select top 3 * from renyuan where code not in(select top 5 code from renyuan order by age)order by age--分页查询,要求每页显示5条信息--第一页select top 5 * from renyuan--第二页select top 5 * from renyuan where code not in(select top 5 code from renyuan)--第三页select top 5 * from renyuan where code not in(select top 10 code from renyuan)--总共有几页:count是int类型,5也是int类型,这样结果就会自动显示int类型,所以要把5改成5.0得到的结果取上线.select CEILING(COUNT(*)/5.0) from renyuan--查找销售部里面年龄大于35岁的人的全部信息select * from renyuan where code in(select code from renyuan where age>35 and bc=(select bcode from bumen where bname=‘销售部‘))--查看所有人员信息，将部门编号替代为部门名称select code,name,sex,age,(select bname from bumen where bumen.bcode=renyuan.bc) as 部门 from renyuan--连接查询--将人员表中的信息全部查出,并且该部门编号为部门名称--将每个人的主管添加上select code,name,sex,age,(select bname from bumen where bumen.bcode=renyuan.bc) as 部门,(select bceo from bumen where bumen.bcode=renyuan.bc)from renyuan

create database lianxi0903gouse lianxi0903go--创建供应商表create table gong(    gcode int primary key,--供应商编号    gname varchar(20),    --供应商名称    gtel varchar(20)      --供应商电话)go--创建仓库表create table cang(    ccode int primary key,--货品编号    cname varchar(20),    --货品名称    cshu int,             --货品数量    cprice decimal(18,2), --货品价格    cgc int               --货品供应商编号)go--创建门店表create table men(    mcode int primary key,--货品编号    mname varchar(20),    --货品名称    mshu int,             --货品数量    mprice decimal(18,2)  --货品价格)go--先建立主外键关系再插入数据--首先插入供应商数据insert into gong values(1001,‘蒙牛‘,‘1234567‘)insert into gong values(1002,‘伊利‘,‘2345678‘)insert into gong values(1003,‘得益‘,‘3456789‘)go--再插入仓库数据insert into cang values(101,‘高钙纯牛奶‘,5,3,1001)insert into cang values(102,‘蒙牛酸牛奶‘,6,2.5,1001)insert into cang values(103,‘优酸乳‘,9,4,1001)insert into cang values(104,‘伊利纯牛奶‘,4,3.5,1002)insert into cang values(105,‘高钙酸牛奶‘,2,2.5,1002)insert into cang values(106,‘大果粒‘,7,4.5,1002)insert into cang values(107,‘得益纯牛奶‘,6,3,1003)insert into cang values(108,‘红枣酸奶‘,5,2,1003)insert into cang values(109,‘低脂酸奶‘,4,2.4,1003)go--再插入门店数据insert into men values(101,‘高钙纯牛奶‘,5,3)insert into men values(102,‘蒙牛酸牛奶‘,6,2.5)insert into men values(103,‘优酸乳‘,9,4)insert into men values(104,‘伊利纯牛奶‘,4,3.5)insert into men values(105,‘高钙酸牛奶‘,2,2.5)insert into men values(106,‘大果粒‘,7,4.5)insert into men values(107,‘得益纯牛奶‘,6,3)insert into men values(108,‘红枣酸奶‘,5,2)insert into men values(109,‘低脂酸奶‘,4,2.4)go--查看所有信息select * from men--查询蒙牛供应商提供的商品中价格最低的商品的所有信息select top 1 * from men where mname like(‘%蒙牛%‘) order by mpriceselect * from men where mprice in(select MIN(mprice) from men where mcode in(select ccode from cang where cgc in(select gcode from gong where gname=‘蒙牛‘)))and mname like(‘%蒙牛%‘)--将仓库表中的供应商编号替换为名称select ccode,cname,cshu,cprice,(select gname from gong where gong.gcode=cang.cgc) as 供应商名称 from cang--将商品的仓库编号,供应商编号添加上,并且供应商编号替换为名称select mcode,mname,mshu,mprice,(select ccode from cang where cang.ccode=men.mcode)as 仓库编号 from men