网咖管理系统sql记录

use wangka
drop table userProfile

create table userProfile (
 idcard char(20) not null primary key,
 name nvarchar(20) not null,
 level nvarchar(10) default N'临时'
)

insert into userProfile (idcard,name)
values('211234561222',N'小昊')，

insert into userProfile
values('211234561222',N'',N'会员'),('211234561222',N'',N'会员')


SELECT * FROM userProfile

SELECT * FROM wangguan
WHERE  id = 3

drop table computer

create table computer (
  id int identity(1,1) not null primary key,
  name nvarchar(20) default N'火星人'
)

insert into computer(name) values(N'粑粑人')

select id as '电脑编号',name as '电脑名' from computer 

select * from computer

-- 统计每种机器有多少台
select count(name),name from computer 
group by name
-- 统计粑粑人电脑有多少台
select count(id) from computer 
WHERE  name = N'粑粑人'

select * from [work] w 

-- computer userProfile wangguan work

-- 网管工作表
select w.date as '日期', as '网管id', as '网管名' from work w , wangguan wg
where w.wangguanId = 
-- 默认生序 asc
order by w.date asc

create table play (
  idcard  char(20) foreign key references userProfile(idcard),
  date datetime not null default GETDATE(),
  startTime int not null default DATEPART(hour ,GETDATE()),
  endTime int,
)

alter table play
alter computerId int not null foreign key references computer(id)

select * from computer

update play 
set computerId = 2
where idcard != '211234561222'

select * from play

insert into play (idcard)
values('211234561222')

insert into play (idcard,startTime)
values('211234561222',5)

select *, DATEPART(year,date), DATEPART(month,date), DATEPART(day,date) from play

-- *更新 用户211234567下机 时间为当前的时
update play 
set endTime = DATEPART(hour ,GETDATE())
where idcard = '211234567'
and DATEPART(year,date) = DATEPART(year ,GETDATE()) 
and DATEPART(month,date) = DATEPART(month ,GETDATE())
and DATEPART(day,date) = DATEPART(day ,GETDATE())
and endTime is NULL
-- 指定下机小时
update play 
set endTime = 22
where idcard = '211234561222'
and DATEPART(year,date) = DATEPART(year ,GETDATE()) 
and DATEPART(month,date) = DATEPART(month ,GETDATE())
and DATEPART(day,date) = DATEPART(day ,GETDATE())
and endTime is NULL
-- 子查询 设置小昊还未下机的记录的下机时间
update play 
set endTime = 24
where idcard = (select idcard from userProfile where name = N'小昊')
and DATEPART(year,date) = DATEPART(year ,GETDATE()) 
and DATEPART(month,date) = DATEPART(month ,GETDATE())
and DATEPART(day,date) = DATEPART(day ,GETDATE())
and endTime is NULL
 

insert into play (idcard)
values((select idcard from userProfile where name = N'小红'))

select * from userProfile

select * from wangguan

drop table work

create table work (
wangguanId int foreign key references wangguan(id),
date datetime not null default GETDATE(),
)

insert into work (wangguanId)
values(2)

select *,  DATEPART(day ,date) from work

--查询上机的信息和用户名和上机电脑名 和当天网管名
select 
play.date as '日期',
play.startTime as '上机时间/小时',
play.endTime as '下机时间/小时',
 as '玩家名',
up.idcard as '玩家身份证号',
 as '电脑名',
 as '当天网管'
from play , userProfile up ,computer c, work w, wangguan wg
where 
up.idcard = play.idcard and  = play.computerId 
--日期match
and DATEPART(year,play.date) = DATEPART(year,w.date)
and DATEPART(month,play.date) = DATEPART(month,w.date)
and DATEPART(day,play.date) = DATEPART(day,w.date)
and w.wangguanId = 

select * from work

insert into play (idcard)
values('211234561222')

select * from play
--查询今天上机还没下机的用户
select * from play 
where endTime is null
and DATEPART(year,date) = DATEPART(year,GETDATE())
and DATEPART(month,date) = DATEPART(month,GETDATE())
and DATEPART(day,date) = DATEPART(day,GETDATE())

--查询今天的上机记录
select * from play 
where 
DATEPART(year,date) = DATEPART(year,GETDATE())
and DATEPART(month,date) = DATEPART(month,GETDATE())
and DATEPART(day,date) = DATEPART(day,GETDATE())


--查询指定日期的上机记录
select * from play 
where DATEPART(year,date) = 2021
and DATEPART(month,date) = 12
and DATEPART(day,date) = 11


select * from userProfile up 

select SYSDATETIME()
select  GETDATE()

--获取datetime的年
select DATEPART(year,GETDATE())
--获取datetime的月
select DATEPART(MONTH ,GETDATE())
--获取datetime的日
select DATEPART(day ,GETDATE())
--获取datetime的时
select DATEPART(hour ,GETDATE())
--获取datetime的分
select DATEPART(MINUTE ,GETDATE())
--获取datetime的秒
select DATEPART(SECOND ,GETDATE())