Common Tasks in SQLite Manager

Create a New Database

1. To create a new database file, go to the Database drop down menu and select "Create Database".
2. A dialog box will pop up, enter the database name. A suffix will be appended to the file 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,

1. Go to the Database drop down menu in the Menu bar and select "Connect Database".
2. 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 combination 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.

Be cautious about using the INTEGER (strict), TEXT(strict) or REAL(strict) data types.

1. Within the dialog box, check any column(s) which will be the table's PRIMARY KEY. It is possible to add a PRIMARY KEY later using the Structure tab but it is not recommended.
2. 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 In a Table

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

Note: Editing multiple rows at the same time will require the use of a SQL UPDATE statement within the Execute SQL tab.

Delete Record(s) From A Table

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.

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.

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.

===Storing SQL statements SELECT Queries and other SQL statements can be stored in the database and rerun at a later time from the Execute SQL tab of SQLite Manager. To enable this functionality, go to the Tools menu in the Menu bar and check 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. Views can be used in Select queries in the same manner as a table.

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.

SQLite Manager Menu Bar Drop Down Listings