MySQL

Introduction to DBMS:-
 DBMS stands for Database Management System.
 A Database is collection of data/information that is
well organized.
 Database Management System is formed from two
terms (i) Database and (ii) Management System
where Database means collection of well organized
data/information and Management System means
various operations/functions operated on
data/information stored in database.
  So a DBMS is a collection of well organized data
along with set of operations operated on data stored
in database. following main operations are
performed on database-
(i) insertion of new data.
(ii) deletion of existing data.
(iii) update existing data.
(iv) retrieval of required data.

Disorganized VS Well Organized Collection

 Before Database Management System (DBMS) File System was

used but File System was not well organized and therefore
following problems were there in File System:-

(i) Difficulty Access:- In file system data is scattered in various

files and therefore it is difficult to access combination of data from
different files. In file system a programmer need to write separate
program to access data from different files.

(ii) Data Redundancy:- In file system data is scattered in

various files and therefore same data is duplicated in different files
which leads to Data Redundancy.

(iii)Data Inconsistency:- Since multiple copies of same data

(Data Redundancy) exist in file system so there are high chances that
one will update at one (File)place and forget to update the other copy
of the same data in the file which leads to Data Inconsistency.

(iv) Data Isolation:- In file system, data related to one entity is

scattered among multiple files, each file may have different file format.
Programmer need to write different program for different files since
there is not mapping among data of same entity.

(v) Data Dependency:- In file system, data is accessed through

programs. If format of data is changed for improving the overall system
than entire application program need to be redesigned. Since data is
dependent on application programs used.
DBMS VS FILE SYSTEM

Various advantages of Database Management System are:-

 Minimum Data Redundancy:- Since data is stored in centralized
database with only one copy for data related to one entity so redundancy is
minimum.

 Maintain Consistency:– DBMS does not have redundancy so there are

very less chances of having inconsistency in the database.

 Sharing of Data:- DBMS provide facility of sharing data among multiple

user of the DBMS.
  Database Enforce Standards:- DBMS enforces several rules to maintain
error free data in database. These rules are standard rules followed by all
the related technologies working with DBMS.

 Data Security:- Only authorized users can access the data of the
database. User without authentication key are not able to access the data
of DBMS.

Data Model :-
 A data model defines the overall logical structure (design) of the
database. It shows how data is managed in a database.
 Variety of data models are used to store data for different
circumstances like- (a) Hierarchical data model. (b) Network Data
Model. (c) Entity Relationship data model and (d) Relational Data
Model.
 Relational data model is the most widely used data model for
Database Management System.
Relational Data Model (Relational Database Management System):-
 In Relational Data model the data is stored in the form of tables (A
two dimensional Data structure).
 These tables are called relations.
 Each table have columns and rows.
 Rows of the table are called tuples and columns of the table is
called attributes.
Relational DBMS related terms:-
(a) Relation:- A table in RDBMS is called relation.
(b) Tuple:- A row of table in RDBMS is called a tuple.
(c) Attribute:- A column of table in RDBMS is called attribute.
(d) Degree:- Number of columns in a table is called Degree.
(e) Cardinality:- Number of rows in a table are called cardinality.
(f) View:- A view is a virtual table that is based on some real table. It is
used to protect data from other users.
(g) Primary Key:- Primary key is a set of columns which is used to
identify a row in a table uniquely.
(h) Candidate Key:- All the columns or set of columns that can be used
as primary key are called candidate key.
(i) Alternate Key:- A candidate key that is not selected as primary key is
called alternate key.
(j) Foreign Key:- foreign key is used to relate one table to another table
where foreign key is a column in one table that refers to the primary
key in another table.
MySQL Database System:-
 MySQL is an open source Relational Database
Management System Software.
 MySQL is currently the second most popular
RDBMS is the world after oracle SQL.
 MySQL uses SQL (Structured Query Language)
which is a declarative programming language.
 Since MySQL is Relational Database
Management System therefore information is
stored in Tables.
 MySQL works on Client-Server architecture in
which database is stored on servers side (A
dedicated computer having Database) and
respond to the queries fired (asked) by clients
(End User).
 MySQL Server:- A MySQL server is a powerful
