sql 语句备忘(qbit)

qbit
结构化查询语言(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 注意:这种方式会伤害无关字段!
本文出自 qbit snap
阅读 1k

qbit snap
开箱即用,拿走不谢。
200 声望
262 粉丝
0 条评论
你知道吗?

200 声望
262 粉丝
宣传栏