28. Insert and update data using DDL, DML, and DCL statements.
V E S R N TECHNOLOGIES
Insert and update data using DDL, DML, and DCL statements.
AimTo perform insertion and updation of data using DDL, DML and DCL commands in MySQL.
Requirements
Computer system
MySQL / XAMPP / phpMyAdmin
Part – 1 : Create Database (DDL – Data Definition Language)
CREATE DATABASE iti_training;
USE iti_training;
Create Table (DDL)
CREATE TABLE trainee ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, trade VARCHAR(30), marks INT );
Alter Table (DDL)
Add a new column:
ALTER TABLE trainee ADD grade VARCHAR(5);
Part – 2 : Insert Data (DML – Data Manipulation Language)
INSERT INTO trainee(name, trade, marks) VALUES ('Raju','Electrician',78), ('Kumar','Fitter',82), ('Ravi','Welder',69);
View Data
SELECT * FROM trainee;
Update Data (DML)
UPDATE trainee SET marks = 85 WHERE name='Raju';
Update Multiple Columns
UPDATE trainee SET marks=90, grade='A' WHERE id=2;
Delete Data (DML)
DELETE FROM trainee WHERE id=3;
Part – 3 : Apply DCL (Data Control Language)
Create User
CREATE USER 'student1'@'localhost' IDENTIFIED BY '1234';
Grant Permission
GRANT SELECT, INSERT, UPDATE ON iti_training.* TO 'student1'@'localhost';
Revoke Permission
REVOKE UPDATE ON iti_training.* FROM 'student1'@'localhost';
Result
DDL used to create/modify table, DML used to insert/update/delete records and DCL used to control user permissions.
Precautions
Always use WHERE condition in UPDATE/DELETE
Give limited permissions to users
Take backup before altering table
I practiced all under my trainer Venugopal Sir.
