MySQL

Life is 10% what happens to us and 90% how we react to it. If you don't build your dream, someone else will hire you to help them build theirs.

MySQL

MySQL is a relational database management system (RDBMS), and ships with no GUI tools to administer MySQL databases or manage data contained within the databases. Users may use the included command line tools or use MySQL “front-ends”, desktop software and web applications that create and manage MySQL databases, build database structures, back up data, inspect status, and work with data records. The official set of MySQL front-end tools, MySQL Workbench is actively developed by Oracle, and is freely available for use
MySQL is offered fewer than two different editions: the open source MySQL Community Server and the proprietary Enterprise Server. MySQL Enterprise Server is differentiated by a series of proprietary extensions which install as server plugins, but otherwise shares the version numbering system and is built from the same code base.

Storage Engine

MySql server provides various database engines to meet the organizations and individual requirement. User can select the required database engine to boost the database performance. Here are the various database engines provided my MySql server:

1. MyISAM
2. InnoDB
3. Merge
4. Memory or Heap
5. Archive
6. CSV
7. Federated

Each storage engine has its own advantages and disadvantages.

1. MyISAM

MyISAM extends the former ISAM storage engine. The MyISAM tables are optimized for compression a speed. MyISAM tables are also portable between platforms and OSes.

The size of MyISAM table can be up to 256TB, which is huge. In addition, MyISAM tables can be compressed into read-only tables to save space. At startup, MySQL checks MyISAM tables for corruption and even repair them in case of errors. The MyISAM tables are not transaction-safe.

2. InnoDB

The InnoDB tables fully support ACID-compliant and transactions. They are also very optimal for performance. InnoDB table supports foreign keys, commit, rollback, roll-and forward operations. The size of the InnoDB table can be up to 64TB. Like MyISAM, the InnoDB tables are portable between different platforms and OSes. MySQL also checks and repair InnoDB tables, if necessary, at startup.

3. Merge

A MERGE table is a virtual table that combines multiple MyISAM tables, which has similar structure, into one table. The MERGE storage engine is also known as the MRG_MyISAM engine. The MERGE table does not have its own indexes; it uses indexes of the component tables instead. Using MERGE table, you can speed up performance in joining multiple tables. MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations on the MERGE tables.

4. Memory or Heap

The memory tables are stored in memory and used hash indexes so that they are faster than MyISAM tables. The lifetime of the data of the memory tables depends on the up time of the database server. The memory storage engine is formerly known as HEAP.

5. Archive

The archive storage engine allows you to store a large number of records, which for archiving purpose, into a compressed format to save disk space. The archive storage engine compresses a record when it is inserted and decompress it using zlib library as it is read. The archive tables only allow INSERT and SELECT commands. The archive tables do not support indexes, so reading records requires a full table scanning.

6. CSV

The CSV storage engine stores data in comma-separated values file format. A CSV table brings a convenient way to migrate data into non-SQL applications such as spreadsheet software. CSV table does not support NULL data type and read operation requires a full table scan.

7. FEDERATED

The FEDERATED storage engine allows you to manage data from a remote MySQL server without using cluster or replication technology. The local federated table stores no data. When you query data from a local federated table, the data is pull automatically from the remote federated tables.

Database management package is used to manage database, users and their access. So the first step towards database management is to create a database.

Creating database

Following command is used to create database in mysql server:

CREATE DATABASE database_name;

You can also check the existence of database by using [IF NOT EXISTS] clause. Here is the full syntax: 

CREATE DATABASE [IF NOT EXISTS] database_name;

This command will create a database with the given name. To check all the databases in the database server, following command is used: 

Show databases;

By default MySql server create two additional databases:

1. information_schema
2. mysql

Selecting database to work

Before starting work on any database, one need to select the database. Use command is given to select the database. Here is the syntax:

USE databasename;

After the above command all the queries will be run on the selected database. Suppose we have a database; say Employees. Then the command will be:

USE Employees;
Removinf database

Removing database will completly delete the database from the Management server and disk as well. “DROP DATABASE” command is used to delete a database. Here is the syntax: 

DROP DATABASE databasename;

In our case, we need to give:

DROP DATABASE Employees;
Data Types

There are many columns in database table. Each one of the column is of different type. Following data types are supported by MySql database:

Numeric Data Types

