SQL Basics Tutorial

The acronym SQL stands for Structured Query Language. The author of these tutorials has worked with AS 400 DB2 SQL, Oracle 8i SQL, MS SQL, MySQL/MariaDB and way back in the day Microsoft Access SQL. In essence it is the language to do one of the only four things done with a database. Add data to the database which is what we call INSERT. Get information from a database which is probably the main function of a database which we call SELECT. Update information in a database which we call UPDATE and lastly remove data from a database which we call DELETE. There is commonality among all databases in their specific SQL which will make learning any database SQL easier once you have become adept at any one database SQL query language. These tutorials are made for the beginner in learning SQL for the intermediate to advanced to provide a medium to become familiar with the WPDB Power Tool.

These tutorials are intertwined with tutorials on Stored Procedures. There are many advantages to using stored procedures and few disadvantages. The greatest of these advantages is that they localize your SQL statements in one location and often (as has been the case for the author) application data issues can be easily isolated and resolved in the stored procedure without ever tampering with the code. Although this is not always the case whenever the author was working in an environment that used stored procedures as opposed to raw SQL in the midst of application code be it PHP, VB.Net, Perl, Cscript/VBScript data issues were resolved in a more timely manner.

It is our hope these tutorials expand your horizon and add to your Tech Tool Box. Take what you find useful discard what you do not.

SQL Basics Tutorial – Let’s start with a simple table so we have something to work with. Tables are the core of a database. Different tables are used to store different data. For this part of the tutorial we are just going to create one table with a few data types as an example. Future the WPDB Power Tool tutorials will have more in depth information about tables, creating them and considerations prior to creating them.

The SQL to be used in creating tables with the WPDB Power tool is very strict and unforgiving. If you wander outside the syntax structure defined here you could have problems creating a table. Following is the SQL to build a robust table for use in the tutorials built here for your learning enjoyment that you might even find functional for your own applications.

