Back to: MySQL Tutorials for Beginners and Professionals
An Overview of MySQL Database
In this article, I am going to give you an Overview of MySQL Database. Please read our previous article, where we discussed How to Download and Install MySQL on Windows Operating System for development.
What is a database?
The structured query language is all about databases. Therefore, we must first understand what a database is? The database is a structured set of data organized in such a way so that a computer can quickly access, manage and update it. In other words, a Database is a collection of one or more tables and therefore the tables contain “data rows” or “data records”.
Imagine the database with an example of a refrigerator. The refrigerator is a database. The various compartments in the refrigerator are tables. And the vegetables, foods, and beverages stored inside the refrigerator’s compartments are the information.
To interact with any database
- The user must have an appropriate privilege to access the database.
- The user must select the database before executing any SQL statement.
How to create and use a Database?
In order to create a database, first, we should log in to the MySQL database. Once you login into the database server, then you can use the following SQL statement to create a new database in MySQL.
Syntax: CREATE DATABASE DatabaseName;
Example: CREATE DATABASE school;
Here, school is the database name. Hit Enter And you will get Query 1 rows affected message. That is the new database has been created. Now, if you want to see all the existing databases, then you can use the below SQL query in MySQL.
SHOW DATABASES;
Once you execute the above SQL Query, you will see the following out. Further, if you notice then you can see the newly created school database that we just created.
As you can see in the above image, we have many databases in our MySQL server. But if you want to write and execute queries in a particular database for example school database, then first we need to select the school database by executing the below SQL statement:
USE school; Here, school is nothing but the name of an existing database. Once you execute the above SQL Statement you will get the database changed message. At the moment our school database is empty, i.e. there are no tables or data rows inside the database.
How to Drop a Database in MySQL?
If you want to drop an existing database in MySQL, then you need to execute the following SQL statement. The following SQL Statement will delete the school database from the MySQL Server.
DROP DATABASE school; Here, the school is the existing database name. When you execute the above SQL Statement, then you will get Query OK 0 rows affected message, that is our database is removed.
Now again type SHOW DATABASES; SQL statement, to display the list of all the existing databases. And you can see, the school database is gone as shown in the below image.
MySQL Default Database Type:
To use MySQL’s default database Type, execute the below SQL Statement:
USE mysql; Here mysql is the default existing database name. You will get the database changed message. Now type SHOW tables; to display the available tables in the mysql database as shown in the below image.
Now type SELECT * FROM user; it will display multiple types of privileges that are granted to the user account as shown in the below image.
What is a database table?
A database table consists of systematically structured vertical columns or fields and horizontal rows or records. Each column represents a property of the item while each row represents an item. The cell is the unit where a column and row intersect. Data elements or values are stored in a cell. A database table has a specified number of columns, but it can have any number of rows. A single database must contain a unique table name, while in multiple databases, the same table name can exist in another database.
SQL statements for a database table
To start working on tables, we must first create and then select the database. And the SQL statement to create a database is CREATE DATABASE school, here school is the database name, and then use “USE school; SQL Statement to select the database. The SQL statement to display the existing tables inside the selected database is, “SHOW TABLES;”. This SQL statement will return all the existing tables inside the selected database.
How to Create a Table in MySQL?
Let us understand how to create a table in MySQL with an example. The following SQL Query will create a table with the Students with Id, Name, Class, and Age columns.
CREATE TABLE Students ( Id INT PRIMARY KEY, Name VARCHAR(40) NOT NULL, Class VARCHAR(20), Age INT );
Once you execute the above SQL Statements, it will create a new table called Students with the given columns and their data types inside the school database that we selected.
How to see the Structure of a Database in MySQL?
Once a table is created, later if you want to see the structure of that table or if you want to see the structure of any existing database table then you can use the following SQL Statement. The following SQL Statement will show the structure of the Students table that we just created.
DESCRIBE Students;
Once you execute the above SQL Statement, it will give the following output which describes the properties of each field of the Students table.
Now, if you execute the below SQL Statements, then it will show the Students table.
SHOW TABLES;
It will give you the list of the existing table in the school database.
How to add data rows to a database table in MySQL?
Before adding the data rows to a table, we must first select the database.
USE school;
Syntax:
The SQL statement to add data row or add a new record in a database table is,
INSERT INTO tablename VALUES (value1, value2, value3, – – -);
Another method to insert new row in a table is,
INSERT INTO tablename (column1, column2, column3, – – -) VALUES (value1, value2, value3, – – -);
In the first method, we must provide all the column values in the sequence. While in the second method you can add the values to only selected columns. The second method is more convenient. Let’s learn how to INSERT data rows in a table with some practical examples. We will see how to insert data into the student table:
First method:
INSERT INTO Students VALUES (1, ‘Anurag’, ‘First’, 5);
Using INSERT INTO SQL statement, we are inserting these data rows with values to the table students. Once you execute the above SQL statement, it will insert one row into the Students table. If you get Query OK 1 row affected message, then you successfully inserted the data row.
Note: The values must be in the database table column order that is Id, Name, Class, and Age.
Second method:
INSERT INTO students (Id, Name, Class, Age) VALUES (2, ‘Priyanka’, ‘Second’, 7);
Compare the two SQL statements. In the first case, we didn’t mention the data column names. In the second SQL statement. In the first bracket, we mentioned table columns like Id, Name, Class, Age, and the respected values in the second bracket. Press Enter to execute the SQL Statement and again we inserted another row. If you get Query OK 1 row affected, we successfully inserted another data row.
Inserting multiple Rows in MySQL:
You can also add multiple rows using the same method like-
INSERT INTO students (Id, Name, Class, Age) VALUES
(3, ‘Hina’, ‘First’, 8),
(4, ‘Sambit’, ‘Second’, 7);
The INSERT INTO students in first brackets type the table columns, we can press enter to make the SQL statement neat and clean and type another line the values. And one more row. Semicolon to terminate the SQL statement, press enter, and this time, we inserted two rows using the one SQL statement.
Let’s check if the rows are successfully added to the database table.
The SQL statement to display all the rows in a database table is:
SELECT * FROM Students;
Once you execute the above Select Statement, you will get the following output.
Understands Keywords, Identifiers, Constants, and Clauses
Keywords: Just like sentences in the English language are made up of words, the same way the SQL statements are made up of special words Keywords, Identifiers, Constants Clauses. Keywords are SQL standards words used to construct the SQL statements. Some keywords are optional while some of them are mandatory.
Identifiers: Identifiers are the names; we give to the database tables or columns.
Constants: The constants are literals that represent fixed values.
Clauses: A clauses is a portion of a SQL statement. The name of the clause corresponds to the SQL keyword that begins the clause.
Example1: SELECT name FROM students WHERE id=5;
Here is an example of a SQL query made up of keywords, Identifiers, and Constants. In this query, SELECT, FROM, WHERE are Keywords. SELECT and FROM are mandatory while WHERE is optional. Equals sign (=) is a special type of keyword called operator. name, students are Identifiers. name, id are column names while students is a table name. 5 is numeric constant. And the SQL statement contains the SELECT clause, FROM clause WHERE clause.
Example 2: SELECT * FROM students WHERE age>6;
Here is another example, in this query, SELECT, FROM WHERE are Keywords. SELECT and FROM are mandatory while WHERE IS optional. The “*” (asterisk) is a special type of keyword, while means all the columns from the table. Less than sign a special type of keyword called comparison operators. age is a column name while students is a table name. 6 is a numeric constant, And the SQL statement contains the SELECT clause, FROM clause WHERE clause. We will learn more about each clause in the next section.
How to Delete a table in MySQL?
You can delete a table in MySQL using the following Statement.
DROP TABLE students;
It will delete the table using SQL statements.
Learn about SQL Statements
Some database management systems require a semicolon at the end of each SQL statement to complete the statement. SQL statements play a major role in interacting with the database server.
A statement is the smallest standalone element that expresses some actions to be carried out. A Syntax is how the Keywords, Constants Clauses are combined together to form a valid statement. SQL is not case sensitive. That is, “select” (all small letters) is similar to “SELECT” (all capital letters). To distinguish the keywords in a statement, we will be using all the keywords in uppercase. Even though the language is case insensitive make a habit to follow a certain writing style. Some database management systems require a semicolon at the end of each SQL statement to complete the statement. By adding a semicolon at the end of the statement, you can execute more than one SQL statement in a single call to the server. MySQL requires a semicolon at the end of the statements. So, in this course, we will be using a semicolon at the end of each statement.
Data Definition Language (DDL) and Data Manipulation Language (DML)
SQL statements can be divided into two types: Data Definition Language (DDL) and Data Manipulation Language (DML). DDL and DML both are SQL statements. The best way to distinguish between DDL and DML is the type of SQL statements used and their effect on the database. DDL changes the database structure while DML changes only the data.
Data Definition Language (DDL)
Data Definition Language is used to manage database objects like tables and columns. Data Definition Language changes the database structure. Database objects like tables and columns are created, modified, or removed using Data Definition Language SQL statements. The most important Data Definition Language statements are:
- CREATE: creates a table with columns
- ALTER: modifies the table
- DROP: removes the table and columns from the database.
Data Manipulation Language (DML)
Data Manipulation Language is used to manage the data that resides in our tables and columns. DML changes only the data. The data inside a table is inserted, updated, or deleted using the Data Manipulation Language SQL statements.
The most important Data Manipulation Language statements are:
- INSERT: INSERT SQL statement adds the data
- UPDATE: UPDATE modifies the data.
- DELETE: DELETE removes the data from a database table
Here, in this article, I try to give you an Overview of MySQL Database. In the next article, I am going to discuss how to connect with the MySQL Server database using the workbench, and also, we will learn how to create databases and tables in MySQL Database using the workbench.