SQL for Web Developers (Part II)

Raq Robinson
Jun 26, 2020 · 5 min read

Before we move on to creating our first database, let‘s examine a common and important principle that all web developers should be familiar with…

CRUD stands for Create, Read, Update, and Delete which are the four basic functions of persistent storage. In SQL, the CRUD Operations are used in the following way:

It is important as a developer to understand the CRUD paradigm because it’s used frequently in building web applications. CRUD also provides a memorable framework for constructing full usable models. Furthermore, it corresponds to the HTTP methods, POST, GET, PUT, and DELETE that RESTful API’s are built upon.

With CRUD in mind let’s pick back up where we left off in Part I!

Open up your terminal and log in as the admin user previously created

mysql -u user_name -p

To CREATE a new database called ‘acme’, SHOW that it’s was successfully created and USE it, run the following commands:

To create a new Users table in the above ‘acme’ database run…

Confirm the table’s creation as shown below:

Now that you have some familiarity with typing SQL commands in the CLI, let’s get to querying using the MySQL Workbench GUI tool! If you hadn’t had a chance to install it, go ahead and do that now and open up the application.

Open up MySQL Workbench and click the ‘Local instance 3306' button on left to log in as root as shown above. This will open up the following query window. Right-click the acme database and select ‘Set as Default Schema’

To commands you write in SQL to access information from a database or make a change to it is called a ‘statement’.

Statements usually follow the structure above. SELECTindicates to the database ‘what’ results we want, FROM says ‘where’ to find the information and WHEREdictates the conditions we want applied to the results aka the ‘how’. To execute a statement type it in the editor and hit the lightning bolt or ‘cmd + enter’ to run. For example, to add a new record to the Users table use the INSERT INTO statement as shown below.

The green checkmark on line 4 at the bottom indicates that the commands were successfully executed. (These are the same commands you would run the the CLI).

Inserting multiple records at once, takes the same format as above but commas are used to separate each record to be added.

INSERT INTO users (first_name, last_name, email, password, location, dept,  is_admin, register_date) values 
('Fred', 'Smith', 'fred@gmail.com', '123456', 'New York', 'design', 0, now()),
('Sara', 'Watson', 'sara@gmail.com', '123456', 'New York', 'design', 0, now()),
('Will', 'Jackson', 'will@yahoo.com', '123456', 'Rhode Island', 'development', 1, now()),
('Paula', 'Johnson', 'paula@yahoo.com', '123456', 'Massachusetts', 'sales', 0, now()),
('Tom', 'Spears', 'tom@yahoo.com', '123456', 'Massachusetts', 'sales', 0, now());

To access records in a table using the SELECT method

The pictured command returns ALL(*) columns and records in the acme users table. Other ways to use select are:

// returns only first and last name columns of user records
SELECT first_name, last_name FROM users;
// return all users from massachusetts only
SELECT * FROM users WHERE location='Massachusetts'
//return all users from massachusetts who work in sales only
SELECT * FROM users WHERE location='Massachusetts' AND dept='sales';

Now that we have our create and read actions covered let’s look at the update and delete ones. When using the DELETE and UPDATE methods in SQL be sure to use WHERE otherwise all your records will be deleted.

// To update a user's email
UPDATE users SET email = 'freddy@gmail.com' WHERE id = 2;
// To delete a user by id
DELETE FROM users WHERE id = 6;
// To delete a user by first_name
DELETE FROM users WHERE first_name = "john";
// To delete all users in the sales department
DELETE FROM users WHERE dept = "sales";

If we need to update the Users table itself by either adding a new column or modify an existing one, we use the ALTER command as follows:

// Adds age column to Users table
ALTER TABLE users ADD age VARCHAR(3);
// Edit age column datatype from a string to a numeric
ALTER TABLE users MODIFY COLUMN age INT(3);

To Delete this table in the future:

DROP TABLE tablename;

For now :). In Part III I will look at how to further manipulate the database using more advanced methods. We will also look at adding multiple tables to the database and connecting them using foreign keys!!

RESOURCES

The Startup

Get smarter at building your thing. Join The Startup’s +725K followers.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store