CONTENT

CHANGES

Szukaj

#top Zapytania SQL¶

[<<]|[<]|[^]|[v]|[>]|[>>]|[Zwiń]|[#top]|[X]

#top users¶

#top USER CREATE¶

CREATE USER username PASSWORD 'password'
   [FIRSTNAME 'firstname']
   [MIDDLENAME 'middlename']
   [LASTNAME 'lastname']
   [GRANT ADMIN ROLE]

Example:

-- comment

#top USER ALTER¶

ALTER USER username
   [PASSWORD 'password']
   [FIRSTNAME 'firstname']
   [MIDDLENAME 'middlename']
   [LASTNAME 'lastname']
   [{GRANT|REVOKE} ADMIN ROLE]

-- At least one of the optional parameters must be present.
-- GRANT/REVOKE ADMIN ROLE is reserved to privileged users.

Example:

-- comment

#top USER SET PASSWORD¶

ALTER USER username
   [PASSWORD 'password']

Example:

-- comment

#top USER DROP¶

DROP USER username

Example:

-- comment

#top permissions¶

#top GRANT¶

GRANT
   {<privileges> ON <object> | role}
   TO <grantees>
   [WITH {GRANT|ADMIN} OPTION]
   [{GRANTED BY | AS} [USER] grantor]

Example:

-- comment

#top REVOKE¶

REVOKE
   [{GRANT|ADMIN} OPTION FOR]
   {<privileges> ON <object> | role}
   FROM <grantees>
   [{GRANTED BY | AS} [USER] grantor]

Example:

-- comment

#top SHOW GRANTS¶

comment

Example:

-- comment

#top databases¶

#top DATABASE CREATE¶

CREATE {DATABASE | SCHEMA}
   ...
   [PAGE_SIZE [=] size]
   ...
   [DEFAULT CHARACTER SET charset [COLLATION collation]]
   ...
   [DIFFERENCE FILE 'filepath']

size  ::=  4096 | 8192 | 16384

Example:

-- comment

#top DATABASE ALTER¶

ALTER {DATABASE | SCHEMA}
   [<add_sec_clause> [<add_sec_clause> ...]]
   [ADD DIFFERENCE FILE 'filepath' | DROP DIFFERENCE FILE]
   [{BEGIN | END} BACKUP]

<add_sec_clause>  ::=  ADD <sec_file> [<sec_file> ...]

<sec_file>        ::=  FILE 'filepath'
                          [STARTING [AT [PAGE]] pagenum]
                          [LENGTH [=] num [PAGE[S]]

Example:

-- comment

#top DATABASE DROP¶

comment

Example:

-- comment

#top schemas¶

#top SCHEMA CREATE¶

comment

Example:

-- comment

#top SCHEMA ALTER¶

comment

Example:

-- comment

#top SCHEMA DROP¶

comment

Example:

-- comment

#top tablespaces¶

#top TABLESPACE CREATE¶

comment

Example:

-- comment

#top TABLESPACE ALTER¶

comment

Example:

-- comment

#top TABLESPACE DROP¶

comment

Example:

-- comment

#top tables¶

#top TABLE CREATE¶

CREATE GLOBAL TEMPORARY TABLE name
   (column_def [, column_def | table_constraint ...])
   [ON COMMIT {DELETE | PRESERVE} ROWS]

#top TABLE CREATE LIKE¶

-- comment

#top TABLE CREATE AS¶

-- comment

#top TABLE CREATE with PRIMARY KEY¶

-- comment

#top TABLE CREATE with auto_increment¶

-- comment

#top TABLE CREATE with UNIQUE KEY (singlecolumn)¶

-- comment

#top TABLE CREATE with UNIQUE KEY (multicolumn)¶

-- comment

#top TABLE CREATE with FULLTEXT KEY¶

-- comment

#top TABLE CREATE with FOREIGN KEY¶

-- comment

#top TABLE CREATE with INDEX¶

-- comment

#top TABLE CREATE with partitioned¶

-- comment

#top TABLE CREATE with COLLATE without UNIQUE¶

-- comment

#top TABLE CREATE with COLLATE with UNIQUE¶

-- comment

#top TABLE ALTER¶

alter table MyData
  add MyDay date default current_date

#top TABLE ALTER with PRIMARY KEY¶

-- comment

#top TABLE ALTER with auto increment¶

-- comment

#top TABLE ALTER with UNIQUE KEY (singlecolumn)¶

-- comment

#top TABLE ALTER with UNIQUE KEY (multicolumn)¶

-- comment

#top TABLE ALTER with FULLTEXT KEY¶

-- comment

#top TABLE ALTER with FOREIGN KEY¶

-- comment

#top TABLE ALTER with INDEX¶

-- comment

#top TABLE ALTER with partitioned¶

-- comment

#top TABLE ALTER with COLLATE without UNIQUE¶

-- comment

#top TABLE ALTER with COLLATE with UNIQUE¶

-- comment

#top TABLE RENAME¶

Zobacz także TABLE RENAME dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Brak obsługi zmiany nazw tabel!!! Przeczytaj poniższy tekst przedstawiający możliwości realizacji tej operacji
zaczerpnięty ze strony http://www.firebirdfaq.org/faq363/.

How to rename a database table?
This is not possible. You have two options:

1. Create a new table with a different name. You can use your favorite administration tool to extract DDL statement for the old table and just replace the table name. After you create a new table, copy the data using a statement like this:

insert into newtable select * from oldtable;

2. Create a view that is identical to your old table. In fact, you get to keep the table, and use the view as a kind of an alias:

create view newname (columns...)
as
select columns...
from oldtable;

Such plain VIEWS even allow DML statement like UPDATE, INSERT and DELETE and will change the records in the underlying table.
Make sure you test this well because different Firebird versions may behave differently regarding the automatic behavior of such views.

#top TABLE TRUNCATE¶

Zobacz także TABLE TRUNCATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Brak obsługi usuwania danych / zerowania / przycinania tabeli!!! Przeczytaj poniższy tekst przedstawiający możliwości realizacji tej operacji
zaczerpnięty ze strony http://www.firebirdnews.org/rfc-truncate-table-in-firebird/.

One of the often task in ETL applications is to quick erase all data in some table(s). Often amount of data is big or huge (tens of millions records).
In Firebird we have two choices currently – DELETE all rows or RECREATE TABLE. Both have its own drawbacks.
DELETE produced a lot of record versions to clean up, its slow as works row-by-row basis and fire triggers which is almost always is not needed.
RECREATE is free from this drawbacks but it will fail if there is dependencies and all related objects such as triggers, indices, constraints must be recreated too.

#top TABLE DROP¶

comment

Example:

-- comment

#top sequences¶

#top SEQUENCE CREATE¶

CREATE SEQUENCE sequence-name

Example:

-- comment

#top SEQUENCE ALTER¶

ALTER SEQUENCE sequence-name RESTART WITH <newval>

<newval>  ::=  A signed 64-bit integer value.

Example:

-- comment

#top SEQUENCE DROP¶

DROP SEQUENCE sequence-name

Example:

-- comment

#top indexes¶

#top INDEX CREATE¶

CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname
   ON tablename
   { (<col> [, <col> ...]) | COMPUTED BY (expression) }

<col>  ::=  a column not of type ARRAY, BLOB or COMPUTED BY

Example: alter table add INDEX

-- comment

#top INDEX ALTER¶

comment

Example: alter table add INDEX

-- comment

#top INDEX DROP¶

comment

Example: alter table drop INDEX

-- comment

#top functions¶

#top Functions and Operators¶

-- comment

#top DATA Manipulations¶

#top SELECT with LIMIT and OFFSET¶

SELECT [FIRST (<int-expr>)] [SKIP (<int-expr>)] <columns> FROM ...

<int-expr>  ::=  Any expression evaluating to an integer.
<columns>   ::=  The usual output column specifications.

Example:

CREATE TABLE temp_limit (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
"value" varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
);

INSERT INTO temp_limit(idtemp,name,"value") VALUES(1,'name-01','val-01');
INSERT INTO temp_limit(idtemp,name,"value") VALUES(2,'name-02','val-02');
INSERT INTO temp_limit(idtemp,name,"value") VALUES(3,'name-03','val-03');
INSERT INTO temp_limit(idtemp,name,"value") VALUES(4,'name-04','val-04');
INSERT INTO temp_limit(idtemp,name,"value") VALUES(5,'name-05','val-05');
INSERT INTO temp_limit(idtemp,name,"value") VALUES(6,'name-06','val-06');
INSERT INTO temp_limit(idtemp,name,"value") VALUES(7,'name-07','val-07');
INSERT INTO temp_limit(idtemp,name,"value") VALUES(8,'name-08','val-08');
INSERT INTO temp_limit(idtemp,name,"value") VALUES(9,'name-09','val-09');

Po wykonaniu powyższych zapytań mających na celu utworzenie tabeli oraz dodaniu przykładowych danych można wykonać poniższe zapytania w celu sprawdzenia działania klauzul LIMIT przy wyświetlaniu danych z tabeli:

SELECT FIRST 3 * FROM temp_limit;
SELECT FIRST 5 * FROM temp_limit;
SELECT FIRST 7 * FROM temp_limit;

Uzyskany rezultat powinien być analogiczny do poniższego:

|IDTEMP|NAME   |value |
+------+-------+------+
|1     |name-01|val-01|
|2     |name-02|val-02|
|3     |name-03|val-03|

Retrieved 3 rows in 0.005 seconds.

|IDTEMP|NAME   |value |
+------+-------+------+
|1     |name-01|val-01|
|2     |name-02|val-02|
|3     |name-03|val-03|
|4     |name-04|val-04|
|5     |name-05|val-05|

Retrieved 5 rows in 0.004 seconds.

|IDTEMP|NAME   |value |
+------+-------+------+
|1     |name-01|val-01|
|2     |name-02|val-02|
|3     |name-03|val-03|
|4     |name-04|val-04|
|5     |name-05|val-05|
|6     |name-06|val-06|
|7     |name-07|val-07|

Retrieved 7 rows in 0.004 seconds.

SELECT FIRST 3 SKIP 0 * FROM temp_limit;
SELECT FIRST 3 SKIP 3 * FROM temp_limit;
SELECT FIRST 3 SKIP 6 * FROM temp_limit;

Uzyskany rezultat powinien być analogiczny do poniższego:

|IDTEMP|NAME   |value |
+------+-------+------+
|1     |name-01|val-01|
|2     |name-02|val-02|
|3     |name-03|val-03|

Retrieved 3 rows in 0.004 seconds.

|IDTEMP|NAME   |value |
+------+-------+------+
|4     |name-04|val-04|
|5     |name-05|val-05|
|6     |name-06|val-06|

Retrieved 3 rows in 0.006 seconds.

|IDTEMP|NAME   |value |
+------+-------+------+
|7     |name-07|val-07|
|8     |name-08|val-08|
|9     |name-09|val-09|

Retrieved 3 rows in 0.004 seconds.

#top WHERE IN subselect with multiple columns¶

#top Deleting duplicates rows¶

#top REPLACE INTO¶

UPDATE OR INSERT INTO
   {tablename | viewname} [(<columns>)]
   VALUES (<values>)
   [MATCHING (<columns>)]
   [RETURNING <values> [INTO <variables>]]

<columns>      ::=  colname  [, colname  ...]
<values>       ::=  value    [, value    ...]
<variables>    ::=  :varname [, :varname ...]

Example:

-- comment

#top GROUP with CONCAT¶

-- comment

Zmodyfikowany ostatnio: 2017/11/01 23:57:28 (6 lat temu), textsize: 45,9 kB, htmlsize: 85,1 kB

Zapraszam do komentowania, zgłaszania sugestii, propozycji, własnych przykładów, ...

Dodaj komentarzKomentarze użytkowników