常用sql语句（备忘）

yorknong

　　1、添加一个自增ID字段
ALTER TABLE tblname ADD COLUMN csmid COUNTER (0, 1)　　2、删除一个字段
ALTER TABLE tblname DROP COLUMN csmid　　3、access创建一个有自增字段的表
CREATE TABLE tblname(id autoincrement, sname memo, ...)　　4、sql server判断某个字段是否为空字符串
SELECT * FROM tblname where fieldname!=''　　
或者
　　
SELECT * FROM tblname where fieldname''
　　5、取某一列的最大值
SELECT MAX(fieldname) from tblname;　　6、查询一个字段有哪几种取值
SELECT DISTINCT fieldname From tblname;　　6.1、统计一个字段不同取值的数量，即有多少种取值。
SELECT COUNT(DISTINCT fieldname) FROM tblname;　　7、统计一个字段的几种取值，并按统计量降序排列。
SELECT fieldname, COUNT(*)　　
FROM tblname
　　
GROUP BY fieldname
　　
ORDER BY COUNT(*) DESC;
　　8、统计学号相同，班级不同的学生
SELECT sno, COUNT(*) FROM　　
(
　　
SELECT sno, sclass FROM students
　　
GROUP BY sno, sclass
　　
)
　　
GROUP BY SNO
　　
ORDER BY COUNT(*) DESC
　　9、ACCESS，在students表中将name字段中含有abc的字段放在前面。其他数据库可能要将INSTR换为CHARINDEX。在access中instr从1开始计数，而不是从0开始计数。
SELECT *　　
FROM students
　　
ORDER BY INSTR(name, 'abc') DESC;
　　9.1、instr函数切记注意第一个和第四个参数。(可参考这里)
　　10、设置和去除主键，参考这里。
设置主键　　
ALTER TABLE tblname ADD CONSTRAINT PrimaryKey Primary Key(主键字段名)
　　
去除主键
　　
ALTER TABLE tblname DROP CONSTRAINT PrimaryKey
　　11、ACCESS从数据库A复制表A到数据库B（新建了表B）。先连上数据库A，然后
SELECT * INTO tableB [IN 'D:\B.mdb'] FROM tableA　　12、ACCESS从数据库A复制表A到数据库B（追加数据到表B）。先连上数据库A，然后
INSERT INTO tableB(field1, field2) [IN 'D:\B.mdb'] SELECT field1, field2 FROM tableA　　当表结构完全相同，合并表时可以这么写：
INSERT INTO tableB [IN 'D:\B.mdb'] SELECT * FROM tableA　　13、MySQL查询时将日期加8小时。(DATE_ADD函数)
SELECT DATE_ADD(datefiled, INTERVAL 8 HOUR ) FROM tbl;　　14、MySQL清除空白可查阅trim和replace函数。
　　15、根据关键字段去重，需用到临时表。这里主要是提供思路，需根据具体数据库得到具体sql语句。（参考这里）
/*添加自增ID*/　　
select identity(int,1,1) as autoID, * into tmpTable from tableName
　　
/*选出重复记录中值最小的自增ID*/
　　
select min(autoID) as autoID into tmpTable2 from tmpTable group by keyField1, keyField2
　　
/*得到最终结果*/
　　
select * from tmpTable where autoID in(select autoID from tmpTable2)
　　16、ADO获取SELECT COUNT(*)的结果。
num = rs->GetCollect(_variant_t((long)0))　　17、ADO判断字段值是否为NULL。
vField.vt ?= VT_NULL　　18、ADO访问ACCESS的3092错误的两种可能原因。
/* date为保留字，应该加中括号 */　　
SELECT date FROM tableName
　　
/* 这个中括号按说不应该影响的 */
　　
SELECT COUNT(*) FROM tableName WHERE field'[D2037'
　　原来第2种问题是有说法的，解决办法见这里。
　　19、ACCESS与外部其它数据交互 - IN子句，可参考这里。
　　20、VC++下使用ADO访问Access数据库完整篇。
　　21、ACCESS单引号的转义字符是单引号，也就是说如果字段值中出现单引号，需用两个单引号表示。（可参见这里）
　　21.1、另外注意，字符串中若有nul字符，也会插入失败，这种情况比较难以排查。因为看起来的状况是，拷贝到access文件里面里能执行，而代码里不能执行。
　　22、SQLServer导出到excel。
　　23、设置表的autoid字段从10开始自增，每次增1。
ALTER TABLE tableName　　
ALTER COLUMN [autoid] COUNTER (10, 1)
　　24、ACCESS得到数据库中所有表名。
SELECT NAME FROM MSysObjects　　
WHERE TYPE=1 AND FLAGS=0
　　25、ACCESS设置字段前导0示例。
UPDATE tblname　　
SET fieldname=FORMAT(fieldname, "00000");
　　26、mysql无重复插入。（参考这里）
　　1）、无则插入，有则忽略。
/*某些版本的mysql会有一个警告*/　　
INSERT IGNORE INTO tblname(a,b,c) VALUES (1,2,3);
　　
/*不会有警告*/
　　
INSERT INTO tblname(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c;
　　2）、无则插入，有则更新。
INSERT INTO tblname(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;　　27、mssql（SQLServer）无重复插入，注意下面两个1并不是walker抽风了。（参考这里和那里）
INSERT INTO  tableName(keyA, keyB) SELECT valA, valB　　
WHERE NOT EXISTS
　　
(SELECT TOP 1 1 FROM tableName WHERE keyA=valA AND keyB=valB);
　　28、两表相减。（in、not in、exists、not exists）
　　1）、单字段。
SELECT field FROM tableA　　
WHERE field NOT IN
　　
(
　　
SELECT field FROM tableB
　　
)
　　2）、多字段。
SELECT * FROM tableA　　
WHERE NOT EXISTS
　　
(
　　
SELECT * FROM tableB
　　
WHERE tableA.field1=tableB.field1 AND tableA.field2=tableB.field2
　　
)
　　29、Access跨文件连接查询。（Access 跨数据库查询和插入数据）
SELECT * FROM [;database=d:\A.mdb].tblA,[;database=d:\B.mdb].tblB　　
WHERE tblA.fieldA=tblB.fieldB;
　　

　　
SELECT * FROM tblA,[;database=d:\B.mdb].tblB
　　
WHERE tblA.fieldA=tblB.fieldB;
　　30、Access数据导入到SQLServer可用如下Transact-SQL语句。（参考这里）
INSERT INTO destTable　　
SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'D:\test\test.mdb';'admin';'', srcTable);
　　
INSERT INTO destTable
　　
SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'D:\test\test.mdb';'admin';'', srcTable);
　　31、查找以数字打头的数据。（MySQL，正则）
SELECT * FROM tableName WHERE fieldName REGEXP '^[0-9]' LIMIT 100;　　32、MySQL利用存储过程执行循环语句。
USE dbname;　　

　　
DELIMITER $$  --定义结束符为$$
　　

　　
DROP PROCEDURE IF EXISTS test_insert;  --删除已有存储过程
　　

　　
CREATE PROCEDURE test_insert()  --创建新的存储过程
　　BEGIN
　　DECLARE i INT DEFAULT 1;
　　WHILE i