MySQL – DBMS Keys Examples

Posted on May 22, 2021 in Feature Questions

A key is an attribute or set of an attribute which helps you to identify a row(tuple) in a relation(table). They allow you to find the relation between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table.

Why we need a Key?

Here, are reasons for using Keys in the DBMS system.

  • Keys help you to identify any row of data in a table. In a real-world application, a table could contain thousands of records. Moreover, the records could be duplicated. Keys ensure that you can uniquely identify a table record despite these challenges.
  • Allows you to establish a relationship between and identify the relation between tables
  • Help you to enforce identity and integrity in the relationship. Keys are used to enforce referential integrity in your database.

Various Keys in Database Management System

DBMS has following seven types of Keys each have their different functionality:

  • Super Key
  • Primary Key
  • Candidate Key
  • Alternate Key
  • Foreign Key
  • Compound Key
  • Composite Key
  • Surrogate Key
  • Unique Key

Super key

SUPER KEY is a group of single or multiple keys which identifies rows in a table. A Super key may have additional attributes that are not needed for unique identification.

Primary Key

PRIMARY KEY is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key can’t be a duplicate meaning the same value can’t appear more than once in the table. A table cannot have more than one primary key.

Rules for defining Primary key:

Two rows can’t have the same primary key value

  • It must for every row to have a primary key value.
  • The primary key field cannot be null.
  • The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.

Example : 

StudID Roll No First Name LastName Mobile No.
1 11 Tom Price 9999999999
2 12 Nick Wright 8787878787
3 13 Dana Natan 7474747474

In the above-given example, StudID is a primary key because it uniquely identifies an employee record. In this table, no other employee can have the same employee ID.

Alternate key

ALTERNATE KEYS is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key.

Example:In this table, StudID, Roll No, Mobile No are qualified to become a primary key. But since StudID is the primary key, Roll No, Mobile No becomes the alternative key.

 

StudID Roll No First Name LastName Mobile No.
1 11 Tom Price 9999999999
2 12 Nick Wright 8787878787
3 13 Dana Natan 7474747474

Foreign key

FOREIGN KEY is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table.

This concept is also known as Referential Integrity.

Example: Refer following two table .

Student Info

StudID Roll No First Name LastName Mobile No.
1 11 Tom Price 9999999999
2 12 Nick Wright 8787878787
3 13 Dana Natan 7474747474

Receipt Info : In this table, adding the foreign key in StudID to the Receipt Info, we can create a relationship between the two tables (Between Student Info and Receipt Info)

ReceiptID StudID Amount
1001 1 100
1002 2 200
1003 3 450

Candidate Key

CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.

Properties of Candidate key:

  • It must contain unique values
  • Candidate key may have multiple attributes
  • Must not contain null values
  • It should contain minimum fields to ensure uniqueness
  • Uniquely identify each record in a table

Example: In the given table Stud ID, Roll No, and Mobile No are candidate keys which help us to uniquely identify the student record in the table.

StudID Roll No First Name LastName Mobile No.
1 11 Tom Price 9999999999
2 12 Nick Wright 8787878787
3 13 Dana Natan 7474747474

Candidate Key

Compound key

COMPOUND KEY has two or more attributes that allow you to uniquely recognize a specific record. It is possible that each column may not be unique by itself within the database. However, when combined with the other column or columns the combination of composite keys become unique. The purpose of the compound key in database is to uniquely identify each record in the table.

Example: In this example, OrderNo and ProductID can’t be a primary key as it does not uniquely identify a record. However, a compound key of Order ID and Product ID could be used as it uniquely identified each record.

OrderNo PorductID Product Name Quantity
B005 JAP102459 Mouse 5
B005 DKT321573 USB 10
B005 OMG446789 LCD Monitor 20
B004 DKT321573 USB 15
B002 OMG446789 Laser Printer 3

Composite key

COMPOSITE KEY is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table.

The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key.

Surrogate key

