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
- 2. Fetch all database
- 3. Select database
- 4. Fetch all tables in a database
- 5. Create & joining table in MySQL
- 6. Add & Drop Column in a table
- 7. Select rows, sort & limit
- 8. Wildcard
- 9. Alias for column while fetching
- 10. Select rows with NULL & NOT NULL values
- 11. Count rows of a table
- 12. Contact columns with separator
- 13. Group Concat
- 14. Truncate vs delete table
- 15. Drop Database
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
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
4. Fetch all tables in a database
To fetch tables from database in MySQL use
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
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.