MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations. It is the world's second most popular and widely-used open source database. It is interesting how MySQL name was given to this query language. The term My is coined by the name of the daughter of co-founder Michael Widenius's daughter, and SQL is the short form of Structured Query Language. Using MySQL is free of cost for the developer, but enterprises have to pay a license fee to Oracle.

Formerly MySQL was initially owned by a for-profit firm MySQL AB, then Sun Microsystems bought it, and then Oracle bought Sun Microsystems, so Oracle currently owns MySQL.

MySQL is an Oracle-supported Relational Database Management System (RDBMS) based on structured query language. MySQL supports a wide range of operating systems, most famous of those include Windows, Linux & UNIX. Although it is possible to develop a wide range of applications with MySQL, it is only used for web applications & online publishing. It is a fundamental part of an open-source enterprise known as Lamp.

MySQL is written in C and C++, and its SQL parser is written in yacc.

SQL is known as the standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.

A PHP script is required to store and retrieve the values inside the database.

SQL is a computer language, whereas MySQL is a software or an application

SQL is used for the creation of database management systems whereas MySQL is used to enable data handling, storing, deleting and modifying data

MySQL has the following technical specifications -

• Flexible structure
• High performance
• Manageable and easy to use
• Replication and high availability
• Security and storage management
• Drivers
• Graphical Tools
• MySQL Enterprise Monitor
• MySQL Enterprise Security
• JSON Support
• Replication & High-Availability
• Manageability and Ease of Use
• OLTP and Transactions
• Geo-Spatial Support

There is a major difference between a database and a table. The differences are as follows:

• Tables are a way to represent the division of data in a database while the database is a collection of tables and data.
• Tables are used to group the data in relation to each other and create a dataset. This dataset will be used in the database. The data stored in the table in any form is a part of the database, but the reverse is not true.
• A database is a collection of organized data and features used to access them, whereas the table is a collection of rows and columns used to store the data.

The default port for MySQL server is 3306.

irst of all, the MYSQL server is free to use for developers and small enterprises.

MySQL server is open source.

MySQL's community is tremendous and supportive; hence any help regarding MySQL is resolved as soon as possible.

MySQL has very stable versions available, as MySQL has been in the market for a long time. All bugs arising in the previous builds have been continuously removed, and a very stable version is provided after every update.

The MySQL database server is very fast, reliable, and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.

There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present:

• MyISAM
• Heap
• Merge
• INNO DB
• ISAM

A column is a series of cells in a table that stores one value for each row in a table. We can add columns in an existing table using the ALTER TABLE statement as follows:

ALTER TABLE table_name     
    ADD COLUMN column_name column_definition [FIRST|AFTER existing_column]; 

We can delete a table in MySQL using the Drop Table statement. This statement removes the complete data of a table, including structure and definition from the database permanently. Therefore, it is required to be careful while deleting a table. After using the statement, we cannot recover the table in MySQL. The statement is as follows:

DROP TABLE  table_name;   

While creating a table, we have kept one of the column names incorrectly. To change or rename an existing column name in MySQL, we need to use the ALTER TABLE and CHANGE commands together. The following are the syntax used to rename a column in MySQL:

ALTER TABLE table_name     
    CHANGE COLUMN old_column_name new_column_name column_definition [FIRST|AFTER existing_column];  

 

We can remove, drop, or delete one or more columns in an existing table using the ALTER TABLE statement as follows:

 

ALTER TABLE table_name DROP COLUMN column_name1, column_name2....; 

We can insert data in a MySQL table using the INSERT STATEMENT. This statement allows us to insert single or multiple rows into a table. The following is the basic syntax to insert a record into a table:

INSERT INTO table_name ( field1, field2,...fieldN )    
VALUES  ( value1, value2,...valueN );    

If we want to insert more than one rows into a table, use the below syntax:

INSERT INTO table(field1, field2,...fieldN)  
VALUES   
   (value1, value 2, ...),  
   (value1, value2, ...),  
    ...  
   (value1, value2, ...); 

We can delete a row from the MySQL table using the DELETE STATEMENT within the database. The following is the generic syntax of DELETE statement in MySQL to remove one or more rows from a table:

DELETE FROM table_name WHERE Condition_specified; 

We can connect two or more tables in MySQL using the JOIN clause. MySQL allows various types of JOIN clauses. These clauses connect multiple tables and return only those records that match the same value and property in all tables. The following are the four easy ways to join two or more tables in MySQL:

• Inner Join
• Left Join
• Right Join
• Cross Join

MySQL Inner Join

The MySQL Inner Join is used to returns only those results from the tables that match the specified condition and hides other rows and columns. MySQL assumes it as a default Join, so it is optional to use the Inner Join keyword with the query.

We can understand it with the following visual representation where Inner Joins returns only the matching results from table1 and table2:

 

