Apply for Zend Framework Certification Training

Mysql


< First Create table in mysql >



MySQL Notes: DML and Other SQL Language Categories
SQL (Structured Query Language) is used to interact with MySQL databases. It is divided into different categories based on the type of operation performed.
1. DDL (Data Definition Language)
Definition:
DDL commands are used to define, modify, and remove database structures such as databases, tables, indexes, and views.
Common DDL Commands
Command Purpose
CREATE Creates database or table
ALTER Modifies table structure
DROP Deletes database or table
TRUNCATE Removes all records from a table
RENAME Renames database objects
Example
Create Database
CREATE DATABASE College;
Use Database
USE College;
Create Table
CREATE TABLE Student(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);
Alter Table
ALTER TABLE Student
ADD city VARCHAR(30);
Rename Table
RENAME TABLE Student TO Students;
Drop Table
DROP TABLE Students;
Truncate Table
TRUNCATE TABLE Students;
2. DML (Data Manipulation Language)
Definition
 
DML commands are used to insert, modify, delete, and retrieve data stored in tables.
 
Common DML Commands
Command Purpose
INSERT Add new records
UPDATE Modify existing records
DELETE Remove records
SELECT Retrieve records
INSERT Command
Syntax
INSERT INTO table_name(column1,column2,...)
VALUES(value1,value2,...);
Example
INSERT INTO Student(id,name,age)
VALUES(1,'Raj',20);
 
Insert Multiple Rows
 
INSERT INTO Student
VALUES
(2,'Aman',21),
(3,'Simran',19),
(4,'Priya',22);
SELECT Command
Display All Records
SELECT * FROM Student;
Display Specific Columns
SELECT name,age
FROM Student;
Using WHERE
SELECT *
FROM Student
WHERE age>20;
ORDER BY
 
Ascending
 
SELECT *
FROM Student
ORDER BY age;
 
Descending
 
SELECT *
FROM Student
ORDER BY age DESC;
LIMIT
SELECT *
FROM Student
LIMIT 3;
UPDATE Command
Syntax
UPDATE table_name
SET column=value
WHERE condition;
Example
UPDATE Student
SET age=23
WHERE id=2;
 
Update Multiple Columns
 
UPDATE Student
SET age=22,
city='Delhi'
WHERE id=1;
DELETE Command
Syntax
DELETE FROM table_name
WHERE condition;
Example
 
Delete One Record
 
DELETE FROM Student
WHERE id=3;
 
Delete All Records
 
DELETE FROM Student;
WHERE Clause Operators
Operator Meaning
= Equal
!= or <> Not Equal
> Greater Than
< Less Than
>= Greater Than Equal
<= Less Than Equal
BETWEEN Range
IN Multiple Values
LIKE Pattern Matching
IS NULL Check NULL
 
Example
 
SELECT *
FROM Student
WHERE age BETWEEN 18 AND 22;
LIKE Operator
 
Starts with R
 
SELECT *
FROM Student
WHERE name LIKE 'R%';
 
Ends with a
 
SELECT *
FROM Student
WHERE name LIKE '%a';
 
Contains "am"
 
SELECT *
FROM Student
WHERE name LIKE '%am%';
Aggregate Functions
Function Description
COUNT() Counts records
SUM() Total
AVG() Average
MAX() Highest value
MIN() Lowest value
 
Example
 
SELECT AVG(age)
FROM Student;
GROUP BY
 
Groups rows with the same values.
 
Example
SELECT city,COUNT(*)FROM StudentGROUP BY city;
HAVING Used with GROUP BY.
SELECT city,COUNT(*) FROM Student GROUP BY city HAVING COUNT(*)>2;
 
3. DQL (Data Query Language)
Definition
DQL is used only for retrieving data.
Command
SELECT
Example
SELECT *
FROM Student;
 
4. DCL (Data Control Language)
Definition
DCL commands control user permissions and access.
Commands
Command       Purpose
GRANT            Give privileges
REVOKE          Remove privileges
Example
GRANT SELECT ON College.Student TO 'user1'@'localhost';
 
Remove Permission
REVOKE SELECT ON College.Student FROM 'user1'@'localhost';
 
5. TCL (Transaction Control Language)
Definition
TCL manages transactions in the database.
Commands
Command         Purpose
COMMIT           Save changes
ROLLBACK        Undo changes
SAVEPOINT       Create rollback point
COMMIT
COMMIT;
ROLLBACK
ROLLBACK;
SAVEPOINT
SAVEPOINT sp1;
Rollback to Savepoint
ROLLBACK TO sp1;
Commit Transaction
COMMIT;
Example Table
CREATE TABLE Student(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(30)
);
 
Insert Data
INSERT INTO Student
VALUES
(1,'Raj',20,'Delhi'),
(2,'Aman',21,'Mumbai'),
(3,'Priya',22,'Delhi'),
(4,'Simran',19,'Punjab'),
(5,'Rahul',23,'Delhi');
 
Summary Table
SQL Category Full Form                              Main Commands Purpose
DDL Data Definition Language              CREATE, ALTER, DROP, TRUNCATE, RENAME Defines database objects
DML Data Manipulation Language         INSERT, UPDATE, DELETE, SELECT Manipulates data
DQL Data Query Language                   SELECT Retrieves data
DCL Data Control Language                  GRANT, REVOKE Controls user permissions
TCL Transaction Control Language         COMMIT, ROLLBACK, SAVEPOINT Manages transactions

< First Create table in mysql >



Ask a question



  • Question: 
    {{questionlistdata.blog_question_description}}
    • Answer:
      {{answer.blog_answer_description  }}
    Replay to Question

(+91)-9910699802
Become A Member     Become A Teacher
Back to Top