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子句，可参考这里。
21、ACCESS单引号的转义字符是单引号，也就是说如果字段值中出现单引号，需用两个单引号表示。（可参见这里）
21.1、另外注意，字符串中若有nul字符，也会插入失败，这种情况比较难以排查。因为看起来的状况是，拷贝到access文件里面里能执行，而代码里不能执行。
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
i
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子句，可参考这里。
21、ACCESS单引号的转义字符是单引号，也就是说如果字段值中出现单引号，需用两个单引号表示。（可参见这里）
21.1、另外注意，字符串中若有nul字符，也会插入失败，这种情况比较难以排查。因为看起来的状况是，拷贝到access文件里面里能执行，而代码里不能执行。
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
i
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