MySQL Inner Join Syntax:
The Inner Join keyword is used with the SELECT statement and must be written after the FROM clause. The following syntax explains it more clearly:

SELECT columns  
FROM table1  
INNER JOIN table2 ON condition1  
INNER JOIN table3 ON condition2  
...; 

MySQL Inner Join Example

Let us first create two tables "students" and "technologies" that contains the following data:

Table: student

Table: technologies

To select records from both tables, execute the following query:

SELECT students.stud_fname, students.stud_lname, students.city, technologies.technology    
FROM students   
INNER JOIN technologies    
ON students.student_id = technologies.tech_id;

After successful execution of the query, it will give the following output:

Sometimes we need to fetch data from three or more tables. There are two types available to do these types of joins. Suppose we have three tables named Student, Marks, and Details.

Let's say Student has (stud_id, name) columns, Marks has (school_id, stud_id, scores) columns, and Details has (school_id, address, email) columns.

1. Using SQL Join Clause

This approach is similar to the way we join two tables. The following query returns result from three tables:

SELECT name, scores, address, email FROM Student s   
INNER JOIN Marks m on s.stud_id = m.stud_id   
INNER JOIN Details d on d.school_id = m.school_id; 

2. Using Parent-Child Relationship

It is another approach to join more than two tables. In the above tables, we have to create a parent-child relationship. First, create column X as a primary key in one table and as a foreign key in another table. Therefore, stud_id is the primary key in the Student table and will be a foreign key in the Marks table. Next, school_id is the primary key in the Marks table and will be a foreign key in the Details table. The following query returns result from three tables:

 

SELECT name, scores, address, email   
FROM Student s, Marks m, Details d   
WHERE s.stud_id = m.stud_id AND m.school_id = d.school_id;

Indexing is a process to find an unordered list into an ordered list. It helps in maximizing the query's efficiency while searching on tables in MySQL. The working of MySQL indexing is similar to the book index.

Suppose we have a book and want to get information about, say, searching. Without indexing, it is required to go through all pages one by one, until the specific topic was not found. On the other hand, an index contains a list of keywords to find the topic mentioned on pages. Then, we can flip to those pages directly without going through all pages.

Working with the MySQL server, it is a common task to view or list the available databases. We can view all the databases on the MySQL server host using the following command:

SHOW DATABASES; 

• TRUNCATE is a DDL command, and DELETE is a DML command.
• It is not possible to use Where command with TRUNCATE QLbut you can use it with DELETE command.
• TRUNCATE cannot be used with indexed views, whereas DELETE can be used with indexed views.
• The DELETE command is used to delete data from a table. It only deletes the rows of data from the table while truncate is a very dangerous command and should be used carefully because it deletes every row permanently from a table.

There are only six Triggers allowed to use in the MySQL database.

1. Before Insert
2. After Insert
3. Before Update
4. After Update
5. Before Delete
6. After Delete

A trigger is a set of codes that executes in response to some events.

Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high-speed storage on a temporary basis. They do not allow BLOB or TEXT fields.

BLOB is an acronym that stands for a large binary object. It is used to hold a variable amount of data.

There are four types of the BLOB.

1. TINYBLOB
2. BLOB
3. MEDIUMBLOB
4. LONGBLOB

The differences among all these are the maximum length of values they can hold.

TEXT is a case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set, and values are stored and compared based on the collation of the character set.

There are four types of TEXT.

1. TINYTEXT
2. TEXT
3. MEDIUMTEXT
4. LONGTEXT

Heap tables:

Heap tables are found in memory that is used for high-speed storage temporarily. They do not allow BLOB or TEXT fields.

Heap tables do not support AUTO_INCREMENT.

Indexes should be NOT NULL.

Temporary tables:

The temporary tables are used to keep the transient data. Sometimes it is beneficial in cases to hold temporary data. The temporary table is deleted after the current client session terminates.

Main differences:

The heap tables are shared among clients, while temporary tables are not shared.

Heap tables are just another storage engine, while for temporary tables, you need a special privilege (create temporary table).

FLOAT stores floating-point numbers with accuracy up to 8 places and allocate 4 bytes. On the other hand, DOUBLE stores floating-point numbers with accuracy up to 18 places and allocates 8 bytes.

1. MySQL is a free, fast, reliable, open-source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users.
2. MySQL uses only just under 1 MB of RAM on your laptop, while Oracle 9i installation uses 128 MB.
3. MySQL is great for database enabled websites while Oracle is made for enterprises.
4. MySQL is portable.

1. MySQL is not so efficient for large scale databases.
2. It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
3. Transactions are not handled very efficiently.
4. The functionality of MySQL is highly dependent on other addons.
5. Development is not community-driven.

