Working with SQLiteStudio

Views

One convenient feature that relational databases provide is Views . You can use them to automate extensive, cross-table queries and evaluations such as totals or counts without having to retype them every time. Instead, you access the output product via the view, as if it were a table.

Rather than clicking your way around, you can use an appropriate SQL query to create views in SQLiteStudio (note that, if you do not possess SQL knowledge, you might get stuck at this point). In the following example, we create a phone list that lists the corresponding connection data for each person.

First, right-click on Views in the left column of the object and select the Create a View item from the context menu that appears. Next, enter a name for the view, and then enter the corresponding SQL statement in the Editor window (Figure 10).

Figure 10: You create a view using an appropriate SQL statement.

The SQL command in the example consists of listing the corresponding table columns, enumerating the matching tables, a where clause with the condition, and a sorting instruction. You enter the columns of the table in the form <table>.<column> . After the from keyword, list the tables used, separated by commas.

The where clause in the example contains two fields from different tables, which must have the same content (= ). For numerical comparisons you can use < (lesser) and > (greater) in your other projects. For a sorted list, add the statement order by with the corresponding table columns.

Listing 1 shows the complete SQL statement with syntax highlighting such as offered by SQLiteStudio. In Figure 11, you can see the results, which you can access in the Data tab, just like a table.

Figure 11: You can work with the result of the views just like any other table.

select staff.name, staff.name, connections.type, connections.info from staff, connections where staff.snum = connections.snum order by staff.name, staff.name, connections.type

To use the view outside of SQLiteStudio in a conventional SQL client, you need to add a statement to create the view before the select (create view directory as… ). You also need to close the SQL statement with a semicolon (; ).

Data Import

To add new values to the database, for example, for staff , you do not need to type in the corresponding data line by line in SQLiteStudio. For a more convenient approach, enter the data in a CSV file up front and then import into SQLiteStudio – this works fine with just a few mouse clicks.

For our example, you first create the file from Listing 2 using any text editor and save it as staff.csv . Notice the empty field at the beginning of each record: that's for the snum field. Then, in the menu of SQLiteStudio, select Tools | Import or click the Import icon (the four arrows moving toward one another).

NULL,Clark,Kent,24/12/1980,
NULL,Bruce,Banner,11/11/1981,

In the first dialog of the wizard that then opens, select the database and table for import, in this case test.staff . When you press Next > , you will see the following window (Figure 12), in which the source type CSV is the default.

Figure 12: The Import Wizard allows for detailed information on the data to be parsed.

Now specify the input file by clicking the small folder icon next to Input file . In Text encoding , select the appropriate character set, as well as in the Data source options and Field separator . Some CSV files also have column headers. In this case, check the box to ignore the header.

After you click Finish , SQLiteStudio then imports the values from the CSV file. Now looking at the staff table, you will see the new entries.