Как установить sqlitestudio на windows 7

Table of Contents

• Installation and running
  • Supported platforms
  • Installing binary package
  • Installing from source code
• Basics
  • Creating new database
    • Permanent vs transient database
  • Using existing database
  • Managing database list
    • Editing database details
    • Organizing database list
    • Deleting database from the list
  • Executing SQL queries
    • SQL Editor window
    • Buttons on toolbar
    • Typing SQL queries
    • Code completion
    • Query execution history
    • Built-in SQL functions
    • Editing the data
      • Value editor dialog
    • Transparent database attaching
  • Creating and editing tables
    • Column dialog
      • Column constraints
    • Table constraints
    • WITHOUT ROWID tables
    • Virtual tables
  • Creating and editing indexes
    • Partial indexes
  • Creating and editing triggers
    • Trigger for a View
  • Creating and editing views
  • Moving and copying objects between databases
  • Exporting schema and data
  • Importing data
  • Useful keyboard shortcuts
  • Configuration dialog
    • Customizing data type editors
  • Command line options
  • SQLiteStudio configuration file
  • SQLiteStudio updates
    • Automatic
    • Manual
• Advanced usage
  • Custom SQL functions
  • Code snippets
  • Custom collations
  • SQLite extensions
  • Populating tables
  • DDL history
  • Converting SQLite database version
• Command line interface (CLI)
• Troubleshooting
  • SQLiteStudio crashed! What do I do?
  • I have saved binary data into SQLite 2 database, but now it’s damaged, empty or truncated!
  • I’m using Fcitx input method, but it doesn’t work in SQLiteStudio
  • See also

Installation and running

Supported platforms

Installing binary package

Installing from source code

See Compiling application from sources.

Basics

Creating new database

You can create new database in three ways:

1. Click on toolbar button:
   
2. Click on main menu entry:
   
3. Right-click on database list and pick entry from context menu:
   

Then the database dialog will appear:

Click on the browse button:

You will see filesystem browsing dialog (this may look different on various operating systems):

In this dialog go to directory where you want to create your database and type in the file name for your database (on the image above it’s «test.db»). Click «Save». Now the database dialog has file name and database name filled in. You can change the name to whatever you want (in which case you have to disable the checkbox on the right hand side), or just leave it as it is.

The database name is just symbolic name, an alias used by SQLiteStudio to present database in the list. You can pick any name you want, it just has be unique in context of databases already added to SQLiteStudio.

You can press «Test connection» to make sure that your database file can be created in specified localisation (in regards of directory permissions, free space, etc). If everything is okay, than you will see green icon next to it:

If there was any problem, the red icon will appear.

You can now press «Ok» to create the database. You will see it in the databases list on the left:

Double-click to open it:

Permanent vs transient database

You may have noticed the «Permanent» check in the database dialog:

When this option is checked, then the database is added permanently to SQLiteStudio. This means that when you close SQLiteStudio and start it again, the database will remain on the list.

If this option is not checked, then the databases will disappear from the list after SQLiteStudio was restarted.

Using existing database

Adding an existing database to the list is very similar to creating new database.

Repeat steps from above, util you see database dialog:

Then you click (just like before) on browse button:

In the file browsing dialog pick an existing database file:

Then click «Save» and «Ok» in next dialog and your database will be added to the list. You can double-click on it to open it:

Alternatively you can use drag&drop mechanism: drag database file from your file manager and drop it onto the database list, then confirm the database dialog.

Managing database list

Editing database details

You can edit database details at any moment. Just right-click on it and pick «Edit database» from context menu. The standard database dialog will appear.

Changing database’s name is allowed as far, as the new name is unique across all databases being currently on the list.

The database name is just symbolic name, an alias used by SQLiteStudio to present database in the list.

When changing database file path, it will mean that under the same name you will access the new database file. It will not copy nor move the old database file to new location.

When editing database details it’s not possible to change it’s type (SQLite2, SQLite3, or any other), because this is allowed only when creating new datababase. If you want to convert one SQLite version into another, use database conversion dialog.

Organizing database list

You can move databases up and down with mouse «drag & drop». You can create virtual «directories» where you can group databases. It helps maintaining big number of databases on the list.

To create a group, right-click on the databases group and pick «Grouping» → «Create group». Then you can drag & drop databases into that group:

Groups can be nested.

