Four-learn

数据删除

数据删除

数据检索

数据检索

测试代码：
create table T_Employee
(FNumber varchar(50),FName varchar(50),FAge int,FSalary numeric(10,2)
,PRIMARY KEY(FNumber));
insert into T_Employee(FNumber,FName,FAge,FSalary)values('DEV001','Tom',25,8300);
insert into
T_Employee(FNumber,FName,FAge,FSalary)values('DEV002','Jerry',28,2300.80);
insert into
T_Employee(FNumber,FName,FAge,FSalary)values('SALES001','John',23,5000);
insert into
T_Employee(FNumber,FName,FAge,FSalary)values('SALES002','Kerry',28,'6200');
insert into
T_Employee(FNumber,FName,FAge,FSalary)values('SALES003','Stone',22,1200);
insert into
T_Employee(FNumber,FName,FAge,FSalary)values('HR001','Jane',23,2200.88);
insert into
T_Employee(FNumber,FName,FAge,FSalary)values('HR002','Tina',25,5200.36);
insert into
T_Employee(FNumber,FName,FAge,FSalary)values('IT001','Smith',28,3900);
insert into
T_Employee(FNumber,FAge,FSalary)values('IT002',27,2800);

感兴趣，可以试一试下面代码
select FName,FAge from T_Employee
where FSalary>5000;
select FName as 姓名,FAge as 年龄 from T_Employee
where FSalary<5000;
select NEWID()
select getdate()
select 1+2

版本号：
select @@VERSION

select 1+1 as 列1,GETDATE()as 时间,NEWID() as 编号;

select FName as 姓名,FAge as 年龄,FSalary+10000 as 月薪,GETDATE() as 当前时间
from T_Employee

数据汇总

数据汇总

计数/最大工资/最小值/平均值/合计
select count(*)from T_Employee
select MAX(FSalary)from T_Employee
select min(FSalary)from T_Employee
select avg(FSalary)from T_Employee
select sum(FSalary)from T_Employee

检索出工资大于5000的个数
select count(*) from T_Employee
where FSalary>5000;

数据排序

数据排序

排序 升序ASC /降序DESC
select * from T_Employee
order by FSalary ASC

select * from T_Employee
Order by FSalary DESC

检索出表，年龄升序排，工资降序排
select * form T_Employee
order by FAge DESC, FSalary ASC

当出现判断条件时，注意where关键字的位置
select *from T_Employee
where FAge>23
order by Fage DESC, FSalary ASC

通配符过滤（模糊匹配）

通配符过滤00:20:00

select * from T_Employee
where FName='Tom';

但是如果只有 To 字段的片段时，如何操作
单字符匹配
select * from T_Employee
where FName LIKE'_erry'

select *from T_Employee
where FName like'%n%'

select *from T_Employee
where FNumber like'DEV%';

空值处理

理解这句话null表示不知道不是没有。
null+1=？/不知道+1=不知道
答案是null（不知道）
要想知道表中是null
select * from T_Employee
where FName is null;

不是
select * from T_Employee
where FName is not null;

空值处理

多值匹配

多值匹配

下面的方式，作用相同或者类似，只是写法不同取决于你高兴！！！

你高兴！哈哈哈

检索出制定的年龄
select * from T_Employee
where FAge=23,FAge=25orFAge=28;

检索出指定的年龄
selsct *form T_Employee
where FAge in (23,25,28);

检索出年龄在20和30之间的
select *from T_Employee
where FAge>20 and FAge <30;

检索出年龄在23与30之间的
select *form T_Employee
where FAge between 23 and 30;