Introduction to MySQL

Introduction of MySQL

Welcome to SQL Course. You will learn introduction to SQL, history of SQL, Uses of SQL and much more. Let’s start!!!

What is SQL?

SQL, or Structured Query Language, is a specialized language used to manage, manipulate, and retrieve data stored in relational databases.

SQL stands for Structured Query Language which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL was developed in the 1970s by IBM Computer Scientists and became a standard of the American National Standards Institute (ANSI) in 1986, and the International Organization for Standardization (ISO) in 1987.

SQL is the standard language to communicate with Relational Database Systems. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their Standard Database Language.

Why SQL?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

A Brief History of SQL 

• 1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He described a relational model for databases.
• 1974 − Structured Query Language (SQL) appeared.
• 1978 − IBM worked to develop Codd's ideas and released a product named System/R.
• 1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle.
• 1987 − SQL became the part of the International Organization for Standardization (ISO).

How MySQL Works? 

MySQL, as a popular open-source relational database management system, operates through a client-server architecture:

Client: Applications or users interact with MySQL by sending queries or commands to the MySQL server.

Server: The MySQL server manages and processes these queries, handling the data storage, retrieval, manipulation, and security.

Key components and functionalities of MySQL:

Storage Engine: MySQL supports multiple storage engines that manage how data is stored and accessed. Examples include InnoDB (default), MyISAM, and more, each with its own features and optimizations.

SQL Interface: MySQL uses SQL (Structured Query Language) to communicate between clients and the server. Users or applications can send queries using SQL statements to perform various operations like INSERT, SELECT, UPDATE, DELETE, etc.

Parsing and Optimizing: When a query is received, MySQL parses and optimizes it. Parsing involves understanding the query structure, while optimization involves choosing the most efficient way to execute the query based on indexes, available resources, and algorithms.

Execution: The optimized query is executed by the MySQL server. If data manipulation is requested (like SELECT or UPDATE), the server fetches, modifies, or deletes the data as required.

Data Storage: MySQL stores data in databases, which consist of tables that organize data in rows and columns. Each database can contain multiple tables, and these tables, in turn, can be related to each other through defined relationships.

Concurrency and Locking: MySQL handles multiple user connections concurrently. To ensure data integrity and prevent conflicts between simultaneous transactions, it uses various locking mechanisms.

Security: MySQL offers security features such as user authentication, access control, encryption, and auditing to protect data from unauthorized access or modification.

Replication and Backup: MySQL supports replication, allowing data to be copied and synchronized across multiple servers for redundancy and scalability. It also provides mechanisms for backups to ensure data recovery in case of failures.

What is Database

 The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, and reports, etc.

 

For example: The college Database organizes the data about the admin, staff, students and faculty etc.

Using the database, you can easily retrieve, insert, and delete the information. 

Types of databases: Relational vs non-relational

Database Management System

• Database management system is a software which is used to manage the database. For example: MySQL, Oracle, etc are a very popular commercial database which is used in different applications.
•  
• DBMS provides an interface to perform various operations like database creation, storing data in it, updating data, creating a table in the database and a lot more.
•  
• It provides protection and security to the database. In the case of multiple users, it also maintains data consistency.

 Database Management Terminology

Data: Data is a collection of Raw facts that can be recorded and it should be abstract in nature. Example: student marks, customer names.

Information: Data with some added meaning is called information. Example: student mark sheet, invoice of a customer.

Record: Record is a collection of logically related data or it is a line of data in a table about one item. Example: In the diary database there would be a Person table and each person would have a row (a record) for them.

For each person we may want to have the following fields (items of data) available; Forename, Family Name, Birthday, Mobile. These items of data would make up what we want to know about someone.  

Database: The database is a collection of records or a collection of logically related data.

DBMS: DBMS is a collection of logically related data and a set of programs to access those data

Application of DBMS:

Banking

Tele communications

Reservations Systems

Sales

Scientific Applications

 

The goal of DBMS: DBMS is used for effective storage and retrieval of data from a database.

Rows/Record/Tuple:  A single row of tables is called a Record or Tuple.

Column/Attribute: A single column of the table is called an attribute. Attributes are also called characteristics. The characteristics of the tuple are represented by attributes or fields. 

