常用sql语句(备忘)-阿里云开发者社区

开发者社区> 数据库> 正文

常用sql语句(备忘)

简介:

1、添加一个自增ID字段

1
ALTER TABLE tblname ADD COLUMN csmid COUNTER (0, 1)

2、删除一个字段

1
ALTER TABLE tblname DROP COLUMN csmid

3、access创建一个有自增字段的表

1
CREATE TABLE tblname(id autoincrement, sname memo, ...)

4、sql server判断某个字段是否为空字符串

1
2
3
SELECT FROM tblname where fieldname!=''
或者
SELECT FROM tblname where fieldname<>''

5、取某一列的最大值

1
SELECT MAX(fieldname) from tblname;

6、查询一个字段有哪几种取值

1
SELECT DISTINCT fieldname From tblname;

6.1、统计一个字段不同取值的数量,即有多少种取值。

1
SELECT COUNT(DISTINCT fieldname) FROM tblname;

7、统计一个字段的几种取值,并按统计量降序排列。

1
2
3
4
SELECT fieldname, COUNT(*)
FROM tblname
GROUP BY fieldname
ORDER BY COUNT(*) DESC;

8、统计学号相同,班级不同的学生

1
2
3
4
5
6
7
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开始计数。

1
2
3
SELECT *
FROM students
ORDER BY INSTR(name'abc'DESC;

9.1、instr函数切记注意第一个和第四个参数。(可参考这里)

10、设置和去除主键,参考这里

1
2
3
4
设置主键
ALTER TABLE tblname ADD CONSTRAINT PrimaryKey Primary Key(主键字段名)
去除主键
ALTER TABLE tblname DROP CONSTRAINT PrimaryKey

11、ACCESS从数据库A复制表A到数据库B(新建了表B)。先连上数据库A,然后

1
SELECT INTO tableB [IN 'D:\B.mdb'FROM tableA

12、ACCESS从数据库A复制表A到数据库B(追加数据到表B)。先连上数据库A,然后

1
INSERT INTO tableB(field1, field2) [IN 'D:\B.mdb'SELECT field1, field2 FROM tableA

当表结构完全相同,合并表时可以这么写:

1
INSERT INTO tableB [IN 'D:\B.mdb'SELECT FROM tableA

13、MySQL查询时将日期加8小时。(DATE_ADD函数)

1
SELECT DATE_ADD(datefiled, INTERVAL 8 HOUR FROM tbl;

14、MySQL清除空白可查阅trim和replace函数。

15、根据关键字段去重,需用到临时表。这里主要是提供思路,需根据具体数据库得到具体sql语句。(参考这里

1
2
3
4
5
6
/*添加自增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(*)的结果。

1
num = rs->GetCollect(_variant_t((long)0))

17、ADO判断字段值是否为NULL。

1
vField.vt ?= VT_NULL

18、ADO访问ACCESS的3092错误的两种可能原因。

1
2
3
4
/* 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。

1
2
ALTER TABLE tableName
ALTER COLUMN [autoid] COUNTER (10, 1)

24、ACCESS得到数据库中所有表名。

1
2
SELECT NAME FROM MSysObjects
WHERE TYPE=1 AND FLAGS=0

25、ACCESS设置字段前导0示例。

1
2
UPDATE tblname
SET fieldname=FORMAT(fieldname, "00000");

26、mysql无重复插入。(参考这里

1)、无则插入,有则忽略。

1
2
3
4
/*某些版本的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)、无则插入,有则更新。

1
INSERT INTO tblname(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

27、mssql(SQLServer)无重复插入,注意下面两个1并不是walker抽风了。(参考这里那里

1
2
3
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)、单字段。

1
2
3
4
5
SELECT field FROM tableA
WHERE field NOT IN
(
SELECT field FROM tableB
)

2)、多字段。

1
2
3
4
5
6
SELECT FROM tableA
WHERE NOT EXISTS
(
SELECT FROM tableB 
WHERE tableA.field1=tableB.field1 AND tableA.field2=tableB.field2
)

29、Access跨文件连接查询。(Access 跨数据库查询和插入数据

1
2
3
4
5
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语句。(参考这里

1
2
3
4
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,正则)

1
SELECT FROM tableName WHERE fieldName REGEXP '^[0-9]' LIMIT 100;

32、MySQL利用存储过程执行循环语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
USE dbname;
 
DELIMITER $$  --定义结束符为$$
 
DROP PROCEDURE IF EXISTS test_insert;  --删除已有存储过程 
 
CREATE PROCEDURE test_insert()  --创建新的存储过程
    BEGIN  
    DECLARE INT DEFAULT 1;
    WHILE i<10  DO
        INSERT INTO tableName(idx) VALUES (i); 
        SET i=i+1;
    END WHILE;
     
END $$  --结束定义语句
 
DELIMITER ;  --恢复结束符为;
 
CALL test_insert();  --调用存储过程

33、SQLite数据多表联合update。(注意:这种方式会伤害无关字段!


N、....


*** walker * Updated 2016-08-05 ***

本文转自1、添加一个自增ID字段

1
ALTER TABLE tblname ADD COLUMN csmid COUNTER (0, 1)

2、删除一个字段

1
ALTER TABLE tblname DROP COLUMN csmid

3、access创建一个有自增字段的表

1
CREATE TABLE tblname(id autoincrement, sname memo, ...)

4、sql server判断某个字段是否为空字符串

1
2
3
SELECT FROM tblname where fieldname!=''
或者
SELECT FROM tblname where fieldname<>''

5、取某一列的最大值

1
SELECT MAX(fieldname) from tblname;

6、查询一个字段有哪几种取值

1
SELECT DISTINCT fieldname From tblname;

6.1、统计一个字段不同取值的数量,即有多少种取值。

1
SELECT COUNT(DISTINCT fieldname) FROM tblname;

7、统计一个字段的几种取值,并按统计量降序排列。

1
2
3
4
SELECT fieldname, COUNT(*)
FROM tblname
GROUP BY fieldname
ORDER BY COUNT(*) DESC;

8、统计学号相同,班级不同的学生

1
2
3
4
5
6
7
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开始计数。

1
2
3
SELECT *
FROM students
ORDER BY INSTR(name'abc'DESC;

9.1、instr函数切记注意第一个和第四个参数。(可参考这里)

10、设置和去除主键,参考这里

1
2
3
4
设置主键
ALTER TABLE tblname ADD CONSTRAINT PrimaryKey Primary Key(主键字段名)
去除主键
ALTER TABLE tblname DROP CONSTRAINT PrimaryKey

11、ACCESS从数据库A复制表A到数据库B(新建了表B)。先连上数据库A,然后

1
SELECT INTO tableB [IN 'D:\B.mdb'FROM tableA

12、ACCESS从数据库A复制表A到数据库B(追加数据到表B)。先连上数据库A,然后

1
INSERT INTO tableB(field1, field2) [IN 'D:\B.mdb'SELECT field1, field2 FROM tableA

当表结构完全相同,合并表时可以这么写:

1
INSERT INTO tableB [IN 'D:\B.mdb'SELECT FROM tableA

13、MySQL查询时将日期加8小时。(DATE_ADD函数)

1
SELECT DATE_ADD(datefiled, INTERVAL 8 HOUR FROM tbl;

14、MySQL清除空白可查阅trim和replace函数。

15、根据关键字段去重,需用到临时表。这里主要是提供思路,需根据具体数据库得到具体sql语句。(参考这里

1
2
3
4
5
6
/*添加自增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(*)的结果。

1
num = rs->GetCollect(_variant_t((long)0))

17、ADO判断字段值是否为NULL。

1
vField.vt ?= VT_NULL

18、ADO访问ACCESS的3092错误的两种可能原因。

1
2
3
4
/* 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。

1
2
ALTER TABLE tableName
ALTER COLUMN [autoid] COUNTER (10, 1)

24、ACCESS得到数据库中所有表名。

1
2
SELECT NAME FROM MSysObjects
WHERE TYPE=1 AND FLAGS=0

25、ACCESS设置字段前导0示例。

1
2
UPDATE tblname
SET fieldname=FORMAT(fieldname, "00000");

26、mysql无重复插入。(参考这里

1)、无则插入,有则忽略。

1
2
3
4
/*某些版本的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)、无则插入,有则更新。

1
INSERT INTO tblname(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

27、mssql(SQLServer)无重复插入,注意下面两个1并不是walker抽风了。(参考这里那里

1
2
3
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)、单字段。

1
2
3
4
5
SELECT field FROM tableA
WHERE field NOT IN
(
SELECT field FROM tableB
)

2)、多字段。

1
2
3
4
5
6
SELECT FROM tableA
WHERE NOT EXISTS
(
SELECT FROM tableB 
WHERE tableA.field1=tableB.field1 AND tableA.field2=tableB.field2
)

29、Access跨文件连接查询。(Access 跨数据库查询和插入数据

1
2
3
4
5
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语句。(参考这里

1
2
3
4
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,正则)

1
SELECT FROM tableName WHERE fieldName REGEXP '^[0-9]' LIMIT 100;

32、MySQL利用存储过程执行循环语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
USE dbname;
 
DELIMITER $$  --定义结束符为$$
 
DROP PROCEDURE IF EXISTS test_insert;  --删除已有存储过程 
 
CREATE PROCEDURE test_insert()  --创建新的存储过程
    BEGIN  
    DECLARE INT DEFAULT 1;
    WHILE i<10  DO
        INSERT INTO tableName(idx) VALUES (i); 
        SET i=i+1;
    END WHILE;
     
END $$  --结束定义语句
 
DELIMITER ;  --恢复结束符为;
 
CALL test_insert();  --调用存储过程

33、SQLite数据多表联合update。(注意:这种方式会伤害无关字段!


N、....


*** walker * Updated 2016-08-05 ***

本文转自walker snapshot博客51CTO博客,原文链接http://blog.51cto.com/walkerqt/1149535如需转载请自行联系原作者


RQSLT

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章