MySQL create Database

               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.

MySQL implements a database as a directory that stores all files in the form of a table. It can be done by following way:

1. MySQL Workbench

MySQL Workbench

It is a visual database designing or GUI tool used to work with database architects, developers, and Database Administrators. This visual tool supports SQL development, data modeling, data migration, and comprehensive administration tools for server configuration, user administration, backup, and many more. It allows us to create new physical data models, E-R diagrams, and SQL development (run queries, etc.).

To create a new database using this tool, we first need to launch the MySQL Workbench and log in using the username and password that you want. It will show the following screen:

Now do the following steps for database creation:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the previously created databases. If we want to create a new database, right-click under the Schema menu and select Create Schema or click the database icon (red rectangle), as shown in the following screen.

2. The new Schema window screen open. Enter the new database name (for example, employeedb) and use default character set and collation. Now, click on the Apply button as shown in the screen below:

3. A new popup window appears. Click on the Apply button.

4. A new popup screen appears. Click on the Finish button to complete the database creation.

5. After successful database creation, we can see new databases in the Schema menu. If we do not see this, click on the refresh icon into the Schema menu.

6. We can see more information about the database by selecting the database and click on the ‘i’ icon. The information window displays several options, like Table, Triggers, Indexes, Users, and many more.

7. MySQL Workbench does not provide an option to rename the database name, but we can create, update, and delete the table and data rows from the database.

Variables in MySQL

                             Variables in MySQL

Variables are used for storing data or information during the execution of a program. It is a way of labeling data with an appropriate name that helps to understand the program more clearly by the reader. The main purpose of the variable is to store data in memory and can be used throughout the program.

MySQL can use variables in three different ways, which are given below:

1. User-Defined Variable
2. Local Variable
3. System Variable

User-Defined Variable

Sometimes, we want to pass values from one statement to another statement. The user-defined variable enables us to store a value in one statement and later can refer it to another statement. MySQL provides a SET and SELECT statement to declare and initialize a variable. The user-defined variable name starts with @ symbol.

The user-defined variables are not case-sensitive such as @name and @NAME; both are the same. A user-defined variable declares by one person cannot visible to another person. We can assign the user-defined variable into limited data types like integer, float, decimal, string, or NULL. The user-defined variable can be a maximum of 64 characters in length.

Syntax

The following syntax is used to declare a user-defined variable.

1. By using the SET statement

SET @var_name = value;  

2. By using the SELECT statement

SELECT @var_name := value;  

Local Variable

It is a type of variable that is not prefixed by @ symbol. The local variable is a strongly typed variable. The scope of the local variable is in a stored program block in which it is declared. MySQL uses the DECLARE keyword to specify the local variable. The DECLARE statement also combines a DEFAULT clause to provide a default value to a variable. If you do not provide the DEFAULT clause, it will give the initial value NULL. It is mainly used in the stored procedure program.

Syntax

We can use the DECLARE statement with the following syntax:

DECLARE variable_name datatype(size) [DEFAULT default_value];  

Let us see the following example to use the local variable.

Example

mysql> DECLARE total_price Oct(8,2) DEFAULT 0.0;

System Variable

System variables are a special class to all program units, which contains predefined variables. MySQL contains various system variables that configure its operation, and each system variable contains a default value. We can change some system variables dynamically by using the SET statement at runtime. It enables us to modify the server operation without stop and restart it. The system variable can also be used in the expressions.

MySQL server gives a bunch of system variables such as GLOBAL, SESSION, or MIX types. We can see the GLOBAL variable throughout the lifecycle of the server, whereas the SESSION variable remains active for a particular session only.

We can see the names and values of the system variable by using the following ways:

1. To see the current values used by the running server, execute the following command.

mysql> SHOW VARIABLES;  
  
OR,  
  
Mysql> SELECT @@var_name;  

Example

mysql> SHOW VARIABLES LIKE '%wait_timeout%'; 

Data Types in MySQL

                        Data Types in MySQL

Properly defining the fields in a table is important to the overall optimization of your database. You should use only the type and size of field you really need to use. For example, do not define a field 10 characters wide, if you know you are only going to use 2 characters. These type of fields (or columns) are also referred to as data types, after the type of data you will be storing in those fields.

MySQL uses many different data types broken into three categories −

• Numeric
• Date and Time
• String Types.

Let us now discuss them in detail.

Numeric Data Types

MySQL uses all the standard ANSI SQL numeric data types, so if you’re coming to MySQL from a different database system, these definitions will look familiar to you.

