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
- NOTE!!
- To avoid conversion that depend on the timezone of your client or server use the unix_time (seconds since 1-1-1970) as integer whenever possible. Also in programs avoid date/time conversions except for presentation.
- 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",@@session.time_zone)
- 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(NOW())
- Unix epoch timestamp
- 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
Works for updates too
UPDATE table1
JOIN ( SELECT field1, field2, field3
FROM table2
WHERE field1 = "xx"
) as name on name.id = table1.id
SET table1.field2=name.field1
Subquery and insert
INSERT into table1(field1, field2, field3)
SELECT field1, field2, field3 from table2
Join
Combine data from more tables. w3schools have a nice page on this.
Like/Regexp/rlike
Match data using wildcard characters.
- select * from table where column like '%xxx_'
- Match all records where column1 values have at least 5 characters and end with xxx or XXX and an arbitrary single character
Adding 'binary' in front of the expression makes the match case sensitive.
Using like:
- %
- Match 1 or more characters
- _
- Match exactly 1 character
- Putting '\' in front escapes these wildcards (the % or _ character is matched).
With regexp
or rlike
you can use POSIX Regular Expressions
- select * from table where column1 regexp binary '.+xxx.$'
- Match all records where column1 values have at least 5 characters and end with xxx and an arbitrary single character
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;
insert table_name
set column1 = value1,
column2 = value2
on duplicate key
update column1 = value1,
column2 = value2
delete from table_name
where column1 = value1
If using joins:
delete table_name from table_name
join table2 on table2.column2 = table_name.columnx
where table2.column3 = value3
Troubleshooting
For problems with processes waiting for a lock check this. (item to be updated further)
- show processlist;
- Show all running queries
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