To insert data into MySQL table you would need to use INSERT INTO SQL

Syntax:

To insert string data types it is required to keep all the values into double or single quote, for example:- “value”.

Inserting Data Using PHP:

Inserting a row in “emp_info” table:

Inserting Form Data Using PHP:

It is very easy to drop an existing MySQL table. While you are deleting a table you need to be very careful, because data lost will not be recovered after deleting a table.

Dropping Tables Using PHP:

Example:

MySQL Create table

MySQL CREATE TABLE is used to create a table within a database.The table creation command requires:

• Name of the table
• Names of fields
• Definitions for each field

MySQL storage engine represents each table by following files

The CREATE TABLE statement Syntax.

Example:

Here few items need explanation:

• Constraint NOT NULL is used because we do not want this field to be NULL. if we try to create a entry with NULL value then MySQL Engine will through an error.
• AUTO_INCREMENT tells to MySQL Insert new record with next available incremented value.
• Constraint PRIMARY KEY is used to define a column as primary key. Multiple columns need to be separated by comma to define a primary key.
• The UNIQUE constraint used to uniquely identify each record in a table.The UNIQUE and PRIMARY KEY constraints both assures for uniqueness for a field or set of fields. A PRIMARY KEY constraint by default has a UNIQUE constraint defined for it.We can have many UNIQUE constraints for a table, but only one PRIMARY KEY per table.

The statement above will create ’emp_info’ table which have columns like following

A data type specifies the possible values for that type, the operations that can be performed on that type and the way the values of that type are stored.MySQL uses many different data types, broken into three categories: numeric, date and time, and string types.

MySQL Numeric Types

Integer types

Floating-Point Types

Fixed-Point Types

Bit Value Types

Numeric type attributes

MySQL Date and Time Types

DATETIME, DATE, and TIMESTAMP Types

String Types

CHAR and VARCHAR Types

BINARY and VARBINARY Types

BLOB and TEXT Types

ENUM Types

A string object whose value is chosen from a list of values given at the time of table creation. For example –

Once you get connection with MySQL server,it is required to select a specific database to perform any operation on that DB.

Selecting MySQL Database Using PHP:

PHP provides function mysql_select_db to select a database.It returns TRUE on success or FALSE on failure.

MySQL drop database

DROP followed by a name of the database is used to drop or delete a database.

The MySQL statement above will drop the “emp_db” database;

Note: While deleting a database using PHP script, it does not prompt you for any confirmation. So be careful while deleting a MySQL database.

MySql create database:

In MySql, the create statement is used to create a database.

Example:

The database names are case sensitive under Unix but this restriction does not apply in Windows. This is also true for table names.

Before you perform any operations, you need to connect to MySQL server and select a MySQL database.

Connecting to MySQL server and database using PHP

PHP uses mysql_connect() function to open a database connection.Here is prototype for mysql_connect() function.

MySQL persistent connection with PHP

MySQL persistent connection is a connection which first tries to find if any identical connection (i.e with same hostname, username and password) exists. If so, then commands followed will use that connection. If such a connection does not exist, it would create one. MySQL persistent connection can not be close using mysql_close(). Persistent connection is used for better performance out of your MySQL server

Disconnecting or closing a MySQL connection using PHP

What is Database?

MySQL is a open source Relational Database Management System. This is called relational database because all the data is stored into different tables and relations are established using different constraints like primary keys or other keys known as foreign keys.MySQL is very efficient,fast, reliable and flexible Database Management System. It provides a high performance and it is multithreaded and multiuser Relational Database management system.

A is a separate application that stores a collection of data. Every database management system has one or more distinct APIs for creating, accessing, managing, searching, and replicating the data it stores.

What Is Relational DataBase Management System (RDBMS):

RDBMS stands for Relational Database Management System. It is the system which manages your data structures in table format with fields,columns and rows. It also maintain the relation between one data to other data. To manage the relation between the data, it users primary key and foreign key concept.

RDBMS Features:

• Implement a database with tables, columns, and indexes.
• Provides facility primary key, to uniquely identify the rows.
• Referential Integrity and constraints between rows of various tables.
• Creates indexes for quicker data retrieval
• Provides a virtual table creation in which sensitive data can be stored and simplified query can be applied.(views).
• Sharing a common column in two or more tables(primary key and foreign key)
• Provides multi user accessibility that can be controlled by individual users
• Interprets an SQL query and combines information from various tables.

RDBMS Terminology:

• Database: A database is a collection of tables, with related data.
• Table: A table is a matrix with data. A table in a database looks like a simple spreadsheet.
• Column: One column (data element) contains data of one and the same kind, for example the column postcode.
• Row: A row or record is a group of related data
• Redundancy: Data replication enables to make the system faster.
• Primary Key: Primary key is the column of your table which can uniquely identify the row of that column
• Foreign Key: Is the column which is referencing to other table from this table. The foreign key of one table must be a primary key of the one it is referencing to.
• Compound Key: A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.
• Index: Index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns
• Referential Integrity: Referential Integrity assure that a foreign key value always points to an existing row.

MySQL Database:

MySQL is a fast, easy-to-use RDBMS used 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.

Feature Of MySQL:

• MySQL is an open-source RDMS.
• 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 popular language for web development.
• MySQL supports large databases. The default file size limit for a table is 4GB, but you can increase this.
• MySQL is customizable.

Advantages Of MySQL:

Prerequisites To Learn MySQL:

Before you begin this tutorial you must have basic knowledge of SQL.