SQL
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Structured Query Language for databases.
- LPAD(<value>,<no>,<char>)
- Left fill <value> with <char> until <no> length
- concat(<value1>,<value2>)
- Concatenate 2 strings
- NOW()
- UTC_TIMESTAMP()
- Current timestamp (mysql)
- date_sub(NOW(),interval 6 hour)
- Subtract 6 hours from the current timestamp (mysql)
- date(
- Get the date part from a time
- Sort on a part of a datetime (e.g. to rule out the seconds )
select date_format(datefield,"%Y-%m-%d:%H-%i") as date
order by date
- select a, b, count(b) from table group by b having count(b) > 1;
- Find duplicate values
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
- Dump to standard output
echo "sqlstatement;"|sqllite <DBfile>.DB