INTRODUCTION TO MySQL –
MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons −
- MySQL is released under an open-source license. So you have nothing to pay to use it.
- MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
- MySQL uses a standard form of the well-known SQL data language.
- MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
- MySQL works very quickly and works well even with large data sets.
- MySQL is very friendly to PHP, the most appreciated language for web development.
- MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
- MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.
Some of The Most Important SQL Commands
SELECT– extracts data from a database
UPDATE– updates data in a database
DELETE– deletes data from a database
INSERT INTO– inserts new data into a database
CREATE DATABASE– creates a new database
ALTER DATABASE– modifies a database
CREATE TABLE– creates a new table
ALTER TABLE– modifies a table
DROP TABLE– deletes a table
CREATE INDEX– creates an index (search key)
DROP INDEX– deletes an index
MySQL is offered under two different editions: the open source MySQL Community Server and the proprietary Enterprise Server.MySQL Enterprise Server is differentiated by a series of proprietary extensions which install as server plugins, but otherwise shares the version numbering system and is built from the same code base.
Major features as available in MySQL 5.6:
- A broad subset of ANSI SQL 99, as well as extensions
- Cross-platform support
- Stored procedures, using a procedural language that closely adheres to SQL/PSM
- Full-text indexing and searching
- Embedded database library
Most Popular MySQL Commands
In this article, we will review the basic usage for making a CRUD (Create, Read, Update, Delete) functionality and the most-used commands with MySQL that could also be run in a WHM / cPanel stack. Most of the commands in this article can be run through a phpMyAdmin interface and we will leave a reference to MySQL’s documentation.
Creating, editing, updating and deleting content on a website is what makes the site dynamic. That’s what we are going to be doing in this post.
1. Create operation:
The first letter of CRUD, ‘C’, refers to CREATE aka add, insert. In this operation, it is expected to insert a new record using the SQL insert statement. SQL uses INSERT INTO statement to create new records within the table.
Create table table_name(column_name column_type constraints);
- column_name –
Name of the particular column with any space.
- column_type –
Datatype of the column. Datatype depends upon the data of the reference column. Datatype can be – char(), varchar(), int(), float(), etc.
- constraints –
In order to give restrictions to particular column constraints are used. Constraints can be – not null, primary key, foreign key, etc. These are the keywords which give set of restriction to the particular column.
2. Read Operation :
The Read operations are used to retrieve the content of the table from a particular database. Read operation is done by DDL commands.
select *from table_name;
3. Update Operation :
Altering the content of the table or the structure of the table is done with the help of Update Operations. Two Commands are mostly used for Update Operation.
- Alter Table Command –
This is the DDL command (Data Definition Language) used to change the structure of the table.
alter table table_name;
- Update Table Command –
This is the DML command(Data Manipulating Language) used to alter and update the records.
Alter Table Command that change the size of name column from varchar(40) to varchar(50) for the Student table :
update table_name set
4. Delete Operation :
Two commands are mostly used for the Delete operations –
- Delete Command –
(DML command) works on the records of the table.
delete from table_name
2.Drop Command –
(DDL command) works on the structure of the table.
- drop table table_name;
- drop database database_name;
MySQL Export and Import Database
Database export and import in MySQL is a process of moving data from one place to another place.
Export and import are useful methods for backing up essential data or transferring our data between different versions.
It is necessary to keep it in a secure place. So we need to export it in a safe place, and whenever it lost from the original location, we can restore it using import options.
Importing and exporting databases is a common task in software development. You can use data dumps to back up and restore your information.
You can also use them to migrate data to a new server or development environment.