[go: up one dir, main page]
Everand Logo

Welcome to Everand!

  • Discover millions of ebooks, audiobooks, and so much more with a free trial

    From $11.99/month after trial. Cancel anytime.

    Learn SQLite in 24 Hours
    Learn SQLite in 24 Hours
    Learn SQLite in 24 Hours
    Ebook183 pages1 hour

    Learn SQLite in 24 Hours

    By Alex Nordeen

    ()

    Read preview

    About this ebook

    SQLite is a very lightweight database, with no installation required, zero configuration, and no server required. SQLite is widely used as a database of choice in mobile apps, camera, home electronic devices and other embedded systems.


    This book is designed for beginners with little or no prior database experience.


    Table Of Content


    Here is what you will learn:


    Chapter 1: Introduction


    Chapter 2: Create Database


    CREATE Database


    CREATE Database in a Specific Location


    Create database and populate it with tables from a file


    Backup & Database


    Drop Database


    Chapter 3: Create, Alter, Drop Table


    Create table


    Drop table


    Alter table


    Add columns- Using ALTER TABLE Command


    Insert value into a table


    Chapter 4: Primary & Foreign Key


    SQLite constraint


    Primary Key


    Not null constraint


    DEFAULT Constraint


    UNIQUE constraint


    What is a SQLite Foreign KEY?


    Chapter 5: Data Types


    Storage Classes


    Affinity Type


    Examples of storing data types in SQLite


    Chapter 6: Select, Where, Limit, Count, Group By, Union


    Reading data with select


    WHERE


    Limiting and Ordering


    Group BY


    Query & Subquery


    Set Operations -UNION,Intersect


    Conditional results


    Advanced queries


    Chapter 7: Joins


    Introduction to SQLite JOIN Clause


    INNER JOIN


    JOIN … USING


    NATURAL JOIN


    LEFT OUTER JOIN


    CROSS JOIN


    Chapter 8: INSERT, UPDATE, DELETE


    INSERT


    Update


    Delete


    Conflict clause


    Chapter 9: Index, Trigger & View


    View


    Index


    Trigger


    Chapter 10: SQLite Functions


    Finding the LENGTH of a string


    UPPER Function and LOWER Function


    TRIM Function


    TYPEOF Function


    SQLite LAST_INSERT_ROWID


    SQLite library


    User-defined functions

    LanguageEnglish
    PublisherPublishdrive
    Release dateNov 12, 2021
    Learn SQLite in 24 Hours

    Read more from Alex Nordeen

    Related to Learn SQLite in 24 Hours

    Related ebooks

    Internet & Web For You

    View More
    View More

    Related podcast episodes

    Related categories

    Reviews for Learn SQLite in 24 Hours

    0 ratings

    0 ratings0 reviews

    What did you think?

    Tap to rate

    Review must be at least 10 words

      Book preview

      Learn SQLite in 24 Hours - Alex Nordeen

      Learn SQLite in 24 Hours

      By Alex Nordeen

      Copyright 2021 - All Rights Reserved – Alex Nordeen

      ALL RIGHTS RESERVED. No part of this publication may be reproduced or transmitted in any form whatsoever, electronic, or mechanical, including photocopying, recording, or by any informational storage or retrieval system without express written, dated and signed permission from the author.

      Table Of Content

      Chapter 1: Introduction

      Chapter 2: Create Database

      CREATE Database

      CREATE Database in a Specific Location

      Create database and populate it with tables from a file

      Backup & Database

      Drop Database

      Chapter 3: Create, Alter, Drop Table

      Create table

      Drop table

      Alter table

      Add columns- Using ALTER TABLE Command

      Insert value into a table

      Chapter 4: Primary & Foreign Key

      SQLite constraint

      Primary Key

      Not null constraint

      DEFAULT Constraint

      UNIQUE constraint

      What is a SQLite Foreign KEY?

      Chapter 5: Data Types

      Storage Classes

      Affinity Type

      Examples of storing data types in SQLite

      Chapter 6: Select, Where, Limit, Count, Group By, Union

      Reading data with select

      WHERE

      Limiting and Ordering

      Group BY

      Query & Subquery

      Set Operations -UNION,Intersect

      Conditional results

      Advanced queries

      Chapter 7: Joins

      Introduction to SQLite JOIN Clause

      INNER JOIN

      JOIN … USING

      NATURAL JOIN

      LEFT OUTER JOIN

      CROSS JOIN

      Chapter 8: INSERT, UPDATE, DELETE

      INSERT

      Update

      Delete

      Conflict clause

      Chapter 9: Index, Trigger & View

      View

      Index

      Trigger

      Chapter 10: SQLite Functions

      Finding the LENGTH of a string

      UPPER Function and LOWER Function

      TRIM Function

      TYPEOF Function

      SQLite LAST_INSERT_ROWID

      SQLite library

      User-defined functions

      Chapter 1: Introduction

      What is SQLite?

      SQLite is a relational database management system. It was designed originally on August 2000. It is an Open source software.

      SQLite is very lightweight; it is less than 500Kb size. Unlike other database management systems like SQL Server, or Oracle.

      SQLite is not a client-server database management system: it is an in-memory library that you can call it and use it directly, no installation and no configuration required.

      A typical SQLite database will be contained on a single file on the computer disk storage with all the database objects (tables, views, triggers, etc.) included on that file, no dedicated server required.

      SQLite Performance - Why and When to use SQLite?

      When to use SQLite:

      Whenever you need a lightweight database, if you are developing an embedded software for devices like televisions, mobile phones, cameras, home electronic devices, etc. then SQLite is a good choice.

      If you want to manage complex session information for a website, SQLite can handle low to medium traffic HTTP requests.

      When you need to store an archive of files, SQLite can produce smaller size archives and much amount metadata included than regular ZIP archives.

      If you want to do some processing on some data within an application, you can use SQLite as a temporary dataset. You can load the data into an SQLite in-memory database and do the queries the way you want. You can extract the data in the way you want back to your application.

      It gives you an easy and efficient way to deal with data rather than do the data processing internally inside in-memory variables. For example, if you are developing a program and you have some records that you want to do some calculations on them. Then you can create an SQLite database and insert therecords there, and with only one query, you can select the records and do the calculations you want on them directly.

      When you need a database system for learning and training purposes, SQLite is a good fit for that. As we explained, no installation and no configuration are required. Just copy the SQLite libraries in your computer and is ready for creating the database.

      Why to use SQLite:

      It is all free – SQLite is an open source, no license required to work with it.

      SQLite is cross-platform database management system; it can be used on a broad range of different platforms like Windows, Mac OS, Linux, and Unix. It can also be used on a lot of embedded operating systems like Symbian, and Windows CE.

      SQLite offers an efficient way for storing data, the length of the columns is variable and is not fixed. So SQLite will allocate only the space a field needs. For example, if you have a varchar(200) column, and you put a 10 characters' length value on it, then SQLite will allocate only 20 characters' space for that value not the whole 200 space.

      A broad range of SQLite APIs – SQLite provides APIs for a broad range of programming language, for example.Net languages (Visual Basic, C#), PHP, Java, Objective C, Python and a lot of other programming language.

      SQLite is very flexible.

      SQLite variables are dynamic typed, meaning that the type of the variable is not determined until it is assigned a value, and not on the declaration.

      INSERT ON CONFLICT REPLACE statement. With this statement, you can tell SQLite to try to do an insert on a table and if it found rows with the same primary keys, then update them with the values from the inserted values.

      With SQLite, you can work on multiple databases on the same session on the same time. just attach those databases and then you can access all the databases' objects (tables, views, etc..) on the same time.

      SQLite limitations and Unsupported Features

      The following are the list of unsupported features and limitations in SQLite:

      SQLite doesn't support neither RIGHT OUTER JOIN nor FULL OUTER JOIN. It supports only LEFT OUTER JOIN.

      Limitations in ALTER table statement: with ALTER TABLE statement in SQLite you can only add a column or rename a table (as we will see in the following tutorials). However, you can't do the following:

      ALTER column.

      DROP a column.

      ADD a constraint.

      VIEWs are read-only – you can't write INSERT, DELETE, or UPDATE statements into the view. However, you can create a trigger on a view and do the INSERT, DELETE, or UPDATE statements into it.

      GRANT and REVOKE commands are not implemented in SQLite. There are only normal file access permissions implemented in SQLite. This is because SQLite reads and writes to the disk files, unlike other Database management systems.

      TRIGGERS – As we will see

      Enjoying the preview?
      Page 1 of 1