SURROGATE KEYS is An artificial key which aims to uniquely identify each record is called a surrogate key. This kind of partial key in dbms is unique because it is created when you don’t have any natural primary key. They do not lend any meaning to the data in the table. Surrogate key is usually an integer. A surrogate key is a value generated right before the record is inserted into a table.

 

Fname Lastname Start Time End Time
Anne Smith 09:00 18:00
Jack Francis 08:00 17:00
Anna McLean 11:00 20:00
Shown Willam 14:00 23:00

Above, given example, shown shift timings of the different employee. In this example, a surrogate key is needed to uniquely identify each employee.

Surrogate keys in sql are allowed when

  • No property has the parameter of the primary key.
  • In the table when the primary key is too big or complicated.

Unique Key

UNIQUE KEY in MySQL is a single field or combination of fields that ensure all values going to store into the column will be unique. It means a column cannot stores duplicate values. For example, the email addresses and roll numbers of students in the “student_info” table or contact number of employees in the “Employee” table should be unique.

MySQL allows us to use more than one column with UNIQUE constraint in a table. It can accept a null value, but MySQL allowed only one null value per column. It ensures the integrity of the column or group of columns to store different values into a table.

Needs of Unique Key

  • It is useful in preventing the two records from storing identical values into the column.
  • It stores only distinct values that maintain the integrity and reliability of the database for accessing the information in an organized way.
  • It also works with a foreign key in preserving the uniqueness of a table.
  • It can contain null value into the table.

Difference Between Primary key & Unique key

Primary Key Unique Key
Unique identifier for rows of a table Unique identifier for rows of a table when primary key is not present
Cannot be NULL Can be NULL
Only one primary key can be present in a table Multiple Unique Keys can be present in a table
present in a table present in a table
Selection using primary key creates clustered index Selection using unique key creates non-clustered index

Summary

  • DBMS keys allow you to establish a relationship between and identify the relation between tables
  • Seven Types of DBMS keys are Super, Primary, Candidate, Alternate, Foreign, Compound, Composite, and Surrogate Key.
  • A super key is a group of single or multiple keys which identifies rows in a table.
  • A column or group of columns in a table which helps us to uniquely identifies every row in that table is called a primary key
  • All the keys which are not primary key are called an alternate key
  • A super key with no repeated attribute is called candidate key
  • A compound key is a key which has many fields which allow you to uniquely recognize a specific record
  • A key which has multiple attributes to uniquely identify rows in a table is called a composite key
  • An artificial key which aims to uniquely identify each record is called a surrogate key
  • Primary Key never accept null values while a foreign key may accept multiple null values.

//Ex:
CREATE TABLE Tbl_Employee (
    EmployeeID int,
    LastName varchar(255),
    FirstName varchar(255),
    Gender varchar(255),
    City varchar(255), 
    PRIMARY KRY(EmployeeID)
);

INSERT INTO Tbl_Employee VALUES (1,'Smith','James','Male','Mumbai');
INSERT INTO Tbl_Employee VALUES (2,'Johnson','Maria','Female','New York');
INSERT INTO Tbl_Employee VALUES (3,'Miller','David','Male','London');
INSERT INTO Tbl_Employee VALUES (4,'Wilson','Maria','Female','Paris');
INSERT INTO Tbl_Employee VALUES (5,'Thomas','Mary','Female','Singapore');
INSERT INTO Tbl_Employee VALUES (6,'Martin','Steven','Male','Bangkok');
INSERT INTO Tbl_Employee VALUES (7,'Lee','Karen','Female','Dubai');

//SQL to show keys
SHOW KEYS FROM Tbl_Employee WHERE Key_name = 'PRIMARY'

MySQL – Database Optimization

Posted on May 1, 2020 in Feature Questions

Database Optimization

Database optimization refers to a variety of strategies for reducing database system response time. In this guide, We will take you through the steps of optimizing SQL queries and databases.

#1: Index All Columns Used in ‘WHERE’, ‘ORDER BY’ and ‘GROUP BY’ Clauses

