This article on MySQL Database contains details on MySQL like what is database and MySQL, features of MySQL, and in detail about the MySQL languages and commands. Every point is explained in easily understandable language for example. So, read the article till the end to get knowledge in detail.
What is Database?
The database is an organized collection of data in the form of a relation table. In a database information is stored in the computer system electronically. Data management is very easy by using a database. There are many types of the database like Relational Database, Object-Oriented Database, Hierarchical Database, Distributed Database but mostly Relational Database is used.
Databases are used because we can manage and access data very easily in a database. A common example of a database is an online telephone directory. The database uses a Structured Query Language(SQL) for creating and managing the data. Databases like MySQL, Oracle, MongoDB, SQL Server, etc are used these days. Read More About Database
What is MySQL?
MySQL is a very popular database management system. It is nowadays mostly used as a database management system for managing the relational database. It is an Open-Source and Relational Database management system(RDBMS). It is both ideals for large and small applications and it is also free. MySQL is cross-platform i.e it can be used on different computers and different types of computers. It is very fast, scalable, and reliable. It is also easy to use. It is distributed, developed, and supported by Oracle Corporation. MySQL is used by big companies’ websites like Facebook, Twitter, Uber, Youtube, GitHub, etc.
- What are the types of computers?
- What is MotherBoard and how does it work?
- What is Cloud Computing | Know Everything About it
Features of MySQL
- Easy to Use – By getting a basic knowledge about MySQL you can use it. Knowing a few statements of SQL you will be able to interact and build a database with MySQL.
- Secure – MySQL is password encrypted and it needs access permission. It includes a data security layer for protecting sensitive data. Every company need their data secure in the database, MySQL is secured,
- Free to use and download- MySQL is free to download and use from the official website without any charges.
- Speed – MySQL is a very fast database language.
- Commit and Roll-back – MySQL contains data control language(DCL) and data control language in MySQL allows transaction rolled back, commit, and crash recovery.
- Platform dependency – MySQL is platform-independent. It is downloaded, installed, and executed on most Operating systems.
- Performance – MySQL uses a unique storage engine architecture that is more reliable and performs faster. And at a cheaper cost. It provides better performance than other databases and without losing any of the essential functions of the software. It also contains cache memory, which is good for fast-loading utilities.
- GUI Support – GUI is a Graphical User Interface, a type of Operating System in which we work with mouse clicks.MySQL supports graphical user interface tools to work with database architects, developers, and administrators. The actions we perform using commands on MySQL server, we can also do those using the GUI provided.
- Architecture – It is a client server-based platform where clients communicate with the server, that is, they can query data, save the change, and more.
MySQL Languages with their Commands
DATA DEFINITION LANGUAGE(DDL)
Data Definition Language is the language used to define the structure of the database. This language is used to create schema, indexes, tables, tables, constraints, etc in a database management system. DDL stores information of metadata like the number of schemas, tables, names, columns in each constraint, table.
Commands under DDL
The operation we can perform using the DDL commands are Creating the database(CREATE Statement), Making changes in the database(ALTER Statement), Deleting all records of the table(TRUNCATE Statement), To drop the existing table(DROP TABLE Statement)
CREATE Statement – Create command is used to create database objects. Inside the create command we declare different objects. For example, for creating an employee table we need objects like employees’ IDs, employees’ names, employees’ designation, etc.
ALTER Statement – The alter statement is used to add, delete, or change/modifies columns of the table. The table is created after that for further addition or deletion of data is done by the Alter. With the Alter statement three other statements are from the used: Add the statement, Drop Statement, Change/Modify Statement.
- Add statement – Add statement is used to add a column in an existing table. Like in the employee’s table example if we want to add employees’ addresses we can use the Alter (Add) statement.
- Drop statement – drop statement is used to delete any column of the existing table. Example- If you want to delete the employee posts column then you can use an alter (drop)statement.
- Change/Modify Statement – Change/Modify Statement is used to change the data type of any column of the existing table. Example – Like in the Employees table, Employees Id was earlier in number (int data type) but after that, it got changed to the character (Varchar data type) then we can use an alter (modify ) Statement.
TRUNCATE TABLE Statement – TRUNCATE Statement is used to remove all the rows from the table but the structure of the table, its columns, indexes, constraints remain. Example – If we want to delete the whole data/details of employees from the employee’s table, we can use the Truncate statement.
DROP TABLE Statement – DROP statement is used to drop the existing table from the database and using drop table statement you can completely delete the information of the table.
DATA MANIPULATION LANGUAGE(DML)
DML stands for data manipulation language. It is used to manipulate the data stored in the table. This language helps in updating, inserting, deleting the existing information of the table. And also accessing the data in the database. DML also manages the user’s request. For example, in the employee’s table, if we want to update any profile of the employee or/and change the designation of the table we can do this using the DML statements. And if we want to delete an employee’s details, then also we can use the DML command and other things.
Commands under DML
Operations we can perform in the DML languages are SELECTION(SELECT Statement), INSERTION(INSERT Statement), UPDATION(UPDATE Statement), DELETION(DELETE Statement), and other operations like MERGE, CALL, EXPLAIN PLAN, LOCK Table.
SELECT Statement – Using the select command we can retrieve our data from the database. We can also give parameters about which all data we want to see or select. For example – From the employee table, if we want the employee designation, employee address of a particular employee then we can use a select statement.
INSERT Statement – Using the insert statement we can insert any data in the existing table. For example from the employee table if we want to insert any new employee then we can do it using an insert statement.
UPDATE Statement – using the update statement we can update any existing data in the database. For example – From the employee table, if any employee changes his address then using the update statement we can update his present address.
DELETE Statement – Using the delete statement we can delete the existing record of the table of that database. It is used to delete any number of tuples of the table. For example, from the employee’s table, if an employee wants to delete any details then we can use a delete statement.
DATA CONTROL LANGUAGE(DCL)
DCL stands for Data Control Language. DCL is used to retrieve the saved data from the database. Its execution is transactional and it has rollback parameters.
Commands Under DCL
Operations we can perform in DCL are grant permission of the database(GRANT Statement) and also revoke permission of the database(REVOKE Statement).
GRANT Statement – Using Grant statements we can permit the database. For example – from the employee table, if anyone wants to see and operate in the employee table then for that we use a grant statement.
REVOKE Statement – Using the Revoke statement we can take back the permission of the database. For example – From the employee table if someone is no longer working on the employee table then we can use a revoke statement and take back the permission.
Transaction Control Language(TCL)
TCL stands for Transaction Control Language. It is grouped for the logical transaction. It runs the changes made by the data manipulation language(DML).
Commands Under TCL
The operation we can do in TCL are Save transaction(COMMIT Statement), and restore the last commit(ROLLBACK Statement).
COMMIT Statement – Commit Statement is used to save the transaction of the database. For Example – From the employee’s table, when we save the transaction of employees.
ROLLBACK Statement- Using the rollback statement we roll back the changes made. For Example- From the employee’s table, we can roll back the last change or saved transaction of the table.
MySQL is a very popular database. It is secure and is free to download. It is very easy to work on MySQL. MySQL’s all the details and download link is available on its website. Its commands are easy and are the same as our daily language. Like if we want to select some records we will use the SELECT statement and if want to create a from the database we can use CREATE table statement.