blog

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_’;

What are indexes in a Database. What are the types of indexes?

Indexes are the quick references for fast data retrieval of data from a database.

There are two different kinds of indexes.

Clustered Index
1. Only one per table.
2. Faster to read than non clustered as data is physically stored in index order.
Non­clustered Index
1. It can be used many times per table.
2. Quicker for insert and update operations than a clustered index.

What is a join in MySQL?

In MySQL, joins are used to query data from two or more tables. The query is made using the relationship between certain columns existing in the table.

There are four types of joins in MySQL.

  1. Inner join returns rows if there is at least one match in both tables.
  2. Left join returns all the rows from the left table even if there is no match in the right table.
  3. Right join returns all the rows from the right table even if no matches exist in the left table.
  4. Full join would return rows when there is at least one match in the tables.

WordPress Shortcode: Category based Fetch Post

WordPress Shortcode: Category based Fetch Post

WordPress Shortcodes: A Complete Guide With Examples to Create Your Own

The Shortcode API is a simple set of functions for creating WordPress shortcodes for use in posts and pages.
Shortcodes are written by providing a handler function. Shortcode handlers are broadly similar to WordPress filters: they accept parameters (attributes) and return a result (the shortcode output).

Shortcode names should be all lowercase and use all letters, but numbers and underscores should work fine too. Be wary of using hyphens (dashes), you’ll be better off not using them.

The add_shortcode function is used to register a shortcode handler. It takes two parameters: the shortcode name (the string used in a post body), and the callback function name.

Three parameters are passed to the shortcode callback function. You can choose to use any number of them including none of them.

$atts – an associative array of attributes, or an empty string if no attributes are given
$content – the enclosed content (if the shortcode is used in its enclosing form)
$tag – the shortcode tag, useful for shared callback functions

Simple Shortcode

WordPress Query

WordPress Standard Loop

How to Increase Maximum Upload File Size in WordPress

1. Update .htaccess file

2. Update wp-config.php file

WordPress Register Taxonomy

register_taxonomy( $taxonomy, $object_type, $args );

WordPress Register Custom Post Type

Registers a custom post type.
register_post_type( string $post_type, array|string $args = array() )

WordPress Paths

get_stylesheet_directory()
This function returns the absolute path of the current theme (the stylesheet directory) that contains your stylesheet(s). If you have a child theme, this returns the absolute path of the child theme.