Mitsubishi Electric MELSEC iQ-R-R04CPU - Data Operation

998
APPX
Appendix 14 List of Available SQL Commands for CPU Module Database Access Function
Data Operation
INSERT
This command adds a record to the specified table in the database.
*1 Input null not to store the field value.
*2 When the field value is a character string, enclose it in single quotation marks (').
■Application example
• To add records to all fields
INSERT INTO "table1" VALUES (11, null, 'abc');
• To add records to the specified fields
INSERT INTO "table1" ("fld1", "fld3") VALUES (12, 'efg');
SELECT
This command outputs a value of the specified record from the specified table in the database.
■Option 1
■Option 2
*1
*1 Multiple options can be used.
■Application example
• To output all record values in table1 (all fields of table1)
SELECT * FROM table1;
• To output all record values in table1 (fld1 and fld2 in table1)
SELECT "fld1", "fld2" FROM table1;
• To output all records excluding the overlapped records in fld1 of table1
SELECT DISTINCT "fld1" FROM table1;
Syntax
*1*2
• To add records to all fields
INSERT INTO [table name] VALUES (field 1 value, field 2 value, , field X value);
• To add records to the specified fields
INSERT INTO [table name] (field specification 1, field specification 2, , field specification X) VALUES (field 1 value, field 2 value, , field X value);
Syntax
• To output all values
SELECT [output target field or numeric expression] FROM [table name] [option];
• To output a value excluding overlapped values
SELECT DISTINCT [output target field or numeric expression] FROM [table name] [option 1] [option 2];
Item Description Syntax
INNER JOIN Performs inner join. (Records that exist in the specified both
tables are output.)
SELECT [output target field or numeric expression] FROM [table name
1] INNER JOIN [table name 2] ON [combining condition];
LEFT JOIN Performs outer join. (All records that exist in [table name 1]
are output.)
SELECT [output target field or numeric expression] FROM [table name
1] LEFT JOIN [table name 2] ON [combining condition];
RIGHT JOIN Performs outer join. (All records that exist in [table name 2]
are output.)
SELECT [output target field or numeric expression] FROM [table name
1] RIGHT JOIN [table name 2] ON [combining condition];
FULL JOIN Performs outer join. (All records that exist in both of specified
tables are output.)
SELECT [output target field or numeric expression] FROM [table name
1] FULL JOIN [table name 2] ON [combining condition];
Item Description
WHERE Specifies the condition to obtain a specific record. ( Page 1000 WHERE).
GROUP BY Specifies the grouping condition of the record to be obtained. ( Page 1000 GROUP BY).
HAVING Specifies the condition to narrow down a search using the result of the aggregate function. ( Page 1000 HAVING).
ORDER BY Specifies the sorting condition of the record to be obtained. ( Page 1001 ORDER BY).