computer having physical database. It receive
the queries from the client (End user) and
manipulate these queries using database and
send the result back to the client (End User).
 MySQL Client:- MySQL Client are the end users
who send queries to the server and get
appropriate response.
 Both client and server are connect to each
other through network.
SQL (Structured Query Language):-
 SQL structured Query Language is used to access the data
stored within the MySQL database.
 SQL is the set of commands supported by almost all the
RDBMS.
 SQL commands are mainly classified into four categories.
 DML ( Data Manipulation Language) Commands:-
Commands used to perform data manipulation on data
stored in database are known as DML. Like Retrieval of
required information, insertion of new information,
Deletion of information and Modification of existing
information in the database.
 DDL (Data Definition Language) Commands:- Commands
used to create new structures or modify structure of the
tables and database are known as DDL commands.
 DCL (Data Control language) Commands:- Commands used
to control access rights to the users of the database are
known as DCL commands.
  TCL (Transaction Control Language) Commands:-
Commands used to control transactions in a database are
known as DCL commands.
Installing MySQL RDBMS software :-
To download MySQL software click here on DOWNLOAD
button.
DOWNLOAD
FOR 64-BIT OPERATING SYSTEM
DOWNLOAD
FOR 32-BIT OPERATING SYSTEM
Once you download this software, install this in your personal
computer.
While installing MySQL software in your personal computer
execute following steps carefully-
Setp-1 Install the software with all default settings.
Setp-2 :- Choose Custom setup and click next:->
Step-3:- Set password or your user login. Keep your password “abc123”
(recommended) and press next:->
Step4:- Click finish. (The set up is installed successfully.
Creating Database:-
 A database is a collection of tables.
 We can create multiple databases in MySQL to store tables for each
enterprise.
 For example to store all tables of school, we can create “school” database
and to store all tables of an organisation we can create organisation
database.
 Following DDL (Data Definition Language) command is used to create a
database:-
Syntax:- CREATE DATABASE database_name;
Ex:- CREATE DATABASE school;

Connecting to a Database:-
 We can create multiple databases in RDBMS.
 At a time we work on a single database which is connected.
 To work in a database, first we need to connect to that database
using following sql command:-
Syntax:- CONNECT database_name;
Example:- CONNECT school;
Removing a Database:-
  Removing a database means delete all the tables of that database
from RDBMS.
 Once you remove a database from the RDBMS nothing is left
related to that database in RDBMS.
 Following sql command is used to remove a database:-
Syntax:- DROP DATABASE database_name;
Ex:- DROP DATABASE school;
Data types:-
 Data type specifies the nature of data.
 Nature of data means whether data is alphabetic, numeric,
boolean or date type.
 While creating tables in dbms we need to specify the data type of
each column of a table.
 Data type of a column specifies that what kind of data value can
be stored in that column.
 Following data types are supported by sql:-


Data
Type Range specification

Its a fixed length character (string)

data type. Means it allocate memory
as per the given size no matter what
is the size of actual data value. default
CHAR 0-255 size of CHAR data type is 1.
 Its a variable length character (string)
data type. Means it allocate memory
equal to the number characters
passed to the column. Remaining
bytes of memory are free. default size
VARCHAR 0-65535 is 80.

-2147483648 This data type is used store non-

to fractional numerical data in a column
INT 2147483647 of table.

Decimal This data type is used to store

precise to 23 Fractional numerical data in a column
FLOAT digits of table.

Decimal This data type is used to store

precise to 53 fractional numerical data in a column
DOUBLE digits of table.

This data type is used to store date in

DATE YYYY-MM-DD a column of table.

TIME HH:MM:SS This data type is used to store

Creating Table in RDBMS:-

 A table is used to store data in 2-dimensional form.
 A table is called relation in RDBMS.
 CREATE TABLE command is a DDL (Data definition Language)
command.
  Following syntax is used to create a table in sql:-
Syntax:- CREATE TABLE table_name (column1 datatype(size),
column2 datatype(size),
column3 datatype(size)..);

Ex:- CREATE TABLE student

(Roll_no int(3),
Name varchar(30),
Class int(3),
Per int(3));

Inserting Data into table:-

 This command is used to insert a new row in a table.
 This command comes under the DML( Data Manipulation
Language) statement.
 Syntax of using INSERT command is as follows:-
Syntax:- INSERT INTO table_name VALUES (value1,value2,value3...);

Ex:- INSERT INTO student value(101,"Rakesh",12,85);

Inserting NULL Value:-
 While inserting data value for a table if we do not have value for a
column than we can insert “NULL” value and can update later.
  To insert NULL value we just need to specify the value as NULL for
missing column.
 Syntax for inserting NULL value for missing values is as follows-
Syntax:- INSERT INTO table_name VALUES (value1,value2,NULL,value3);
Ex:- INSERT INTO student VALUES (104,"Vijay",NULL,94);
Inserting specific column Values:-
 Some times we need to insert only specific column values in a row
of a table.
 This situation occur when we do not have value for a few
columns.
 Syntax for inserting a tuple (row) without all column values is as
follows-
Syntax:- INSERT INTO table_name (column1,column2,column3..)
VALUES (value1,value2,value3...);
Ex:- INSERT INTO student (roll_no,name,class) VALUES
(107,"Ramesh",11);
Selecting table data using Select command:-
 SELECT command is the most commonly used command of SQL.
 SELECT command is used to fetch required data from a table of
database.
 Data retrieved from SELECT command is also in a tabular form and
known as result set.
 We can fetch either entire data of a table or specific data as per
our requirement.
 SELECT command have mainly three clauses-
  (a) SELECT clause
 (b) FROM clause
 (c) WHERE clause
 Each clause of SELECT command is used for following purpose :-
 (a) SELECT :- This clause is used to fetch specific columns of a
table.
 (b) FROM:- This clause is used to specify table_name from which
data is retrieved.
 (c) WHERE:- This clause is used to specify the condition. This
condition is used to filter tuples (rows) of the table. Tuples (rows)
satisfying this condition will be retrieved only.
 Syntax for using SELECT command is as follows:-
Syntax:- SELECT column1, column2, column3...
FROM table_name
WHERE test_condition;
Ex:- SELECT roll_no, Name, Class, Per
FROM student
WHERE Per>90;
Selecting distinct data values from a column:-
 While fetching data from a single column if there are
multiple identical data values in different rows they
all will be displayed.
 To avoid retrieval of duplicate data values of a
column we can use DISTINCT key word.
 Syntax for retrieving DISTINCT data values is as
follows:-
Syntax:- SELECT DISTINCT column1
FROM table_name;
Ex:- SELECT DISTINCT Class FROM student;
Condition based on a Range:-
 To filter and retrieve data according to a given range we use
“BETWEEN” key word in WHERE clause.
 Range contains two values- lower limit and upper limit of range to
test condition.
 Values falling in this range satisfy the condition.
  Both lower limit and upper limit itself will be included while
testing. (Inclusive)
 Syntax for using BETWEEN keyword for testing condition based on
range is as follows:-
Syntax:- SELECT column1, column2, column3 ...
FROM table_name
WHERE column_name BETWEEN Lower_limit AND Upper_limit;
EX:- SELECT roll_no, name, class, per
FROM student
WHERE per BETWEEN 90 AND 95;
Selection based on List:-
 Sql provide us facility to retrieve data from a table on the
basis of matching items from a list.
  In this case if items of a column exists in list specified in
WHERE clause then test condition get satisfied and these
records become part of the result set.
 IN operator is used to match items from a list.
 Syntax for using selection based on List with IN operator is
as follows-
Syntax:- SELECT column1, column2, column3...
FROM table_name
WHERE column_name IN (item1, item2, item3...);
Ex:- SELECT roll_no, name, class, per
FROM student
WHERE name in ("Amit", "Kezia", "Priya", "Ankit");
Condition based on pattern matches:-
 Pattern matching is used to retrieve data on the basis of
matching string or substrings.
 Patterns matching is used with LIKE keyword.
 LIKE clause is used to compare values to similar values.
 It use two wildcard characters (i) “%” percent (ii) “_”
underscore
  (i) “%” percent is used to match any string of any lenght.
 (ii)”_” underscore is used to match single character.
 following syntax is used to perform pattern matching using
LIKE keyword-
Syntax:- SELECT column1, column2, column3...
FROM table_name
WHERE column_name LIKE "%substring";

Ex:- SELECT roll_no, name, class, per

FROM student
WHERE name LIKE "A%";
Note:- The above select query will display all the records from
student table for which name of student starts with letter A.
Syntax:- SELECT column1, column2, column3...
FROM table_name
WHERE column_name LIKE "______";
Ex:- SELECT roll_no, name, class, per
FROM student
WHERE name LIKE "_____";

Note:- The above select query will display all the records from student table for
which name of student contains only 5 characters.
Searching for NULL values:-
 While inserting new records in a table if value of one or more
column is not known then NULL value is automatically inserted at
that column by SQL.
 Sometimes we need to search for records of a table for which an
specific column value is NULL.
 To search for NULL value, SQL provide a IS NULL clause.
 Following syntax is used to search for NULL values:-
Synatx:- SELECT Column1, Column2, Column3...
FROM table_name
WHERE Column_name IS NULL;
Ex:- SELECT roll_no, Name, class, per
FROM student
WHERE Name IS NULL;
Modifying Data in Table:-
 UPDATE command is used to modify existing data values in a
table.
 UPDATE command comes under DML (Data Manipulation
Language) commands.
 SET and WHERE clause are used with UPDATE command to modify
data value.
  SET clause is used to set new value in place of previous value.
 WHERE clause is used to specify the rows where information will
be updated.
 Following syntax is used to change data values using UPDATE
command:-
Syntax:- UPDATE table_name
SET column_name= New_Value
WHERE Test_Condition;
EX:- Update student
SET Name="Rakesh"
WHERE Roll_no=106;
Deleting data from a table:-
 DELETE command is used to delete data values from a table.
 DELETE command delete entire row (tuple) of a table.
  DELETE command have two clauses with it (i) FROM and (ii)
WHERE
 FROM Clause of DELETE command is used to specify the table
name from which data is going to be deleted.
 WHERE clause of DELETE command is used to specify the rows
which are going to be deleted.
 DELETE command is also a DML (Data Manipulation Command).
 Note:-* In absence of WHERE clause DELETE command delete all
the rows (tuples) available in that table.
 Following syntax is used implement DELETE command:-
Syntax:- DELETE FROM table_name
WHERE test_Condition;
Ex:- DELETE FROM student
WHERE Roll_no=107;
Constraints in SQL:-
 Constraints are rules used to prevent insertion of incorrect data values in a
table.
 Constraints keep table of database accurate and consistent.
 If user does not follow constraints rules then MySQL does not allow user to
insert record in a table. By this table of database does not have incorrect
data values in it.
 There are two types of constraints-
  (i) Column level constraints
 (ii) Table level constraints
Creating Table with Column level SQL Constraints:-
 Column level Constraints are used to restrict a user from inserting incorrect
data values for a column in a table.
 In absence of column level constraint user may insert certain incorrect data
values in a column which make our table inconsistent.
 Following column level constraints are used in MySQL-
1. NOT NULL
2. PRIMARY KEY
3. UNIQUE
4. DEFAULT
5. CHECK
Syntax:-
CREATE TABLE table_name (Column1 datatype(size) constraint_definition,
Column2 datatype(size) constraint_definition,
Column3 datatype(size) constraint_definition);

Ex:- CREATE TABLE student (Roll_no int(4) NOT NULL PRIMARY KE, Name
varchar(30), Class int(3), Per float(5,2));
NOT NULL constraint:-
 Sometimes it is required to have a value for a column instead of NULL
value.
  NOT NULL constraint is used to restrict a user from inserting NULL value in a
column of table.
 Once we define NOT NULL constraint on a column, any attempt to insert a
record with NULL value for that specific column will be not work.
 Following syntax is used to impose NOT NULL constraint-
Syntax:-
CREATE TABLE table_name

(Column1 datatype(size) NOT NULL,

Column2 datatype(size),

Column3 datatype(size));

Ex:- CREATE TABLE student

(Roll_no int(4) NOT NULL,

Name varchar(30),

Class int(3),

Per float(5,2));
PRIMARY KEY Constraint:-
 A Primary Key is a single column or combination of
multiple columns used to identify each row (tuple) of a
table uniquely.
 If a column or Combination of column is defined as
PRIMARY KEY, then it is compulsory to insert unique value
 for that column or combination of columns for each row
(tuple).
 If we try to insert duplicate value for such a column
(Defined as PRIMARY KEY) MySQL does not allow us to
insert such row (tuple).
 Note:-*While defining a column as PRIMARY KEY it is mandatory
to define NOT NULL constraint as well for that column.
 Syntax for defining PRIMARY KEY is as follows-

Syntax:-
CREATE TABLE table_name
(column1 data_type(size) NOT NULL PRIMARY KEY,
column2 data_type(size),
column3 data_type(size));

Ex:- CREATE TABLE student

(Roll_no int(4) NOT NULL PRIMARY KEY,
 Name varchar(30), Class int(3), Per float(5,2));

UNIQUE constraint:-
 Unique constraint is also used to prevent user from inserting
duplicate value for a column.
 If we try to insert duplicate value for a column defined as UNIQUE
constraint, MySQL does not allow us to insert such record.
 For a column defined as UNIQUE constraint we can insert NULL
value without any restriction.
  Syntax for define UNIQUE constraint is as follows-
Syntax:- CREATE TABLE table_name
(Column1 data_type(size) UNIQUE,
Column2 data_type(size),
Column3 data_type(size));
Ex:- CREATE TABLE student
(Roll_No int(3) UNIQUE,
Name varchar(30),
Class int(3),
Per float(5,2));
Difference between PRIMARY KEY and UNIQUE constraint:-

PRIMARY KEY
CONSTRAINT UNIQUE CONSTRAINT

Used to identify each Used to identify each tuple of a

tuple of a table table uniquely when Primary key
uniquely. is not present.

Column defined as
Primary key can not Column Defined as UNIQUE key
have NULL value. constraint can have NULL value

One table have only One table may have

one Primary key. multiple Unique keys.

DEFAULT Constraint:-
 DEFAULT constraint is used to set a default value for a
column of table in RDBMS.
 In absence of DEFAULT constraint if user skip value for a
column in a row (tuple), MySQL insert NULL value for that
column.
 If DEFAULT constraint is defined for a column and user do
not specify any specify value for that column then MySQL
insert the default value in that column automatically.
 Following syntax is used to set DEFAULT constraint:-
Syntax:- CREATE TABLE table_name
(column1 data_type(size),
column2 data_type(size) DEFAULT VALUE,
column3 data_type(size));
Ex:- CREATE TABLE student (Roll_no int(3), Name
varchar(30), Class int(3) DEFAULT 12, Per int(3));

Altering Tables:-
 Altering tables means modification in structure of existing
table.
 Table structure modification include following operations-
  (a) Addition of new column in a table.
 (b) deletion of existing column from a table.
 (c) Change in the column definition of existing column.
 Structure of an existing table can be changed using ALTER
TABLE command.
 ALTER TABLE command is a DDL (Data Definition
Language) Command.
Add new column in a table:-
 ADD clause of ALTER TABLE command is used to add a new
column in a table.
 Once we add a new column in a table, this newly added
column contain NULL value for each row (Tuple).
 Using UPDATE command we can update new values in
place of NULL.
 Following syntax is used to add a new column in a table:-
Syntax:- ALTER TABLE table_name ADD (Column_Name
Data_type(size));EX:- ALTER TABLE student ADD (City
 varchar(30));

Remove an existing column from a Table:-

 DROP clause of ALTER TABLE command is used to remove
a column from a table.
 Following syntax is used to remove a column from a table:-
Syntax:- ALTER TABLE table_name
DROP COLUMN column_name;
EX:- ALTER TABLE student
DROP COLUMN City;

Modify column data type and size:-

 Modify clause of ALTER TABLE command is used change data type
definition of a column of existing table.
 Following syntax is used to modify data type definition of
column:-
Syntax:- ALTER TABLE table_name
 MODIFY COLUMN_NAME new_datatype(new_size);
Ex:- ALTER TABLE student
MODIFY Per float(5,2);