If you right-click on the database and pick «Create group», then the clicked database will be automatically moved into the group after the group was created.

Deleting database from the list

To delete database from the list, select the database and either:

• right-click on it and pick «Remove database»,
• or press «Delete» from the keyboard.

You will be asked for confirmation, before the database is actually removed from the list.

Deleting database from the list does not delete database file! It only removes it from the list of databases in SQLiteStudio. There’s no way to delete actual database file using SQLiteStudio. This is for safety reason — if you really need to delete the database file, do it using your operatins system utils (file manager, command line, …).

Executing SQL queries

SQL Editor window

To execute any SQL queries on SQLite database you have to open SQL Editor window:

You can find it also in the «Tools» menu:

It’s also available under keyboard shortcut «Alt+e» (by default, can be changed).

The SQL editor has a upper text edit for entering SQL queries and lower view for displaying results of the query:

If you prefer to have query edit field on one page and results on another page, you can switch it very quickly from toolbar:

And in the result you get 3 tabs in the editor, instead of 2:

Buttons on toolbar

The list may differ if any plugin adds its own buttons to the toolbar.

Typing SQL queries

When you type SQL query in the editor, it will highlight SQL syntax, but also if you type the correct name of any object (that is existing table, index, trigger, view, database or column name), the name gets highlighted with blue color (by default, it’s configurable):

If you use incorrect SQL syntax, the problematic part will be underlined with wavy line:

To execute the query, use toolbar button , or keyboard shortcut «F9»
(the shortcut is configurable).

Error handling is done by static syntax analysis, therefore there’s a chance that some errors are detected by mistake. If you think that the marked error is not really the error, you can execute query and SQLiteStudio will not analyze it. Instead it will put query directly to SQLite driver to execute it. If it was indeed correct, then it will return data, but for such cases SQLiteStudio is unable to extract metadata from query and results will not be editable. Only queries that were not marked with error marker will return full metadata and results will be editable. This limitation also affects transparent database attaching.

Even SQLiteStudio claims that the syntax is incorrect, you can still try to execute the query:

In this case, the query was invalid, obviously.

Now, let’s get back to correct query (remove the «qqq» from it) and also switch the results presentation mode, so results are displayed below query, not in separate tab:

Execute the query:

The results appeared in the view below the query and the proper execution status message appeared in the bottom status area.

You can type multiple queries, one after another, just separate them with semicolon. In this case SQLiteStudio will execute all queries types in the editor, but will present only results from the last query:

If you want to execute only one query (even more queries are typed in), select that query and then press «Execute»:

The behaviour of executing all queries typed in is a default, but you can change it, so only query which the input cursor is currently at is executed. To do so, open configuration dialog (button _on_main_toolbar), go to «General» page and enable this option:

From now on whenever you hit «Execute» in SQL Editor, only one query will be executed, the one under input cursor:

In this mode, after you execute current query, that entire query will be selected, so you can be sure what query exactly was executed:

Code completion

SQLiteStudio provides code assistant, so it can help you with the SQL syntax. When you hit Ctrl+Space (it’s a configurable shortcut), it will show you all possible values for the current cursor position. Let say you typed «SELECT c» and hit Ctrl+Space:

You can see it shows only valid entries that start with the letter «c». Let’s pick «ccc«, then ask the assistant about next suggestions:

First position (with a bullet icon) is a pseudo value, which tells you that you can put an alias name for table or column here. You typed «ccc» before, which is a column name, so here you could type an alias name for it. We won’t do that. Instead we pick «FROM» and add «tab where» and ask the assistant again:

The assistant proposes several columns which you can use in your «where» clause, but notice, that columns from «tab» table are listed at the begining, as you use that table in the query already. This is how the assistant tries to be smart with proposing values — when they are more likely to be used in the context, it will list them at the top.

The assistant is triggered manually by the keyboard shortcut mentioned above, but it also triggers automatically when you type «name.«, where the name is name of existing database or table. In following example I didn’t use keyboard shortcut, this just appeared after I types «wikidb.«:

There’s a small problem with the assistant on Linux with KWin composition enabled, which causes the assistant to show up with a small delay (about half a second). The root cause of this problem is not yet identified. If it bothers you, disabling composition in KWin fixes this issue.

Query execution history

SQL history tab keeps the history of SQL queries you have ever executed. The number of queries kept in history is configurable in Configuration dialog and defaults 1000 entries. When that limit is exceeded, the oldest entry gets deleted.

