Common Tasks in SQLite Manager

Create a New Database

To create a new database file, go to the Database drop down menu and select "Create Database".
A dialog box will pop up, enter the database name. A suffix will be appended to that name. The default file extension is ".sqlite " however you can specify that a different suffix be applied to new SQLite databases by going to the "Tools" drop down menu and selecting "Options". This will bring up the "SQLite Manager Options" dialog box. Select the "Main" tab and enter the new extension where it says "Default file extension of SQLite files"

Open an Existing Database

To open an existing SQLite database file, go to the Database drop down menu in the Menu bar and select "Connect Database".
The Select SQLite Database dialog box will pop up allowing you the browse for the file.
You also bring up this dialog box with the letter key combinations of Alt + D and then Ctrl + O.

Tables

Create A Table

  1. From the “View” drop down Menu at the top of the screen, select “Create Table” to open the Create Table dialog box.
  2. Enter tablename, column names and data types for columns.
    I would recommend against using the INTEGER (strict), TEXT(strict) or REAL(strict) data types for most applications.
  3. Check any column(s) which is part of the table's PRIMARY KEY. It is possible to add a Primary and or Foreign Key later using the Structure tab to edit the datatype associated with one of the columns in a table.
  4. Click the OK button in the Create Table dialog box.

Add a new record to a table

  1. Select the table from the Database Objects Panel.
  2. Go the the Browse & Search tab.
  3. Click on the Add New Record button or press the Alt + A key combination to bring up the data entry form for the table.
  4. Fill in the form, tab down to the OK button and press enter.

Edit records within a table

Double click on the record to edit to bring up the Edit Record dialog box.
Edit the record and confirm by clicking the OK button in the dialog box.

Note: Editing multiple records at the same time will require that a SQL UPDATE statement be entered into the Execute SQL tab.

Delete Record(s)

  1. Select the table from the Database Objects Panel.
  2. From the Browse & Search tab, highlight the record(s) to be deleted.
  3. Right click for the context menu and select delete or click on the Delete button at the top of the tab.

Task : Make a Copy of a table within a SQLite Database

  1. In the Database Object Panel, click on the name of the table to be copied to highlight it
  2. Right mouse click to bring up the table menu or click on the Table drop down menu on the menu bar. Select the "Copy Table" option.
  3. A dialog box will open, enter the new table name.
  4. By default rows stored in the old table will be copied along with the structure to the new table. Use the check box in the dialog if you only want to copy the structure.

Task : Copy a table from one SQLite Database into another

  1. Open one of the databases then select Attach Database from the Database menu.
  2. Use the dialog box to browse for the second database and enter an alias for it.
  3. Using the drop down box at the top of the navigation pane select the database where the table to be copied is located.
  4. Highlight that table and select the "Copy Table" option from the Table Menu.
  5. A dialog box will open, select the database that the table is to be copied to.
  6. Enter the new table name. Use the check box in the dialog if you only want to copy the table structure. Click OK.

Drop or Delete A Table

  1. Highlight the table name to delete in the left hand menu.
  2. Go to the table drop down menu, select the “Drop Table” option.
  3. A dialog box will pop up asking you to confirm the action.

Save the Results of a Select Query

You can save the results of a query by Copying rows from Query Result and pasting them into a spreadsheet program such as Excel or Gnumeric.

  1. Highlight the records that you want to copy.
  2. Right click your mouse to bring up the context menu.
  3. Select Copy Rows as CSV (MS Excel Compatible).
  4. Paste the records into the spreadsheet as Text

To include column names in the file, run a CREATE TABLE AS SELECT statement to generate a Table within the database and then export the table as a CSV file which can be opened by most Spreadsheet Programs.

Queries can also be retained within the Execute SQL tab of SQLite Manager by going to the Tools menu in the Menu bar and checking the Use Table for Extension Data item . After that it will only be necessary to click on the little diskette icon in the Execute SQL tab which will prompt you to enter a query name.

Views

A view is a saved SELECT query that is read only. Each time the VIEW is opened the query is run.

Task : Create a View

  1. Test the select statement that you wish to use in the Execute SQL tab to verify that the query will produce the results that you want.
  2. Copy the SELECT statement by highlighting it and pressing ctrl C or right click your mouse to get the copy/paste menu.
  3. From the “View” drop down Menu at the top of the screen, select “Create View” to open the Create View dialog box.
  4. Paste the sql query in the “Select statement” text area, enter a view name and click the OK button in the dialog box.
Task :Modify a View
  1. Select the view from the left menu and copy the original specification from the “Structure” tab.
  2. Paste the specification into "Enter SQL" text box of the Execute SQL tab.
  3. Remove "CREATE VIEW 'view_name' AS" leaving only the sql query.
  4. Edit and test the sql query until the desired results are obtained.
  5. Copy the sql query into the clipboard by highlighting it and pressing ctrl C or right click your mouse to get the copy/paste menu.
  6. From the structure tab, click on the modify button for the VIEW to be changes. You should be the Modify View dialog box pop up.
  7. Paste the sql query in the “Select statement” text area of the dialog box click the Ok button.

SQLite Manager Menu Bar Drop Down Listings

Database Table Index View Trigger Tools Help
______________________ ____________ ____________ ____________ ____________ _______________________ ______________
New Database Create Table Create Index Create View Create Trigger Options Report A Problem
New In-Memory Database Drop Table Drop Index Drop View Drop Trigger Shared Pager Cache Frequently Asked Questions
Connect Database Empty Table Reindex Index Rename View Rename Trigger Use Table for Extension Data SQLite Homepage
Close Database Rename Table Modify View Open On-Connect SQL Tab SQLite Syntax Help
Recently Used Modify Table Export View Extension Homepage
Attach Database Copy Table About SQLite Manager
Detach Database Export Table
Copy Database Reindex Table
Compact Database
Analyze Database
Check Integrity
Export All Tables
Export Database
Export Database Structure
Import
Refresh Shift+F5
Reconnect
Exit


Colin Riley --Updated January 2011--