MySQL Query Tutorial for Beginners

MySQL Create Database, Tables, Data Types

CREATE DATABASE movies; OR CREATE DATABASE IF NOT EXISTS movies;

MySQL SELECT statement syntax

SELECT * FROM ‘members’;

  • SELECT is the SQL keyword that lets the database know that you want to retrieve data.
  • [DISTINCT | ALL] are optional keywords that can be used to fine tune the results returned from the SQL SELECT statement. If nothing is specified then ALL is assumed as the default.
  • {*| [fieldExpression [AS newName]} at least one part must be specified, “*” selected all the fields from the specified table name, fieldExpression performs some computations on the specified fields such as adding numbers or putting together two string fields into one.
  • FROM tableName is mandatory and must contain at least one table, multiple tables must be separated using commas or joined using the JOIN keyword.
  • WHERE condition is optional, it can be used to specify criteria in the result set returned from the query.
  • GROUP BY is used to put together records that have the same field values.
  • HAVING condition is used to specify criteria when working using the GROUP BY keyword.
  • ORDER BY is used to specify the sort order of the result set.

MySQL WHERE Clause with Examples – AND, OR, IN, NOT IN

SELECT * FROM tableName WHERE condition;
SELECT * FROM `members` WHERE `membership_number` = 1;
SELECT * FROM `movies` WHERE `category_id` = 2 AND `year_released` = 2008;
SELECT * FROM `movies` WHERE `category_id` = 1 OR `category_id` = 2;
SELECT * FROM `members` WHERE `membership_number` IN (1,2,3);
SELECT * FROM `members` WHERE `membership_number` NOT IN (1,2,3);
SELECT * FROM `members` WHERE `gender` = ‘Female’;
SELECT * FROM `payments` WHERE `amount_paid` > 2000;
SELECT * FROM `movies` WHERE `category_id`<> 1;
SELECT * FROM `movierentals` WHERE `return_date` < '2012-06-25' AND movie_returned = 0;

MySQL query INSERT INTO Table with Examples

INSERT INTO `table_name`(column_1,column_2,…) VALUES (value_1,value_2,…);

MySQL DELETE Query with Example

DELETE FROM `table_name` [WHERE condition];
DELETE FROM `movies` WHERE `movie_id` = 18;
DELETE FROM `movies` WHERE `movie_id` IN (20,21);

MySQL UPDATE Query with Example

UPDATE `table_name` SET `column_name` = `new_value’ [WHERE condition];
UPDATE `members` SET `contact_number` = ‘0759 253 542’ WHERE `membership_number` = 1;

ORDER BY in MySQL: DESC & ASC

SELECT * FROM members ORDER BY date_of_birth DESC;
SELECT * FROM members ORDER BY date_of_birth ASC
SELECT * FROM `members` ORDER BY `gender`;
SELECT * FROM `members` ORDER BY `gender`,`date_of_birth` DESC;

MySQL GROUP BY and HAVING Clause with Examples

Simple Query: SELECT `gender` FROM `members`;
Group by: SELECT `gender` FROM `members` GROUP BY `gender`; ( Fetch only male, female not all columns male and female)
Simple query: SELECT `category_id`,`year_released` FROM `movies` ;
Group by: SELECT `category_id`,`year_released` FROM `movies` GROUP BY `category_id`,`year_released`;
SELECT `gender`,COUNT(`membership_number`) FROM `members` GROUP BY `gender`;

Restricting query results using the HAVING clause

SELECT * FROM `movies` GROUP BY `category_id`,`year_released` HAVING `category_id` = 8;

MySQL Wildcards Tutorial: Like, NOT Like, Escape, ( % ), ( _ )

SELECT * FROM members WHERE postal_address like ‘% TX’;

% the percentage

SELECT statements… WHERE fieldname LIKE ‘xxx%’;
SELECT * FROM movies WHERE title LIKE ‘%code%’;
SELECT * FROM movies WHERE title LIKE ‘%code’;
SELECT * FROM movies WHERE title LIKE ‘code%’;

_ underscore wildcard

SELECT * FROM movies WHERE year_released LIKE ‘200_’;
SELECT * FROM movies WHERE year_released NOT LIKE ‘200_’;

Leave a comment

Your email address will not be published. Required fields are marked *