You can browse and preview queries from the history. Double-click on the entry to automatically copy it into SQL editor, so you can re-execute it.

You can erase entire SQL queries history by clicking on «Erase history» toolbar button and confirming your choice.

Built-in SQL functions

SQLiteStudio defines several SQL functions in databases that it connects to, so you can use them as long as you execute queries from withing SQLiteStudio.

Functions registered by SQLiteStudio are available only from SQLiteStudio. They will not be available in other applications connecting to the database, unless those applications define such functions by themself

Editing the data

In the Grid view of the data you can start editing values by:

• double-clicking on the cell (old value will remain and will be initially selected),
• selecting the cell and pressing Enter key (old value will remain and will be initially selected),
• selecting the cell and simply start typing the new value (old value will be overwritten immediately),
• right-clicking on the cell and selecting «Edit value in editor» from the context menu (value will be edited in the separate dialog window).

Any changes you make to data are not commited immediately to the database. You have to commit them manually by clicking «Commit» on the toolbar, or hitting «Ctrl+Enter» keyboard shortcut (the shortcut can be changed in Configuration dialog.

If you decide to drop any changes to data you made, click on «Rollback» on the toolbar, or hit «Ctrl+Backspace» keyboard shortcut (it’s also configurable).

You can selectively commit/rollback cells (to commit/rollback only some of current changes). To do so, select cells you want to commit/rollback and right-click on them. You will see «Commit selected cells» and «Rollback selected cells» positions in the context menu.

In the Form view of the data you can edit values of a single row at the time. Each cell from the row is represented here as a tabbed view/editor. By hitting «Commit» or «Rollback» in the Form view you commit/rollback only the current row.

The Form view operates only on rows currently visible in the Grid view, so when you reach the last row from the grid view and you want to edit next rows, you need to switch back to the Grid view, go to next data page and then go to Form view and continue your work.

Note, that before changing pages in Grid view you have to commit/rollback your current changes to the data, otherwise they will be lost.

Value editor dialog

If the column edited is of «BLOB» type, a separate value editor dialog will open. Editor dialog has several tabs where you can view and edit data using different modes (textual, binary, etc). The same dialog will open if you right-click on the cell and select «Edit in value editor».

The same data editors/views are used on the Form view, so if you plan to edit all column using the value editor, you may just as good switch to the Form view.

Value editor can be configured, so it uses certain editor/view modes for the certain column datatype. To do so, use an icon on the right side of the editor view:

On the left side of the configuration you see the list of datatypes recognized by value editor, where you can select the datatype you want to configure. On top-right area you can see list of all available editor widgets (they are provided by plugins, so the list depends on what plugins you have currently loaded, but many plugins are already built into application). On bottom-right area you have a sample of how your current configuration for selected datatype look like. You can also drag&drop editor tabs to change their order. The left-most editor is a default editor for that datatype (which means that the left tab is always open first for that datatype).

If there’s a datatype not specified in the configuration list and you step into that type somewhere in your database, SQLiteStudio will pick default editors (Text and Hex).

Transparent database attaching

Transparent attaching feature in SQLiteStudio is an ability of calling ATTACH and DETACH automatically by SQLiteStudio whenever it’s necessary. You can enter your SQL query in SQL editor using tables from several different databases, using database names as presented on databases list:

Since transparent attaching calls ATTACH and DETACH internally, it’s affected by all limitations that apply to those statements. That is no support for attaching databases of different type. It means that you cannot mix SQLite 2 and SQLite 3 databases in the single query, or any other database type mix ups.

Creating and editing tables

TODO: remember about multi-column sorting TODO: explain difference between column constraints and table constraints

Column dialog

Column constraints

PRIMARY KEY constraints

»’FOREIGN KEY constraints »’

»’ NOT NULL constraints »’

»’ UNIQUE constraints »’

»’ COLLATE constraints »’

»’ CHECK constraints »’

»’ DEFAULT constraints »’

Table constraints

»’ PRIMARY KEY constraints »’

»’ FOREIGN KEY constraints »’

»’ UNIQUE constraints »’

»’ CHECK constraints »’

WITHOUT ROWID tables

Virtual tables

Creating and editing indexes

Partial indexes

Creating and editing triggers

Trigger for a View

Creating and editing views

Moving and copying objects between databases

Exporting schema and data

Importing data

Useful keyboard shortcuts

Configuration dialog

Customizing data type editors

Command line options

Here’s a list of options that can be passed as arguments when running SQLiteStudio. Those options are valid only for the GUI application, for CLI there is separate set of options (click CLI to see details).

SQLiteStudio configuration file

Entire SQLiteStudio configuration is stored in a single SQLite3 database file. More details at Configuration directory location page.

SQLiteStudio updates

Automatic

Automatic updates perform:

• updates availability check,
• updates downloading,
• updates installing.

Automatic updates work only for a Portable distribution. If the application was installed from Linux’s package manager, it’s the responsibility of that package manager to update the application.

The updates availability check is always performed at application startup, but it can be disabled by the user (and re-enabled in configuration dialog). It can also be manually triggered by the user from menu Help / Check for updates. User is always asked for confirmation before downloading updates.

Installation of updates might require administrator privileges. If so, SQLiteStudio will ask user for password (on Linux and MacOS X), or for confirmation of privileged operation (on Windows).

HTTP proxy is not supported as for now, so if your HTTP connection requires proxy to be configured, then automatic updates will not work for you. Support for proxy configuration is planned in future versions.

Manual

Manual update is simply about downloading the fresh version from the download page and replacing old version with it.

Advanced usage

Custom SQL functions

SQLiteStudio allows user to create additional SQL functions to be used in SQL queries. You can create for example function «test123()» which will return a random string for you and you will be able to type:

INSERT INTO myTable VALUES (test123());

Custom SQL functions in SQLiteStudio are very useful if your application uses some custom SQL functions for itself and you want to be able to emulate your application’s environment in SQLiteStudio, so you can execute similar queries on the database.

Other use case is when you want to simplify some database maintanance tasks. Creating function which does some more complicated operations using some simple input values can be treated as a procedure by SQLite, because you can execute it with:

SELECT functionName(input_arguments);

To create/edit/delete SQL functions, open Custom SQL Functions editor:

In the functions editor you need to define few of things:

1. Function name. It needs to be unique.

2. Is your function goind to be Scalar or Aggregate?

   1. Scalar functions are those applied to every single data row separately. Example of such function is «length()«.
   2. Aggregate functions are used together with GROUP BY clause and they are applied to the entire result set. Example of such function is «min()«.

3. Implementation language — a programming language that you will use to define what the function actually do.

4. Input arguments — list of named input arguments. Currently they’re just symbolic names used by a SQL syntax assistant to suggest what are expected input arguments. Nothing more.

5. Databases — for which databases the function should be registered by SQLiteStudio.

6. For Scalar function — a single implementation field is required. It’s the code in chosen programing language that does the actual function’s job. The return value of this code becomes the result of the function call.

7. For Aggregate function there are 3 implementation fields:

   1. Initialization code — this code is executed once, at the begining of grouped query results. You can use it to initialize some variables to «0» values, or something like that. This field is optional.
   2. Per step code — this code is executed for each data row from the results. Each iteration uses the same context, which means all variables are shared across all steps.
   3. Final step code — it’s executed after last «per step» code. You can calculate the final result and releasy any resources you acquired. This code must return the result of the aggregate function.

Arguments passed to function are available in the code as a variables, whose names depend on certain implementation language you chose. For Tcl arguments are available in «$argv«. For QtScript they’re in «arguments» array. To learn how to handle input arguments in certain language, read about the specific plugin that provides support for that language. Offical plugins are described at Scripting languages plugins page).

