Good

 ADBMS Lab

MYSQL Database Queries

Create Database

Mysql>CREATE DATABASE sjcds;

Show Database

mysql> SHOW DATABASES;

Use Database

mysql> USE emplyeedb;

Drop Database

DROP DATABASE [IF EXISTS] database_name;

Create Table

mysql> CREATE TABLE employee_table(

id int NOT NULL AUTO_INCREMENT,

name varchar(50) NOT NULL,

degree varchar(35) NOT NULL,

age int NOT NULL,

PRIMARY KEY (id) );

Show Tables

mysql> SHOW TABLES;

Alter table

ALTER TABLE cus_tbl

ADD mobile int(10) NOT NULL;

Drop Table

mysql> DROP TABLE orders;

Truncate Table

TRUNCATE TABLE table_name;

Rename Table

RENAME TABLE stu_table TO student_table;

Insert Table

CREATE TABLE People(

    id int NOT NULL AUTO_INCREMENT,

    name varchar(45) NOT NULL,

    occupation varchar(35) NOT NULL,

    age int,

    PRIMARY KEY (id)

);

INSERT INTO People VALUES

(102, 'Joseph', 'Developer', 30),

(103, 'Mike', 'Leader', 28),

(104, 'Stephen', 'Scientist', 45);

Select Table

SELECT * FROM People;

Select * from People WHERE id = 103;

SELECT Name FROM employee_detail;

Update Table

UPDATE People

SET name = 'Mary', occupation = 'Content Writer'

WHERE id = 105;

Delete Table

DELETE FROM cus_tbl

WHERE cus_id = 6;

MySQL Select Query

SELECT * from customers;

Truncate Table

truncate table customers;

Drop Table

drop table customers;

 

Group by

SELECT name FROM stu_table GROUP BY age HAVING (age)>25;

MYSQL Control Flow Function

IF

SELECT IF(200>350,'YES','NO');

SELECT IF(251 = 251,' Correct', 'Wrong');

SELECT IF(STRCMP('Rinky Ponting','Yuvraj Singh')=0, 'Correct', 'Wrong');

SELECT lastname, IF(age>20,"Mature","Immature") As Result FROM student;

IFNULL

SELECT IFNULL(0,5);

SELECT IFNULL("Hello", "How are you?");

SELECT IFNULL(NULL,5);

SELECT contactname, IFNULL(cellphone, homephone) phone FROM student_contact;

SELECT name, IFNULL(mobile, phone) phone FROM stu_table;

NULLIF

SELECT NULLIF("ADBMS", "ADBMS");

In the above function, the MySQL statement checks the first expression is equal to the second expression or not. If both expressions are the same, it returns NULL. Otherwise, it will return the first expression.

SELECT NULLIF("Hello", "404");

CASE

SELECT studentid, firstname,

CASE class

WHEN 'CS' THEN 'Computer Science'

WHEN 'EC' THEN 'Electronics and Communication'

ELSE 'Electrical Engineering'

END AS department from students;

MYSQL Conditions

AND

SELECT * FROM cus_tbl WHERE cus_firstname = 'Ajeet' AND cus_id > 3;

OR

SELECT * FROM cus_tbl WHERE cus_firstname = 'Ajeet' OR cus_id > 100;

AND & OR

SELECT * FROM students WHERE (course_name = 'Java' AND student_name = 'Aryan') OR (student_id < 2);

Boolean

Select TRUE, FALSE, true, false, True, False;

SELECT studentid, name, pass FROM student1 WHERE pass is TRUE;

IN (To reduce OR)

SELECT * FROM stu_table WHERE name IN ('Dinesh', 'Joseph', 'Cecil');

LIKE

SELECT officer_name FROM officers WHERE address LIKE 'Luck%';

Exists

CREATE TABLE customer(

  cust_id int NOT NULL,

  name varchar(35),

  occupation varchar(25),

  age int

);

CREATE TABLE orders (

    order_id int NOT NULL,

    cust_id int,

    prod_name varchar(45),

    order_date date

);

INSERT INTO customer(cust_id, name, occupation, age)

VALUES (101, 'Peter', 'Engineer', 32),

(102, 'Joseph', 'Developer', 30),

(103, 'John', 'Leader', 28),

(104, 'Stephen', 'Scientist', 45),

(105, 'Suzi', 'Carpenter', 26),

(106, 'Bob', 'Actor', 25),

(107, NULL, NULL, NULL);

INSERT INTO orders (order_id, cust_id, prod_name, order_date)

VALUES (1, '101', 'Laptop', '2020-01-10'),

(2, '103', 'Desktop', '2020-02-12'),

(3, '106', 'Iphone', '2020-02-15'),