Apart from guaranteeing uniquely identifiable records, an index allows MySQL server to fetch results faster from a database. An index is also very useful when it comes to sorting records.

MySQL indexes may take up more space and decrease performance on inserts, deletes and updates. However, if your table has more than 10 rows, they can considerably reduce select query execution time.

#2: Optimize Like Statements with Union Clause

Sometimes, you may want to run queries using the comparison operator ‘OR‘ on different fields or columns in a particular table. When the ‘OR‘ keyword is used too much in WHERE clause, it might make the MySQL optimizer to incorrectly choose a full table scan to retrieve a record.

A UNION clause can make the query run faster especially if you have an index that can optimize one side of the query and a different index to optimize the other side.

#3: Avoid Like Expressions with Leading Wildcards

MySQL is not able to utilize indexes when there is a leading wildcard in a query. If we take example on the students table, a search like this will cause MySQL to perform full table scan even if you have indexed the ‘first_name’ field on the students table.

#4: Take Advantage of MySQL Full-text Searches

If you are faced with a situation where you need to search data using wildcards and you don’t want your database to under perform, you should consider using MySQL full-text search (FTS) because it is far much faster than queries using wildcard characters.
Furthermore, Full Text Search can also bring better and relevant results when you are searching a huge database.

#5: MySQL Query Caching

If your website or application performs a lot of select queries (e.g. WordPress), you should take advantage of MySQL query caching feature. This will speed up performance when read operations are conducted.

#6: Avoid unnecessary columns in SELECT clause

Instead of using ‘SELECT *’, always specify columns in the SELECT clause to improve MySQL performance. Because unnecessary columns cause additional load on the database, slowing down its performance as well whole systematic process.

#7: Use INNER JOIN, instead of OUTER JOIN if possible

Use OUTER JOIN only when it is necessary. Using it needlessly not only limits database performance but also limits MySQL query optimization options, resulting in slower execution of SQL statements.

#8: Use DISTINCT and UNION only if it is necessary

Using UNION and DISTINCT operators without any major purpose causes unwanted sorting and slowing down of SQL execution. Instead of UNION, using UNION ALL brings more efficiency in the process and improves MySQL performance more precisely.

#9 : Use DISTINCT instead of GROUP BY when appropriate

If one of them is faster, it’s going to be DISTINCT. This is because, although the two are the same, a query optimizer would have to catch the fact that your GROUP BY is not taking advantage of any group members, just their keys. DISTINCT makes this explicit, so you can get away with a slightly dumber optimizer.

#10: Don’t Use MySQL as a Queue

Queues can really affect your database performance right from the core and could enter in your app databases without your knowledge. For instance, if you are setting up a status for particular item so that a ‘relevant process’ can access it, you are unintentionally creating a queue. What it actually does is that it builds up extra load time to access the resource without any major reason.

#11 : Avoid Unused Tables

No matter how carefully you plan the initial approach to the database, as time goes by, you inevitably end up with unused tables. Just do not hesitate: delete them! Having unused tables is quite similar to keeping things you don’t use in a full drawer. When you go looking for something, it can take you much longer to find it! The same thing happens in the databases: the systems have to track all the tables and elements until they find the answer to the query.

#12 : Use Optimal Data Types

MySQL supports different data types including integer, float, double, date, date_time, Varchar, and text, among others. When designing your tables, you should know that “shorter is always better.”

For instances, if you are designing a system user’s table which will hold less than 100 users, you should use ‘TINYINT’ data type for the ‘user_id’ field because it will accommodate all your values from -128 to 128.

Also, if a field expects a date value (e.g. sales_order_date), using a DATE_TIME data type will be ideal because you don’t have to run complicated functions to convert the field to date when retrieving records using SQL.

Use integer values if you expect all values to be numbers (e.g. in a student_id or a payment_id field). Remember, when it comes to computation, MySQL can do better with integer values as compared to text data types such as VARCHAR.

#13 : Avoid Null Values

