MySQL Tables Manipulations

Created for

Iva E. Popova, 2016,

Create table

The minimal syntax


CREATE TABLE table_name(
    column_name1 data_type,
    column_name2 data_type,
    ....
);

Column names should not be MySQL reserved words. If you really want to use them (which is a bad practice), you have to put them in backticks(``)

Create table - example


CREATE TABLE artist (
  artist_id SMALLINT(5) NOT NULL DEFAULT 0,
  fname VARCHAR(20) DEFAULT NULL,
  lname VARCHAR(20) NOT NULL,
  PRIMARY KEY  (artist_id)
);

Results in:


Query OK, 0 rows affected
Time: 0.280s

If table exists an error will be thrown!

Show CREATE TABLE


show CREATE TABLE artist\G
        
'\G' modifier is used instead of ';' to display wide results in vertical form

Results in:


***************************[ 1. row ]***************************
Table        | artist
Create Table | CREATE TABLE `artist` (
  `artist_id` smallint(5) NOT NULL DEFAULT '0',
  `fname` varchar(20) DEFAULT NULL,
  `lname` varchar(20) NOT NULL,
  PRIMARY KEY (`artist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This allows us to see the create statement that MySQL engine had used. It shows even the default options, which we did not state explicitly.

List All Tables


SHOW TABLES;

Results in:


+----------------------+
| Tables_in_music_db   |
|----------------------|
| artist               |
+----------------------+

Show Table Structure


DESC artist;

Results in:


+-----------+-------------+--------+-------+-----------+---------+
| Field     | Type        | Null   | Key   |   Default | Extra   |
|-----------+-------------+--------+-------+-----------+---------|
| artist_id | smallint(5) | NO     | PRI   |         0 |         |
| fname     | varchar(20) | YES    |       |    <null> |         |
| lname     | varchar(20) | NO     |       |    <null> |         |
+-----------+-------------+--------+-------+-----------+---------+

Delete a Table


DROP TABLE artist;

If table do not exists, an error is thrown:


ERROR 1051 (42S02): Unknown table 'music_db.artist'

Alter tables

Add column


ALTER TABLE table_name
  ADD new_column_name column_definition
  [ FIRST | AFTER column_name ]; # optional


ALTER TABLE artist
    ADD birth_date TINYINT
    AFTER lname;

Remove column


ALTER TABLE artist DROP birth_date;

Exercises

Create DB schema dumped in external file

Create a simple DB: music_db, using the SQL statements provided in the file music_db_schema.sql.: You can use the MySQL Workbench to execute the statements by: 'File' => 'Run SQL script...' and selecting the 'music_db.sql' file you have downloaded.

Create table as music_db_admin

Reconnect to the music_db as music_db_admin user.
Create new table producer with following scheme:

Executing SQL Statements stored in external file

While connecting with MySQL client


mysql -u user_name -p [db_name] < full_file_name

[db_name] is optional (if not set, you'll have to state 'use db_name' later)
text_file is an ASCII file, which contains SQL statements

In MySQL client


mysql> source full_file_name

Using MySQL Workbench

Go to 'File'=>'Run SQL script...' and browse to select the file, which contains the SQL statements you want to execute.
You can do the same by 'File'=>'Open SQL script...' and execute the statements in Workbench editor.