MySQL ENUM
The ENUM data type in MySQL is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, which means that each column may have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to represent string values.
CREATE TABLE shirts (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35),
size ENUM('small', 'medium', 'large', 'x-large')
);
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35),
size ENUM('small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts(id, name, size)
VALUES (1,'t-shirt', 'medium'),
(2, 'casual-shirt', 3),
(3, 'formal-shirt', 4),
(4, 'polo-shirt', 'small');
VALUES (1,'t-shirt', 'medium'),
(2, 'casual-shirt', 3),
(3, 'formal-shirt', 4),
(4, 'polo-shirt', 'small');
Types of Table in SQL
MySQL supports two types of tables
- Transaction-safe tables:-enables you to execute transactions or data manipulation commands without losing data.You can undo the changes made to transaction-safe tables
- Non Transaction-safe tables:-also allow you to execute transaction or data manupulation commands,However you can not undo the changes made to non-transaction safe tables because the changes are permanent
Command for the transaction safe tables
- Commit:-The COMMIT statement saves all the modifications made in the current transaction since the last commit or the START TRANSACTION statement.
- Rollback:-The ROLLBACK operation undoes all the changes done by the current transaction i.e. If you invoke this statement, all the modifications are reverted until the last commit or the START TRANSACTION statement.
MySQL saves the changes done after the execution of each statement. To save changes automatically, set the autocommit option as shown below:-
SET autocommit=0;
Assume we have created a table with name Players in MySQL database using CREATE statement as shown below:-
CREATE TABLE Players(
ID INT,
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Date_Of_Birth date,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255),
PRIMARY KEY (ID)
);
Now, we will insert 7 records in Players table using INSERT statements:-
insert into Players values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into Players values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into Players values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into Players values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into Players values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into Players values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into Players values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');
Following query saves the changes:-
COMMIT;
Now, let us delete the last record from the table:-
Delete from Players where id = 7;
You can retrieve the contents of the above create table using the SELECT statement as shown below:-
SELECT * FROM Players;
Following statement reverts the changes after the last commit.
ROLLBACK;
All the changes done past the last commit will be reverted if we rollback a transaction. Since we have deleted the 7th record after setting the commit, this delete is reverted at the time of roll back.
The SQL WHERE Clause
The WHERE clause is used to filter records.It is used to extract only those records that fulfill a specified condition.
Syntax:-
SELECT column1, column2.. FROM table_name WHERE condition;
SQL DELETE Statement
The DELETE statement is used to delete existing records in a table
Syntax:-
DELETE FROM table_name WHERE condition;
SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.
Syntax:-
UPDATE table_name SET column1 = value1, column2 = value2..WHERE condition;
SQL | DESCRIBE Statement
As the name suggests, DESCRIBE is used to describe something. Since in a database, we have tables, that’s why do we use DESCRIBE or DESC(both are the same) commands to describe the structure of a table.
Syntax:-
DESCRIBE one;
MySQL - SHOW STATUS Statement
The SHOW STATUS Statement displays the name and values of variables that gives you information about the server status. This statement has GLOBAL and SESSION modifier by specifying them you can retrieve server and session information using these.
Syntax:-
Show status;
MySQL - SHOW VARIABLES Statement
The SHOW VARIABLES Statement is used to display names and values of MySQL system variables.
Syntax:-
Show variables;