Null is the absence of any value in a column. You should avoid this kind of values whenever possible because they can harm your database results. For instance, if you want to get the sum of all orders in a database but a particular order record has a null amount, the expected result might misbehave unless you use MySQL ‘ifnull‘ statement to return alternative value if a record is null.

In some cases, you might need to define a default value for a field if records don’t have to include a mandatory value for that particular column/field.

#14 : Avoid Too Many Columns

Wide tables can be extremely expensive and require more CPU time to process. If possible, don’t go above a hundred unless your business logic specifically calls for this.

Instead of creating one wide table, consider splitting it apart in to logical structures. For instance, if you are creating a customer table but you realize a customer can have multiple addresses, it is better to create a separate table for holding customers addresses that refer back to the customers table using the ‘customer_id’ field.

#15 : Optimize Joins

Always include fewer tables in your join statements. An SQL statement with poorly designed pattern that involves a lot of joins may not work well.

Conclusion

In this guide, we have shown you how to optimize your MySQL server for speed and performance.

This article provides in detail, the account of the best practices for database optimization and handy MySQL performance tuning tips every developer must know. It’s a complete guide for those backend devs, who are uncertain about their poor database performance and need some handy techniques to optimize MySQL database from the core.

We believe that the guide will allow you to craft better queries and have a well-structured database structure that will not only be simple to maintain but also offer more stability to your software applications or website.

Thats all about Database Optimization. 🙂

Tags: , , ,

Mysql – DDL, DML, DCL and TCL

Posted on April 21, 2020 in Feature Questions

MySQL – DDL, DML, DCL and TCL Commands

DDL:

DDL is stands for DATA DEFINITION LANGUAGE, which deals with database schemas and descriptions, of how the data should reside in the database.

  • CREATE – To create a database and its objects like (table, index, views, store procedure, function, and triggers) 
    //Syntax
CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
    ....
 );

//Ex:
CREATE TABLE Tbl_Employee (
EmployeeID int,
LastName varchar(255),
FirstName varchar(255),
Gender varchar(255),
City varchar(255) 
);

Try It Now
  • ALTER – Alters the structure of the existing database. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. 
    //Syntax
ALTER TABLE table_name ADD column_name COLUMN DEFINITION; 
//to add new column
ALTER TABLE table_name MODIFY(COLUMN DEFINITION....); 
//to modify existing coulmn

//Ex:
ALTER TABLE Tbl_Employee ADD Email varchar(255);
ALTER TABLE Tbl_Employee MODIFY Email varchar(50);

Try It Now
  • DROP – Delete objects from the database. It is used to delete both the structure and record stored in the table. 
    //Syntax:
DROP TABLE table_name;

//Ex:
DROP TABLE Tbl_Employee;
  • TRUNCATE – Remove all records from a table, including all spaces allocated for the records are removed. In this case primary key will get reset to 1. 
    //Syntax:
TRUNCATE TABLE table_name;

//Ex:
TRUNCATE TABLE Tbl_Employee;
  • RENAME – Rename an object. It might be Table name 
    //Syntax:
RENAME tablename to newtablename;

//Ex: RENAME Tbl_Employee TO Tbl_Employee_New; 
//rename table name

DML:

DML is stands for DATA MANIPULATION LANGUAGE which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc. and it is used to store, modify, retrieve, delete and update data in a database.

  • SELECT– Retrieve data from a database. 
    //Syntax:
SELECT column1, column2, ...
FROM table_name;

//Ex:
SELECT * FROM Tbl_Employee;

Try It Now
  • INSERT– Insert data into a table. 
    //Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
OR
INSERT INTO table_name
VALUES (value1, value2, value3, ...); 

//Ex:
INSERT INTO Tbl_Employee 
VALUES (1,'Smith','James','Male','Mumbai');

Try It Now
  • UPDATE– Updates existing data within a table. 
    //Syntax:
UPDATE table_name
  SET column1 = value1, column2 = value2, ...
  WHERE condition;

//Ex:
UPDATE Tbl_Employee SET City = 'Delhi' 
WHERE EmployeeID = 1;

