Tables

What are tables in relational databases? How can i crate tables? How can i delete tables? How can i change the structure of relational database tables?
Categories: 

CREATE TABLE - create a database table

Syntax

Description:

    CREATE TABLE creates a new empty table in the selectted database from the sructure description in the command.

Datatype

    Datatype is here a type of values known by the system which would be filled into the specifiyed database table column.

    Text data types:

      CHARACTER - textfields
      CHAR - same as CHARACTER

    Numeric datatypes (exact numeric):

      DECIMAL - a number with decimals
      DEC - same as DECIMAL
      NUMERIC - same as DECIMAL, but the number of decimals is ...
      INTEGER - number without decimals (plain numbers), is used without specifying a fieldsize
      because it is defined by the database system
      INT - same as INTEGER
      SMALLINT - number without decimals (plain numbers), is used without specifying a fieldsize
      because it is defined by the database system and uses probably less space than INTEGER, because is designed for small numbers. The format depends on DBMS.

    Numeric datatypes (approximate numeric):

      FLOAT - numbers in exponential form
      REAL - such as FLOAT, but is used without specifying the fieldsize, because it is defined by the database system.
      DOUBLE PRECISION - such as REAL, but probably with doppelten precision
      DOUBLE - same as DOUBLE PRECISION

    Other datatypes, not defined in the standart SQL :

      DATE - datevalue, im most european implementations in the following format: dd.mm.yyyy
      TIME - timevalue, im most european implementations in the following format: hh-mm-ss
      VARCHAR - string with variable length, depending on the impementation maximal 254 to 2048 characters.
      LONG VARCHAR - sting with variable length, depending on the impementation maximal 16 kilobytes.

    Example:

 CREATE TABLE torder (
 onum INTEGER,
 anz  INTEGER );

Creates an empty table torder with records, which have two integer fields
onum and anz.

Size

    Syntax: whole size [,decimals]

    Description:

    Decimals are allowed only with DECIMAL and NUMERIC declarations. The size declaration is allowed only with variable length datatypes. When the size declaration is not given for the variable length fields, then the default value 1 would be used by the system. Exception: for CHARAKTER a size must always be specifyed (no default values).

    Example:

CREATE TABLE tverkauf (
 vnum  INTEGER,
 vname CHAR(30),
 stadt CHAR(20),
 prov  DECIMAL);

Here is an empty table tverkauf with four records (vnum - number, vname - 30 characters, stadt - 20 characters and prov - number) created.

Fieldtype

    Syntax:

    Fieldtype [...]

    Fieldtype is here the type of the table column to be created.

    NOT NULL

      Is needed when, no NULL-Values in the columns are allowed.

    UNIQUE

      Allows only unique Values in the database table column.

    CHECK( logical construct )

      Is used if an error check is needed before new values are saved into the field.

    DEFAULT = Value

      Defines a default value for the column. Empty fields would have the specifyed value and would not be NULL.

    PRIMARY KEY

      Specifyes the primary key for the database table.

    Example:

CREATE TABLE tverkauf (
 vnum  INTEGER NOT NULL UNIQUE PRIMARY KEY,
 vname CHAR(30) NOT NULL UNIQUE,
 stadt CHAR(20) DEFAULT='Berlin',
 prov  DECIMAL CHECK(comm &lt 1) );

Creates a new empty table tverkauf with records with four fields :
vnum - unique, not null number as primary key;
vname - 30 character not null and unique textfield;
stadt - 20 character textfield with default value 'Berlin';
prov - number, which is always smaller than 1.

Categories: 

DROP TABLE - delete a table from the database

Syntax

Description

    With DROP TABLE you can delete a table from a database. The table must not be explicitly empty. It depends on the database system.

    Example

DROP TABLE tkunden;

Here is the customer table tkunden deleted.

Categories: 

ALTER TABLE - modify tables

Syntax

Description

    With ALTER TABLE you can add, remove or modify table columns. depending on the database management system, the table should not be empty.

    Example:

ALTER TABLE tkunden ADD COLUMN new_number INTEGER NOT NULL;

Adds the new INTEGER column new_number to existing table tkunden and sets a NOT NULL contraint.

Example:

ALTER TABLE TABLE tkunden DROP COLUMN new_number;

Drops the column new_number from the table tkunden.

Categories: 

GRANT - grant table privileges to users

Syntax

    GRANT { SELECT | INSERT | UPDATE | DELETE | ALL }
    ON tablename
    TO
    { User | {GROUP|ROLE} groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ];

Description

    With GRANT you can give following rights or privileges to database users or roles (groups of users):
    SELECT - revtrieve or query data
    INSERT - insert new records into a table
    UPDATE - modify datafields in a table
    DELETE - delete records from a table
    ALL - means all rights on a table

    When PUBLIC is used instead of a list of concrete users or roles, then the specifyed priviledges will be given to all users of the database system (depending on the used RDMBS the rights are given only to the users woth connect permission to the current database).

    WITH GRANT OPTION allows users to delegate their table priviledges to other users.

    Example:

GRANT SELECT ON tkunden TO my_user;

The user my_user becames the priviledge to query, to retrieve data from the table tkunden.

Categories: 

REVOKE - remove table priviledges from users

Syntax

    REVOKE { SELECT | INSERT | UPDATE | DELETE | ALL }
    ON tablename
    FROM public | {user|role [,..]} ;

Description

    REVOKE removes the followong priviledges from the specifyed users or roles (user groups):
    SELECT - query or retrieve data
    INSERT - insert new records into a table
    UPDATE - modify existing records in a table
    DELETE - delete records from a table
    ALL - means all priviledges
    When PUBLIC is used, the specifyed priviledges will be removed from all users in the database (depending on the concrete RDMBS, the rights will be removed only from the users or the current database).

    Example:

REVOKE UPDATE ON tkunden TO my_user;

Here the user my_user looses the right to modify existing records in the table tkunden.

Categories: