CONTENT

CHANGES

Szukaj

#top Zapytania SQL¶

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

#top users¶

#top USER CREATE¶

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

Dokumentacja IBM Informix:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_1821.htm
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_0472.htm
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_1823.htm

Składnia:

>>-CREATE------------------------------------------------------->

                        .-ACCOUNT UNLOCK-.
>--+-DEFAULT USER WITH--+----------------+--| Properties |--------------------------------+-><
   |                    '-ACCOUNT LOCK---'                                                |
   '-USER--user--+----------------------------------------------------------------------+-'
                 |                               .-ACCOUNT UNLOCK-.                     |
                 '-WITH--+--------------------+--+----------------+--+----------------+-'
                         '-PASSWORD--password-'  '-ACCOUNT LOCK---'  '-| Properties |-'

Properties

|--PROPERTIES--------------------------------------------------->

                                 .-,-------------------.
                            (1)  V                     |
>--+-UID--user_ID--GROUP--(--------+-surrog_group_ID-+-+--)----------+-->
   |                               '-surrog_group----'               |
   '-USER--surrog_user--+------------------------------------------+-'
                        |               .-,-------------------.    |
                        |          (1)  V                     |    |
                        '-GROUP--(--------+-surrog_group_ID-+-+--)-'
                                          '-surrog_group----'

>--+-----------------------+------------------------------------>
   '-HOME--"--directory--"-'

>--+---------------------------------------+--------------------|
   |                   .-,------------.    |
   |                   V              |    |
   '-AUTHORIZATION--(----+-DBSA-----+-+--)-'
                         +-DBSSO----+
                         +-AAO------+
                         '-BARGROUP-'

Example:

-- comment

#top USER ALTER¶

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

Dokumentacja IBM Informix:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_1824.htm
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_2304.htm
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_1186.htm

Składnia:

>>-ALTER--+-DEFAULT USER-------------------+-------------------->
          '-USER--user--+----------------+-'
                        +-ACCOUNT LOCK---+
                        '-ACCOUNT UNLOCK-'

   .-,------------------------------------------------------------.
   V                                                              |
>----+-+-ADD----+--+-PASSWORD--password-----------------------+-+-+-><
     | '-MODIFY-'  +-UID--user_ID-----------------------------+ |
     |             |           .-,-----------------------.    | |
     |             |           V  (1)                    |    | |
     |             +-GROUP--(--------+-surrog_group_ID-+-+--)-+ |
     |             |                 '-surrog_group----'      | |
     |             +-USER--surrog_user------------------------+ |
     |             |                   .-,------------.       | |
     |             |                   V              |       | |
     |             +-AUTHORIZATION--(----+-DBSA-----+-+--)----+ |
     |             |                     +-DBSSO----+         | |
     |             |                     +-AAO------+         | |
     |             |                     '-BARGROUP-'         | |
     |             '-HOME--"--directory--"--------------------' |
     '-DROP--+-PASSWORD------------------------------+----------'
             +-UID-----------------------------------+
             |           .-,-------------------.     |
             |           V                     |     |
             +-GROUP--(----+-surrog_group_ID-+-+--)--+
             |             '-surrog_group----'       |
             +-USER----------------------------------+
             |                   .-,------------.    |
             |                   V              |    |
             +-AUTHORIZATION--(----+-DBSA-----+-+--)-+
             |                     +-DBSSO----+      |
             |                     +-AAO------+      |
             |                     '-BARGROUP-'      |
             '-HOME----------------------------------'

Example:

-- comment

Example: USER RENAME

-- comment

#top USER SET PASSWORD¶

>>-SET USER PASSWORD OLD--old_password--NEW--new_password------><

Example:

-- comment

#top USER DROP¶

>>-DROP USER--user---------------------------------------------><

Example:

-- comment

#top permissions¶

#top GRANT¶

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

Dokumentacja IBM Informix:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_0828.htm
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/SSGU8G_11.70.0/com.ibm.ddi.doc/ids_ddi_105.htm
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_0852.htm

Składnia:

>>-GRANT-------------------------------------------------------->

      (1)                                  (2)