Custom SQL functions created in SQLiteStudio exist only in context of SQLiteStudio. Functions are not persisted in database, it’s just not how SQLite databases work. If you want to use some function in your own application, you have to register that function in SQLite from your application.

Custom scripts repository

There is a collection of useful scripts maintained here on the wiki: https://github.com/pawelsalawa/sqlitestudio/wiki/Scripts_repository

Code snippets

Code snippets feature was introduced in SQLiteStudio 3.4.0. It allows user to define collection of code snippets that can be then used (inserted) in SQL Editor window.

To define snippets, open the Code Snippets editor either by using toolbar button:

or from menu Tools -> Open Code Snippets editor.

To use (insert) a snippet into current SQL Editor, invoke the code assistant (Ctrl+Space by default — or Cmd+Space on MacOSX) and then, while code assistant is still active, hit the same hotkey again (Ctrl+Space / Cmd+Space). This way you will switch code assistant to another mode — for inserting code snippets. From there you can choose the snippet to insert. If you defined a «code assistant shortcut» for a particular snippet (in snippets editor), then you can hit that shortcut, while code assistant is in «snippets mode». Otherwise you just have to pick the snippet from the list in the assistant.

Custom collations

To add/edit/delete user’s custom collation sequence, open Collations editor:

Collation sequence is an algorithm definition that is used by SQLite to compare two values and find out if one is less/greater/equal to another. Collations can be used in several contexts by SQLite — for example table’s column can be defined with a specific collation, which will affect how is sorting done for that column, how is grouping by that column done, etc. See SQLite documentation for more details.

