SQL
Structured Query Language for databases.
MySql and mariaDB
- mysql -u<user> -p<password> < <sql-file>|sed 's/\t/,/g'
- Execute the query in <sql-file> and make the output comma separated (csv)
Misc functions
- LPAD(<value>,<no>,<char>)
- Left fill <value> with <char> until <no> length
- CONCAT(<value1>,<value2>)
- Concatenate 2 strings
- ROUND(value,decimals)
- Round a value to a number of decimals. TRUNCATE just cuts of the remaining decimals.
- select a, b, count(b) from table group by b having count(b) > 1;
- Find duplicate values
Date and time functions
- 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
- convert_tz(date,"UTC","MET")
- Convert from UTC to local timezone. Look here to set up timezones properly.
- 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
- UNIX_TIMESTAMP(STR_TO_DATE('Jul 15 2018 10
- 34AM', '%M %d %Y %h:%i%p'))
- Convert to unix-time (seconds since 1-1-1970 aka unix epoch)
- FROM_UNIXTIME(<unix-time>, '%W %e %M, %Y')
- Unix time to readable format. The formatting fields are optional.
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
Join
Combine data from more tables. w3schools have a nice page on this.
Basic statements
select * from table_name
where column1 = 'value'
and column2 = 'value'
insert into table_name (column1, column2, column3, ...)
values (value1, value2, value3, ...);
update table_name
set column1 = value1,
column2 = value2
where column1 = value;
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