>--+-----+-+-| Database-Level Privileges |-----+--TO--+-PUBLIC---------+-+--+-><
   |     | |                             (3)   |      | .-,----------. | |  |
   |     | '-DEFAULT ROLE--| Role Name |-------'      | V            | | |  |
   |     |                                            '-----'user'---+-' |  |
   |     |                 (3)                                           |  |
   |     '-+-| Role Name |------| TO Options |-------+-------------------'  |
   |       |                                     (4) |                      |
   |       +-| Security Administration Options |-----+                      |
   |       |                               (5)       |                      |
   |       '-| Surrogate User Properties |-----------'                      |
   |                              (6)                                       |
   '-+-| Table-Level Privileges |-------------------------+--| TO Options |-'
     |  (1)                               (7)             |
     '-----+-| Routine-Level Privileges |---------------+-'
           |                                   (8)      |
           '---+-| Language-Level Privileges |------+---'
               |                           (9)      |
               +-| Type-Level Privileges |----------+
               |                               (10) |
               '-| Sequence-Level Privileges |------'

TO Options

         .-,----------.
         V            |
|--TO--+-----'user'---+--+-------------------+-+--+---------------+--|
       |                 '-WITH GRANT OPTION-' |  '-AS--'grantor'-'
       | .-,----------.                        |
       | V            |                        |
       '---+-'role'-+-+------------------------'
           +-'user'-+
           '-PUBLIC-'

For the syntax of the GRANT and REVOKE statements, see the IBM Informix Guide to SQL: Syntax.
Database-level privileges

privileges

Ownership rights

privileges

Table-level privileges

privileges

Column-level privileges

privileges

Type-level privileges

privileges

Routine-level privileges

privileges

Language-level privileges

privileges

Automate privileges

privileges

Determine current role at runtime

privileges

Example:

-- comment

#top REVOKE¶

>>-REVOKE------------------------------------------------------->

      (1)                                  (2)                      (3)
>--+-----+-+-| Database-Level Privileges |------FROM--| User List |-----+-------------+-+-><
   |     | '-+-DEFAULT ROLE-+--FROM--+-PUBLIC---------+-----------------'             | |
   |     |   '-ACCESS-------'        | .-,----------. |                               | |
   |     |                           | V            | |                               | |
   |     |                           '---'--user--'-+-'                               | |
   |     |                 (4)                                                        | |
   |     '-+-| Role Name |------FROM--+-PUBLIC-------------+--+-------------------+-+-' |
   |       |                          | .-,--------------. |  '-AS--'--revoker--'-' |   |
   |       |                          | V                | |                        |   |
   |       |                          '---+-'--user--'-+-+-'                        |   |
   |       |                              '-'--role--'-'                            |   |
   |       |                                     (5)                                |   |
   |       '-| Security Administration Options |------------------------------------'   |
   |                              (6)                                                   |
   '-+-| Table-Level Privileges |-------------------------+--| FROM Options |-----------'
     |  (1)                               (7)             |
     '-----+-| Routine-Level Privileges |---------------+-'
           |                                   (8)      |
           '---+-| Language-Level Privileges |------+---'
               |                           (9)      |
               +-| Type-Level Privileges |----------+
               |                               (10) |
               '-| Sequence-Level Privileges |------'

FROM options

|--FROM--------------------------------------------------------->

                     (3)  .-CASCADE--.
>----+-| User List |------+----------+-----+----+-------------------+--|
     |                    '-RESTRICT-'     |    '-AS--'--revoker--'-'
     |       .-,-------------------------. |
     |  (1)  V                   (4)     | |
     '-----------+-| Role Name |-----+---+-'
                 '-'--user--'--------'

Example:

-- comment

#top SHOW GRANTS¶

comment

Example:

-- comment

#top databases¶

#top DATABASE CREATE¶

>>-CREATE DATABASE--+---------------+--database--+-------------+-->
                    '-IF NOT EXISTS-'            '-IN--dbspace-'

                                    .-NLSCASE SENSITIVE-------.
>--+-----------------------------+--+-+---------------------+-+-><
   '-WITH--+-+----------+--LOG-+-'    '-NLSCASE INSENSITIVE-'
           | '-BUFFERED-'      |
           '-LOG MODE ANSI-----'

Example:

-- comment

#top DATABASE ALTER¶

comment

Example:

-- comment

#top DATABASE DROP¶

>>-DROP DATABASE--+-----------+--| Database Name |-------------><
                  '-IF EXISTS-'

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¶

           .-STANDARD-.
>>-CREATE--+-+-----+--+--TABLE--+---------------+--table-------->
             '-RAW-'            '-IF NOT EXISTS-'

>--+-| Table Definition |---------+--+------------------------+-->
   |  (1)                     (2) |  |  (1)                   |
   '-------| OF TYPE Clause |-----'  '-------| WITH Options |-'

