1. Recap
In the previous unit, we associated the Create and Read words of the CRUD acronym with the INSERT and sELECT SQL keyWords. Before inserting data into a database, there are a few things we need to make sure we have first:
1. A database 2. A database table definition
Creating databases
By applying the CREATE DATABASE name; SQL statement, we were able to open a space in the computer's memory for data to be stored in it. We learned that databases are sometimes called schemas and that the CREATE sCHEMA name ; alternate syntax may work as well.
Creating tables
Table definition is a little bit more complex in the way that we need to define the table datatypes and constraints. By defining the PRIMARY KEY and the NOT NULL SQL constraints we improve the functionality and reliability of our database tables. On the other hand when we AUTO_INCREMENT a number-type field, we automate the insertion of fields like the id column's.
Inserting data
Finally, the INsERT keyword allows us to create data in the database table by specifying the order and actual data that is meant to live inside the table rows.
Head to the next section to know the scope of this unit.
2. What we'll learn
We already know how to read and create data, let's UPDATE some data! In this unit, we will learn how to modify some of our database tables data as well as the table definition itself. By the end of this unit you will:
Understand how to UPDATE existing data sets
ALTER existing table column datatype definitions ALTER table names ADD columns to an existing table
Are you ready? Let's continue.
3. Resetting the unit's databases
If you make a mistake while editing any of the unit's databases or just want to reset the databases back to their original state, click the "Reset Section Database" button that is available in the relevant sections.
4. Analysing the schema
Reset section database
If you make a mistake while editing the EPDriver database in this set of sections or just want to reset the database back to its original state, return to this page and click the "Reset Section Database" button below. Reset Section Database
In order to illustrate the SQL UPDATE functionality, we created an Easy Private Driver App schema, EPDriver for short, that represents the storage of some of the data from an app that enables users to hire private drivers. Click on the mysq1> prompt in the left pane and verify that the EPDriver database exists by eXecuting the SHOW DATABASES; Statement. You should see this:
I Database
information_schema EPDriver more db's
Use the EPDriver schema and display its tables: mysql> USE EPDriver; mysql> SHOW TABLES; This is the result:
Tables_in_EPDriver
drivers trips users
rows in set (0.00 sec)
Let's evaluate the table columns' data types in the next section.
4. 1. Showing column data
First, let's evaluate the trips table with the SHOW cOLUMNS FROM trips; statement. The out