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).
To create a database, use
CREATE DATABASE db_name
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');
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.
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%';
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;
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.