MySQL All Commands_ Practical
Users Management Commands
Database Management Commands
Table Commands
Keys in MySQL
Constraints in MySQL
Views
Manipulation Commands
Joins
Index
Data Control Commands
Triggers
Transaction Commands
MRS.SWATI PRASAD PATREKAR 1 COPA_ITI_KARAD
1-CREATE USER SwatiPatrekar IDENTIFIED BY 'InfoEdge@123';
2-DROP USER SwatiPatrekar;
3-SELECT USER from MYSQL.Patients;
4-CREATE SCHEMA PatientsDatabase;
5-SHOW SCHEMAS;
6-SHOW DATABASES;
7-DROP SCHEMA PatientsDatabase;
8-DROP DATABASE PatientsDatabase;
9-USE PatientsDatabase;
10-CREATE TABLE Patients
PatientID int,
PatientName varchar(255),
Sex varchar(255),
Age int,
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
RegDate date
);
11-CREATE TABLE SamplePatientsTable AS
SELECT PatientID, PatientName, Age
FROM Patients;
MRS.SWATI PRASAD PATREKAR 2 COPA_ITI_KARAD
12-SHOW TABLES; 12-A-SELECT * FROM Patients;
13-USE PatientsDatabase;
14-ALTER TABLE Patients;
ADD INT BP;
15-DESCRIBE Patients;
16-TRUNCATE TABLE Patients;
17-DROP TABLE Patients;
18-RENAME TABLE Patients TO PatientsInfo;
19-INSERT INTO Patients
PatientID,PatientName,Sex,Age,Address,PostalCode,State,Country,RegDate date
) VALUES ('06', 'Suhana','F', ‘12’, 'House No 34C Jubilee Hills', '500046', ‘Telangana’, 'India',
‘02/09/2021’);
–OR
20-INSERT INTO Patients VALUES ('06', 'Suhana','F', ‘12’, 'House No 34C Jubilee Hills', '500046',
‘Telangana’, 'India', ‘02/09/2021’);
21-UPDATE Patients
SET PatientName = 'Afsana', PostalCode= '500034'
WHERE PatientID = 1;
22-DELETE FROM Patients
WHERE PatientName='Anay';
23-ALTER TABLE Patients
ADD COLUMN BP int;
–OR
24-ALTER TABLE Patients
MRS.SWATI PRASAD PATREKAR 3 COPA_ITI_KARAD
ADD COLUMN BP int AFTER Age;
–OR
25-ALTER TABLE TableName (
ADD COLUMN BP int AFTER Age,
ADD COLUMN Weight int AFTER BP);
26-ALTER TABLE Patients
ADD COLUMN BP int;
–OR
27-ALTER TABLE Patients
ADD COLUMN BP int AFTER Age;
–OR
28-ALTER TABLE TableName (
ADD COLUMN BP int AFTER Age,
ADD COLUMN Weight int AFTER BP);
29-SHOW COLUMNS FROM Patients FROM PatientsDatabase;
--OR
30-SHOW COLUMNS FROM PatientsDatabase.Patients;
31-ALTER TABLE Patients
CHANGE COLUMN Address PatientAddress VARCHAR(255);
--OR
32-ALTER TABLE Patients (
CHANGE COLUMN Address PatientAddress VARCHAR(255),
CHANGE COLUMN BP PatientBP INT);
—OR
MRS.SWATI PRASAD PATREKAR 4 COPA_ITI_KARAD
33-ALTER TABLE Patients
RENAME COLUMN Address TO PatientAddress;
–OR
34-ALTER TABLE Patients (
RENAME COLUMN Address TO PatientAddress,
RENAME COLUMN BP TO PatientBP);
35-CREATE TABLE Patients
PatientID int PRIMARY KEY,
PatientName varchar(255),
Sex varchar(255),
Age int,
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
RegDate date
);
36-CREATE TABLE Doctors
DoctorID int PRIMARY KEY,
DoctorName varchar(255),
CONSTRAINT DocPatient FOREIGN KEY (DoctorID)
MRS.SWATI PRASAD PATREKAR 5 COPA_ITI_KARAD
REFERENCES PatientID
);
37-CREATE TABLE Patients
PatientID int UNIQUE,
PatientName varchar(255),
Sex varchar(255),
Age int,
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
RegDate date
);
38-CREATE TABLE Patients
PatientID int NOT NULL,
PatientName varchar(255),
Sex varchar(255) NOT NULL,
Age int,
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
MRS.SWATI PRASAD PATREKAR 6 COPA_ITI_KARAD
RegDate date
);
39-CREATE TABLE Patients
PatientID int,
PatientName varchar(255),
Sex varchar(255,
Age int CHECK (Age >=20),
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
RegDate date
);
40-CREATE TABLE Patients
PatientID int,
PatientName varchar(255),
Sex varchar(255),
Age int DEFAULT ‘0’,
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
MRS.SWATI PRASAD PATREKAR 7 COPA_ITI_KARAD
RegDate date
);
41-CREATE VIEW Kids
PatientID int,PatientName varchar(255),Sex varchar(255),Age int
FROM Patients
WHERE Age <=18;
);
42-CREATE OR REPLACE VIEW Kids
PatientID int,PatientName varchar(255),Sex varchar(255),Age int
FROM Patients
WHERE Age <=18;
);
43-DROP VIEW Kids;
44-SELECT PatientName, State FROM Patients;
SELECT * FROM Patients;
MySQL Clauses
45-SELECT PatientName, State FROM Patients WHERE Age > 20;
46-SELECT DISTINCT State FROM Patients;
47-SELECT PatientID, State FROM Patients;
48-SELECT * FROM Patients
-ORDER BY Age;
SELECT * FROM Patients
MRS.SWATI PRASAD PATREKAR 8 COPA_ITI_KARAD
-ORDER BY Age DESC;
-SELECT * FROM Patients
ORDER BY Age, PatientName;
-SELECT * FROM Patients
ORDER BY Age DESC, PatientName ASC;
49-SELECT COUNT(PatientID), State
FROM Patients
GROUP BY State
ORDER BY COUNT(PatientID);
50-SELECT COUNT(PatientID), State
FROM Patients
GROUP BY State
ORDER BY COUNT(Age) > 40;
-Use of Operators
51-SELECT * FROM Patients
WHERE State='Telangana' AND Age > 23;
52-SELECT * FROM Patients
WHERE State='Telangana' OR Age > 23;
53-SELECT * FROM Patients
WHERE NOT State='Telangana';
53-SELECT * FROM Patients
WHERE Age BETWEEN 15 AND 18;
54-SELECT * FROM Patients
WHERE PatientName LIKE ‘A%’;
MRS.SWATI PRASAD PATREKAR 9 COPA_ITI_KARAD
55-SELECT * FROM Patients
WHERE State IS NULL;
56-SELECT * FROM Patients
WHERE State IS NOT NULL;
57-SELECT * FROM Patients
WHERE State IN (‘Rajasthan’, ‘Telangana’);
58-SELECT PatientName
FROM Patients
WHERE EXITS (SELECT Sex FROM Patients WHERE PatientID =1 OR Age<35);
59-SELECT PatientName
FROM Patients
WHERE PatientID = ALL (SELECT PatientID FROM Patients WHERE Age < 35);
60-SELECT PatientName
FROM Patients
WHERE PatientID = ANY (SELECT PatientID FROM Patients WHERE BETWEEN 20 AND 35);
61-SELECT PatientName
FROM Patients
WHERE Age <> ‘35’;
–OR
62-SELECT PatientName
FROM Patients
WHERE Age != ‘35’;
-AGGREGATE FUNCTIONS
63-SELECT SUM(Age)
FROM Patients;
MRS.SWATI PRASAD PATREKAR 10 COPA_ITI_KARAD
64-SELECT AVG(Age)
FROM Patients;
65-SELECT MIN(Age)
FROM Patients;
66-SELECT MAX(Age)
FROM Patients;
67-SELECT COUNT(Age)
FROM Patients;
68-SELECT PatientID, Age
FROM Patients
LIMIT 1;
– Select first 3 records
SELECT PatientID, Age
FROM Patients
LIMIT 3;
69-SELECT PatientID, Age
FROM Patients
ORDER BY PatientID DESC
LIMIT 1;
-SET OPERATIONS
70-SELECT PatientID FROM Patients
UNION
SELECT PatientID FROM Patients2;
71-SELECT PatientID FROM Patients
MRS.SWATI PRASAD PATREKAR 11 COPA_ITI_KARAD
UNION ALL
SELECT PatientID FROM Patients2;
72-SELECT PatientID FROM Patients
INTERSECT
SELECT PatientID FROM Patients2;
73-SELECT PatientID FROM Patients
MINUS
SELECT PatientID FROM Patients2;
-Joins
74-SELECT Diseases.DiseaseID, Patients.PatientName
FROM Diseases
LEFT JOIN Patients ON Diseases.PatientID = Patients.PatientID
ORDER BY Diseases.DiseaseID;
75-SELECT Diseases.DiseaseID, Patients.PatientName
FROM Diseases
RIGHT JOIN Patients ON Diseases.PatientID = Patients.PatientID
ORDER BY Diseases.DiseaseID;
76-SELECT Diseases.DiseaseID, Patients.PatientName
FROM Diseases
INNER JOIN Patients ON Diseases.PatientID = Patients.PatientID;
77-SELECT Diseases.DiseaseID, Patients.PatientName
FROM Diseases
FULL OUTER JOIN Patients ON Diseases.PatientID = Patients.PatientID;
MRS.SWATI PRASAD PATREKAR 12 COPA_ITI_KARAD
-Indexes
78-CREATE INDEX SampleIndex ON Patients;
79-ALTER INDEX SampleIndex ON PatName;
80-DROP INDEX SampleIndex;
-Data Control Commands
81-GRANT CREATE ANY TABLE TO localhost;
82-REVOKE INSERT ON *.* FROM Patients;
-Triggers
Triggers are a set of SQL statements that are invoked automatically in response to an event. Every
trigger, associated with a table, is activated by DML commands such as INSERT, UPDATE, and
DELETE.
There are two types of triggers:
Row-Level Trigger: Activated for each row, affected by INSERT, UPDATE or DELETE
commands.
Statement-Level Trigger: Activated for each of the events which occurs, irrespective of any of the
DML commands.
Types of Actions in Triggers
Before Insert: Activated before the data is inserted into the table.
After Insert: Activated after the data is inserted into the table..
Before Delete: Activated before the removal of data from the table.
After Delete: Activated after the removal of data from the table.
Before Update: Activated before the data being updated in the table.
After Update: Activated after the data being updated in the table
83. CREATE TRIGGER
Used to create a trigger in MySQL.
Syntax:
MRS.SWATI PRASAD PATREKAR 13 COPA_ITI_KARAD
CREATE TRIGGER TriggerName
(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON TableName FOR EACH ROW
BEGIN
--Declarations
--Trigger Code
END;
-TCL-Transaction Control Commands
Transaction commands deal with all the transactions related to the database. The commands are:
COMMIT
ROLLBACK
SAVEPOINT
RELEASE SAVEPOINT
SET TRANSACTION
84. COMMIT
Used to save all the transactions of the database since the last COMMIT or ROLLBACK command.
Syntax:
COMMIT;
Example:
DELETE FROM Patients WHERE Age > 45;
COMMIT;
85. ROLLBACK
Used to undo all the transactions since the last COMMIT or ROLLBACK.
Syntax:
ROLLBACK;
Example:
DELETE FROM Patients WHERE Age > 45;
MRS.SWATI PRASAD PATREKAR 14 COPA_ITI_KARAD
ROLLBACK;
86. SAVEPOINT
Used to roll the transaction back to a certain point without actually rolling back the entire transaction.
Syntax:
--Save the SAVEPOINT
SAVEPOINT SAVEPOINTNAME;
--Rollback to the Savepoint
ROLLBACK TO SAVEPOINTNAME;
Example:
SAVEPOINT EX1;
DELETE FROM Patients WHERE Age > 45;
SAVEPOINT EX2;
87. RELEASE SAVEPOINT
Used to remove a SAVEPOINT created previously.
Syntax:
RELEASE SAVEPOINT SAVEPOINTNAME;
Example:
RELEASE SAVEPOINT EX1;
88. SET TRANSACTION
Used to give a name to the transaction.
Syntax:
SET TRANSACTION [ READ WRITE | READ ONLY ];
MRS.SWATI PRASAD PATREKAR 15 COPA_ITI_KARAD