Try It Now
  • DELETE– The DELETE statement is used to delete existing records in a table. 
    //Syntax:
DELETE FROM table_name WHERE condition;

//Ex:
UPDATE FROM Tbl_Employee WHERE EmployeeID = 1;

Try It Now

DCL:

DCL is stands for DATA CONTROL LANGUAGE which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.

  • GRANT – Allow users access privileges to the database. 
    //Syntax:
GRANT SELECT, UPDATE ON MY_TABLE 
TO SOME_USER, ANOTHER_USER;
  • REVOKE – Withdraw users access privileges given by using the GRANT command. 
    //Syntax:
REVOKE SELECT, UPDATE ON MY_TABLE 
FROM SOME_USER, ANOTHER_USER;

TCL:

TCL is stands for TRANSACTION CONTROL LANGUAGE which deals with a transaction within a database.

  • COMMIT – Commits a Transaction.When a successful transaction is completed, the COMMIT command should be issued so that the changes to all involved tables will take effect. 
    //Syntax:
COMMIT; //Execute after any mysql execution

//Ex:
DELETE FROM Tbl_Employee  WHERE EmployeeID = 1; 
COMMIT; ##transaction has been complete

Try It Now
  • ROLLBACK – Rollback a transaction in case of any error occurs. 
    //Syntax:
ROLLBACK; //Execute after any mysql execution 

//Ex: 
DELETE FROM Tbl_Employee WHERE EmployeeID = 1;
COMMIT; ##rollback previous transaction

Try It Now
  • SAVEPOINT – To rollback the transaction making points within groups. 
    //Syntax:
Mysql query statment1;
SAVEPOINT tran1; //Belongs to statment1

Mysql query statment2;
SAVEPOINT tran2; //Belongs to statment2

ROLLBACK TO tran1; 
//only output of statement1 will remain 
and output of statemnt2 will rollback.

//Ex:
DELETE FROM Tbl_Employee  WHERE EmployeeID = 1; 
SAVEPOINT tran1;

DELETE FROM Tbl_Employee  WHERE EmployeeID = 2; 
SAVEPOINT tran2;

ROLLBACK TO tran1; ##roll back trans1

Try It Now
  • SET TRANSACTION – Specify characteristics of the transaction.
Tags: , , , ,

MySQL indexes

Posted on April 2, 2020 in Feature Questions

What is MySQL indexes ?

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookup and efficient ordering of access to records. An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.

The index can also be a UNIQUE index, which means that you cannot have duplicate values in that column, or a PRIMARY KEY which in some storage engines defines where in the database file the value is stored.

Create Index Syntax:
Create index on column and allow duplicate values.
CREATE INDEX id_index ON table_name(column_name);

//Example
CREATE INDEX id_index ON Tbl_Employee(EmployeeID);
Try It Now
Create Index Syntax:
Create unique index which can't allow duplicate values.
CREATE INDEX UNIQUE INDEX ON table_name(column_name);

//Example
CREATE UNIQUE INDEX city_index ON Tbl_Employee(City);
Try It Now
ALTER command to ADD and DROP INDEX

ALTER ADD INDEX
ALTER TABLE table_name ADD INDEX(column_name);
//Example
ALTER TABLE Tbl_Employee ADD INDEX(City);

ALTER DROP INDEX
ALTER TABLE table_name DROP INDEX(column_name);
//Example
ALTER TABLE Tbl_Employee DROP INDEX(City);

Try It Now
Show Index
Syntax:
SHOW INDEXES FROM table_name;
//Example
SHOW INDEXES FROM Tbl_Employee ;
Try It Now

How does MySQL indexes work?

Database table does not reorder itself every time the query conditions change in order to optimize the query performance: that would be unrealistic. In actuality, what happens is the index causes the database to create a data structure. The data structure type is very likely a B-Tree. Advantages of the B-Tree is that it is sortable. When the data structure is sorted in order it makes our search more efficient.

Test  Employee Table:

