edu Toidayhoc

Category: sqlite

  • Khóa học miễn phí SQLite – AND & OR Clauses nhận dự án làm có lương

    SQLite – AND & OR Operators

    SQLite AND & OR operators are used to compile multiple conditions to narrow down the selected data in an SQLite statement. These two operators are called conjunctive operators.

    These operators provide a means to make multiple comparisons with different operators in the same SQLite statement.

    The AND Operator

    The AND operator allows the existence of multiple conditions in a SQLite statement”s WHERE clause. While using AND operator, complete condition will be assumed true when all the conditions are true. For example, [condition1] AND [condition2] will be true only when both condition1 and condition2 are true.

    Syntax

    Following is the basic syntax of AND operator with WHERE clause.

    SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

    You can combine N number of conditions using AND operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE.

    Example

    Consider COMPANY table with the following records −

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

    Following SELECT statement lists down all the records where AGE is greater than or equal to 25 AND salary is greater than or equal to 65000.00.

    sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

    The OR Operator

    The OR operator is also used to combine multiple conditions in a SQLite statement”s WHERE clause. While using OR operator, complete condition will be assumed true when at least any of the conditions is true. For example, [condition1] OR [condition2] will be true if either condition1 or condition2 is true.

    Syntax

    Following is the basic syntax of OR operator with WHERE clause.

    SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

    You can combine N number of conditions using OR operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, only any ONE of the conditions separated by the OR must be TRUE.

    Example

    Consider COMPANY table with the following records.

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

    Following SELECT statement lists down all the records where AGE is greater than or equal to 25 OR salary is greater than or equal to 65000.00.

    sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQLite – UPDATE Query nhận dự án làm có lương

    SQLite – UPDATE Query

    SQLite UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows, otherwise all the rows would be updated.

    Syntax

    Following is the basic syntax of UPDATE query with WHERE clause.

    UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

    You can combine N number of conditions using AND or OR operators.

    Example

    Consider COMPANY table with the following records −

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

    Following is an example, which will update ADDRESS for a customer whose ID is 6.

    sqlite> UPDATE COMPANY SET ADDRESS = ''Texas'' WHERE ID = 6;

    Now, COMPANY table will have the following records.

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          Texas       45000.0
7           James       24          Houston     10000.0

    If you want to modify all ADDRESS and SALARY column values in COMPANY table, you do not need to use WHERE clause and UPDATE query will be as follows −

    sqlite> UPDATE COMPANY SET ADDRESS = ''Texas'', SALARY = 20000.00;

    Now, COMPANY table will have the following records −

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          Texas       20000.0
2           Allen       25          Texas       20000.0
3           Teddy       23          Texas       20000.0
4           Mark        25          Texas       20000.0
5           David       27          Texas       20000.0
6           Kim         22          Texas       20000.0
7           James       24          Texas       20000.0

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQLite – Expressions nhận dự án làm có lương

    SQLite – Expressions

    An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value.

    SQL expressions are like formulas and they are written in query language. You can also use to query the database for a specific set of data.

    Syntax

    Consider the basic syntax of the SELECT statement as follows −

    SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

    Following are the different types of SQLite expressions.

    SQLite – Boolean Expressions

    SQLite Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax −

    SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

    Consider COMPANY table with the following records −

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

    Following is a simple examples showing the usage of SQLite Boolean Expressions −

    sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           James        24          Houston   10000.0

    SQLite – Numeric Expression

    These expressions are used to perform any mathematical operation in any query. Following is the syntax −

    SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

    Here, numerical_expression is used for mathematical expression or any formula. Following is a simple example showing the usage of SQLite Numeric Expressions.

    sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21

    There are several built-in functions such as avg(), sum(), count(), etc., to perform what is known as aggregate data calculations against a table or a specific table column.

    sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
RECORDS = 7

    SQLite – Date Expressions

    Date Expressions returns the current system date and time values. These expressions are used in various data manipulations.

    sqlite> SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP = 2013-03-17 10:43:35

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQLite – GLOB Clause nhận dự án làm có lương

    SQLite – GLOB Clause

    SQLite GLOB operator is used to match only text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the GLOB operator will return true, which is 1. Unlike LIKE operator, GLOB is case sensitive and it follows syntax of UNIX for specifying THE following wildcards.

    • The asterisk sign (*)
    • The question mark (?)

    The asterisk sign (*) represents zero or multiple numbers or characters. The question mark (?) represents a single number or character.

    Syntax

    Following is the basic syntax of * and ?.

    SELECT FROM table_name
