SQL Server语句大全(增删改查数据、创建表、删除表、修改表)

SQL server 增删改查语句

新增

insert into test.dbo.users (id,username,password)
values(1,'lisi',123),(2,'lisi',123);

insert into test.dbo.users (id,username,password)	-- 将查询结果插入
select * from test.dbo.users;

删除

delete test.dbo.users where id=1

在sqlserver中delete的from是可以省略的。

 

修改

update test.dbo.users set username='aaa' where id=1;

查询

select distinct * from test.dbo.users;	-- 去重
select top 3 * from test.dbo.users;		-- 前n行

表操作

创建表

create table teacher(
    id int primary key,
    name varchar(10) not null,
    age int
    )

删除表

drop table teacher;

修改表

alter table teacher		-- 添加字段
add name varchar(10) not null;
	
alter table teacher		-- 删除字段
drop column name;
	
exec sp_rename 'teacher.name','newname','COLUMN';	-- 修改字段
	
alter table teacher		-- 修改字段类型
alter column name varchar(10) not null;

SQL server 查询语句

基础查询

select * from test.dbo.users    -- 普通条件查询
where id=1;

模糊查询

select * from test.dbo.users where username like '%li%';

范围查询

select * from test.dbo.users	-- id在1~3之间的数据
where id between 1 and 3;
	
select * from test.dbo.users	-- id在1~3以外的数据
where id not between 1 and 3;

子查询

	select * from test.dbo.users	-- id为1或2或3的数据
	where id in(1,2,3);
	
	select * from test.dbo.users	-- id不是1或2或3的数据
	where id not in(1,2,3);

排序

select * from test.dbo.users	-- 从小到大排序
order by id asc;
    
select * from test.dbo.users	-- 从大到小排序
order by id desc;

整篇文章是整个SQL Server基础的操作语句，熟练掌握即可基本进行维护与实施工作。