Complete guide to MySQL (Part 1)
Welcome to a series on MySQL! In this part of our series, we’ll be going over Data Definition Language and something that’s often overlooked, but important nonetheless — data types. But before we jump into the details lets understand what MySQL is!
- MySQL is a relational database management system based on the Structured Query Language, which is the popular language for accessing and managing the records in the database.
- MySQL is open-source and free software under the GNU license.
- It is supported by Oracle Company.
- It follows the working of Client-Server Architecture.
What is Database?
- It is an application that stores the organised collection of records.
- It allows us to organise data into tables, rows, columns, and indexes to find the relevant information very quickly.
- Examples : MySQL, Sybase, Oracle, MongoDB, PostgreSQL, SQL Serve
What can be done using MySQL?
- It allows us to implement database operations on tables, rows, columns, and indexes.
- It defines the database relationship in the form of tables (collection of rows and columns), also known as relations.
- It provides the Referential Integrity between rows or columns of various tables.
- It allows us to update the table indexes automatically.
- It uses many SQL queries and combines useful information from multiple tables for the end-users.
Fun Fact : “MySQL is named after the daughter of co-founder Michael Widenius whose name is “My”.”
Features of MySQL:
- Easy to use
- It’s secure
- Client-Server Architecture
- Free to download
- It is scalable
- Speed
- High flexibility
- Compatible on any operating system
- Allows rollback
- Memory efficiency
- High performance
- High productivity
- GUI support
But there are few disadvantages too!
- MySQL version less than 5.0 doesn’t support ROLE, COMMIT, and stored procedure.
- It does not support a very large database size as efficiently.
- It doesn’t handle transactions very efficiently, and it is prone to data corruption.
- It is accused that it doesn’t have a good developing and debugging tool compared to paid databases.
- It doesn’t support SQL check constraints.
Download MySQL from https://dev.mysql.com/downloads/installer/
Now lets talk about the various Data Types that are accepted in MySQL
Data Types:
- It specifies a particular type of data.
- It also identifies the possible values for that type, the operations that can be performed on that type, and the way the values of that type are stored.
Numeric data type:
- TINYINT
— signed (-128 to 127)
— unsigned (0 to 255)
— Takes 1 byte for storage
- SMALLINT
— signed (-32768 to 32767)
— unsigned (0 to 65537)
— Takes 2 bytes of storage
- BIGINT
— signed (-9223372036854775808 to 9223372036854775807)
— unsigned (0 to 18446744073709551615)
— Takes 8 bytes of storage
- FLOAT
— It is a floating-point number that cannot be unsigned.
— Takes 2 bytes for storage
- DOUBLE
— It is a double-precision floating-point number
— Cannot be unsigned
— Takes 8 bytes for storage
- DECIMAL
— An unpacked floating-point number that cannot be unsigned.
— In unpacked decimals, each decimal corresponds to one byte.
- BIT
— Used for storing bit values into the table column.
— Range of 1 to 64
- BOOL
— Used only for the true and false condition.
— It considers numeric value 1 as true and 0 as false.
- BOOLEAN
— Same as bool
Date and time data type
- YEAR[(2|4)] :
— Year value as 2 digits or 4 digits.
— The default is 4 digits.
— It takes 1 byte for storage.
- DATE:
— Displayed as ‘yyyy-mm-dd’.
— It takes 3 bytes for storage
- TIME:
— Displayed as ‘HH:MM:SS’.
— It takes 3 bytes plus fractional seconds for storage.
- DATETIME:
— Displayed as ‘yyyy-mm-dd hh:mm:ss’.
— It takes 5 bytes plus fractional seconds for storage.
- TIMESTAMP(m):
— Displayed as ‘YYYY-MM-DD HH:MM:SS’.
— It takes 4 bytes plus fractional seconds for storage.
String data type:
It is used to hold plain text and binary data
- CHAR(size):
— Size is the number of characters to store.
— Maximum size = 255 characters
— Fixed-length strings.
- VARCHAR(size):
— Variable-length string
— size is the number of characters to store.
— Maximum size = 255 characters
- TEXT(size):
— Maximum size of 65,535 characters.
- MEDIUMTEXT(size):
— Maximum size of 16,777,215 characters
- LONGTEXT(size):
— Maximum size of 4GB or 4,294,967,295 characters
- BINARY(size):
— Size is the number of binary characters to store.
— Fixed length strings
— Maximum upto 255 characters
- VARBINARY(size):
— Variable length string
— Maximum upto 255 characters
- ENUM:
— 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.
— takes 1 or 2 bytes
— maximum of 65,535 values.
- SET:
— It can hold zero or more, or any number of string values.
— They must be chosen from a predefined list of values specified during table creation.
— Takes 1, 2, 3, 4, or 8 bytes that depends on the number of set members.
— It can store a maximum of 64 members.
DATA DEFINITION LANGUAGE (DDL):
It deals with database schemas and descriptions of how the data should reside in the database.
- CREATE:
- To create a database and its objects like (table, index, views, store procedure, function, and triggers)
Query Syntax:
CREATE TABLE [IF NOT EXISTS] table_name(
col_1 datatype,
col_2 datatype,
……..,
table_constraints
);
Query:
Output:
Table Created
2. ALTER:
- The ALTER statement is always used with “ADD”, “DROP” and “MODIFY” commands according to the situation
ADD a column in the table
Query Syntax:
ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ];
Query:
Output:
Table Altered
- MODIFY column in the table
Query Syntax:
ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name ];
Query:
Output:
Table Altered
- DROP column in table
Query Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
Query:
Output:
Table Altered
- RENAME table
Query Syntax:
ALTER TABLE table_name
RENAME TO new_table_name;
Query:
Output:
Table Altered
- TRUNCATE:
Remove all records from a table, including all spaces allocated for the records are removed
Query Syntax:
TRUNCATE TABLE TABLE_NAME;
- We first insert values into the table to check this query:
Query for inserting values:
Output:
- Now let’s check the data in the table:
Output:
- Truncating the table:
Query:
Output:
Table Truncated
- Let’s check the data in the table again
Output:
No Data Found
Here the data in the table is lost but not the table itself.
Summing up , we had a brief look at the concept of MySQL , the data types and the DDL queries . I hope this blog was worth reading and helpful in gaining knowledge about MySQL.
Stay connected to learn more !
Written By - Varsha Gajula