You can find all SQL standard numeric types in MySQL including exact number data type and approximate numeric data types including integer, fixed-point and floating point. In addition, MySQL also supports BIT data type for storing bit field values. Numeric types can be signed or unsigned except the BIT type. Following are the numeric types in MySql:

Numeric Types Description
TINYINT A very small integer
SMALLINT A small integer
MEDIUMINT A medium-sized integer
INT A standard integer
BIGINT A large integer
DECIMAL A fixed-point number
FLOAT A single-precision floating-point number
DOUBLE A double-precision floating-point number
BIT A bit field
String Data Types

In MySQL, string can hold anything from plain text to binary data such as images and files. The following table shows you the string data types in MySQL:

String Types Description
CHAR A fixed-length non-binary (character) string
VARCHAR A variable-length non-binary string
BINARY A fixed-length binary string
VARBINARY A variable-length binary string
TINYBLOB A very small BLOB (binary large object)
BLOB A small BLOB
MEDIUMBLOB A medium-sized BLOB
LONGBLOB A large BLOB
TINYTEXT A very small non-binary string
TEXT A small non-binary string
MEDIUMTEXT A medium-sized non-binary string
LONGTEXT A large non-binary string
ENUM An enumeration; each column value may be assigned one enumeration member
SET A set; each column value may be assigned zero or more set members
Date and Time Data Types

MySQL provides types for date and time as well as a combination of date and time. In addition, MySQL also provides timestamp data type for tracking the changes of a row in a table. Here is the List:

Data Types Description
DATE A date value in ‘CCYY-MM-DD’ format
TIME A time value in ‘hh:mm:ss’ format
DATETIME A date and time value in ‘CCYY-MM-DD hh:mm:ss’ format
TIMESTAMP A timestamp value in ‘CCYY-MM-DD hh:mm:ss’ format
YEAR A year value in CCYY or YY format
Spatial Data Types

MySQL supports many spatial data types that contain various kinds of geometrical and geographical values as shown in the following table:

Data Types Description
GEOMETRY A spatial value of any type
POINT A point (a pair of X Y coordinates)
LINESTRING A curve (one or more POINT values)
POLYGON A polygon
GEOMETRYCOLLECTION A collection of GEOMETRY values
MULTILINESTRING A collection of LINESTRING values
MULTIPOINT A collection of POINT values
MULTIPOLYGON A collection of POLYGON values
Tables in MySql

Database consist of record in the form of tables. These tables represent the real life objects and related to each other. In order to create table in database, CREATE TABLE command is used. Here is the syntax:

CREATE TABLE tablename (column1 datatype, column2 datatype,....)
	engine=table_type;

For example, you need to create a table namly department. Table consist of dept_id, dept_name, manager and address columns. So here will be table definition:

CREATE TABLE Department (
		dept_id int(11) NOT NULL AUTO_INCREMENT,
		dept_name varchar(100) DEFAULT NULL, 
		manager varchar(100) DEFAULT NULL, 
		address varchar(250) DEFAULT NULL, 
		PRIMARY KEY (dept_id)
		)ENGINE=InnoDB;

Here “PRIMARY KEY” clause is used to define the primary key on the dept_id column. dept_id collumn is Not NULL and auto incremented. This means there will be a value in each row for this column. Rest columns are intilized with default value of NULL.

ALTER TABLE

Alter table modify the existing structure of the table. Column can be added/dropped by alter table command. Here is the syntax:

ALTER TABLE table_name action1[,action2,…]

For example to add a new column city in the existing Employee table, you can write:

ALTER TABLE Department 
	ADD COLUMN city varchar(100) NULL after address;

This will add the city column after the address column in the table.

To change the column attribute, you can use CHANGE keyword in alter command. For example:

ALTER TABLE Department CHANGE COLUMN city varchar(150);

This will change the city column size to 150 characters. Similarly you can drop the column using DROP keyword. for example to drop city column:

ALTER TABLE Department DROP COLUMN city;

MySql provide an easy way to rename a table column without changing its other attribute. For example you can change the city column to “domicile” by this command:

ALTER TABLE Department RENAME city to domicile;
DROP TABLE

Drop table command is used to remove table from database. Syntax for drop table is:

DROP TABLE Department;

MySql also provide a feature to remove all the tables which belongs to a pattern of similar name. This will help you if you forget the table name. LIKE keyword is used to pass the pattern. For example, you can remove the Department table by:

DROP TABLE LIKE ‘Emp%’;
Primary key

