乐趣区

sql-语句备忘

jiezi

结构化查询语言 (Structured Query Language) 简称 SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

  • 添加一个自增 ID 字段
ALTER TABLE tblname ADD COLUMN csmid COUNTER (0, 1)
  • access 创建一个有自增字段的表
CREATE TABLE tblname(id autoincrement, sname memo, ...)
  • 删除一个字段
ALTER TABLE tblname DROP COLUMN csmid
  • sql server 判断某个字段是否为空字符串
SELECT * FROM tblname where fieldname!=''
# OR
SELECT * FROM tblname where fieldname<>''
  • 取某一列的最大值
SELECT MAX(fieldname) from tblname;
  • 查询一个字段有哪几种取值
SELECT DISTINCT fieldname From tblname;
  • 统计一个字段不同取值的数量，即有多少种取值。
SELECT COUNT(DISTINCT fieldname) FROM tblname;
  • 统计一个字段的几种取值，并按统计量降序排列。
SELECT fieldname, COUNT(*)
FROM tblname
GROUP BY fieldname
ORDER BY COUNT(*) DESC;
  • 统计学号相同，班级不同的学生
SELECT sno, COUNT(*) FROM
(
SELECT sno, sclass FROM students
GROUP BY sno, sclass
)
GROUP BY SNO
ORDER BY COUNT(*) DESC
  • ACCESS，在 students 表中将 name 字段中含有 abc 的字段放在前面。其他数据库可能要将 INSTR 换为 CHARINDEX。在 access 中 instr 从 1 开始计数，而不是从 0 开始计数。
SELECT *
FROM students
ORDER BY INSTR(name, 'abc') DESC;
  • Access 的 instr 函数切记注意第一个和第四个参数。(可参考这里)
  • 设置和去除主键，参考这里。
# 设置主键
ALTER TABLE tblname ADD CONSTRAINT PrimaryKey Primary Key(主键字段名)
# 去除主键
ALTER TABLE tblname DROP CONSTRAINT PrimaryKey
  • ACCESS 从数据库 A 复制表 A 到数据库 B（新建了表 B）。先连上数据库 A，然后
SELECT * INTO tableB [IN 'D:\B.mdb'] FROM tableA
  • 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
  • MySQL 查询时将日期加 8 小时。(DATE_ADD 函数)
SELECT DATE_ADD(datefiled, INTERVAL 8 HOUR) FROM tbl;
  • MySQL 清除空白可查阅 trim 和 replace 函数。
  • 根据关键字段去重，需用到临时表。这里主要是提供思路，需根据具体数据库得到具体 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)
  • ADO 获取 SELECT COUNT(*)的结果。
num = rs->GetCollect(_variant_t((long)0))
  • ADO 判断字段值是否为 NULL。
vField.vt ?= VT_NULL
  • ADO 访问 ACCESS 的 3092 错误的两种可能原因。
# date 为保留字，应该加中括号 
SELECT date FROM tableName
# 这个中括号按说不应该影响的 
SELECT COUNT(*) FROM tableName WHERE field<>'[D2037'
  • ACCESS 与外部其它数据交互 – IN 子句，可参考这里。
  • VC++ 下使用 ADO 访问 Access 数据库完整篇
  • ACCESS 单引号的转义字符是单引号，也就是说如果字段值中出现单引号，需用两个单引号表示。（可参考这里）

另外注意，字符串中若有 nul 字符，也会插入失败，这种情况比较难以排查。因为看起来的状况是，拷贝到 access 文件里面里能执行，而代码里不能执行。

  • 设置表的 autoid 字段从 10 开始自增，每次增 1。
ALTER TABLE tableName
ALTER COLUMN [autoid] COUNTER (10, 1)
  • ACCESS 得到数据库中所有表名。
SELECT NAME FROM MSysObjects
WHERE TYPE=1 AND FLAGS=0
  • ACCESS 设置字段前导 0 示例
UPDATE tblname
SET fieldname=FORMAT(fieldname, "00000");
  • 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;
  • 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
)
  • 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;
  • 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);
  • 查找以数字打头的数据。（MySQL，正则）
SELECT * FROM tableName WHERE fieldName REGEXP '^[0-9]' LIMIT 100;
  • MySQL 利用存储过程执行循环语句。
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();  # 调用存储过程
  • SQLite 数据多表联合 update（ 注意：这种方式会伤害无关字段！）

本文出自 walker snapshot

退出移动版