Modifying Tables

In the previous lesson we discussed how to create and destroy tables. Now we will discuss how to modify the table structure after a table is created and how to add, modify, and delete data.

ALTER TABLE

To modify the structure of a table we use the ALTER TABLE statement. While the statement itself is part of the standard SQL, the specific clauses on how to alter a table can be vendor specific. The primary clauses that can be used with this statement are:

1. ADD COLUMN column_name column_type [constraints]



ALTER TABLE Department
  ADD COLUMN DepartmentHead BOOLEAN NULL;

2. MODIFY COLUMN column_name column_type [constraints]



ALTER TABLE Department
  MODIFY COLUMN DepartmentHead INT;

You can also use MODIFY COLUMN to change column ordering like so:



ALTER TABLE Department
MODIFY COLUMN DepartmentHead INT AFTER Name;
-- Replace AFTER Name; with FIRST; to move the new DepartmentHead column to be
the first column in table! 

3. ADD CONSTRAINT constraint_name



ALTER TABLE Department
  ADD CONSTRAINT FK_Department_Employee FOREIGN KEY (DepartmentHead)
  REFERENCES Employee (EmployeeNumber)
  ON DELETE NO ACTION ON UPDATE CASCADE;

4. DROP CONSTRAINT constraint_name



ALTER TABLE Department
  DROP FOREIGN KEY FK_Department_Employee;

You may also drop a PRIMARY KEY (which must be done before we add a new PRIMARY KEY):



ALTER TABLE Department
DROP PRIMARY KEY;

5. DROP COLUMN column_name



ALTER TABLE Department
  DROP COLUMN DepartmentHead;

There are many more clauses available. Find a complete list in the MySQL Documentation.

Data Manipulation Language - DML

Now that we know how to define the structure of the tables in a database, it is time to work with the actual data that gets stored in those tables. We will discuss below the INSERT, UPDATE, and DELETE statements to insert, modify, and delete rows of data.

Inserting new data rows - INSERT

For the following examples we will be using the following Midn table to practice inserting data. The table was created usign the following CREATE TABLE statement:

DROP TABLE IF EXISTS Midn;
CREATE TABLE Midn (
   Alpha CHAR(6) NOT NULL,
   LastName VARCHAR(45) NULL,
   FirstName VARCHAR(45) NULL,
   Company INT NULL,
   In_brigade BOOLEAN NOT NULL DEFAULT 1,
    PRIMARY KEY (Alpha));

To populate information into our tables we use the INSERT statement. We have the option to specifically call out what columns we want to insert into (and all the others will have the default values or NULL), or if this list of columns is not provided, we must provide data for all columns in the order they exist in the table (you can use SHOW FIELDS to see the columns in a table and their order). We can insert multiple rows into a table at once by separating entries with a comma.

INSERT INTO table_name [ (column_list) ] VALUES (list_of_data_values);

-- We can skip the column list when we are inserting data into every column!
INSERT INTO Midn
  VALUES ('115106', 'Paul', 'Taylor', 1, 1),
  ('115112', 'Paulson', 'Jacob', 29, 1),
  ('115114', 'Paxton', 'Anthony', 17, 1);

-- But we must use the column list if we will skip a column (like our In_brigade column below)!
INSERT INTO Midn (Alpha, LastName, FirstName, Company)
  VALUES ('115106', 'Paul', 'Taylor', 1),
  ('115112', 'Paulson', 'Jacob', 29),
  ('115114', 'Paxton', 'Anthony', 17);

NULLs or default values will be inserted into any fields that are not explicitly named. If there are any unsatisfied constraints (such as FOREIGN KEY constraints) the insert will be rejected by the database and the rows will not be inserted.

Advanced INSERT — updating existing entries.

Continuing with our Midn table from above, let's try and run some updates on our midshipmen after the first semester of academic boards wraps-up and love chits have been processed!.

Now try and run the following lines in your database:

 -- This will fail, but think about why!
INSERT INTO Midn  (Alpha, LastName, FirstName, Company, In_brigade)
  VALUES ('115106', 'Paul', 'Taylor', 1, 0),
  ('115112', 'Paulson', 'Jacob', 29, 1),
  ('115114', 'Paxton', 'Anthony', 7, 1);

We cannot INSERT a row with a Primary Key that already exists in our table! We have a few options, ranked in reverse order of usefulness:

1. DELETE the row and then re-add it with INSERT.
2. UPDATE the row, which we will cover next.
3. Explicitly tell MySQL what to do when it encounters a duplicate key! This is most useful in our Midn table if we want to add the newest class of plebes and also update the youngsters through first class who have either been kicked out or swapped companies in one fell swoop.

Try this code:

 -- This works and is quite efficient for our Midn table update:
