SQL in Ignition

For information about databases and how they are used in Ignition, see the Databases in Ignition section.

What is SQL?

SQL or Structured Query Language is a standard computer language for accessing and manipulating database systems. SQL statements are used to create, maintain, and query relational databases.

Examples

SELECT Firstname FROM Contacts WHERE Lastname = 'Smith'
INSERT INTO Contacts VALUES ('Joe', 'Smith')
UPDATE Contacts SET Lastname = 'Howard' WHERE ID = 1
DELETE FROM Contacts WHERE Lastname = 'Smith'

Tables

The foundation of every database system is a table. Every database consists of one or more tables, which store the database’s data/information. Each table is identified by a name, for example, Customers or Orders, and consists of columns and rows.

The database table columns have their own unique names and have a pre-defined data types. Table columns can have various attributes defining the column functionality (such as the primary key, index, default value, and so on).

While table columns describe the data types, the table rows contain the actual data for the columns.

images/download/thumbnails/1704273/image2015-6-8_11_23_22.png

Primary Key

A primary key is a way to uniquely identify each row in a table. Every table must have a primary key. A primary key comprises of a single column or set of columns.
No two distinct rows in a table can have the same value (or combination of values) in those columns.

The Primary Key can be:

  • Auto-incrementing

  • Statically defined

Index

Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book.

Indexes are extremely important when querying large sets of data. You should create an index for each set of columns you specify in a where clause. For example, you should add an index on the timestamp
column of a historical table when querying the table by a start and end date.

Foreign Key

A foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.

The columns in the referencing table must be the primary key in the referenced table.

Examples

Supplier ( SupplierNumber, Name, Address, Type )
Invoices ( InvoiceNumber, SupplierNumber, Text )

NULL Value

NULL is a special marker used in SQL to indicate that a data value does not exist in the database.

SELECT Command

The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set.

Examples

SELECT * FROM Customers
SELECT Name FROM Customers
SELECT Name, Address FROM Customers

UPDATE Command

The UPDATE statement is used to update records in a table.

Examples

UPDATE Customers SET Name = 'Inductive Automation'
UPDATE Customers SET Address = '2110 21st Street' WHERE ID = 1

INSERT Command

The INSERT statement is used to insert a new row in a table.

Examples

INSERT INTO Customers (Name, Address, City, State, Zip, Country, Phone)
VALUES ('Inductive Automation', '2110 21st Street', 'Sacramento', 'CA',
'95818', NULL, '800-266-7798')
INSERT INTO Customers SELECT * FROM OtherCustomers

DELETE Command

The DELETE statement is used to delete records in a table.

Examples

DELETE FROM Customers
DELETE FROM Customers WHERE Name = 'Inductive Automation'

WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion. Operators Allowed in the WHERE Clause are as follows:

= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns

The AND & OR operators are used to filter records based on more than one condition.

Examples

SELECT * FROM Customers WHERE State = 'CA'
SELECT Name FROM Customers WHERE Address LIKE '%St%' AND State = 'CA'

ORDER BY Clause

The ORDER BY keyword is used to sort the result-set by a specified column. The ORDER BY keyword sort the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword.

Examples

SELECT * FROM Customers ORDER BY Name ASC
SELECT * FROM Customers ORDER BY State ASC, Name DESC

Joins

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Tables in a database are often related to each other with keys. A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

Example

SELECT * FROM Contacts ct JOIN Customers cn ON cn.ID = ct.CustomerID

GROUP BY Clause

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Example

SELECT SUM(Duration) FROM Downtime GROUP BY CauseID

In this section ...