Difference between revisions of "SQL"
Jump to navigation
Jump to search
(4 intermediate revisions by the same user not shown) | |||
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 5: | Line 7: | ||
:Concatenate 2 strings | :Concatenate 2 strings | ||
+ | ==SubQuery== | ||
+ | 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 | Example query | ||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
select int_id, | select int_id, | ||
− | 'fieldspec1' as ' | + | '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 | from | ||
− | (Select distinct | + | (Select distinct fieldname, |
− | + | from table1 tabalias | |
− | + | Inner Join table2 on table2field = tabalias.field1 | |
− | |||
− | |||
− | |||
− | from table1 | ||
− | Inner Join table2 on table2field = | ||
Inner Join table3 on table3field = table2field | Inner Join table3 on table3field = table2field | ||
− | And table3filed = | + | And table3filed = avalue |
− | Where (field1 > | + | Where (field1 > 1value And (field2 = 1 And field3 = 'value')) |
− | or ( | + | or (field1 > avalue ) |
− | ) as | + | ) as SubQuery |
− | Inner Join jointable1 On jointable1. | + | Inner Join jointable1 On jointable1.field2 = subquery.field1 |
− | Where subquery. | + | 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 19:54, 8 June 2019
Structured Query Language for databases.
- LPAD(<value>,<no>,<char>)
- Left fill <value> with <char> until <no> length
- concat(<value1>,<value2>)
- Concatenate 2 strings
SubQuery
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