MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL). It's Original author - MySQL AB and it is written in C, C++.
Current veriosn of MySQL: 8.0 MySQL 8.0 New Features
This is the most important asked MySQL Interview Questions.
- DDL: Data Definition Language
- DML: Data Manipulation Language
- DCL: Data Control Language
- TCL: Transaction Control Language
- CREATE - to create objects in the database
- ALTER - alters the structure of the database
- DROP - delete objects from the database
- TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
- COMMENT - add comments to the data dictionary
- RENAME - rename an object
- SELECT - retrieve data from the a database
- INSERT - insert data into a table
- UPDATE - updates existing data within a table
- DELETE - deletes all records from a table, the space for the records remain
- MERGE - UPSERT operation (insert or update)
- CALL - call a PL/SQL or Java subprogram
- EXPLAIN PLAN - explain access path to data
- LOCK TABLE - control concurrency
- GRANT - gives user's access privileges to database
- REVOKE - withdraw access privileges given with the GRANT command
- COMMIT - save work done
- SAVEPOINT - identify a point in a transaction to which you can later roll back
- ROLLBACK - restore database to original since the last COMMIT
- SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
You may also like - MongoDB Interview Questions Answers
MySQL Interview Questions - MySQL Data Types
|CHAR(size)||Holds a fixed length string (can contain letters, numbers, and special characters).|
|VARCHAR(size)||Holds a variable length string (can contain letters, numbers, and special characters).|
|TINYTEXT||Holds a string with a maximum length of 255 characters|
|TEXT||Holds a string with a maximum length of 65,535 characters|
|BLOB||For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data|
|MEDIUMTEXT||Holds a string with a maximum length of 16,777,215 characters|
|MEDIUMBLOB||For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data|
|LONGTEXT||Holds a string with a maximum length of 4,294,967,295 characters|
|LONGBLOB||For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data|
|ENUM(A,B,C,etc.)||Let you enter a list of possible values. You can list up to 65535 values in an ENUM list.|
|SET||Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice.|
You may like this - Database Interview Questions
- PHP Driver
- JDBC Driver
- ODBC Driver
- C WRAPPER
- PYTHON Driver
- PERL Driver
- RUBY Driver
- Log into your server using SSH.
- Now, Login into MySQL
- Now you can list all database using - show databases; command
- Now choose your database using - use DATABSENAME; command
Following are some techniques of query optimization
- use Indexes
- select only required columns instead of slecting all columns with *
SQL injection is a code injection technique through which attacker can execute malicious SQL statements on your database server.
You can prevent SQL injection by using following:
- Sanitize input variables.
- Parameters binding.
- Using stored procedures
- Never use depericated functions etc
MySQL Interview Questions - MySQL Regular Expressions
Regular expressions in MySql are used in queries for searching a pattern in a string.
* Matches 0 more instances of the string preceding it.
+ matches 1 more instances of the string preceding it.
? Matches 0 or 1instances of the string preceding it.
. Matches a single character.
[abc] matches a or b or z
| separates strings
^ anchors the match from the start.
REGEXP can be used to match the input characters with the database.
MySQL Interview Questions - Database
- The database connection can be closed
- Opens the page every time the page is loaded.
- The database connection cannot be closed.
- The page need not be opened every time the page is loaded.
You may like this - Laravel Interview Questions Answers
A Stored Procedure is a routine or set of SQL Statements invoked with a CALL statement.
Read more in details about MySQL Stored Procedure
MySQL Interview Questions - MySQL Stored Procedure and Functions
|It Compiles only one time.||It Compiles every time.|
|Return values is not mandatory, It may return or not. It can use the IN, OUT, INOUT parameters||It must return value. IN, OUT and INOUT cannot be used in function|
|We can call function inside stored procedure||We can't call stored procedure inside function|
|Stored Procedure Supports DML Commands||Functions supports only SELECT Commands|
|It support TCL Commands||It does not support TCL Commands|
|Stored Procedure support Exception Handling||Function doesn’t support Exception Handling|
You may like this - MySQL Stored Procedure example with parameter
mysql_fetch_array(): Fetch a result row as an associative array, a numeric array, or both
Note: In place of "MYSQL_NUM" you can use "MYSQL_ASSOC" or "MYSQL_BOTH"
mysql_fetch_object(): it returns the result from the database as objects
mysql_fetch_row(): it returns result as an numeric
//Note: this extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0.
column name: It can be upto 64 chars.
table name: It can be upto 64 chars.
database name: It can be upto 64 chars.
You may like this - Node.js Interview Questions Answers
- CHAR_LENGTH() returns the length of the string measured in characters.
- LENGTH() returns the length of the string measured in bytes.
- SELECT TABLE_ROWS from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'employee';
- SELECT SQL_CALC_FOUND_ROWS * FROM employee;
- SELECT FOUND_ROWS();
To copy with indexes and triggers do these 2 queries:
To copy just structure and data use this one:
Use DISTINCT in the query
will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.
When you’re not deleting by row ID. Such as in DELETE FROM questions ORDER BY timestamp LIMIT 1.
This will delete the most recently posted question in the table questions.
You can do this in two ways
1) select salary from employee order by salary desc limit 1,1; 2) select max(salary) from employee where salary < (select max(salary) from employee);
You may like this - Core PHP Interview Questions Answers
Suppose Table Name is - users
Delete row with highest id:
Delete row with lowest id:
- WHERE act as Pre-filter whereas HAVING act as Post-filter.
- A WHERE is used to filter records before any groupings take place.
- HAVING is used to filter values after they have been groups. Only columns or expression in the group can be included in the HAVING clause’s conditions.
- WHERE always comes before GROUP BY and HAVING clause when both WHERE and HAVING clause are used in same query.
- HAVING clause can also be used without Group BY.
You may like this - SQL JOINs Types with Examples