SQL: Unterschied zwischen den Versionen
Aus informatikvs
(→snippets) |
|||
| (6 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt) | |||
| Zeile 36: | Zeile 36: | ||
;olap cube() rollup() | ;olap cube() rollup() | ||
== snippets == | == snippets == | ||
| + | <pre> | ||
| + | select | ||
| + | current_date date_1 | ||
| + | , VARCHAR_FORMAT(char(current_timestamp), 'J') date_2 | ||
| + | , TO_CHAR(char(current_timestamp), 'J') date_3 | ||
| + | , days(current_date) date_4 | ||
| + | , TO_CHAR(char(current_timestamp), 'DDD') date_5 | ||
| + | from sysibm.sysdummy1 | ||
| + | ; | ||
| + | date_1 date_2 date_3 date_4 date_5 | ||
| + | 2017-05-04 2457878 2457878 736453 124 | ||
| + | |||
| + | </pre> | ||
<pre> ... dec(round(plan_mod, +2), 6, 2)</pre> | <pre> ... dec(round(plan_mod, +2), 6, 2)</pre> | ||
| − | <pre> ... date(a.abdagc+693594) = current_date </pre | + | <pre> ... date(a.abdagc+693594) = current_date </pre> |
| + | <pre> | ||
| + | insert into tdatbib.v7tabgld | ||
| + | (v7apid, v7tbnr, v7tgid, v7spcd, v7tgtx) | ||
| + | values('KW', 386, ' 758', 'D', 'Max.Mueller@test.de') | ||
| + | ; | ||
| + | </pre> | ||
| + | <pre> | ||
| + | select * from datbib.zz | ||
| + | left outer join tdatbib.v7tabgld | ||
| + | on (v7apid, v7tbnr, v7tgid, v7spcd) | ||
| + | =('KW', 386, (' ' concat zzusrk), 'D') | ||
| + | where zzusrk='357' | ||
| + | ; | ||
| + | </pre> | ||
| + | <pre> | ||
| + | with test (Ziffer, wort) | ||
| + | as (values (1, 'eins') | ||
| + | , (2, 'zwei') | ||
| + | , (3, 'drei')) | ||
| + | select * | ||
| + | from test | ||
| + | where wort <>'eins' | ||
| + | order by Ziffer | ||
| + | ; | ||
| + | </pre> | ||
Aktuelle Version vom 4. Mai 2017, 14:59 Uhr
terms
- sql-ddl
- (Data Definition Language) das erstellen/ändern/löschen von datenbankstrukturen (tables, fields, views, ...)
- sql-dml
- (Data Manipulation Language) das erstellen/ändern/löschen des inhalts (daten) der datenbankstrukturen
- sql-dcl
- (Data Control Language) Transaktionskontrolle und Zugriffsberechtigungen
- sql-injection
- transaktionen
- attribut
- index
- commands
- such as "Select", "Insert", "Update", "Delete", "Create", and "Drop"
- tables
- are uniquely identified by their names and are comprised of columns and rows.
- columns
- contain the column name, data type, and any other attributes for the column.
- rows
- contain the records or data for the columns.
- the where-clause
- optional specifies which data values or rows will be returned (criterias: =, <>, <, >, >=, <=, like)
- tuple
- is a collection of one or more attributes or rows present in a table
- constraints
- are uses to limit the values of variables NOT NULL; UNIQUE; PRIMARY KEY; FOREIGN KEY; CHECK; DEFAULT
- subquery
- aggregat functions
- avg(); count(); count(dinstinct field); max(); ...
- scalar functions
- ucase(), lcase(), mid(), len(), left(), right(), round(), mod(), now()
- olap cube() rollup()
snippets
select current_date date_1 , VARCHAR_FORMAT(char(current_timestamp), 'J') date_2 , TO_CHAR(char(current_timestamp), 'J') date_3 , days(current_date) date_4 , TO_CHAR(char(current_timestamp), 'DDD') date_5 from sysibm.sysdummy1 ; date_1 date_2 date_3 date_4 date_5 2017-05-04 2457878 2457878 736453 124
... dec(round(plan_mod, +2), 6, 2)
... date(a.abdagc+693594) = current_date
insert into tdatbib.v7tabgld
(v7apid, v7tbnr, v7tgid, v7spcd, v7tgtx)
values('KW', 386, ' 758', 'D', 'Max.Mueller@test.de')
;
select * from datbib.zz
left outer join tdatbib.v7tabgld
on (v7apid, v7tbnr, v7tgid, v7spcd)
=('KW', 386, (' ' concat zzusrk), 'D')
where zzusrk='357'
;
with test (Ziffer, wort)
as (values (1, 'eins')
, (2, 'zwei')
, (3, 'drei'))
select *
from test
where wort <>'eins'
order by Ziffer
;
