Learn MySQL

This is a full MySQL course that covers SQL queries and installation of MySQL workbench.

1) SHOW databases

SHOW databases;

2)Create Database

CREATE database vishal;

3)Use Database - Selects the database where you will run all SQL commands

USE vishal;

4)Create a table using SQL Command:

CREATE table students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  ST_NAME VARCHAR(255) NOT NULL,
  Grade CHAR(2) DEFAULT 'A1',
  Roll_no INT NOT NULL UNIQUE KEY CHECK(Roll_no < 50)
);

5)Show table structure:

DESCRIBE students;

6)Insert Rows in table:

INSERT INTO students (ST_NAME,Grade,Roll_no) VALUES ("ThinkX Academy",'A1',1)

7)Update Rows in table:

UPDATE students SET ST_NAME="VISHAL" WHERE Roll_no=1

8)Delete Rows in table:

DELETE FROM students WHERE Roll_no=1

9)Alter Table Name

ALTER TABLE students
RENAME TO StudentInfo

10)Alter Column Roll_no from INT to VARCHAR

ALTER TABLE StudentInfo
MODIFY Roll_no VARCHAR(50)

11)Add a Column

ALTER TABLE StudentInfo
ADD (Attendance INT DEFAULT 0)

12)Drop a Column:

ALTER TABLE StudentInfo
Drop Attendance

13)Drop Table:

DROP StudentInfo

14)Display table contents using SELECT:

Select all rows with id less than 5:

SELECT * FROM StudentInfo WHERE id<5

15)Eliminating Redundant Data ,Using AND and <>(not)

SELECT DISTINCT Grade FROM StudentInfo WHERE id <> 5 AND id < 10

16)Selecting based on Range

SELECT ST_NAME FROM StudentInfo WHERE id BETWEEN 6 AND 10

17)Selecting NOT based on Range

SELECT ST_NAME FROM StudentInfo WHERE id NOT BETWEEN 6 AND 10

18)Condition based on list

SELECT ST_NAME FROM StudentInfo WHERE id IN (1,3,4)

19)Condition based on Pattern Matches

SELECT ST_NAME FROM StudentInfo WHERE ST_NAME LIKE "V%"

SELECT ST_NAME FROM StudentInfo WHERE ST_NAME NOT LIKE "V%"

SELECT ST_NAME FROM StudentInfo WHERE ST_NAME  LIKE "_____L"

20)Sorting Results:

SELECT ST_NAME FROM StudentInfo WHERE id <> 1 ORDER BY ST_NAME

20)Performing Simple Calculations:

Dual table is used in the following codes which is a dummy table consisting of one column and one row.

SELECT 5*3 FROM dual

SELECT sysdate FROM dual

SELECT lower("VISHAL") FROM dual

SELECT upper("thinkx academy") FROM dual

SELECT getdate() FROM dual

PRACTICE all of these SQL queries on MySQL Workbench and see the output.