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 in descending order(e.g. to rule out the seconds )

select date_format(datefield,"%Y-%m-%d %H:%i") as date
 order by date desc

Formatting codes

Code	Shows
%Y	Year in 4 digits
%m	Month in 2 digits
%M	Month as 3 characters
%d	Day in 2 digits
%H	Hour in 2 digits
%i	Minute in 2 digits
%s	Second in 2 digits

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