Structured Query Language (SQL) is a special purpose programming language. It is a database query and programming language for accessing data, querying, updating and managing relational database systems.

• Add a self-increasing ID field

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

• access creates a table with self-increasing fields

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

• Delete a field

ALTER TABLE tblname DROP COLUMN csmid

• sql server determines whether a field is an empty string

SELECT * FROM tblname where fieldname!=''
# OR
SELECT * FROM tblname where fieldname<>''

• Take the maximum of a column

SELECT MAX(fieldname) from tblname;

• Query what values a field has

SELECT DISTINCT fieldname From tblname;

• Statistics of the number of different values of a field, that is, how many values.

SELECT COUNT(DISTINCT fieldname) FROM tblname;

• Statistically count several values of a field and arrange them in descending order.

SELECT fieldname, COUNT(*)
FROM tblname
GROUP BY fieldname
ORDER BY COUNT(*) DESC;

• Students of the same statistics number and different classes

SELECT sno, COUNT(*) FROM
(
SELECT sno, sclass FROM students
GROUP BY sno, sclass
)
GROUP BY SNO
ORDER BY COUNT(*) DESC

• ACCESS, in the students table, puts the field with abc in the name field in front. Other databases may need to change INSTR to CHARINDEX. Instr counts in access from 1, not from 0.

SELECT *
FROM students
ORDER BY INSTR(name, 'abc') DESC;

• The instr function of Access is mindful of the first and fourth parameters. (Reference) Here)
• Setting and removing primary keys, reference Here.

# Set Primary Key
ALTER TABLE tblname ADD CONSTRAINT PrimaryKey Primary Key(Primary key field name)
# Remove primary keys
ALTER TABLE tblname DROP CONSTRAINT PrimaryKey

• ACCESS copies table A from database A to database B (table B is newly created). First connect to database A, then

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

• ACCESS copies table A from database A to database B (additional data to table B). First connect to database A, then

INSERT INTO tableB(field1, field2) [IN 'D:\B.mdb'] SELECT field1, field2 FROM tableA
# When the table structure is identical, merge tables can be written as follows:
INSERT INTO tableB [IN 'D:\B.mdb'] SELECT * FROM tableA

• MySQL queries add 8 hours to the date. ( DATE_ADD Function)

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

• MySQL clears the blanks by referring to trim and replace functions.
• Temporary tables are needed to de-duplicate according to key fields. Here is mainly to provide ideas, according to the specific database to get specific sql statements. (Refer here)

# Add self-increasing ID
select identity(int,1,1) as autoID, * into tmpTable from tableName
# Select the self-increasing ID with the minimum median value of duplicate records
select min(autoID) as autoID into tmpTable2 from tmpTable group by keyField1, keyField2
# Get the final result
select * from tmpTable where autoID in(select autoID from tmpTable2)

• ADO gets the result of SELECT COUNT(*).

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

• ADO determines whether the field value is NULL.

vField.vt ?= VT_NULL

• Two possible reasons for ADO's 3092 error in accessing ACCESS.

# date should be bracketed for reservations 
SELECT date FROM tableName
# This middle bracket should not be affected. 
SELECT COUNT(*) FROM tableName WHERE field<>'[D2037'

• ACCESS interacts with other external data - IN clause for reference Here.
• Visiting Access Database with ADO under VC++
• The escaped character of ACCESS single quotation marks is a single quotation mark, that is to say, if there is a single quotation mark in the field value, two single quotation marks are needed. (Reference) Here)

Also note that if there are nul characters in the string, insertion fails, which is more difficult to check. Because it looks like the situation is that copy to access file can be executed, and code can not be executed.

• The autoid field of the Settings table increases from 10 to 1 each time.

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

• ACCESS gets all table names in the database.

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

• ACCESS Settings Field Leader 0 Example

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

• mysql has no repetitive insertion. (Reference) Here)

1) No insertion, no neglect.

/*Some versions of mysql have a warning*/
INSERT IGNORE INTO tblname(a,b,c) VALUES (1,2,3);    
/*No warning*/
INSERT INTO tblname(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c;    

2) No insertion, no update.

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

• mssql (SQL Server) has no repetitive insertion. Note that the following two 1 are not walker pumps. (Refer here and there)

INSERT INTO  tableName(keyA, keyB) SELECT valA, valB
 WHERE NOT EXISTS 
 (SELECT TOP 1 1 FROM tableName WHERE keyA=valA AND keyB=valB);

28. Subtract the two tables. ( in,not in,exists,not exists)
1) Single field.

SELECT field FROM tableA
WHERE field NOT IN
(
SELECT field FROM tableB
)

2) Multi-field.

SELECT * FROM tableA
WHERE NOT EXISTS
(
SELECT * FROM tableB 
WHERE tableA.field1=tableB.field1 AND tableA.field2=tableB.field2
)

• Access queries across file connections. ( Access queries and inserts data across databases)

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 data can be imported into SQL Server using the following Transact-SQL statements. (Reference) Here)

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);

• Find data that starts with numbers. (MySQL, regular)

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

• MySQL uses stored procedures to execute circular statements.

USE dbname;

DELIMITER $$  # Define the terminator as$$

DROP PROCEDURE IF EXISTS test_insert;  # Delete existing stored procedures 

CREATE PROCEDURE test_insert()  # Create a new stored procedure
    BEGIN  
    DECLARE i INT DEFAULT 1;
    WHILE i<10  DO
        INSERT INTO tableName(idx) VALUES (i); 
        SET i=i+1;
    END WHILE;
    
END $$  # End Definition Statement

DELIMITER ;  # Recovery terminator is;

CALL test_insert();  # Calling stored procedures

• SQLite Data Multi-table Joint update (Note: This will hurt irrelevant fields!)

This article is from walker snapshot