当前位置：首页 > news >正文

条件查询-2

news 2026/5/15 21:41:41

use DBTESTS
if exists(select * from sys.objects where name = 'Department' and type = 'U')
drop table Department
--建表
create table Department
(

DepartmentId int primary key identity(1,1),
DepartmentName nvarchar(50) not null,
DepartmentRemark text

)
--char 无论存储是否10个字节仍然占用10个字节
--varchar 使用多少就占用多少个字节
--text长文本

create table [Rank]
(

RankId int primary key identity(1,1),
RankName nvarchar(50) not null,
RankRemark text

)

create table People
(

PeopleId int primary key identity(1,1),
DepartmentId int references Department(DepartmentId) not null,--部门 引用外键
RankId int  references [Rank] (RankId) not null,--职级 引用外键
PeopleName nvarchar(50) not null,
PeopleSex nvarchar(1) default('男')check(PeopleSex='男' or PeopleSex='女')not null,
PeopleBirth smalldatetime not null,
PeopleSalary decimal(12,2) check(PeopleSalary>=1000 and PeopleSalary<=1000000) not null,
PeoplePhone varchar(20) unique not null,
PeopleAddress varchar(300),
PeopleAddTime smalldatetime default(getdate()) --添加时间 只需要记录到年月日时分，不需要秒、毫秒

)

--修改表结构
--添加列
--alter table 表名 add 新列名数据类型
alter table People add PeopleMail varchar(200)

--删除列
--alter table 表名 drop column 列名
alter table People drop column PeopleMail

--修改列
--alter table 表名 alter column 列名数据类型
--修改地址varchar(300)为varchar(200)
alter table People alter column PeopleAddress varchar(200)

--删除约束删除添加
--alter table 表名 drop constraint 约束名
--删除月薪的约束
alter table People drop constraint CK__People__PeopleSa__403A8C7D

--添加约束
--alter table 表名 add constraint 约束名
alter table People add constraint CK__People__PeopleSa1
check(PeoPleSalary>=1000 and PeoPleSalary<=1000000)

--添加约束主键
alter table 表名 add constraint 约束名 primary key(列名)
--添加约束唯一
alter table 表名 add constraint 约束名 unique(列名)
--添加约束默认值
alter table 表名 add constraint 约束名 default 默认值 for 列名
--添加约束(外键)
alter table 表名 add constraint 约束名 foreign key(列名)
references 关联表名(列名(主键))

--查询出80厚的员工信息
select* from People where PeopleBirth>='1980-1-1'
and PeopleBirth<='1989-12-31'
select* from People where PeopleBirth between '1980-1-1'and'1989-12-31'
select* from People where year(PeopleBirth)between 1980 and 1989
--查询30-40岁之间并且工资15000-30000之间的员工信息
--假设年龄=当前年份-生日年份
select* from People where
(year(getdate())-year(PeopleBirth)>=30 and year(getdate())-year(PeopleBirth)<=40)
and
(PeopleSalary>=15000 and PeopleSalary<=30000)

select* from People where
(year(getdate())-year(PeopleBirth) between 30 and 40)
and
(PeopleSalary between 15000 and 30000)

--查询出星座是巨蟹座的员工信息(6.22-7.22)
select * from People where
(month(PeopleBirth)=6 and day(PeopleBirth)>=22)
or
(month(PeopleBirth)=7 and day(PeopleBirth)<=22)

--查询出工资比赵云高的人的信息
select * from People where PeopleSalary>
(select PeopleSalary from People where PeopleName = '赵云')

--查询出和赵云在一个城市的人的信息
select * from People where PeopleAddress=
(select PeopleAddress from People where PeopleName = '赵云')

--查询所有员工信息添加一列显示生肖
select*,
case
when year(PeopleBirth) % 12 = 4 then '鼠'
when year(PeopleBirth) % 12 = 5 then '牛'
when year(PeopleBirth) % 12 = 6 then '虎'
when year(PeopleBirth) % 12 = 4 then '兔'
when year(PeopleBirth) % 12 = 5 then '龙'
when year(PeopleBirth) % 12 = 6 then '蛇'
when year(PeopleBirth) % 12 = 4 then '马'
when year(PeopleBirth) % 12 = 5 then '羊'
when year(PeopleBirth) % 12 = 6 then '猴'
when year(PeopleBirth) % 12 = 4 then '鸡'
when year(PeopleBirth) % 12 = 5 then '狗'
when year(PeopleBirth) % 12 = 6 then '鼠'
end 生肖
from People