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