Working with SQLiteStudio

Manual Data Entry

To enter or edit data, click on Data . You can choose between Grid view (Figure 5) and Form view .

Figure 5: Data entry in Grid view.

This window also contains a toolbar with the following functions (from left to right) refresh view , insert a new row (plus sign), delete row (minus sign),commit changes (checkmark), and rollback changes (X).

Do not click on a column with AutoIncrement when entering data – in our case, do not click on snum . The field must contain the default value of NULL in italics; otherwise, the input will not work!

The following examples relate to a small database with a total of three tables for managing phone and fax numbers, email addresses, and so on. To do this, we need to create the tables, types , and links . The information necessary for filling is located:

for staff in Table 1,
for types in Figure 6 and Table 2, and
for connections in Figure 7, Figure 8, and Table 3.

Figure 6: Structure of types.

Figure 7: Structure of table connections.

Figure 8: Creating connections.type with a foreign key.

Table 2

Types

typelong	typeshort
Phone landline	t
SFTP account	s
Mobile	m
Fax	f
Email	e

Table 3

Connections

snum	type	info
2	m	01231234567
3	t	0987 654321
2	f	0864 975321
3	e	mail@example.tld
1	m	0137 246890
1	t	09751 23468
The content of the field order is created automatically.

In this example, foreign keys are used for the type of connection data in connections.type . To avoid the need to continually write out the words Phone, Fax, or Cell in the connection data, there is a shortcut for each category.

To make these shortcuts binding for the entries in connections.type , use the types.typeshort as a foreign key. Figure 8 shows how to create the field. As the Constraint , select the Foreign Key and then click Configure in the same line. This opens another dialog in which you select the table (types ) and the column typeshort (primary key of types ). In the Reactions section, you can make some additional fine adjustments if needed.

You the same with connections.snum to prevent orphaned entries in staff .

Next, populate the tables with sample data. When filling out the connection.type , SQLiteStudio suggests using the matching entries for the fields with the foreign keys if you double-click the box and select the arrow that then appears.

Sorting, Filtering, Searching

In the Grid view of a table, you can double-click on the header of the desired column to sort the data. To search, first select how the search will take place. To do this, press the small arrow next to the funnel symbol next to the search field in the Grid view toolbar. The following methods are available:

Filter by text : SQLiteStudio searches all table fields for the search term. A "T" will appear in the filter icon.
Filter by Regular Expression : This setting supports searching by pattern. An "R" appears in the filter icon.
Filter by SQL expression : SQLiteStudio expects the value to look for in a column. An "S" in the filter icon reflects this setting.

Figure 9 shows a search for all table entries in connections that include a cellphone number. The search was carried out in SQL mode. Clicking on the round X symbol in the search window clears the display of search results.