Collation implementation is like writting custom SQL function implementation with 2 rules:

• The collation function will always be provided with exactly 2 arguments — values to compare.
• The collation function has to return -1, 0, or 1, when first argument is less then, equal or greater than second argument.

SQLite extensions

SQLite extensions are supported only in SQLite 3. SQLite documentation for details on what are SQLite runtime loadable extensions.

To load SQLite extension into SQLiteStudio database connections, the easiest way is to put it into one of directories, that SQLiteStudio scans for such extensions. You can learn these directories by going into Help -> About -> Environment -> SQLite extensions directories. Usually these are ext and extensions directories, next to plugins directory (in the SQLiteStudio installation directory).

If you put compiled SQLite extension into one of those directories, it will automatically be loaded by SQLiteStudio upon next startup. Note, that such (automatically loaded) extension will not be registered in any database, until you enable it for all or particular databases.

To enable (or just see/add/edit) SQLite extensions currently managed by SQLiteStudio, open Extension Manager:

You can add more extension directories scanned for automatic loading by defining SQLITESTUDIO_SQLITE_EXTENSIONS environment variable (before application start) pointing to directories (it could be multiple directories, just like you would define PATH environment variable). Another option is to define extra directory during compilation of SQLiteStudio, by passing SQLITE_EXTENSIONS_DIR symbol to compiler with value of desired directory path.

Extension manager window is similar to Collation editor window. On the left side it has list (2) of extensions already registered in the application, which are loaded for defined set (or all) of databases. At the top (1) there is a toolbar, where you can add/remove extensions and commit/rollback any changes made in the manager window. Also in that toolbar there is a help button pointing to this User Manual section.

The main part (3, 4, 5, 6) of the Extension manager window will become enabled/disabled according to your current selection in the list (2) — if there is nothing selected in the list, the main part will stay disabled. When you add/edit certain extension, you need to provide path to the library file, which delivers the extension. This is what you do in the first field (3). You can use file dialog (4) to make it easier.

The initialization function field (5) is optional and should be used if SQLite was unable to initialize an extension, due to missing initialization function. As you may have read in the SQLite documentation, the initialization function name is predicted basing on the library/extension file name, so if the file name does not match initialization function accordingly, you may need to provide the initialization function manually and that’s what you can do in the field (5). Otherwise you can leave it empty.

List of databases in area (6) is just like in Collations editor and Functions editor. You can define there whether certain extension should be loaded for all databases (for SQLite version 3 only) used in the application, or should it be loaded for preselected set of databases.

SQLiteStudio will try to load (probe) the extension that you’re editing (while you type the file path), so you will be notified if the file is invalid/inexisting or there is any other problem (such as initialization function not found). If anything is wrong with the extension file, you will see pulsing red dot at the left-top corner of the file entry. Once you fix file path or initialization extension function name, the red dot will disappear automatically.

Remember to commit your editions. Otherwise they will not be applied.

SQLiteStudio version 3.1.x and older supports extension loading only through load_extension() SQL function executed in SQL editor.

Populating tables

Populating tables feature is a way to insert lots of data into tables, basing on some rules, in order to have some test data to work with. It automates inserting lots of data that have predictible values, like sequence o numbers, random numbers, random text, random numbers, etc, etc.

Populating is done using rules that are defined by Populating plugins. Each plugin has its own input arguments (like minimum/maximum values, etc) and produces set of values basing on input arguments and internal algorithms.

