Difference between revisions of "SQL"
Jump to navigation
Jump to search
(5 intermediate revisions by the same user not shown) | |||
Line 29: | Line 29: | ||
:Get the date part from a time | :Get the date part from a time | ||
− | ;convert_tz(date,"UTC", | + | ;convert_tz(date,"UTC",@@session.time_zone) |
− | :Convert from UTC to local timezone. Look [ | + | :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 ) | ;Sort on a part of a datetime (e.g. to rule out the seconds ) | ||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
− | select date_format(datefield,"%Y-%m-%d | + | select date_format(datefield,"%Y-%m-%d %H:%i") as date |
order by date | order by date | ||
</syntaxhighlight> | </syntaxhighlight> | ||
− | ;UNIX_TIMESTAMP(STR_TO_DATE('Jul 15 2018 10:34AM', '%M %d %Y %h:%i%p')) | + | ;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) | :Convert to unix-time (seconds since 1-1-1970 aka unix epoch) | ||
Line 67: | Line 69: | ||
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> | </syntaxhighlight> | ||
Revision as of 12:19, 15 April 2021
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",@@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.
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