SQLite visual management tool (SQLite Expert Pro)

SQLite visual management tool allows users to perform operations such as creation, editing, copying, and extraction on the SQLite server. SQLite Expert Professional is a visual database management tool that allows users to perform operations such as creating, editing, copying, and extracting on the SQLite server. SQLite Expert supports all SQLite features of the graphical interface. It includes a visual query builder, a SQL editor with syntax highlighting and code auto-completion, powerful table and view design and import and export functions.

Supported platforms: windows 2000, XP, VISTA, 7. 

Main features:

-Visual SQL query builder
-Build complex SQL queries easily.
-Formatted SQL query text layout.
-A powerful means of SQL query parsing and analysis.
- Advanced SQL editor with syntax highlighting and code completion.

Powerful reorganization function:
- Visual editor for table columns, indexes, foreign keys, triggers, unique and check constraints.
- Reorganize tables of any complexity without losing data.
- Any reorganization operations are wrapped in a nested transaction and rolled back if any errors occur while the changes are being applied.

Import and export data:
-Import data from ADO data sources, CSV files, SQL scripts or SQLite.
-Export data to CSV files, SQL scripts, Excel or SQLite.
- Copy tables between SQLite databases using drag and drop operations.
- Export to Excel via data from clipboard.

Data editing:
- Edit data in the grid using powerful in-place editing.
-The image editor currently supports JPEG, PNG, BMP, GIF and ICO image formats.
-Visualize and modify BLOB fields using the integrated hex editor.

Install and use SQLite Expert

Download SQLiteExpert software

Installation is very simple, just keep going to the next step

After install, the installation is successful

Open the software

Open sqlite database

After confirmation, open the sqlite data.

Now you need to use MMDS to export the sqlite data file in eclipse

OK after opening

In data/data/your project name/database file

After finding the database file, export it to local and then open it with a tool

Create a table using SQLite expert

Use SQLite expert to create a table and let it generate the required sql statements for us

In the following example, we create a table to store user names and passwords; the database name is "users" and the table name is "user_accounts". There are three columns: row_id INTEGER auto-increment type primary key, usernameTEXT type used to save user names. passwordTEXT type, used to save user passwords;

Create table

In order to create a table, you must first have a database file. Click "File"->""New Database and then select the path of the file. Click OK to create a database file. After creating the database, create the table. Right-click the database file name and select " New Table" as shown below:

At this time we have entered the "design state", fill in the table name (user_accounts), click "Add", fill in "row_id" in Name, and select Integer for Type, as shown in the figure below:

Because the row_id column is special and is the primary key of our index, we also need to click "Index", and then you will see the following picture:

The Available Fields on the left contains the row_id we just created. Click "Add" to add the row_id to the Index Fields on the right. At this time, the above Primary and AutoIncrement will become available. Check Primary and AutoIncrement and click OK;

Go back to Field and create two other columns, the username type is TEXT, and the password type is also TEXT, but there is no need to create an Index; after completion, click Apply, so that we can use SQLite expert to create a table, click DDL, we can See that SQLite expert has generated the SQL statements required to generate this table for us:

When you need code to generate a table in Android, just copy this code and that's it.

Add user

Click SQL and execute the following SQL statement to add a user to the table:

INSERT INTO user_accounts(row_id,username,password) VALUES(001,'John','abcdef');

Click Data and you will find that a user named John has been added to the database. For practice, let's add two more users, David and Sarah

INSERT INTO user_accounts(row_id,username,password) VALUES(002,'David','123456');INSERT INTO user_accounts(row_id,username,password) VALUES(003,'Sarah','00000000');

Delete user

Execute the following statement to delete user David:

DELETE FROM user_accounts WHERE username = 'David';

Change password

Execute the following statement to change Sarah’s password:

update user_accounts SET password='666666' WHERE username = 'Sarah';

View all user information

You can use the following statement to view the information of all users in the table:

SELECT * FROMM user_accounts;

Generally speaking, the select * statement is only used during testing and is not recommended in official code.

View the contents of a specified column

Execute the following statement to view the usernames and passwords of all users:

SELECT username,password FROM user_accounts;

At this time, I found that the row_id column was not displayed.

Query information on specific conditions

SQL can perform precise searches based on given query conditions. For example, we only need John's password. You can use statements like this

SELECT password FROM user_accounts WHERE username = 'John';

The following flow chart comes from SQLite official documentation

Create Table Statement

column-def

type-name

column-constraint

table-constraint

foriegn-key-clause

Insert Statement

Delete Statement

qualified-table-name

Update Statement

qualified-table-name

Select Statement

select-core

result-column

join-source

single-source

join-op

join-constraint

ordering-term

compound-operator

Other features:

-Full Unicode support.
-Supports additional databases.
-Supports encrypted database.
- Scripting support for Lua and Pascal.

Latest features:

- View all features and collations installed for every SQLite extension!
-New in 3.0: Customize the look and feel of the application using skins.
-New in 3.0: Send any content of the grid to the printer or export it to a PDF file.