The following list shows the common numeric data types and their descriptions −

• INT − A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.
• TINYINT − A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.
• SMALLINT − A small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5 digits.
• MEDIUMINT − A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.
• BIGINT − A large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 20 digits.
• FLOAT(M,D) − A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT.
• DOUBLE(M,D) − A double precision floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE.
• DECIMAL(M,D) − An unpacked floating-point number that cannot be unsigned. In the unpacked decimals, each decimal corresponds to one byte. Defining the display length (M) and the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.

Date and Time Types

The MySQL date and time datatypes are as follows −

• DATE − A date in YYYY-MM-DD format, between 1000–01–01 and 9999–12–31. For example, December 30th, 1973 would be stored as 1973–12–30.
• DATETIME − A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000–01–01 00:00:00 and 9999–12–31 23:59:59. For example, 3:30 in the afternoon on December 30th, 1973 would be stored as 1973–12–30 15:30:00.
• TIMESTAMP − A timestamp between midnight, January 1st, 1970 and sometime in 2037. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ).
• TIME − Stores the time in a HH:MM:SS format.
• YEAR(M) − Stores a year in a 2-digit or a 4-digit format. If the length is specified as 2 (for example YEAR(2)), YEAR can be between 1970 to 2069 (70 to 69). If the length is specified as 4, then YEAR can be 1901 to 2155. The default length is 4.

String Types

Although the numeric and date types are fun, most data you’ll store will be in a string format. This list describes the common string datatypes in MySQL.

• CHAR(M) − A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1.
• VARCHAR(M) − A variable-length string between 1 and 255 characters in length. For example, VARCHAR(25). You must define a length when creating a VARCHAR field.
• BLOB or TEXT − A field with a maximum length of 65535 characters. BLOBs are “Binary Large Objects” and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data. The difference between the two is that the sorts and comparisons on the stored data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.
• TINYBLOB or TINYTEXT − A BLOB or TEXT column with a maximum length of 255 characters. You do not specify a length with TINYBLOB or TINYTEXT.
• MEDIUMBLOB or MEDIUMTEXT − A BLOB or TEXT column with a maximum length of 16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.
• LONGBLOB or LONGTEXT − A BLOB or TEXT column with a maximum length of 4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.
• ENUM − An enumeration, which is a fancy term for list. When defining an ENUM, you are creating a list of items from which the value must be selected (or it can be NULL). For example, if you wanted your field to contain “A” or “B” or “C”, you would define your ENUM as ENUM (‘A’, ‘B’, ‘C’) and only those values (or NULL) could ever populate that field.

Advantages and disadvantages of MySQL

            MySQL Advantages and                     Disadvantages

Advantages:

Like other relational database system, MySQL database server also has its advantages and disadvantages. Some of these advantages have been explained below.

Reduced Total Cost of Ownership

MySql one of the most popular open source database management system that allows you to manage relational database. Since MySql open source, you can use MySql free and if you want, you can tailor its source code according to your requirement. Most of the companies prefer MySQL because they don’t have to pay anything for this excellent product.

Portability

MySQL is cross platform database server. It can run on different plateform like Linux,Solaris and Windows etc. It is good choice for those project that target multiple platforms particularly web application. In fact, MySQL is a part of the famous LAMP (Linux Apache MySQL PHP) server stack which is used worldwide for web application development. MySql support many plateform with different languages like C, C++, PHP, PERL, JAVA, Python etc.

Seamless Connectivity

There are various secure and seamless connection mechanisms are available in order to connect with MySQL server. These connections include named pipes, TCP/IP sockets and UNIX Sockets.

Rapid Development and Round-the-Clock Uptime

MySQL comes with the assurance of 24x7 uptime and offers a wide range of high-availability solutions, including specialized cluster servers and master/slave replication configurations. MySQL has a very large developer community which releases regular patches and updates for MySQL.

Data Security

MySQL is globally recognize the most secure and reliable database management system used in popular web applications including WordPress, Drupal, Joomla, Facebook and Twitter. Data protected via password and good thing about these passwords is that they are stored in encrypted form and can not break these complex encryption algorithms.

Disadvantages:

MySQL database server has its disadvantages. Some of these disadvantages have been explained below.

1. MySQL lower version (5.0 or less) doesn’t support ROLE, COMMIT and stored procedure.
2. MySQL does not support a very large database size as efficiently.
3. MySQL doesn’t handle transactions very efficiently and it is prone to data corruption.
4. MySQL doesn’t have a good developing and debugging tool compared to other databases.
5. MySQL doesn’t support SQL check constraints.

Features of MySQL

                                  Features of MySQL

The following are the most important features of MySQL:

Relational Database Management System (RDBMS):

MySQL is a relational database management system. This database language is based on the SQL queries to access and manage the records of the table.

Easy to use:

MySQL is easy to use. We have to get only the basic knowledge of SQL. We can build and interact with MySQL by using only a few simple SQL statements.

It is secure:

MySQL consists of a solid data security layer that protects sensitive data from intruders. Also, passwords are encrypted in MySQL.

Client/ Server Architecture:

MySQL follows the working of a client/server architecture. There is a database server (MySQL) and arbitrarily many clients (application programs), which communicate with the server; that is, they can query data, save changes, etc.

Free to download:

MySQL is free to use so that we can download it from MySQL official website without any cost.

It is scalable:

MySQL supports multi-threading that makes it easily scalable. It can handle almost any amount of data, up to as much as 50 million rows or more. The default file size limit is about 4 GB. However, we can increase this number to a theoretical limit of 8 TB of data.

Speed:

MySQL is considered one of the very fast database languages, backed by a large number of the benchmark test.

High Flexibility:

MySQL supports a large number of embedded applications, which makes MySQL very flexible.

Compatible on many operating systems:

MySQL is compatible to run on many operating systems, like Novell NetWare, Windows* Linux*, many varieties of UNIX* (such as Sun* Solaris*, AIX, and DEC* UNIX), OS/2, FreeBSD*, and others. MySQL also provides a facility that the clients can run on the same computer as the server or on another computer (communication via a local network or the Internet).

Allows roll-back:

MySQL allows transactions to be rolled back, commit, and crash recovery.

Memory efficiency:

Its efficiency is high because it has a very low memory leakage problem.

High Performance:

MySQL is faster, more reliable, and cheaper because of its unique storage engine architecture. It provides very high-performance results in comparison to other databases without losing an essential functionality of the software. It has fast loading utilities because of the different cache memory.

High Productivity:

MySQL uses Triggers, Stored procedures, and views that allow the developer to give higher productivity.

Platform Independent:

It can download, install, and execute on most of the available operating systems.

Partitioning:

This feature improves the performance and provides fast management of the large database.

GUI Support:

MySQL provides a unified visual database graphical user interface tool named “MySQL Workbench” to work with database architects, developers, and Database Administrators. MySQL Workbench provides SQL development, data modeling, data migration, and comprehensive administration tools for server configuration, user administration, backup, and many more. MySQL has a fully GUI supports from MySQL Server version 5.6 and higher.

Dual Password Support:

MySQL version 8.0 provides support for dual passwords: one is the current password, and another is a secondary password, which allows us to transition to the new password.

Timeline and history of MySQL

           Timeline and history of MySQL

• Unireg, which is the code base of MySQL, was started in 1981.
• MySQL was founded in 1995 in Sweden.
• In 2000, MySQL went open source, so it could be accessed and used by all.
• In the year 2001, Marten Mickos was elected as the CEO of MySQL.
• In the year 2002, MySQL launched its headquarters in USA, in addition to Sweden headquarters.
• In 2003, MySQL entered into a partnership with SAP, and many features were developed in MySQL keeping SAP in mind.
• In 2005, MySQL launched MySQL Network. Oracle purchased Innobase, which is the backend of MySQL’s InnoDB storage.
• In 2008, MySQL was acquired by Sun Microsystems.
• In 2009, Sun Microsystems and Oracle entered intoa definitive agreement under which Oracle acquired Sun Microsystems.

Let us now understand the history of MySQL:

• The mSQL database system was designed to connect the data that is stored in tables using customized fast low level (ISAM) routines.
• ISAM refers to indexed sequential access method, which is a file management system. It is a technique that helps access records in the tables sequentially, i.e in the same order in which the records were entered into the table, or randomly with the help of an index. Every index can be used to define a different order for the records in the table.
• ISAM was originally developed by IBM before the development of VSAM (Virtual Storage Access Method) and relational databases.
• When mSQL was tested to see its efficiency, it was observed that mSQL was not quick enough or flexible enough for the requirements in hand.
• MySQL was initially created for personal usage from mSQL based on the low-level language ISAM.
• MySQL has been named after co-founder Monty Widenius’s daughter- My. The logo, a dolphin is known as ‘Sakila’.