What is Data?

Data is a collection of information gathered by observations, measurements, research or analysis.

What is Database?

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS).

What is MySQL?

SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

Features of MySQL

What is RDBMS?

RDBMS stands for Relational Database Management System.
RDBMS is a program used to maintain a relational database.
RDBMS is the basis for all modern database systems such as MySQL, Microsoft SQL Server, Oracle, and Microsoft Access.
RDBMS uses SQL queries to access the data in the database.
.

Advantage that MySQL offers over other RDBMS

Real World Websites where database are used

Licensing of MySQL

MySQL Create Database

A database is used to store the collection of records in an organized form. It allows us to hold the data into tables, rows, columns, and indexes to find the relevant information frequently. We can access and manage the records through the database very easily.

Syntax to create database:-

create database databasename;

MySQL SELECT Database

SELECT Database is used in MySQL to select a particular database to work with. This query is used when multiple databases are available with MySQL Server.You can use SQL command USE to select a particular database.

Syntax to use database:-

use databasename;

MySQL Show/List Databases

We can list all the databases available on the MySQL

Syntax to Show/List database:-

show databases;

MySQL DROP Database

We can drop/delete/remove a MySQL database quickly with the MySQL DROP DATABASE command. It will delete the database along with all the tables, indexes, and constraints permanently. Therefore, we should have to be very careful while removing the database in MySQL because we will lose all the data available in the database. If the database is not available in the MySQL server, the DROP DATABASE statement throws an error.

Syntax to DROP database:-

drop database databasename;

MySQL Data Types

Data types are used to represent the nature of the data that can be stored in the database table.

Data types mainly classified into three categories for every database.

  • String Data types
  • Numeric Data types
  • Date and time Data types

MySQL String Data Types


Name Description
CHAR(Size) It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1.
VARCHAR(Size) It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters.
TEXT(Size) It holds a string that can contain a maximum length of 255 characters.
ENUM(val1, val2, val3,...) It is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted.
SET( val1,val2,val3,....) It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list.

MySQL Numeric Data Types


INT(size) It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and unsigned range varies from 0 to 4294967295. The size parameter specifies the max display width that is 255.
DECIMAL(size, d) It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by d parameter. The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0.

MySQL Date and Time Data Types


DATE It is used to specify date format YYYY-MM-DD. Its supported range is from '1000-01-01' to '9999-12-31'.
DATETIME(fsp) It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from '1000-01-01 00:00:00' to 9999-12-31 23:59:59'
TIME(fsp) It is used to specify the time format. Its format is hh:mm:ss. Its supported range is from '-838:59:59' to '838:59:59'
YEAR It is used to specify a year in four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000.

MySQL CREATE TABLE

CREATE TABLE statement is used to create table in a database.If you want to create a table, you should name the table and define its column and each column's data type.

Syntax to CREATE TABLE:-

create table "tablename"("column1" "data type","column2" "data type","column3" "data type",..."columnN" "data type");

MySQL INSERT STATEMENT

SQL INSERT statement is a SQL query. It is used to insert a single or a multiple records in a table.There are two ways to insert data in a table:

Syntax to INSERT STATEMENT:-

  • By specifying column names
    • INSERT INTO table_name (column1, column2, column3....)VALUES (value1, value2, value3.....);

  • Without specifying column names
    • INSERT INTO table_name VALUES (value1, value2, value3....);

  • From Multiple values
    • INSERT INTO table_name VALUES (value1, value2, value3....),(value1, value2, value3....),(value1, value2, value3....)...n;<

MySQL SELECT Statement

The SELECT statement is the most commonly used command in Structured Query Language. It is used to access the records from one or more database tables and views. It also retrieves the selected data that follow the conditions we want.

Syntax to SELECT Statement column wise:-

SELECT Column_Name_1, Column_Name_2, ....., Column_Name_N FROM Table_Name;

Syntax to SELECT Statement all columns:-

SELECT * FROM Table_Name;