EmployeeID LastName FirstName Gender City
1 Smith James Male Mumbai
2 Johnson Maria Female New York
3 Miller David Male Mumbai
4 Wilson Maria Female Mumbai
5 Thomas Mary Female New York
6 Martin Steven Male New York
7 Lee Karen Female Mumbai

From above table, We have to search Employee fitstname which have city Mumbai then We write query.
SELECT FirstName FROM Tbl_Employee WHERE city= ‘Mumbai’;

Without indexing

In order to non indexing, database search for City = ‘Mumbai’ in all rows of table and return the result. It is time consuming for large data table.

When we apply indexing then index table look like:

EmployeeID LastName FirstName Gender City
1 Smith James Male Mumbai
3 Miller David Male Mumbai
4 Wilson Maria Female Mumbai
7 Lee Karen Female Mumbai
2 Johnson Maria Female New York
5 Thomas Mary Female New York
6 Martin Steven Male New York

In indexing, the query search only the rows in the column City that have Mumbai and then using the pointer that can go into the table to find the specific row where that pointer lives. The query can then go into the table to retrieve the fields for the columns requested for the rows that meet the conditions.

If the search were presented visually, it would look like this:

Index to non index relation

Tags: , ,

Mysql – utf-8, utf-16 and utf-32

Posted on January 27, 2020 in Feature Questions

Character encoding

Character encoding is an important concept in process of converting byte streams into characters, which can be displayed. To convert bytes in to characters mainly there are two things, a character set and an encoding.

Character set
A character set is nothing but list of characters, where each symbol or character is mapped to a numeric value, also known as code points. Since there are so many characters and symbols in the world, a character set is required to support all those characters.

Encoding Schemes
Encoding schemes describe how code points are mapped to bytes, using different bit values as a basis. E.g. 8 bits for UTF-8, 16 bits for UTF-16 and 32 bits for UTF-32 UTF stands for Unicode Transformation, which defines an algorithm to map every Unicode code point to a unique byte sequence.

Difference between UTF-8, UTF-16 and UTF-32

UTF-8 UTF-16 UTF-32
Variable length encoding Variable length encoding Fixed width encoding scheme
Uses one byte at the minimum for encoding and 4 bytes maximum Uses minimum two bytes and 4 bytes maximum Uses fixed 4 bytes for encoding
It is variable length encoding and takes 1 to 4 bytes, dependingupon code point It is also variable length character encoding but either takes 2 or 4 bytes. Fixed 4 bytes
Compatible with ASCII Incompatible with ASCII Incompatible with ASCII

Summary:

  1. In UTF-8, every code point from 0-127 is stored in single bytes. Only code points 128 and above are stored using 2,3 or in fact, up to 4 bytes. In short, UTF-8 is variable length encoding and takes 1 to 4 bytes, depending upon code point. UTF-16 is also variable length character encoding but either takes 2 or 4 bytes. On the other hand UTF-32 is fixed 4 bytes.
  2. UTF-8 has an advantage where ASCII are most used characters, in that case most characters only need one byte. UTF-8 file containing only ASCII characters has the same encoding as an ASCII file, which means English text looks exactly the same in UTF-8 as it did in ASCII. Given dominance of ASCII in past this was the main reason of initial acceptance of Unicode and UTF-8.
  3. UTF16 is not fixed width. It uses 2 or 4 bytes. Only UTF32 is fixedwidth and unfortunately no one uses it. Also, worth knowing is that Java Strings are represented using UTF-16 bit characters, earlier they use USC2, which is fixed width.
  4. You might think that because UTF-8 take less bytes for many characters it would take less memory that UTF-16, well that really depends on what language the string is in. For non-European languages, UTF-8 requires more memory than UTF-16.
  5. ASCII is strictly faster than multi-byte encoding scheme because less data to process faster.
  6. UTF-32 will cover all possible characters in 4 bytes. This makes it pretty bloated. I can’t think of any advantage to using it.

 

Tags: , ,

