1. MySQL overview

Databases is a collection of data, which can be created and updated using Structured Query Language (SQL) commands. The SQL commands are known as queries. There are various databases which support SQL e.g. MS-SQL, MySQL and PostgreSQL. MySQL is the freely available relational database management system . We can write SQL queries in MySQL to manage data. Further, there are non-relational base database e.g. Redis and Hbase etc. These databases are known as NoSQL databases. Also, there are databases which do not support SQL e.g. MongoDB. This page shows various SQL queries in MySQL with examples.

Note

  • MySQL commands and stored-data are case-insensitive in MySQL.
  • But Database and table names are case-sensitive.
  • # is used for comments.

1.1. Basic commands

This section includes the basic commands for creating a database.

1.1.1. Login and logout

Execute following commands to enter into mysql server. Replace ‘root’ with correct username. Use ‘exit’ to quit from the mysql server.

$ mysql -u root -p            # replace 'root' with username
Enter password:
Welcome to the MySQL monitor.
[...]

mysql> quit       # exit the database
Bye
$

1.1.2. Display databases

‘show databases()’ is used to see the complete list of databases. Also, each query is ended with a semicolon (;). In the following outputs, four databases are shown which are created by ‘MySQL’ itself,

$ mysql -u root -p

mysql> show databases;  # show the list of databases

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

1.1.3. Create and delete database

“CREATE DATABASE” and “DROP DATABASE” commands create and delete the database respectively. “testdb” is the name of the database defined by user. Further, each query (i.e. mysql statement) is ended with semicolon (;).

