View PlGroup Datenbank
| ID | 5 |
|---|
| Text | Datenbank |
|---|
| Title | Not set |
|---|
PlItems
- SELECT * FROM myTable WHERE id=1;
SELECT field2,field17 FROM myTable WHERE id=1 AND name='test';
SELECT field3,field1 FROM myTable WHERE city like '%ew Yor%';
- ; In M werden Tabellen Globals genannt
S id=1
W !,^TABLE(id)
S street="Hollywood Drive"
S city="Los Angeles"
W !,^PERSONS(street,city)
S temp=^GLOBAL(0,"test","temp1")
- ; Es existiert eine Tabelle namens Table.Cities welche einen Eintrag mit der ID=15 enthält. Diese Tabelle enthält das Feld "Name"
Set ds=##class(Table.Cities).%OpenId(15)
Write !,ds.Name
; Oder über SQL
Set result = ##class(%Library.ResultSet).%New()
Set sql = "SELECT Name FROM Table.Cities WHERE ID=15"
Do result.Prepare(sql)
Do result.Execute("")
For
{
Quit:'result.Next()
Set name=result.Data("Name")
}
- -- 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
- -- 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
- INSERT INTO DestinationDB.dbo.Table1 SELECT * FROM SourceDB.dbo.Table1
-- Alternative
-- bereits vorhandene Tabelle in der Ziel-DB löschen
DROP TABLE DB_DESTINATION.dbo.TableCopy
SELECT * INTO DB_DESTINATION.dbo.TableCopy FROM DB_SOURCE.dbo.TableOrginal
- -- 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
- SELECT
T.TABLE_CATALOG
,T.TABLE_SCHEMA
,T.TABLE_NAME
,T.TABLE_TYPE
FROM
INFORMATION_SCHEMA.TABLES T
- SELECT schema.functionname(para1, para2)
- CREATE #myTempTable
(
ID Int IDENTITY
,tempField1 Int
,tempField2 VarChar(30)
)
DECLARE @field1 AS Int
DECLARE @field2 AS VarChar(30)
DECLARE myCursor CURSOR FOR SELECT myTableField1, myTableField2 FROM myTable
OPEN myCursor
FETCH NEXT FROM myCursor INTO @field1, @field2;
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @field1 -- Prints value from column myTable.myTableField1
PRINT @field2 -- Prints value from column myTable.myTableField2
INSERT INTO #myTempTable (tempField1,tempField2) VALUES (@field1, @field2)
-- do something else...
FETCH NEXT FROM myCursor INTO @field1, @field2; -- next row
END
CLOSE myCursor
DEALLOCATE myCursor
DROP #myTempTable
- -- Beispiel:
-- http://msdn.microsoft.com/de-de/library/ms186734.aspx
SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode FROM Sales.vSalesPerson
- -- Durchsucht alle Functions, Stored Procedures, Views nach dem enthaltenen Begriff dbo.table1:
DECLARE @suchbegriff VarChar(50) = '%dbo.table1%'
SELECT
*
FROM
(
SELECT
ROUTINE_SCHEMA AS [Schema]
, ROUTINE_NAME AS [Objectname]
, ROUTINE_DEFINITION AS [Objectdefinition]
, ROUTINE_TYPE AS [Objecttype]
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE @suchbegriff
AND (ROUTINE_TYPE='PROCEDURE' OR ROUTINE_TYPE='FUNCTION')
UNION ALL
SELECT
TABLE_SCHEMA AS [Schema]
, TABLE_NAME AS [Objectname]
, VIEW_DEFINITION AS [Objectdefinition]
, 'VIEW' AS [Objecttype]
FROM
INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE @suchbegriff
) subQuery1
ORDER BY subQuery1.[Objectname]
-- gefunden bei: http://www.mssqltips.com/tip.asp?tip=1419
-- erweitert um View-Definition
- Select *
INTO SQL2.dbo.NeueTabelle
FROM SQL1.dbo.AlteTabelle
WHERE 0=1
- SELECT OBJECT_ID ('tempdb..#myTableTable1')
- -- Welche Tabellen ...
select o.name, c.name,
from sys.objects o inner join sys.columns c on o.object_id = c.object_id
where c.is_identity = 1
-- --------------------------------------
-- Hat diese Tabelle ...
IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N'dbo.myTable'), 'TableHasIdentity')) = 1)
PRINT 'Yes'
ELSE
PRINT 'No'
- http://www.databasejournal.com/features/mssql/article.php/3286501/T-SQL-Programming-Part-4---Setting-Variables-in-Calling-T-SQL-Code-While-Using-spexecutesql.htm
use Northwind
go
declare @RECCNT int
declare @ORDID varchar(10)
declare @CMD Nvarchar(100)
set @ORDID = 10436
SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' +
' where OrderId < @ORDERID'
print @CMD
exec sp_executesql @CMD,
N'@RECORDCNT int out, @ORDERID int',
@RECCNT out,
@ORDID
print 'The number of records that have an OrderId' +
' greater than ' + @ORDID + ' is ' +
cast(@RECCNT as char(5))
- -- Siehe auch: http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7
CREATE Procedure spDeleteRows
-- Recursive row delete procedure.
-- It deletes all rows in the table specified that conform to the criteria selected,
-- while also deleting any child/grandchild records and so on. This is designed to do the
-- same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys
-- table to find any child tables, then deletes the soon-to-be orphan records from them using
-- recursive calls to this procedure. Once all child records are gone, the rows are deleted
-- from the selected table. It is designed at this time to be run at the command line. It could
-- also be used in code, but the printed output will not be available.
(
@cTableName varchar(50), -- name of the table where rows are to be deleted
@cCriteria nvarchar(1000), -- criteria used to delete the rows required
@iRowsAffected int OUTPUT -- number of records affected by the delete
)
As
set nocount on
declare @cTab varchar(255), -- name of the child table
@cCol varchar(255), -- name of the linking field on the child table
@cRefTab varchar(255), -- name of the parent table
@cRefCol varchar(255), -- name of the linking field in the parent table
@cFKName varchar(255), -- name of the foreign key
@cSQL nvarchar(1000), -- query string passed to the sp_ExecuteSQL procedure
@cChildCriteria nvarchar(1000), -- criteria to be used to delete
-- records from the child table
@iChildRows int -- number of rows deleted from the child table
-- declare the cursor containing the foreign key constraint information
DECLARE cFKey CURSOR LOCAL FOR
SELECT SO1.name AS Tab,
SC1.name AS Col,
SO2.name AS RefTab,
SC2.name AS RefCol,
FO.name AS FKName
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id
AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id
AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName
OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
BEGIN
-- build the criteria to delete rows from the child table. As it uses the
-- criteria passed to this procedure, it gets progressively larger with
-- recursive calls
SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +
@cRefTab +'] WHERE ' + @cCriteria + ')'
print 'Deleting records from table ' + @cTab
-- call this procedure to delete the child rows
EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
END
Close cFKey
DeAllocate cFKey
-- finally delete the rows from this table and display the rows affected
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName
- CREATE TABLE zieltabelle SELECT * FROM quelltabelle WHERE ID=-1;
- SELECT SUBSTRING(
(SELECT ',' + CSVItem
FROM TableWithItems s
ORDER BY s.Name
FOR XML PATH('')),2,200000)
GO
- -- http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
SELECT
R.TITEL AS TITEL
,R.ANLEITUNG AS ANLEITUNG
,GROUP_CONCAT(CONCAT(CAST(RZ.MENGE AS CHAR),' ',RZ.EINHEIT,' ' ,Z.ZUTAT) SEPARATOR ',') AS ZUTATEN
,R.NOTIZ AS NOTIZ
FROM REZEPT AS R
LEFT JOIN REZEPT_HAS_ZUTAT AS RZ ON R.ID=RZ.REZEPT_ID
LEFT JOIN ZUTAT AS Z ON RZ.ZUTAT_ID=Z.ID AND RZ.REZEPT_ID=R.ID
- -- Find characters which are also special characters for search terms, e.g. % or _
-- see also: http://msdn.microsoft.com/en-us/library/ms179859%28v=sql.105%29.aspx
SELECT * FROM table_1 WHERE column_1 LIKE '%!_%' ESCAPE '!'
-- Find results which includes the character "_". The escape character is defined as "!"
- SELECT DB_NAME() AS DB_Name
- -- 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
- CREATE TABLE new_table AS SELECT * FROM old_table
-- this action can't be done cross databases
- 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;
- CREATE TABLE myTable
(
id INT IDENTIY(1,1),
field1 varchar(100)
)
- CREATE TABLE myTable
(
id SERIAL,
field1 varchar(100)
)
- DROP TABLE myTable;
CREATE TABLE myTable AS SELECT * FROM mySecondTable;
- 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
- SELECT ISNULL(MyColumn1, 1.0) AS MyColumnWithoutNULL;
- SELECT COALESCE(MyColumn1, 1.0) AS MyColumnWithoutNULL;
- DROP TABLE IF EXISTS `DestinationDB`.`Table1`;
CREATE TABLE `DestinationDB`.`Table1` SELECT * FROM `SourceDB`.`Table1`;
- SET IDENTITY_INSERT myTable ON
INSERT INTO myTable(Identity_ID, Value) VALUES (4711, 'Back To The Future...')
SET IDENTITY_INSERT myTable OFF
- -- 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));
- -- Table-value
-- Variant 1
CREATE FUNCTION whichContinent
(@country nvarchar(15))
RETURNS TABLE
AS
RETURN
(
SELECT @country AS Country
)
-- Variant 2
CREATE FUNCTION whichContinent
(@country nvarchar(15))
RETURNS
@returnTableResult TABLE
(
id int
,currency VARCHAR(100)
)
AS
BEGIN
IF @country='Germany'
BEGIN
INSERT INTO @returnTableResult VALUES (1,'EURO')
END
RETURN
END
- SELECT IFNULL(MyColumn1, 1) AS MyColumnWithoutNULL;
- SELECT DATABASE() AS DB_Name;
- SELECT
*
FROM (
VALUES
(1, 2),
(3, 4)
) AS q (col1, col2)
GO
-- the same as:
SELECT 1 AS col1, 2 AS col2 UNION ALL
SELECT 3 AS col1, 4 AS col2
GO
-- col1 | col2
-- ===========
-- 1 | 2
-- -----------
-- 3 | 4
- SELECT
*
FROM (
VALUES
(1, 2),
(3, 4)
) AS q (col1, col2)
;
-- the same as:
SELECT 1 AS col1, 2 AS col2 UNION ALL
SELECT 3 AS col1, 4 AS col2
;
-- col1 | col2
-- ===========
-- 1 | 2
-- -----------
-- 3 | 4
- SELECT FieldA, LISTAGG(CSVItem, ',') WITHIN GROUP (ORDER BY FieldA) AS Members
FROM TableWithItems
GROUP BY FieldA;
- CREATE TEMPORARY TABLE myTempTable1
(
id INT
,myText TEXT
)
- -- Achtung, er werden nur Felder mit Datentypen übernommen.
-- Keine Constraints (PK, FK, Defaults, ...) oder IDENTITY Informationen
-- Sollte die Zieltabelle (myDestinationTable) bereits vorhanden sein, kommt es zu einer Fehlermeldung
SELECT
*
INTO myDestinationTable
FROM mySourceTable
- -- Sollte die Zieltabelle (myDestinationTable) bereits vorhanden sein
-- wird diese überschrieben (neue Strukturen werden übernommen)
SELECT
*
INTO myDestinationTable
FROM mySourceTable
- CREATE TABLE newDestinationTable AS
SELECT * FROM oldSourceTable
- -- Es soll die Menge an Datensätze ausgegeben reduziert um eine andere Menge (Rest)
-- Die einzelnen Tabellen oder Subqueries müssen die gleichen Spalten im Statement haben
SELECT
FieldA
,FieldB
,FieldC
FROM myTable1
EXCEPT
SELECT
FieldA
,FieldB
,FieldC
FROM myTable2
- -- Es soll die Menge an Datensätze ausgegeben reduziert um eine andere Menge (Rest)
-- Die einzelnen Tabellen oder Subqueries müssen die gleichen Spalten im Statement haben
SELECT
FieldA
,FieldB
,FieldC
FROM myTable1
EXCEPT
SELECT
FieldA
,FieldB
,FieldC
FROM myTable2
- UPDATE TableToBeUpdated
SET myField1 = 'Update this field'
FROM
(
SELECT * FROM JoinedTable
WHERE cond1 = 1
)
J
WHERE J.ID = TableToBeUpdated.ID
;
- UPDATE UPD_TABLE
SET UPD_TABLE.myField1 = 'Update this field'
FROM TableToBeUpdated UPD_TABLE
LEFT JOIN
(
SELECT * FROM JoinedTable
WHERE cond1 = 1
)
J
WHERE J.ID = UPD_TABLE.ID
;
- UPDATE TableToBeUpdated UPD_TABLE
INNER JOIN JoinedTable J ON J.id = UPD_TABLE.id
SET UPD_TABLE.myField1 = 'Update this field'
;
- SELECT
FieldA
,Nz([FieldB,"This field is null") As FieldB_Nz
FROM TableA
- CREATE OR REPLACE FUNCTION increment(i INT) RETURNS void AS $$
BEGIN
INSERT INTO accounts (id,name, balance) VALUES (i, 'Test', 1200);
END; $$
LANGUAGE plpgsql;
DROP FUNCTION increment(i INT);
- CREATE PROCEDURE increment(@i INT) AS
BEGIN
INSERT INTO accounts (id,name, balance) VALUES (i, 'Test', 1200);
END;
- COMMENT ON FUNCTION increment(i INT) IS 'This is a comment';
- <?php
// Example as SQL:
// SELECT * FROM MainTable
// WHERE fieldA=5 AND fieldB IN (SELECT id FROM SubTable WHERE fieldC=551)
$fieldC = 551;
$SubTable_idQry = SubTable::find()->select('id')->where(['fieldC' => $fieldC]);
// Static way:
$fieldA = 5;
// As result:
$fieldA = ThirdTable::find()
->select('fieldA_Reference')
->where(['fieldC' => $fieldC])
->one();
$MainTableResultQry = MainTable::find()
->where(['in', 'fieldB', $SubTable_idQry])
->andWhere(['fieldA' => $fieldA])
->all();
?>
- <?php
/* // Example as SQL
SELECT
FieldA
,COUNT(FieldA) as cnt
FROM MainTable
WHERE FieldA = 5
GROUP BY FieldA
*/
$MainTableResultQry = MainTable::find()
->select(['FieldA', 'COUNT(FieldA) as cnt'])
->where(['FieldA' => 5])
->groupBy(['FieldA'])
->createCommand()
->queryAll();
?>
- -- Siehe BigQuery -> Differenzen anzeigen
-- https://proglang.meta-grid.com/index.php?r=plItems/view&id=294
- SELECT
"oid",
datname, -- database name
datdba,
"encoding",
datcollate,
datctype,
datistemplate,
datallowconn,
datconnlimit,
datlastsysoid,
datfrozenxid,
datminmxid,
dattablespace,
datacl
FROM
pg_catalog.pg_database
WHERE datistemplate = FALSE -- do not list template0, template1, ...
;
- UPDATE
`project.dataset.table1` t1
SET
t1.target_column = t2.source_column
FROM
`project.dataset.table2` t2
WHERE
t1.join_key = t2.join_key;