Useful SQL Queries and Commands

At some point you will need to store data in a database and this blog post should help you get familiar with a SQL database like mySQL (there are other SQL databases like PostgreSQL). I want to cover some basic concepts like setting up the database, specifying proper structure or schema, and playing with tables, columns, and rows. I will focus mostly on SELECT queries but also cover INSERT, UPDATE, DELETE, plus some math functions and regular expressions. I feel like learning SQL seems scary at first so I am hoping that this quick guide will alleviate some of these fears. I will use SQLfiddle (website for testing and sharing SQL queries) and the concept of a blog to work through some common mysql examples and database concepts.

My simple concept of a blog has users and blog posts, the users having attributes like username and password while the blog has attributes like title, body, and the url path. You could think about books and authors or artists and songs, either way we have 2 types of objects which will be stored in their own tables. These objects or tables, user and blog, have attributes called fields or columns, and these attributes have structures or field types. So the users table has an id and a username and these attributes could have a different data type, an id always being an integer while a username being any character (a mix of letter and numbers). You can also specify more information about the data type, like size, which is all very helpful to make the database efficient in processing your queries.

CREATE and INSERT SQL Queries

Here is an example of our 2 tables with some sample data shown with create and insert SQL queries.


CREATE TABLE user (
id int(10) NOT NULL AUTO_INCREMENT,
username VARCHAR(255) NULL,
password VARCHAR(255) NULL,
email VARCHAR(255) NULL,
status int(10) NOT NULL DEFAULT 0,
PRIMARY KEY(id)
);

INSERT INTO blog
(title, body, url)
VALUES
('blog post 1', 'this is my first blog post','blog-post-1'),
('blog post 2', 'this is my second blog post','blog-post-2'),
('blog post 3', 'this is my third blog post','blog-post-3'),
('blog post 4', 'this is my forth blog post','blog-post-4');


Every user and blog entry needs an id so we give identify the data type as NOT NULL and we also specify AUTO_INCREMENT so that it will increment the number on each entry. I have added a status field to each table because we might want some users to be enabled/disabled or some blog posts to be in draft/published mode, and we can specify the default behavior. As a result, when I insert some data into the table, I don’t have to provide information for these fields. We want to tie the user to each blog post so I added a user_id field to the blog table and by default, all blog entries belong to the admin user.

The PRIMARY KEY(id) uniquely identifies each record in a database table and the primary key is always indexed so it helps with performance. I will leave database indexes and SQL optimization for another post.

SELECT Queries with WHERE, AND, OR, IN, and LIKE

Select statements, as you can imagine, retrieve data from the tables. Here is an example of selecting all the data from the users table.

SELECT * FROM user

This selects everything (*) from the table but you could just ask for the column or field by specifying that field name in the query:

SELECT username FROM user

You could also filter the request by specifying a conditional using the WHERE clause. Perhaps you want to retrieve all users where id is 0:

SELECT * FROM user WHERE ID=1

You can get a little more complex with your conditional by using the AND and OR operators. Consider the following queries:

SELECT * FROM user WHERE ID=1 AND username='admin'
SELECT * FROM user WHERE ID=1 OR ID=2
SELECT * FROM user WHERE ID<5
SELECT * FROM user WHERE ID IN (1,2)

The IN operator is used to specify multiple values in a WHERE clause but it can also accept another SELECT statement inside the parenthesis. Consider a situation where you want to grab all the blog posts from the admin user but you do not know the user_id of the admin user. This example would work:

SELECT * FROM blog WHERE user_id IN (SELECT id FROM user WHERE username='admin')

This will retrieve all blog posts since they all currently belong to the admin user. You could also use NOT IN. How about checking for NULL values or NOT NULL values:

SELECT * FROM blog WHERE user_id IS NOT NULL
SELECT * FROM blog WHERE url IS NULL

That last query will not give us anything as we don't allow the data to be NULL in the structure but it could be applicable elsewhere. We can also use the LIKE operator if we don't know the exact value name. Consider these queries:

SELECT * FROM user WHERE username LIKE '%min'
SELECT * FROM user WHERE username LIKE 'ad%'
SELECT * FROM user WHERE username LIKE '%dmi%'

As you probably have guessed, the first query looks for all usernames where the value ends with "min", the second query looks for any username where the value begins with "ad", and the third query searches for any username value that contain the letters "dmi".

SELECT queries with ORDER BY, GROUP BY, and LIMIT

When you retrieve information from the database, you might want to order it in a certain way, group some rows, or even limit the amount of data that comes back. Here is an example of ordering the users alphabetically by username in the reverse order and only returning 2 users:

SELECT * FROM user ORDER BY username DESC LIMIT 2

As you can see, admin is not shown as this is a 3rd user in the table in reverse alphabetical order. Sometimes it makes sense to group rows together based on some field but this is not really applicable to our data. You could group all users by status with this select query:

SELECT * FROM user GROUP BY status

The result is only 1 row as all of our users currently have a status of 0.

UPDATE queries with SET and WHERE

Lets modify some of this data with the UPDATE query. Here is an example of assigning the last blog entry to the test user:

UPDATE blog SET user_id=2 WHERE id=4

Now, the 4th blog entry belongs to the user with ID 2 (test user). If you wanted to update all blog entries to the test user, you can make a whole column update by just removing the WHERE clause.

SQL queries with Math functions and REGEX

There are some really useful math functions that you can use with your SELECT queries and SQL also allows Regular Expressions. For example, you can use min and max functions to return the smallest and largest values of the selected column. Here is an example of the SQL count function used to find out how many blog posts currently belong to the admin user:

SELECT count(id) FROM blog WHERE user_id=1

Since we have assigned one of the blog post to the test user, the result is 3. Regular expressions really make the queries powerful and you can learn more about using regexp here and here. Here is an example of retrieving all users from the database where username begins with the letter a:

SELECT * FROM user WHERE username REGEXP '^a'

You might also find the SQL SUBSTRING() function useful to manipulate the string you are retrieving.

Delete Rows, Drop Columns, and Add columns with SQL

Sometimes, you need to delete a specific row from your database and here is an example of a query that deletes the customuser from the user table:

DELETE FROM user WHERE username='customuser'

How about removing a column? Here is an example of altering the table and dropping the username column:

ALTER TABLE user DROP username

Finally, here is an example of adding a column gender to the user table after the email column:

ALTER TABLE user ADD gender VARCHAR(255) AFTER email;

Was this helpful? In the future, I will follow up with SQL JOINS, indexes, conditionals, and working with import, export, and processes in SQL command-line.

External:

10 Most Useful SQL Queries
Useful SQL commands for MSSQL DB Admins
SQL Commands
SQL Commands