sql语句-备忘

-- 清空全部数据，不写日志，不可恢复，速度极快
truncate table 表名;
 
-- 清空全部数据，写日志，数据可恢复，速度慢
delete from 表名;

 

--从一个数据库复制一张表带另一个数据库（或同一个数据库）

SELECT * INTO [zbdb].[dbo].t_bctest FROM [zbdb].[dbo].tbl_zb_weekbcinfo

 

--删除数据表中的空格字段

delete from [zbdb].[dbo].t_test where dqfsmj is NULL and bzxzfsmj is NULL and ljfsmj is Null and bzfzmj is null and dqrxfmj is null and ljfzmj is null and bz is null

 

--删除数据表中值为0的字段

delete from [zbdb].[dbo].t_bctest where dqfsmj + bzxzfsmj + ljfsmj + bzfzmj + dqrxfmj + ljfzmj =0

 

--删除前几条数据

delete FROM [zbdb].[dbo].t_test where basicid in(select top 15215 basicid FROM [zbdb].[dbo].t_test)

 

--创建自动增长类型字段的表

CREATE   TABLE   t_bctest(zjid [int] IDENTITY(1,1)NOT NULL);

 

--添加新字段

alte table t_test add zhujian int(10);

 

--join连贯查询
SELECT
    t_zwsbdt.begindate,
    t_pestweek.*
FROM
    t_pestweek
INNER JOIN t_zwsbdt ON LEFT (t_pestweek.weekbh, 4) = t_zwsbdt. YEAR
WHERE
    t_zwsbdt.zwname = "大豆花生"

SELECT
    t_zwsbdt.begindate,
    t_pestweek.*,
concat(t_pestweek.weekbh,t_zwsbdt.begindate) as ywsj
FROM
    t_pestweek
INNER JOIN t_zwsbdt ON LEFT (t_pestweek.weekbh, 4) = t_zwsbdt. YEAR
WHERE
    t_zwsbdt.zwname = "大豆花生"

--使用sql语句添加列   

alter   table   student   add   nickname   char(20)

--添加主键 

alter table t_pest add primary key(bh);