Mysql – Views

Posted on March 21, 2019 in Feature Questions

Mysql database view is known as a “virtual table”. virtual table created by a query by joining one or more tables. By Virtual, we mean, the tables do not store any data of their own but display data of other tables. In other simple words, VIEWS are nothing but SELECT Queries.

Create View Syntax:

CREATE VIEW[OR REPLACE] my_view_name AS
SELECT columnname1, columnname2, ...
FROM table_name
WHERE condition;

Example

Sample example of create and call view 
Ex : CREATE VIEW employee_view AS
SELECT EmployeeID, LastName, FirstName
FROM Tbl_Employee
WHERE Gender = "Female";
Try It Now

Views are nothing but SELECT Queries.

CREATE VIEW employee_view AS
SELECT EmployeeID, LastName, FirstName
FROM Tbl_Employee
WHERE Gender = "Female";

SAME AS

SELECT * FROM (
 SELECT EmployeeID, LastName, FirstName
 FROM Tbl_Employee
 WHERE Gender = "Female"
) x;

Drop view using-

DROP VIEW IF EXISTS <view_name>;

How to display the contents of a view?

SHOW CREATE TRIGGER <trigger_name>; 
Ex : SHOW CREATE TRIGGER after_insert_trigger; 
Try It Now

Show list of views in Database-

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
Try It Now

Advantages of views:

  • View the data without storing the data into the object.
  • You can restrict users to the view instead of the underlying table(s), thereby enhancing security.
  • Join two or more tables and show it as one object to user.
  • Restict the access of a table so that nobody can insert the rows into the table.
  • Simply If we are having complex queries with joins etc you can make and put in view and use that view where need of reusing it.
Tags: ,

Triggers

Posted on February 24, 2019 in Feature Questions

What Is MySQL Triggers?

The MySQL trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE and it can be invoked before or after the event.

Definition –
A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.
A trigger is defined to activate when a statement inserts, updates, or deletes rows in the associated table. These row operations are trigger events. For example, rows can be inserted by INSERT or LOAD DATA statements, and an insert trigger activates for each inserted row. A trigger can be set to activate either before or after the trigger event. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated.

Uses for Triggers

  • Complex Auditing
  • Enforce Business Rules
  • Derive Column Values
  • Triggers Are Tricky!
  • automatically generate derived column values
  • prevent invalid transactions
  • enforce complex security authorizations
  • enforce referential integrity across nodes in a distributed database
  • enforce complex business rules
  • provide transparent event logging
  • provide sophisticated auditing
    Types of Triggers

    • DML Triggers
    • DDL Triggers

Logon Triggers

DML Triggers

DML stands for Data Manipulation Language. INSERT, UPDATE, and DELETE statements are DML statements. DML triggers get fired whenever data is modified using INSERT, UPDATE, and DELETE events.

DML triggers can be again classified into 2 types.
After triggers (Sometimes called FOR triggers)

  1. Instead of triggers
  2. After triggers

After triggers get fired after only with a condition when a modification action occurs. The INSERT, UPDATE and DELETE commands because of an after trigger gets fired after the execution of a complete statement.

Create Trigger Syntax:

CREATE TRIGGER trigger_name trigger_time trigger_event

ON table_name

FOR EACH ROW

BEGIN

...

END;
Sample example of create and call trigger
Try It Now

Drop trigger using-

DROP TRIGGER IF EXISTS <trigger_name>;

Show trigger definition-

SHOW CREATE TRIGGER <trigger_name>; 
Ex : SHOW CREATE TRIGGER after_insert_trigger; 
Try It Now

Show list of Triggers-

SHOW TRIGGERS; 

Try It Now
Tags: ,

Stored Procedure

Posted on February 25, 2018 in Feature Questions

What Is MySQL Stored Procedure?

A stored procedure is a segment of declarative SQL statements stored inside the database catalog. A stored procedure can be invoked by triggers, other stored procedures, and applications such as Java, Python, PHP..
In another word Stored procedure is set of SQL code which is stored in the database server and can be invoked by program or trigger or stored procedure itself. Stored procedure is a way to execute many tasks on your database server directly.

