Tables and DDL¶

In this section, you'll learn about the DDL subset (Data Definition Language) of SQL language. You learn how to create, delete or modify existing structures.

Create¶

The command is used to create the basic building block of databases - tables: CREATE TABLE table_name, where table_name is to be the actual name of the table being created. Basic table names may contain:

letters
digits
characters $, _
code characters: U+0080-U+FFFF
lowercase and uppercase letters, however, whether their interpretation will be: case sensitive, or incase sensitive depends on the server and database configuration.

However, table names cannot:

consist of only digits
consist of only SQL keywords (unless they are between the single quotation marks `)
end with a space character.

The following example shows the general command syntax CREATE TABLE:

CREATE TABLE table_name(
    column_name_1 column_type_1 [attributes],
    column_name_2 column_type_2 [attributes],
    column_name_3 column_type_3 [attributes]
    ...
);

Column names, like table names, can contain both uppercase and lowercase letters. Depending on the database server we use and its settings, these values can be case insensitive or case sensitive.

Column types specify data type, which can be stored in a given column.

An example of creating a table:

CREATE TABLE Product(
    id INTEGER,
    Name VARCHAR(20),
    Producer VARCHAR(25)
);

Note that attributes are optional when creating tables.

You can also create tables by indicating the specific database to which they should belong. The table name is preceded by the database name. Both names are separated by a character ., i.e.:

CREATE TABLE shop.Producer(
    id INTEGER,
    Name VARCHAR(20),
    Address VARCHAR(30)
);

Creating a non-existent table¶

If you try to create a table that is already in the database, the statement will result in an error (with content, e.g. Table 'Producer' already exists). To create a table in the absence of it, you can use command: IF NOT EXISTS, i.e.:

CREATE TABLE IF NOT EXISTS table_name(
    column_name_1 column_type_1 [attributes],
    column_name_2 column_type_2 [attributes]
    ...
);

Creating a temporary table¶

Using the CREATE TEMPORARY TABLE command, you can create a temporary table that will be removed from the system when the connection is closed. The table, moreover, is associated with a specific connection and therefore, two different users can create exactly the same temporary tables. In addition to adding the keyword TEMPORARY, command is identical to creating a regular table.

CREATE TEMPORARY TABLE table_name(
    column_name_1 column_type_1 [attributes],
    column_name_2 column_type_2 [attributes]
    ...
);

Creating one table based on another¶

Instruction:

CREATE TABLE Producer2 LIKE Producer

you can create a new table that reflects the structure of the base table. This table does not contain the original table data.

column attributes¶

Each of the columns declared in the table may have additional attributes. These include:

PRIMARY KEY
NOT NULL
AUTO_INCREMENT
DEFAULT
INDEX
UNIQUE.

PRIMARY KEY¶

Attribute PRIMARY KEY means that the column is primary key, with simultaneous indexing of these columns. If the column is to be a primary key, the name of this attribute should be placed after its basic definition:

CREATE TABLE table_name(
    column_name column_type PRIMARY KEY,
    column_name_2 column_type_2 [attributes],
    column_name_3 column_type_3 [attributes]
    ...
);

CREATE TABLE Person(
    Personal_id INTEGER PRIMARY KEY,  
    Name VARCHAR(30),
    Surname VARCHAR(40)
);

The primary key can also be defined after column definitions (also using the PRIMARY KEY syntax). This syntax is useful when you want to create a primary key based on many columns:

CREATE TABLE Book(
    ISBN VARCHAR(20),  
    Publisher VARCHAR(30),
    Title VARCHAR(40),
    PRIMARY KEY(ISBN, Title)
);

ATTENTION: Creating a primary key on many columns, remember that their combination of values must be unique.

NOT NULL¶

The NOT NULL attribute means that the column cannot contain empty values. In other words, these fields must contain some values. BLack of defining data or entering the value NULL will result in a query failure.

CREATE TABLE Product(
    Id INTEGER PRIMARY KEY,  
    Name VARCHAR(30) NOT NULL
);

NULL¶

Attribute NULL is the default attribute used when creating the table. It means that a given column can contain empty values.

ATTENTION: In most relational databases, the NULL attribute cannot be used on a column that is part of the primary key.

AUTO_INCREMENT¶

This attribute is responsible for automatically generating the next value. It can be used only for columns operating on integer values. The column value is automatically increased by 1.

CREATE TABLE Product(
    Id INTEGER PRIMARY KEY AUTO_INCREMENT,  
    Name VARCHAR(30) NOT NULL
);

When adding values to such a table, we do not need to provide the value of such a column or the value on may be NULL.

UNIQUE¶

The UNIQUE attribute forces that only unique values can be found in a given table. We create such a table, e.g.:

CREATE TABLE Product(
    Id INTEGER PRIMARY KEY AUTO_INCREMENT,  
    Name VARCHAR(30) UNIQUE
);

ATTENTION: The UNIQUE attribute can also be used, like PRIMARY KEY - after the column declarations. You can also ensure that the combination of multiple column values is unique.

DEFAULT¶

This attribute allows you to set a default value for a column field. If the user provides no value, the default value will be set. Specifying NULL for this column, not, will result in the default value being selected.

CREATE TABLE Product(
    Id INTEGER PRIMARY KEY AUTO_INCREMENT,  
    Name VARCHAR(30) DEFAULT 'unknown'
);

Indexes¶

Indexes are objects that can be added to existing tables. Their goal is to increase the efficiency of operations such as searching and sorting. Each additional index on the table reduces the efficiency of adding or editing existing records. When creating an index, a special optimization structure is created inside the database.

The index is created using instructions:

CREATE [UNIQUE] INDEX index_name ON table_name;

It can be created for one or many columns. Multiple indexes can be created in one table. For table:

CREATE TABLE Product(
    Id INTEGER PRIMARY KEY,  
    Name VARCHAR(30),
    Type VARCHAR(15)
);

the index declaration may look like this:

CREATE INDEX name_idx ON Product (Name);
CREATE INDEX type_idx ON Product (Type);

or:

CREATE INDEX index_product ON Product (Name, Type);

Indexes can also be declared in the table definition itself, e.g.:

CREATE TABLE table_name(
    column_name column_type PRIMARY KEY,
    column_name_2 column_type_2 [attributes],
    column_name_3 column_type_3 [attributes],
    ...
    INDEX index_name_1 (columns),
    INDEX index_name_2 (columns)
);

You can use the SHOW instruction to display the indexes:

SHOW INDEX FROM Product;

Index removal can be accomplished using the DROP statement:

DROP INDEX index_name ON table_name;

Data modifications¶

Show¶

Information on the structure of an existing table can be obtained using the SHOW COLUMNS command:

SHOW COLUMNS FROM table [FROM database] [LIKE `table_pattern`];

SHOW COLUMNS FROM Product.sda;
SHOW COLUMNS FROM Product.sda LIKE 'name%';

Instead of using SHOW COLUMNS, we can use theDESCRIBE command, which, however, has fewer options for selecting details.

Alter¶

If you need to change the structure of an existing table, use the ALTER TABLE command:

ALTER TABLE table_name change1[, change2[, ...]];

With this command there is a possibility:

adding columns
removing columns
modify type, names
adding indexes
removing indexes.

Adding a column¶

To add a column use the command:

ALTER TABLE table_name ADD [COLUMN] column_definition;
ALTER TABLE table_name ADD [COLUMN] column_definition [FIRST | AFTER column_name];

Keywords FIRST andAFTER allow you to place a column in a specific table structure, e.g.:

ALTER TABLE Product ADD product_desc VARCHAR(255) AFTER product_price;

Delete column¶

To remove columns use the DROP command:

ALTER TABLE Product DROP COLUMN product_desc;

The command above removes the column named product_desc.

Adding and removing the primary key¶

By using the ALTER command and theADD keyword, you can add a primary key:

ALTER TABLE Person ADD PRIMARY_KEY (Pesel);

You can delete an existing key using the DROP keyword:

ALTER TABLE Person DROP PRIMARY_KEY (Pesel);

Column modification¶

Modification of the column can also be accomplished using the ALTER command together with the keyword MODIFY COLUMN:

ALTER TABLE Product MODIFY COLUMN Id INTEGER AUTO_INCREMENT;

The above operation modifies the Id column so that it increases automatically for subsequent records.

ALTER TABLE Product MODIFY COLUMN product_desc VARCHAR(200)

Thanks to the instructions above, the product_desc column will be able to contain200 characters.

Renaming the table¶

The table name can be changed by using the keyword RENAME:

ALTER TABLE Product RENAME Product;

Modifying the default column value¶

Adding the default value for a given column can be done using the SET DEFAULT operation:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'default_value';

The following example sets the default value for the product_desc column:

ALTER TABLE Product ALTER COLUMN product_desc SET DEFAULT 'none';

Deleting the default value can be done using the ALTER COLUMN command connected toDROP DEFAULT:

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

The following example will remove the default value for the product_desc column:

ALTER TABLE Product ALTER COLUMN product_desc DROP DEFAULT;

Adding and removing table index¶

Using the ADD INDEX command we can create an index in the table:

ALTER TABLE table_name ADD INDEX index_name (column_name_1, column_name_2);

ALTER TABLE Product ADD INDEX name_id (name, id);

The DROP INDEX command allows you to delete an index from the table:

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE Product DROP INDEX name_id;

Drop¶

The table is deleted using the DROP TABLE command as below:

DROP TABLE [IF EXISTS] table1, table2, ..., tableN;

You can delete one table or the entire set of tables, provided that the table actually exists.

DROP TABLE Product;

Truncate¶

With the Truncate command it is possible to delete data inside a table without removing the structure itself, e.g.:

TRUNCATE TABLE Product;

The above command deletes all data entered under the Product table.

ATTENTION: If you want to delete all records from the table, TRUNCATE will usually be faster than the commandDELETE FROM table_name.