常用的sql语句

把Chanpin1表里面的数据导入Chanpin里面

INSERT INTO [t].[dbo].[Chanpin]([shengfen] ,[s_name],[product_name],[product_sb],[ton],[zsbh],[date]) select [F2],[F3],[F4],[F5],[F6],[F7],[F8] from Chanpin1

下面是sql语句的批处理更新操作：

update product1_26bak2_12 setproduct1_26bak2_12.company=companybak2_10.name

from companybak2_10  where product1_26bak2_12.comid=companybak2_10.id

 

update a set namecn=b.namecn,nameen=b.nameen

from chemDictionary a,chemDictionary_5_1b wherea.cas=b.casand a.id<601641

下面是sql语句的批量删除操作,就是这时候的id不要用=号而用in来进行操作

delete from product1_26where idin(select idfromproduct1_26)

 

delete from productbak2_14testwhere id notin (selectmin(id)

from productbak2_14test group by namecn,company)

如果是跨库操作的话

select * intochemcndata.dbo.admin_group1 from admin_group

 

下面一行的作用是创建于Pollu_freeProduct表结构相同的Pollu_freeProduct1表，然后把Pollu_freeProduct表中的不相同的数据放入Pollu_freeProduct1中，太强大了

select distinct*into Pollu_freeProduct1fromPollu_freeProduct

UPDATE HomelyRecipe

  SET food_res=replace(food_res,'&#8226;','')

REPLACE(<string_expression1>，<string_expression2>，<string_expression3>) 用string_expression3 替换在string_expression1中的子串string_expression2。

简单的创建存储过程：

create procedure proc_company
as
declare @avg float
select @avg = avg(validate) from company
print '平均:'+convert(varchar(5),@avg)
go

执行存储过程：

exec proc_company

创建触发器：

create trigger cfq
on company
for update
as
begin
insert into company(name)values('触发器插入')
end

select id,vip,email from 
( select c.id,c.vip,c.email,c.name,a.createtime as acreatetime,a.validate as avalidate,a.id as aid from company c,auditedSupplier a where c.id=a.comid ) as t

正序反序正序的分页存储方法

select top 5 * from table where id in
    (select top 5 id from table where id in (select top 10 idfrom table order by  id ) order by id desc)
    order by id

好了，到这里，我们的第六条到第十条，也就是第二页的内容就算是取出来了

Mysql 多个join 语句的写法

ELECT view_kwarg_temp.kwarg_id, course_name, class_name, teacher_name, classroom_name, jkjsA, jkjsB
FROM view_kwarg_temp
LEFT JOIN jkjsA ON view_kwarg_temp.kwarg_id = jkjsA.kwarg_id
LEFT JOIN jkjsB ON view_kwarg_temp.kwarg_id = jkjsB.kwarg_id