博文
精妙SQL语句(转)(2007-08-11 14:36:00)
摘要：
本文转自:http://www.kingmx.com/article/14874
一、基础
1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice \'disk\', \'testBack\', \'c:mssql7backupMyNwind_1.dat\' --- 开始 备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键： Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 wher......
几条SQL语句(2007-04-06 11:51:00)
摘要：1，CONVERT和BETWEEN。。。AND
SELECT DWMC,SUM(CK.ZJE) AS SJHK,SUM(CK.ZCJE) AS ZCJE,SUM(CK.BZJE) AS BZJE ,SUM(JSJE) AS JSJE FROM CKDXX AS CK
INNER JOIN SGDWJBXX AS SG ON SG.DWDM=CK.SGDW
WHERE JSJE!=0 AND CONVERT(DATETIME,CK.KDRQ) BETWEEN '2007-03-24' AND '2007-03-30' GROUP BY DWMC ORDER BY DWMC
2，把查询结果SQL语句里作为一个新字段，查询结果为空则赋0值
SELECT DISTINCT ISNULL((SELECT SUM(ZXSRS) FROM XXJBXX WHERE XXJBXX.XXDM LIKE '" + xxdm + "%' AND XXLB LIKE '" + xxlb + "%'),0) AS ZRS, SUM(TJ.XYS) AS XYS, SUM(TJ.XYE) AS XYE
FROM ZCFLTJ AS TJ INNER JOIN XXJBXX AS XX ON TJ.XXDM = XX.XXDM
WHERE TJ.XXDM LIKE'" + xxdm + "%' AND XX.XXLB LIKE '" + xxlb + "%' AND TJ.ND='" + year + "' AND TJ.FL='" + flmc + "'
3，把一个字段值取最后四位后，求其最大值
select max(convert(int,(right(DM,4)))) from DM_ZCFL where dm like '"+fl+"%' and len(dm)="+(fl.Length+4)+" and flbz=0"......