15 MySQL Query every beginners should know

by  
Share:
MySQL query for beginners

In this article, We have compiled a list of 15 MySQL Query that every beginner should know. These queries are the foundation of learning MySQL, and you must practice them.

1. Create database

To create a database in MySQL use CREATE DATABASES YOUR-DATABASE-NAME;

2. Fetch all database

To fetch the list of all database in MySQL use SHOW DATABASES;

Let’s say you want to check if a database is present or search for a database. Using LIKE clause you can use wildcards too.

SHOW DATABASES LIKE 'MYDB';

SHOW DATABASES LIKE '%DB%';

3. Select database

Creating a database does not means it is ready to use, you must sselect that database first, use this query to select database USE YOUR-DATABASE-NAME;

4. Fetch all tables in a database

To fetch tables from database in MySQL use SHOW TABLES;

To fetch the list of tables from a particual database use the sql below.

SHOW TABLES FROM YOUR-DATABASE-NAME

e.g
SHOW TABLES FROM address_book

5. Create & joining table in MySQL

I am going to show you a practical example of creating and joining table. Additionally, you can learn unique key in this article Beginners guide to MySQL unique key.

Take an inventory system as an example, we will be creating 2 tables viz. category and products.

  • Category – Products can be classified into category like mobiles, tablets etc.
  • Products – It contain product information like name, price, category assigned to it

Creating category table and adding some dummy values.

