SQLite Learning Notes

The data type in 1.java and the corresponding relationship in SQLite

Boolean for INTEGER (SQLite does not have a separate Boolean storage type, but instead stores Boolean values as integers 0 (false) and 1 (true).)
     Byte corresponds to INTEGER
     Short corresponds to INTEGER
     Integer corresponds to INTEGER
     Long corresponds to INTEGER

     Float corresponds to REAL
     Double for REAL

     String for TEXT

     Byte [] corresponds to BLOB

     Date corresponds to INTEGER / TEXT / REAL

     [Please refer to more details] (http://www.oschina.net/translate/data-types-in-sqlite-version-3?cmp)
2.sqlite database import and export: .dump command

2.sqlite database Import and export:. Dump command

• $sqlite 3 testdb.db. Dump > Testdb.sql
  ==> to dump the contents of the Testdb.db database into an ASCII text file Testdb.sql

• $sqlite 3 Testdb.db < Testdb.sql
  ==> recovering (inserting) data from Testdb.sql into the database testdb.db

3.sqlite CREATE TABLE:

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

The. Tables command in the 4.SQLIte command to verify that the table was created successfully

sqlite>.tables  COMPANY   DEPARTMENT

5.SQLite Delete a table

sqlite>DROP TABLE COMPANY;--删除表sqlite>.tables；查看删除后的表

6.SQLite Insert Statement

To create a table:

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Inserting data

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, ‘Paul‘, 32, ‘California‘, 20000.00 );

All fields can also be inserted:

INSERT INTO COMPANY VALUES (7, ‘James‘, 24, ‘Houston‘, 10000.00 );

You can also use one table to populate another table

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
SELECT column1, column2, ...columnN 
FROM second_table_name
[WHERE condition];

7.SQLite logical operators

• and
  
  • The AND operator allows the existence of multiple conditions in the WHERE clause of an SQL statement.
  • Sqlite> SELECT * from company WHERE age >= and SALARY >= 65000;
• between
  
  • The between operator is used to search for a value within a range of values for a given minimum and maximum value.
  • Qlite> SELECT * from company WHERE age between and 27;
• EXISTS
  
  • The EXISTS operator is used to search for the existence of a row in a specified table that satisfies certain criteria.
  • Sqlite> Select age from Company where EXISTS (select age from Company where SALARY > 65000);
  • Sqlite> SELECT * FROM company where-age > (select-from-company where SALARY > 65000);
• inch
  
  • The in operator is used to compare a value to a series of values in a specified list.
  • Sqlite> SELECT * from company WHERE Age in (25, 27);
• not in
  
  • The opposite of the not-in operator, which is used to compare a value to a value that is not within a series of specified lists.
  • Sqlite> SELECT * FROM company WHERE age is not in (25, 27);
• like
  
  • The LIKE operator is used to compare a value to a similar value using a wildcard operator.
  • Sqlite> SELECT * FROM company WHERE NAME is like ' ki% ';
  • ' ki% ' indicates that name begins with Ki (also ' ki* ', followed by similar), '%ki ' indicates that name is terminated with Ki, '%ki% ' indicates that the middle of name contains Ki
• GLOB
  
  • The GLOB operator is used to compare a value to a similar value using a wildcard operator. The difference between GLOB and like is that it is case-sensitive.
  • Sqlite> SELECT * from company WHERE NAME GLOB ' ki* ';
• not
  
  • The NOT operator is the opposite of the logical operator used. such as not EXISTS, not between, not in, and so on. It is a negation operator.
• OR
  
  • The OR operator is used to combine multiple conditions in a WHERE clause of an SQL statement.
  • Sqlite> SELECT * from company WHERE age >= OR SALARY >= 65000;
• is NULL
  
  • The null operator is used to compare a value to a null value.
• is not
  
  • The is not operator is similar to! =.
  • Sqlite> SELECT * FROM company WHERE age is not NULL;
• is
  
  • The IS operator is similar to =.
• ||
  
  • Connect two different strings and get a new string.
• UNIQUE
  
  • The unique operator searches for each row in the specified table, ensuring uniqueness (No duplicates).

8.SQLite Update Statement

