查询SQL server 密码策略 怎么查询sqlserver密码

1.非条件查询

--查询所有列所有行
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;

2.条件查询

=：等于，比较是否等于及赋值

!=:比较不等于

>：比较大于

<：比较小于

>=：比较大于等于

<=：比较小于等于

IS NULL：比较为空

IS NOT NULL：比较不为空

in：比较是否其中

like：模糊查询

BETWEEN...AND...：比较是否在两者之间

and：逻辑与（两个条件同时成立表达式成立）

or：逻辑或（两个条件有一个成立表达式成立）

not：逻辑非（条件成立，表达式则不成立；条件不成立，表达式则成立）

查询示例：

--查询性别为女的员工信息
select *from People where PeopleSex='女';
--查询工资大于等于10000元的员工信息
select *from People where PeopleSalary>=10000;
--查询出性别为女，工资大于等于10000元的员工信息（多条件）
select *from People where PeopleSex='女' and PeopleSalary>=1000;
--查询月薪大于等于10000员工，或者月薪大于等于8000的女员工
select *from People where PeopleSalary>=10000 or (PeopleSalary>=8000 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;
--查询所有员工信息，根据名字长度排序（降序）
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;
--查询出地址没有填写的员工信息
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)) between 30 and 40;
--查询出星座是巨蟹座的员工信息（6.22-7.22）
select *from People where(month(PeopleBirth)=6 and day(PeopleBirth)>=22) or(month(PeopleBirth)=7 and day(PeopleBirth)<=21);
--查询出工资比赵云高的人的信息
insert into People
(
DepartmentId,
RankId,
PeopleName,
PeopleSex,
PeopleBirth,
PeopleSalary,
PeoplePhone,
PeopleAddress)
values
(18,1,'赵云','男','1987-10-1','6500','13580000010','深圳');
select *from People where PeopleSalary>(select top 1 PeopleSalary from People where PeopleName='赵云');
--查询出生肖人员信息
--鼠、牛、虎、兔、龙、蛇、马、羊、猴、鸡、狗、猪
--4   5    6   7   8   9   10  11  0   1  2    3
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=11 then '羊'
when year(PeopleBirth)%12=0 then '猴'
when year(PeopleBirth)%12=1 then '鸡'
when year(PeopleBirth)%12=2 then '狗'
when year(PeopleBirth)%12=3 then '猪'
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 11 then '羊'
when 0 then '猴'
when 1 then '鸡'
when 2 then '狗'
when 3 then '猪'
end 生肖 from People

--查询出姓赵的员工信息
select *from People where PeopleName like '赵%';
--查询出名字含有"一"的员工信息
insert into People
(
DepartmentId,
RankId,
PeopleName,
PeopleSex,
PeopleBirth,
PeopleSalary,
PeoplePhone,
PeopleAddress)
values
(18,1,'徐一凡','男','1987-10-1','6500','13580000020','深圳');
select *from People where PeopleName like '%一%';
--查询出姓徐的员工信息，名字是三个字
select *from People where PeopleName like '徐__';
select *from People where SUBSTRING(PeopleName,1,1)='徐' and LEN(PeopleName)=3;
--查询出电话号码开头为138的，第四位好像是7或者8，最后一个号码是5
select *from People where PeoplePhone like '138[7,8]%5';
--查询出电话号码开头为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]';

注：

模糊查询使用like关键字和通配符结合来实现，通配符具体含义如下：

%：代表匹配0个字符、1个字符或多个字符

_:代表匹配有且只有1个字符

[]:代表匹配范围内

[^]:代表匹配不在范围内

3.连表查询

--分组查询
select PeopleAddress 城市,count(*) 总人数,
MIN(PeopleSalary) 最低工资,
MAX(PeopleSalary) 最高工资,
AVG(PeopleSalary)平均工资 
from People group by PeopleAddress having COUNT(*)>=2;
--内连接：不符合主外键关系的数据不会被显示
select *from People inner join Department on People.DepartmentId =Department.DepartmentId;
--外连接（左连接，右连接，全外联接）
--左连接：以左表为主表进行数据显示，主外键关系找不到的数据null取代
select *from People 
left join Department on People.DepartmentId =Department.DepartmentId 
left join [Rank] on People.RankId =[Rank].RankId;
--右连接：以右表为主表进行数据显示，主外键关系找不到的数据null取代
select *from People
right join Department on People.DepartmentId =Department.DepartmentId 
right join [Rank] on People.RankId =[Rank].RankId;
--全连接：两张表的所有数据无论是否符合主外键关系必须全部显示，不符合主外键关系的地方null取代
select *from People 
full join Department on People.DepartmentId =Department.DepartmentId 
full join [Rank] on People.RankId =[Rank].RankId;
--根据部门分组统计员工人数，员工工资总和、平均工资、最高工资和最低工资
--平均工资在9000以下的不参与统计，并且根据平均工资降序排序
select 
[Department].DepartmentId 部门ID,
[Department].DepartmentName 部门名称,
[Rank].RankName 级别名称,
sum(PeopleSalary) 平均总和,
avg(PeopleSalary) 平均工资,
max(PeopleSalary) 最高工资,
min(PeopleSalary) 最低工资
from [People]
left join [Department] on [People].DepartmentId =[Department].DepartmentId
left join [Rank] on [People].RankId =[Rank].RankId
group by [Department].DepartmentId,[Department].DepartmentName,[Rank].RankName
having avg(PeopleSalary)>=9000
order by avg(PeopleSalary) desc;