WHERE column GLOB ''XXXX*''
or
SELECT FROM table_name
WHERE column GLOB ''*XXXX*''
or
SELECT FROM table_name
WHERE column GLOB ''XXXX?''
or
SELECT FROM table_name
WHERE column GLOB ''?XXXX''
or
SELECT FROM table_name
WHERE column GLOB ''?XXXX?''
or
SELECT FROM table_name
WHERE column GLOB ''????''

    You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.

    Example

    Following table lists a number of examples showing WHERE part having different GLOB clause with ”*” and ”?” operators.

    Sr.No. Statement & Description
    1

    WHERE SALARY GLOB ”200*”

    Finds any values that start with 200
    2

    WHERE SALARY GLOB ”*200*”

    Finds any values that have 200 in any position
    3

    WHERE SALARY GLOB ”?00*”

    Finds any values that have 00 in the second and third positions
    4

    WHERE SALARY GLOB ”2??”

    Finds any values that start with 2 and are at least 3 characters in length
    5

    WHERE SALARY GLOB ”*2”

    Finds any values that end with 2
    6

    WHERE SALARY GLOB ”?2*3”

    Finds any values that have a 2 in the second position and end with a 3
    7

    WHERE SALARY GLOB ”2???3”

    Finds any values in a five-digit number that start with 2 and end with 3

    Let us take a real example, consider COMPANY table with the following records −

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

    Following is an example, which will display all the records from COMPANY table, where AGE starts with 2.

    sqlite> SELECT * FROM COMPANY WHERE AGE  GLOB ''2*

    This will produce the following result.

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

    Following is an example, which will display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text −

    sqlite> SELECT * FROM COMPANY WHERE ADDRESS  GLOB ''*-*

    This will produce the following result.

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQLite – LIKE Clause nhận dự án làm có lương

    SQLite – LIKE Clause

    SQLite LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. There are two wildcards used in conjunction with the LIKE operator −

    • The percent sign (%)
    • The underscore (_)

    The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. These symbols can be used in combinations.

    Syntax

    Following is the basic syntax of % and _.

    SELECT FROM table_name
WHERE column LIKE ''XXXX%''
or
SELECT FROM table_name
WHERE column LIKE ''%XXXX%''
or
SELECT FROM table_name
WHERE column LIKE ''XXXX_''
or
SELECT FROM table_name
WHERE column LIKE ''_XXXX''
or
SELECT FROM table_name
WHERE column LIKE ''_XXXX_''

    You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.

    Example

    Following table lists a number of examples showing WHERE part having different LIKE clause with ”%” and ”_” operators.

    Sr.No. Statement & Description
    1

    WHERE SALARY LIKE ”200%”

    Finds any values that start with 200
    2

    WHERE SALARY LIKE ”%200%”

    Finds any values that have 200 in any position
    3

    WHERE SALARY LIKE ”_00%”

    Finds any values that have 00 in the second and third positions
    4

    WHERE SALARY LIKE ”2_%_%”

    Finds any values that start with 2 and are at least 3 characters in length
    5

    WHERE SALARY LIKE ”%2”

    Finds any values that end with 2
    6

    WHERE SALARY LIKE ”_2%3”

    Finds any values that has a 2 in the second position and ends with a 3
    7

    WHERE SALARY LIKE ”2___3”

    Finds any values in a five-digit number that starts with 2 and ends with 3

    Let us take a real example, consider COMPANY table with the following records.

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

    Following is an example, which will display all the records from COMPANY table where AGE starts with 2.

    sqlite> SELECT * FROM COMPANY WHERE AGE LIKE ''2%

    This will produce the following result.

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

    Following is an example, which will display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text.

    sqlite> SELECT * FROM COMPANY WHERE ADDRESS  LIKE ''%-%

    This will produce the following result.

    ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQLite – ATTACH Database nhận dự án làm có lương

    SQLite – ATTACH Database

    Consider a case when you have multiple databases available and you want to use any one of them at a time. SQLite ATTACH DATABASE statement is used to select a particular database, and after this command, all SQLite statements will be executed under the attached database.

    Syntax

    Following is the basic syntax of SQLite ATTACH DATABASE statement.

    ATTACH DATABASE ''DatabaseName'' As ''Alias-Name

    The above command will also create a database in case the database is already not created, otherwise it will just attach database file name with logical database ”Alias-Name”.

    Example

    If you want to attach an existing database testDB.db, then ATTACH DATABASE statement would be as follows −

    sqlite> ATTACH DATABASE ''testDB.db'' as ''TEST

    Use SQLite .database command to display attached database.

    sqlite> .database
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db

    The database names main and temp are reserved for the primary database and database to hold temporary tables and other temporary data objects. Both of these database names exist for every database connection and should not be used for attachment, otherwise you will get the following warning message.

    sqlite> ATTACH DATABASE ''testDB.db'' as ''TEMP
