Sqlite3 database_name.dbSELECTCREATEINSERTUPDATEDROPDELETESQLite uses SQLITE SECURE DELETE option which overwrites all deleted content with zeroes. CREATE TABLE statement is used to create a table in SQLite database. You have to define the columns and data types of each column while creating the table.CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
columnN datatype,
);autoincrement, you have to declare a column of the table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. INSERT INTO statement is used to insert data in a table in SQLite database. There are two ways to insert data in table :INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN); INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
SELECT command is used to retrieve data from SQLite table. If you want to retrieve all columns from the table use SELECT * otherwise use the specific column's name separated by commas.SELECT * FROM table_name;
Or
SELECT column1, column2, columnN FROM table_name;
UPDATE query is used to modify the existing records in the SQLite table. You have to use the WHERE clause to modify a specific row otherwise all rows will be updated.UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
SQLite, DELETE command is used to delete the existing records from a table. You should use the WHERE clause to choose the specific row otherwise all rows will be deleted.DELETE FROM table_name WHERE [conditions....................];
SELECT statement when we want a limited number of fetched records.SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]ORDER BY clause is used to sort the fetched data in a specific order either ascending or descending.SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];SQLite source code is not described by any ECCN. Hence, the ECCN should be reported as EAR99. But if you are adding new code or linking SQLite with the application, then it might change the EECN number. SQLite does not enforce the length of a VARCHAR. You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there. And it will keep all 500-million characters intact. Your content is never truncated. SQLite understands the column type of "VARCHAR(N)" to be the same as "TEXT", regardless of the value of N. SQLite allows you to store BLOB data in any column, even columns that are declared to hold some other type. BLOBs can even be used as PRIMARY KEYs. SQLite GROUP BY clause is used to collect the same elements into a group. It is used with SELECT statement.SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN DISTINCT clause is always used with SELECT statement. It is used to retrieve only unique records and restrict the duplicate entries.SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]SQLite UNION Operator is used to combine the result set of two or more tables using SELECT statement. Both the tables must have same number of fields in result table.SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];UNION ALL operator is used to combine the result of two or more tables using SELECT statement. The unique difference between UNION and UNION ALL operator is that UNION operator ignores the duplicate entries while combining the results while UNION ALL doesn't ignore duplicate values.SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION ALL
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];SQLite JOIN clause is used to combine two or more tables in a database. It combines the table by using the common values of the both table.JOINS supported in SQlite :SQLite INNER JOIN is simplest and most common join. It combines all rows from both tables where the condition is satisfied.SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expressionSQLite Cross join is used to match every rows of the first table with every rows of the second table. If the first table contains x columns and second table contains y columns then the resultant Cross join table will contain the x*y columns.SELECT ... FROM table1 CROSS JOIN table2ate and time () functions are used to retrieve current date and time and also do calculations on the dates and time.date and time () function in SQLite:ate() function is used to fetch the date and show it in 'YYYY-MM-DD' format.date(timestring, [ modifier1, modifier2, ... modifier_n ] )datetime() function is used to retrieve current date and time in 'YYYY-MM-DD HH:MM:SS' format.datetime(timestring, [ modifier1, modifier2, ... modifier_n ] )SELECT MIN(aggregate_expression)
FROM tables
[WHERE conditions];SQLite MAX aggregate function is used to fetch the maximum value of an expression.SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];SQLite AVG function returns the average value of the expression.SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];QLite COUNT function is used to retrieve total count of an expression.SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions]; SQLite SUM aggregate function is used to get the total summed value of an expression.SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];strftime() function is used to fetch date and time and also perform time and date calculation.strftime(format, timestring [, modifier1, modifier2, ... modifier_n ] ) time() function is used to fetch current time in 'HH-MM-SS' format.time(timestring, [ modifier1, modifier2, ... modifier_n ] )