Exploring MySQL

MySQL is an open source RDBMS that uses intuitive keywords such as SELECT, INSERT INTO, and DELETE to communicate with the database. These keywords are used in queries that instruct the server on how to handle data, how to read and write the data, or to perform operations on the database objects or the server, such as creating or modifying tables, stored procedures, functions, and views. The database objects are defined and manipulated using SQL commands and all communication and instructions issued to the database by the client applications are done using SQL code.

MySQL has a wide range of applications in business. This includes data warehousing, inventory management, logging user sessions on web pages, and storing employee records.

MySQL is based on the client-server model. The client-server model makes it possible for MySQL to handle concurrent connections from multiple users and host a great number of databases, each with their own tables and fine-tuned security permissions to ensure the data is only accessed by the appropriate users.

In the next section, you will explore some of the data types that are used in MySQL for storing data.

Data types

Each column in a database table requires a data type to identify the type of data that will be stored in it. MySQL uses the assigned data type to determine how it will work with the data.

In MySQL version 8.0, there are three main data types. These data types are known as string, numeric, and date and time. The following table describes these types in more detail.

• string: Strings are text-based representations of data. There are various types of string data types, including CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. These data types can represent data from single text characters in CHAR types to full strings of text in VARCHAR types. The size of string variables can vary from 1 byte to 4 GB, depending on the type and size of the data being stored. To learn more about these data types, you can visit https://dev.mysql.com/doc/refman/8.0/en/string-types.html.
• numeric: Numeric data types store numeric values only. There are various types of numeric data, including INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT, DECIMAL, NUMERIC, FLOAT, DOUBLE, and BIT. These data types can represent numbers of various formats. Types such as DECIMAL and FLOAT represent decimal values, whereas INTEGER types can only represent integer values. The size range stored is dependent on the numeric data type assigned to the field and can range from 1 to 8 bytes, depending on whether the data is signed, and whether the type supports decimal values. To learn more about these data types, you can visit https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html.
• date and time: There are five date and time data types: Date, Time, Year, DateTime, and TimeStamp. Date, Time, and Year store different components of date in separate columns, DateTime will record a combined date and time, and Timestamp will indicate how many seconds have passed from a fixed point in time. Date-based data types typically take up around 8 bytes in size, depending on whether they store the time as well as the date. Visit the following link for further details: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html.

As the developer, it is your responsibility to select the appropriate data type and size for the information you will be storing in the column. If you know a field is only going to use 5 characters, define its size as 5.

In the next exercise, you will learn how to organize a set of data in a relational format, with proper data types for each field.