Update customer address 6
sqlite> UPDATE COMPANY SET ADDRESS = ‘Texas’ WHERE ID = 6;

Modify all values of the ADDRESS and SALARY columns in the COMPANY table
sqlite> UPDATE COMPANY SET ADDRESS = ‘Texas’, SALARY = 20000.00;

9.SQLite Delete Statement

Remove customer with ID 7
sqlite> DELETE FROM COMPANY WHERE ID = 7;

Delete all records from the COMPANY table
sqlite> DELETE FROM COMPANY;

10.SQLite LIKE clause

SQLite's LIKE operator is a text value that matches a wildcard specified pattern. If the search expression matches the pattern expression, the LIKE operator returns true, which is 1.
Here are two wildcard characters used with the LIKE operator:
Percent sign (%)
Underscore (_)
A percent sign (%) represents zero, one, or more digits or characters. The underscore (_) represents a single number or character. These symbols can be used in combination.

SELECT FROM table_name
WHERE column LIKE ‘XXXX%’

or

SELECT FROM table_name
WHERE column LIKE ‘% XXXX%’

or

SELECT FROM table_name
WHERE column LIKE ‘XXXX_’

or

SELECT FROM table_name
WHERE column LIKE ‘_XXXX’

or

SELECT FROM table_name
WHERE column LIKE ‘_XXXX_’

WHERE SALARY LIKE ‘200%’ Find any value starting with 200
WHERE SALARY LIKE ‘% 200%’ Find any value containing 200 anywhere
WHERE SALARY LIKE ‘_00%’ finds any value with 00 in the second and third digits
WHERE SALARY LIKE ‘2 _% _%’ Find any value that starts with 2 and is at least 3 characters long
WHERE SALARY LIKE ‘% 2’ finds any value ending in 2
WHERE SALARY LIKE ‘_2% 3’ Finds any value whose second digit is 2 and ends with 3
WHERE SALARY LIKE ‘2___3’ finds any value that is 5 digits in length and starts with 2 and ends with 3

Note the distinction: _ underscore and,-minus sign:
Display all records that contain a hyphen (-) in the ADDRESS text in the COMPANY table:
sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE ‘%-%’;

11.SQLite GLOB clause

SQLite's GLOB operator is used to match text values in a wildcard specified pattern. The GLOB operator returns true if the search expression matches the pattern expression,
Which is 1. Unlike the LIKE operator, GLOB is case-sensitive. For the following wildcard characters, it follows the UNIX syntax.
Asterisk (*)
Question mark (?)
An asterisk (*) represents zero, one or more numbers or characters. A question mark (?) Represents a single number or character. These symbols can be used in combination


SELECT FROM table_name
WHERE column GLOB ‘XXXX *’

or

SELECT FROM table_name
WHERE column GLOB ‘* XXXX *’

or

SELECT FROM table_name
WHERE column GLOB ‘XXXX?’

or

SELECT FROM table_name
WHERE column GLOB ‘? XXXX’

or

SELECT FROM table_name
WHERE column GLOB ‘? XXXX?’

or

SELECT FROM table_name
WHERE column GLOB ‘????’


WHERE SALARY GLOB ‘200 *’ Find any value that starts with 200
WHERE SALARY GLOB ‘* 200 *’ find any value containing 200 anywhere
WHERE SALARY GLOB ‘? 00 *’ Find any value with 00 in the second and third digits
WHERE SALARY GLOB ‘2 ??’ Find any value that starts with 2 and is at least 3 characters long
WHERE SALARY GLOB ‘* 2’ finds any value ending in 2
WHERE SALARY GLOB ‘? 2 * 3’ finds any value whose second digit is 2 and ends with 3
WHERE SALARY GLOB ‘2 ??? 3’ finds any value that is 5 digits in length and starts with 2 and ends with 3

12.SQLite Limit clause

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

Number of rows extracted from the table: sqlite> SELECT * FROM COMPANY LIMIT 6;

Extract 3 records from skipped 2 rows: sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2; 

13.SQLite Order BY clause

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

Install SALARY in ascending order:
sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;
result:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
================================================== == =======