CREATE TABLE IF NOT EXISTS `category` (
  `cat_id` int(11) AUTO_INCREMENT NOT NULL,
  `cat_name` varchar(255) NOT NULL,
  PRIMARY KEY (`cat_id`),
  UNIQUE (`cat_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `category` (`cat_id`, `cat_name`) VALUES
(1, 'Laptops'),
(2, 'Mobiles');

Creating products table and adding some dummy values. In this table we join category table as well. It will have column named prd_cat_id which is the foreign key to category table. I have set ON DELETE RESTRICT so no user can delete category if product is present in the table (Referential Integrity).

CREATE TABLE IF NOT EXISTS `products` (
  `prd_id` int(11) AUTO_INCREMENT NOT NULL,
  `prd_name` varchar(255) NOT NULL,
  `prd_cat_id` int(11) NOT NULL,
  `prd_price` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`prd_id`),
  FOREIGN KEY (`prd_cat_id`) REFERENCES `category` (`cat_id`)  ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `products` (`prd_id`, `prd_name`, `prd_cat_id`, `prd_price`) VALUES
(1, 'HP laptop1', 1, 25000),
(2, 'HP laptop2', 1, 30000),
(3, 'Iphone X', 2, 40000),
(4, 'Iphone 11', 3, 65000);

6. Add & Drop Column in a table

Let’s say you want to drop a column in a table, use the MySQL Query given below

-- dropping single column
ALTER TABLE `product` DROP `prd_model`;

-- dropping multiple columns
ALTER TABLE `product` DROP `prd_model`, DROP `prd_price`;

To add a new column in the table use the query given below. This will add a new column name prd_desc of type varchar.

-- add new column in the table
ALTER TABLE `product` ADD `prd_desc` VARCHAR(255) NOT NULL;

7. Select rows, sort & limit

To get the records of the table use the query below.

-- fetch all column and rows of table
SELECT * FROM `product`

-- fetch only two columns but all records
SELECT `prd_id`, `prd_name` FROM `product`

In a similar case, if you want to fetch the row with that has product id = 1

-- row that has prodict id = 1
SELECT * FROM `product` WHERE `prd_id` = 1

If you want to fetch record ascending or descending wise based on certain column use the query below.

-- Sort by product ID Ascending Wise
SELECT * FROM `product` ORDER BY `prd_id` ASC

-- Sort by product ID Descending Wise
SELECT * FROM `product` ORDER BY `prd_id` DESC

Now, it is not recommend to fetch all the rows at a single time. We can limit rows by using LIMIT keyword.

-- Fetch 5 rows 
SELECT * FROM `product` LIMIT 5

-- Fetch 10 rows but from 6 position
SELECT * FROM `product` LIMIT 6, 10

8. Wildcard

A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator. Symbol used in wildcards are % and _

In general terms, you can use this wildcards to search within records

--search for records where product name starts with I
SELECT * FROM `product` WHERE `prd_name` LIKE 'I%' 

--search for records where product name ends with laptop
SELECT * FROM `product` WHERE `prd_name` LIKE '%laptop' 

--search for records where product name contain the word phone
SELECT * FROM `product` WHERE `prd_name` LIKE '%phone%' 

--search for records where product name where first character can be anything rest of the string
SELECT * FROM `product` WHERE `prd_name` LIKE '_phone'

9. Alias for column while fetching

Sometimes when you join tables or use any function to fetch the column you want a different name of the column. You can achieve it via ALIAS. take a look at an example below.

-- same value but using column as alias
SELECT `prd_id` AS prodct_id  `prd_name` as product_name FROM products;

-- products count but different columnn name
SELECT count(prd_id) as total_products_count FROM products;

10. Select rows with NULL & NOT NULL values

To fetch the rows of the table with null values use the query SELECT * from tablename where columnnname IS NULL; and to fetch records with non null values use SELECT * from tablename where columnnname IS NOT NULL;

11. Count rows of a table

To get the count of rows in the table just use SELECT count(pid) FROM products;if you want you can use WHERE clause as well SELECT COUNT(pid) FROM products WHERE pid > 200;

12. Contact columns with separator

There are times when you prefer using MySQL function over PHP function. For instance, if you want to fetch full name from users table, get fetch the columns (first name, middle name, last name) and then concat it on server side. Instead use can use CONCAT_WS() functions provided in MySQL.

As stated in the official documentation, CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

Take a look at an example below. First create a table with some sample data.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(255) NOT NULL,
  `mname` varchar(255) DEFAULT NULL,
  `lname` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `users` (`id`, `fname`, `mname`, `lname`) VALUES
(1, 'Sachin', 'Ramesh', 'Tendulkar'),
(2, 'Saurav', NULL, 'Ganguly'),
(3, 'Mahindra', 'Singh', 'Dhoni'),
(4, 'Yuvraj', NULL, 'Singh');

Let us now concatenate the user name with a blank space separator using CONCAT_WS() function.

SELECT id, concat_ws(' ', `fname`, `mname`, `lname`) FROM `users` WHERE 1 ORDER BY `id` ASC

Result will be
-------
1     Sachin Ramesh Tendulkar
2     Saurav Ganguly
3     Mahindra Singh Dhoni
4     Yuvraj Singh

13. Group Concat

GROUP_CONCAT() is a MySQL function that is used to concatenate single column values into a single string. It can come in handy when you want to avoid PHP loops.

Let us take a look at an example, we create a table named “football” and add some data.

CREATE TABLE IF NOT EXISTS `football` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `player` varchar(255) NOT NULL,
  `scored` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `football` (`pid`, `player`, `scored`) VALUES
(1, 'Beckham', 'yes'),
(2, 'Ronaldo', 'yes'),
(3, 'Messi', 'no'),
(4, 'Giggs', 'yes'),
(5, 'Gerrard', 'no'),
(6, 'Henry', 'yes'),
(7, 'Pepe', 'no');

The MySQL query to fetch the names of player based on scoring group.

SELECT scored, group_concat(player) as names FROM football GROUP BY scored;

the ressults will be
scored | names
yes | Beckham,Ronaldo,Giggs,Henry
no | Messi,Gerrard,Pepe

14. Truncate vs delete table

To understand the difference between delete and truncate table is very crucial.

TRUNCATE – It drop and re-create the table, and is much faster than deleting rows one by one, particularly for large tables. It empties a table completely; to truncate any table use TRUNCATE [TABLE] tbl_name

DELETE – removes rows from a table. You can use WHERE clause as well to give some conditions as well. The DELETE statement deletes rows from tbl_name and returns the number of deleted rows.

--To delete all records
DELETE FROM products;  

-- to delete products with id > 500
DELETE FROM products WHERE prd_id > 500

15. Drop Database

And all good things comes to an end. if ever you need to drop the database use this MySQL query DROP DATABASE your-database-name; Be extremely careful when you uses drop statement as it drops all tables in the database and deletes the database.

Get the latest updates directly in your inbox for FREE: Subscribe here

Share your feedback / let me know your doubts regarding this tutorial in the comment box given below.

Share:
Hasan

Shahrukh Khan (Hasan)

A software engineer who's a die-hard coder, blogger, dreamer and mentor with years of expertise in web development. Know more...

Related Posts

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.