[QT] [Sql]sql Study record 3_sqlite use

See: http://www.runoob.com/sqlite/sqlite-create-table.html

Constraint Table Instance

The following is an instance that creates a company table with an ID as the primary key, and a constraint of NOT NULL indicates that the fields cannot be null when records are created in the table:

SQLite> CREATE TABLE company(   ID INT PRIMARY KEY isn't     null, NAME TEXT not null, 
           
             age INT not NULL
            , ADDRESS CHAR, SALARY REAL);     
           

Use one table to populate another table

You can populate data into another table by using a SELECT statement on a table that has a set of fields. Here's the syntax:

[(column1, column2,... columnn)] SELECT column1, column2,... [WHERE condition];              

You can skip the above statement for the time being, and you can first learn the SELECT and WHERE clauses described in the later sections.

SQLite operator

Definitions/Examples See: http://www.runoob.com/sqlite/sqlite-operators.html

• Arithmetic operators

• Comparison operators

• logical operators

• Bitwise operators

SqliteUpdate StatementInstance

Suppose the company table has the following records:

ID NAME Age ADDRESS SALARY----------  ----------  ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-mond 65000.05 david 27  texas 85000.06 kim 22 Span class= "Typ" >south-hall 45000.07 james 24  houston 10000.0      

Here is an instance that updates the customer address with ID 6:

SQLite>=' Texas '=6;     

The company table now has the following records:

ID NAME Age ADDRESS SALARY----------  ----------  ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 rich-mond 65000.05 david 27 texas  85000.06 kim22 texas 45000.07 james 24  houston 10000.0      

If you want to modify all the values of the ADDRESS and SALARY columns in the company table, you do not need to use the WHERE clause, the UPDATE query is as follows:

SQLite>=' Texas ',=20000.00;      

The company table now has the following records:

ID NAME Age ADDRESS SALARY----------  ----------  ---------- ---------- ----------1 Paul 32 Texas 20000.02 Allen 25 Texas 20000.03 Teddy 23 Texas 20000.04 Mark 25 texas20000.05 david 27 texas  20000.06 kim22 texas 20000.07 james 24  texas 20000.0      

SqliteDelete StatementInstance

Suppose the company table has the following records:

ID NAME Age ADDRESS SALARY----------  ----------  ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-mond 65000.05 david 27  texas 85000.06 kim 22 Span class= "Typ" >south-hall 45000.07 james 24  houston 10000.0      

The following is an instance that deletes a customer with an ID of 7:

SQLite>=7;   

The company table now has the following records:

ID NAME Age ADDRESS SALARY----------  ----------  ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04mark 25 rich -mond 65000.05 david 27  texas 85000.06 kim 22 Span class= "Typ" >south-hall 45000.0   

If you want to delete all records from the company table, you do not need to use the WHERE clause, and the delete query is as follows:

SQLite> DELETE from company;  

Now, there are no records in the company table because all the records have been deleted through the DELETE statement.

SqliteLike clauseInstance

The following examples demonstrate different places like clauses with the '% ' and ' _ ' operators:

Statement	Description
WHERE SALARY like ' 200% '	Find any value starting with 200
WHERE SALARY like '%200% '	Find any value that contains 200 in any location
WHERE SALARY like ' _00% '	Find any value for the second and third digits 00
WHERE SALARY like ' 2_%_% '	Find any value starting with 2 with a length of at least 3 characters
WHERE SALARY like '%2 '	Find any value ending with 2
WHERE SALARY like ' _2%3 '	Find any value with a second digit of 2 and ending with 3
WHERE SALARY like ' 2___3 '	Finds any value that has a length of 5 digits and ends with 3, starting with 2

[QT] [Sql]sql Study record 3_sqlite use