SQL / MySQL Tutorial – a Beginner’s Basic MySQL Guide

Hi, in this SQL / MySQL tutorial, you will learn basics of MySQL. You will know what is MySQL, how to create-read-update-delete (CRUD) database, table, insert data into table, joint query, search in tables and some very important MySQL functions.

In SQL / MySQL, SQL referred to Structured Query Language. SQL is a special kind of programming language to manage databases. My Sequel or MySQL is the world’s most used relational database management system (DBMS). MySQL is open source and MySQL is mostly used in web applications. see more about MySQL on Wikipedia…

There are mainly four things we need to know while working with databases – Create, Read, Update and Delete (CRUD).

Create

Create Database

To create a database, use

CREATE DATABASE db_name

Create Tables

To create a table, use

CREATE TABLE table_name(
	id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	state CHAR(2)
);

id name, state are the column names.

Insert data into table

To insert data into a table, use

INSERT INTO table_name(name, state)
	VALUES('Arpan', 'West Bengal');

Read

Creating a database does not select it for use; you must do that explicitly. To make a database the current database, use this command:

USE db_name
/* You can see which database is currently selected using SELECT DATABASE().*/

Read from Database

After selecting the database, it’s time to read data from the selected database. Use the following commands to do that.

SHOW TABLES LIKE ‘%phpbb_%’; # search tables
SHOW DATABASES LIKE ‘%test%’; # search databases
SELECT * FROM table_name;
SELECT DISTINCT FROM table_name; # selects distinct values of rows
SELECE COUNT(*) FROM table_name; # counting rows on a table
SELECT col_name1, col_name2 FROM table_name; # selects particular tables
SELECT col_name AS c1, col_name2 AS c2 FROM table_name; 
	/* c1 will be reference of col_name1 and c2 will be reference of col_name2 and */
SELECT COUNT(col_name) FROM table_name; # count rows on a particular column

‘ * (asterisk)’ means to select all. Or you can specify one or more particular row in the place of asterisk.

Joint query

SELECT c.name, c.continent, ct.name as capital
	FROM country AS c
	JOIN city AS ct
	ON ct.id = c.capital
	WHERE continent = 'Asia'
	ORDER BY c.name DESC;

“ON” provides condition for joining. “JOIN” joins tables into common query. In old syntax, “ON” = “where” and “JOIN” = “FROM”. “ORDER BY” is shorting the returned data. There are two ways to sort, ASC and DESC;

Search in tables

Searching data is one of the most important things to learn. To search, we’ll use the “WHERE” clause. Try the codes below.

/*
	simple search by id, this will return only one result because each id is unique
*/
SELECT * FROM table_name WHERE id = 2;

/*
	search by wildcard character '%'
		'a%' finds starts with 'a'
		'%a' finds ends with 'a'
		'%a%' finds occurrence of 'a' anywhere
*/
SELECT * FROM table_name WHERE name LIKE '%arp%';

/*
	returns rows with phone_no
*/
SELECT * FROM table_name WHERE phone_no IN ('col1', 'col2') LIKE '9748%';

Update

Syntax for updating mysql tables as below.

/*
	updating row(s)from a table with WHERE clause - single column
*/
UPDATE table_name SET col = 50 WHERE id = 3;

/*
	updating row(s)from a table with WHERE clause - multiple column
*/
UPDATE table_name SET  col1='value1', col2='value2', col3='value3' WHERE id = 3;

Delete

Be careful about delete command. Don’t forget to use WHERE clause, because without using WHERE, you may delete all of your records and that can’t be undone. Syntax for updating mysql tables as below.

/*
	deleting row(s)from a table with WHERE clause - single column
*/
DELETE FROM table_name WHERE id = 3;

/*
	deleting a entire table
*/
DROP TABLE table_name;

/*
	deleting multiple tables
*/
DROP TABLE table_name1, table_name2, table_name3;

/*
	deleting a entire database
*/
DROP DATABASE db_name;

Important MySQL functions

• AVG – Aggregate function, returns average value numbers in columns.
• GROUP BY – Group rows for aggregate function.
• SUM – Aggregate function, returns sum of numeric values.
• RIGHT – Return the specified rightmost number of characters.
• LEFT – Return the specified leftmost number of characters.
• CONCAT – Concatenates two strings.
• SUBSTR – Subtracts strings.
• TRIM – Remove unnecessary spaces, tabs.
• UPPER – Make all uppercase.
• LOWER – Make all lowercase.
• ABS – Absolute value for a number.
• SEC_TO_TIME – Convert seconds to time format 00:00:00
• DIV – Division operator, returns integer quadrant (without remainder).
• MOD – Moulder function, returns integer remainder.
• LPAD – Pads character to left.
• CURRDATE – Returns the current date YYYY-MM-DD.
• CURRTIME – Returns current time HH:MM:SS.
• NOW – Returns current date and time YYYY-MM-DD HH:MM:SS.
• DATE_ADD – Adds dates.
• DATESUB – Subtracts dates.
• INTERVAL – Provides interval for DATE_ADD / DATE_SUB.
• WEEK – Provides quantity of interval for DATE_ADD / DATE_SUB.
• MIN – Get minimum from rows.
• MAX – Get maximum from rows.
• See more string functions…

Hope you understood and enjoyed MySQL tutorial. Having problems? Just comment below, I’ll be back to you.

Thank you!