Difference between revisions of "Search an SQL database using sqlite3"
(Created page with "Category:Cookbook Category:Sqlite") |
|||
(11 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
https://www.sqlite.org/index.html | |||
<code>sqlite</code> implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine to query <code>.sql</code> files. | |||
== Getting started == | |||
To use sqlite, you must have a valid metadata file with a <code>.db</code> file extension, and an installed version of SQL. This tutorial assumes knowledge of the command line. | |||
To start the sql engine, type: | |||
<syntaxhighlight lang="SQL"> | |||
sqlite3 | |||
</syntaxhighlight> | |||
The prompt will change, showing that the sqlite engine has been initialised. To open a metadata file, type: | |||
<syntaxhighlight lang="SQL"> | |||
.open filename.db | |||
</syntaxhighlight> | |||
=== Displaying tables and columns === | |||
You can then display tables with: | |||
<syntaxhighlight lang="SQL"> | |||
.tables | |||
</syntaxhighlight> | |||
Based on what is returned, you can then query the content of each table with commands such as: | |||
<syntaxhighlight lang="SQL"> | |||
SELECT * FROM mytable; | |||
</syntaxhighlight> | |||
You may want to have a more human-readable view of the data that is printed in the terminal, by organising the headings and columns: | |||
<syntaxhighlight lang="SQL" line> | |||
.headers ON | |||
.mode column | |||
</syntaxhighlight> | |||
You will get output like: | |||
<syntaxhighlight lang="SQL" line> | |||
.headers on | |||
.mode column | |||
select * from mytable; | |||
id foo bar | |||
---------- ---------- ---------- | |||
1 val1 val2 | |||
2 val3 val4 | |||
</syntaxhighlight> | |||
=== Getting a list of column names === | |||
An alternative way to get a list of column names not mentioned here that is cross platform and does not rely on the sqlite3.exe shell is to select from the <code>PRAGMA_TABLE_INFO()</code> table value function. | |||
<syntaxhighlight lang="SQL" line> | |||
SELECT name FROM PRAGMA_TABLE_INFO('your_table'); | |||
name | |||
tbl_name | |||
rootpage | |||
sql | |||
</syntaxhighlight> | |||
You can check if a certain column exists by querying: | |||
<syntaxhighlight lang="SQL" line> | |||
SELECT 1 FROM PRAGMA_TABLE_INFO('your_table') WHERE name='column1'; | |||
1 | |||
</syntaxhighlight> | |||
[[Category:Cookbook]] | [[Category:Cookbook]] | ||
[[Category: | [[Category:SQLite]] |
Latest revision as of 19:02, 23 November 2021
https://www.sqlite.org/index.html
sqlite
implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine to query .sql
files.
Getting started
To use sqlite, you must have a valid metadata file with a .db
file extension, and an installed version of SQL. This tutorial assumes knowledge of the command line.
To start the sql engine, type:
sqlite3
The prompt will change, showing that the sqlite engine has been initialised. To open a metadata file, type:
.open filename.db
Displaying tables and columns
You can then display tables with:
.tables
Based on what is returned, you can then query the content of each table with commands such as:
SELECT * FROM mytable;
You may want to have a more human-readable view of the data that is printed in the terminal, by organising the headings and columns:
.headers ON
.mode column
You will get output like:
.headers on
.mode column
select * from mytable;
id foo bar
---------- ---------- ----------
1 val1 val2
2 val3 val4
Getting a list of column names
An alternative way to get a list of column names not mentioned here that is cross platform and does not rely on the sqlite3.exe shell is to select from the PRAGMA_TABLE_INFO()
table value function.
SELECT name FROM PRAGMA_TABLE_INFO('your_table');
name
tbl_name
rootpage
sql
You can check if a certain column exists by querying:
SELECT 1 FROM PRAGMA_TABLE_INFO('your_table') WHERE name='column1';
1