sql server2008r2 添加、修改、删除、查询
1.插入数据行
insert [into] 表名 [(列名)] values（值列表）
例：在对应的数据库school中新建查询，insert into+表名students（列名）+values+(值列表)
insert into students(name,address,grade,emaill,sex)
values('张青ming',DEFAULT,1,'zhang@163',1)
2.通过 insert select 语句将现有表中的数据添加到已存在的表中去
insert into <表名>(列名) select <列名> from <源表名>
例：
insert into new_table(uname,uaddress)
select name,address
from students
3.通过select into 语句将现有表中的数据添加到新表中（适用于备份表，只可用一次）
select <列名> into(表名) from <源表名>
例：
select name,address,grade,emaill,sex
into student_bak
from students
4.通过 union 关键字合并数据进行插入
insert into <列名>(表名)
select <列名> union
select <列名> union
select <列名> union
......
例：
insert into students(name,address,grade,emaill,sex)
select '大宝','湖北',1,null,1 union
select '二宝','湖北',1,null,1 union
select '三宝','湖北',1,null,1
5.使用 update 更新数据行
update <表名> set 列名 = 更新值,列名 = 更新值....[where 更新条件]
例：
update students set name = '张清山' where name = '张青ming'
update students set grade = grade + 90 where grade <=90
update students set name = 'zhangwen',address = '湖北chacha' where code = 3
6.使用 delete 删除数据行
delete [from] <表名> [where<删除条件>]
例：
delete from students where address = ''
7.使用truncate 删除数据行
truncate table 表名
例：truncate table students
相当于：delete from students
8.查询语法
select <列名> from <表名> [where <查询条件>] [order by <排序的列名>[asc或desc]]
例：
select code,name,address from students where sex =1 order by code
select * from students where sex =1 order by code
select * from students order by code
select * from students
9.使用as命名列
例：
select code as '编号',name as '姓名',address as '地址' from students
select '编号'= code,'姓名' = name,'地址' = address from students
select grade + 5 as '成绩加5显示'from students
10.限制行数
限制固定行数
例：
select top 5 * from students
select top 5 name,address from students
select top 5 name,address from students where sex = 1
按百分比返回
select top 20 percent * from students
select top 20 percent name,address from students
select top 20 percent name,address from students where sex = 1
11.排序
升序排序
select * from students order by grade
select * from students order by grade asc
降序
select * from students order by grade desc
多列排序
select * from students order by grade,code
select * from students order by grade desc,code desc
