MySQL Commands, SQL Tutorial

 

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.