Difference between revisions of "SQL"
(22 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
Structured Query Language for databases. | 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>) | ;LPAD(<value>,<no>,<char>) | ||
:Left fill <value> with <char> until <no> length | :Left fill <value> with <char> until <no> length | ||
− | ; | + | ;CONCAT(<value1>,<value2>) |
:Concatenate 2 strings | :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() | ;NOW() | ||
+ | ;UTC_TIMESTAMP() | ||
:Current timestamp (mysql) | :Current timestamp (mysql) | ||
Line 15: | Line 31: | ||
;date(<time>) | ;date(<time>) | ||
:Get the date part from a time | :Get the date part from a time | ||
+ | |||
+ | ;convert_tz(date,"UTC",@@session.time_zone) | ||
+ | :Convert from UTC to local timezone. Look [[MariaDB/MySQL#Populate_and_update_timezone_information|here]] to set up timezones properly. | ||
+ | |||
+ | ;Sort on a part of a datetime (e.g. to rule out the seconds ) | ||
+ | <syntaxhighlight lang='sql'> | ||
+ | select date_format(datefield,"%Y-%m-%d %H:%i") as date | ||
+ | order by date | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | ;UNIX_TIMESTAMP(NOW()) | ||
+ | :Unix [https://www.epochconverter.com/ epoch] timestamp | ||
+ | ;<nowiki>UNIX_TIMESTAMP(STR_TO_DATE('Jul 15 2018 10:34AM', '%M %d %Y %h:%i%p'))</nowiki> | ||
+ | :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== | ==SubQuery== | ||
Line 40: | Line 73: | ||
Where subquery.field1 <> jointable1.field1 | Where subquery.field1 <> jointable1.field1 | ||
</syntaxhighlight> | </syntaxhighlight> | ||
+ | |||
+ | Works for updates too | ||
+ | <syntaxhighlight lang='sql'> | ||
+ | UPDATE table1 | ||
+ | JOIN ( SELECT field1, field2, field3 | ||
+ | FROM table2 | ||
+ | WHERE field1 = "xx" | ||
+ | ) as name on name.id = table1.id | ||
+ | SET table1.field2=name.field1 | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | Subquery and insert | ||
+ | <syntaxhighlight lang='sql'> | ||
+ | INSERT into table1(field1, field2, field3) | ||
+ | SELECT field1, field2, field3 from table2 | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | ==Join== | ||
+ | Combine data from more tables. [https://www.w3schools.com/sql/sql_join.asp 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 <code>regexp</code> or <code>rlike</code> 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== | ||
+ | <syntaxhighlight lang=sql> | ||
+ | select * from table_name | ||
+ | where column1 = 'value' | ||
+ | and column2 = 'value' | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | <syntaxhighlight lang=sql> | ||
+ | insert into table_name (column1, column2, column3, ...) | ||
+ | values (value1, value2, value3, ...); | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | <syntaxhighlight lang=sql> | ||
+ | update table_name | ||
+ | set column1 = value1, | ||
+ | column2 = value2 | ||
+ | where column1 = value; | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | <syntaxhighlight lang=sql> | ||
+ | insert table_name | ||
+ | set column1 = value1, | ||
+ | column2 = value2 | ||
+ | on duplicate key | ||
+ | update column1 = value1, | ||
+ | column2 = value2 | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | <syntaxhighlight lang=sql> | ||
+ | delete from table_name | ||
+ | where column1 = value1 | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | If using joins: | ||
+ | <syntaxhighlight lang=sql> | ||
+ | delete table_name from table_name | ||
+ | join table2 on table2.column2 = table_name.columnx | ||
+ | where table2.column3 = value3 | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | ==Troubleshooting== | ||
+ | For problems with processes waiting for a lock check [https://stackoverflow.com/questions/19801139/mysql-permanently-getting-waiting-for-table-metadata-lock this]. (item to be updated further) | ||
+ | |||
+ | ;show processlist; | ||
+ | :Show all running queries | ||
=SQLite= | =SQLite= |
Latest revision as of 22:31, 7 January 2024
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