To populate table with data, click on «Populate table» button on Table window’s toolbar. In the Populate dialog you will be able to define how many rows of data you want to produce and select which columns should be populated. Unselected columns will be populated with their default values. When selecting columns to populate, you need to pick a plugin used for populating — this determinates what data will be inserted into that column. Most plugins also have configuration available to specify some details of data production.

DDL history

When managing numerous databases and doing lots of schema changes, it’s useful to have a tool to track and reproduce changed made on the schema. SQLiteStudio shows the DDL preview before executing database object creation/change statements (unless this option was disabled by user). It gets the job done, but it can be clunky to copy the DDL and paste to some it to some other text file to keep the history. It’s also risky, cause one might forget to copy the DDL sometimes.

That’s where the DDL history window comes in. It keeps history of any DDL changes (CREATE statements, DROP statements and any other statements related to changes of database objects), regardless of DDL preview dialog being enabled or not. It keeps information about date and time of execution and database on which the DDL was executed.

Together with SQL history from SQL Editor, DDL history window makes any changes made to any database from SQLiteStudio reproducible.

To open DDL history window, click on «DDL history» button on toolbar:

Converting SQLite database version

Command line interface (CLI)

If you’re looking for command line options for running main SQLiteStudio application, see Command line options.

CLI is a separate, text-only console application. See Command line interface for more details.

Troubleshooting

SQLiteStudio crashed! What do I do?

Report this on bugs.sqlitestudio.pl, or — if you prefere — you can do it from SQLiteStudio. Go to Help menu and pick «Report a bug».

You can also ask for help on forum.sqlitestudio.pl. Sometime there is a workaround for some bugs, so you can apply them before the bug gets fixed and released in new version.

I have saved binary data into SQLite 2 database, but now it’s damaged, empty or truncated!

The following applies to SQLite 2 only.

Long story short: The old SQLite 2 library is very limited in regards of data types, especially when it comes to binary data. It’s better to use regular text columns and store base64-encoded data.

In more details: To be able to store binary data in SQLite 2 table, you need to define column data type as BLOB, or CLOB, so SQLiteStudio knows that you will be storing binary data in that column. Without that, SQLiteStudio is unable to write and read binary data correctly from that column.

Additionally, the SQLite 2 version has a bug, which makes storing binary data to be truncated to the first occurrence of the null byte (byte with value = 0), just like it is done for regular strings. There’s nothing SQLiteStudio can do about it. Unless you store data with all non-null bytes, the only solution is to use regular TEXT based columns and store base64-encoded data.

I’m using Fcitx input method, but it doesn’t work in SQLiteStudio

Not sure about Windows or MacOS X, but under Linux you can use Fcitx with SQLiteStudio. Here’s how to do that.

• If you are using Linux managed package:

The only thing you may need to do is to change default shortcut for SQLiteStudio code assistant (Ctrl+Space), as it is colliding with Fcitx triggering shortcut.

• If you’re using Portable distribution (binary packages downloaded from SQLiteStudio’s home page):

Copy platforminputcontexts/libfcitxplatforminputcontextplugin.so from your Qt installation into SQLiteStudio/platforminputcontexts/libfcitxplatforminputcontextplugin.so (you will have to create the platforminputcontexts subdirectory), restart SQLiteStudio and that’s it!

In other words, what you have to do is:

sudo apt-get install fcitx-frontend-qt5
ln -s /usr/lib/`arch`-linux-gnu/qt5/plugins/platforminputcontexts/ /path/to/SQLiteStudio3/

Just remember to change the SQLiteStudio’s code assistant shortcut (Ctrl+Space), so it doesn’t collide with Fcitx.

See also

1. FAQ
2. Forum

• Главная

• Инструкции

• SQLite

• Краткое руководство по работе с SQLite

SQLite — это внутрипроцессная библиотека, которая реализует автономный, бессерверный, не требующий настройки транзакционный механизм базы данных SQL. Исходный код для SQLite имеется в открытом доступе, позволяет модифицирование и является бесплатным. SQLite выбирают за скорость, минимализм, надёжность. В сервисах timeweb.cloud вы можете установить её на VDS-сервер. 

Кстати, в официальном канале Timeweb Cloud мы собрали комьюнити из специалистов, которые говорят про IT-тренды, делятся полезными инструкциями и даже приглашают к себе работать.

Библиотека SQLite уже скомпилирована и доступна к скачиванию и установке с официального сайта. Желающие могут компилировать исходники и самостоятельно. 

