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', 'email@example.com', '123456', 'New York', 'design', 0, now()),
('Sara', 'Watson', 'firstname.lastname@example.org', '123456', 'New York', 'design', 0, now()),
('Will', 'Jackson', 'email@example.com', '123456', 'Rhode Island', 'development', 1, now()),
('Paula', 'Johnson', 'firstname.lastname@example.org', '123456', 'Massachusetts', 'sales', 0, now()),
('Tom', 'Spears', 'email@example.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 = 'firstname.lastname@example.org' 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!!
What is CRUD? | Codecademy
When we are building APIs, we want our models to provide four basic types of functionality. The model must be able to…
CRUD operations in SQL Server
December 10, 2018 by CRUD operations are foundation operations every database developer and administrator needs to…