Error: database TEMP is already in use
sqlite> ATTACH DATABASE ''testDB.db'' as ''main
Error: database TEMP is already in use

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQLite – Data Type nhận dự án làm có lương

    SQLite – Data Type

    SQLite data type is an attribute that specifies the type of data of any object. Each column, variable and expression has related data type in SQLite.

    You would use these data types while creating your tables. SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

    SQLite Storage Classes

    Each value stored in an SQLite database has one of the following storage classes −

    Sr.No. Storage Class & Description
    1

    NULL

    The value is a NULL value.
    2

    INTEGER

    The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
    3

    REAL

    The value is a floating point value, stored as an 8-byte IEEE floating point number.
    4

    TEXT

    The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)
    5

    BLOB

    The value is a blob of data, stored exactly as it was input.

    SQLite storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths.

    SQLite Affinity Type

    SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Each table column in an SQLite3 database is assigned one of the following type affinities −

    Sr.No. Affinity & Description
    1

    TEXT

    This column stores all data using storage classes NULL, TEXT or BLOB.
    2

    NUMERIC

    This column may contain values using all five storage classes.
    3

    INTEGER

    Behaves the same as a column with NUMERIC affinity, with an exception in a CAST expression.
    4

    REAL

    Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation.
    5

    NONE

    A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.

    SQLite Affinity and Type Names

    Following table lists down various data type names which can be used while creating SQLite3 tables with the corresponding applied affinity.

    Data Type Affinity
    • INT
    • INTEGER
    • TINYINT
    • SMALLINT
    • MEDIUMINT
    • BIGINT
    • UNSIGNED BIG INT
    • INT2
    • INT8
    		 INTEGER
    • CHARACTER(20)
    • VARCHAR(255)
    • VARYING CHARACTER(255)
    • NCHAR(55)
    • NATIVE CHARACTER(70)
    • NVARCHAR(100)
    • TEXT
    • CLOB
    		 TEXT
    • BLOB
    • no datatype specified
    		 NONE
    • REAL
    • DOUBLE
    • DOUBLE PRECISION
    • FLOAT
    		 REAL
    • NUMERIC
    • DECIMAL(10,5)
    • BOOLEAN
    • DATE
    • DATETIME
    		 NUMERIC

    Boolean Datatype

    SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

    Date and Time Datatype

    SQLite does not have a separate storage class for storing dates and/or times, but SQLite is capable of storing dates and times as TEXT, REAL or INTEGER values.

    Sr.No. Storage Class & Date Formate
    1

    TEXT

    A date in a format like “YYYY-MM-DD HH:MM:SS.SSS”
    2

    REAL

    The number of days since noon in Greenwich on November 24, 4714 B.C.
    3

    INTEGER

    The number of seconds since 1970-01-01 00:00:00 UTC

    You can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQLite – Home nhận dự án làm có lương

    SQLite tutorial

    SQLite Tutorial







    SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain. This tutorial will give you a quick start with SQLite and make you comfortable with SQLite programming.

    Audience

    This tutorial has been prepared for beginners to help them understand the basic-to-advanced concepts related to SQLite Database Engine.

    Prerequisites

    Before you start practicing various types of examples given in this reference, we assume that you are already aware about what is a database, especially RDBMS and what is a computer programming language.

    Frequently Asked Questions about SQLite

    There are some very Frequently Asked Questions(FAQ) about SQLite, this section tries to answer them briefly.

    SQLite is a database management system used to store and manage data in a structured format. It is often used in applications that need to save data locally on a device or computer, such as mobile apps, desktop software, and embedded systems. SQLite allows developers to create databases, organize data into tables, and perform operations like inserting, updating, and querying data.

    You should use SQLite because it is easy to use and does not require a separate server to run. It is perfect for applications that need to store data locally on a device, like mobile apps or desktop software. SQLite is also fast and reliable, making it a good choice for projects where simplicity and efficiency are important. Plus, since it is self-contained, you can easily distribute SQLite databases with your application without worrying about additional setup for users.

    SQLite requires minimal maintenance compared to other database management systems. Since it is self-contained and doesn”t require a separate server, you don”t need to worry about installing updates or managing server configurations. However, regular backups of your SQLite databases are recommended to prevent data loss in case of accidents or errors. Additionally, you may need to periodically optimize your databases to improve performance, especially if they become large or complex.

    SQLite databases can become locked to prevent multiple processes from simultaneously making changes that could conflict with each other. When a process accesses an SQLite database, it acquires a lock to ensure exclusive access to the database while performing operations like writing data or executing transactions. This locking mechanism helps maintain data integrity and prevents data corruption by ensuring that only one process can modify the database at a time. Once the process completes its operations, it releases the lock, allowing other processes to access the database.

    SQLite is a type of relational database management system (RDBMS). It allows users to store, manage, and retrieve data in a structured format organized into tables with rows and columns. Users can define relationships between different tables, perform queries to retrieve specific data, and execute transactions to ensure data integrity.

    SQLite is written in the C programming language. This means that the core functionality of SQLite, including its engine for managing databases, is implemented using C code. However, SQLite provides interfaces for many programming languages, allowing developers to interact with SQLite databases using languages like Python, Java, C++, and more.

    Yes, SQLite is open source, which means its source code is freely available for anyone to view, modify, and distribute. This openness allows developers to inspect SQLite”s code, contribute improvements, and use it in their projects without restrictions.

    SQLite is known for its speed and efficiency in handling database operations. It is designed to be lightweight and optimized for quick access to data, making it fast for reading and writing data. Because SQLite is self-contained and doesn”t require a separate server, there is minimal overhead in communication, resulting in faster response times for database queries and transactions.

    SQLite was invented by D. Richard Hipp. He created SQLite to provide a simple, lightweight, and efficient database solution that could be embedded into software applications without requiring a separate server. Hipp designed SQLite to be fast, reliable, and easy to use, making it suitable for a wide range of projects and environments.

    The time it takes to learn SQLite can vary depending on your familiarity with databases and SQL (Structured Query Language). If you are completely new to databases, it might take a few days to understand the basics of SQLite and how to perform common operations like creating tables, inserting data, and querying information. With consistent practice and experimentation, you can become proficient in SQLite within a few weeks or months. However, mastering more advanced features and optimizing database performance could take longer and might require additional learning and experience.

    The latest version of SQLite is 3.36.0. However, it is important to note that new versions may have been released. SQLite developers regularly release updates to improve performance, fix bugs, and add new features. To find the most current version of SQLite, you can visit the official SQLite website or check the release notes on their GitHub repository.

    Yes, you can use SQLite in a browser environment. There are various ways to do this, but one common method is to use JavaScript libraries or frameworks that provide SQLite functionality directly within a web browser. These libraries include a JavaScript implementation of SQLite or provide wrappers around SQLite that allow you to execute SQL queries and interact with SQLite databases directly from your web browser.

    This enables you to create web applications that use SQLite databases without needing a server-side database management system. However, it is important to note that browser-based SQLite implementations may have limitations compared to using SQLite in a traditional server environment.

    SQLite is popular for several reasons. They are as follows −

    • Simplicity − It is easy to set up and use, making it accessible to developers of all skill levels.

    • Portability − SQLite databases are self-contained files that can be easily shared and transferred between different systems, making it convenient for use in various environments.

    • No Server Required − Unlike traditional database management systems, SQLite does not require a separate server to run, reducing setup complexity and resource requirements.

    • Efficiency − SQLite is lightweight and optimized for performance, allowing for fast data access and manipulation, even in resource-constrained environments.

    • Flexibility − It supports a wide range of SQL features and data types, making it suitable for a variety of applications, from mobile apps to desktop software to embedded systems.

    SQLite databases are stored as single files on your computer or device. When you create a SQLite database, it is saved as a single file with a .sqlite or .db extension. This file contains all the tables, rows, and other database objects you”ve defined, as well as the data you have inserted into the database. You can move, copy, or share SQLite database files just like any other file on your computer.

    To connect to SQLite, you need to use a programming language that supports SQLite and provides libraries or modules for interacting with SQLite databases −

    • Install SQLite − First, you need to ensure SQLite is installed on your system. Most systems come with SQLite pre-installed, but if not, you can download and install it from the official SQLite website.

    • Choose a Programming Language − Decide which programming language you want to use to interact with SQLite. Popular choices include Python, Java, C/C++, and many others.

    • Install SQLite Library or Module − Install the SQLite library or module for your chosen programming language. These libraries/modules provide functions or classes to interact with SQLite databases.

    • Connect to the Database − Use the functions or methods provided by the SQLite library/module to connect to your SQLite database. Generally, you will need to specify the path to your SQLite database file when establishing the connection.

    • Perform Operations − Once connected, you can execute SQL queries, insert, update, delete data, and perform other database operations using the functions or methods provided by the SQLite library/module.

    SQLite is like a small, lightweight tool for managing data in simple applications. It struggles with lots of users accessing data at once and isn”t great for huge amounts of information. It is best for small projects where simplicity is more important than handling lots of data or users.

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQLite – Overview nhận dự án làm có lương

    SQLite – Overview

    This chapter helps you understand what is SQLite, how it differs from SQL, why it is needed and the way in which it handles the applications Database.

    SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines around, but that”s growth in terms of popularity, not anything to do with its size. The source code for SQLite is in the public domain.

    What is SQLite?

    SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a database, which is zero-configured, which means like other databases you do not need to configure it in your system.

    SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly.

    Why SQLite?

    • SQLite does not require a separate server process or system to operate (serverless).

    • SQLite comes with zero-configuration, which means no setup or administration needed.

    • A complete SQLite database is stored in a single cross-platform disk file.

    • SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted.

    • SQLite is self-contained, which means no external dependencies.

    • SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.

    • SQLite supports most of the query language features found in SQL92 (SQL2) standard.

    • SQLite is written in ANSI-C and provides simple and easy-to-use API.

    • SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT).

    SQLite A Brief History

    • 2000 – D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program.

    • 2000 – In August, SQLite 1.0 released with GNU Database Manager.

    • 2011 – Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database).

    SQLite Limitations

    There are few unsupported features of SQL92 in SQLite which are listed in the following table.

    Sr.No. Feature & Description
    1

    RIGHT OUTER JOIN

    Only LEFT OUTER JOIN is implemented.
    2

    FULL OUTER JOIN

    Only LEFT OUTER JOIN is implemented.
    3

    ALTER TABLE

    The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported.
    4

    Trigger support

    FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
    5

    VIEWs

    VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view.
    6

    GRANT and REVOKE

    The only access permissions that can be applied are the normal file access permissions of the underlying operating system.

    SQLite Commands

    The standard SQLite commands to interact with relational databases are similar to SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature −

    DDL – Data Definition Language

    Sr.No. Command & Description
    1

    CREATE

    Creates a new table, a view of a table, or other object in database.
    2

    ALTER

    Modifies an existing database object, such as a table.
    3

    DROP

    Deletes an entire table, a view of a table or other object in the database.

    DML – Data Manipulation Language

    Sr.No. Command & Description
    1

    INSERT

    Creates a record
    2

    UPDATE

    Modifies records
    3

    DELETE

    Deletes records

    DQL – Data Query Language

    Sr.No. Command & Description
    1

    SELECT

    Retrieves certain records from one or more tables

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí SQLite – DETACH Database nhận dự án làm có lương

    SQLite – DETACH Database

    SQLite DETACH DATABASE statement is used to detach and dissociate a named database from a database connection which was previously attached using ATTACH statement. If the same database file has been attached with multiple aliases, then DETACH command will disconnect only the given name and rest of the attachment will still continue. You cannot detach the main or temp databases.

    If the database is an in-memory or temporary database, the database will be destroyed and the contents will be lost.

    Syntax

    Following is the basic syntax of SQLite DETACH DATABASE ”Alias-Name” statement.

    DETACH DATABASE ''Alias-Name

    Here, ”Alias-Name” is the same alias, which you had used while attaching the database using ATTACH statement.

    Example

    Consider you have a database, which you created in the previous chapter and attached it with ”test” and ”currentDB” as we can see using .database command.

    sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db
3    currentDB        /home/sqlite/testDB.db

    Let”s try to detach ”currentDB” from testDB.db using the following command.

    sqlite> DETACH DATABASE ''currentDB

    Now, if you will check the current attachment, you will find that testDB.db is still connected with ”test” and ”main”.

    sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc