Sqlite3 often use commands & syntax

http://blog.csdn.net/linchunhua/article/details/7184439


SQLite database only with a 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 include multiple copies of SQLite for different applications.
PHP takes SQLite as a built-in database.
The Skypeclient software uses SQLite internally.
SymbianOS (the navigator of smartphone operating platform) built-in SQLite.
The AOL mail client is bound to SQLite.
Solaris 10 requires SQLite to be used during the boot process.
McAfee anti-virus software uses SQLite.
iphones uses SQLite.
Many mobile phone manufacturers, such as Symbian and Apple, use SQLite.
The following is an introduction to the frequently used commands and syntax in SQLite
http://www.sqlite.org/download.html can download the relevant version number of different operating systems SQLite gedit
You can also use the plugin in Firefox, SQLite Manager


New Database
Sqlite3 DatabaseFileName
Check if the databasefilename exists, create and enter the database if it does not exist (assuming that the database file is not created if you exit directly) assume that there is already a direct access to the database to operate the database


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


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


The. Mode list|column|insert|line|tabs|tcl|csv changes the output format, as detailed below


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
Assuming that 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;
10 ACCOUNTING NEW YORK
20 The DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


sqlite>. Mode Tcl
Sqlite> select * FROM dept;
"10" "ACCOUNTING""NEW YORK"
"20" "The""DALLAS"
"30" "SALES" "CHICAGO"
"40" "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 with the specified string instead of the output null string. Nullvalue "


Field type:


Each value stored in the database has a type that belongs to one of the following listed types (controlled by the database engine)
Null: This value is a null value
Integer: The value is identified as an integer and 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 the BLOB data, how the input is stored, does not change 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 checks, resolves types at run time, and converts between numeric storage types (integers and real numbers) and text types.
Text in a section of a SQL statement with a double-or single-argument is defined as text,
Suppose the literal is not quoted and no decimal point or exponent is defined as an integer,
Suppose the text is not quoted but has a decimal point or exponent that is defined as a real number,
If the value is NULL, it is defined as a null value.
BLOB data is identified by using the symbol X ' ABCD '.


In practice, however, Sqlite3 also accepts data types such as the following:
smallint a 16-bit integer.
Interger a 32-bit integer.
The decimal (p,s) exact value p means that all have several decimal digits, and S is the number of decimals that can be followed by a decimal point. Assuming there is no special designation, the system will feel 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), except that 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 includes the year, month, and date.
Time includes hours, minutes, seconds.
Timestamp includes one second of the year, month, day, time, minute, second, and thousand points.


SQLite includes, for example, the following time/date functions:
DateTime () produces a date and time without an indication of the current time and date
Sqlite> select DateTime ();
2012-01-07 12:01:32
A string parameter 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 participation and no participation
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, a string such as the following format can be used 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
Now is the time to produce.


Date is not the correct size, 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 is able to format dates and times with the following symbols:
%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
The number of seconds from January 1, 1970 to today in%s
%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. Starting with y read Z characters y minimum 1
Sqlite> Select substr (' ABCdef ', 3, 3);
Cde


QUOTE (A) Adding an argument to A string
Sqlite> Select quote (' AAA ');
' AAA '


Conditional inference function
Ifnull (x, y) assumes X is null to return 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 the 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 number 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 ' financial department '
When the ' house Department '
Else ' other departments '
End as Dept
From EMP;


Support for correlated subqueries in the following syntax can have limit (MySQL is not able)
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 repeated
Select Deptno from emp
Union
Select Deptno from dept;


Select Deptno from emp
UNION ALL
Select Deptno from dept;


Adding distinct in front of the column name is also going 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;


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


Build an index


Assuming that the table has quite a lot of data, we will build the index to heighten the speed. 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 will actively use the index when querying against the field. All this is done in the background of the initiative, without the user special instructions.


Other special use methods of SQLite


SQLite can run 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 often use commands &amp; syntax