» about » content » twitter » github »

SQLite

SQLite is one of the coolest databases I've ever worked with; as of this writing, I've worked as a DBA on Oracle, DB2, SQL Server, MySQL and many, many more. The big databases are great, but sometimes, you just need a simple to use, small database that can still handle a lot of data; enter SQLite. It is used as an embedded in many applications and operating systems (for example Firefox and the iPhone). Give it a try...

Download it from here.

The Basics

Access the database (it will create it for you):

sqlite3 MyDatabase

Create a table:

sqlite> CREATE TABLE BIGDATA (
   ...>   datakey varchar(30) primary key,
   ...>   notes text,
   ...>   notenumber real
   ...> );
sqlite>
    

Insert a row into the table:

insert into bigdata values('first entry','hello!',10);

View the data on your table:

select * from bigdata;

View the metadata of all the DDL objects on the database:

select * from sqlite_master;

Take a look at some of your options:

.help

Exit the database:

.exit

Some Hints

How to create an auto-incrementing column : examplecolumn integer primary key autoincrement Then when you insert a NULL into this column, it will auto-increment

How to insert the current date+time : mydatetime datetime Then to insert, use: datetime('now')

More

This is a very in-depth look at SQLite and the many things you can use it for.

Take a look here for much more detail.

There's a really nice GUI tool for managing SQLite data here (that can even encrypt your database).

© Roqet 2018 :: 2018-05-11 17:07:11