WELCOME TO THE VIRTUAL CLASS
OF
INFORMATIC PRACTICES
CLASS XI
MySQL
MySQL is a relational database management
system based on SQL – Structured Query
Language. The application is used for a wide
range of purposes, including data
warehousing, e-commerce, and logging
applications. The most common use for
mySQL however, is for the purpose of a web
database.
What is Database
A database is an organized collection of data,
stored and accessed electronically from a
computer system.
When a database is used to interact with end
user, applications to accept, analyze and interpret
data according to the requirement it becomes
DataBase Management System(DBMS).
When in a DBMS number of databases are inter-
related it forms Relation Database
Management System (RDBMS)
Main Features of Database
Management Systems
• Removes Redundancy
• Controls Inconsistency
• Facilitates Sharing of Data
• Ensures Security
• Maintain Integrity
Based on above discussed features there are
many DBMS like MySQL, SyBase, Oracle, Ingress
etc. but MySQL is most widely used.
Why MySQL is widely used
• Open Source and Free Cost Software
• Easily Portable
• Due its Connectivity
• Security
Terminology of RDBMS
• Relation/Table
It is two dimensional structure used to store data in
the form of Rows and Columns
• Tuple/Entity/Record
A row of table is called Tuple / Entity / Record
• Attribute/Field
A Column of a table is called Attribute/Field
• Degree
Number of Columns in a table is called Degree
• Cardinality
Number of Rows in a table is called Cardinality
Concept of Key
• Key
A column/s which identifies a record is called key
• Primary Key
A column or set of columns which uniquely identifies a
record in a table is called Primary Key
• Candidate Key
A column or set of columns which can be used as Primary
key is called Candidate key
• Alternate Key
A candidate key which are not Primary key is called Alternate
Key
• Foreign key
Primary key of one table is non primary key of another table
is called Foreign Key (this concept is used to inter link two or
more tables)
Type of MySQL
Commands
Data Transaction
Data Definition Data Control
Manipulation Control
Language Language
Language Language
Create Commit
Select
Drop Rollback
Update Grant
Alter Savepoint
Insert Revoke
Being
Delete transaction
Set transaction
DATA DEFINITION LANGUAGE(DDL)
• DDL changes the structure of the table like
creating a table, deleting a table, altering a table,
etc.
• All the command of DDL are auto-committed that
means it permanently save all the changes in the
database.
DATA MANIPULATION LANGUAGE(DML)
• The SQL commands that deals with the
manipulation of data present in the database
belong to DML or Data Manipulation Language
and this includes most of the SQL statements.
Commands Related to Database
• Creating a Database
mysql> CREATE DATABASE school;
• Opening a Database
mysql> USE school;
• Deleting a Database
mysql> DROP DATABASE school;
• View the List of Existing databases
mysql> SHOW DATABASES;
• To view the currently opened database
mysql> SELECT DATABASE;
Commands Related to Table
• Opening a Database
mysql> USE school;
• Creating a Table
mysql> CREATE TABLE student
(<column name><data type><size><constraint>,…);
• Deleting a Table
mysql> DROP TABLE student;
• View the List of Existing in a databases
mysql> SHOW TABLES;
• To view the structure of Table
mysql> DESCRIBE student;
Creating Tables in Detail
• Creating a Table
mysql> CREATE TABLE student
(<column name><datatype><size><constraint>,…);
Note:- Constraint is optional which we will discuss
in future
What is this DataType?
Data Type is the type of Data which will be entered
as value in the particular column. There are
different data types in MySQL
DATA TYPES
• Char (Size):- It is fixed length string upto 255 characters and
String or default is 1
Text Data • Varchar (size):- It is variable length string upto 255 characters
Type
• Date:- Used to store date in the format YYYY-MM-DD
Date & • Time:- Stores Time in the format HH:MM:SS
Time Data
type
• Integer or int:- Use to store 11 digit number without decimal
point
Numeric • Small Int:- Use to store 5 digit number without decimal point
Data Type • Decimal/Float:- used to store Real numbers of n digits including
decimal point for example:-demical(5,2) can store 33.50
INSERTING DATA IN TABLE
• After creating a table with required fields of
specific data types, our table is ready accept data
values.
First Method
mysql> INSERT INTO <table name> values (value1,
value2, value3…..);
Second Method
mysql> INSERT INTO table_name (column1,
column2, column3, ...) VALUES (value1, value2,
value3, ...);
FOR EXAMPLE
• Create a table named “computer” under database
“hardware” with the columns as comp_type of char data
type, quantity_purchased of integer data type and price
of decimal data type
Commands you will give are
mysql>create database hardware;
mysql>Use hardware;
mysql>Create table computer (comp_type char(25),
quantity_purchased int, price decimal(6,2));
Now to add records
mysql>insert into computer values(“Laptop”,1,20000);
Or
mysql>insert into computer (comp_type,
quantity_purchased, price) values (“Laptop”,1,20000);
TRY YOURSELF
Create a table named “Student” in the database
“school” and enter seven records in the table. The
structure of the table must be as under:-
Column Name Data Type
RollNo Integer
Name Varchar(25)
Gender Char(1)
Marks1 Decimal(4,1)
Data to be inserted
WHAT is QUERY?
• A query is a request for data or information from a
database table or combination of tables. In other
retrieving data or information from a single or multiple
tables according to the requirement.
• Select Clause is used to handle query.
Query Handling using Select
Command
• Retrieving all columns of a table
mysql> SELECT * from student;
• Retrieving a single column
mysql> SELECT rollno from student;
Query Handling using Select
Command
• Retrieving a multiple columns but not all
mysql>SELECT name,rollno from student;
Eliminating duplicate values
(only for display)
mysql>SELECT marks1 from mysql>SELECT distinct marks1
student; from student;
Using Arithmetic Operations with Select
Command
• Arithmetic Opertions can be used only with numeric
datatype columns.
• When we use any operator or conditions with select
command they are only for display that means they
never changes the data of table in original. So following
are the arithmetic operations that can be used
Try These commands by yourself
mysql>SELECT name, marks1+5 from student;
mysql>SELECT name, marks1+0.5*marks1 from student;
mysql>SELECT name, marks1-10 from student;
mysql>SELECT name, marks1/2 from student;
mysql>SELECT marks1 as “Marks Obtained” from student;
• You will see every time calculation is done on the
original marks1 which you have entered using insert
command.
• The result of last will show the data of column
marks1 but named as Marks Obtained which is name
of display for column marks1
Filtering the data using Select
Command
When we want to display the data based on some
specific condition that is called filtration. In Excel
we do this filter command of DATA menu same can
be done in MySQL by using “where” clause with
select command. Syntax is as under
mysql>SELECT <column/s> from <table name>
where <condition>;
Try these commands and observe the result
• mysql>SELECT name, marks1 from student
where marks1 <80;
• mysql>SELECT * from student where
name=“Gurpreet kaur”;
• mysql>SELECT name, marks1 from student
where rollno <=3;
• mysql>SELECT name, marks1 from student
where rollno <>3;
• mysql>SELECT name, marks1 from student
where name <> “Mani Kumar”;
Note:- here all the operators we used are called
relational operators the result of which is always True
or False. The use of which is described as under
Use of Logical Operators
• There are three logical operators AND (&&),
OR(||), NOT(!) which can used with select
command for filtering of data.
• mysql>SELECT name, marks1 from student
where marks1>70 and marks1<80;
• mysql>SELECT name, marks1 from student
where marks1>70 or marks1<80;
• mysql>SELECT name, marks1 from student
where marks1>70 not rollno<3;
Observe the result
Range Based Conditions
• Between Operator is used to form a condition
based on range. It includes both upper and
lower values
• mysql>SELECT name, marks1 from student
where marks1>=70 and marks1<=80;
• mysql>SELECT name, marks1 from student
where marks1 between 70 and 80;
List Based Conditions
• In Operator is used to form a condition based
on values given in the list.
• mysql>SELECT name, marks1 from student
where marks1 in(75,74,78,70,80);
Pattern Match Conditions
• Sometimes when we remember only a part of data
the we use filter the given data on the basis of
patterns.
• Two symbols % and _ are used to match the
pattern and these are used only with text data type
columns. These are wild card characters used with
LIKE clause.
• % is used to represent multiple characters
• _ is used to represent single character
• This can be more clearly understood when you
execute the commands given on next slide
Try these Commands
• mysql>SELECT * from student where name like
“%sen”;
• mysql>SELECT * from student where name like
“J%”;
• mysql>SELECT * from student where name like
“J%sen”;
• mysql>SELECT * from student where name like
“%sen%”;
• mysql>SELECT * from student where name like
“A____ Ali”;
Sorting the Data
• Data can be sorted either in ascending order or
in descending order by using the ORDER BY
clause with select command as under
• mysql>SELECT * from student where
marks1>70 order by rollno;
• mysql>SELECT * from student where
marks1>70 order by rollno desc;
• mysql>SELECT * from student where order by
marks1;
• mysql>SELECT * from student where order by
marks1 desc;
Now lets modify the table
structures and data already
entered and also learn how to
enter data based on certain
conditions
Modifying Table Structure
• Use school;
• Show tables;
• Describe student;
• Select * from student;
Modifying Table Structure
• Alter table student add games varchar(20);
• Describe student;
• Select * from Student;
Modifying Table Structure
• Alter table student modify games integer;
• Describe student;
• Alter table student drop games;
• Describe student;
• Hence we saw that we can add new column, modify the
datatype of existing column and delete any existing
column from the table with the help of Alter Table
command.
Updating/Modifying the Data
• Update Command –used to change the value of
existing column.
mysql>UPDATE <table name> SET <column
name>=<value>…) WHERE <condition>;
Try these commands on the table Student
mysql>UPDATE student SET Marks1=94 WHERE
name=“Monica Rana”;
mysql>UPDATE student SET name= “chhavi
chanana” , marks1=90 WHERE Rollno=15;
Note:- Any change done with update and alter
command will effect the table data and structure
respectively.
Deleting the Data
• Delete Command –used to remove entire row
not the column.
mysql>DELETE FROM <table name> WHERE
<condition>;
Try these commands on the table Student
mysql>DELETE FROM student WHERE
Rollno=14;
mysql>DELETE FROM student;
Constraints
• Constraints are the rules enforced on the data
columns of a table. These are used to limit the
type of data that can go into a table. This
ensures the accuracy and reliability of the data
in the database.
• Constraints can be specified when a table is
created with the CREATE TABLE statement or
you can use the ALTER TABLE statement to
create constraints even after the table is
created.
Types of Constraints
• Constraints could be either on a column
level or a table level. The column level
constraints are applied only to one column,
whereas the table level constraints are
applied to the whole table.
Commonly used Constraints
•NOT NULL Constraint − Ensures that a column
cannot have NULL value.
•DEFAULT Constraint − Provides a default value for
a column when none is specified.
•UNIQUE Constraint − Ensures that all values in a
column are different.
•PRIMARY Key − Uniquely identifies each
row/record in a database table.
•FOREIGN Key − Uniquely identifies a row/record in
any of the given database table.
Working with Constraints
Adding a Constraint:- A constraint can be added at the time of table
creation with Create Table command or also after creation of table
with Alter Table command
For example (at the time of table creation):-
Create table shoes (code char (4) Primary key, name varchar(25) size
int(2));
OR
Create table shoes (code char (4), name varchar(25) size int(2),
Primary key (code));
For example (after table creation):-
Alter table shoes add primary key(code);
Working with Constraints
Removing a Constraint:- A constraint can be removed with Alter
Table command
For example (after table creation):-
Alter table shoes drop primary key;
this will delete only the constraint not the column or data of the
column
• Modifying a Constraint:- A constraint can be modified with Alter
Table command
For example (after table creation):-
Alter table shoes modify code not null;