Sort the results in ascending order by NAME and SALARY: (the order is from left to right, first by NAME, and the result set is sorted by SALARY (does not affect the first order)
For example: There are Allen1 and Allen2, the second sort is just to sort the SALARY of the two
sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
result:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
5 David 27 Texas 85000.0
7 James 24 Houston 10000.0
6 Kim 22 South-Hall 45000.0
4 Mark 25 Rich-Mond 65000.0
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0

Sort by NAME in descending order:

sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
3 Teddy 23 Norway 20000.0
1 Paul 32 California 20000.0
4 Mark 25 Rich-Mond 65000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
5 David 27 Texas 85000.0
2 Allen 25 Texas 15000.0

14.SQLite Group by

The basic syntax of the GROUP BY clause is given below. The GROUP BY clause must be placed after the condition in the WHERE clause and before the ORDER BY clause.
Such as:
SELECT column-list
FROM table_name
WHERE [conditions]
GROUP BY column1, column2 ...... columnN
ORDER BY column1, column2 ...... columnN

The data table is as follows:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

If you want to know the total salary of each customer, you can use the GROUP BY query,
As follows:
sqlite> SELECT NAME, SUM (SALARY) FROM COMPANY GROUP BY NAME;

This will produce the following results:
NAME SUM (SALARY)
---------- -----------
Allen 15000.0
David 85000.0
James 10000.0
Kim 45000.0
Mark 65000.0
Paul 20000.0
Teddy 20000.0

Our table has records with duplicate names as follows:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
8 Paul 24 Houston 20000.0
9 James 44 Norway 5000.0
10 James 45 Texas 5000.0

Let's use the same GROUP BY statement to group all records by the NAME column,
As follows:

sqlite> SELECT NAME, SUM (SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

This will produce the following results:

NAME SUM (SALARY)
---------- -----------
Allen 15000
David 85000
James 20000
Kim 45000
Mark 65000
Paul 40000
Teddy 20000

SQLite HAVING clause

Here is where the HAVING clause is in the SELECT query

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT OFFSET

In a query, the HAVING clause must be placed after the GROUP BY clause and must be placed before the ORDER BY clause. Here is the syntax of a SELECT statement with a HAVING clause:
SELECT column1, column2
FROM table1, table2
WHERE [conditions]
GROUP BY column1, column2
HAVING [conditions]
ORDER BY column1, column2


The COMPANY table has the following records:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
8 Paul 24 Houston 20000.0
9 James 44 Norway 5000.0
10 James 45 Texas 5000.0

Here is an example that will show all records with a name count less than 2:

sqlite> SELECT * FROM COMPANY GROUP BY name HAVING count (name) <2;

This will produce the following results:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000
5 David 27 Texas 85000
6 Kim 22 South-Hall 45000
4 Mark 25 Rich-Mond 65000
3 Teddy 23 Norway 20000


Here is an example that will show all records with a name count greater than 2:

sqlite> SELECT * FROM COMPANY GROUP BY name HAVING count (name)> 2;

This will produce the following results:

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
10 James 45 Texas 5000

SQLite Distinct Keywords

The SQLite DISTINCT keyword is used with a SELECT statement to eliminate all duplicate records and get only one record.
It may happen that there are multiple duplicate records in a table. The DISTINCT keyword becomes particularly meaningful when extracting such records,
It fetches only the single record, not the duplicate records.

grammar

The basic syntax of the DISTINCT keyword to eliminate duplicate records is as follows:
SELECT DISTINCT column1, column2, ..... columnN
FROM table_name
WHERE [condition]

Examples
Suppose the COMPANY table has the following records:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
8 Paul 24 Houston 20000.0
9 James 44 Norway 5000.0
10 James 45 Texas 5000.0

First, let's look at the following SELECT query, which will return duplicate payroll records:

sqlite> SELECT name FROM COMPANY;

This will produce the following results:

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James
Paul
James
James

Now let's use the DISTINCT keyword in the above SELECT query:

sqlite> SELECT DISTINCT name FROM COMPANY;

This will produce the following results without any duplicate entries:

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James

Reference from: http://www.runoob.com/sqlite/sqlite-operators.html

SQLite Learning Notes