mysql> CREATE DATABASE testdb;     # create new database
Query OK, 1 row affected (0.04 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| [...]              |
| testdb             |
+--------------------+
5 rows in set (0.08 sec)


mysql> DROP DATABASE testdb;

Note

MySQL is case insensitive language. As convention, uppercase letters are used for MySQL keywords e.g. ‘CREATE’, ‘DATABASE’ and ‘DROP’ are written in uppercase as these are the MySQL keywords.

1.1.4. Selecting database

  • ‘USE’ command is used to select a database for operation.
mysql> CREATE DATABASE writerdb;   # create new database
Query OK, 1 row affected (0.00 sec)

mysql> USE writerdb     # select database for operation
Database changed
mysql>
  • To see the selected database, ‘SELECT’ command can be used,
mysql> SELECT DATABASE();   # display the selected dtatabase

+------------+
| DATABASE() |
+------------+
| writerdb   |
+------------+
1 row in set (0.00 sec)
  • For convenience, ‘->’ and ‘mysql>’ is removed from the queries, which does not generate any output; but used with queries which generates outputs, to make clear distinction between queries and outputs, hence above code will be shown as below throughout the tutorial,
# no '->' and 'mysql' is used for queries which do not generate outputs
DROP DATABASE IF EXISTS writerdb;    # delete database 'writerdb' if exists
CREATE DATABASE writerdb;
USE writerdb;

# 'mysql>' and '->' is used for queries which generate outputs
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| writerdb   |
+------------+
1 row in set (0.00 sec)

mysql>

1.1.5. Create Table

Various commands are used in below code to create a table “writer” to store information whhic are explained below,

  • USE writerdb: It selects the writerdb database, to perform further operations. We need not to execute this command if database is already selected.

  • DROP TABLE IF EXISTS writer: Deletes table if already exists (optional). This command is not required if table does not exist or we do not want to delete the existed table.

  • id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY: This query contains various parts and some of these are optional parameters as shown below.

    • id: the name of the column.
    • INT UNSIGNED: defines the type of values.
    • NOT NULL(optional): This column can not be empty
    • AUTO_INCREMENT (optional): User need not to enter the value for this column. Value will be automatically added by MySQL.
    • PRIMARY KEY (optional): One table can have only one primary key. It uniquely defines the record in the table, therefore it can not be same for two columns and can not be a null value.
    • name VARCHAR(30) NOT NULL UNIQUE: VARCHAR(30) is variable length string which can contain 30 characters. Since there can be only one primary key in a table, therefore to make other columns unique, we can use UNIQUE command. There is no limits on numbers of UNIQUE command in a table.
  • While adding data to the table, all the elements are compulsory except ‘age’ as we do not use “not null” for this.

DROP TABLE IF EXISTS writer;   # optional: delete the existed table
CREATE TABLE writer
(
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(30) NOT NULL UNIQUE,
  age   int
);

mysql> SHOW TABLES;
+--------------------+
| Tables_in_writerdb |
+--------------------+
| writer             |
+--------------------+
1 row in set (0.00 sec)

mysql>
Table 1.1 Various fields in ‘CREATE TABLE’
ITEM DETAILS
VARCHAR character variable
NULL field can be empty
NOT NULL must provided some value

ENUM

SET

user-defined variable with fixed number of values i.e. only either ‘M’ or ‘F’ can be used in above example

set is same as enum except multiple values can be assigned at one time

DATE

TIME

DATETIME

YEAR

TIMESTAMP

YYYY-MM-DD

HH:MM:SS

YYYY-MM-DD HH:MM:SS

YYYY

YYYYMMDDHHMMDD

AUTO_INCREMENT increment the number, when new student is added
PRIMARY KEY

uniquely identify the record, can not be same for two rows.

can not be null, and original value can not be changed.

TINYINT

SMALLINT

MEDIUMINT

INT

range -128 to 127

range -32768 to -32767

range -8,388,608 to 8,388,607

range -2^31 to 2^31 - 1

CHAR

VARCHAR

character of fixed length

character of variable length

1.1.6. Insert data

Insert statement is used to insert the data into a table. Three methods are shown in below code to save the data in the table, which are explained in comments,

#method 1: Fill in correct sequnces
# Since 'id' is AUTO_INCREMENT, therefore NULL is set for this.
INSERT INTO writer VALUES
  (NULL, 'Rabindranath Tagore', 80),
  (NULL, 'Leo Tolstoy', 82);

#Method 2:  skip age as it it optional
INSERT INTO writer (name) VALUES ('Pearl Buck');

# Method 3: fill with keywords args i.e (age, name)
# Since 'id' is PRIMARY KEY AUTO_INCREMENT,
# therefore it is not passed in INSERT statement.
INSERT INTO writer (age, name) VALUES
  (30, 'Meher Krishna Patel');

Following are the saved data in the table, which can be seen using ‘SELECT’ command. Here, ‘*’ is used to show all data in the table. We can also specify the specific column as show below,

mysql> SELECT * FROM writer;
+----+---------------------+------+
| id | name                | age  |
+----+---------------------+------+
|  1 | Rabindranath Tagore |   80 |
|  2 | Leo Tolstoy         |   82 |
|  3 | Pearl Buck          | NULL |
|  4 | Meher Krishna Patel |   30 |
+----+---------------------+------+
4 rows in set (0.00 sec)


mysql> SELECT name FROM writer;  # show only name
+---------------------+
| name                |
+---------------------+
| Leo Tolstoy         |
| Meher Krishna Patel |
| Pearl Buck          |
| Rabindranath Tagore |
+---------------------+
4 rows in set (0.00 sec)

mysql>

In the above query, three methods are shown to insert data into table.

  • Method 1: To insert the data, all the column and corresponding values are defined in the table. Note that, NULL is used for primary key as it it auto-incremented.
  • Method 2: Since ‘age’ column is not defined as ‘NOT NULL’, therefore it is not used for inserting the data.
  • Method 3: Here positional parameters are used i.e. (age, name) and then corresponding values are assigned.
  • SELECT * FROM writer: In SQL, * indicates all. Therefore this statement says that select everythign from table ‘writer’. ‘Select’ statement is discuss in next section.
  • SELECT name FROM writer: Display only name column.

Note

  • Primary key defines the clustered-index which decides the physical arrangement of the table.
  • To increase the speed of the search, we can create non-clustered index for other columns.

1.2. Select statement

In previous section, SELECT query which display the complete table. In general, SELECT statement is used with various other clauses e.g. WHERE, LIKE and ORDER BY etc. to specify the selection criteria. In this section, various such clauses are shown.

1.2.1. Basic SELECT Queries

  • SELECT colName1 AS ‘NewName1’, colName2 AS ‘NewName2’, … FROM tableName ;
mysql> SELECT name, age FROM writer;  # Select name and age only
+---------------------+------+
| name                | age  |
+---------------------+------+
| Rabindranath Tagore |   80 |
| Leo Tolstoy         |   82 |
| Pearl Buck          | NULL |
| Meher Krishna Patel |   30 |
+---------------------+------+
4 rows in set (0.00 sec)

mysql> SELECT name as 'Author', age FROM writer; # Display 'name' as 'Author'.
+---------------------+------+
| Author              | age  |
+---------------------+------+
| Rabindranath Tagore |   80 |
| Leo Tolstoy         |   82 |
| Pearl Buck          | NULL |
| Meher Krishna Patel |   30 |
+---------------------+------+
4 rows in set (0.04 sec)

mysql>

1.2.2. Where

  • SELECT colName1, colName2, … FROM tableName WHERE conditions ;
# Select Leo Tolstoy only
mysql> SELECT * FROM writer WHERE name = 'Leo Tolstoy';
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  2 | Leo Tolstoy |   82 |
+----+-------------+------+
1 row in set (0.00 sec)

# Selects id between 1 and 4 i.e 2 and 3
mysql> SELECT * FROM writer WHERE (id >1 and id < 4);
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  2 | Leo Tolstoy |   82 |
|  3 | Pearl Buck  | NULL |
+----+-------------+------+
2 rows in set (0.06 sec)

Note

We can not use 1< id < 4 in SQL. We have to use ‘and/or’ for multiple conditions.

1.2.3. Inserting data with SELECT-WHERE

  • Before moving further, lets create another table “book”, which stores the names of the books by the authors in table ‘writer’.
  • In the below table, SELECT-WHERE statements are used to insert the writer_id into ‘book’ from ‘writer’ table.
DROP TABLE IF EXISTS book;
CREATE TABLE book # creating table 'book'
(
 writer_id  INT UNSIGNED NOT NULL,
 book_id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(100) NOT NULL,
 price INT UNSIGNED
);

# Insert data in the table
INSERT INTO book (writer_id,title,price)
  SELECT id, 'The Good Earth', 200         # select id
  FROM writer WHERE name = 'Pearl Buck';   # where name = 'Pearl Buck'

INSERT INTO book (writer_id,title,price)
  SELECT id, 'The Home and The World',  250
  FROM writer WHERE name = 'Rabindranath Tagore';

INSERT INTO book (writer_id,title,price)
  SELECT id, 'Gitanjali', 100
  FROM writer WHERE name = 'Rabindranath Tagore';

INSERT INTO book (writer_id,title,price)
    SELECT id, 'War and Peace', 200
    FROM writer WHERE name = 'Leo Tolstoy';

INSERT INTO book (writer_id,title,price)
    SELECT id, 'Anna Karenina', 100
    FROM writer WHERE name = 'Leo Tolstoy';
  • Following is the data stored in ‘book’ table,
mysql> SELECT * FROM book;
+-----------+---------+------------------------+-------+
| writer_id | book_id | title                  | price |
+-----------+---------+------------------------+-------+
|         3 |       1 | The Good Earth         |   200 |
|         1 |       2 | The Home and The World |   250 |
|         1 |       3 | Gitanjali              |   100 |
|         2 |       4 | War and Peace          |   200 |
|         2 |       5 | Anna Karenina          |   100 |
+-----------+---------+------------------------+-------+
5 rows in set (0.00 sec)

mysql>

1.2.4. ORDER BY

  • Syntax:
SELECT colName1, colName2, ... FROM tableName
    ORDER BY 'colName1' ASC, colName2 DESC, ... ;
  • Example:
# ORDER BY: ASC by default
mysql> SELECT title, price FROM book ORDER BY title;
 +------------------------+-------+
 | title                  | price |
 +------------------------+-------+
 | Anna Karenina          |   100 |
 | Gitanjali              |   100 |
 | The Good Earth         |   200 |
 | The Home and The World |   250 |
 | War and Peace          |   200 |
 +------------------------+-------+
 5 rows in set (0.06 sec)

 # descending order
 mysql> SELECT title, price FROM book ORDER BY title DESC;
 +------------------------+-------+
 | title                  | price |
 +------------------------+-------+
 | War and Peace          |   200 |
 | The Home and The World |   250 |
 | The Good Earth         |   200 |
 | Gitanjali              |   100 |
 | Anna Karenina          |   100 |
 +------------------------+-------+
 5 rows in set (0.00 sec)


 # First arrange by price and then by title.
 # 'The Good Earth' and 'War and Peace' have same prices.
 # Since 'title ASC' is used, therefore 'The Good Earth' is place above the 'War and Peace'.

 mysql> SELECT title, price FROM book ORDER BY title DESC, price ASC;
 +------------------------+-------+
 | title                  | price |
 +------------------------+-------+
 | War and Peace          |   200 |
 | The Home and The World |   250 |
 | The Good Earth         |   200 |
 | Gitanjali              |   100 |
 | Anna Karenina          |   100 |
 +------------------------+-------+
 5 rows in set (0.00 sec)

 mysql>

1.2.5. LIKE

LIKE is used for pattern matching with percentage ( % ) and underscore ( _ ) signs.

# %th: find titles which start with 'th'.
mysql> SELECT title, price FROM book WHERE title LIKE 'th%';
+------------------------+-------+
| title                  | price |
+------------------------+-------+
| The Good Earth         |   200 |
| The Home and The World |   250 |
+------------------------+-------+
2 rows in set (0.00 sec)

# %an%: find titles which contain 'an'.
mysql> SELECT title, price FROM book WHERE  title LIKE '%an%';
+------------------------+-------+
| title                  | price |
+------------------------+-------+
| The Home and The World |   250 |
| Gitanjali              |   100 |
| War and Peace          |   200 |
| Anna Karenina          |   100 |
+------------------------+-------+
4 rows in set (0.00 sec)

# th%: find titles which end with 'th'.
mysql> SELECT title, price FROM book WHERE title LIKE '%th';
+----------------+-------+
| title          | price |
+----------------+-------+
| The Good Earth |   200 |
+----------------+-------+
1 row in set (0.00 sec)

# %_an%: find titles which contain
# atleast one word before 'an'.
mysql> SELECT title, price FROM book WHERE title LIKE '%_an%';
+------------------------+-------+
| title                  | price |
+------------------------+-------+
| The Home and The World |   250 |
| Gitanjali              |   100 |
| War and Peace          |   200 |
+------------------------+-------+
3 rows in set (0.00 sec)

Note

% : it looks for zero or more characters to fill it’s place _ : It looks for exactly one character to fill it’s place. For two characters, use two underscore and so on.

  • Try these commands as well,
#Try for these commands also.
SELECT * FROM book WHERE title LIKE '_rt_';

SELECT * FROM book WHERE title LIKE '%rt_';

#two underscore arter 'rt'
SELECT * FROM book WHERE title LIKE '%rt__';

#three underscore after 'rt'
SELECT * FROM book WHERE title LIKE '%r___';

SELECT * FROM book WHERE title LIKE '%r_%';

1.3. JOIN

  • Syntax:
SELECT colName1, colName2, ... FROM tableName1
JOIN_TYPE tableName2
ON tableName1.colName = tableName2.colName ;

Data are often stores in different tables or databases. In our example, Author details are stored in ‘writer’ table, whereas books’ details are stored in ‘book’ table. Further, ‘book’ table does not contain the name of the authors; but it contains the ‘writer_id’ which can be used to find the name of the author from ‘writer’ table. JOIN queries are used for such operations.

Next, we will see the examples of following joins,

  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN

Note

Before we start, remember that we don’t have any record of writer ‘Meher Krishna Patel’ in ‘book’ table.

1.3.1. INNER JOIN

INNER JOIN combines the data which are common in both the tables; e.g. ‘Meher Krishna Patel’ is not available in ‘book’ table, therefore it will not be displayed in INNER JOIN’s results. Further, order of the tables does not matter in INNER JOIN as shown in example.

In following example, writer.id = book.writer_id is used because in writer’s ‘id’ is stored as ‘writer_id’ in book.

# 'writer' is used before 'book'
mysql> SELECT id, name, title, price FROM writer
-> INNER JOIN book
-> ON writer.id  = book.writer_id ORDER BY id;
+----+---------------------+------------------------+-------+
| id | name                | title                  | price |
+----+---------------------+------------------------+-------+
|  1 | Rabindranath Tagore | Gitanjali              |   100 |
|  1 | Rabindranath Tagore | The Home and The World |   250 |
|  2 | Leo Tolstoy         | Anna Karenina          |   100 |
|  2 | Leo Tolstoy         | War and Peace          |   200 |
|  3 | Pearl Buck          | The Good Earth         |   200 |
+----+---------------------+------------------------+-------+
5 rows in set (0.03 sec)


# 'writer' is used after 'book'
mysql> SELECT id, name, title, price FROM book
    -> INNER JOIN writer
    -> ON writer.id  = book.writer_id ORDER BY id;
+----+---------------------+------------------------+-------+
| id | name                | title                  | price |
+----+---------------------+------------------------+-------+
|  1 | Rabindranath Tagore | Gitanjali              |   100 |
|  1 | Rabindranath Tagore | The Home and The World |   250 |
|  2 | Leo Tolstoy         | Anna Karenina          |   100 |
|  2 | Leo Tolstoy         | War and Peace          |   200 |
|  3 | Pearl Buck          | The Good Earth         |   200 |
+----+---------------------+------------------------+-------+
5 rows in set (0.00 sec)

1.3.2. LEFT OUTER JOIN

It is same as inner join except it takes all the rows of left-table, and then joins the right-table row to it.

#'writer' is left table.
mysql> SELECT id, name, title, price FROM writer
    -> LEFT OUTER JOIN book
    -> ON writer.id  = book.writer_id ORDER BY id;
+----+---------------------+------------------------+-------+
| id | name                | title                  | price |
+----+---------------------+------------------------+-------+
|  1 | Rabindranath Tagore | Gitanjali              |   100 |
|  1 | Rabindranath Tagore | The Home and The World |   250 |
|  2 | Leo Tolstoy         | Anna Karenina          |   100 |
|  2 | Leo Tolstoy         | War and Peace          |   200 |
|  3 | Pearl Buck          | The Good Earth         |   200 |
|  4 | Meher Krishna Patel | NULL                   |  NULL |
+----+---------------------+------------------------+-------+
6 rows in set (0.00 sec)

1.3.3. RIGHT OUTER JOIN

It takes all the rows of right-table, and then joins the left-table row to it.

# 'book' is right table.
# Meher Krishna Patel is not added here, because
# it takes all data from 'book' table and then add 'writer' to it
mysql> SELECT id, name, title, price FROM writer
    -> RIGHT OUTER JOIN book
    -> ON writer.id  = book.writer_id ORDER BY id;
+------+---------------------+------------------------+-------+
| id   | name                | title                  | price |
+------+---------------------+------------------------+-------+
|    1 | Rabindranath Tagore | Gitanjali              |   100 |
|    1 | Rabindranath Tagore | The Home and The World |   250 |
|    2 | Leo Tolstoy         | Anna Karenina          |   100 |
|    2 | Leo Tolstoy         | War and Peace          |   200 |
|    3 | Pearl Buck          | The Good Earth         |   200 |
+------+---------------------+------------------------+-------+
5 rows in set (0.02 sec)

1.4. Group by

Let’s create another table with more data, to understand GROUP BY clause.

# use database writerdb
USE writerdb;

# create table
DROP TABLE IF EXISTS student;
CREATE TABLE student
(
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(30) NOT NULL UNIQUE,
  gender NVARCHAR(5),
  age   int,
  city  NVARCHAR(30),
  marks int
);

# insert data
INSERT INTO student (name, gender, age, city, marks) VALUES
  ('Tom', 'M', 20, 'NY', 30),
  ('Kim', 'F', 18, 'NY', 40),
  ('Sam', 'M', 18, 'NY', 60),
  ('Kris', 'M', 20, 'DEL', 80),
  ('Harry', 'M', 19, 'DEL', 70),
  ('Eliza', 'F', 19, 'DEL', 50),
  ('Kate', 'F', 15, 'ACK', 20),
  ('Peter', 'M', 21, 'ACK', 80),
  ('Ryan', 'M', 20, 'ACK', 60);

Note

GROUP BY command is always used with aggregate functions e.g. sum, min, max and avg etc.

# Find the average marks of students from same city.
# Also, display the total number of students in same city.
mysql> SELECT city, AVG(marks) as 'AVG Marks', count(id) as 'Total Students'
    -> FROM student GROUP BY city;
+------+-----------+----------------+
| city | AVG Marks | Total Students |
+------+-----------+----------------+
| ACK  |   53.3333 |              3 |
| DEL  |   66.6667 |              3 |
| NY   |   43.3333 |              3 |
+------+-----------+----------------+
3 rows in set (0.11 sec)



# Find the sum of marks of students from same city and same gender.
# Also, display the total number of students in this case.
mysql> SELECT city, gender, SUM(marks) as 'Total Marks', COUNT(city) 'Total Students' FROM student
    -> GROUP BY city, gender
    -> ORDER BY city;
+------+--------+-------------+----------------+
| city | gender | Total Marks | Total Students |
+------+--------+-------------+----------------+
| ACK  | F      |          20 |              1 |
| ACK  | M      |         140 |              2 |
| DEL  | F      |          50 |              1 |
| DEL  | M      |         150 |              2 |
| NY   | F      |          40 |              1 |
| NY   | M      |          90 |              2 |
+------+--------+-------------+----------------+
6 rows in set (0.01 sec)

1.5. Having vs Where

Note

  • WHERE clause filters rows before aggregation operation.
  • HAVING clause filters rows after aggregation operation
# writing age once is compulsory, as HAVING operation is performed on age.
# replace both ages from below query, and it will give blank result.
mysql> SELECT age, SUM(marks), COUNT(id) FROM student
    -> GROUP BY age HAVING age < 20;
+------+------------+-----------+
| age  | SUM(marks) | COUNT(id) |
+------+------------+-----------+
|   15 |         20 |         1 |
|   18 |        100 |         2 |
|   19 |        120 |         2 |
+------+------------+-----------+
3 rows in set (0.00 sec)

#writing age is is not compulsory in WHERE.
mysql> SELECT SUM(marks), COUNT(id) FROM student
    -> WHERE age < 20 GROUP BY age;
+------------+-----------+
| SUM(marks) | COUNT(id) |
+------------+-----------+
|         20 |         1 |
|        100 |         2 |
|        120 |         2 |
+------------+-----------+
3 rows in set (0.00 sec)

Note

Aggregate function can not be used with WHERE, i.e. [SELECT * from student WHERE sum(marks)>100] is a invalid query; whereas [SELECT city, COUNT(id) FROM student GROUP BY city HAVING SUM(marks)< 180] is valid query.

1.6. Store Procedure

If certain queries are used frequently, then we can write store procedures for it. We can call store procedures using ‘CALL’ command as shown in next examples. Three examples are shown here. In first example no parameter is passed. In second example only one input parameter is passed. Then in third example, one input and one output parameter is passed. We can have any number of input and output parameters in store procedures.

1.6.1. Simple store procedure

# simple store procedure
# use database
USE writerdb;

# creating store procedure
DELIMITER //
CREATE PROCEDURE cpGetWriter()
 BEGIN
 SELECT * FROM writer;
 END //
DELIMITER ;
  • ‘CALL’ is used to invoke the store procedure,
# results will be same as SELECT * FROM writer;
mysql> CALL cpGetWriter();
+----+---------------------+------+
| id | name                | age  |
+----+---------------------+------+
|  1 | Rabindranath Tagore |   80 |
|  2 | Leo Tolstoy         |   82 |
|  3 | Pearl Buck          | NULL |
|  4 | Meher Krishna Patel |   30 |
+----+---------------------+------+
4 rows in set (0.00 sec)

1.6.2. Store procedure with input

# Store procedure with input
USE writerdb; # use database

#create store procedure
DELIMITER //
CREATE PROCEDURE cpBookDetails(IN writerID INT)
BEGIN
SELECT id, name, title, price FROM writer
INNER JOIN book
ON writer.id  = book.writer_id WHERE id=writerID;
END //
DELIMITER ;
  • Following are the outputs for the above store procedure,
mysql> CALL cpBookDetails(2);
+----+-------------+---------------+-------+
| id | name        | title         | price |
+----+-------------+---------------+-------+
|  2 | Leo Tolstoy | War and Peace |   200 |
|  2 | Leo Tolstoy | Anna Karenina |   100 |
+----+-------------+---------------+-------+
2 rows in set (0.00 sec)

1.6.3. Store procedure with input and output

# use database
USE writerdb;

DELIMITER $$
CREATE PROCEDURE cpTotalBooks(
 IN writerID INT,
 OUT total int)
BEGIN
 SELECT COUNT(book_id) INTO total
 FROM book WHERE writer_id = writerID;
END$$
DELIMITER ;

1.7. Save and Execute query from ‘.sql’ file

#call stored procedure
# input parameter = 1,
# output parameter = @totalBooks. @ is compulsory
mysql> CALL cpTotalBooks(1, @totalBooks);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @totalBooks; #display value
+-------------+
| @totalBooks |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

Note

  1. Delimiter: Create procedure needs two delimiter i.e. to end the create command and store procedure itself. Therefore “DELIMITER $$” or “DELIMITER //” are used. At the end of the store procedure, DELIMITER should be set to ‘ ; ‘ again.
  2. For multiple inputs and outputs use following format:
DELIMITER $$
   CREATE PROCEDURE cpTotalBooks(
     IN writerID INT, IN bookID INT,
     OUT totalWriter int, OUT totalBook int)
   BEGIN
    ---query here---
   END$$
DELIMITER ;

1.8. View

VIEWs are nothing but virtual tables. Suppose we do not want to display writer_id, to the clients from ‘book’ table. Then instead of creating a new table, we can create a view of ‘book’ table, which does not display the writer_id. All the quires, which are applicable to tables can be used with VIEW.

# VIEW Example 1:
USE writerdb;

#create VIEW: display title and price of books.
CREATE VIEW BookPrice AS
    SELECT title, price FROM book;

mysql> select * from BookPrice;
+------------------------+-------+
| title                  | price |
+------------------------+-------+
| The Good Earth         |   200 |
| The Home and The World |   250 |
| Gitanjali              |   100 |
| War and Peace          |   200 |
| Anna Karenina          |   100 |
+------------------------+-------+
5 rows in set (0.00 sec)



# VIEW Example 2:
# create VIEW: Display writers, their books and prices
CREATE VIEW writerBookPrice AS
    SELECT name, title, price FROM writer
    INNER JOIN book
    ON writer.id  = book.writer_id ORDER BY id;

# using VIEW as table
mysql> select * from writerBookPrice;
+---------------------+------------------------+-------+
| name                | title                  | price |
+---------------------+------------------------+-------+
| Rabindranath Tagore | The Home and The World |   250 |
| Rabindranath Tagore | Gitanjali              |   100 |
| Leo Tolstoy         | War and Peace          |   200 |
| Leo Tolstoy         | Anna Karenina          |   100 |
| Pearl Buck          | The Good Earth         |   200 |
+---------------------+------------------------+-------+
5 rows in set (0.00 sec)

1.9. Constraint

  • Syntax
ALTER TABLE tableName1
ADD CONSTRAINT constraint_name
FOREIGN KEY foreignKeyName (colName1)
REFERENCES tableName2 (colName2)
ON UPDATE CASCADE
ON DELETE RESTRICT;

Currently, table ‘book’ is independent of table ‘writer’ i.e. we can any writer_id to ‘book’ e.g. INSERT INTO book (writer_id,title,price) VALUES (8, ‘Unknown ID’, 200); will insert data into book. But, there is no such writer with id = 8. To avoid such entries, we can use CONSTRAINT as follows.

# ADD CONSTRAINT to existing table:
ALTER TABLE book
  ADD CONSTRAINT fk_Writer
  FOREIGN KEY key_writer(writer_id)
  REFERENCES writer(id)
  ON UPDATE CASCADE
  ON DELETE CASCADE;
# CONSTRAINT will not add, if there is already some writer_id in
# 'book', which is not present in 'writer' table.

Note

ON DELETE / UPDATE CASCADE: If any author is deleted from ‘writer’ table, then CASCADE option will delete the corresponding columns in ‘book’ table.

We can replace ‘CASCADE’ with ‘RESTRICT’ or ‘NO ACTION’ (these two do not allow delete operation) or “SET NULL” (it will set writer_id to NULL). Same rules apply for ON UPDATE operations.

  • We can also define constraint, while creating a new table as follows,
# Add constraint while creating table
DROP TABLE IF EXISTS book;
CREATE TABLE book
(
  writer_id  INT UNSIGNED NOT NULL,
  book_id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  price INT UNSIGNED,
  CONSTRAINT fk_Writer
  FOREIGN KEY key_writer(writer_id)
   REFERENCES writer(id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
);
# ADD keyword is not used here

1.10. Command Summary

Table 1.2 Command Summary
Command Description
mysql -u root -p Login (replace ‘root’ with correct username)
quit exit from mysql
show databases display all the databases
CREATE DATABASE dbname create database ‘dbname’
DROP DATABASE dbname delete database ‘dbname’
DROP DATABASE IF EXISTS dbname delete database ‘dbname if exists
USE dbname select database for working
SELECT DATABASE() shows the selected database
CREATE TABLE tblname(…); create table ‘tblname’
SHOW TABLES display the list of tables
DESCRIBE tblname shows the details of table ‘tblname’
INSERT INTO tblname VALUES(…) insert values to table
SELECT * FROM tblname show all contents of tblname
ALTER TABLE tblname ADD … add row to existed table i.e. alter table