Basic use of SQLite

SqliteCreate a table

The SQLite Create table statement is used for creating a new table in any given database. Create a basic table that involves naming tables, defining columns, and data types for each column.

Grammar

The basic syntax for the CREATE TABLE statement is as follows:

CREATE TABLE database_name.table_name (   column1 datatype  PRIMARY KEY (one or more columns),   column2 datatype ,   column3 datatype,   ...   .. COLUMNN datatype,);

A CREATE table is a keyword that tells the database system to create a new table. The CREATE table statement is followed by the unique name or identity of the table. You can also choose to specify a database_namewith table_name .

SqliteDelete a table

The Drop table statement from SQLite is used to delete the table definition and all its related data, indexes, triggers, constraints, and permission specifications for the table.

Use this command with special care, because once a table is deleted, all information in the table will be lost forever.

Grammar

The basic syntax for the DROP TABLE statement is as follows. You can optionally specify a database name with a table name, as follows:

DROP TABLE database_name.  table_name;  

SQLite Insert Statement

The INSERT into statement for SQLite is used to add new rows of data to a table in the database

Grammar

The INSERT into statement has two basic syntaxes, as follows:

(column1, column2, column3,... columnn)] values(value1, value2, value3,... Valuen);                 

Here, Column1, Column2,... ColumnN is the name of the column in the table where you want to insert the data.

If you want to add values for all the columns in the table, you can also not need to specify the column names in the SQLite query. However, make sure that the order of values is consistent with the order of the columns in the table. The INSERT into syntax for SQLite is as follows:

Insert into TABLE_NAME Values(value1,value2,value3,... Valuen);

Use one table to populate another table

 You can populate the data into another table by using a SELECT statement on a table that has a set of fields. Here is the syntax: 
 
   insert INTO  first_table_name [(column1, Column2,  ...  Columnn)]  select             column1 Column2          

... columnn  fromsecond_table_name[wherecondition];   

SqliteSelect Statement

SQLite's SELECT statement is used to fetch data from the SQLite database table and return the data as a result table. These result tables are also known as result sets.

Grammar

The basic syntax for SQLite's SELECT statement is as follows:

Select Column1, Column2, COLUMNN from table_name;

Here, Column1, Column2 ... Are the fields of the table, and their values are what you want to get. If you want to get all the available fields, you can use the following syntax:

SELECT * FROM table_name;

SqliteUpdate Statement

SQLite's UPDATE query is used to modify existing records in the table. You can update the selected row with an update query with a WHERE clause, or all rows will be updated.

Grammar

The basic syntax for an UPDATE query with a WHERE clause is as follows:

= value1,= value2 ....,=[condition];        

You can use the and or or operators to combine N number of conditions.

SqliteDelete Statement

The delete query for SQLite deletes records that are already in the table. You can delete a selected row by using a delete query with a WHERE clause, or all records will be deleted.

Grammar

The basic syntax for a DELETE query with a WHERE clause is as follows:

[condition]; 

You can use the and or or operators to combine N number of conditions.

SqliteWhere clause

The WHERE clause of SQLite is used to specify conditions for fetching data from one table or multiple tables.

If the given condition is true (true), a specific value is returned from the table. You can use the WHERE clause to filter records and get only the records you need.

The WHERE clause is not only available in the SELECT statement, it can also be used in UPDATE, DELETE statements, and so on, which we will learn in subsequent chapters.

Grammar

The basic syntax for SQLite's SELECT statement with a WHERE clause is as follows:

SELECT column1, column2,[condition]     

Sqliteand/or operator

SQLite's and and or operators are used to compile multiple conditions to narrow down the data selected in the SQLite statement. These two operators are called join operators.

These operators provide the possibility of multiple comparisons between different operators in the same SQLite statement.

And operator

The AND operator allows the existence of multiple conditions in the WHERE clause of an SQL statement. When you use the AND operator, the entire condition is true only if all conditions are true (true). For example, [Condition1] and [Condition2] is true only if both Condition1 and Condition2 are true (true).

Grammar

The basic syntax for the AND operator with a WHERE clause is as follows:

SELECT column1, column2,[condition1][condition2] ... [conditionn];           

You can use the AND operator to combine N number of conditions. The action that the SQLite statement needs to perform is that all conditions separated by and must be true (true), whether it is a transaction or a query.

SqliteLike clause

The like operator of SQLite is the literal value used to match the wildcard specified pattern. If the search expression matches the pattern expression, the LIKE operator returns True (TRUE), which is 1. Here are two wildcard characters to use with the LIKE operator:

• Percent percent (%)

• Underline (_)

A percent symbol (%) represents 0, one or more digits or characters. An underscore (_) represents a single number or character. These symbols can be used in combination.

Grammar

The basic syntax for% and _ is as follows:

' xxxx% 'or'%xxxx% 'or 'xxxx_ ' or ' _xxxx ' or '_xxxx_ '    

You can use the and or or operators to combine N number of conditions. Here, XXXX can be any number or string value.

Instance

The following examples demonstrate different places like clauses with the '% ' and ' _ ' operators:

Statement	Description
WHERE SALARY like ' 200% '	Find any value starting with 200
WHERE SALARY like '%200% '	Find any value that contains 200 in any location
WHERE SALARY like ' _00% '	Find any value for the second and third digits 00
WHERE SALARY like ' 2_%_% '	Find any value starting with 2 with a length of at least 3 characters
WHERE SALARY like '%2 '	Find any value ending with 2
WHERE SALARY like ' _2%3 '	Find any value with a second digit of 2 and ending with 3
WHERE SALARY like ' 2___3 '	Finds any value that has a length of 5 digits and ends with 3, starting with 2

SqliteGLOB clause

The GLOB operator of SQLite is the literal value used to match the wildcard specified pattern. If the search expression matches the pattern expression, the GLOB operator returns True (TRUE), which is 1. Unlike the LIKE operator, GLOB is case-sensitive and follows the UNIX syntax for the following wildcard character.

• asterisk (*)

• Question mark (?)

An asterisk (*) represents 0, one, or more digits or characters. The question mark (?) represents a single number or character. These symbols can be used in combination.

Grammar

and? The basic syntax is as follows:

' xxxx* 'or' *xxxx* 'or' XXXX? ' or'? XXXX 'or'? XXXX? ' or'???? '         

You can use the and or or operators to combine N number of conditions. Here, XXXX can be any number or string value.

Instance

The following examples demonstrate different GLOB clauses with the ' * ' and '? ' Operators:

Statement	Description
WHERE SALARY GLOB ' 200* '	Find any value starting with 200
WHERE SALARY GLOB ' *200* '	Find any value that contains 200 in any location
WHERE SALARY GLOB '? 00* '	Find any value for the second and third digits 00
WHERE SALARY GLOB ' 2?? '	Find any value starting with 2 with a length of at least 3 characters
WHERE SALARY GLOB ' * * *	Find any value ending with 2
WHERE SALARY GLOB '? 2*3 '	Find any value with a second digit of 2 and ending with 3
WHERE SALARY GLOB ' 2??? 3 '	Finds any value that has a length of 5 digits and ends with 3, starting with 2

Basic use of SQLite