--增加
--语法：insert into 表名 values(列值)

insert into  tb_stu(sname,ssex) values('绿茶妹妹','女')
insert into  tb_stu values('张三','男',38,'男人村','喜欢姐妹村的菇凉')
--批量插入
insert into  tb_stu
select '李四','男',28,'男人村','喜欢姐妹村的菇凉' union
select '李四2','男',48,'男人村','喜欢姐妹村的菇凉' union
select '李四3','男',58,'男人村','喜欢姐妹村的菇凉' 
--查询
select * from  tb_stu
--查询性别为女的学生信息
select * from  tb_stu where ssex='女'
--查询学号、姓名、年龄 
select sid,sname,sage from tb_stu
----查询学号、姓名、年龄  取别名
select sid 学号,sname as 姓名,年龄=sage from tb_stu
--查询住在姐妹村且姓郑的学生信息
select * from tb_stu where sname like '郑%' and saddress='姐妹村'
--查询姓名中第二个字为姐的学生信息
select  * from  tb_stu  where sname like '_姐%'
--查询地址中包含妹字的学生信息
select  * from  tb_stu where saddress  like '%妹%'
--年龄在18岁到38岁之间的学生信息
select  * from  tb_stu where sage>=18 and sage<=38
select * from tb_stu where sage between 18 and 38
--查询学号为 1,3,5,7,8的学生信息
select * from  tb_stu where sid=1 or sid=3 or sid=5 or sid=7 or sid=8
select  * from tb_stu where sid not in(1,3,5,7,8)
--根据年龄进行降序排列  order by asc  desc
select * from tb_stu order by sage desc
--查询学号排在前三位的学生信息
select top 3 * from tb_stu order by sid 
--查询学号排在后三位的学生信息
select top 3 * from tb_stu order by sid  desc
--查询表中一半的数据
select top 50 percent * from tb_stu
--修改
--update 表名 set 列名=修改后的值 where 条件
--将年龄18岁的住在姐妹村的姓名修改为"小姐妹"
update tb_stu set sname ='小姐妹' where sage =18 and saddress='姐妹村'
--将王小妹的年龄修改为 38  地址修改为“姐妹村”
update tb_stu set sage = 38 , saddress ='姐妹村' where sname='王小妹'
--将所有人的年龄增加2岁
update tb_stu set sage = 12 where sage is null
update tb_stu set sage-=2  where sage =150
update tb_stu set sage+=2
select * from  tb_stu

--删除
--语法： delete  from 表名 where 条件
--删除学号为3的学生信息
delete from tb_stu where sid =3
--删除住再姐妹村的女生
delete tb_stu where saddress='姐妹村' and ssex='女'
--删除成绩表中的所有信息
delete tb_score
--删除学生表中的所有数据
delete tb_stu
--删除学生表结构
drop table tb_stu
drop table tb_score
--删除数据库
drop database db_t267

--往用户表中增加一条信息(赵麻子,123456,男,22,超级管理员)
insert into tb_users values('赵麻子',123456,'男',22,'超级管理员')

--批量增加一批房间信息
--(1001,2,199,2,2,45,已住满
--1002,3,399,2,0,45,空置
--1003,2,199,2,1,45,未住满
--1004,4,599,4,0,45,空置)
insert into tb_kefang
select 1001,2,199,2,2,45,'已注满'union
select 1002,3,399,2,0,45,'空置' union
select 1003,2,199,2,1,45,'未住满' union
select 1004,4,599,4,0,45,'空置' 

--查询用户名、密码
select uname ,upwd from tb_users
--查询刘德华的密码
select uname from tb_users where upwd='刘德华'
--查询年龄大于28岁的用户
select * from tb_users where uage<=28
--查询年龄在38到50岁之间的用户 姓名
select  * from  tb_users where uage>=38 and uage<=50
--根据年龄降序排列
select * from tb_users order by uage desc
--查询姓张的用户
select * from tb_users where uname like '张%'
--查询性别为女的用户
select * from tb_users where usex='女'
--查询姓名中第二字为‘来’的用户
select  * from  tb_users  where uname like '_来%'
--查询已住满的房间信息
select * from tb_kefang where fkzrs=fkzrs
--查询空置的房间信息
select * from tb_kefang where fkzrs>=0 and fyzrs=0
--查询未住满的房间信息
select * from tb_kefang where fkzrs>fyzrs
--查询房间价格在100到300之间的房间信息
select * from tb_kefang where fprice>=100 and fprice<=300
--查询住在8301的用户名
select dname from tb_dengji where dfid=8301
--客房表按照入住的次数降序排列
select * from tb_kefang order by fcnt desc
--查询入住次数排在前三位的房间信息
select top 3 * from tb_users order by uid 
--登记表按照入住时间升序
select * from tb_dengji order by drz
--查询有wifi的房间信息
select * from tb_kefang where fmenu like 'wifi%'
--查询没有电视机的房间信息
select * from tb_kefang where fmenu not like '电视机%'

--修改刘德华的密码为123456
update tb_users set upwd=123456 where uname='刘德华'
--将张三的性别修改为男，并且把年龄修改为88
update tb_users set usex='男', uage=88 where uname='张三'
--删除用户编号为3的用户信息
delete from tb_users where uid =3
--删除用户表中的所有数据
delete tb_users
--删除用户的表结构
drop table tb_users

一、数据库表介绍：

编号	表名	中文说明
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	类型编号	varchar	20	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、查询所有用户
select * from tb_users
--3、查询客房类型表的所有记录
select * from tb_type

--4、查询所有客房的信息
select * from tb_kefang

--5、查看登记表
select * from tb_dengji

--6、查看用户表中所有用户的用户名及密码
select uname as 用户名,upwd as 密码 from tb_users;
--7、查看客房的状态（即只查询客房表的客房编号及状态）
select fid as 客房编号,fyzrs as 已住人数 from tb_kefang;

--8、查房：在登记表中查所有住在101的客户
select * from tb_kefang  where fid=101

--9、在客房表中查找所有的空房间
select * from tb_kefang where fyzrs = 0;

--10、查询所有入住统计大于１００次的房间编号
select * from tb_kefang where fcnt > 100

--11、查询喜来乐在该酒店住了多少天

--12、查询所有住满的房间，但要求字段名用中文别名
select * from tb_type
select * from tb_kefang where tid=100 and fkzrs-fyzrs=0

--13、查询所有2018年5月份入住的客户
select * from tb_dengji where MONTH(drz)=5

--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 drz asc;

--17、登记表中按房间号升序和入住时间降序
select * from tb_dengji order by dfid asc,drz 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 dtf is not null 

--3、查询所有客户的名，不含姓 （substring()）
select SUBSTRING(uname,2,LEN(uname)) from tb_users

--4、将所有客户的名字变成类似“张某某”再显示，即只显示姓，名用某某代替
select uid,REPLACE(uname,SUBSTRING(uname,2,LEN(uname)),'某某')as uname,upwd,usex,uage,usf from tb_users;

--5、查询登记表中，4月入住的客户(month( ))
select * from tb_dengji where MONTH(drz)=4

--6、查询所有入住时间小于2007的登记记录
select * from tb_dengji where YEAR(drz)<=2018

--7、查询客户表中所有一楼客房的入住情况（convert, left）
select * from tb_kefang where LEFT(fid,1)=1

--8、查询押金在200到500之间的客户
select * from tb_dengji where dyj between 200 and 500

--9、查询豪华间的入住情况
select * from tb_type
select * from tb_kefang where tid=103

--10、查询所有维修的房间
select * from tb_kefang where fmenu like '%维修%'

--11、按房价排序
select * from tb_kefang order by fprice 

--12、查询所有还可以住一个人的客房
select * from tb_kefang  where fkzrs-fyzrs = 1;

--13、查询还可以住人的三人间
select * from tb_type
select * from tb_kefang where tid=102 and fkzrs-fyzrs>0

--14、查询所有可以住人的房间，即未满房间和空房间
select * from tb_kefang where  fkzrs-fyzrs>0

--作业三：
--1、用脚本创建学生信息表，包含列名：              
 --StudentNo(学号),
        --       LoginPwd（密码）,
       --        StudentName（学生姓名）,
       --        Sex（学生性别）,
       --        GradeId（年级）,
       --        Phone（电话号码）,
      --         Address（地址）,
      --        BornDate（生日）,
     --          Email（邮箱）
create table tb_stu(
    StudentNo int primary key, 
    LoginPwd int not null,
    StudentName varchar(20) not null,
    Ssex varchar(4) not null, 
    GradeId varchar(20) not null,
    Phone int not null,
    SAddress varchar(max) not null,
    BornDate date not null,
    Email varchar(200)
)

--2、编写脚本在学生信息表中插入5条模拟数据
insert into tb_stu
select 1,123,'雷佳鑫','男','三年级',686886,'浪琴湾小区燕联',20021010,'www.qq.com@520'
select 2,345,'雷欣宇','女','三年级',886668,'浪琴湾小区燕联',20020118,'www.qq.com@521'
select 3,567,'张三','男','一年级',525722,'浪琴湾小区清水',19980510,'www.qq.com@345'
select 4,789,'李四','女','二年级',812532,'浪琴湾小区清水',20001124,'www.qq.com@627'
select 5,890,'王五','男','三年级',278633,'浪琴湾小区燕联',19990421,'www.qq.com@265'

--3、创建学生通讯录，含有字段：
--SName,Phone,Address,Email，并插入数据
create table tb_tel(
      sname varchar(10),
      phone varchar(10),
      saddress varchar(20),
      email varchar(20)
     
      ) 

insert into tb_tel 
select '雷佳鑫','520','浪琴湾','www.qq.com@666666'  union
select '雷欣宇','521','浪琴湾','www.qq.com@888888'  union
select '雷凯超','666','浪琴湾','www.qq.com@666688'

--4、编写科目表，含有字段：cno（编号），SubjectName（科目名）,ClassHour（学时）,GradeId（年级）
create table tb_subject(
      cno int,
      SubjectName varchar(10),
      ClassHour varchar(10),
      GradeId int
     
      ) 
insert into tb_subject
select  1,'lxy','数学',18  union
select  2,'ljx','英语',11  union
select  3,'lkc','语文',11
--5、建立成绩表，含有字段：StudentNo,SubjectId（科目编号）,StudentResult（学生成绩）,
--ExamDate（建表日期）

--并同时插入多条数据
create table tb_score(
 StudentNo int,
      SubjectId int,
      StudentResult int,
      ExamDate date
     
      ) 

insert into tb_score 
select  '数学',88,20201222 union
select  '英语',54,20210326  union
select  '语文',90,20220110
--6、将学生信息表中叫张扬的学生的性别改为男
update tb_stu set sex='男' where StudentName='张扬'
--7、在学生信息表中建立约束，性别只能为男或者女
 

产品表（Production）
产品编号	产品名称	生产日期
P#	Pname	Pdate
1	产品A	2020/01/02
2	产品B	2019/04/02
3	产品C	2020/03/02

销售人员表（Sales）
销售人员编号	销售人员姓名
S#	Sname
1	赵一
2	王二
3	张三
4	李四

销售量表（Deal）
销售人员编号	产品编号	销售量
S#	P#	Volume
1	1	20
1	2	36
1	3	55
2	2	24
2	3	56
2	1	44
3	2	11
3	3	77
4	1	36
4	2	91
4	3	22

--问题：
--1、查询张三19年的销售量(使用left join 关键字)。
select * from tb_sales
left join tb_deal on tb_sales.s#=tb_deal.s#
left join tb_production on tb_deal.p#=tb_production.p#
where Sname='张三' and Pdate like '2019%';
--2、查询生产日期在一季度产品的销量。
select
t2.p#,t2.pname, SUM(volume) 总销量,t2.pdate
from tb_deal t1
left join tb_production t2 on t1.p#=t2.p#
where t2.Pdate like '2020%'
GROUP BY t2.p#,t2.pname,t2.pdate;
--3、查询销售量最高的产品的销量（使用group by 关键字）。
select a.p#,a.pname,max(a.sum1)
from(
select
t1.p#,t1.pname,sum(volume) as sum1
from tb_production t1
left join tb_deal t2 on t1.p#=t2.p#
group by t1.p#
) a;