Apply for Zend Framework Certification Training

Mysql


< What is Database 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

< What is Database 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