SQL语句实用教程：增删改查操作

//创建删除表
create table person2(Name nvarchar(max),Age int,ID int not null )
drop table person2
insert into Person1(number,name,age)values(9,'jim',19)
//获取guid
select newid()
--练习插入列，更新列，以及设置guid主键
create table Person3(id uniqueidentifier,name nvarchar(max),age int ,nickname nvarchar(max))
insert into person3(id,name,age,nickname)values(newid(),'yukky',20,'halloo')
insert into person3(id,name,age)values(newid(),'jim',24)
insert into person3(id,name,age,nickname)values(newid(),'dodo',45,'hoo')
insert into person3(id,name,age,nickname)values(newid(),'holyt',27,'jhy')
update person3 set nickname='人' where age>=30 or age<20
--数据删除
delete from person3 where name='yukky'
--数据检索
create table T_Employee(FNumber varchar(20),FName varchar(20),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 as 姓名,FAge as 年龄,FSalary as 月薪 from T_Employee where FSalary<5000
select * from T_Employee where FSalary>5000
select getdate()as 日期

select count(*)from T_Employee
select max(FSalary)from T_Employee
select min(FSalary)from T_Employee
select avg(FSalary) from T_Employee
select count(*)from T_Employee where FSalary>5000
select *from T_Employee order by FAge ASC,FSalary ASC

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

select * from T_Employee
where FNumber like 'DEV%'

select null+1

select null+'kk'

select * from T_Employee
where FName is not null--microsoft sqlserver中使用is null或is not null进行空值判断！null意为“不知道”


select * from T_Employee
where FAge<25 and FAge>20

select * from T_Employee
where FAge between 20 and 25

select * from T_Employee
where FAge in(23,25,28)