Scribd
Upload
1 views

MySQL part 6

This document provides an overview of MySQL commands for inserting, updating, and deleting records in a database. It includes syntax and examples for each operation, as well as explanations of data types like CHAR and VARCHAR, and the differences between DDL and DML commands. Additionally, it outlines distinctions between commands such as ALTER and UPDATE, and DROP and DELETE.

Uploaded by

mohitbhagtani05
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
1 views

MySQL part 6

This document provides an overview of MySQL commands for inserting, updating, and deleting records in a database. It includes syntax and examples for each operation, as well as explanations of data types like CHAR and VARCHAR, and the differences between DDL and DML commands. Additionally, it outlines distinctions between commands such as ALTER and UPDATE, and DROP and DELETE.

Uploaded by

mohitbhagtani05
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 14

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;

You might also like