A primary key is a column or a set of columns that uniquely identifies each row in the table. Because MySQL works faster with integers, the primary key column’s type should be an integer type e.g., INT or BIGINT. You can choose a smaller integer type such as TINYINT, SMALLINT, etc. A primary key column often has AUTO_INCREMENT attribute that generates a unique sequence for the key automatically.

Defining Primary Key

Primary key can be defined at two levels. First at the time of table creation and second after the table creation.

To define the primary key at the time of table creation PRIMARY KEY clause is used in the table definition. For example: 

CREATE TABLE Department (
		dept_id int(11) NOT NULL AUTO_INCREMENT,
		dept_name varchar(100) DEFAULT NULL, 
		manager varchar(100) DEFAULT NULL, 
		address varchar(250) DEFAULT NULL, 
		PRIMARY KEY (dept_id)
		)ENGINE=InnoDB;

Here dept_id is defined as primary key. PRIMARY KEY clause can be used along with the column definition. For example, above table definition can be written as

CREATE TABLE Department (
		dept_id int(11) NOT NULL 
			AUTO_INCREMENT PRIMARY KEY,
		dept_name varchar(100) DEFAULT NULL, 
		manager varchar(100) DEFAULT NULL, 
		address varchar(250) DEFAULT NULL				
		)ENGINE=InnoDB;

To define the primary key after table creation, ALTER TABLE command is used. For example you can add primary key on Department table by:

ALTER TABLE Department ADD PRIMARY KEY (dept_id);
PRIMARY KEY vs. UNIQUE KEY vs. KEY

A KEY is a synonym for INDEX. You use KEY when you want to create an index for a column or a set of column that is not a part of a primary key or unique key.

A UNIQUE index creates a constraint for a column whose values must be unique. Unlike the PRIMARY index, MySQL allows NULL values in the UNIQUE index. A table can also have multiple UNIQUE indexes.

Foreign key

A foreign key is a field in a table that matches a field of another table. A foreign key places constraints on data in the related tables that, which enables MySQL to maintain referential integrity. For example We have two tables: customers and orders. Each customer has zero or more orders and each order belongs to only one customer. The relationship between customers table and orders table is one-to-many, and it is established by a foreign key in the orders table specified by the customerNumber field. The customerNumber field in the orders table relates to the customerNumber primary key field in customers table.

A foreign key has not only one column but also a set of columns. The columns in the child table often refer to the primary key columns in the parent table. A table may have more than one foreign key, and each foreign key in the child table can have a different parent table.

Foreign keys enforce referential integrity that helps you maintain the consistency and integrity of the data automatically. For example, you cannot create an order for a non-existent customer.

In addition, you can set up a cascade on delete action for the customerNumber foreign key so that when you delete a customer in the customers table, all the orders associated with the customer are also deleted. This saves you time and efforts of using multiple DELETE statements or a DELETE JOIN statement.

The same as deletion, you can also define a cascade on update action for the customerNumber foreign key to perform cross-table update without using multiple UPDATE statements or an UPDATE JOIN statement.

Here is the syntax:

CONSTRAINT constraint_name
	FOREIGN KEY foreign_key_name (columns)
	REFERENCES parent_table(columns)
	ON DELETE action
	ON UPDATE action
Copy Table

It is very easy to coply table into another. Here copy means the table structure as well as table data. You can use the below query to copy data from existing table into new table.

CREATE TABLE new_tablename
Select * from existing_tablename
Query on table data

SQL SELECT statement is used to query table data. You can provide the list of columns to be shown in result. Here is the syntax:

SELECT [*][column1,column2,.....] from table_name 
	WHERE conditions
	GROUP BY group HAVING group_conditions
	ORDER BY column_1 [ASC | DESC]
	LIMIT offset, row_count;

Here * represent the all columns from the table(Department). You can select only dept_name and manager using below command:

SELECT dept_name, manager from Department;

This will list out all the dept_name and manager from the Department table. You can filter the record returned from above command using WHERE clause. Here is the syntax:

SELECT column list..... from table_name WHERE condition;

For example, we want all the departments whoes manager is “Arun Shah”. Here is the implementation:

SELECT dept_name, manager from Department 
	where manager like 'Arun Shah';

Similary you can use arithmatic operators on numeric columns. Lets have another table Employee; whoes structure is:

