Difference between revisions of "SQL"

From wiki
Jump to: navigation, search
m
 
Line 8: Line 8:
  
 
;NOW()
 
;NOW()
 +
;UTC_TIMESTAMP()
 
:Current timestamp (mysql)
 
:Current timestamp (mysql)
  

Latest revision as of 21:31, 19 November 2019

Structured Query Language for databases.

LPAD(<value>,<no>,<char>)
Left fill <value> with <char> until <no> length
concat(<value1>,<value2>)
Concatenate 2 strings
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
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
select a, b, count(b) from table group by b having count(b) > 1;
Find duplicate values

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

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