Here we will learn some of basic sqlite syntaxes with examples and set of rules to use tables, comments, naming conventions, reserve keywords, etc. In sqlite with examples.
In SQLite we will follow certian format of rules and query statements to perform database related operations like create tables, get data from tables, comment code, etc. Called sqlite syntaxes.
Following are the certain common sqlite statement syntaxes which we will follow to perform operations on sqlite databases.
There are two ways to create a comment within your query statements using SQLite. First one that we know as single line comment and second one is multi-line comment.
Single line comment
We can create single line comment in SQLite using two symbol. You can either use – symbol or you can use # to create a single line comment.
-- Comment goes here
We can create multi-line comment in SQLite using /* */ symbols. For multiline comment, comment starts with /* and ends with */ symbol. Within these symbols, you have to write your comment.
Following is the example of using single-line and multi-line comments in sqlite query statements.
SELECT * FROM DEPT_MASTER -- Department Master
SELECT * FROM EMP_MASTER #Employee Master
SELECT * FROM DEPT_MASTER /* Department
A naming convention is a set of rules for choosing the character sequence to be used for identifiers which denote name of database, table, column, index, trigger, or view in SQLite.
An identifier name must begin with a letter or the underscore character, which is followed by any alpha numeric character or underscore. Other characters are not valid.
Following are example of valid identifiers.
Following are the example of some invalid identifiers.
However, you can use other character enclosed in double quotes, for example.
CREATE TABLE "table master"("column 1", " ");
SQLite does not have any upper limit for length of identifier name. You can use any length of identifier that may suitable for you.
If you want to use Reserved Keyword as an identifier in SQLite, you should have to take special care. As a general rule of thumb you should try to avoid using any keywords from the SQLite language as identifiers, although if you really want to do so, use special keyword enclosed within square brackets.
Following is example of using reserved keyword in sql statement.
CREATE TABLE [TABLE] (
SQLite Case Sensitivity
SQLite is a case insensitive. Table names and column names can be typed in uppercase, lowercase, or mixed case, and different capitalizations of the same database object name can be used interchangeably.
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 (;).
Creating and Dropping Tables
Creating and dropping tables in SQLite is done with the CREATE TABLE and DROP TABLE commands respectively.
Following is the basic syntax of CREATE TABLE
CREATE [TEMP | TEMPORARY] TABLE table-name
(Column-def [, column-def]*
[, constraint]* );
Following is the syntax of dropping table.
DROP TABLE databasename.tablename
Table Column Definitions
Following is the syntax of Table column.
name[type] [[CONSTRAINT name] column-constraint]*
Here name is any valid identifier; type can be any data type like INTEGER, VARCHAR etc. If you want to apply more than one constraint to column, then you can use optional CONSTRAINT keyword.
Here column constraints may be one of following
These are the some of rules and syntaxes to follow in SQLite while creating database, tables or constraints, etc. based on our requirements.