MySQL Commands:
1. SHOW DATABASES
Syntax:
SHOW DATABASES;Example:
SHOW DATABASES;Description: Displays the databases visible to your MySQL account.
2. CREATE DATABASE
Syntax:
CREATE DATABASE database_name;Example:
CREATE DATABASE School;Description: Creates a new database named School.
3. DROP DATABASE
Syntax:
DROP DATABASE database_name;Example:
DROP DATABASE School;Description: Deletes the School database, including its tables and their data.
4. USE
Syntax:
USE database_name;Example:
USE School;Description: Selects School as the current database for subsequent commands.
5. SHOW TABLES
Syntax:
SHOW TABLES;Example:
USE School;
SHOW TABLES;Description: Displays the tables in the current database.
6. CREATE TABLE
Syntax:
CREATE TABLE table_name (column1 datatype, column2 datatype, ... );Example:
CREATE TABLE Student (RollNo INT, Name VARCHAR(30), Marks INT);Description: Creates a table named Student with three columns:
|
Column |
Data type |
Purpose |
|
RollNo |
INT |
Stores an integer roll number |
|
Name |
VARCHAR(30) |
Stores a name of up to 30
characters |
|
Marks |
INT |
Stores integer marks |
7.1 INSERT INTO … VALUES
Syntax:
INSERT INTO table_name
VALUES (value1, value2, ...);Example:
INSERT INTO Student
VALUES (101, 'Aman', 85);Description: Inserts one record. Values must follow the order of the table’s columns.
7.2 INSERT INTO … VALUE
Syntax:
INSERT INTO table_name
VALUE (value1, value2, ...);Example:
INSERT INTO Student
VALUE (102, 'Riya', 92);Description: Inserts one record. In this MySQL INSERT syntax, VALUE and VALUES are synonyms.
7.3 INSERT with Column Names and VALUE
Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUE (value1, value2, ...);Example:
INSERT INTO Student (Name, RollNo, Marks)
VALUE ('Neha', 103, 88);Description: Inserts values according to the specified column order. Here, 'Neha' goes into Name, and 103 goes into RollNo.
7.4 INSERT with Column Names and VALUES
Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);Example:
INSERT INTO Student (RollNo, Name)
VALUES (104, 'Rahul');Description: Inserts values into the specified columns. In our table, the omitted Marks column receives NULL because it allows NULL and has no explicit default.
7.5 Insert Multiple Records with Column Names
Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES
(value1, value2, ...),
(value1, value2, ...);Example:
INSERT INTO Student (RollNo, Name, Marks)
VALUES
(105, 'Priya', 90),
(106, 'Mohit', 76),
(107, 'Kiran', 82);Description: Inserts multiple records using one command. Each parenthesised group represents one record.
7.6 Insert Multiple Records without Column Names
Syntax:
INSERT INTO table_name
VALUES
(value1, value2, ...),
(value1, value2, ...);Example:
INSERT INTO Student
VALUES
(108, 'Sonal', 95),
(109, 'Vikas', 68),
(110, 'Anjali', 89);Description: Inserts multiple records. Values in each record must follow the table’s column order.
8.1 SELECT All Columns, all rows
Syntax:
SELECT * FROM table_name;Example:
SELECT * FROM Student;Description: Retrieves all columns and all records from Student. The asterisk (*) represents all columns.
8.2 SELECT Specific Columns, all rows
Syntax:
SELECT column1, column2, ...
FROM table_name;Example:
SELECT Name, Marks
FROM Student;Description: Retrieves only the Name and Marks columns for all records.
8.3 SELECT All Columns, but specific rows with WHERE
Syntax:
SELECT *
FROM table_name
WHERE condition;Example:
SELECT *
FROM Student
WHERE Marks >= 80;Description: Retrieves all columns of students whose marks are 80 or more.
8.4 SELECT Specific Columns and specific rows with WHERE
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;Example:
SELECT RollNo, Name
FROM Student
WHERE Marks >= 90;Description: Retrieves only the roll numbers and names of students whose marks are 90 or more.
9.1 DESCRIBE
Syntax:
DESCRIBE table_name;Example:
DESCRIBE Student;Description: Displays the table’s structure, including column names, data types, NULL permissions, keys and default values.
9.2 DESC
Syntax:
DESC table_name;Example:
DESC Student;Description: A short form of DESCRIBE. It displays the same table structure.
10.1 UPDATE without WHERE
Syntax:
UPDATE table_name
SET column1 = new_value,
column2 = new_value;Example:
UPDATE Student
SET Marks = 100;Description: Changes Marks to 100 for every record because no WHERE condition is specified.
10.2 UPDATE with WHERE
Syntax:
UPDATE table_name
SET column1 = new_value,
column2 = new_value
WHERE condition;Example:
UPDATE Student
SET Name = 'Aman Sharma',
Marks = 95
WHERE RollNo = 101;Description: Changes the name and marks only for records whose RollNo is 101.
11.1 DELETE without WHERE
Syntax:
DELETE FROM table_name;Example:
DELETE FROM Student;Description: Deletes all records from the table. The table structure remains.
11.2 DELETE with WHERE
Syntax:
DELETE FROM table_name
WHERE condition;Example:
DELETE FROM Student
WHERE Marks < 33;Description: Deletes only the records whose marks are below 33.
12.1 TRUNCATE without TABLE Keyword
Syntax:
TRUNCATE table_name;Example:
TRUNCATE Student;Description: Removes all records while retaining the table structure. In MySQL, the TABLE keyword is optional.
12.2 TRUNCATE TABLE
Syntax:
TRUNCATE TABLE table_name;Example:
TRUNCATE TABLE Student;Description: Has the same effect as TRUNCATE Student;. A WHERE clause cannot be used with TRUNCATE.
13. DROP TABLE
Syntax:
DROP TABLE table_name;Example:
DROP TABLE Student;Description: Deletes the entire table, including its data and structure.
14.1 ALTER TABLE … ADD COLUMN
Syntax:
ALTER TABLE table_name
ADD COLUMN column_name datatype;Example:
ALTER TABLE Student
ADD COLUMN City VARCHAR(30);Description: Adds a new column named City to the table.
14.2 ALTER TABLE … ADD
Syntax:
ALTER TABLE table_name
ADD column_name datatype;Example:
ALTER TABLE Student
ADD Age INT;Description: Adds a new column named Age. The COLUMN keyword is omitted because it is optional.
14.3 ALTER TABLE … DROP COLUMN
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;Example:
ALTER TABLE Student
DROP COLUMN City;Description: Removes the existing City column and all data stored in that column.
14.4 ALTER TABLE … DROP
Syntax:
ALTER TABLE table_name
DROP column_name;Example:
ALTER TABLE Student
DROP Age;Description: Removes the existing Age column and its data. The COLUMN keyword is optional.
14.5 ALTER TABLE … MODIFY COLUMN
Syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;Example:
ALTER TABLE Student
MODIFY COLUMN Name VARCHAR(50);Description: Changes the maximum length of Name from 30 to 50 characters.
14.6 ALTER TABLE … MODIFY
Syntax:
ALTER TABLE table_name
MODIFY column_name new_datatype;Example:
ALTER TABLE Student
MODIFY Marks DECIMAL(5,2);
Description: Changes Marks
from INT to DECIMAL(5,2), allowing values such as 85.50. It permits five digits
in total, including two digits after the decimal point.
Note: MODIFY changes a
column’s definition, not its name. To retain attributes such as NOT NULL and
DEFAULT, include them in the new definition.
