Here we will learn some of the 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 a certain format of rules and query statements to perform database related operations like create tables, get data from tables, comment code, etc. Called SQLite syntaxes.
The 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. The first one that we know as a single-line comment and second one is a multi-line comment.
Single line comment
We can create a single-line comment in SQLite using two symbols. You can either use – symbol or you can use # to create a single line comment.
-- Comment goes here
Multiline comment
We can create multi-line comment in SQLite using /* */ symbols. For multiline comment, the comment starts with /* and ends with */ symbol. Within these symbols, you have to write your comment.
/* Comment
Goes
here*/
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
Master Table/*
A naming convention is a set of rules for choosing the character sequence to be used for identifiers that denote the name of a database, table, column, index, trigger, or view in SQLite.
Valid Characters
An identifier name must begin with a letter or the underscore character, which is followed by any alphanumeric character or underscore. Other characters are not valid.
The following are examples of valid identifiers.
The following are the example of some invalid identifiers.
However, you can use other characters enclosed in double quotes, for example.
CREATE TABLE "table master"("column 1", " ");
Name Length
SQLite does not have any upper limit for the length of the identifier name. You can use any length of identifier that may suitable for you.
Reserved Keywords
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 an example of using a reserved keyword in SQL statement.
CREATE TABLE [TABLE] (
[SELECT],
[INTEGER] INTEGER,
[FROM],
[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.
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 (;).
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 a 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 the column, then you can use an 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 the database, tables or constraints, etc. based on our requirements.