MySQL Tutorial

Summary

In this chapter, we learned how to create and delete databases, tables, and indexes and how to change the structure of an existing table.

Case Sensitivity and Identifiers

Database names have the same case sensitivity as directories in your operating system. Table names follow the same rules as filenames. Everything else is case insensitive.
All identifiers except aliases can be up to 64 characters long. Aliases can be up to 255 characters long.
Identifiers can contain most characters, but database names may not contain /, \, or . and table names cannot contain . or /.
You can use reserved words for identifiers as long as you put them in quotes.

Creating a Database

create database dbname; creates a database.
use database dbname; selects a database for use.

Creating Tables

Use the create table statement, which has this general form:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

or

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_table_name;

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] (index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [INDEX] [index_name] (index_col_name,...)
  or    FULLTEXT [INDEX] [index_name] (index_col_name,...)
  or    [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  or    CHECK (expr)

Column Types

Exact numeric types are TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT, NUMERIC, and DECIMAL.
Approximate numeric types are FLOAT and DOUBLE.
String types are CHAR, VARCHAR, TEXT, and BLOB.
Date and time types are DATE, TIME, DATETIME, TIMESTAMP, and YEAR.
There are also various aliases to these type names.

Dropping Databases, Tables, and Indexes

Drop a database with
```
drop database dbname;
```
Drop a table with
```
drop table tablename;
```
Drop an index with
```
drop index indexname on tablename;
```

Altering Existing Table Structures

Change table structure with ALTER TABLE. This is the general structure of the ALTER TABLE command:

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_spec:
        ADD [COLUMN] create_definition [FIRST | AFTER col_name ]
  or    ADD [COLUMN] (create_definition, create_definition,...)
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ADD FULLTEXT [index_name] (index_col_name,...)
  or    ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [referenc_e_definition]
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
               [FIRST | AFTER col_name]
  or    MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    DISABLE KEYS
  or    ENABLE KEYS
  or    RENAME [TO] new_tbl_name
  or    ORDER BY col_name
  or    table_options

[ Directory ]