MYSQL

  • date 5th January, 2021 |
  • by Prwatech |
  • 0 Comments

Before we get started let’s understand what is a database, A database is a separate application that stores and keep a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds.

Other kinds of data stores can also be used, such as files on the file system or large hash tables in memory but data fetching and writing would not be so fast and easy with those type of systems.

Nowadays, we use relational database management systems (RDBMS) to store and manage huge volume of data. This is called relational database because all the data is stored into different tables and relations are established using primary keys or other keys known as Foreign Keys.

What is MySql? It is currently the most popular database management system software used for managing the relational database, It is commonly used in conjunction with PHP scripts for creating powerful and dynamic server-side or web-based enterprise applications.

 It is open-source database software, which is supported by Oracle Company. It is fast, scalable, and easy to use database management system in comparison with Microsoft SQL Server and Oracle Database.

How it works?

MySQL follows the working of Client-Server Architecture. This model is designed for the end-users called clients to access the resources from a central computer known as a server using network services. Here, the clients make requests through a graphical user interface (GUI), and the server will give the desired output as soon as the instructions are matched. The process of MySQL environment is the same as the client-server model.

MySQL creates a database that allows you to build many tables to store and manipulate data and defining the relationship between each table.

MySQL is an open-source database, so you don’t have to pay any amount. It is a very powerful program that can handle a large set of functionality of the most expensive and powerful database packages. It’s customizable because it is an open-source database, and the open-source GPL license facilitates programmers to modify the SQL software according to their own specific environment, quicker than other databases, so it can work well even with the large data set. It can support many operating systems with many languages like PHP, JAVA, etc. MySQL uses a standard form of the well-known SQL data language.

SQL Data Types for MySQL

The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.

Each column in a database table is required to have a name and a data type.

An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

Each column in a database table is required to have a name and a data type.

An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

SQL Data Types for MySQL

Data typeDescription
CHAR(size)A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters – can be from 0 to 255. Default is 1
VARCHAR(size)A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum column length in characters – can be from 0 to 65535
BINARY(size)Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1
VARBINARY(size)Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes.
TINYBLOBFor BLOBs (Binary Large OBjects). Max length: 255 bytes
TINYTEXTHolds a string with a maximum length of 255 characters
TEXT(size)Holds a string with a maximum length of 65,535 bytes

MYSQL BASIC COMMANDS

Create a database

create database Prwatech;

List all database on the server

show databases;

Switch to a database

use prwatech;

Create a table in the database

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

To see all the tables in the database

show tables;

To see a table field format

describe persons;

To delete a database

drop database sys;

To delete a table in the database

drop table persons;

To see the column information in the table of the database

show columns from Institute;

Inserting information to a table in the database;

INSERT INTO Institute (Course, Duration, Fees, Name, Country, Postalcode)

VALUES (‘Big Data’, ‘6 months’, ‘30000’, ‘George’, ‘India’,’560039′);

Adding a column into a table

ALTER TABLE Institute

-> ADD Email varchar(255);

To delete a column in a table

ALTER TABLE Institute

-> DROP COLUMN Email;

The following SQL ensures that the “ID”, “LastName”, and “FirstName” columns will NOT accept NULL values when the “Persons” table is created:

The NOT NULL constraint enforces a column to NOT accept NULL values.

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

show columns from persons;

SQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

ALTER TABLE Persons

-> ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

SQL Check on ALTER TABLE

To create a CHECK constraint on the “Age” column when the table is already created, use the following SQL:

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons

-> ADD CHECK (Age>=33);

SQL Default on ALTER TABLE

To create a DEFAULT constraint on the “City” column when the table is already created, use the following SQL:

ALTER TABLE training

-> ALTER CITY SET DEFAULT ‘Bangalore’;

SQL CREATE INDEX STATEMENT

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Quick Support

image image