>--+-------------------------+--+-------------------------------+-->
   |                     (3) |  |  (1)                      (4) |
   '-| SECURITY Clause |-----'  '-------| Storage Options |-----'

>--+-------------------+--+------------------------------+------>
   |               (5) |  |                          (6) |
   '-| LOCK Mode |-----'  '-| USING Access-Method  |-----'

>--+----------------------------------+------------------------><
   |  (1)                         (7) |
   '-------| Statistics Options |-----'

Table Definition

      .-,-------------------------.
      V                       (8) |
|--(----| Column Definition |-----+----------------------------->

>--+-----------------------------------------------+--)---------|
   |    .-,--------------------------------------. |
   |    V                                  (9)   | |
   '-,----+-| Multiple-Column Constraint |-----+-+-'
          |                       (8)          |
          '-| Column Definition |--------------'

WITH Options

|--+---------------------+--------------------------------------|
   '-WITH--+-AUDIT-----+-'
           +-CRCOLS----+
           +-REPLCHECK-+
           +-VERCOLS---+
           '-ERKEY-----'

Składnia:

                                                   .-,-------------------------.
                                                   V                       (1) |
>>-CREATE TEMP TABLE -+---------------+--table--(----| Column Definition |-----+-->
                      '-IF NOT EXISTS-'

>--+----------------------------------------------------------+--)-->
   |      .-,---------------------------------------------.   |
   |      V                                         (2)   |   |
   '-,------+-| Multiple-Column Constraint Format |-----+-+---'
            |                       (1)                 |
            '-| Column Definition |---------------------'

>--+-------------+--+-------------------------------+----------->
   '-WITH NO LOG-'  |  (3)                      (4) |
                    '-------| Storage Options |-----'

>--+---------------------------+--+------------------------------------+-><
   |                       (5) |  |                                (6) |
   '-| LOCK MODE Options |-----'  '-| USING Access-Method Clause |-----'

#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--+-table---+--+-| Basic Table Options |------+--><
                '-synonym-'  |                          (1) |
                             +-| Logging TYPE Options |-----+
                             |                        (2)   |
                             '-| Statistics Options |-------'

Basic Table Options

     .-,---------------------------------------------.
     V                         (3)                   |
|--+---+-| ADD Column Clause |---------------------+-+-+--------|
   |   |                      (4)                  |   |
   |   +-| ADD AUDIT Clause |----------------------+   |
   |   |                           (5)             |   |
   |   +-| ADD CONSTRAINT Clause |-----------------+   |
   |   |                   (6)                     |   |
   |   +-| MODIFY Clause |-------------------------+   |
   |   |                       (7)                 |   |
   |   +-| DROP AUDIT Clause |---------------------+   |
   |   |                            (8)            |   |
   |   +-| DROP CONSTRAINT Clause |----------------+   |
   |   |                        (9)                |   |
   |   +-| DROP Column Clause |--------------------+   |
   |   |  (10)                                (11) |   |
   |   +--------| MODIFY EXTENT SIZE Clause |------+   |
   |   |  (10)                              (12)   |   |
   |   +--------| MODIFY NEXT SIZE Clause |--------+   |
   |   |  (10)                       (13)          |   |
   |   +--------| LOCK MODE Clause |---------------+   |
   |   |                (14)                       |   |
   |   +-| PUT Clause |----------------------------+   |
   |   |                            (15)           |   |
   |   '-| SECURITY POLICY Clause |----------------'   |
   '-+-+-ADD--+--+-ROWIDS--+------+--------------------'
     | '-DROP-'  '-VERCOLS-'      |
     |                       (16) |
     +-| ER Shadow Columns |------+
     |                     (17)   |
     '-| ADD TYPE Clause |--------'

#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¶

>>-RENAME TABLE--+--------+--old_table--TO--new_table----------><
                 '-owner.-'

Example: rename table

-- comment

#top TABLE TRUNCATE¶

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

Dokumentacja IBM Informix:
http://www-01.ibm.com/support/knowledgecenter/api/content/nl/en-us/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_1236.htm

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

Składnia:

             .- TABLE-.
>>-TRUNCATE -+--------+--+-------------+--+- table --+---------->
                         '- 'owner' -.-'  '- synonym '

   .- DROP STORAGE -.
>--+----------------+------------------------------------------><
   '- REUSE STORAGE '

Example:

-- comment

#top TABLE DROP¶

>>-DROP TABLE--+-----------+--+-----------+--+-table---+-------->
               '-IF EXISTS-'  '- owner -.-'  '-synonym-'

   .-CASCADE------.
