MySQL Part - 6
INSERTION of Records
SYNTAX
INSERT INTO tablename
VALUES(value 1, value 2,....);
EXAMPLE
Considering the 'Student' table, to insert the data as given
Srno Name Class DateOfBirth
1011 Amit Sharma XI Sc 2005-02-12
INSERT INTO STUDENT
VALUES (1011, 'Amit Sharma', 'XI Sc', '2005-02-12');
Inserting multiple records
INSERT INTO STUDENT VALUES
(1012, 'Manish Sharma', 'XI Arts', null),
(1013, 'Ravi Kumar', 'XI Arts', '2004-02-05'),
(1015, 'Nitin Kapoor', 'XI Arts', '2004-11-25');
If we want to provide values only for some of the
attributes in a table (supposing other attributes
having NULL or any other default value), then we
use the following syntax of INSERT INTO
statement.
SYNTAX
INSERT INTO tablename (column1, column2, ...)
VALUES (value1, value2, ...);
EXAMPLE
INSERT INTO STUDENT (SRNO, CLASS, NAME)
VALUES (1017,'XI Sc', 'Aditya Jain');
Data Updation
We may need to make changes in the value(s)
of existing records in a table.
For example, we may require some changes in
address, phone number or spelling of name,
etc.
The UPDATE statement is used to make such
modifications in the existing data.
SYNTAX:
UPDATE table_name
SET attribute1 = value1, attribute2 = value2, ...
WHERE condition;
EXAMPLE:
UPDATE STUDENT
SET CLASS = 'XI Arts'
WHERE SRNO = 1011;
Updating multiple values
Suppose we want to change multiple values for a record
we can do so by specifying values after set clause.
EXAMPLE: we want to change the class of srno 1015 to
XI Comm and also the dateofbirth to '2004-1-2'
UPDATE STUDENT
SET CLASS = 'XI COMM', DATEOFBIRTH= '2004-1-2'
WHERE SRNO = 1015;
Updating all records
If we want to change the value of a particular column in
all the records, then we can skip the where clause.
EXAMPLE: To change the class of all students to XI Sc
UPDATE STUDENT
SET CLASS = 'XI Sc' ;
Data Deletion
The DELETE statement is used to delete one or
more record(s) from a table.
SYNTAX:
DELETE FROM table_name
WHERE condition;
EXAMPLE: To delete the record of srno 1012
DELETE FROM STUDENT
WHERE SRNO = 1012 ;
Deleting all records
If we want to delete all the records, then we
can skip the where clause.
EXAMPLE: To delete all records from the
student table
DELETE FROM STUDENT ;
Difference between char and varchar
char Varchar
Fixed length string Variable length string
If a column is given datatype as If a column is given datatype as
CHAR(10), then MySQL ensures that VARCHAR(10), then the maximum
all values in that column are of 10 size of string in that column can be
characters or it adds spaces to its 10 characters, but if the string is
right side to make it of 10 shorter MySQL stores it as it is and
characters. uses less number of bytes.
Uses more memory Uses less memory
Create table student Create table student
(rollnoint(4), name char(20)); (rollnoint(4), name varchar(20));
Difference between DDL and DML
DDL DML
Data Definition Language Data Manipulation Language
These commands work on the These commands work on the data
structure of the tables. stored inside the tables.
Create, Alter, Drop Insert, Update, Delete, Select
Example Example
drop table student; delete from student
where rollno=1;
Difference between Alter and Update
ALTER UPDATE
Data Definition Language Data Manipulation Language
It is used to change the structure of It is used to change the data stored
the table, that is, to in the table.
add/drop/modify columns.
Cannot be cancelled. Can be cancelled using rollback.
Example Example
Alter table student Update student
add address varchar(20); set marks=90
where rollno=1;
Difference between Drop and Delete
DROP DELETE
Data Definition Language Data Manipulation Language
It is used to remove both the data It is used to delete the records in a
and structure of a table. table.
Cannot be cancelled. Can be cancelled using rollback.
Example Example
Drop table student; Delete from student
where rollno=1;
