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

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;

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;