SQL Server 的高级查询

统计函数的概括

--Family数据库
--编号表名中文说明
--1 Tb_Users 家庭成员表
--2 Tb_InOutField 收入支出项目表
--3 tb_InOutInfo 日常收支表
--1、Tb_Users 家庭成员表
--字段名中文说明字段类型字段大小是否为空其它
--uid 成员编号 Int false 主键
--uname 姓名 Varchar 20 False
--upart 家庭角色 Varchar 20 False
--ubirth 出生日期 datetime False
--upassword 密码 Varchar 20 False 默认888888
--ucompany 工作单位 Varchar 30 True
--uremark 备注 Varchar 100 True
--2、 Tb_InOutField收支项目表
--字段名中文说明字段类型字段大小是否为空其它
--xid 编号 Int False 主键,标识列
--xName 项目名 Varchar 20 False
--xtype 收支类型 varchar 20 False
--3、tb_InOutInfo日常收支表
--字段名中文说明字段类型字段大小是否为空其它
--rid 编号 Int False 主键
--uid 操作人 int False 外键(成员表uid)
--xid 收支项目 int False 外键(项目表xid)
--rmoney 金额 float False 默认为0
--rdate 操作时间 DateTime False
--rmenu 备注 varchar 100 true

--2、查询所有家庭成员工

--3、查询收支项目表

--4、查询日常收支表

--模糊查询
--5、查询所有梁姓家庭成员（like）

--6、查询日常收支表中所有rmenu即备注含‘去’字的记录

--7、查询日常收支表中2月到3月之间的收支情况（between）

--8、查询日常收支表中1000到5000之间金额的收支记录

--9、查询日常收支表中工资和奖金记录，即xid为1，2的记录

--聚合函数
--10、求存款额，即对金额求和

--11、求总支出，即为金额为负数的求和

--12、求梁山伯今年发了几次工资，即为uid为1且xid为1的记录记数

--13、最大收入额，即求最大值

--分组
--14、求每个人的财务金额和，即根据uid分组后求rmoney的和

--15、求每个人的支出金额，即条件为rmoney为负数，根据uid分组求rmoney的和

--16、求每个人的收入金额，但只显示超过10000元，即条件为rmoney大于0, 根据uid分组求和，并有having筛选大于10000的

--17、求入支出项目个数，即在项目表中按收支类型分组，再计数

--联表查询
--18、在收支项目表和日常收支表中查询项目编号，项目名称，收支类型，收支金额

--19、在成员表和日常收支表中查询家庭角色，姓名，金额，操作日期

--20、在收支项目表和日期收支表，成员表中查询姓名，项目名称，收支金额

--家庭支出表
create table tb_inoutfield--收入与支出表
(
xid float,
xname nvarchar(255),
xtype nvarchar(255)
);

INSERT INTO tb_inoutfield (xid, xname, xtype) VALUES (1, N'工资', N'收入');
INSERT INTO tb_inoutfield (xid, xname, xtype) VALUES (2, N'奖金', N'收入');
INSERT INTO tb_inoutfield (xid, xname, xtype) VALUES (3, N'分红', N'收入');
INSERT INTO tb_inoutfield (xid, xname, xtype) VALUES (4, N'送礼', N'支出');
INSERT INTO tb_inoutfield (xid, xname, xtype) VALUES (5, N'坐车', N'支出');
INSERT INTO tb_inoutfield (xid, xname, xtype) VALUES (6, N'伙食', N'支出');
INSERT INTO tb_inoutfield (xid, xname, xtype) VALUES (7, N'电话费', N'支出');
INSERT INTO tb_inoutfield (xid, xname, xtype) VALUES (9, N'穿衣', N'支出');
INSERT INTO tb_inoutfield (xid, xname, xtype) VALUES (10, N'旅游', N'支出');
select * from tb_inoutfield;
--
create table tb_inoutInfo 收入表
(
rid float,
uid float,
xid float,
rmoney float,
rdate datetime,
rmenu nvarchar(255)
);

INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (1, 1, 1, 5000, N'2007-02-15 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (2, 1, 1, 5000, N'2007-03-15 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (3, 1, 1, 5000, N'2007-04-15 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (4, 1, 1, 5000, N'2007-05-15 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (5, 1, 2, 3000, N'2007-02-22 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (6, 1, 2, 2000, N'2007-03-22 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (7, 1, 2, 4200, N'2007-04-22 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (8, 2, 1, 3500, N'2007-02-15 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (9, 2, 1, 3500, N'2007-03-15 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (10, 2, 1, 3500, N'2007-04-15 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (11, 2, 2, 3000, N'2007-04-20 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (12, 4, 1, 8000, N'2007-03-15 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (13, 4, 1, 8000, N'2007-02-15 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (14, 5, 2, 10000, N'2007-05-01 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (15, 1, 7, -300, N'2007-03-01 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (16, 1, 5, -50, N'2007-03-15 00:00:00.000', N'充乘车卡');
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (17, 2, 4, -200, N'2007-01-01 00:00:00.000', N'刘德华结婚');
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (18, 2, 9, -100, N'2007-03-01 00:00:00.000', N'为儿子买衬衫');
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (19, 2, 6, -500, N'2007-02-01 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (20, 2, 6, -500, N'2007-03-01 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (21, 2, 6, -500, N'2007-04-01 00:00:00.000', null);
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (22, 5, 10, -2000, N'2007-05-01 00:00:00.000', N'去张家界玩');
INSERT INTO tb_inoutInfo (rid, uid, xid, rmoney, rdate, rmenu) VALUES (24, 2, 9, -500, N'2007-04-05 00:00:00.000', N'换季狂购');
select * from tb_inoutInfo ;
---
create table tb_users(--家庭表
uid float,
uname nvarchar(255),
upart nvarchar(255),
ubirth datetime,
upassword nvarchar(255),
ucompany nvarchar(255),
uremart nvarchar(255)
);

--模糊查询
--5、查询所有梁姓家庭成员（like）
select *from tb_users where uname like '%梁%';
--6、查询日常收支表中所有rmenu即备注含‘去’字的记录
select *from tb_inoutInfo where umenu like '%去%'
--7、查询日常收支表中2月到3月之间的收支情况（between）
select *from tb_inoutInfo where MONTH(umoney) between 2 and 3;
--8、查询日常收支表中1000到5000之间金额的收支记录
select *from tb_inoutInfo where rmoney between 1000 and 5000;
--9、查询日常收支表中工资和奖金记录，即xid为1，2的记录
select xid,rdate from tb_inoutInfo where xid in (1,2);

--聚合函数
--10、求存款额，即对金额求和
select SUM(rmoney)as'总今额' from tb_inoutInfo;

--11、求总支出，即为金额为负数的求和
select SUM(rmoney)from tb_inoutInfo where rmoney<0;
--12、求梁山伯今年发了几次工资，即为uid为1且xid为1的记录记数
select COUNT(*)from tb_inoutInfo where uid=1 and xid=1;
--13、最大收入额，即求最大值
select MAX(rmoney) from tb_inoutInfo;

--分组
--14、求每个人的财务金额和，即根据uid分组后求rmoney的和
select uid,SUM(rmoney) from tb_inoutInfo group by uid;
--15、求每个人的支出金额，即条件为rmoney为负数，根据uid分组求rmoney的和
select uid ,SUM(rmoney) from tb_inoutInfo where rmoney<0 group by uid;
--16、求每个人的收入金额，但只显示超过10000元，即条件为rmoney大于0, 根据uid分组求和，并有having筛选大于10000的
select uid,SUM(rmoney) from tb_inoutInfo where rmoney>0 group by uid having SUM(rmoney)>10000;
--17、求入支出项目个数，即在项目表中按收支类型分组，再计数
select COUNT(*)from tb_inoutfield group by xtype

--联表查询
--18、在收支项目表和日常收支表中查询项目编号，项目名称，收支类型，收支金额
select a.xid,a.xname,a.xtype,b.rmoney from tb_inoutfield a left join tb_inoutInfo b on a.xid=b.xid
--19、在成员表和日常收支表中查询家庭角色，姓名，金额，操作日期
select a.upart,a.uname,b.rmoney,b.rdate from tb_users a left join tb_inoutInfo b on a.uid=b.uid
--20、在收支项目表和日常收支表，成员表中查询姓名，项目名称，收支金额
select uname,xname,xtype,rmoney,rdate from tb_users a join tb_inoutinfo b on a.uid=b.uid join tb_inoutfield c on b.xid=c.xid
--T-SQL练习题
--查询
--下面的查询基于下图的3个表进行。
--Student：【学号姓名性别年龄系别】
--Course：课程表【课程号课程名学分】
--SC：成绩表【学号课程号分数】
--1.查询全体学生的姓名、学号、所在系。
--2.查询年龄不在20~23岁之间的学生姓名、系别和年龄。
--Between 1 and 3
--3.查询所有姓刘学生的姓名、学号和性别。
--4.求各个课程号及相应的选课人数。
--5.查询每个学生的学号、姓名、选修的课程名及成绩
--6.将所有学生的年龄增加1岁
--7.检索不学课程号为“C01”课程的学生信息
--8.检索至少学过课程号为“C01”和“C02”的学生信息；
--9.从学生表中删除成绩出现过0分的所有学生信息
--10.找出没有选修过“数据库基础”课程的所有学生姓名
--11.检索所有比“宋平”年龄大的学生的姓名、年龄和性别，
--12.将选课在3门以上（包括3门）的学生的学号，姓名，平均分和选课门数按平均分降序排序，并将结果存于数据库表stu_temp（字段名为学号，姓名，平均分和选课门数）
--13.将“高等数学”课程不及格的成绩修改为0分
--Student：【学号姓名性别年龄系别】
create table Student(
sid int not null primary key,--学号
sname varchar(20) not null,--姓名
ssex varchar(2) not null check(ssex='男' or ssex='女'),--性别
sage int not null default 18 check(sage>0 and sage<150),--年龄
sDepartment varchar(50) not null ,--系别
);
--Course：课程表【课程号课程名学分】
create table Course(
bid int not null primary key,--课程号
lesson varchar(20) not null,--课程名
Credits int,--学分
);
--SC：成绩表【学号课程号分数】
create table sc(
Student int not null primary key, --学号
aid int not null,--课程号
fraction int not null,--分数
);
--1.查询全体学生的姓名、学号、所在系
select * from Student;
--2.查询年龄不在20~23岁之间的学生姓名、系别和年龄。
select sage,sname,sDepartment from Student where sage between 20 and 23;
--3.查询所有姓刘学生的姓名、学号和性别。
select sname ,sid,ssex from Student where sname like '%刘%'
--4.求各个课程号及相应的选课人数。
select bid from Course bid

-- Family 数据库
--编号表名中文说明
--1 Tb_Users 家庭成员表
--2 Tb_InOutField 收入支出项目表
--3 Tb_InOutInfo 日常收支表
--1、Tb_Users 家庭成员表
--字段名中文说明字段类型字段大小是否为空其它
--uid 成员编号 Int false 主键
--uname 姓名 Varchar 20 False
--upart 家庭角色 Varchar 20 False
--ubirth 出生日期 datetime False
--upassword 密码 Varchar 20 False 默认 888888
--ucompany 工作单位 Varchar 30 True
--uremark 备注 Varchar 100 True
--3、Tb_InOutInfo 日常收支表
--字段名中文说明字段类型字段大小是否为空其它
--rid 编号 Int False 主键
--uid 操作人 int False 外键(成员表 uid)
--xid 收支项目 int False 外键(项目表 xid)
--rmoney 金额 float False 默认为 0
--rdate 操作时间 DateTime False
--rmenu 备注 varchar 100 true
--2、查询所有家庭成员工
--3、查询收支项目表
--4、查询日常收支表
--模糊查询
--5、查询所有梁姓家庭成员（like）
--6、查询日常收支表中所有 rmenu 即备注含‘去’字的记录
--7、查询日常收支表中 2 月到 3 月之间的收支情况（between）
--8、查询日常收支表中 1000 到 5000 之间金额的收支记录
--9、查询日常收支表中工资和奖金记录，即 xid 为 1，2 的记录
--2、 Tb_InOutField 收支项目表
--字段名中文说明字段类型字段大小是否为空其它
--xid 编号 Int False 主键,标识列
--xName 项目名 Varchar 20 False
--xtype 收支类型 varchar 20 False
--
create table tb_inoutfield
(
xid float,
xname nvarchar(255),
xtype nvarchar(255)
);

--
create table tb_inoutInfo
(
rid float,
uid float,
xid float,
rmoney float,
rdate datetime,
rmenu nvarchar(255)
);

---
create table tb_users(
uid float,
uname nvarchar(255),
upart nvarchar(255),
ubirth datetime,
upassword nvarchar(255),
ucompany nvarchar(255),
uremart nvarchar(255)
);

INSERT INTO tb_users (uid, uname, upart, ubirth, upassword, ucompany, uremart) VALUES (1, N'梁山伯', N'户主', N'1963-03-01 00:00:00.000', N'zyt', null, N'很爱祝英台');
INSERT INTO tb_users (uid, uname, upart, ubirth, upassword, ucompany, uremart) VALUES (2, N'祝英台', N'老婆', N'1968-09-01 00:00:00.000', N'lsb', null, null);
INSERT INTO tb_users (uid, uname, upart, ubirth, upassword, ucompany, uremart) VALUES (3, N'梁小山', N'儿子', N'1990-01-01 00:00:00.000', N'aaa', null, null);
INSERT INTO tb_users (uid, uname, upart, ubirth, upassword, ucompany, uremart) VALUES (4, N'梁小伯', N'女儿', N'1982-12-01 00:00:00.000', N'bbb', null, null);
INSERT INTO tb_users (uid, uname, upart, ubirth, upassword, ucompany, uremart) VALUES (5, N'梁爸爸', N'爸爸', N'1930-02-01 00:00:00.000', N'bbb', null, null);
--2、查询所有家庭成员工
select * from tb_users;
--3、查询收支项目表
select * from tb_inoutfield;
--4、查询日常收支表
select * from tb_inoutInfo;
--5、查询所有梁姓家庭成员（like）
select uname from tb_users where uname like '%梁%'
--6、查询日常收支表中所有 rmenu 即备注含‘去’字的记录
select rmenu from tb_inoutInfo where rmenu like '%去%'
--7、查询日常收支表中 2 月到 3 月之间的收支情况（between）
select *from tb_inoutInfo where rdate between '2007-02-01' and '2007-03-01'
--8、查询日常收支表中 1000 到 5000 之间金额的收支记录
select *from tb_inoutInfo where rmoney between 1000 and 5000
--9、查询日常收支表中工资和奖金记录，即 xid 为 1，2 的记录
select *from tb_inoutInfo where xid in
( select xid from tb_inoutfield where xname='工资' or xname='奖金')
--客房管理系统
--一、数据库表介绍：
--编号表名中文说明
--1 tb_type 客房类型表
--2 tb_kefang 客房表
--3 tb_dengji 登记表
--4 tb_users 用户表

--二、数据表结构：
--1、tb_users 用户表
--字段名中文说明字段类型字段大小是否为
--空
--其它
--uid 用户编号 int false 主键,标识列
--uname 用户名 varchar 20 False
--upwd 密码 varchar 16 False 默认值为 888888
--usex 性别 varchar 2 False 取值“男”或“女”
--uage 年龄 int False 取值大于 0 小于 150
--usf 身份 varchar 20 False

--2、tb_type 客户类型表
--字段名中文说明字段类型字段大小是否为
--空
--其它
--tid 类型编号 int false 主键，标识列
--tname 类型名 varchar 20 False
--tmenu 备注 varchar 30 true

---3、tb_kefang 客房表
--字段名中文说明字段类型字段大小是否为
--空
--其它
--fid 客房编号 int false 主键(由楼层和房间号组
--成)
--tid 类型编号 int false 外键（客房类型 tid）
--fprice 房价 float false
--fkzrs 可住人数 int false
--fyzrs 已住人数 int flase 默认值为 0
--fcnt 入住统计 int false 默认值为 0
--fmenu 备注 varchar 100 false

--4、tb_dengji 登记表
--字段名中文说明字段类型字段大小是否为
--空
--其它
--did 编号 int false 主键
--dname 客户名 varchar 20 false
--dfid 房间编号 int false 外键(客房表 fid)
--dsfz 身份证号 varchar 18 false
--dyj 押金 float false
--drz 入住时间 datetime false 默认为当前日期
-- dtf 退房时间 datetime true

--上机作业一
--2、查询所有用户
--3、查询客房类型表的所有记录
--4、查询所有客房的信息
--5、查看登记表
--6、查看用户表中所有用户的用户名及密码
--7、查看客房的状态（即只查询客房表的客房编号及状态）
--8、查房：在登记表中查所有住在 101 的客户
--9、在客房表中查找所有的空房间
--10、查询所有入住统计大于１００次的房间编号
--11、查询喜来乐在该酒店住了多少天
--12、查询所有住满的房间，但要求字段名用中文别名
--13、查询所有 2007 年 5 月份入住的客户
--14、客房表按入住次数排降序
--15、客户表按入住次数排降序，但只显示前三甲
--16、登记表按入住时间排升序
--17、登记表中按房间号升序和入住时间降序
--18、查询刘德华的登录密码
--19、查询所有有电视的房间（提示:用 like）
--上机作业二
--1、查询所有姓张的客户（提示：left()）
--2、查询所有已经退房的记录(提示: is not null)
--3、查询所有客户的名，不含姓（substring()）
--4、将所有客户的名字变成类似“张某某”再显示，即只显示姓，名用某某代替
--5、查询登记表中，4 月入住的客户(month( ))
--6、查询所有入住时间小于 2007 的登记记录
--7、查询客户表中所有一楼客房的入住情况（convert, left）
--8、查询押金在 200 到 500 之间的客户
--9、查询豪华间的入住情况
--10、查询所有维修的房间
--11、按房价排序
--12、查询所有还可以住一个人的客房
--13、查询还可以住人的三人间
--14、查询所有可以住人的房间，即未满房间和空房间
create table tb_dengji
(
did float,
kname nvarchar(255),
fid float,
sfz nvarchar(255),
yajin float,
ruzhu datetime,
tufang datetime
);

INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (1, N'小猫', 101, N'123', 200, N'2007-05-12 07:22:18.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (2, N'阿狗', 101, N'123', 200, N'2007-05-14 10:22:39.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (3, N'阿牛', 101, N'123', 200, N'2007-05-15 14:23:02.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (4, N'王昭君', 102, N'123', 200, N'2007-05-15 10:23:31.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (5, N'西施', 102, N'123', 200, N'2007-05-15 03:23:46.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (6, N'昭婵', 102, N'123', 200, N'2007-05-13 00:23:57.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (8, N'鲁迅', 103, N'123', 300, N'2007-05-15 00:24:31.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (9, N'刘德华', 201, N'111', 500, N'2007-05-15 00:25:10.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (10, N'张学友', 201, N'111', 500, N'2007-05-12 10:25:22.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (11, N'成龙', 205, N'222', 800, N'2007-05-15 00:25:57.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (12, N'李世民', 301, N'333', 1000, N'2007-05-11 02:26:35.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (13, N'朱元璋', 305, N'444', 1000, N'2007-05-15 00:27:09.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (14, N'张曼玉', 401, N'55', 100, N'2007-05-15 00:27:24.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (15, N'张柏芝', 402, N'777', 1000, N'2007-05-14 11:10:12.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (16, N'小猫', 102, N'123', 200, N'2007-03-12 07:22:00.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (17, N'小羊', 201, N'222', 100, N'2007-03-14 00:00:00.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (18, N'喜来乐', 201, N'222', 100, N'2007-04-01 00:31:04.000', null);
INSERT INTO tb_dengji (did, kname, fid, sfz, yajin, ruzhu, tufang) VALUES (19, N'小李子', 404, N'111', 500, N'2007-05-15 00:00:00.000', null);
create table tb_users
(
uid float,
uname nvarchar(max),
upwd nvarchar(max),
usf nvarchar(max)
);

INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (1, N'黎明', N'111', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (2, N'刘德华', N'222', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (3, N'张学友', N'333', N'经理');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (10, N'张三', N'111', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (6, N'李四', N'111', N'经理');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (20, N'yishanjing', N'123', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (15, N'要', N'aa', N'老板');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (16, N'小李子', N'111', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (20, N'yishanjing', N'123', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (10, N'name', N'pwd', N'sf');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (100, N'aa', N'111111', N'老板');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
INSERT INTO tb_users (uid, uname, upwd, usf) VALUES (200, N'众', N'aa', N'前台');
create table tb_type
(
tid float,
tname nvarchar(max),
tmenu nvarchar(max)
);

INSERT INTO tb_type (tid, tname, tmenu) VALUES (1, N'总统套房', N'最贵的');
INSERT INTO test.dbo.tb_type (tid, tname, tmenu) VALUES (2, N'豪华间', null);
INSERT INTO test.dbo.tb_type (tid, tname, tmenu) VALUES (3, N'单间', null);
INSERT INTO test.dbo.tb_type (tid, tname, tmenu) VALUES (4, N'双人间', null);
INSERT INTO test.dbo.tb_type (tid, tname, tmenu) VALUES (5, N'三人间', null);
create table tb_kefang
(
fid float,
tid float,
fprice float,
fkzrs float,
fyzrs float,
fzhutai nvarchar(max),
fcnt float,
fmenu nvarchar(max)
);

INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (101, 5, 50, 3, 3, N'住满', 100, N'电视,热水器');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (102, 5, 50, 3, 3, N'住满', 50, N'电视,热水器');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (103, 5, 60, 3, 2, N'未住满', 80, N'电视,热水器,风扇');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (104, 5, 60, 0, 0, N'维修', 30, N'电视,热水器,风扇');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (105, 5, 60, 3, 0, N'空房', 100, N'电视,热水器,风扇');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (201, 4, 80, 2, 2, N'住满', 100, N'电视,热水器');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (202, 4, 80, 2, 0, N'空房', 200, N'电视,热水器');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (203, 4, 100, 2, 0, N'空房', 201, N'电视,热水器,空调');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (204, 4, 100, 2, 0, N'空房', 20, N'电视,热水器,空调');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (205, 4, 120, 2, 1, N'未住满', 100, N'电视,热水器,电话,空调');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (301, 3, 150, 1, 1, N'住满', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (302, 3, 150, 1, 0, N'空房', 500, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (303, 3, 150, 1, 0, N'空房', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (304, 3, 150, 0, 0, N'维修', 50, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (305, 3, 150, 1, 1, N'住满', 50, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (401, 2, 200, 1, 1, N'住满', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (402, 2, 200, 1, 1, N'住满', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (403, 2, 180, 1, 0, N'空房', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (404, 2, 200, 1, 1, N'住满', 500, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (405, 2, 200, 0, 0, N'维修', 100, N'无');
INSERT INTO tb_kefang (fid, tid, fprice, fkzrs, fyzrs, fzhutai, fcnt, fmenu) VALUES (501, 5, 1000, 1, 0, N'空房', 500, N'无');
--2、查询所有用户
select *from tb_users;
--3、查询客房类型表的所有记录
select *from tb_kefang;
--4、查询所有客房的信息
select *from tb_dengji;
--5、查看登记表
select *from tb_type;
--6、查看用户表中所有用户的用户名及密码
select uname ,upwd from tb_users;
--7、查看客房的状态（即只查询客房表的客房编号及状态）
select fid,fmenu from tb_kefang;
--8、查房：在登记表中查所有住在 101 的客户
select *from tb_dengji where fid=101;
--9、在客房表中查找所有的空房间
select *from tb_kefang where fzhutai ='空房';
--10、查询所有入住统计大于１００次的房间编号
select *from tb_kefang where fcnt>100;
--11、查询喜来乐在该酒店住了多少天
select kname ,DATEDIFF(DD,ruzhu,GETDATE())from tb_dengji where kname='喜来乐';

--12、查询所有住满的房间，但要求字段名用中文别名
select *from tb_kefang where fzhutai ='住满';
--13、查询所有 2007 年 5 月份入住的客户
select *from tb_dengji where MONTH(ruzhu) between 5 and 6;
--14、客房表按入住次数排降序
select *from tb_kefang order by fcnt desc;
--15、客户表按入住次数排降序，但只显示前三甲
select top 3*from tb_kefang order by fcnt desc;
--16、登记表按入住时间排升序
select *from tb_dengji order by ruzhu ;
--17、登记表中按房间号升序和入住时间降序
select *from tb_dengji order by did , ruzhu desc;
--18、查询刘德华的登录密码
select uname,upwd from tb_users where uname='刘德华';
--19、查询所有有电视的房间（提示:用 like）
select fid from tb_kefang where fmenu like '%电视%';
--上机作业二
--1、查询所有姓张的客户（提示：left()）
select *from tb_users where left(uname,1)='张';
--2、查询所有已经退房的记录(提示: is not null)
select *from tb_dengji where tufang is not null;
--3、查询所有客户的名，不含姓（substring()）
--select *from tb_users where SUBSTRING (uname,1,2);
--4、将所有客户的名字变成类似“张某某”再显示，即只显示姓，名用某某代替
update tb_users
set uname=replace(uname,'张学友','张某某');
select *from tb_users

--5、查询登记表中，4 月入住的客户(month( ))
select *from tb_dengji where MONTH(ruzhu)=4;
--6、查询所有入住时间小于 2007 的登记记录
select *from tb_dengji where YEAR(ruzhu)<2007;
--7、查询客户表中所有一楼客房的入住情况（convert, left）
select *from tb_kefang where LEFT(fid,1)=1
--8、查询押金在 200 到 500 之间的客户
select kname from tb_dengji where yajin between 200 and 500;
--9、查询豪华间的入住情况
select fzhutai from tb_kefang where fmenu='电视'or fmenu='热水器';
--10、查询所有维修的房间
select *from tb_kefang where fzhutai='维修';
--11、按房价排序
select *from tb_kefang order by fprice;
--12、查询所有还可以住一个人的客房
select *from tb_kefang where fkzrs=1 and fzhutai='空房'or fzhutai='未住满';
--13、查询还可以住人的三人间
select*from tb_kefang where fkzrs=3 and fzhutai='空房'or fzhutai='未住满';
--14、查询所有可以住人的房间，即未满房间和空房间
select *from tb_kefang where fzhutai='空房'or fzhutai='未住满';

posted @ 2022-05-24 19:36 逞强阅读(357) 评论(0) 收藏举报

刷新页面返回顶部

3034607727zoujiafu

SQL Server 的高级查询

公告