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;

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;