CREATE TABLE Employee (
			emp_id int(11) NOT NULL 
				AUTO_INCREMENT PRIMARY KEY,
			first_name varchar(100) DEFAULT NULL,
			last_name varchar(100) DEFAULT NULL, 
			email varchar(100) DEFAULT NULL, 
			address varchar(250) DEFAULT NULL,
			dept_id int(11),				
			office_id int(11),
			contact_no varchar(15),
			salary int(11),
			status varchar(25)
		)ENGINE=InnoDB;

For example, we want all the list of all employees whoes salary is more than 40,000. The command will be:

SELECT first_name, last_name, salary from Employee 
	where salary>=40000;

You can arrange the retuerned record into accending or decending order using ORDER BY clause. For example, You want to list 40000 above salary in decending order. That is heighest salary will be displayed on top. The command will be:

SELECT first_name, last_name from Employee where salary>=40000
	ORDER BY salary DESC;

Similarly We can group the result into categories using GROUP BY clause. Please note that GROUP BY clause is used only with the aggrigate functions. You can not list all record using GROUP BY clause. For exanmple, you can count the no of employees in each department by:

SELECT count(emp_id), dept_id from Employee GROUP BY dept_id;

Please also note that you can only pass selected columns in the column list which are specified with GROUP BY clause. You can also add filter with GROUP BY clause with WHERE keyword. For example, you want to get the total salary given to each office of dept_id 10 (IT). Here will be the command:

SELECT SUM(salary), office_id from Employee 
		WHERE dept_id=10
		GROUP BY office_id;

You can find the office_id wise total salary of the active employees. i.e. GROUP BY clause will be used with a condition. Here HAVING keyword will be used with the GROUP BY. Here is the complete command:

SELECT SUM(salary), office_id from Employee
		WHERE dept_id=10
		GROUP BY office_id HAVING status='Active';

In above command all the rows will be grouped on th basis of office_id and Sum of salary of each group will be calculated based on the HAVING condition.

Similarly you can get the selected rows from the query result. For example if you want only the first three rows of the query result then you can use LIMIT caluse with SELECT statement. Lets have an example, suppose you have to get top three heighest salaries among all the employees, then you have to first arrange all the rows into decending order of salary and then fetch only top three rows. Here is the SQL statement:

SELECT emp_id, first_name, last_name, salary from Employee
	 ORDER BY salary
	 LIMIT 0,3;
BETWEEN operator

BETWEEN and IN operators are used for the range selection. Lets make it more clear, suppose you have to get all employees list whoes salary lies in the range of 40,000 to 60,000. Here you have a minimum and maximum limit. You may use BETWEEN operator here as:

SELECT first_name, last_name, salary from Employee
	 WHERE salary BETWEEN 40000 and 60000;

Same result can be obtained by using less than (<) and greater than (>) operatoers. SQL statement is:

SELECT first_name, last_name, salary from Employee
	 WHERE salary>=40000 and salary<=60000;
IN operator

IN operator gives an advantage to get the rows in random group. You can get all the rows with the matching condition. All the condition will be combined with the or operator, i.e. SQL statement will give you result if any one of the values specified with the IN is matched in the table. For eaxmple, you want to get the employees of department 10 in office id of 2,3,7. Here is the SQL statement:

SELECT first_name, last_name from Employee
	WHERE dept_id=10 and office_id IN(2,3,7);

Please note that it is not nessary that a employee of department 10 is posted in each office, so above command will return all the employees from office id 2,3 and 7 with department id 10.

Modify Table Data

DML (UPDATE, DELETE and INSERT) statement is used to modify the table data. INSERT statement is used to add data in table. You can pass the new values with the insert statement. here is the syntax of INSERT statement:

INSERT INTO table_name (column1, column2, column3,......)
	VALUES(value1, value2, value3,......);

You need to mention all the not NULL column value with the INSERT statement. If you want to add new row in Employee table, then here is the SQL statement:

INSERT INTO Employee (first_name, last_name, 
	 email, address, dept_id, office_id, contact_no, 
	 salary, status) 
	VALUES('Ankit','Sharma',
	 'ankit.sh@gmail.com','Gr. Noida', 10,2,'9901245852',
	  30000,'Active');

About Us

"This portal is made to share the knowledge among the people. If you have any query then you can contact on the below mail. Help us maintain the site with accurate and updated information."

Quick Links

Contact Us

Email : info@estudy-portal.com

Copyright © 2015 eStudy Portal. All Rights Reserved | Design by W3layouts