SQL stands for Structured Query Language and is a common language used for relational databases. This post will go over the basics of the SQL language.
Creating Databases and Tables
To create databases and tables in SQL use the CREATE DATABASE command and CREATE TABLE commands. Before using a database you will have to select it using the USE databasename command.
CREATE DATABASE databasename;
CREATE TABLE users (
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
pass CHAR(128) NOT NULL,
registration_date DATETIME NOT NULL,
The columns in the table above will be one of these MySQL Data Types.
To view the tables in a database use the SHOW TABLES; command. To view the columns inside of the table use SHOW COLUMNS FROM tablename; command or the DESCRIBE tablename; command.
SHOW COLUMNS FROM users;
** or **
The INSERT command can be written in two ways.
- Name the columns you are going to enter data for before entering data. If you do not name a column to receive data it will be given a default value if one is specified or NULL if a default value is not specified. If a column is defined as NOT NULL and does not have a default value, not specifying a value for it will cause an error / warning.
INSERT INTO tablename (column1, column2) VALUES (value_for_column1, value_for_column2);
2. Do not specify any columns, but to include values for every column that exists in the table. Note that you can pass null to the primary key column to get the database to increment the primary key.
INSERT INTO tablename VALUES (value1, null, value2, value3);
INSERT INTO users VALUES (null, 'Zoe', 'Isabella', 'firstname.lastname@example.org', SHA2('mojito', 512), NOW());
Quotes in Queries:
- Numeric values shouldn’t be quoted
- String values (CHAR, VARCHAR, TEXT) must be quoted.
- Functions cannot be quoted.
- The word null must not be quoted.
- Single or Double Quotes may be used
You may add multiple values to a single query by separating them with commas.
INSERT INTO users (first_name, last_name, email, pass, registration_date) VALUES
('John', 'Lennon', 'email@example.com', SHA2('HappinEss' 512), NOW()),
('Paul', 'McCarthey', 'firstname.lastname@example.org', SHA2('letItbe', 512), NOW()),
('Geroge', 'Harrison', 'email@example.com', SHA2('something', 512), NOW()),
('Ringo', 'Starr', 'firstname.lastname@example.org', SHA2('thisboy', 512), NOW());
- INTO is optional in INSERT queries in MySQL
- Depending on which version of MySQL you are running, failing to provide a value for a NOT NULL column may provide a warning with the query still working or errors with it failing
- The backtick (`) is used to reference a table or column name that could be the same as an existing keyword. Table and column names are often wrapped in this character.
- If you are given warnings about the a query, the command SHOW WARNINGS will display the problem.
- A variation on INSERT is REPLACE. If the value used for the table’s primary key or a UNIQUE index is already used, REPLACE updates that row. If not REPLACE inserts a new row.
SELECT `which_columns` FROM `which_table`;
A select query returns rows of data.
SELECT * FROM `users`;
The * is short for all and means return everything. The alternative to * is separating table names with commas.
SELECT `first_name` , `last_name` FROM `users`;
- SELECT is not limited to table columns (
- The order you list columns in a SELECT statement dictates the order the columns are presented in.
- SELECT columns can be used to select an individual column more than once in order to manipulate the column’s data in different ways.
Sometimes when using SELECT queries you will want to limit rows selected based on certain criteria. This can be accomplished in SQL using conditionals. Conditionals are paired with operators which can be viewed here.
SELECT which_columns FROM which_table WHERE condition(s);
SELECT `name` FROM `people` WHERE `birth_date` = '2011-01-26';
SELECT * FROM `items` WHERE (`price` BETWEEN 10.00 AND 20.00) AND (`quantity` > 0);
SELECT * FROM `cities` WHERE (`zip_code` = 90210) OR (`zip_code` = 90211);
SELECT * FROM `cities` WHERE `zip_code` IN (90210, 90211);
The last two queries above will produce the same results. Take a look at the following queries to better understand the use of conditionals in SQL.
SELECT * FROM `users` WHERE `last_name` = 'Simpson';
SELECT `first_name` FROM `users` WHERE `last_name` = 'Simpson';
SELECT * FROM `users` WHERE `email` IS NULL;
SELECT `user_id`, `first_name`, `last_name` FROM `users` WHERE pass = SHA2('mypass', 512);
SELECT `first_name`, `last_name` FROM `users` WHERE (`user_id` < 10) OR (`user_id` > 20);
** The above query can be written as below: **
SELECT `first_name`, `last_name` FROM `users` WHERE `user_id` NOT BETWEEN 10 AND 20;
** Or the query can be written using NOT IN **
SELECT `first_name`, `last_name` FROM `users` WHERE `user_id` NOT IN (10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20);
Using LIKE and NOT LIKE
To search for strings that contain text use LIKE and NOT LIKE. These are commonly used with the following wildcards:
- % – wildcard for 0 or more characters
- _ – wildcard for 1 character
- Consider the following last names in a database – Bank, Banks, Banksy
SELECT * FROM `users` WHERE `last_name` LIKE 'Bank_'; - returns Banks
SELECT * FROM `users` WHERE `last_name` LIKE 'Bank%'; - returns Bank, Banks, Banksy
Sorting Query Results
To give meaning to the order that query results are returned in use the ORDER BY clause. The default order when using ORDER BY is ASC. To reverse this order, use DESC.
- Numbers – low to high
- Dates – oldest to most recent
- Text – Alphabetical A – Z
- Numbers – high to low
- Dates – most recent to oldest
- Text – Alphabetical Z – A
SELECT * FROM tablename ORDER BY column DESC;
SELECT * FROM `users` WHERE `last_name` != 'Simpson'ORDER BY `registration_date` DESC;
The above query selects users who don’t have the last name Simpson and orders them by most recent to oldest.
Limiting Query Results
LIMIT states how many records to return.
SELECT * FROM tablename LIMIT x
You can also return a set number of records using the following syntax.
SELECT * FROM tablename LIMIT x, y
The above example returns y records starting at x. To return records 11 though 20 use the query below:
SELECT * FROM tablename LIMIT 10, 10
Because SELECT does not use a meaningful order when returning results you almost always want to apply an ORDER BY clause. LIMIT can be used with WHERE and ORDER BY clauses, with LIMIT always being placed last.
SELECT which_columns FROM tablename WHERE conditions ORDER BY column LIMIT x
SELECT `first_name`, `last_name` FROM `users` ORDER BY `registration_date` DESC LIMIT 5;
The query above returns the last 5 registered users.
SELECT `first_name`, `last_name` FROM `users` ORDER BY `registration_date` ASC LIMIT 1, 1;
The query above selects the second person to register.
- The LIMIT x,y clause is frequently used when paginating query results (displaying them over multiple pages)
- The LIMIT clause can be used with most types of queries – not just SELECT
After a table contains data, you may need to edit that data. Maybe it was entered incorrectly or it has changed and needs to be updated. The syntax for updating records is written below.
UPDATE tablename SET column=value;
Multiple columns can be updated at once, provided they are separated by a comma.
UPDATE tablename SET column1=valuea, column5=valueB...
You will almost always use a WHERE clause. Omitting this will override every column.
UPDATE tablename SET column2=value WHERE column5=value;
UPDATE `users` SET `email@example.com' WHERE `user_id` = 18;
To protect yourself against accidentally updating too many rows – you can get in the habit of adding a LIMIT clause.
UPDATE `users` SET `firstname.lastname@example.org' WHERE `user_id` = 18 LIMIT 1;
To entirely remove a record from the database you use the DELETE command.
DELETE FROM tablename;
The above query will delete every record in a table, making it empty again. TRUNCATE TABLE is the preferred way to empty a table.
TRUNCATE TABLE tablename;
Usually you will want to delete individual rows. To do this insert a WHERE clause. The process that may be involved with deleting a row is illustrated below.
DELETE FROM tablename WHERE condition;
** Find Primary Key for record to be deleted **
SELECT `user_id` FROM `users` WHERE `first_name`='Peter' AND `last_name`='Tork';
** Preview what will happen when the delete is made **
SELECT * FROM `users` WHERE `user_id`=8;
** Delete the record **
DELETE FROM `users` WHERE `user_id`=8 LIMIT 1;
Finally, we will go over some functions you can use with your MySQL or MariaDB queries. Most of these are functions that can be used with SELECT queries. A query using these functions would loosely follow the syntax below.
SELECT `column1`, FUNCTION(`column2`), `column3` FROM `tablename`;
Before we look at functions let’s take a look at Aliases – these are useful with functions.
An alias is a symbolic renaming of an item used in a query. It is normally applied to tables, columns or function calls. Aliases are created using the term AS. The query below will return the data from the registration_date column with the name reg.
SELECT `registration_date` AS `reg` FROM `users`;
Text functions are functions meant for manipulating text. The most common of these can be viewed here. We will look at 3 of these.
CONCAT() concatenates strings.
SELECT CONCAT(t1, t2) FROM tablename;
SELECT CONCAT('hello', 'world');
** helloworld **
SELECT CONCAT('hello', ', ' 'world');
** hello, world
SELECT CONCAT(`first_name`, ' ', `last_name`) FROM `users`;
By default, MySQL or MariaDB names the column to the function call (i.e. CONCAT(first_name, ‘ ‘, last_name) ). If you are working with PHP you will want to use an Alias.
SELECT CONCAT(`first_name`, ' ', `last_name`) AS `name` FROM `users`;
LENGTH() returns the number of characters in a string. This can be useful if you would like to sort results by their length.
** 5 **
SELECT LENGTH(`last_name`) AS `length`, `last_name` FROM `users` ORDER BY `length` DESC LIMIT 1;
CONCAT_WS or concat with separator adds a separator in between each text item. The function takes arguments in the following order CONCAT_WS(separator, t1, t2, …).
To format a persons full name with spaces between first middle and last names the query below would be used.
SELECT CONCAT_WS(' ', `first`, `middle`, `last`) AS `Name` FROM `tablename`;
MySQL provides a couple dozen functions for formatting and performing calculations on numeric values. For a list of commonly used functions click here. We will review four of these.
FORMAT(n1, n2) takes a number as n1 and adds a comma every 3 spaces. It also adds n2 decimal places. Some uses of it are below:
SELECT FORMAT(2000, 2);
** 2,000.00 **
SELECT CONCAT('$', FORMAT(5639.6, 2)) AS cost;
** $5,639.60 **
Round will take a value (n1) and round that to n2 decimal places. If n2 is omitted it will round the number to the nearest integer.
** 3 **
SELECT ROUND(2.546, 2);
** 2.55 **
RAND() returns a random number between 0 and 1. It also can be used to order the results from a select query in a random order.
** returns a random number between 0 and 1 **
SELECT * FROM tablename ORDER BY RAND();
** returns results in random order **
The MOD() function is the same as using the % symbol.
** 1 **
** 1 **
Date and Time Functions
MySQL contains robust date and time functions. The most common of these can be viewed here. There are five functions that MySQL uses to generate dates:
** 2018-12-24 **
** 06:53:06 **
** 2018-12-24 06:53:36 **
** 1545652441 **
** 2018-12-24 11:54:27 **
Some of these contain just the date, some contain just the time and some contain both. UNIX_TIMESTAMP() contains the number of seconds since January 1, 1970. The above functions can be used to create a value for a column, or to be used as a parameter for the remaining functions.
Formatting the Date and Time
There are two additional functions for formatting date and time: DATE_FORMAT() and TIME_FORMAT(). DATE_FORMAT is used when the value contains both the date and the time (i.e. YYYY-MM-DD HH:MM:SS). TIME_FORMAT must be used if only the time is being stored (i.e. HH:MM:SS).
The format for using this function is DATE_FORMAT(datetime, formatting). The formatting options can be viewed here.
SELECT DATE_FORMAT(NOW(), '%W %M %e, %Y');
** Monday December 24, 2018 **
In your web applications you should almost always use MySQL functions to format the dates (as opposed to using PHP to format dates).
That concludes our introduction to SQL. In the future this area will link to a post on database design and advanced SQL.