1. CHAR and VARCHAR have differed in storage and retrieval.
2. CHAR column length is fixed, while VARCHAR length is variable.
3. The maximum no. of character CHAR data types can hold is 255 characters, while VARCHAR can hold up to 4000 characters.
4. CHAR is 50% faster than VARCHAR.
5. CHAR uses static memory allocation, while VARCHAR uses dynamic memory allocation.

Mysql_connect:

1. It opens a new connection to the database.
2. Every time you need to open and close the database connection, depending on the request.
3. Opens page whenever it is loaded.

Mysql_pconnect:

1. In Mysql_pconnect, "p" stands for persistent connection, so it opens the persistent connection.
2. The database connection cannot be closed.
3. It is more useful if your site has more traffic because there is no need to open and close connection frequently and whenever the page is loaded.

The "i_am_a_dummy flag" enables the MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present. Hence it can save the programmer from deleting the entire table my mistake if he does not use WHERE clause.

Let us take a table named the employee.

To find Nth highest salary is:

select distinct(salary)from employee order by salary desc limit n-1,1

if you want to find 3rd largest salary:

select distinct(salary)from employee order by salary desc limit 2,1

REGEXP is a pattern match using a regular expression. The regular expression is a powerful way of specifying a pattern for a sophisticated search.

Basically, it is a special text string for describing a search pattern. To understand it better, you can think of a situation of daily life when you search for .txt files to list all text files in the file manager. The regex equivalent for .txt will be .*.txt.

You can a create maximum of 16 indexed columns for a standard table.

If you want to display the current date and time, use -

SELECT NOW();

If you want to display the current date only, use:

SELECT CURRENT_DATE();

NOW() command is used to show current year, month, date with hours, minutes, and seconds while CURRENT_DATE() shows the current year with month and date only.

SELECT * FROM table_name LIMIT 0,20;

SELECT COUNT user_id FROM users;

SELECT team_name FROM team WHERE team_won IN (1, 3, 5, 7);

MyISAM table is stored on disk in three formats.

• '.frm' file : storing the table definition
• '.MYD' (MYData): data file
• '.MYI' (MYIndex): index file

MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compressed if required. On the other hand, InnoDB stores the tables in the tablespace. Its further optimization is difficult.

Mysql_fetch_object is used to retrieve the result from the database as objects, while mysql_fetch_array returns result as an array. This will allow access to the data by the field names.

For example:

Using mysql_fetch_object field can be accessed as $result->name.

Using mysql_fetch_array field can be accessed as $result->[name].

Using mysql_fetch_row($result) where $result is the result resource returned from a successful query executed using the mysql_query() function.

 

Mysql_close() cannot be used to close the persistent connection. However, it can be used to close a connection opened by mysql_connect().

MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default, the information managed my MySQL = server mysqld is stored in the data directory.

In MySQL, regular expressions are used in queries for searching a pattern in a string.

• * Matches 0 more instances of the string preceding it.
• + matches one more instances of the string preceding it.
• ? Matches 0 or 1 instances of the string preceding it.
• . Matches a single character.
• [abc] matches a or b or z
• | separates strings
• ^ anchors the match from the start.
• "." Can be used to match any single character. "|" can be used to match either of the two strings
• REGEXP can be used to match the input characters with the database.

Example:

The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):

Select employee_name    
From employee    
Where employee_name REGEXP '1000'    
Order by employee_name   

In MySQL, the "i-am-a-dummy" flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present.

Federated tables are tables that point to the tables located on other databases on some other server.

To identify each row of a table, we will use a primary key. For a table, there exists only one primary key.

A candidate key is a column or a set of columns, which can be used to uniquely identify any record in the database without having to reference any other data.

Following are the drivers available in MySQL:

• PHP Driver
• JDBC Driver
• ODBC Driver
• C WRAPPER
• PYTHON Driver
• PERL Driver
• RUBY Driver
• CAP11PHP Driver
• Ado.net5.mxz

Majorly SQL commands can be divided into three categories, i.e., DDL, DML & DCL. Data Definition Language (DDL) deals with all the database schemas, and it defines how the data should reside in the database. Commands like CreateTABLE and ALTER TABLE are part of DDL.

Data Manipulative Language (DML) deals with operations and manipulations on the data. The commands in DML are Insert, Select, etc.

Data Control Languages (DCL) are related to the Grant and permissions. In short, the authorization to access any part of the database is defined by these.

CHAR_LENGTH  is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.

DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

Some of the advantages of JavaScript are:

• Server interaction is less
• Feedback to the visitors is immediate
• Interactivity is high
• Interfaces are richer

To submit a form using JavaScript use document.form[0].submit();

document.form[0].submit();

Escape characters (Backslash) is used when working with special characters like single quotes, double quotes, apostrophes and ampersands. Place backslash before the characters to make it display.

Example:

document.write "I m a "good" boy"
document.write "I m a "good" boy"