Using MySQL-MK Tutorial

MySQL Unsigned Keyword

The “unsigned” in MySQL is a data type. Whenever we write an unsigned to any column that means you cannot insert negative numbers. Suppose, for a very large number you can use unsigned type.The maximum range with unsigned int is 4294967295.

create table UnsignedDemoWithPositiveValue (
Distance int unsigned;
);

MySQL DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column.The default value will be added to all new records, if no other value is specified.

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);

MySQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

MySQL NULL Values

A field with a NULL value is a field with no value.If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

MySQL AUTO_INCREMENT Keyword

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;

MySQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table.Primary keys must contain UNIQUE values, and cannot contain NULL values.A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);

SQL FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

CREATE TABLE customer (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name varchar(50) NOT NULL,
City varchar(50) NOT NULL,
);

create table contact(
id int,
customer_id int,
customer_info varchar(10),
primary key(customer_id),
foreign key(id) references customer(id)
);

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')
);

INSERT INTO shirts(id, name, size)
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.