Category: sqlite

SQLite – Indexes ”; Previous Next Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers. An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data. Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order. Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there”s an index. The CREATE INDEX Command Following is the basic syntax of CREATE INDEX. CREATE INDEX index_name ON table_name; Single-Column Indexes A single-column index is one that is created based on only one table column. The basic syntax is as follows − CREATE INDEX index_name ON table_name (column_name); Unique Indexes Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows − CREATE UNIQUE INDEX index_name on table_name (column_name); Composite Indexes A composite index is an index on two or more columns of a table. The basic syntax is as follows − CREATE INDEX index_name on table_name (column1, column2); Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query”s WHERE clause as filter conditions. Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice. Implicit Indexes Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints. Example Following is an example where we will create an index in COMPANY table for salary column − sqlite> CREATE INDEX salary_index ON COMPANY (salary); Now, let”s list down all the indices available in COMPANY table using .indices command as follows − sqlite> .indices COMPANY This will produce the following result, where sqlite_autoindex_COMPANY_1 is an implicit index which got created when the table itself was created. salary_index sqlite_autoindex_COMPANY_1 You can list down all the indexes database wide as follows − sqlite> SELECT * FROM sqlite_master WHERE type = ”index”; The DROP INDEX Command An index can be dropped using SQLite DROP command. Care should be taken when dropping an index because performance may be slowed or improved. Following is the basic syntax is as follows − DROP INDEX index_name; You can use the following statement to delete previously created index. sqlite> DROP INDEX salary_index; When Should Indexes Be Avoided? Although indexes are intended to enhance the performance of a database, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered. Indexes should not be used in − Small tables. Tables that have frequent, large batch update or insert operations. Columns that contain a high number of NULL values. Columns that are frequently manipulated. Print Page Previous Next Advertisements ”;

SQLite – ATTACH Database ”; Previous Next 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 Print Page Previous Next Advertisements ”;

SQLite – Syntax ”; Previous Next SQLite is followed by unique set of rules and guidelines called Syntax. This chapter lists all the basic SQLite Syntax. Case Sensitivity The important point to be noted is that SQLite is case insensitive, i.e. the clauses GLOB and glob have the same meaning in SQLite statements. Comments SQLite comments are extra notes, which you can add in your SQLite code to increase its readability and they can appear anywhere; whitespace can occur, including inside expressions and in the middle of other SQL statements but they cannot be nested. SQL comments begin with two consecutive “-” characters (ASCII 0x2d) and extend up to and including the next newline character (ASCII 0x0a) or until the end of input, whichever comes first. You can also use C-style comments, which begin with “/*” and extend up to and including the next “*/” character pair or until the end of input, whichever comes first. C-style comments can span multiple lines. sqlite> .help — This is a single line comment SQLite Statements All the SQLite statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., and all the statements end with a semicolon (;). SQLite ANALYZE Statement ANALYZE; or ANALYZE database_name; or ANALYZE database_name.table_name; SQLite AND/OR Clause SELECT column1, column2….columnN FROM table_name WHERE CONDITION-1 {AND|OR} CONDITION-2; SQLite ALTER TABLE Statement ALTER TABLE table_name ADD COLUMN column_def…; SQLite ALTER TABLE Statement (Rename) ALTER TABLE table_name RENAME TO new_table_name; SQLite ATTACH DATABASE Statement ATTACH DATABASE ”DatabaseName” As ”Alias-Name”; SQLite BEGIN TRANSACTION Statement BEGIN; or BEGIN EXCLUSIVE TRANSACTION; SQLite BETWEEN Clause SELECT column1, column2….columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2; SQLite COMMIT Statement COMMIT; SQLite CREATE INDEX Statement CREATE INDEX index_name ON table_name ( column_name COLLATE NOCASE ); SQLite CREATE UNIQUE INDEX Statement CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,…columnN); SQLite CREATE TABLE Statement CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); SQLite CREATE TRIGGER Statement CREATE TRIGGER database_name.trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN stmt1; stmt2; …. END; SQLite CREATE VIEW Statement CREATE VIEW database_name.view_name AS SELECT statement….; SQLite CREATE VIRTUAL TABLE Statement CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log ); or CREATE VIRTUAL TABLE database_name.table_name USING fts3( ); SQLite COMMIT TRANSACTION Statement COMMIT; SQLite COUNT Clause SELECT COUNT(column_name) FROM table_name WHERE CONDITION; SQLite DELETE Statement DELETE FROM table_name WHERE {CONDITION}; SQLite DETACH DATABASE Statement DETACH DATABASE ”Alias-Name”; SQLite DISTINCT Clause SELECT DISTINCT column1, column2….columnN FROM table_name; SQLite DROP INDEX Statement DROP INDEX database_name.index_name; SQLite DROP TABLE Statement DROP TABLE database_name.table_name; SQLite DROP VIEW Statement DROP INDEX database_name.view_name; SQLite DROP TRIGGER Statement DROP INDEX database_name.trigger_name; SQLite EXISTS Clause SELECT column1, column2….columnN FROM table_name WHERE column_name EXISTS (SELECT * FROM table_name ); SQLite EXPLAIN Statement EXPLAIN INSERT statement…; or EXPLAIN QUERY PLAN SELECT statement…; SQLite GLOB Clause SELECT column1, column2….columnN FROM table_name WHERE column_name GLOB { PATTERN }; SQLite GROUP BY Clause SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name; SQLite HAVING Clause SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition); SQLite INSERT INTO Statement INSERT INTO table_name( column1, column2….columnN) VALUES ( value1, value2….valueN); SQLite IN Clause SELECT column1, column2….columnN FROM table_name WHERE column_name IN (val-1, val-2,…val-N); SQLite Like Clause SELECT column1, column2….columnN FROM table_name WHERE column_name LIKE { PATTERN }; SQLite NOT IN Clause SELECT column1, column2….columnN FROM table_name WHERE column_name NOT IN (val-1, val-2,…val-N); SQLite ORDER BY Clause SELECT column1, column2….columnN FROM table_name WHERE CONDITION ORDER BY column_name {ASC|DESC}; SQLite PRAGMA Statement PRAGMA pragma_name; For example: PRAGMA page_size; PRAGMA cache_size = 1024; PRAGMA table_info(table_name); SQLite RELEASE SAVEPOINT Statement RELEASE savepoint_name; SQLite REINDEX Statement REINDEX collation_name; REINDEX database_name.index_name; REINDEX database_name.table_name; SQLite ROLLBACK Statement ROLLBACK; or ROLLBACK TO SAVEPOINT savepoint_name; SQLite SAVEPOINT Statement SAVEPOINT savepoint_name; SQLite SELECT Statement SELECT column1, column2….columnN FROM table_name; SQLite UPDATE Statement UPDATE table_name SET column1 = value1, column2 = value2….columnN=valueN [ WHERE CONDITION ]; SQLite VACUUM Statement VACUUM; SQLite WHERE Clause SELECT column1, column2….columnN FROM table_name WHERE CONDITION; Print Page Previous Next Advertisements ”;

SQLite – CREATE Table ”; Previous Next SQLite CREATE TABLE statement is used to create a new table in any of the given database. Creating a basic table involves naming the table and defining its columns and each column”s data type. Syntax Following is the basic syntax of CREATE TABLE statement. CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ….. columnN datatype ); CREATE TABLE is the keyword telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Optionally, you can specify database_name along with table_name. Example Following is an example which creates a COMPANY table with ID as the primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table. sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Let us create one more table, which we will use in our exercises in subsequent chapters. sqlite> CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL ); You can verify if your table has been created successfully using SQLite command .tables command, which will be used to list down all the tables in an attached database. sqlite>.tables COMPANY DEPARTMENT Here, you can see the COMPANY table twice because its showing COMPANY table for main database and test.COMPANY table for ”test” alias created for your testDB.db. You can get complete information about a table using the following SQLite .schema command. sqlite>.schema COMPANY CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Print Page Previous Next Advertisements ”;