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/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
146
PL/pgSQL
Datenbank
Tabelle kopieren
CREATE TABLE new_table AS SELECT * FROM old_table
-- this action can't be done cross databases
150
PL/pgSQL
Datenbank
DB-Funktion erstellen
CREATE OR REPLACE FUNCTION whichContinent(country TEXT)
RETURNS character varying AS $BODY$
DECLARE returnValue character varying;
BEGIN
SELECT INTO 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;
$BODY$ LANGUAGE PLPGSQL;
151
T-SQL
Datenbank
Automatisch hochzählendes Feld
CREATE TABLE myTable
(
id INT IDENTIY(1,1),
field1 varchar(100)
)
152
PL/pgSQL
Datenbank
Automatisch hochzählendes Feld
CREATE TABLE myTable
(
id SERIAL,
field1 varchar(100)
)
154
PL/pgSQL
Datenbank
Erstellen und Befüllen Tabelle aus Query
DROP TABLE myTable;
CREATE TABLE myTable AS SELECT * FROM mySecondTable;
158
T-SQL
Datenbank
Schema-Vergleich
SELECT
*
FROM
(
SELECT
TABLES_P.TABLE_SCHEMA AS [SCHEMA_P]
, TABLES_P.TABLE_NAME AS [OBJECTNAME_P]
, TABLES_P.TABLE_TYPE AS [OBJECTTYPE_P]
FROM <DATABASE_PRODUCTION>.INFORMATION_SCHEMA.TABLES AS TABLES_P
UNION ALL
SELECT
ROUTINES_P.ROUTINE_SCHEMA AS [SCHEMA_P]
, ROUTINES_P.ROUTINE_NAME AS [OBJECTNAME_P]
, ROUTINES_P.ROUTINE_TYPE AS [OBJECTTYPE_P]
FROM <DATABASE_PRODUCTION>.INFORMATION_SCHEMA.ROUTINES AS ROUTINES_P
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER TABLE' AS [OBJECTTYPE_P]
FROM
<DATABASE_PRODUCTION>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_PRODUCTION>.sys.tables AS TAB ON TRIG.parent_id = TAB.object_id
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER VIEW' AS [OBJECTTYPE_P]
FROM
<DATABASE_PRODUCTION>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_PRODUCTION>.sys.views AS TAB ON TRIG.parent_id = TAB.object_id
) ss_P
FULL OUTER JOIN
(
SELECT
TABLES_E.TABLE_SCHEMA AS [SCHEMA_E]
, TABLES_E.TABLE_NAME AS [OBJECTNAME_E]
, TABLES_E.TABLE_TYPE AS [OBJECTTYPE_E]
FROM <DATABASE_DEVELOPMENT>.INFORMATION_SCHEMA.TABLES AS TABLES_E
UNION ALL
SELECT
ROUTINES_E.ROUTINE_SCHEMA AS [SCHEMA_E]
, ROUTINES_E.ROUTINE_NAME AS [OBJECTNAME_E]
, ROUTINES_E.ROUTINE_TYPE AS [OBJECTTYPE_E]
FROM <DATABASE_DEVELOPMENT>.INFORMATION_SCHEMA.ROUTINES AS ROUTINES_E
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER TABLE' AS [OBJECTTYPE_P]
FROM
<DATABASE_DEVELOPMENT>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_DEVELOPMENT>.sys.tables AS TAB ON TRIG.parent_id = TAB.object_id
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER VIEW' AS [OBJECTTYPE_P]
FROM
<DATABASE_DEVELOPMENT>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_DEVELOPMENT>.sys.views AS TAB ON TRIG.parent_id = TAB.object_id
) ss_E ON ss_P.[SCHEMA_P]=ss_E.[SCHEMA_E] AND ss_P.OBJECTNAME_P=ss_E.OBJECTNAME_E AND ss_P.OBJECTTYPE_P=ss_E.OBJECTTYPE_E
180
T-SQL
Datenbank
NULL Wert eines Feldes übersteuern
SELECT ISNULL(MyColumn1, 1.0) AS MyColumnWithoutNULL;
181
PL/pgSQL
Datenbank
NULL Wert eines Feldes übersteuern
SELECT COALESCE(MyColumn1, 1.0) AS MyColumnWithoutNULL;
206
MySQL
Datenbank
Tabelle DB übergreifend kopieren
DROP TABLE IF EXISTS `DestinationDB`.`Table1`;
CREATE TABLE `DestinationDB`.`Table1` SELECT * FROM `SourceDB`.`Table1`;