Для написания и исполнения запросов к базам SQLite можно использовать простую программу-оболочку командной строки — sqlite3. Но также существуют множество бесплатных (например, SQLiteStudio) и коммерческих инструментов с графическим интерфейсом для управления базами SQLite.

Установка и запуск SQLite на Windows

1. Переходим на страницу загрузки SQLite и загружаем файлы, обеспечивающие работу SQLite в Windows, в том числе sqlite3:

2. На своем компьютере создаём новую папку, например, C:sqlite.

3. Извлекаем содержимое скачанного файла в папку C:sqlite. Там должны появиться три программы:

• Sqlite3.exe
• Sqlite3_analizer.exe
• sqldiff.exe

4. В командной строке переходим в папку с sqlite3.exe и запускаем этот файл. При этом можно указать имя базы данных:

C:>cd C:sqlite
sqlite3 <имя базы данных SQLite>

 Если файл с таким именем не существует, то он будет создан автоматически. Если в командной строке не указано имя файла с базой данных, создается временная база данных, которая автоматически удаляется при выходе из sqlite3.

5. Пользователи Windows могут дважды кликнуть значок sqlite3.exe, чтобы открылось всплывающее окно терминала с запущенным sqlite. Однако, так как двойной клик запускает sqlite3 без аргументов, файл базы данных не будет указан, а будет использоваться временная база данных, которая удалится при завершении сеанса.

Установка и запуск SQLite на Linux

Посмотрим как установить на Linux SQLite на примере Ubuntu.

1.  Чтобы установить sqlite3 в Ubuntu, сначала обновите список пакетов:

$ sudo apt update

2. Затем установите sqlite3:

$ sudo apt install sqlite3

3. Понять, прошла ли установка, можно, проверив версию:

$ sqlite3 --version

В случае успеха, вы получите нечто подобное:

3.38.3 2022-04-27 12:03:15 3bfa9cc97da10589251b342961df8f5f68c7399fa117345eeb516bee837balt1

Как создать базу данных в SQLite

Существует несколько способов, чтобы сделать создать базу в SQLite:

1. Как отмечалось выше, при запуске sqlite3 можно указать имя базы данных:

$ sqlite3 my_first_db.db

Если база my_first_db.db существует, то она откроется, если нет — она будет создана и автоматически удалится при выходе из sqlite3, если к базе не было совершено ни одного запроса. Поэтому, чтобы убедиться, что база записана на диск, можно запустить пустой запрос, введя ; и нажав Enter:

sqlite> ;

После работы изменения в базе можно сохранить с помощью специальной команды SQLite «.save» с указанием имени базы:

sqlite> .save my_first_db.db

или полного пути до базы:

sqlite> .save C:/sqlite/my_first_db.db

При использовании команды «.save» стоит проявлять осторожность, так как эта команда перезапишет все ранее существовавшие файлы с таким же именем не запрашивая подтверждения.

2. В SQLite создать базу данных можно с помощью команды «.open»:

sqlite> .open my_first_db.db

Как и в первом случае, если база с указанным именем существует, то она откроется, если же не существует — то будет создана. При таком способе создания новая база данных SQLite не исчезнет при закрытии sqlite3, но все изменения перед выходом из программы нужно сохранить с помощью команды «.save», как показано выше.

3. Как уже упоминалось, при запуске sqlite3 без аргументов, будет использоваться временная база данных, которая будет удалена при завершении сеанса. Однако эту базу можно сохранить на диск с помощью команды «.save»

$ sqlite3
SQLite version 3.38.3 2022-04-27 12:03:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ... many SQL commands omitted ...
sqlite> .save db1.db
sqlite>

SQLite. Создание таблицы

Информация в базах SQLite хранится в виде таблиц. Для создания таблиц в SQLite используется запрос CREATE TABLE. Этот запрос должен содержать имя таблицы и имена полей (столбцов), а также  может содержать типы данных, описания полей (ключевое поле) и значения по умолчанию. Например, создадим таблицу с описаниями параметров разных пород собак, применяя CREATE TABLE в SQLite:

sqlite> CREATE TABLE dog_params (id integer PRIMARY KEY,
    dog_breed text,
    speed_km_per_h integer,
    weight_kg integer);

В нашей таблице колонка id помечена как PRIMARY KEY. Это значит, что id будет ключевым столбцом (индексом) и целое число для него будет генерироваться автоматически.