>--+-+----------+-+--------------------------------------------><
     '-RESTRICT-'

Example:

-- comment

#top sequences¶

#top SEQUENCE CREATE¶

>>-CREATE SEQUENCE--+---------------+--+----------+--sequence--->
                    '-IF NOT EXISTS-'  '-owner--.-'

>--+-------------------------------------+---------------------><
   | .-----------------------------.     |
   | V              .-BY-.         | (1) |
   '---+-INCREMENT--+----+--step-+-+-----'
       |        .-WITH-.         |
       +-START--+------+--origin-+
       | .-NOMAXVALUE----.       |
       +-+-MAXVALUE--max-+-------+
       | .-NOMINVALUE----.       |
       +-+-MINVALUE--min-+-------+
       | .-NOCYCLE-.             |
       +-+-CYCLE---+-------------+
       +-+-CACHE--size-+---------+
       | '-NOCACHE-----'         |
       | .-ORDER---.             |
       '-+-NOORDER-+-------------'

Example:

-- comment

#top SEQUENCE ALTER¶

>>-ALTER SEQUENCE--+--------+--sequence------------------------->
                   '-owner.-'

   .------------------------------------------.
   V                                          |
>----+--------------------------------------+-+----------------><
     |    (1)  .-NOCYCLE-.                  |
     +-+-------+-CYCLE---+----+-------------+
     | |  (1)                 |             |
     | +-------+-CACHE size-+-+             |
     | |       '-NOCACHE----' |             |
     | |  (1)  .-ORDER---.    |             |
     | '-------+-NOORDER-+----'             |
     |    (1)             .-BY-.            |
     '-+-------INCREMENT--+----+--step----+-'
       |  (1)           .-WITH-.          |
       +-------RESTART--+------+--restart-+
       |  (1)  .-NOMAXVALUE----.          |
       +-------+-MAXVALUE--max-+----------+
       |  (1)  .-NOMINVALUE----.          |
       '-------+-MINVALUE--min-+----------'

Example:

-- comment

#top SEQUENCE DROP¶

Read syntax diagramSkip visual syntax diagram
>>-DROP SEQUENCE--+-----------+--+------------+--sequence------><
                  '-IF EXISTS-'  '- owner - . '

Example:

-- comment

#top indexes¶

#top INDEX CREATE¶

                                  (1)
>>-CREATE--| Index-Type Options |------------------------------->

>--INDEX--+---------------+--index--ON--+-table---+------------->
          '-IF NOT EXISTS-'             '-synonym-'

                       (2)
>--| Index-Key Specs |------| Index Options |--+--------+------><
                                               '-ONLINE-'

Index Options

|--+------------------------------------+----------------------->
   |                                (3) |
   '-| USING Access-Method Clause |-----'

>--+---------------------------+--+-------------------------+--->
   |                       (4) |  |                     (5) |
   '-| FILLFACTOR Option |-----'  '-| Storage Options |-----'

>--+---------------------+--+--------------------+-------------->
   |                 (6) |  |                (7) |
   '-| Index Modes |-----'  '-HASH ON Clause-----'

>--+-----------------------------+------------------------------|
   |                         (8) |
   '-| Extent Size Options |-----'

Example: alter table add INDEX

-- comment

#top INDEX ALTER¶

>>-ALTER INDEX--index----TO--+-----+--CLUSTER------------------><
                             '-NOT-'

Example: alter table add INDEX

-- comment

#top INDEX DROP¶

>>-DROP INDEX--+-----------+--+------------+--index------------->
               '-IF EXISTS-'  '- owner - . '

>--+--------+--------------------------------------------------><
   '-ONLINE-'

Example: alter table drop INDEX

-- comment

#top functions¶

#top Functions and Operators¶

-- comment

#top DATA Manipulations¶

#top SELECT with LIMIT and OFFSET¶

comment

Example:

-- comment

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:

-- comment

Uzyskany rezultat powinien być analogiczny do poniższego:

comment

-- comment

Uzyskany rezultat powinien być analogiczny do poniższego:

comment

#top WHERE IN subselect with multiple columns¶

#top Deleting duplicates rows¶

#top REPLACE INTO¶

-- comment

#top GROUP with CONCAT¶

-- comment

Zmodyfikowany ostatnio: 2017/11/01 23:57:59 (8 lat temu), textsize: 58,3 kB, htmlsize: 101 kB

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

Dodaj komentarzKomentarze użytkowników