分离、附加、备份、还原
–去重
select distinct 列名from表名
–更新
update fenshu set name = ‘李四’where code = 9
–更改表名
sp_rename 表名
drop database 数据库名（删除数据库）drop table 表名
Delete from table where 列 表达式 值
select *from xinxi where fenshu between 80 and 100（两数必需前小后大）
select *from fenshu where name = ‘李四’and nianling = 26
select *from fenshu where name = ‘李四’or nianling = 26
select *from fenshu where name in(‘李四’,’赵六’)
select *from fenshu where name not in(‘李四’,’赵六’)
–模糊查询，%表示任意很多字符
select *from fenshu where name like ’%四%’
–下划线表示任意一个字符
select *from fenshu where name like’李_’
–下划线加中括号，等同于in的功能，任意一组满足就能查询出来
select *from fenshu where name like’_[李四，赵六]’
–按年龄排序
select *from fenshu order by nianling asc(默认升序)
select *from fenshu order by nianling desc(降序)
–按降序排列后，查前两名
select top 2 *from fenshu order by nianling desc
–组合排序
*用“,”隔开
select *from score order by Cno asc,Degree desc
–按条件查询后排序。查名字叫李四的人，并选前三高
select top 3 *from fenshu where name = ‘李四’ order by nianling desc
cast(列名 as 数据类型)
*代表所有的，将*改为列名，就能显示该列
ex. select top 1 chinese from xuesheng where class = 1 order by chinese
//查询xuesheng列表中class 1 的 chinese最低成绩
delete from haha where name is null—-把名字中含有null的所有数据删掉
update haha set cid = ‘370606198703152121’ 增加一列数据cid
1、向一个表中插入多条数据：
INSERT INTO tableName(col1,col2,col3)
SELECT 3,4,5
UNION ALL
SELECT 6,7,8
2、从其他的多张表中读取数据添加到新表中：
INSERT INTO tableName(col1,col2,col3)
SELECT a,b,c FROM tableA WHERE a=1
UNION ALL
SELECT a,b,c FROM tableB WHERE a=2
3、sql2008独有
INSERT INTO MyTable(ID,NAME)VALUES(7,’003′),(8,’004′),(9,’005′)
ex.
create database fenshu
use fenshu
create table study
(
code int,
name varchar(20),
chengji decimal(18,2),
)
go
sp_rename study,xuesheng
alter table xuesheng add [type] varchar
–alter table xuesheng add [type] sysname
–alter table xuesheng drop column [type]
insert into xuesheng values(‘1′,’张三’,’90’,’男’)
insert into xuesheng (code,name,[type])values(‘2′,’李四’,’女’)
insert into xuesheng values(‘3′,’王五’,’80’,’男’)
select *from xuesheng
select *from xuesheng where name = ‘张三’
