Difference between revisions of "SQL"

From wiki
Jump to: navigation, search
m
Line 1: Line 1:
 +
Structured Query Language for databases.
 +
 
;LPAD(<value>,<no>,<char>)
 
;LPAD(<value>,<no>,<char>)
 
:Left fill <value> with <char> until <no> length
 
:Left fill <value> with <char> until <no> length
Line 28: Line 30:
 
     Where subquery.field1 <> jointable1.field1
 
     Where subquery.field1 <> jointable1.field1
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
 +
=SQLite=
 +
Simple but complete DBMS. No server, just a file that is queried. Often used as embedded database in small devices.
 +
 +
The current implementation used is sqlite3.
 +
 +
;sqlite3 <DBfile>
 +
:Open the database file and enter the sqlite program. Help is available.
 +
:<code>sqlite>.help</code>
 +
 +
;.databases
 +
:Show the databases in the file
 +
 +
In below statements specification is optional if there is only 1 database or table.
 +
;.tables <database>
 +
:Show the tables in the database
 +
 +
;.schema <tablename>
 +
:Show the create statement for the table

Revision as of 08:51, 25 May 2019

Structured Query Language for databases.

LPAD(<value>,<no>,<char>)
Left fill <value> with <char> until <no> length
concat(<value1>,<value2>)
Concatenate 2 strings

The example shows how subquery results can be handled as a table, aliasing of field and tablenames, a case statement and inner joins. Example query

SELECT int_id,
       'fieldspec1' AS Alias,
       (CASE 
         WHEN table1.field1 > 1 AND table2.field1 = 'value' THEN x
         WHEN table1.field1 > 2 AND table2.field1 = 'value' THEN y
         ELSE z
       END) AS CaseResult,
       SubQuery.fieldname AS VALUE_SUBQUERY
  FROM 
   (SELECT DISTINCT fieldname,
     FROM table1 tabalias
     INNER JOIN table2 ON table2field = tabalias.field1
     INNER JOIN table3 ON table3field = table2field
      AND table3filed = avalue 
     WHERE (field1 > 1value AND (field2 = 1 AND field3 = 'value'))
      OR (field1 > avalue )
   ) AS SubQuery
   INNER JOIN jointable1 ON jointable1.field2 = subquery.field1 
    WHERE subquery.field1 <> jointable1.field1

SQLite

Simple but complete DBMS. No server, just a file that is queried. Often used as embedded database in small devices.

The current implementation used is sqlite3.

sqlite3 <DBfile>
Open the database file and enter the sqlite program. Help is available.
sqlite>.help
.databases
Show the databases in the file

In below statements specification is optional if there is only 1 database or table.

.tables <database>
Show the tables in the database
.schema <tablename>
Show the create statement for the table