Create a Database in MySQL

Relational & Non Relational Databases

A relational database is a relational model of data that organizes data in the form of tables of rows and columns. A non-relational database does not use the tabular schema of rows and columns like in relational databases. The data is stored in document-based models where the storage model is optimized for particular requirements.

As the volume of data increases with time, it becomes extremely challenging to perform operations on the data manually. Thus to handle this problem, we use the prevalent Database Management System, commonly known as DBMS. If we are using a relational database, then to manage it, we would require a Relational Database Management System (RDMS). Oracle and MySQL are some examples of RDMS, whereas MongoDB and Redis are some of the non-relational databases. For communication between RDMS to the database, we use a language known as SQL(Structured Query Language). We can add, read or update any data in the database using this language.

For more detail, you can refer to the Scalar topics blog Relational and Non-Relational Databases

What is MySQL?

MySQL is a database management system that is required to store or retrieve data from a database. In this article, we'll be using MySQL for managing our databases. Let's have a brief introduction about it.

1. MySQL is a cross-platform tool compatible with most operating systems. Thus it can run on multiple computing platforms, including Windows, Linux, NetWare, Novell, and other variations of UNIX.
2. It can be used with multiple languages like PHP, NodeJS, Python, and C#.
3. MySQL is open-source software that can be downloaded, used, and modified by anyone worldwide.
4. MySQL is a Relational Database Management System that stores and presents data in a tabular form, organized in rows and columns. We can retrieve data from multiple tables by writing a few commands.
5. MySQL has a well-earned and established reputation for reliability, making it a good choice for use.
6. MySQL stores data efficiently in the memory, ensuring that data is consistent and non-redundant. This makes the access and manipulation of data very fast.
7. MySQL is scalable, i.e., it can work with both small and large databases.
8. MySQL Server works in client/server architecture in which a central program acts as a server, and various clients can request the server for various access or retrieval of data from the database. The central program is the MySQL database server.

MySQL Installation & WorkBench

We must set up MySQL before we start writing queries for manipulating data in our databases. You can refer to the Scalar blog Install and set up MySQL on Windows, Mac OS, and Ubuntu for the setup on your system.

How to Create Database in MySQL?

To create a database in MySQL,

1. Open the MySQL Workbench, type the query -

2. Execute the query by clicking on the third button on top, which is used to run the query. If a green tick appears on the output section, the database has been created successfully.

Our SQL query can be improved by adding some more parameters -

• IF NOT EXISTS - The SQL Server can have multiple databases. If you are dealing with multiple databases, there is a probability of attempting to create a database with a name that already exists on the server. In such situations, the MySQL server throws an error.

  When IF NOT EXISTS is used, the database is created only if the given name does not conflict with an existing database’s name.

• Collation and Character Set - A MySQL collation is a set of rules that compare characters in a particular character set. Each character set in MySQL has at least one default collation. And it can have more than one collation. However, two character sets cannot have the same collation.

Similarly, we can create several databases in MySQL.

Listing All the Databases

To list all the databases we have created so far, we can execute the query -

Output:

All the databases you have created will be displayed when you use this command. For example, the databases student, student1, and test have been displayed in this output.

How to Create Table in MySQL?

Tables are created inside a database. You need to first select a database to create a table in it.

To use a database,

Now create a table inside the database.

The column parameters specify the names of the columns of the table. The datatype parameter sets the type of data the column can hold (e.g., varchar, integer, etc.).

In the above picture, we are creating a table named students inside the student database with columns StudentID, FirstName, LastName, Address, and City along with their datatypes.

The table can be displayed using

An asterisk (" * ") selects all the columns from the table.

The table will appear something like this:

How to Delete Database from SQL Server?

To delete a database from the SQL Server, we use the DROP DATABASE command.

Syntax:

By this command, we can delete only an existing database because of the 'IF EXISTS' option, which is available from SQL Server 2016 (13.x). However, the SQL server will throw an error if you try to delete a database that does not exist.

In the syntax, you'll write the database name after the IF EXISTS option to delete it.

In the above gif, the student database has been deleted after executing the query DROP command. You can verify it by viewing the schemas column on the left side of the gif or by executing the show databases command.

Before deleting a database, we must ensure the following points -

• Deleting a database using the DROP DATABASE clause will result in the loss of complete information stored in the database and the physical disk files used by the database.
• You cannot delete a database that is currently being used.

If you try to drop a database that is currently in use, then the SQL Server will throw an error-

Cannot drop database "database_name" because it is currently in use.

The student1 database that we have created is now deleted.

To check it, execute the show databases query again; this time, it will not list the student1 database.

How to Alter Database in SQL Server?

The alter command is used to modify an existing database, table, view, or other database objects that might need to change in a database.

Let us assume that you have finished designing and implementing your database, after which you realize some of the important information has been missed out in the design phase. You don’t want to lose the existing data but want to include the new information. The alter command comes to your help in such situations. You can use the alter command to change the data type of a field from numeric to string and add a new column in a table.

Syntax:

where database_name is the name of the database, you need to modify.

Examples-

1. ALTER DATABASE student CHARACTER SET= ascii;

The above query changes the character set of the student database.

2. ALTER TABLE students ADD Email varchar(255);

Output:

The Email column has been added to the table.

3. ALTER TABLE students DROP COLUMN Address;

Output:

The Address column has been deleted from the table.

Some Important Points to follow while writing queries

• Use upper case letters for SQL keywords like “DROP DATABASE IF EXISTS student;”
• End all your SQL commands with semi-colons.
• Avoid using spaces in the database, table, and field names; instead, use underscores to separate the names of databases and tables.