MySQL and its basic commands

Explain in details MySQL and itsExplain in details MySQL and its
CommandsCommands
Presented by
Basumatary, Bwsrang
Nag, Dashrat Singh
MLISc. Sem-II, TISS, Mumbai
Date: 26th Feb. 2015

Contents
1.0. Introduction
2.0. What is MySQL ?
3.0. Development of MySQL
4.0. MySQL installation
5.0. Features of MySQL
6.0. MySQL data types
7.0. Basic MySQL Commands
8.0. MySQL Constraints
9.0. Conclusion
References

3
1.0. Introduction
 MySQL is the most popular open source SQL database management system,
 Database is a separate application that stores a collection of tables with related data.
 We use RDBMS to store and manage huge volume of data. This is called relational
database, because all the data is stored into different tables and relations are
established using primary keys or other keys known as foreign keys.
 RDBMS is a software:
 which enables to implement a database with tables, columns and indexes.
 Guarantees the referential integrity between rows of various tables.
 Updates the indexes automatically
 Interprets an SQL query and combines information from various tables.
 MySQL is a fast and easy to use, RDBMS being used for many small and big
businesses.

4
2.0. What is MySQL ?
 Most popular open source SQL database management system
 Developed, distributed and supported by oracle corporation.
 Provide best open source RDBMS being developing web based
software applications.
 Supports including Windows, the major operating systems Linux,
UNIX, Mac.
 Widely accepted.
 MySQL uses a standard form of the well known SQL data language.
 Used by many of the larger online products today.
 Used part of lamp stack, it is used to create the backbone of many
of the popular web site, social networking sites.

5
3.0. Development of MySQL
 Created by Swedish Company, MySQL AB, fonded by Michael
Widenius, David Axmark & Allan Larsson during 1994
 First internal release on 23rd May 1995
 Released Windows version on 8th Jan. 1998 (W95 and NT)
 Latest version 5.6.23 on 02 Feb. 2015

6
4.0. MySQL installation
 Windows: MySQL database server can be installed either w/or
others OS
 Download from "dev.mysql.com", pick the version from
MySQL community server, depends of your PC capacity.
 Other: a part of lamp stack or independently as r unable
program
 And some other all in one software (eg. WAMP server)

7
5.0. Features of MySQL
 Open source: User no need to pay anything for MySQL. Open
source GPL(General Public Licenses),
 Multi-User support: Multiple clients have concurrent access to one
or more databases simultaneously.
 Portability: MySQL works on many operating system.
 Understand SQL: MySQL understand SQL, which is the standard
language of choice for all modern database system.
 High Performances:
 works very quickly and well even with large data sets.
 Support large database up to 50 million rows.

8
Cont......
 Ease to use: It is easy to use. It is simple to install and implement. User can
install MySQL within a few minutes.
 Speed: MySQL is the fast. Respont the request data faster than others.
 Small in size: MySQL has a modest distribution size, especially compared
to the huge disc space footprint of certain other database system.
 Runs many of the world's most demanding websites/search engines/social
networks etc.
eg.:IRCTC, Google, yahoo, youtube etc.

9
6.0. MySQL data types
MySQL uses many different data types which were categorized into 3 parts:
6.1. Numeric Data type,
6.2. Date and Time and
6.3. String data types.
6.1. Numeric Data Types:
 INT: Numeric data type. maximum number of digits may be specified in
parenthesis
The following data types are use for maximum numbers of data storage and
retrieval
 INYINT
 SMALLINT
 BIGINT
 FLOAT
 DOUBLE
 DECIMAL

10
Cont......
6.2. Date and Time Types:
 DATE: Format: YYYY-MM-DD
 DATETIME: Date and time combination. Format: YYYY-MM-DD HH:MI:SS
 TIMESTAMP: This values are stored as the number of seconds. Format like YYYY-MM-DD
HH:MI:SS
 TIME: Format: HH:MI:SS
 YEAR: Year in two-digit or four-digit. [eg. two digit: 80 to 90, representing years from 1980 to
1990]
6.3. Text (String) Types:
 CHAR: Fixed length string(contain letters, numbers, and characters). The fixed size is specified
in parenthesis. Can store up to 255 characters.
 VARCHAR: Field is a set of character data of indeterminate legth. The maximum size is
specified in parenthesis. Can store up to 255 characters
 TINYTEXT: Holds a string with a maximum length of 255 characters
 BLOB or TEXT: These are use for huge data storing. Holds a string with a maximum length of
more than 65 thousand characters.
 MEDIUMBLOB or MEDIUMTEXT: Holds a string with a maximum length of more than 16 lakh
characters.
 LONGBLOB or LONGTEXT: Holds a string with millions of characters
 ENUM: You enter the possible values in this format: ENUM('X','Y','Z').

11
7.0. Basic MySQL Commands
1. CREATE : (DDL). That allows to create database.
Command: CREATE DATABASE <database name>;
For eg.: CREATE DATABASE RESULT;
Now we will show the RESULT database is created or not.
Command: SHOW DATABASES;
N:B: The RESULT database is created.