Create Procedure Syntax:

Syntax : 
CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]
BEGIN
   declaration_section
   executable_section
END;

Ex : 
CREATE PROCEDURE GetAllEmployee()
 BEGIN
 SELECT * FROM Tbl_Employee; 
 END 
 ;

How to call Procedure?

CALL <name>;
Ex : CALL GetAllEmployee();
Sample example of create and call procedure
Try It Now

Procedure Parameter-

  • CREATE PROCEDURE procedureName () : Parameter list is empty
  • CREATE PROCEDURE procedureName (IN varname DATA-TYPE) : One input parameter. The word IN is optional because parameters are IN (input) by default.
  • CREATE PROCEDURE procedureName (OUT varname DATA-TYPE) : One output parameter.
  • CREATE PROCEDURE procedureName (INOUT varname DATA-TYPE) : One parameter which is both input and output.

IN Parameter Example-

CREATE PROCEDURE `testProcedureInParameter` (IN var1 INT)
BEGIN
 SELECT var1 + 2 AS result;
END;
CALL testProcedureInParameter(10);
Try It Now

OUT Parameter Example-

CREATE PROCEDURE `testProcedureOutParameter` (OUT var1 VARCHAR(100))
BEGIN
 SET var1 = 'This Is a test OUT Parameter Procdure test';
END;
CALL testProcedureOutParameter(@var1);
Try It Now

INOUT Parameter Example-

SET @inoutvar = 10;
CREATE PROCEDURE `testProcedureINOutParameter` (INOUT inoutvar INT)
BEGIN
 SET inoutvar = inoutvar * 2;
END;
CALL testProcedureINOutParameter(@inoutvar);

SELECT @inoutvar;
Try It Now

Drop procedure using-

DROP PROCEDURE IF EXISTS <name>;
Ex : DROP PROCEDURE IF EXISTS GetAllEmployee;

Show procedure definition-

SHOW CREATE PROCEDURE <name>;
Ex : SHOW CREATE PROCEDURE GetAllEmployee;
Try It Now

Show list of procedure-

SHOW PROCEDURE STATUS;
Try It Now

 

Tags: ,

Difference between integer datatype

Posted on February 23, 2018 in Feature Questions

.

Type Length
in Bytes		 Minimum Value
(Signed)		 Maximum Value
(Signed)		 Minimum Value
(Unsigned)		 Maximum Value
(Unsigned)
TINYINT 1 -128 127 0 255
SMALLINT 2 -32768 32767 0 65535
MEDIUMINT 3 -8388608 8388607 to 0 16777215
INT 4 -2147483648 2147483647 0 4294967295
BIGINT 8 -9223372036854775808 92233720368
54775807		 0 184467440737
09551615		 
Try It Now

Signed variables, such as signed integers will allow you to represent numbers both in the positive and negative ranges.
Unsigned variables, such as unsigned integers, will only allow you to represent numbers in the positive.

ZEROFILL

INT(5) ZEROFILL with the stored value of 32 will show 00032
INT(5) with the stored value of 32 will show 32
INT with the stored value of 32 will show 32

 

Tags: ,

What is difference between WHERE and HAVING clause in MySQL

Posted on February 21, 2018 in Feature Questions

Difference between WHERE and HAVING clause in MySQL

1) Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query.

2) WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in MySQL.

3) One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause.

4) When WHERE and HAVING clause are used together in a SELECT query with aggregate function, WHERE clause is applied first on individual rows and only rows which pass the condition is included for creating groups. Once group is created, HAVING clause is used to filter groups based upon condition specified.

That’s all on difference between WHERE and HAVING clause in MySQL.

Always remember key difference between WHERE and HAVING clause in SQL, if WHERE and HAVING clause is used together, first WHERE clause is applied to filter rows and only after grouping HAVING clause is applied.

Try It Now
Tags: , ,