(4, '104', 'Mobile', '2020-03-05'),

(5, '102', 'TV', '2020-03-20');

msql> SELECT * FROM customer;

AND,

mysql> SELECT * FROM orders;

mysql> SELECT name, occupation FROM customer

WHERE EXISTS (SELECT * FROM Orders

WHERE customer.cust_id = Orders.cust_id);

NOT

SELECT *

FROM stu_table

WHERE name NOT IN ('Dinesh','Joseph','Mary');

NOT Equal

SELECT * FROM stu_table WHERE degree <> "Developer";

SELECT * FROM students WHERE city != "England";

IS NULL

SELECT *

FROM stu_table

WHERE name IS NULL;

IS NOT NULL

SELECT *

FROM stu_table

WHERE name IS NOT NULL;

BETWEEN

SELECT *

FROM stu_table

WHERE id BETWEEN 101 AND 103;

 

MySQL Clauses

From

SELECT * FROM officers WHERE officer_id <= 3;

Where

SELECT * FROM officers WHERE address = 'Mau';

SELECT * FROM officers WHERE address = 'Lucknow' AND officer_id < 5;

SELECT * FROM officers WHERE address = 'Lucknow' OR address = 'Mau';

Distinct (Remove duplication)

SELECT DISTINCT address FROM officers;

Distinct with multiple expressions

SELECT DISTINCT officer_name, address FROM officers;

Order By

SELECT * FROM officers WHERE address = 'Lucknow' ORDER BY officer_name;

SELECT * FROM officers WHERE address = 'Lucknow' ORDER BY officer_name ASC;

SELECT * FROM officers WHERE address = 'Lucknow' ORDER BY officer_name DESC;

 

MySQL JOINS

MySQL JOINS are used with SELECT statement. It is used to retrieve data from multiple tables. It is performed whenever you need to fetch records from two or more tables.

There are three types of MySQL joins:

MySQL INNER JOIN (or sometimes called simple join)

MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)

MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

Consider two tables "officers" and "students", having the following data.

SELECT officers.officer_name, officers.address, students.course_name

FROM officers

INNER JOIN students

ON officers.officer_id = students.student_id;

 

MySQL Left Outer Join

The LEFT OUTER JOIN returns all rows from the left hand table specified in the ON condition and only those rows from the other table where the join condition is fulfilled.

SELECT officers.officer_name, officers.address, students.course_name

FROM officers

LEFT JOIN students

ON officers.officer_id = students.student_id;

MySQL Right Outer Join

The MySQL Right Outer Join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where he join condition is fulfilled.

SELECT officers.officer_name, officers.address, students.course_name, students.student_name

FROM officers

RIGHT JOIN students

ON officers.officer_id = students.student_id;

 

Constraints used in MySQL

The following are the most common constraints used in the MySQL:

o NOT NULL

o CHECK

o DEFAULT

o PRIMARY KEY

o AUTO_INCREMENT

o UNIQUE

o INDEX

o ENUM

o FOREIGN KEY

NOT NULL Constraint

This constraint specifies that the column cannot have NULL or empty values. The below statement creates a table with NOT NULL constraint.

1. mysql> CREATE TABLE Student(Id INTEGER, LastName TEXT NOT NULL, FirstName TEXT NOT NULL, City VARCHAR(35));

Execute the queries listed below to understand how it works:

1. mysql> INSERT INTO Student VALUES(1, 'Hanks', 'Peter', 'New York');

2.

3. mysql> INSERT INTO Student VALUES(2, NULL, 'Amanda', 'Florida');

Output

In the above image, we can see that the first INSERT query executes correctly, but the second statement fails and gives an error that says column LastName cannot be null.

UNIQUE Constraint

This constraint ensures that all values inserted into the column will be unique. It means a column cannot stores duplicate values. MySQL allows us to use more than one column with UNIQUE constraint in a table. The below statement creates a table with a UNIQUE constraint:

1. mysql> CREATE TABLE ShirtBrands(Id INTEGER, BrandName VARCHAR(40) UNIQUE, Size VARCHAR(30));

Execute the queries listed below to understand how it works:

1. mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Pantaloons', 38), (2, 'Cantabil', 40);

2.

3. mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Raymond', 38), (2, 'Cantabil', 40);

Output

In the below output, we can see that the first INSERT query executes correctly, but the second statement fails and gives an error that says: Duplicate entry 'Cantabil' for key BrandName.

CHECK Constraint

It controls the value in a particular column. It ensures that the inserted value in a column must be satisfied with the given condition. In other words, it determines whether the value associated with the column is valid or not with the given condition.

Before the version 8.0.16, MySQL uses the limited version of this constraint syntax, as given below:

1. CHECK (expr)

After the version 8.0.16, MySQL uses the CHECK constraints for all storage engines i.e., table constraint and column constraint, as given below:

1. [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

Let us understand how a CHECK constraint works in MySQL. For example, the following statement creates a table "Persons" that contains CHECK constraint on the "Age" column. The CHECK constraint ensures that the inserted value in a column must be satisfied with the given condition means the Age of a person should be greater than or equal to 18:

1. mysql> CREATE TABLE Persons (

2. ID int NOT NULL,

3. Name varchar(45) NOT NULL,

4. Age int CHECK (Age>=18)

5. );

Execute the listed queries to insert the values into the table:

1. mysql> INSERT INTO Persons(Id, Name, Age)

2. VALUES (1,'Robert', 28), (2, 'Joseph', 35), (3, 'Peter', 40);

3.

4. mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Robert', 15);

Output

In the below output, we can see that the first INSERT query executes successfully, but the second statement fails and gives an error that says: CHECK constraint is violated for key Age.

DEFAULT Constraint

This constraint is used to set the default value for the particular column where we have not specified any value. It means the column must contain a value, including NULL.

For example, the following statement creates a table "Persons" that contains DEFAULT constraint on the "City" column. If we have not specified any value to the City column, it inserts the default value:

1. mysql> CREATE TABLE Persons (

2. ID int NOT NULL,

3. Name varchar(45) NOT NULL,

4. Age int,

5. City varchar(25) DEFAULT 'New York'

6. );

Execute the listed queries to insert the values into the table:

1. mysql> INSERT INTO Persons(Id, Name, Age, City)

2. VALUES (1,'Robert', 15, 'Florida'),

3. (2, 'Joseph', 35, 'California'),

4. (3, 'Peter', 40, 'Alaska');

5.

6. mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Brayan', 15);

Output

In the below output, we can see that the first insert query that contains all fields executes successfully, while the second insert statement does not contain the "City" column but also executed successfully. It is because it has a default value.

Now, executes the following statement to validate the default value for the 4th column:

1. mysql> SELECT * FROM Persons;

We can see that it works perfectly. It means default value "New York" stored automatically in the City column.

PRIMARY KEY Constraint

This constraint is used to identify each record in a table uniquely. If the column contains primary key constraints, then it cannot be null or empty. A table may have duplicate columns, but it can contain only one primary key. It always contains unique value into a column.

The following statement creates a table "Person" and explains the use of this primary key more clearly:

1. CREATE TABLE Persons (

2. ID int NOT NULL PRIMARY KEY,

3. Name varchar(45) NOT NULL,

4. Age int,

5. City varchar(25));

Next, use the insert query to store data into a table:

1. INSERT INTO Persons(Id, Name, Age, City)

2. VALUES (1,'Robert', 15, 'Florida') ,

3. (2, 'Joseph', 35, 'California'),

4. (3, 'Peter', 40, 'Alaska');

5.

6. INSERT INTO Persons(Id, Name, Age, City)

7. VALUES (1,'Stephen', 15, 'Florida');

Output

In the below output, we can see that the first insert query executes successfully. While the second insert statement fails and gives an error that says: Duplicate entry for the primary key column.

AUTO_INCREMENT Constraint

This constraint automatically generates a unique number whenever we insert a new record into the table. Generally, we use this constraint for the primary key field in a table.

We can understand it with the following example where the id column going to be auto-incremented in the Animal table:

1. mysql> CREATE TABLE Animals(

2. id int NOT NULL AUTO_INCREMENT,

3. name CHAR(30) NOT NULL,

4. PRIMARY KEY (id));

Next, we need to insert the values into the "Animals" table:

1. mysql> INSERT INTO Animals (name) VALUES

2. ('Tiger'),('Dog'),('Penguin'),

3. ('Camel'),('Cat'),('Ostrich');

Now, execute the below statement to get the table data:

1. mysql> SELECT * FROM Animals;

Output

In the output, we can see that I have not specified any value for the auto-increment column, so MySQL automatically generates a unique number in the sequence order for this field.

ENUM Constraint

The ENUM data type in MySQL is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, which means that each column may have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to represent string values.

The following illustration creates a table named "shirts" that contains three columns: id, name, and size. The column name "size" uses the ENUM data type that contains small, medium, large, and x-large sizes.

1. mysql> CREATE TABLE Shirts (

2. id INT PRIMARY KEY AUTO_INCREMENT,

3. name VARCHAR(35),

4. size ENUM('small', 'medium', 'large', 'x-large')

5. );

Next, we need to insert the values into the "Shirts" table using the below statements:

1. mysql> INSERT INTO Shirts(id, name, size)

2. VALUES (1,'t-shirt', 'medium'),

3. (2, 'casual-shirt', 'small'),

4. (3, 'formal-shirt', 'large');

Now, execute the SELECT statement to see the inserted values into the table:

1. mysql> SELECT * FROM Shirts;

Output

We will get the following output:

INDEX Constraint

This constraint allows us to create and retrieve values from the table very quickly and easily. An index can be created using one or more than one column. It assigns a ROWID for each row in that way they were inserted into the table.

The following illustration creates a table named "shirts" that contains three columns: id, name, and size.

1. mysql> CREATE TABLE Shirts (

2. id INT PRIMARY KEY AUTO_INCREMENT,

3. name VARCHAR(35),

4. size ENUM('small', 'medium', 'large', 'x-large')

5. );

Next, we need to insert the values into the "Shirts" table using the below statements:

1. mysql> INSERT INTO Shirts(id, name, size)

2. VALUES (1,'t-shirt', 'medium'),

3. (2, 'casual-shirt', 'small'),

4. (3, 'formal-shirt', 'large');

Now, execute this statement for creating index:

1. mysql> CREATE INDEX idx_name ON Shirts(name);

We can use the query below to retrieve the data using the index column:

1. mysql> SELECT * FROM Shirts USE INDEX(idx_name);

Output

The following output appears:

Foreign Key Constraint

This constraint is used to link two tables together. It is also known as the referencing key. A foreign key column matches the primary key field of another table. It means a foreign key field in one table refers to the primary key field of another table.

Let us consider the structure of these tables: Persons and Orders.

Table: Persons

1. CREATE TABLE Persons (

2. Person_ID int NOT NULL PRIMARY KEY,

3. Name varchar(45) NOT NULL,

4. Age int,

5. City varchar(25)

6. );

Table: Orders

1. CREATE TABLE Orders (

2. Order_ID int NOT NULL PRIMARY KEY,

3. Order_Num int NOT NULL,

4. Person_ID int,

5. FOREIGN KEY (Person_ID) REFERENCES Persons(Person_ID)

6. );

In the above table structures, we can see that the "Person_ID" field in the "Orders" table points to the "Person_ID" field in the "Persons" table. The "Person_ID" is the PRIMARY KEY in the "Persons" table, while the "Person_ID" column of the "Orders" table is a FOREIGN KEY.

Output

Our table contains the following data:

 

MySQL CREATE USER Example

select user from mysql.user;

create user peter@localhost identified by 'jtp12345';

CREATE USER IF NOT EXISTS adam@localhost IDENTIFIED BY 'jtp123456';

Grant Privileges to the MySQL New User

MySQL server provides multiple types of privileges to a new user account. Some of the most commonly used privileges are given below:

1. ALL PRIVILEGES: It permits all privileges to a new user account.

2. CREATE: It enables the user account to create databases and tables.

3. DROP: It enables the user account to drop databases and tables.

4. DELETE: It enables the user account to delete rows from a specific table.

5. INSERT: It enables the user account to insert rows into a specific table.

6. SELECT: It enables the user account to read a database.

7. UPDATE: It enables the user account to update table rows.

GRANT ALL PRIVILEGES ON * . * TO peter@localhost;

GRANT CREATE, SELECT, INSERT ON * . * TO peter@localhost;

FLUSH PRIVILEGES;

SHOW GRANTS for username;

 

MySQL CREATE INDEX Statement

Need for Indexing in MySQL

Suppose we have a contact book that contains names and mobile numbers of the user. In this contact book, we want to find the mobile number of Martin Williamson. If the contact book is an unordered format means the name of the contact book is not sorted alphabetically, we need to go over all pages and read every name until we will not find the desired name that we are looking for. This type of searching name is known as sequential searching.

To find the name and contact of the user from table contactbooks, generally, we used to execute the following query:

mysql> SELECT mobile_number FROM contactbooks WHERE first_name = 'Martin' AND last_name = 'Taybu';

This query is very simple and easy. Although it finds the phone number and name of the user fast, the database searches entire rows of the table until it will not find the rows that you want. Assume, the contactbooks table contains millions of rows, then, without an index, the data retrieval takes a lot of time to find the result. In that case, the database indexing plays an important role in returning the desired result and improves the overall performance of the query.

mysql> CREATE TABLE t_index(

   col1 INT PRIMARY KEY,

   col2 INT NOT NULL,

   col3 INT NOT NULL,

   col4 VARCHAR(20),

   INDEX (col2,col3)

);

CREATE INDEX ind_1 ON t_index(col4);

SHOW INDEXES FROM t_index;

DROP INDEX class ON t_index;