SqlServer数据的修改和删除、基本查询、条件查询

四、数据的修改和删除

select * from Department
select * from [Rank]
select * from People
--修改--------------------------------------------------------
--语法:
--update 表名 set 字段1=值1,字段2=值2 where 条件
--工资调整，每个人加薪1000元
update People set PeopleSalary = PeopleSalary + 1000
--将员工编号为7的人加薪500
update People set PeopleSalary = PeopleSalary + 500 where PeopleId = 7
--将软件部（部门编号为2）的人员工资低于20000的调整成20000
update People set PeopleSalary = 20000
where DepartmentId = 2 and PeopleSalary < 20000
--修改刘备的工资是以前的两倍，并且把刘备的地址改为北京
update People set PeopleSalary = PeopleSalary*2, PeopleAddress = '北京'
where PeopleName = '刘备'

--删除--------------------------------------------------------------
--语法：
--delete from 表名 where 条件
--删除员工所有记录
delete from People
--删除市场部（部门编号为1）中工资大于10000的人
delete from People where DepartmentId = 1 and PeopleSalary > 10000

--关于删除（drop，truncate，delete）
drop table People --删除表对象
truncate table People --删除数据（清空数据，类似初始化），表对象即表结构依然存在
delete from People --删除所有数据，表对象即表结构依然存在
--truncate和delete区别
--truncate清空所有数据，不能有条件；delete可以删除所有数据也可以带条件，删除符合条件的数据
--自动编号：
--假设表中自动编号为1,2,3,4,5
--使用truncate清空数据之后再添加数据，编号仍然是1,2,3,4,5
--使用delete删除数据，删除的自动编号将永远不存在了，新编号变成6,7,8,9,10

五、基本查询

--查询所有列所有行（*代表所有列）
select * from Department
select * from [Rank]
select * from People
--查询指定列（姓名，性别，生日，月薪，电话）
select PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone from People
--查询指定列（姓名，性别，生日，月薪，电话）（列名后空格加别名 查询后显示别名）
select PeopleName 姓名,PeopleSex 性别,PeopleBirth 生日,PeopleSalary 月薪,PeoplePhone 电话 from People
--查询出员工所在城市（不需要重复数据显示）（列名前用distinct）
select distinct(PeopleAddress) from People
--假设准备加工资（上调20%），查询出加工资前后的员工数据
select PeopleName,PeopleSex,PeopleSalary,
PeopleSalary*1.2 加薪后的工资 from People

六、条件查询

--查询所有人信息
select * from People
--查询性别为女的员工信息
select * from People where PeopleSex = '女'
--查询工资大于等于20000元的员工信息
select * from People where PeopleSalary >=20000
--查询出性别为女，工资大于等于10000元的员工信息（多条件）
select * from People where PeopleSex = '女' and PeopleSalary >=10000
--查询出生年月在1980-1-1之后，而且月薪大于等于10000的女员工
select * from People where PeopleBirth >= '1980-1-1' and 
(PeopleSalary >= 10000 and PeopleSex ='女')
--查询月薪大于等于20000的员工，或者月薪大于等于8000的女员工
select * from People where PeopleSalary >= 20000 or 
(PeopleSalary >= 8000 and PeopleSex ='女')
--查询月薪在10000-20000之间的员工信息（between）
select * from People where PeopleSalary >=10000 and PeopleSalary <=20000
select * from People where PeopleSalary between 10000 and 20000
--查询出地址在广州或者北京的员工信息（in）
select * from People where PeopleAddress = '广州' or PeopleAddress = '北京'
select * from People where PeopleAddress in('广州','北京')

--排序order by
--查询所有员工信息，根据工资排序，降序
--asc升序（默认），desc降序
select * from People order by PeopleSalary desc
--查询所有员工信息，根据名字长度排序（降序）
select * from People order by len(PeopleName) desc
--查询出工资最高的5个人的信息
select top 5 * from People order by PeopleSalary desc
--查询出工资最高的10%的员工信息
select top 10 percent * from People order by PeopleSalary desc

--null:空值（null和空字符串不同）
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddTime) 
values(1,1,'马云','男','1977-7-7',50000,'1234567788823',GETDATE())
--查询出地址没有填写的员工信息（不能用= 只能用is null）
select * from People where PeopleAddress is null
--查询出地址已经填写的员工信息
select * from People where PeopleAddress is not null

--查询出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 = '刘备')

--查询出生肖是猪的人的信息
--鼠、牛、虎、兔、龙、蛇、马、羊、猴、鸡、狗、猪
--4   5	  6   7  8   9  10  11  0   1   2   3
select * from People where year(PeopleBirth)%12 = 3

--查询所有的员工信息，添加一列，显示生肖
select *,
case
	when year(PeopleBirth)%12=0 then '猴'
	when year(PeopleBirth)%12=1 then '鸡'
	when year(PeopleBirth)%12=2 then '狗'
	when year(PeopleBirth)%12=3 then '猪'
	when year(PeopleBirth)%12=4 then '鼠'
	when year(PeopleBirth)%12=5 then '牛'
	when year(PeopleBirth)%12=6 then '虎'
	when year(PeopleBirth)%12=7 then '兔'
	when year(PeopleBirth)%12=8 then '龙'
	when year(PeopleBirth)%12=9 then '蛇'
	when year(PeopleBirth)%12=10 then '马'
	when year(PeopleBirth)%12=11 then '羊'
	else''
end 生肖
from People
--简写------
select *,
case year(PeopleBirth)%12
	when 0 then '猴'
	when 1 then '鸡'
	when 2 then '狗'
	when 3 then '猪'
	when 4 then '鼠'
	when 5 then '牛'
	when 6 then '虎'
	when 7 then '兔'
	when 8 then '龙'
	when 9 then '蛇'
	when 10 then '马'
	when 11 then '羊'
	else''
end 生肖
from People 

七、模糊查询

select * from Department 
select * from [Rank]
select * from People

--模糊查询使用like关键字和通配符结合实现，通配符：
--%代表匹配0个字符、1个字符或多个字符
--_代表匹配有且只有1个字符
--[]代表匹配范围内
--[^]代表匹配不在范围内

--查询出姓刘的员工信息
select * from People where PeopleName like '刘%'
--查询出名字中含有'乔'的员工信息
select * from People where PeopleName like '%乔%'
--查询出名字中含有'乔'或者'香'的员工信息
select * from People where PeopleName like '%乔%' or PeopleName like '%香%'
--查询出姓刘的员工信息，名字是两个字
select * from People where PeopleName like '刘_'
select * from People where SUBSTRING(PeopleName,1,1)='刘'
and len(PeopleName)=2
--查询出最后一个字为香，名字共三个字的员工信息
select * from People where PeopleName like '__香'
select * from People where SUBSTRING(PeopleName,3,1)='香'
and len(PeopleName)=3
--查询出电话号码开头为138的员工信息
select * from People where PeoplePhone like '138%'
--查询出电话号码开头为138的，第四位好像是3或4，最后一个号码是8-----------(牛逼这都行）
select * from People where PeoplePhone like '138[3,4]%8'
--查询出电话号码开头是138的，第四位好像是2-5之间，最后一个号码不是2和3
select * from People where PeoplePhone like '138[2,3,4,5]%[^2,3]'
select * from People where PeoplePhone like '138[2-5]%[^2-3]'