Difference between revisions of "SQL"
Jump to navigation
Jump to search
(17 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== | ||
+ | ;NOW() | ||
+ | ;UTC_TIMESTAMP() | ||
+ | :Current timestamp (mysql) | ||
+ | |||
+ | ;date_sub(NOW(),interval 6 hour) | ||
+ | :Subtract 6 hours from the current timestamp (mysql) | ||
+ | |||
+ | ;date(<time>) | ||
+ | :Get the date part from a time | ||
+ | |||
+ | ;convert_tz(date,"UTC","MET") | ||
+ | :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> | ||
+ | |||
+ | ;<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== | ||
The example shows how subquery results can be handled as a table, aliasing of field and tablenames, a case statement and inner joins. | 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 | Example query | ||
Line 29: | Line 67: | ||
Inner Join jointable1 On jointable1.field2 = subquery.field1 | Inner Join jointable1 On jointable1.field2 = subquery.field1 | ||
Where subquery.field1 <> jointable1.field1 | Where subquery.field1 <> jointable1.field1 | ||
+ | </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. | ||
+ | |||
+ | ==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> | ||
Line 49: | Line 125: | ||
;.schema <tablename> | ;.schema <tablename> | ||
:Show the create statement for the table | :Show the create statement for the table | ||
+ | |||
+ | ;Dump to standard output | ||
+ | <syntaxhighlight lang=bash> | ||
+ | echo "sqlstatement;"|sqllite <DBfile>.DB | ||
+ | </syntaxhighlight> |
Revision as of 21:07, 23 July 2020
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
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.
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