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;
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基础的操作语句,熟练掌握即可基本进行维护与实施工作。