You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
-- http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
SELECT (strftime('%s','now') - strftime('%s',date('now','-1 day'))) AS Seconds
-- https://stackoverflow.com/questions/289680/difference-between-2-dates-in-sqlite
SELECT CAST(JulianDay('now') - JulianDay(MAX(datetime_field_in_table)) * 24 * 60 AS Integer) AS diff_minutes FROM example_table
141
SQLite
Datenbank
Fremdschlüsselprüfung aktivieren
-- http://www.sqlite.org/foreignkeys.html
PRAGMA foreign_keys = ON;
-- !! this is only valid for the current session.
-- If you want to use this permanent, you have to use this statement every time you start a new session
216
ORACLE PL/SQL
Datum, Zeit
Aktuelles Datum/Uhrzeit
-- if todays date is: 2014-02-02 then a static query would be
SELECT TO_DATE('02/02/2014','dd/mm/yyyy') FROM dual
-- system variable
SELECT SYSDATE AS TODAY FROM SYS."DUAL"
47
T-SQL
Datenbank
Temporäre DB-Tabelle
-- lokale Temporäre Tabelle (Livetime nur innerhalb von STORED PROCEDURES)
SELECT * INTO #tempdaten FROM myTable
-- ODER globale temporäre Tabellen (Livetime auch außerhalb von STORED PROCEDURES)
SELECT * INTO ##tempdaten FROM myTable
49
T-SQL
Datenbank
Die letzte eingefügte ID eines INSERT ermitteln
-- Nach dem INSERT folgenden SELECT durchführen
-- Achtung bei Verwendung mit TRIGGERN, diese Funktion liefert auch eingefügte Werte von TRIGGERN
-- (unabhängig der Tabelle)
SELECT @@Identity AS NewID
-- Möchte man alle IDs (oder erzeugten Werte speichen, kann man nachfolgende Variante wählen):
IF OBJECT_ID(CONCAT('tempdb..','#Test01')) IS NOT NULL DROP TABLE #Test01;
CREATE TABLE #Test01 ( id INT IDENTITY(1,1) PRIMARY KEY, value1 NVARCHAR(50) );
IF OBJECT_ID(CONCAT('tempdb..','##saveOUTPUT')) IS NOT NULL DROP TABLE ##saveOUTPUT;
CREATE TABLE ##saveOUTPUT ( inserted_id INT, some_text NVARCHAR(50) );
INSERT INTO #Test01 (value1)
OUTPUT INSERTED.id, 'Another Text' INTO ##saveOUTPUT
VALUES ('Hello World!')
INSERT INTO #Test01 (value1)
OUTPUT INSERTED.id, 'Another Text' INTO ##saveOUTPUT
VALUES ('Hello World!')
SELECT * FROM ##saveOUTPUT
236
MySQL
Strings
String nach Delimenter zerlegen
-- Not a real solution, but a workaround if you have rights to create a function
-- see: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/
-- same problem with MSSQL.
CREATE FUNCTION SPLIT_STR(
StringToBeSplitted VARCHAR(255),
delimiter VARCHAR(12),
position INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(StringToBeSplitted, delimiter, position),
LENGTH(SUBSTRING_INDEX(StringToBeSplitted, delimiter, position -1)) + 1),
delimiter, '');
SELECT SPLIT_STR(string, delimiter, position);
46
T-SQL
Datenbank
DB-Funktion erstellen
-- Scalar-value
CREATE FUNCTION whichContinent
(@country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
DECLARE @returnValue varchar(30)
SELECT @returnValue = CASE @country
WHEN 'ARGENTINA' THEN 'SOUTH AMERICA'
WHEN 'BELGIUM' THEN 'EUROPE'
WHEN 'BRAZIL' THEN 'SOUTH AMERICA'
WHEN 'CANADA' THEN 'NORTH AMERICA'
WHEN 'DENMARK' THEN 'EUROPE'
WHEN 'FINLAND' THEN 'EUROPE'
WHEN 'FRANCE' THEN 'EUROPE'
ELSE 'UNKNOWN'
END
RETURN @returnValue
END
214
T-SQL
Exceptions
Try Catch Except
-- See also: http://msdn.microsoft.com/de-de/library/ms175976(v=sql.105).aspx
-- !!
-- TRY-CATCH cannot be used for all operations/situations in T-SQL.
-- Be careful and refer to the link above for details
-- !!
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
81
T-SQL
Datenbank, Collation
Collation-Arten auflisten
-- siehe auch: http://msdn.microsoft.com/de-de/library/ms187963.aspx
SELECT *
FROM fn_helpcollations()
208
PL/pgSQL
Datenbank
Tabelleinhalt DB übergreifend kopieren
-- siehe auch: http://stackoverflow.com/questions/6083132/postgresql-insert-into-select
-- in DestinationDB do:
INSERT INTO Table1
SELECT id, valuefield
FROM dblink('dbname=SourceDB', 'SELECT id, valuefield FROM Table1')
AS t(id integer, valuefield varchar(100));