INSERT INTO Midn (Alpha, LastName, FirstName, Company, In_brigade)
  VALUES ('115106', 'Paul', 'Taylor', 1, 0),
  ('115112', 'Paulson', 'Jacob', 29, 1),
  ('115114', 'Paxton', 'Anthony', 7, 1),
  ('125114', 'Petersen', 'Michael', 11, 1)
ON DUPLICATE KEY UPDATE Company=VALUES(Company), In_brigade=VALUES(In_brigade);

With the ON DUPLICATE KEY UPDATE syntax you can choose which columns you would like to update and if you want to use your newly inserted value (as we did above) or specifically hard-code another value you would like to set the column to.

Advanced INSERT — using queries in your INSERT

We did not discuss selecting rows from a table yet, but there is a SELECT statement for that and we will discuss that more later. For now, just assume that the statement exists and does what you expect (selects rows from one or more tables, based on some conditions). INSERTS can also be performed with the results of a SELECT statement.

INSERT INTO Midn (Alpha, FirstName, LastName)
  SELECT Alpha, First, Last
  FROM OtherTable
  WHERE OtherTable.Alpha > 169999;

Advanced INSERT — scripting your INSERT statements.

Often times it is useful to be able to import data from some existing source into your database. The LOAD DATA statement is the best option for this, but the INSERT statement can be useful too. But how do we generate our INSERT statements, especially if there are thousands of entries to import? Almost every DBMS (or other application) allows you to export tabular data into a CSV file. From here we can use a scripting language to generate our INSERT statements. Let's take a look at how we can automatically generate the SQL statements to add place holder Alphas for the class of 2037 (we're going way into the future!) to our Midn table from Python. Here is an example Python script (python_insert.py) to generate the Alphas and print the insert statements to standard out. Here is a snippet of the most interesting part:

# Add Alphas one insert statement
# at a time, this is slower but helps with knowing
# where to restart your SQL script if you have an error on
# a value in the middle (vice adding all rows in a single INSERT statement)

for Alpha in AlphaList:

    insert_statement = "INSERT INTO Midn ( Alpha )" +\
    " VALUES (\'" + str( Alpha ) + "\');"

    print (insert_statement)

And here are a few lines of the output generated when running the script above:

INSERT INTO Midn ( Alpha ) VALUES ( '370000' );
INSERT INTO Midn ( Alpha ) VALUES ( '370006' );
INSERT INTO Midn ( Alpha ) VALUES ( '370012' );
INSERT INTO Midn ( Alpha ) VALUES ( '370018' );
INSERT INTO Midn ( Alpha ) VALUES ( '370024' );
INSERT INTO Midn ( Alpha ) VALUES ( '370030' );
...  

Go ahead and run the script for the class of 2037. Keep this script in your back pocket for future use and labs when generating SQL code from Python!

Bulk loading - LOAD DATA

Using the LOAD DATA statement is the customary way to import the data from a file to a table.

LOAD DATA LOCAL INFILE 'myFile.txt' INTO TABLE my_table;

Complete documentation for the LOAD DATA statement in MySQL is available at LOAD DATA

Updating Rows - UPDATE

The UPDATE statement is used to update data that already exists in our tables. All the rows that satisfy the condition (if one is specified) are updated. This example changes at most one row, since the condition tests for the value of the primary key:

UPDATE Midn
   SET LastName = 'Targarian', FirstName = 'Tyrian'
 WHERE Alpha = 261234;

While the following example shows a bulk update across multiple rows

UPDATE Midn
   SET Company = 31
 WHERE Company = 30;

Note: Once a row is updated or deleted, the operation cannot be un-done, unless a backup is available!!!! Be careful when specifying what rows are affected by update or delete!

Deleting Rows - DELETE

The DELETE statement is used to remove (specific) rows from a table.

DELETE FROM Grade
  WHERE Course = 'NL110';

You must pay attention, if you omit the WHERE clause every row in the table will be removed!

Keep in mind any constraints that were added to the table, what would happen if we delete a specific Midshipmen would depend on what we set when creating the FOREIGN KEY. As a reminder:

• ON DELETE NO ACTION - Midshipmen can not be deleted if they have any grades!
• ON DELETE CASCADE - All grades for this Midshipmen will be deleted if the Midshipmen record is deleted.

Practice Problems

Working with tables

1. Create a simple table called test with columns name VARCHAR(50) and email VARCHAR(100). Pick and assign a PRIMARY KEY.
2. Add a new phone column to the test table.
3. Make phone column the new primary key
4. Populate the test table with atleast three entries
5. Delete all your hard work (three entries but not your test table) with just three words! This should scare you... don't be this guy!
6. Delete the entire test table
7. This just in: the names and company breakdown of the class of 2037 are known! Warning: there are a few more than 1200 this year! Use the python3 script above as a starting point to generate the necessary INSERT INTO statements in python3 from new_plebes.csv. HINT:use the ON DUPLICATE KEY syntax to be able to update already existing placeholder Alphas and insert new Alphas in one fell swoop!