Внесение записей в таблицу

Для внесения новой записи в таблицу используется SQL-запрос INSERT INTO, в котором указывается в какую таблицу и в какие поля заносить новые значения. Структура запроса:

sqlite> INSERT INTO таблица (столбец1, столбец2)
VALUES (значение1, значение2);

Если количество значений соответствует количеству колонок в таблице, то названия полей можно исключить из запроса. Столбцы таблицы, которые не отображаются в списке столбцов, заполняются значением столбца по умолчанию (указывается как часть инструкции CREATE TABLE) или значением NULL, если значение по умолчанию не было указано.

Например:

sqlite> INSERT INTO dog_params (dog_breed, speed_km_per_h, weight_kg)
VALUES ("Greyhound", 72, 29);
sqlite> INSERT INTO dog_params VALUES (2, "Jack Russell Terrier", 61, 5);
sqlite> INSERT INTO dog_params VALUES (3, "Dalmation", 59, 24);

В первом случае id сгенерировался сам, так как это поле назначено индексом, а для внесения строчек без указания названий колонок нужно прописывать номера id вручную.

С помощью следующего SQL-запроса можно вставить несколько записей одновременно, id опять сгенерируются автоматически:

sqlite> INSERT INTO dog_params (dog_breed, speed_km_per_h, weight_kg)
VALUES ("Borzoi", 58, 39), ("Standard Poodle", 48, 27);

SQLite. Просмотр таблиц

Чтобы просмотреть всё содержимое таблицы, используется запрос SELECT:

sqlite> SELECT * FROM dog_params;

Результат будет выглядеть таким образом:

1|Greyhound|72|29
2|Jack Russell Terrier|61|5
3|Dalmation|59|24
4|Borzoi|58|39
5|Standard Poodle|48|27

С помощью команды WHERE можно просмотреть только те строки, которые удовлетворяют некоторому условию. Например, выведем породы, у которых скорость меньше 60 км/ч:

sqlite> SELECT * FROM dog_params WHERE speed_km_per_h < 60;
3|Dalmation|59|24
4|Borzoi|58|39
5|Standard Poodle|48|27

Изменение записей в таблице

С помощью запроса ALTER TABLE и дополнительных команд можно изменять таблицу следующим образом:

•     переименовать таблицу — RENAME TABLE,
•     добавить колонку — ADD COLUMN,
•     переименовать колонку — RENAME COLUMN,
•     удалить колонку — DROP COLUMN.

К примеру, добавим в нашу таблицу колонку с высотой собаки в холке:

sqlite> ALTER TABLE dog_params ADD COLUMN height_cm integer;

Чтобы изменить значения в существующих записях таблицы понадобится запрос в SQLite – Update. В этом случае возможно как изменение значений ячейки в группе строк, так и изменение значения ячейки отдельной строки.

В качестве примера, внесем значения высоты собак в холке в нашу таблицу:

sqlite> UPDATE dog_params SET height_cm=71 WHERE id=1;
sqlite> UPDATE dog_params SET height_cm=28 WHERE id=2;
sqlite> UPDATE dog_params SET height_cm=53 WHERE id=3;
sqlite> UPDATE dog_params SET height_cm=69 WHERE id=4;
sqlite> UPDATE dog_params SET height_cm=61 WHERE id=5;

Наша итоговая таблица будет выглядеть так:

sqlite> SELECT * FROM dog_params:
1|Greyhound|72|29|71
2|Jack Russell Terrier|61|5|28
3|Dalmation|59|24|53
4|Borzoi|58|39|69
5|Standard Poodle|48|27|61

Как пользоваться SQLiteStudio

Работать с базами данных SQLite можно не только из командной строки, но и с помощью инструментов с графическим интерфейсом, одним из которых является SQLiteStudio.

Инструмент SQLiteStudio бесплатный, портативный, интуитивно понятный и кроссплатформенный. Он предоставляет много наиболее важных функций для работы с базами данных SQLite, такие как импорт и экспорт данных в различных форматах, включая CSV, XML и JSON.

Вы можете скачать установщик SQLiteStudio или его портативную версию с официального сайта https://sqlitestudio.pl. Затем необходимо извлечь (или установить) загруженный файл в папку, например, C:sqlitegui и запустить его. Подробные инструкции по установке и работе с SQLiteStudio можно найти на сайте.