数据库练习实操 寒假自学的 发着存档

--查询所有列所有行 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 --查询出员工所在城市（不需要显示重复显示） select distinct peopleaddress from People --假设准备加工资（上调20%），查询加后的员工数据 查询出加薪前的工资对比 select peoplename,peoplesex,peoplesalary 原始工资, peoplesalary*1.2 加薪后的工资 from People select *from People --查询性别为女 的员工信息 select *from people where peoplename = '倪岳椿' select *from people where peoplename = '贝多芬' --查询工资大于等于13000的员工信息 且是初级工 select *from people where peoplesalary >= 13000 and Rankid = 1 --查询月薪大于20000或者大于8000的高级工 select *from People where peoplesalary >=20000 or (peoplesalary >=8000 and Rankid = 3) --查询生日在1995-1-18之后 且 月薪大于15000的 男性工人 select *from People where peoplebirth >'1995-1-18' and (peoplesalary >=15000 and peoplesex = '男') --查询月薪在10000-20000之间的信息 select *from People where peoplesalary >10000 and peoplesalary <20000 select *from People where peoplesalary between 10000 and 20000 --查询地址在江苏或美国的员工信息 select *from People where peopleaddress ='江苏' or peopleaddress='美国' select *from People where peopleaddress in('江苏','美国') --排序 --查询员工信息 ，根据工资降序 asc正序 desc降序 select *from People order by peoplesalary desc --升序 asc 默认的 select *from People order by peoplesalary asc --根据姓名长度排序 select *from People order by len( peoplename) desc --查询出工资最高的3个人的信息 select top 3 *from People order by peoplesalary desc --查询出工资最高的百分之30的人的信息 select top 30 PERCENT *from People order by peoplesalary desc --null 空值 insert into People (Departmentid ,Rankid,peoplename ,peoplesex,peoplebirth ,peoplesalary,peoplephone,peopleaddtime) values(2,2,'奥特曼','女','1997-7-7',50000,'75656256',getdate()) --查询出地址为null的员工 is null select *from People where peopleaddress is null select *from People where peopleaddress is not null --查询出90后的员工信息 select *from People where peoplebirth >= '1990-1-1' and peoplebirth <= '1999-12-31' select *from People where peoplebirth between '1990-1-1' and '1999-12-31' select *from people where year(peoplebirth) between 1990 and 1999 --查询20-30岁的 并且工资在15000-20000的员工信息 --年龄 = 当前年份-生日年份 --select *from people where year(getdate())-year(peoplebirth)>=30 and year(getdata())-year(peoplebirth)<=40 select *from people where year(getdate())-year(peoplebirth)>=20 and year(getdate())-year(peoplebirth)<=30 and (peoplesalary >=15000 and peoplesalary <=20000) --查询巨蟹座的员工信息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 2001 %12 select *from People where year(peoplebirth)%12 =9 --查询所有员工信息 添加一列 显示生肖 select *, case 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 =11then '羊' when year(peoplebirth)%12 =0 then '猴' when year(peoplebirth)%12 =1 then '坤' when year(peoplebirth)%12 =2 then '狗' when year(peoplebirth)%12 =3 then 'pig' else '' end from people ------------------------------------- select *, case year(peoplebirth)%12 when 4 then '鼠' when 5 then '牛' when 6 then '虎' when 7 then '兔' when 8 then '龙' when 9 then '蛇' when 10 then '马' when 11then '羊' when 0 then '猴' when 1 then '坤' when 2 then '狗' when 3 then 'pig' else '' end from people