CREATE TABLE `clients` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(64) NOT NULL,
  `last_name` VARCHAR(64) NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(20) NULL,
  `main_website` VARCHAR(255) NOT NULL,
  `notes` TEXT NULL,
  `created_date` DATETIME NOT NULL,
  `updated_date` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_UNIQUE` (`email`),
  UNIQUE KEY `main_website_UNIQUE` (`main_website`)
);

This table has a PRIMARY KEY (always good to have one by default for latter data management and relationships to other tables should they be needed). A primary key is indexed. In addition to the PRIMARY KEY it has two UNIQUE KEY fields. The UNIQUE KEY field will prevent any two rows from having the exact same data and will not allow another row to be INSERTED. A bit about the color coding in :
SQL KEY WORDS AND FUNCTIONS,
SQL TABLES and FIELDS,
USER DEFINED PARAMETERS AND VARIABLES,
COMMENTS and COMMENT LINES (PRECEDED BY # or — and a space)

Field Name: id Field Type: INT integer from -2,147,483,648 to 2,147,483,647 NOT NULL is REQUIRED AUTO_INCREMENT table automatically increments next value
Field Name: first_name Field Type: VARCHAR(64) up to 64 variable characters NOT NULL is REQUIRED
Field Name: last_name NULL can be empty
Field Name: email Field Type: VARCHAR(255) up to 255 variable characters maximum length for an email NOT NULL is Required.
Field Name: main_website Field Type: VARCHAR(255) maximum length of a domain name Required
Field Name: notes Field Type: TEXT NULL not required
Field Name: created_date Type: DATETIME must be a DATETIME value formatted YYYY-MM-DD HH:MM:SS Required
Field Name: updated_date Type: DATETIME same as created_date
Primary Key assigns the id as a primary key that can be related to additional tables.

Notice the way the table name, field names and primary key is surrounded by what we call a tick in SQL talk (also known as Character 96 of the ASCII character chart chr(96) in PHP speak and or String.fromCharCode(96) in Javascript speak). The syntax in create table SQL is very specific and must lay out precise. The way we are running this SQL the database will set whatever default engine is used on the database as well as the character set. You can view this with the following SQL (after running the above SQL which has been tested in the WPDB Power Tool):

SHOW CREATE TABLE my_demo_table;

You should notice first of all that your table has been renamed with the WordPress table name prefix. This was established when your WordPress site was created. For these tutorials and references “my_” is what will be used. The next thing to notice is what is on the last line of the SQL a line displayed similar to:
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 This line indicates the DEFAULT CHARSET on this server is latin1. The WPDB Power Tool will set the engine to InnoDB if possible for performance.

Now just for grins lets get an error. Run the CREATE TABLE SQL again and notice the error. With tables you will need to DROP the table before you can create it again. You can do this with the Query Tool or with the WPDB Tables tool.

There are a number of data types in MariaDB/MySQL Tables. This tool accommodates the following at present with no issues:
Text Date Types:
CHAR, VARCHAR
TEXT, MEDIUMTEXT, TINYTEXT, LONGTEXT
JSON

Date/Time Data Types:
DATETIME
DATE
TIME
YEAR

Number Data Types:
INT, TINYINT, SMALLINT, MEDIUMINT, INTEGER
BOOLEAN
DECIMAL
NUMBER
FLOAT
DOUBLE

A great reference for all data types is at mariadb.com data type. We use MariaDB for our database engine and are strong supporters of Open Source software. Although all knowledge is to be earned it needs to be available to all willing to earn it.

The SQL Basics Tutorial of the SELECT statement. With the WPDB Power tool we can explore data in our WordPress website like never before! However we can also do selects on various MySQL/MariaDB functions for testing purposes as well as to see what they do. You can see a complete list at mariadb.com clicking here. Lets go over some that may be useful down the road. To get started lets get into the WPDB Power Tool and click on the Query Tool Tab. From there enter the following queries:

SELECT NOW();        # This will give you the system time of the database
SELECT YEAR(NOW());  # This will give you the current year
SELECT MONTH(NOW()); # Returns the number of the current month
SELECT DATE_FORMAT(NOW(), '%W the %D of %M %Y');               # Here is a handy one
SELECT DATE_FORMAT(NOW(), "%d/%m/%Y %h:%i %p");      # Another handy one
SELECT TRIM('        REMOVE SPACE       ');
SELECT LENGTH(TRIM('       SPACE        ');
SELECT 5*5;
SELECT 5/5;
SELECT POW(5,5) AS '5 to the POWER of 5';
SELECT MOD(5, 5) AS '5 MODULUS 5';

That gives you an idea of how to do some testing before you might find these functions useful. Or if you want to calculate something without breaking out the calculator. Now lets have some fun exploring the WordPress tables. Once you understand this you can explore plugin tables as well as develop your own. WordPress has 12 tables at present they are (preceded by your WordPress prefix):
commentmeta, comments, links, options, postmeta, posts, terms, termmeta, term_relationships, term_taxonomy, usermeta, users

The most comonly used one is more than likely the post table. So lets explore that one with the following:

SELECT * FROM my_posts;

Across the top of the table you will see the columns of the table. You can drill this down even further and be specific with what you want to pull from the table by specifying the fields in your select with:

SELECT post_content, post_title, post_status FROM my_posts;

Now we might want to dial things in better with a WHERE clause but first we need to know our WHERE choices. We can do and get the listed in order ascending with:

SELECT DISTINCT post_status FROM my_posts ORDER BY post_status;

Notice we have a post_status called ‘publish’. These are the pages that are displayed on the website. Lets see what they look like in the Query Tool with:

SELECT post_content, post_title FROM my_posts WHERE post_status = 'publish';

There may be a number of empty posts in there (they only show up on the site when they are added to the menu or linked in a page) so we can enhance our WHERE with:

SELECT post_content, post_title FROM my_posts WHERE post_status = 'publish' AND post_content != '';

That sums up our tutorial for the SELECT statement. Now if you are ready move on over to the SELECT Stored Procedure Tutorial to see how to build a SELECT stored procedure.

The SQL Basics Tutorial of the INSERT statement. INSERT is what is done every time a page is added to a WordPress site. INSERT does not have a criteria going into the database so care must be taken to ensure what is getting inserted is clean. To demonstrate the INSERT function we need a demo table to work with so if you have not goe through the TABLES tutorial reference please do so now.

The table and its structure for reference can be viewed in the Query Editor by entering the query DESC my_clients (replacing my_ with whatever your WordPress prefix is) or you can view it by clicking on the table name in the WPDB Tables tool tab. Results should look like:

Once you have your table lets enter the following SQL into the Query Tool:

INSERT INTO my_clients(first_name, last_name, email, phone, main_website, notes, created_date, updated_date) 
VALUES('Donald', '', 'donald@ducks.com', '', 'duckhaven.com', '', '2021-03-09 07:00:00', '0000-00-00 00:00:00');

The date must be formatted as specified or you will get an error. Down the road we will show you how to address date formatting.

A way to insert multiple rows example in case you might import a CSV file or JSON, XML:

INSERT INTO my_clients(first_name, last_name, email, phone, main_website, notes, created_date, updated_date) 
VALUES 
('Donald', '', 'donald@ducks.com', '', 'duckhaven.com', 'late duck', '2021-03-09 22:00:00', '0000-00-00 01:00:00'),
('Daffy', '', 'daffy@ducks.com', '', 'duckhaven.com', 'goofy duck', '2021-03-09 12:00:00', '2021-03-09 01:00:00'),
('Huey', '', 'huey@ducks.com', '', 'duckhaven.com', 'early duck', '2021-03-09 07:00:00', '2021-03-09 09:00:00');

Go ahead and run both of these SQL queries in the Query Tool Editor. Once done enter the query into the editor:
SELECT * FROM my_clients; and you should see four rows in the my_clients table (or wp_clients if wp_ is your default prefix)

INSERT INTO my_clients(first_name, last_name, email, phone, main_website, notes, created_date, updated_date) 
VALUES('Huey', '' 'huey@ducks.com', '', 'duckhaven.com', 'early duck', '2021-03-09 07:00:00', '2021-03-09 09:00:00');

You should get an error. Take a moment to evaluate the error and see if you can find it. MySQL does its best to help you identify it but at best it is just a clue. It says the error is WordPress database error: [Column count doesn’t match value count at row 1]

Hopefully you can find it. If not look for a missing comma. A hacker mind will deliberately do things wrong to see what kind of errors they will get. This is very useful when you get the actual errors as by invoking them you get an intuitive feel for the real ones. You can have some fun with this by seeing how many different error messages you can generate. Do not be afraid of breaking anything. It hinders learning this stuff.

Here is another one to try:

INSERT INTO my_clients(last_name, email, notes)
VALUES('Duck', 'rosey@looneytunes.com', 'June');

You should get an error because there is no value for first_name and it is NOT NULL as well as created_date and update_date. MySQL/MariaDB will insert and empty character in a text field type of NOT NULL, a 0 in a number field NOT NULL and as you can see 0000-00-00 00:00:00 for DATETIME and the corresponding values for DATE, TIME or YEAR values set to NOT NULL. With a Stored Procedure you can ensure a clean install. Click here for INSERT Stored Procedure Tutorial.

The SQL Basics Tutorial of the DELETE Statement like the UPDATE and SELECT can and should use criteria with the WHERE clause. The reason is that without one or without using one correctly you can inadvertently delete data you did not intend on deleting (this does happen poor bugger that finds himself in this situation without a backup, sometimes there are tears). It can be as damaging as using a TRUNCATE statement then finding out someone needed information in the table that was TRUNCATED (TRUNCATE will not delete rows it will empty the table and start it anew with PRIMARY KEY resets as well).

So that being said lets BACKUP our clients table (my_clients is what we are calling it here however yours will be using whatever WordPress Table Prefix you are setup with) on our WPDB Tables tab before we begin.

Now with our backup lets take a look at our data with SELECT * FROM my_clients; If you have ran all the INSERT statements in the INSERT part of the SQL Basics Tutorial of the INSERT Statement tutorial you should have 6 rows in addition to any additional rows you have added in the INSERT Stored Procedure Tutorial providing the minimum data needed for this tutorial.

The SQL Basics Tutorial of the UPDATE statement can use criteria to update tables in the same manner the select and delete can use criterial. In fact when you do an UPDATE if you fail to have any criteria you are liable to update more than you cared to. So it is important to have a WHERE clause in any UPDATE SQL even if it a LIKE or wild card just to create a good force of habit when building these types of SQL statements. So lets get started!

Assumptions:

1. You have been through the TABLES tutorial and have an active clients database in your WordPress site.
2. You have been through the INSERT tutorial and have some data in there that we can work with to update.

Tutorial Objective:

• Understand how the UPDATE is used and what it does
• Understand how to safely use it with WPDB Power Tool
• Utilize the WPDB Tables for recovering a mistaken update

So let us begin! To start lets click on the WPDB Tables tab and click on the BACKUP option next to your clients table (wp_clients, or what ever your WordPress Prefix is) as we are going to witness what happens when you do an update incorrectly as well as correctly.

So lets get this out of the way. Lets do a SELECT * FROM my_clients; in the Query Tool first to see what we have to update.

Now lets update something benign like the update_time (well its all benign) with:

UPDATE my_clients SET updated_date = Now();

Then in your SQL History click on the previous SELECT statement you did. All the updated_date fields are now whatever time it was on your servers time. Now lets go back to the WPDB Tables tab and click on the RESTORE button for this table. Following that return to the Query Tool tab and view the restored data to ensure all is working well.

Now lets to an update on all rows that are missing a first_name, email or main_website and replace the fields first_name, email and main_website with REMOVE ME Separate each field you are going to update with a comma. (see if you can build the SQL before looking at the answer):

UPDATE my_clients SET first_name = 'REMOVE ME', email = 'REMOVE ME', main_website = 'REMOVE ME'
WHERE TRIM(first_name) = '' OR TRIM(email) = '' OR TRIM(main_website) = '';

Now we can do a SELECT * FROM my_clients WHERE first_name = ‘REMOVE ME‘; to see what has been affected. Lets save these for the DELETE Tutorial. Now lets do another update and add something to the notes fields like ‘Set to REMOVE ME for DELETE tutorial’
at the same time lets update the updated_date to NOW(). Click on the answer after you have constructed what you think the SQL should look like. BACKUP the table now just in case.

UPDATE my_clients SET notes = 'Set to REMOVE ME for DELETE tutorial', updated_date = NOW() 
WHERE first_name = 'REMOVE ME';

This concludes the SQL Basics Tutorial for UPDATE SQL. If you are feeling really motivated move on over to the DELETE SQL Basics followed by the DELETE Stored Procedure Tutorial as we will need to use that to cleanup the data for the UPDATE Stored Procedure Tutorial.