Sqlite3 Common Commands & Syntax

SQLite database with only one file is OK, small and convenient, so is a very good embedded database, SQLite is used in a large number of mobile phones, Pda,mp3 players and set-top box equipment.
Mozilla Firefox uses SQLite as a database.
Mac computers contain multiple copies of SQLite for different applications.
PHP takes SQLite as a built-in database.
The Skype client software uses SQLite internally.
SymbianOS (the navigator of smartphone operating platform) built-in SQLite.
The AOL mail client binds to SQLite.
Solaris 10 requires SQLite to be used during startup.
McAfee anti-virus software uses SQLite.
iphones uses SQLite.
Many mobile phone manufacturers, such as Symbian and Apple, use SQLite.
The following is a description of common commands and syntax in SQLite
http://www.sqlite.org/download.html can download related versions of different operating systems SQLite gedit
You can also use the plugin in Firefox, SQLite Manager


New Database
Sqlite3 DatabaseFileName
Check if databasefilename exists and create and enter the database if it does not exist (if you exit directly, the database file will not be created) if you already exist directly into the database to manipulate the database


In SQLite command:
Start with., Case sensitive (database object names are case insensitive)
. exit
. Help view assistance for commands
. Database displays information about the databases, and contains the location of the current database
The. Tables or. Table display table name does not appear without a table
The. Schema command can view SQL commands when creating data objects;
. Schema databaseobjectname the command to view SQL when the database object was created, and no error message if no content is displayed without this database object


. read FILENAME executes the SQL statement in the specified file
. Headers on/Off display table header default off


. Mode list|column|insert|line|tabs|tcl|csv Change the output format, as follows


sqlite>. Mode List
Sqlite> select * from EMP;
7369| smith| clerk|7902|17-12-1980|800| | 20
7499| allen| Salesman|7698|20-02-1981|1600|300|30
If the field value is NULL, the default is to display an empty string


sqlite>. Mode column
Sqlite> select * from EMP;
7369 SMITH Clerk 7902 17-12-1980 800 20
7499 ALLEN salesman 7698 20-02-1981 1600 300 30
7521 WARD salesman 7698 22-02-1981 1250 500 30


sqlite>. Mode Insert
Sqlite> select * FROM dept;
INSERT into Table VALUES (' ACCOUNTING ', ' NEW YORK ');
INSERT into Table VALUES ("DALLAS");
INSERT into Table VALUES (' SALES ', ' CHICAGO ');
INSERT into table VALUES (+, ' OPERATIONS ', ' BOSTON ');


sqlite>. Mode Line
Sqlite> select * FROM dept;
DEPTNO = 10
Dname = ACCOUNTING
LOC = NEW YORK


DEPTNO = 20
Dname = the
LOC = DALLAS


DEPTNO = 30
Dname = SALES
LOC = CHICAGO


DEPTNO = 40
Dname = OPERATIONS
LOC = BOSTON


sqlite>. Mode tabs
Sqlite> select * FROM dept;
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON


sqlite>. Mode Tcl
Sqlite> select * FROM dept;
"Ten" "ACCOUNTING" "NEW YORK"
"DALLAS", "the" "the"
"+" "SALES" "CHICAGO"
"OPERATIONS" "BOSTON"


sqlite>. Mode csv
Sqlite> select * FROM dept;
10,accounting, "NEW YORK"
20,research,dallas
30,sales,chicago
40,operations,boston


. Separator "x" Change the demarcation symbol to X
sqlite>. Separator ' * * '
Sqlite> select * FROM dept;
10**accounting** "NEW YORK"
20**research**dallas
30**sales**chicago
40**operations**boston


. Dump?            TABLE? Generate SQL scripts that form database tables
. Dump the script that generates the entire database is displayed in the terminal
. output stdout Print to screen default
. Output filename Prints the outputs to a file (. dump. Output combination to export the database to a file as an SQL statement)
. nullvalue string query when the specified string instead of the output null string defaults to. Nullvalue "


Field type:


Each value stored in the database has a type that belongs to one of the types listed below (controlled by the database engine)
Null: This value is a null value
Integer: The value is identified as an integer, which can be stored sequentially as 1,2,3,4,5,6,7,8 bytes, depending on the size of the value
REAL: All values are floating numeric values that are stored as 8-byte IEEE floating tag ordinals.
Text: Literal. The value is a text string that is stored using the database encoding (TUTF-8, utf-16be, or Utf-16-le).
Blob: The value is BLOB data, how to enter it on how to store it, without changing the format.


The value is defined why the type is only related to the value itself, has no relation to the column, and is not related to the variable. So SQLite is called a weakly typed database.
The database engine will examine, parse, and convert the type of numeric storage (integer and real) and text types at execution time.
Text in a section of a SQL statement with double or single quotation marks is defined as text.
If the text is not quoted and no decimal point or exponent is defined as an integer,
If the text is not quoted but the decimal point or exponent is defined as a real number,
If the value is empty, it is defined as a null value.
BLOB data is identified by using the symbol X ' ABCD '.


But in fact, Sqlite3 also accepts the following data types:
smallint a 16-bit integer.
Interger a 32-bit integer.
The decimal (p,s) exact value p means that there are a few decimal numbers, and S is the number of decimals that can be followed by a decimal point. If not specifically specified, the system defaults to p=5 s=0.
Float 32-bit real number.
A double 64-bit real number.
char (n) n-length string, n cannot exceed 254.
The varchar (n) length is not fixed and its maximum length is n, and N cannot exceed 4000.
Graphic (n) is the same as char (n), but its unit is two bytes and n cannot exceed 127. This pattern is intended to support two-byte-long fonts, such as Chinese text.
Vargraphic (n) variable-length double-character string with a maximum length of n, N cannot exceed 2000
Date contains the year, month, and date.
Time contains hours, minutes, seconds.
Timestamp contains the year, month, day, time, minute, second, 1 per thousand seconds.


SQLite includes the following time/date functions:
DateTime () produces date and time no parameter means get current time and date
Sqlite> select DateTime ();
2012-01-07 12:01:32
String argument converts the string to a date
Sqlite> Select DateTime (' 2012-01-07 12:01:30 ');
2012-01-07 12:01:30


Select Date (' 2012-01-08 ', ' +1 Day ', ' +1 Year ');
2013-01-09


Select DateTime (' 2012-01-08 00:20:00 ', ' +1 hour ', ' -12 minute ');
2012-01-08 01:08:00


Select DateTime (' Now ', ' start of Year ');
2012-01-01 00:00:00


Select DateTime (' Now ', ' start of Month ');
2012-01-01 00:00:00


Select DateTime (' Now ', ' start of Day ');
2012-01-08 00:00:00


Select DateTime (' Now ', ' start of Week '); error


Select DateTime (' Now ', ' localtime ');
Result: 2006-10-17 21:21:47


Date () dates generated
Sqlite> Select Date (' 2012-01-07 12:01:30 ');
2012-01-07
With the same argument and without reference
Select Date (' Now ', ' start of Year ');
2012-01-01


Select Date (' 2012-01-08 ', ' +1 month ');
2012-02-08


Time () generation
Select time ();
03:14:30


Select time (' 23:18:59 ');
23:18:59


Select time (' 23:18:59 ', ' start of Day ');
00:00:00


Select time (' 23:18:59 ', ' End of day '); error


In a time/Date function, you can use a string in the following format as a parameter:
Yyyy-mm-dd
YYYY-MM-DD hh:mm
YYYY-MM-DD HH:MM:SS
Yyyy-mm-dd HH:MM:SS. Sss
hh:mm
HH:MM:SS
HH:MM:SS. Sss
Now
Where now is the time to produce the present.


Date does not compare size correctly, will be compared by string, date default format dd-mm-yyyy
Select HireDate from emp order by HireDate;


17-11-1981
17-12-1980
19-04-1987
20-02-1981
22-02-1981


Strftime () Formats the date and time generated by the above three functions
The strftime () function converts date strings in YYYY-MM-DD HH:MM:SS format to other forms of strings.  Strftime (format, date/time, modifier, modifier, ...) Select Strftime ('%d ', DateTime ());
It can be formatted with the following symbols for dates and times:
%d the day ordinal of the month, 01-31
%f decimal form of the second, SS. Sss
%H hours, 00-23
%j figure out the day of the year, 001-366
%m month, 00-12
%M min, 00-59
%s number of seconds from January 1, 1970 to present
%s seconds, 00-59
%w Week, 0-6 (0 is Sunday)
%W calculates the day of the week that falls within that year, 01-53
%Y years, YYYY
Percent hundred percent semicolon


Select Strftime ('%y.%m.%d%h:%m:%s ', ' now ');
Select Strftime ('%y.%m.%d%h:%m:%s ', ' Now ', ' localtime ');
Result: 2006.10.17 21:41:09


Select HireDate from emp
Order by Strftime ('%y.%m.%d%h:%m:%s ', hiredate); That's right


Select Strftime ('%y.%m.%d%h:%m:%s ', HireDate) from EMP
Order by Strftime ('%y.%m.%d%h:%m:%s ', hiredate); Error




Arithmetic functions
ABS (X) returns the absolute value of the given numeric expression.
Max (x,y[,...]) returns the maximum value of an expression. Group function max (column name)
Sqlite> select Max (2,3,4,5,6,7,12);
12


Min (x,y[,...]) returns the minimum value of an expression.
Random () returns an arbitrary number.
Sqlite> select Random ();
3224224213599993831

Round (X[,y]) returns a numeric expression that is rounded to the specified length or precision.


Character processing functions
Length (X) returns the number of characters for the given string expression.
Lower (X) returns a character expression after converting the uppercase character data to lowercase characters.
Upper (X) returns the character expression that converts the lowercase character data to uppercase.
substr (x, y, z) returns part of an expression. Read Z characters y minimum 1 starting from Y
Sqlite> Select substr (' ABCdef ', 3, 3);
Cde


QUOTE (A) to enclose A string
Sqlite> Select quote (' AAA ');
' AAA '


Conditional Judgment function
Ifnull (x, y) if X is null returns Y
Select Ifnull (comm,0) from EMP;
0
300
500
0
1400




Aggregate functions
AVG (X) returns the average of the values in the group.
COUNT (X) returns the number of items in the group.
Max (X) returns the maximum value of the value in the group.
MIN (X) returns the minimum value of the value in the group.
SUM (X) returns the sum of all values in an expression.


Other functions
typeof (X) returns the type of data.
Sqlite> Select typeof (111);
Integer
Sqlite> Select typeof (' 233 ');
Text
Sqlite> Select typeof (' 2012-12-12 ');
Text
Sqlite> Select typeof (' 223.44 ');
Text
Sqlite> Select typeof (223.44);
Real


Last_insert_rowid () returns the ID of the last inserted data.
Sqlite_version () returns the version of SQLite.
Sqlite> select Sqlite_version ();
3.7.9


Change_count () returns the number of rows affected by the previous statement.
Last_statement_change_count ()


CREATE TABLE Emp_bak SELECT * from EMP; not available in SQLite


Inserting records
INSERT INTO table_name values (field1, Field2, field3 ...);
Inquire
SELECT * FROM table_name; View all records in the TABLE_NAME table;
SELECT * FROM table_name where field1= ' xxxxx '; Querying records that meet specified criteria;


Select .....
From Table_name[,table_name2,...]
Where .....
Group by ....
Having ....
ORDER BY ...


Select .....
From table_name INNER JOIN | Left OUTER JOIN | Right outer join table_name2
On ...
Where .....
Group by ....
Having ....
ORDER BY ...


Sub-query:
SELECT *
From EMP m
where sal>
(select AVG (SAL) from EMP where Deptno=m.deptno);


Support for case and then syntax
Update EMP
Set sal=
(
Case
When deptno=10 and job= ' MANAGER ' then sal*1.1
When Deptno=20 and job= ' clerk ' then sal*1.2
When deptno=30 then sal*1.1
When deptno=40 then sal*1.2
else SAL
END
);


Select Ename,
Case DEPTNO
When ten then ' logistics '
When the ' Finance Department '
When the ' house Department '
Else ' other departments '
End as Dept
From EMP;


Supports correlated subqueries in the following syntax can have limit (MySQL is not allowed)
SELECT *
From EMP E
where E.empno in
(
Select Empno
From EMP
where Deptno=e.deptno
ORDER BY SAL Desc
Limit 0,2
);


Support for operations such as data merging between tables and tables
Union to repeat union all without removing duplicates
Select Deptno from emp
Union
Select Deptno from dept;


Select Deptno from emp
UNION ALL
Select Deptno from dept;


Adding distinct before the column name is also to repeat
Sqlite> SELECT DISTINCT deptno from EMP;




Delete
Delete from table_name where ...


Delete a table
Drop table_name; Delete a table;
Drop index_name; Delete index;


Modify
UPDATE table_name
Set xxx=value[, Xxx=value,...]
where ...


Build an index


If the table has quite a lot of information, we will build the index to speed up. Like saying:


Create index Film_title_index on film (title);
This means that for the name field of the film table, an index named Film_name_index is created. The syntax of this instruction is


CREATE [UNIQUE] nonclustered INDEX index_name
On {table | view} (column [ASC | DESC] [,... N])
CREATE INDEX index_name on table_name (field_to_be_indexed);
Once an index is established, Sqlite3 automatically uses the index when it makes a query against that field. All of this is done automatically behind the scenes without special instructions from the user.


Other special uses of SQLite


SQLite can execute commands directly under the shell:
Sqlite3 film.db "SELECT * from EMP;"


Output HTML table:
sqlite3-html film.db "select * from film;"
To "pour out" the database:


Sqlite3 film.db ". Dump" > Output.sql
Using the output data, create an identical database (plus the above directive, which is the standard SQL database backup):


Sqlite3 Film.db < Output.sql
When inserting a large amount of data, you may need to call this command first:


Begin
After inserting the data, remember to call this command, the data will be written into the database:
Commit


Sqlite> begin;
sqlite> insert into AAAA values (' AAA ', ' 333 ');
Sqlite> select * from AAAA;
2|sdfds
Sdfsd|9
2012-12-12|13:13:13
aaa|333
sqlite> rollback;
Sqlite> select * from AAAA;
2|sdfds
Sdfsd|9
2012-12-12|13:13:13


Creating and deleting views
CREATE VIEW View_name as
SELECT column_name (s)
From table_name
WHERE condition
DROP VIEW view_name


Create View E As
Select AVG (SAL) Avgsal,deptno
From EMP
Group BY DEPTNO;


Select Ename,emp. Deptno,sal,avgsal
From EMP INNER JOIN E
On EMP. Deptno=e.deptno
where sal>avgsal;

Practice Employee Table:

PRAGMA Foreign_keys=off;
BEGIN TRANSACTION;
CREATE TABLE DEPT
(
DEPTNO Int (2) is not NULL,
Dname varchar (14),
LOC varchar (13)
);
INSERT into "DEPT" VALUES (' ACCOUNTING ', ' NEW YORK ');
INSERT into "DEPT" VALUES ("DALLAS");
INSERT into "DEPT" VALUES (+, ' SALES ', ' CHICAGO ');
INSERT into "DEPT" VALUES (+, ' OPERATIONS ', ' BOSTON ');
CREATE TABLE EMP
(
EMPNO Int (4) is not NULL,
ename varchar (10),
JOB varchar (9),
MGR Int (4),
HireDate Date,
SAL Int (7),
COMM Int (7),
DEPTNO Int (2)
);
INSERT into "EMP" VALUES (7369, ' SMITH ', ' clerk ', 7902, ' 17-12-1980 ', 800,null,20);
INSERT into "EMP" VALUES (7499, ' ALLEN ', ' salesman ', 7698, ' 20-02-1981 ', 1600,300,30);
INSERT into "EMP" VALUES (7521, ' WARD ', ' salesman ', 7698, ' 22-02-1981 ', 1250,500,30);
INSERT into "EMP" VALUES (7566, ' JONES ', ' MANAGER ', 7839, ' 02-04-1981 ', 2975,null,20);
INSERT into "EMP" VALUES (7654, ' MARTIN ', ' salesman ', 7698, ' 28-09-1981 ', 1250,1400,30);
INSERT into "EMP" VALUES (7698, ' BLAKE ', ' MANAGER ', 7839, ' 01-05-1981 ', 2850,null,30);
INSERT into "EMP" VALUES (7782, ' CLARK ', ' MANAGER ', 7839, ' 09-06-1981 ', 2450,null,10);
INSERT into "EMP" VALUES (7788, ' SCOTT ', ' ANALYST ', 7566, ' 19-04-1987 ', 3000,null,20);
INSERT into "EMP" VALUES (7839, ' KING ', ' president ', NULL, ' 17-11-1981 ', 5000,null,10);
INSERT into "EMP" VALUES (7844, ' TURNER ', ' salesman ', 7698, ' 08-09-1981 ', 1500,0,30);
INSERT into "EMP" VALUES (7876, ' ADAMS ', ' clerk ', 7788, ' 23-05-1987 ', 1100,null,20);
INSERT into "EMP" VALUES (7900, ' JAMES ', ' clerk ', 7698, ' 03-12-1981 ', 950,null,30);
INSERT into "EMP" VALUES (7902, ' FORD ', ' ANALYST ', 7566, ' 03-12-1981 ', 3000,null,20);
INSERT into "EMP" VALUES (7934, ' MILLER ', ' clerk ', 7782, ' 23-01-1982 ', 1300,null,10);
CREATE TABLE Salgrade
(
GRADE int,
Losal int,
hisal int
);
INSERT into "Salgrade" VALUES (1,700,1200);
INSERT into "Salgrade" VALUES (2,1201,1400);
INSERT into "Salgrade" VALUES (3,1401,2000);
INSERT into "Salgrade" VALUES (4,2001,3000);
INSERT into "Salgrade" VALUES (5,3001,9999);
COMMIT;

Sqlite3 Common Commands & Syntax