Degree of a Relation: The number of columns/attributes in a Table/Relation is called the degree of relation.

Cardinality: The number of Rows/ Records/ Tuples in a Table/Relation is called Cardinality.

Table/Relation:  Table/Relation is a Two-dimensional array of data and it can’t contain duplicate rows. For example, we might want to keep information on the people we saw today. We would, therefore, create a Person table to hold the information about each person. We could also create tables to hold the information we want on Date, Activity, Weather, and TV.

Domain: A domain is a collection of all possible values of one or more attributes.

Field: The field is a piece of information. There is no rule to tell you whether a data is one field or three. It depends on what information you want to get out. However, it is usual to hold dates as one field and then extract the bits you want when you need them.

Relationship: These are the links between tables in relational databases. For example, dates and activities are liked (an activity happens on a particular day). However, there is no logical link between, say, TV and weather. These two tables would be kept disconnected (there is no relationship between them).

Primary and Foreign Keys: The links are created by adding Primary Keys and Foreign keys to the tables. The primary key goes at the one end of a relationship. The foreign key goes at the many end. For example, in the data and weather table, each record in the weather table would have the Primary key and any records in the data table that had the same weather would have the same code.

Functions of DBMS:

• It must give facility for defining the database structure also specifies access rights to authorized users.
• The DBMS must-have functions like insertion of record into database updation of data, deletion of data, retrieval of data
• The DBMS must share data items for multiple users by maintaining consistency of data.
• It must protect the database against unauthorized users.
• If for any reason the system fails DBMS must facilitate database recovery.

Advantages of DBMS:

 Program Data Independence: If a database approach is used, data is stored in a central location called a repository. The process of the database allows an organization’s data to change the database without modifying the application programs which are able to process this data. 

Minimal Data Redundancy: Data redundancy exists when the same data are stored unnecessarily at different places. The database approach does not eliminate redundancy completely, but it provides the facilities to the designer to carefully control the amount of redundancy.

Improved Data Consistency: If the amount of data redundancy is controlled, it will reduce the data inconsistency also. It is also highly recommended to maintain the same version of data at all locations.

Improved Data Sharing: A database is designed as a sharable component. DBMS helps in creating an environment in which end users have better access to more data and better manages data. Users are allowed to utilize the services of the database by authentication and authorization.

Enforcement of Standards: To provide services to database management, every database administrator designs procedure & enforcement standards. Procedures are the instructions and rules that govern the design and use of a database system.

Improved Quality: The database approach provides an optimum number of tools & processes to improve data quality. Every data designer can specify a rule called integrity constraints which users can’t violate.

 

The Disadvantages of DBMS:

• DBMS software and hardware cost is high
• The processing overhead by the DBMS for implementation of security, integrity, and sharing of the data.
• Normalization is not possible in DBMS.
• Setup of the database system requires more knowledge, money, skills, and time.
• The complexity of the database may result in poor performance.

Schema and sub-schema:

 A schema is a logical database description. It represents the overall structure or design of the database. In other words, the plan of the database. The schema describes the name of entity and attributes and their relationships.

Schema is a framework in which values of data items are fitted. The plan or format of schema remains the same but the values of the data item may change from time to time.

RDBMS 

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows. 

 

MySQL Data Types

 In MySQL, a data type tells us what kind of information can be stored in a table's columns. It shows what values are allowed, what actions can be done with those values, and how they are kept in the database. Each column in a table has its own data type, which decides what sort of data it can hold in it. 

A data type defines the kind of data a variable can hold, such as integers, floating-point numbers, Booleans, etc. It also specifies what values are acceptable for that type, what operations can be applied to those values, and how the values are stored in memory. In MySQL, every table in a database consists of multiple columns, and each column has its own defined data type, indicating the type of information it can store.

This allows us to determine and manage the specific characteristics of data stored in MySQL tables.

• Varchar : Contains Alphabets, Numbers and Special       Symbols Represented in Single Quotes or Double Quotes
• Numeric Data: Without Quotes
• Int: +VE or -VE Numbers
• Float: = Fractional Numbers (Decimal, +VE, -VE)
• Decimal: Decimal Values