ABC MS Office 2010 PL · Korespondencja seryjna .....140 Przygotowanie bazy danych .....141 Listy ...
Andrzej Leśnicki Bazy Danych, Wykład 2010 1/28 · 2010-04-26 · Andrzej Leśnicki Bazy Danych,...
Transcript of Andrzej Leśnicki Bazy Danych, Wykład 2010 1/28 · 2010-04-26 · Andrzej Leśnicki Bazy Danych,...
Andrzej Leśnicki Bazy Danych, Wykład 2010 1/28
PostgreSQL
1. Wstęp
1.1. Czym jest PostgreSQL? Standardy języka SQL: SQL-86: ANSI X3.135-1986 „Database Language SQL” SQL-89: ANSI X3.168-1989 „Database Language - Embedded SQL” ANSI X3.135-1989 „Database Language – SQL with Integrity Enhancement” SQL-92: ISO/IEC 9075:1992 “Database Language SQL” SQL-99: ISO/IEC 9075:1999 “Information Technology – Database Languages – SQL”
DDL DML DCL TTC CREATE ALTER DROP RENAME TRUNCTATE
SELECT INSERT UPDATE DELETE
GRANT REVOKE
COMMIT ROLLBACK SAVEPOINT
PostgreSQL podtrzymuje standard SQL i oferuje wiele nowoczesnych cech:
- zapytania złożone - klucze obce - trygery (wyzwalacze) - widoki - integralność transakcyjną - sterowanie współbieżnością wielu wersji
1.2. Architektura systemu klient - serwer 1.3. Zakładanie bazy danych $ createdb student123 createdb: command not found $ /usr/local/pgsql/bin/createdb student123
Andrzej Leśnicki Bazy Danych, Wykład 2010 2/28
$ createdb $ dropdb student123 1.4. Dostęp do bazy danych $ psql student123 student123=> student123=# student123=> \q General \copyright show PostgreSQL usage and distribution terms \g [FILE] or ; execute query (and send results to file or | pipe) \h [NAME] help on syntax of SQL commands, * for all commands \q quit psql Query Buffer \e [FILE] edit the query buffer (or file) with external editor \ef [FUNCNAME] edit function definition with external editor \p show the contents of the query buffer \r reset (clear) the query buffer \s [FILE] display history or save it to file \w FILE write query buffer to file Input/Output \copy ... perform SQL COPY with data stream to the client host \echo [STRING] write string to standard output \i FILE execute commands from file \o [FILE] send all query results to file or | pipe \qecho [STRING] write string to query output stream (see \o) Informational (options: S = show system objects, + = additional detail) \d[S+] list tables, views, and sequences \d[S+] NAME describe table, view, sequence, or index
Andrzej Leśnicki Bazy Danych, Wykład 2010 3/28
\da[+] [PATTERN] list aggregates \db[+] [PATTERN] list tablespaces \dc[S] [PATTERN] list conversions \dC [PATTERN] list casts \dd[S] [PATTERN] show comments on objects \dD[S] [PATTERN] list domains \des[+] [PATTERN] list foreign servers \deu[+] [PATTERN] list user mappings \dew[+] [PATTERN] list foreign-data wrappers \df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions \dF[+] [PATTERN] list text search configurations \dFd[+] [PATTERN] list text search dictionaries \dFp[+] [PATTERN] list text search parsers \dFt[+] [PATTERN] list text search templates \dg[+] [PATTERN] list roles (groups) \di[S+] [PATTERN] list indexes \dl list large objects, same as \lo_list \dn[+] [PATTERN] list schemas \do[S] [PATTERN] list operators \dp [PATTERN] list table, view, and sequence access privileges \ds[S+] [PATTERN] list sequences \dt[S+] [PATTERN] list tables \dT[S+] [PATTERN] list data types \du[+] [PATTERN] list roles (users) \dv[S+] [PATTERN] list views \l[+] list all databases \z [PATTERN] same as \dp Formatting \a toggle between unaligned and aligned output mode \C [STRING] set table title, or unset if none \f [STRING] show or set field separator for unaligned query output \H toggle HTML output mode (currently off) \pset NAME [VALUE] set table output option (NAME := {format|border|expanded|fieldsep|footer|null| numericlocale|recordsep|tuples_only|title|tableattr|pager}) \t [on|off] show only rows (currently off) \T [STRING] set HTML <table> tag attributes, or unset if none \x [on|off] toggle expanded output (currently off) Connection \c[onnect] [DBNAME|- USER|- HOST|- PORT|-] connect to new database (currently "student123") \encoding [ENCODING] show or set client encoding \password [USERNAME] securely change the password for a user Operating System \cd [DIR] change the current working directory \timing [on|off] toggle timing of commands (currently off)
Andrzej Leśnicki Bazy Danych, Wykład 2010 4/28
\! [COMMAND] execute command in shell or start interactive shell Variables \prompt [TEXT] NAME prompt user to set internal variable \set [NAME [VALUE]] set internal variable, or list all if no parameters \unset NAME unset (delete) internal variable Large Objects \lo_export LOBOID FILE \lo_import FILE [COMMENT] \lo_list \lo_unlink LOBOID large object operations
2. Opis języka
2.1. Wprowadzenie
{ }4321 ,,, ttttr = ( ) ( ) ( ) ({ }7.88,55,Wolski'',5.63,108,Dragan'',0.75,103,Jarosz'',8.55,101,'Kowalski' )
nazwisko numer_pokoju ciezar_pracownika Kowalski 101 55.8 Jarosz 103 75.0 Dragan 108 63.5 Wolski 55 88.7
znak - dane - informacja 2.2. Składnia języka CREATE TABLE --treść komentarza przedmioty(); CREATE TABLE --treść komentarza przedmioty(); lub taki zapis CREATE TABLE /*treść komentarza */ przedmioty();
Andrzej Leśnicki Bazy Danych, Wykład 2010 5/28
Łańcuchy $function$ BEGIN RETURN ($1 ~ $q$[\t\r\n\v\\]$q$); END; $function$ Operatory + - * / < > = ~ ! @ # % ^ & | ‘ ? Operatory logiczne, to NOT, AND, OR. <> lub != not equal Znaki specjalne Typy danych
Nazwa Aliasy Opis
bigint int8 8-bajtowa liczba całkowita -9223372036854775808 do 9223372036854775807
bigserial serial8 8-bajtowa liczba naturalna z automatycznym przyrostem 1 do 9223372036854775807
bit [(n)] łańcuch bitów o stałej długości np. BIT(3) B’101’ →
bit varying [(n)] varbit łańcuch bitów o stałej długości boolean bool wartość logiczna (true/false)
box prostokąt na płaszczyźnie 32 bajty ((x1,y1),(x2,y2))
bytea dane binarne ("byte array") character varying [(n)] varchar [(n)] łańcuch znaków o zmiennej długości character [(n)] char [(n)] łańcuch znaków o stałej długości cidr IPv4 lub IPv6 adres sieciowy, 7 lub 19B
circle okrąg na płaszczyźnie, 24 bajty <(x,y),r> środek i promień
date data kalendarzowa (rok, miesiąc, dzień), 4 bajty, 4713BC do 5874897AD, dokł. 1dz
double precision float8 liczba zmiennoprzecinkowa o podwójnej precyzji (8 bajtów)
Andrzej Leśnicki Bazy Danych, Wykład 2010 6/28
Nazwa Aliasy Opis precyzja 15 cyfr dziesiętnych
inet IPv4 lub IPv6 adres hosta, 7 lub 19B integer int, int4 4-bajtowa liczba całkowita
interval [fields] [(p)] przedział czasu, 12 bajtów, dokł. 1 , sµ-178000000 lat do 178000000 lat
line linia nieskończona na płaszczyźnie 32 bajty ((x1,y1),(x2,y2))
lseg odcinek linii na płaszczyźnie, 32 bajty, ((x1,y1),(x2,y2))
macaddr MAC (Media Access Control) adres, 6B money ilość waluty numeric [(p,s)] decimal[(p,s)] dokładna liczba o wybranej precyzji
path ścieżka geometryczna na płaszczyźnie ((x1,y1),...)
point punkt geometryczny na płaszczyźnie 16 bajtów, (x,y)
polygon zamknięta ścieżka geometryczna na płaszczyźnie ((x1,y1),...)
real float4 liczba zmiennoprzecinkowa o pojedynczej precyzji (4 bajty) precyzja 6 cyfr dziesiętnych
smallint int2 2-bajtowa liczba całkowita -32768 do +32767
serial serial4 4-bajtowa liczba naturalna z automatycznym przyrostem 1 do 2147483647
text łańcuch znaków o zmiennej długości time [(p)] [without time zone]
czas dnia (bez strefy czasu), 8 bajtów 00:00:00 do 24:00:00, dokł. 1 , 14 cyfr sµ
time [(p)] with time zone timetz czas dnia (ze strefą czasu), 12 bajtów 00:00:00+1459 do 24:00:00-1459 dokł. 1 , 14 cyfr sµ
timestamp [(p)] [without time zone]
data i czas (bez strefy czasu), 8 bajtów 4713BC do 294276AD, dokł. 1 , 14 cyfr sµ
timestamp [(p)] with time zone timestamptz data i czas (ze strefą czasu), 8 bajtów
4713BC do 294276AD, dokł.1 , 14 cyfr sµtsquery tekst szukania zapytania tsvector tekst szukania dokumentu txid_snapshot transakcja na poziomie użytkownika ID uuid universally unique identifier
Andrzej Leśnicki Bazy Danych, Wykład 2010 7/28
Nazwa Aliasy Opis xml XML data Matematyczne operatory Operator Opis Przykład Wynik + dodawanie 2 + 3 5 - odejmowanie 2 - 3 -1 * mnożenie 2 * 3 6 / dzielenie(całkowite dzielenie obcina wynik) 4 / 2 2 % modulo (reszta) 5 % 4 1 ^ podnoszenie do potęgi 2.0 ^ 3.0 8 |/ pierwiastek kwadratowy |/ 25.0 5 ||/ pierwiastek sześcienny ||/ 27.0 3 ! silnia 5 ! 120 !! silnia (prefix operator) !! 5 120 @ wartość bezwzględna (moduł) @ -5.0 5 & bitwise AND 91 & 15 11 | bitwise OR 32 | 3 35 # bitwise XOR 17 # 5 20 ~ bitwise NOT ~1 -2 << bitwise shift left 1 << 4 16 >> bitwise shift right 8 >> 2 2
( ) ( )210 10000032 = ( ) ( )210 113 = Funkcje matematyczne
Funkcja Zwraca Typ Opis Przykład Wynik
abs(x) (same as input)
wartość bezwzględna
abs(-17.4) 17.4
cbrt(dp) dp 3 cbrt(27.0) 3
ceil(dp or numeric)
(same as input)
najmniejsza liczba całkowita nie mniejsza niż argument
ceil(-42.8) -42
ceiling(dp or numeric)
(taki sam jak typ alias dla ceil ceiling(-95.3) -95
Andrzej Leśnicki Bazy Danych, Wykład 2010 8/28
Funkcja Zwraca Typ Opis Przykład Wynik
wejścia)
degrees(dp) dp radiany na stopnie
degrees(0.5) 28.6478897565412
div(y numeric, x numeric) numeric Część całkowita
z y/x div(9,4) 2
exp(dp or numeric)
(taki sam jak typ wejścia)
exponential exp(1.0) 2.71828182845905
floor(dp or numeric)
(taki sam jak typ wejścia)
największa liczba całkowita nie większa niż argument
floor(-42.8) -43
ln(dp or numeric)
(taki sam jak typ wejścia)
naturalny logarytm
ln(2.0) 0.693147180559945
log(dp or numeric)
(taki sam jak typ wejścia)
podstawa 10 logarytm
log(100.0) 2
log(b numeric, x numeric) numeric logarytm o
podstawie b log(2.0, 64.0) 6.0000000000
mod(y, x) (taki sam jak typ wejścia)
reszta z y/x modulo mod(9,4) 1
pi() dp stała "π" pi() 3.14159265358979 power(a dp, b dp) dp potęga a b power(9.0, 3.0) 729
power(a numeric, b numeric) numeric potęga a b power(9.0, 3.0) 729
radians(dp) dp stopnie na radiany
radians(45.0) 0.785398163397448
random() dp Wartość przypadkowa z przedziału 0.0 <= x < 1.0
random()
round(dp or numeric)
(taki sam jak typ wejścia)
zaokrąglenie do najbliższej liczby całkowitej
round(42.4) 42
round(v numeric, s int) numeric
zaokrąglenie do s-tego miejsca po przecinku
round(42.4382, 2) 42.44
setseed(dp) void ziarno dla funkcji
setseed(0.54823)
Andrzej Leśnicki Bazy Danych, Wykład 2010 9/28
Funkcja Zwraca Typ Opis Przykład Wynik
random() (wartość pomiędzy -1.0 and 1.0, włącznie)
sign(dp or numeric)
(taki sam jak typ wejścia)
znak argumentu (-1, 0, +1)
sign(-8.4) -1
sqrt(dp or numeric)
(taki sam jak typ wejścia)
2 sqrt(2.0) 1.4142135623731
trunc(dp or numeric)
(taki sam jak typ wejścia)
Obcięcie w kierunku zera
trunc(42.8) 42
trunc(v numeric, s int) numeric
Obcięcie do s-tego miejsca po przecinku
trunc(42.4382, 2) 42.43
width_bucket(op numeric, b1 numeric, b2 numeric, count int)
int
return the bucket to which operand would be assigned in an equidepth histogram with count buckets, in the range b1 to b2
width_bucket(5.35, 0.024, 10.06, 5)
3
width_bucket(op dp, b1 dp, b2 dp, count int)
int
return the bucket to which operand would be assigned in an equidepth histogram with count buckets, in the range b1 to b2
width_bucket(5.35, 0.024, 10.06, 5)
3
Funkcje trygonometryczne
Funkcja Opis acos(x) arcus cos asin(x) arcus sin atan(x) arcus tangens atan2(y, x) arcus tangens z y/x
Andrzej Leśnicki Bazy Danych, Wykład 2010 10/28
Funkcja Opis cos(x) cos cot(x) cotangents sin(x) sin tan(x) tangens Funkcje agregujące ogólnego przeznaczenia
Andrzej Leśnicki Bazy Danych, Wykład 2010 11/28
Funkcja Argumentu Typ Zwraca Typ Opis
array_agg(expression) any array of the argument type input values concatenated into an array
avg(expression)
smallint, int, bigint, real, double precision, numeric, or interval
numeric for any integer-type argument, double precision for a floating-point argument, otherwise the same as the argument data type
the average (arithmetic mean) of all input values
bit_and(expression) smallint, int, bigint, or bit
same as argument data type
the bitwise AND of all non-null input values, or null if none
bit_or(expression) smallint, int, bigint, or bit
same as argument data type
the bitwise OR of all non-null input values, or null if none
bool_and(expression) bool bool true if all input values are true, otherwise false
bool_or(expression) bool bool true if at least one input value is true, otherwise false
count(*) bigint number of input rows
count(expression) any bigint
number of input rows for which the value of expression is not null
every(expression) bool bool equivalent to bool_and
max(expression) any array, numeric, string, or date/time type
same as argument type maximum value of expression across all input values
min(expression) any array, numeric, string, or date/time type
same as argument type minimum value of expression across all input values
sum(expression)
smallint, int, bigint, real, double precision, numeric, or interval
bigint for smallint or int arguments, numeric for bigint arguments, double precision for floating-point arguments, otherwise the same as the argument data type
sum of expression across all input values
xmlagg(expression) xml xml concatenation of XML values
Andrzej Leśnicki Bazy Danych, Wykład 2010 12/28
Funkcje agregujące dla statystyki
Funkcja Argumentu Typ Zwraca Typ Opis
corr(Y, X) double precision
double precision correlation coefficient
covar_pop(Y, X) double precision
double precision population covariance
covar_samp(Y, X) double precision
double precision sample covariance
regr_avgx(Y, X) double precision
double precision
average of the independent variable (sum(X)/N)
regr_avgy(Y, X) double precision
double precision
average of the dependent variable (sum(Y)/N)
regr_count(Y, X) double precision bigint
number of input rows in which both expressions are nonnull
regr_intercept(Y, X) double precision
double precision
y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs
regr_r2(Y, X) double precision
double precision
square of the correlation coefficient
regr_slope(Y, X) double precision
double precision
slope of the least-squares-fit linear equation determined by the (X, Y) pairs
regr_sxx(Y, X) double precision
double precision
sum(X^2) - sum(X)^2/N ("sum of squares" of the independent variable)
regr_sxy(Y, X) double precision
double precision
sum(X*Y) - sum(X) * sum(Y)/N ("sum of products" of independent times dependent variable)
regr_syy(Y, X) double precision
double precision
sum(Y^2) - sum(Y)^2/N ("sum of squares" of the dependent variable)
stddev(expression) smallint, int, bigint, real, double precision, or
double precision for floating-point arguments,
historical alias for stddev_samp
Andrzej Leśnicki Bazy Danych, Wykład 2010 13/28
Funkcja Argumentu Typ Zwraca Typ Opis numeric otherwise
numeric
stddev_pop(expression)
smallint, int, bigint, real, double precision, or numeric
double precision for floating-point arguments, otherwise numeric
population standard deviation of the input values
stddev_samp(expression)
smallint, int, bigint, real, double precision, or numeric
double precision for floating-point arguments, otherwise numeric
sample standard deviation of the input values
variance(expression)
smallint, int, bigint, real, double precision, or numeric
double precision for floating-point arguments, otherwise numeric
historical alias for var_samp
var_pop(expression)
smallint, int, bigint, real, double precision, or numeric
double precision for floating-point arguments, otherwise numeric
population variance of the input values (square of the population standard deviation)
var_samp(expression)
smallint, int, bigint, real, double precision, or numeric
double precision for floating-point arguments, otherwise numeric
sample variance of the input values (square of the sample standard deviation)
Andrzej Leśnicki Bazy Danych, Wykład 2010 14/28
Funkcje systemu informatycznego
Nazwa Zwraca Typ Opis
current_catalog name name of current database (called "catalog" in the SQL standard)
current_database() name name of current database current_schema[()] name name of current schema
current_schemas(boolean) name[] names of schemas in search path optionally including implicit schemas
current_user name user name of current execution context
current_query text text of the currently executing query, as submitted by the client (might contain more than one statement)
pg_backend_pid() int Process ID of the server process attached to the current session
inet_client_addr() inet address of the remote connection inet_client_port() int port of the remote connection inet_server_addr() inet address of the local connection inet_server_port() int port of the local connection
pg_my_temp_schema() oid OID of session's temporary schema, or 0 if none
pg_is_other_temp_schema(oid) boolean is schema another session's temporary schema?
pg_postmaster_start_time() timestamp with time zone
server start time
pg_conf_load_time() timestamp with time zone
configuration load time
session_user name session user name user name equivalent to current_user version() text PostgreSQL version information
Funkcje daty/czasu
Funkcja Zwraca Typ Opis Przykład Wynik
age(timestamp, timestamp) interval
Odejmuje argumenty, dając “symboliczny” wynik z latami i miesiącami
age(timestamp '2001-04-10', timestamp '1957-06-13')
43 years 9 mons 27 days
Andrzej Leśnicki Bazy Danych, Wykład 2010 15/28
Funkcja Zwraca Typ Opis Przykład Wynik
age(timestamp) interval Odejmuje od current_date (o północy)
age(timestamp '1957-06-13')
43 years 8 mons 3 days
clock_timestamp() timestamp with time zone
Bieżąca data i czas (zmienia się w trakcie wykonywania polecenia)
current_date date Bieżąca data
current_time time with time zone Bieżący czas dnia
current_timestamp timestamp with time zone
Bieżąca data i czas (początek bieżącej transakcji)
date_part(text, timestamp) double precision Pobranie podpola (równoważne extract)
date_part('hour', timestamp '2001-02-16 20:38:40')
20
date_part(text, interval) double precision
Pobranie podpola (równoważne extract)
date_part('month', interval '2 years 3 months')
3
date_trunc(text, timestamp) timestamp Obcięcie do
zadanej precyzji date_trunc('hour', timestamp '2001-02-16 20:38:40')
2001-02-16 20:00:00
extract(field from timestamp)
double precision Pobranie podpola
extract(hour from timestamp '2001-02-16 20:38:40')
20
extract(field from interval)
double precision Pobranie podpola
extract(month from interval '2 years 3 months')
3
isfinite(date) boolean Testowanie, czy data jest skończona (not +/-infinity)
isfinite(date '2001-02-16') true
isfinite(timestamp) boolean Testowanie, czy jest skończony stempel czasu (not +/-infinity)
isfinite(timestamp '2001-02-16 21:28:30') true
isfinite(interval) boolean Testowanie, czy jest skończony przedział
isfinite(interval '4 hours') true
justify_days(interval) interval
Uporządkuj przedział tak, aby okresy 38-dniowe były przedst. jako miesiące
justify_days(interval '35 days')
1 mon 5 days
Andrzej Leśnicki Bazy Danych, Wykład 2010 16/28
Funkcja Zwraca Typ Opis Przykład Wynik
justify_hours(interval) interval
Uporządkuj przedział tak, aby okresy 24-godz. były przedst. jako dni
justify_hours(interval '27 hours')
1 day 03:00:00
justify_interval(interval) interval
Uporządkuj przedział używając justify_days i justify_hours, z dodatkowym znakiem uporządkowania
justify_interval(interval '1 mon -1 hour')
29 days 23:00:00
localtime time Bieżący lokalny czas dnia
localtimestamp timestamp Bieżąca data i lokalny czas (początek bieżącej transakcji)
now() timestamp with time zone
Bieżąca data i lokalny czas (początek bieżącej transakcji)
statement_timestamp() timestamp with time zone
Bieżąca data i lokalny czas (początek bieżącego polecenia)
timeofday() text
Bieżąca data i czas (podobnie jak clock_timestamp, ale z łańcuchem tekstu)
transaction_timestamp() timestamp with time zone
Bieżąca data i czas (początek bieżącej transakcji)
2.3. Zakładanie tabeli CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [ { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ] | table_constraint | LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES } ] ... } [, ... ]
Andrzej Leśnicki Bazy Danych, Wykład 2010 17/28
] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace ] where column_constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE index_parameters | PRIMARY KEY index_parameters | CHECK ( expression ) | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] and table_constraint is: [ CONSTRAINT constraint_name ] { UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] index_parameters in UNIQUE and PRIMARY KEY constraints are: [ WITH ( storage_parameter [= value] [, ... ] ) ] [ USING INDEX TABLESPACE tablespace ] lub w skrócie: CREATE TABLE <table name> (<column definition> [,...<last column definition] [<primary key definition>] [<foreign key definition>]); <column definition> jest zdefiniowane następująco: <column name> <data-type> [DEFAULT <value>] [NOT NULL] [UNIQUE] [<check constraint definition>] <check constraint definition> jest zdefiniowane następująco: CHECK (<search condition>) <primary key definition> jest zdefiniowane następująco: PRIMARY KEY (<column name> [, <column name>]) <foreign key definition> jest zdefiniowane następująco: FOREIGN KEY (<column name>) REFERENCES <table name>
Andrzej Leśnicki Bazy Danych, Wykład 2010 18/28
DROP TABLE zabawki; DROP TABLE dzieci; 2.4. Modyfikowanie tabeli ALTER TABLE [ ONLY ] name [ * ] action [, ... ] ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name ALTER TABLE name SET SCHEMA new_schema where action is one of: ADD [ COLUMN ] column type [ column_constraint [ ... ] ] DROP [ COLUMN ] column [ RESTRICT | CASCADE ] ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ] ALTER [ COLUMN ] column SET DEFAULT expression ALTER [ COLUMN ] column DROP DEFAULT ALTER [ COLUMN ] column { SET | DROP } NOT NULL ALTER [ COLUMN ] column SET STATISTICS integer ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ADD table_constraint DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE REPLICA TRIGGER trigger_name ENABLE ALWAYS TRIGGER trigger_name DISABLE RULE rewrite_rule_name ENABLE RULE rewrite_rule_name ENABLE REPLICA RULE rewrite_rule_name ENABLE ALWAYS RULE rewrite_rule_name CLUSTER ON index_name SET WITHOUT CLUSTER SET WITH OIDS SET WITHOUT OIDS SET ( storage_parameter = value [, ... ] ) RESET ( storage_parameter [, ... ] ) INHERIT parent_table NO INHERIT parent_table OWNER TO new_owner SET TABLESPACE new_tablespace ALTER TABLE foo ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone USING timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
Andrzej Leśnicki Bazy Danych, Wykład 2010 19/28
2.5. Wstawianie i modyfikowanie danych w tabeli INSERT INTO table [ ( column [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] COPY tablename [ ( column [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ HEADER ] [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE NOT NULL column [, ...] ] COPY { tablename [ ( column [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ HEADER ] [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE QUOTE column [, ...] ] UPDATE [ ONLY ] table [ [ AS ] alias ] SET { column = { expression | DEFAULT } | ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ FROM fromlist ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] 2.6. Zapytania SELECT [ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ [ AS ] output_name ] [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ]
Andrzej Leśnicki Bazy Danych, Wykład 2010 20/28
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ] where from_item can be one of: [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ] and with_query is: with_query_name [ ( column_name [, ...] ) ] AS ( select ) TABLE { [ ONLY ] table_name [ * ] | with_query_name } Cross join (złączenie krzyżowe) T1 CROSS JOIN T2 Qualified joins (kwalifikowane złączenia) T1 {[INNER]|{LEFT|RIGHT|FULL}[OUTER]} JOIN T2 ON boolean_expression
T1 {[INNER]|{LEFT|RIGHT|FULL}[OUTER]} JOIN T2 USING (join column list) T1 NATURAL {[INNER]|{LEFT|RIGHT|FULL}[OUTER]} JOIN T2
<SELECT . . . > UNION[ALL] <SELECT . . . > <SELECT . . . > INTERSECT[ALL] <SELECT . . . > <SELECT . . . > EXCEPT[ALL] <SELECT . . . > 2.7. Widoki (perspektywy) CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ] AS query ALTER VIEW name ALTER [ COLUMN ] column SET DEFAULT expression ALTER VIEW name ALTER [ COLUMN ] column DROP DEFAULT ALTER VIEW name OWNER TO new_owner ALTER VIEW name RENAME TO new_name ALTER VIEW name SET SCHEMA new_schema
Andrzej Leśnicki Bazy Danych, Wykład 2010 21/28
DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] 2.8. Operatory i funkcje Operatory logiczne i porównania
a b a AND b a OR b TRUE TRUE TRUE TRUE TRUE FALSE FALSE TRUE TRUE NULL NULL TRUE FALSE FALSE FALSE FALSE FALSE NULL FALSE NULL NULL NULL NULL NULL
a NOT a TRUE FALSE FALSE TRUE NULL NULL Operator Description < less than > greater than <= less than or equal to >= greater than or equal to = equal <> or != not equal expression IS NULL expression IS NOT NULL expression ISNULL expression NOTNULL expression IS DISTINCT FROM expression expression IS NOT DISTINCT FROM expression
Andrzej Leśnicki Bazy Danych, Wykład 2010 22/28
expression IS TRUE expression IS NOT TRUE expression IS FALSE expression IS NOT FALSE expression IS UNKNOWN expression IS NOT UNKNOWN
Funkcje agregujące
Andrzej Leśnicki Bazy Danych, Wykład 2010 23/28
Funkcja Argumentu Typ Zwraca Typ Opis
array_agg(expression) any array of the argument type input values concatenated into an array
avg(expression)
smallint, int, bigint, real, double precision, numeric, or interval
numeric for any integer-type argument, double precision for a floating-point argument, otherwise the same as the argument data type
the average (arithmetic mean) of all input values
bit_and(expression) smallint, int, bigint, or bit
same as argument data type
the bitwise AND of all non-null input values, or null if none
bit_or(expression) smallint, int, bigint, or bit
same as argument data type
the bitwise OR of all non-null input values, or null if none
bool_and(expression) bool bool true if all input values are true, otherwise false
bool_or(expression) bool bool true if at least one input value is true, otherwise false
count(*) bigint number of input rows
count(expression) any bigint
number of input rows for which the value of expression is not null
every(expression) bool bool equivalent to bool_and
max(expression) any array, numeric, string, or date/time type
same as argument type maximum value of expression across all input values
min(expression) any array, numeric, string, or date/time type
same as argument type minimum value of expression across all input values
sum(expression)
smallint, int, bigint, real, double precision, numeric, or interval
bigint for smallint or int arguments, numeric for bigint arguments, double precision for floating-point arguments, otherwise the same as the argument data type
sum of expression across all input values
xmlagg(expression) xml xml concatenation of XML values
Andrzej Leśnicki Bazy Danych, Wykład 2010 24/28
Funkcje definiowane przez użytkownika CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } defexpr ] [, ...] ] ) [ RETURNS rettype | RETURNS TABLE ( colname coltype [, ...] ) ] { LANGUAGE langname | WINDOW | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | COST execution_cost | ROWS result_rows | SET configuration_parameter { TO value | = value | FROM CURRENT } | AS 'definition' | AS 'obj_file', 'link_symbol' } ... [ WITH ( attribute [, ...] ) ] ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) RENAME TO new_name ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) OWNER TO new_owner DROP FUNCTION [ IF EXISTS ] name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [ CASCADE | RESTRICT ] 2.9. Wyzwalacze CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname ( arguments ) : CREATE TRIGGER trigbefore BEFORE INSERT OR UPDATE OR DELETE ON tabtest FOR EACH ROW EXECUTE PROCEDURE funkcja_wyzwalacza( ); : CREATE TRIGGER trigafter AFTER INSERT OR UPDATE OR DELETE ON tabtest FOR EACH ROW EXECUTE PROCEDURE funkcja_wyzwalacza( );
Andrzej Leśnicki Bazy Danych, Wykład 2010 25/28
ALTER TRIGGER name ON table RENAME TO newname DROP TRIGGER [ IF EXISTS ] name ON table [ CASCADE | RESTRICT ] Faza
Wydarzenie BEFORE
AFTER
INSERT
Jest możliwe zawetowanie wstawienia. Wstawiane wartości są dostępne w rekordzie NEW. Modyfikowanie zawartości NEW spowoduje wstawienie zmodyfikowanych danych. W przypadku braku weta funkcja wyzwalacza powinna zwrócić NEW.
Dane znajdują się już fizycznie w tabeli. Wstawione dane są dostępne w rekordzie NEW.
UPDATE
Jest możliwe zawetowanie uaktualnienia. Stare wartości są dostępne w rekordzie OLD, a nowe w NEW. Modyfikowanie zawartości NEW spowoduje wstawienie zmodyfikowanych danych. W przypadku braku weta funkcja wyzwalacza powinna zwrócić NEW.
Dane znajdują się już fizycznie w tabeli. Wstawione dane są dostępne w rekordzie NEW, stare są dostępne w OLD.
DELETE
Jest możliwe zawetowanie usunięcia. Nie ma możliwości uaktualnienia zamiast usunięcia. Skasowane wartości są dostępne w rekordzie OLD.
Rekord jest już fizycznie skasowany. Pola skasowanego rekordu są dostępne w OLD.
2.10. Transakcje Aby ułatwić zapamiętanie warunków jakie ma spełniać bezpieczna transakcja, warunki te nazwano w skrócie ACID (z ang. Atomic, Consistent, Isolated, Durable), czyli transakcja ma być atomowa, spójna, izolowana, trwała. BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ] where transaction_mode is one of:
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY
Andrzej Leśnicki Bazy Danych, Wykład 2010 26/28
START TRANSACTION [ transaction_mode [, ...] ] where transaction_mode is one of: ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY SET TRANSACTION transaction_mode [, ...] SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...] where transaction_mode is one of: ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY COMMIT [ WORK | TRANSACTION ] END [ WORK | TRANSACTION ] ROLLBACK [ WORK | TRANSACTION ] ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name SAVEPOINT savepoint_name 2.11. Bezpieczeństwo w SQL ALTER TABLE table_name OWNER TO new_owner ; GRANT UPDATE ON pracownicy TO student123 ; GRANT ALL ON pracownicy TO PUBLIC ; REVOKE UPDATE ON pracownicy FROM student123 ; REVOKE ALL ON pracownicy FROM PUBLIC ; GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] ) [,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
Andrzej Leśnicki Bazy Danych, Wykład 2010 27/28
ON [ TABLE ] tablename [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] ) [,...] | ALL [ PRIVILEGES ] ( column [, ...] ) } ON [ TABLE ] tablename [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
Andrzej Leśnicki Bazy Danych, Wykład 2010 28/28
2.12. Indeksy CREATE TABLE zabawki ( numer_zabawki int , nazwa_zabawki text , cena numeric ) ; SELECT nazwa_zabawki FROM zabawki WHERE cena<20 ; CREATE INDEX zabawki_cena_indeks ON zabawki(cena); CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ] ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace ] [ WHERE predicate ] ALTER INDEX name RENAME TO new_name ALTER INDEX name SET TABLESPACE tablespace_name ALTER INDEX name SET ( storage_parameter = value [, ... ] ) ALTER INDEX name RESET ( storage_parameter [, ... ] ) DROP INDEX [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] 2.13. Kursory DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query FETCH [ direction { FROM | IN } ] cursorname where direction can be empty or one of: NEXT PRIOR FIRST LAST ABSOLUTE n RELATIVE n count ALL FORWARD FORWARD n FORWARD ALL BACKWARD BACKWARD n BACKWARD ALL MOVE [ direction { FROM | IN } ] cursorname MOVE n CLOSE { name | ALL }