12
Cont......
2. DROP (DDL): This command allows us to remove database or entire objects from the database.
Be careful while deleting any database because you will lose your all the data available in your
database
Command: DROP DATABASE <database name>;
or DROP TABLE <table name>;
3. USE database: Now we will create table. Before creating the table we need to select the specific
database, in which database you are going to create tables and store the data.......
Command: USE <database name>;
For eg.: We will use the database RESULT.
Command: USE RESULT;

13
Cont.......
4. CREATE TABLE: We selected a specific database. Now create the table.
Command: CREATE TABLE <table name> (column1 data type, column2 data type, column3);
Eg.: CREATE TABLE SEM2 (REGNO INT NOT NULL, NAME VARCHAR(100),
SUBJECT VARCHAR(30), MARKS INT NOT NULL);
5. DROP TABLE: Same as previous drop command.

14
Cont.......
6. INSERT INTO (DML): To insert data into MySQL table, you would need to use SQL
INSERT INTO command. You can insert data into MySQL table by using following
command
Command:INSERT INTO <table name> (column1, column2, column3) values
(.......................);
For eg.: We will insert values into SEM2 table
Command: INSERT INTO SEM2 (REGNO, NAME, SUBJECT, MARKS)
VALUES (1234, 'BWSRANG', 'INFORMATION STORAGE AND RETRIEVAL', 80);

16
Cont........
7. SELECT (DRL): Use for retrieve data from database. Use for selecting various attributes or
column of a table. The SQL SELECT command is used to fetch data from MySQL database
Command: SELECT* FROM <table name>;
SELECT* FROM SEM2;

17
Cont......
If you want to retrieve some attributes from the table
command: SELECT <column1> FROM <table> WHERE <condition>;
eg. 1: SELECT SUBJECT FROM SEM2 WHERE MARKS='80';
You can retrieve different condition from table:
eg.2: SELECT* FROM SEM2 WHERE MARK >'80';
here all the greater than 80 marks from SEM2 table will be retrieved.

19
Cont......
8. Where clause: We have seen SQL SELECT command to fetch data from MySQL
table. It works like an if condition in any programming language. We can use a
conditional clause called WHERE clause to filter out results. Using WHERE
clause, we can specify a selection criteria to select required records from a table.
 WHERE clause is an optional part of SELECT command.
 You can specify any condition using WHERE clause. (area, mark less than and
greater than etc.)
 You can specify more than one conditions using AND or OR operators.
 A WHERE clause can be used along with DELETE or UPDATE SQL
command also to specify a condition.

20
Cont......
9. UPDATE (DML): There may be a requirement where existing data in a MySQL
table needs to be modified. You can do so by using SQL UPDATE command. This
will modify any field value of any MySQL table. The WHERE clause is very
useful when you want to update selected rows in a table.
Command: UPDATE <table name>
SET COLUMN1=values
WHERE <condition>;
eg.: UPDATE SEM2
SET SUBJECT='ISR' WHERE NAME ='BWSRANG';

21
Cont.....
10. DELETE (DML) : Use for delete data from table (only specific data). If you want to
delete a record from any MySQL table, then you can use SQL command DELETE
FROM. You can delete records in a single table at a time.
Command: DELETE FROM
eg.: DELETE FROM SEM2 WHERE MARK='90';
11. ALTER (DDL): Use for modifies an existing database objects. MySQL ALTER
command is very useful when you want to change a name of your table, any table field
or if you want to add or delete an existing column in a table.
Command: (Add)
ALTER TABLE SEM2 ADD POINT INT;
Command: (Drop)
ALTER TABLE SEM2 DROP POINT;
eg.: ALTER TABLE SEM2
MODIFY NAME VARCHAR(100) NOT NULL;

22
8.0. MySQL Constraints
Constraints are used to specify rules for the data in table.
 NOT NULL: Used to represent a column can not have value(empty).
eg.: SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE SALARY IS
NOT NULL;
 DEFAULT: Provide a default value for a column when none is specified.
 UNIQUE: Ensures that all the values in columns are unique. (different)
 Duplication can not be happen.
eg. Roll No., date of birth.
 PRIMARY KEY: Combination of NOT NULL and UNIQUE.
 FOREIGN KEY : Uniquely identify a row / record in any other database table.
 CHECK: The check constraints ensures that all values in a column specify certain
condition.
 INDEX: Use to create and retrieve data from database very quickly.

24
References
1. DuBois, P. (2008). Why Choose MySQL ? In MySQL(4th ed., p. 1224).
Pearson Education.
1. SQL Tutorial. (n.d.). Retrieved February 20, 2015, from
http://www.w3schools.com/sql/default.asp
1. Tutorials Point - Simply Easy Learning. (n.d.). Retrieved February 19, 2015,
from http://www.tutorialspoint.com/mysql/mysql-insert-query.htm

MySQL and its basic commands

More Related Content

What's hot

Viewers also liked

Similar to MySQL and its basic commands

Recently uploaded

MySQL and its basic commands