An Overview of MySQL
MySQL is one of the widely used open source database platforms in the world today. According to mysql.com website, this platform is trusted by the world’s 10 top sites that are considered trustworthy and are frequency visited. The data in a MySQL database is stored in the form of tables, which consist of many columns and rows. It is a reliable, stable and robust platform with advanced performance and security features.
Overviewing these PHP MySQL advanced interview questions for experienced as well as individuals and the other crucial factors mentioned here can be remarkably result oriented for candidates and new learners to this niche.
Development History of MySQL
The first version of MySQL was released in May 1995 by a Swedish company MySQL AB.
Latest Version: The most recent version is 8.0.12.
To get into complete detail of MySQL interviews, we are mentioning some of the essential MySQL interview questions and answers for the experience. Go through the issues as they will help you.
- Data Security
- On-Demand Scalability
- High Performance
- Excellent Uptime
- Lowered TCO (Total Cost of Ownership)
For those who are looking for mysql interview questions and answers, we have also included some of the problems for them. Go through it.
Mysql Interview Questions and answers
Looking for a new job? Do not miss to read our Mysql Interview Questions and answers. Whether you are a fresher or an experienced, these questions and answers that can help you to crack your interview.
- What is MySQL and why it is used?
- Who developed Mysql?
- In which year Mysql created?
- Is Mysql is a programming language?
- What are the advantages of Mysql?
- What is the difference between MySQL and SQL?
- Explain Storage engines in Mysql a how many types of engines are in Mysql?
- How we can change the database engine in Mysql?
- In Mysql, what is the default storage engine?
- Explain the difference between and MyISAM and InnoDB?
- What is the default port for MySQL and how it can change?
- List data types in Mysql? Explain
- How to get a version of Mysql?
- How do I import database through command line?
- Explain the difference between primary key and candidate key in Mysql?
- What is MySQL aggregate functions? Explain
- Explain the difference between procedure and function in MySQL?
- In Mysql, what is joins? Explain
- What is TRIGGERS and how it can be used in MySQL?
- What is the difference between the primary and unique key in Mysql?
- Explain the difference between CHAR and VARCHAR in Mysql?
- How we can get the current date in MySQL?
- How to get Nth highest salary from table in a MySQL?
- Explain the difference between NOW and CURRENT_DATE in Mysql?
- How we can create index in mysql?
- List some comparisons operators used in Mysql?
- What are procedures in Mysql? Explain
- How to display top 10 rows in Mysql?
- What is the difference between delete, drop and truncate?
- What is the difference between having and where clause in Mysql? Explain
- How we can get distinct columns values in MySql?
- How we can see all indexes defined for a table in Mysql?
- How is the use of CONCAT() in Mysql?
- How can we get total number of records by query in Mysql?
- What is the difference between IS NULL & IS NOT NULL?
- What is best to upload a large file in MySQL?
- What is the difference between BETWEEN and IN operators in Mysql?
- How to make a copy values from one column to another in Mysql?
MySQL is one of the widely used open source database platforms in the world today. The data in a MySQL database is stored in the form of tables, which consist of many columns and rows. It is a reliable, stable and robust platform with advanced performance and security features.
MySQL is used for a wide range of purposes like data warehousing, e-commerce, web applications, and logging applications.
- Mysql is available free because it is open source, but SQL is not an open source.
- Mysql offers updateable views, but SQL offers indexed views which are more powerful.
- Mysql does not support XML, but SQL supports XML.
- Mysql not supported Auto tuning but SQL can.
- Mysql did not support User-defined functions, but SQL supported.
- Mysql support Transaction is very much limited but SQL support extensively and entirely offered.
- Mysql not offered Stored procedures and full joins but offered in SQL.
- Mysql does not support Cursor feature but is there in SQL.
Storage engines are Mysql components, that can handle the operations for different table types to store and manage information in a database.
- ARCHIVE etc
ALTER TABLE EnterTableName ENGINE = EnterEngineName;
These are most commonly used storage engine in MySQL are MyISAM and InnoDB.
Difference between MyISAM and InnoDB are given below:-
- MyISAM does not support transactions, but InnoDB supports transactions.
- MyISAM supports Table-level Locking, but InnoDB supports Row-level Locking.
- MyISAM supports full-text search, but InnoDB does not.
- MyISAM designed for the need of speed but InnoDB designed for maximum performance.
- MyISAM does not support foreign keys, but InnoDB supports foreign keys.
- We can use commit and rollback with InnoDB but not in MyISAM.
- MyISAM does not support ACID (Atomicity, Consistency, Isolation, and Durability) but InnoDB supports the ACID property.
- In InnoDB table, AUTO_INCREMENT field is a part of the index but not in MyISAM.
- MyISAM stores its tables, data, and indexes in disk space using a separate table name.FRM, table name.MYD and table name.MYI but InnoDB stores its tables and indexes in a tablespace.
The default port is 3306. We can change it in
/etc/MySQL/my.conf there is a port variable. We can update this port according to our need
MySQL -u username -p database_name < file.sql
Here username is your database username like "root," database_name is your database name, file.sql is your SQL file name with complete path.
Primary Key: It is a column that uniquely identifies a record. In Mysql, only one candidate key can behave like Primary Key.
Candidate Key: It can be any column that can qualify as unique key in the database. In MySQL, there can be multiple candidate keys in one table. Each candidate key can behave like as a primary key.
The MySQL aggregate function performs a calculation on a set of values, and it returns a single value as output. It ignores NULL values when it performs calculation except for the COUNT function.
MySQL provides many aggregate functions that are listed below.
- MAX() etc
Functions are generally used for computations, but procedures are usually used for executing business logic.
The function returns only 1 value, but the procedure can return multiple values.
The function can be called directly by SQL statement like select functionName from dual, but procedures cannot be called directly.
The function can be used in the SQL Query directly, but a procedure can not be used in a SQL query.
In Mysql, joins are used to retrieve data from multiple tables. It is performed whenever two or more tables are joined.
There are three types of MySQL joins:-
- MySQL INNER JOIN
SELECT columns FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column;
- MySQL LEFT JOIN
SELECT columns FROM table_1 LEFT JOIN table_2 ON table_1.column = table_2.column;
- MySQL RIGHT JOIN
SELECT columns FROM table_1 RIGHT JOIN table_2 ON table_1.column = table_2.column;
In Mysql, a trigger is a database object that is directly associated with a table. It will be activated when a defined action is executed for the table. It can be performed when you run one of the following MySQL like INSERT, UPDATE and DELETE occurred in a table. It's activation time can be BEFORE or AFTER
mysql> delimiter //
mysql> CREATE TRIGGER age_check BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;
mysql> delimiter ;
1. A table can hold only one primary key, but there can be more than one unique key in one table.
2. A Primary Key cannot be NULL but Unique key can have NULL.
- CHAR can have a maximum of 255 characters, but VARCHAR can hold a maximum of 65,535 characters.
- CHAR field is a fixed length, but VARCHAR is a variable length field.
- CHAR uses static memory allocation, but VARCHAR uses dynamic memory allocation.
We can use
SELECT * FROM Employee Emp1 WHERE (1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)
CURRENT_DATE() will only give you the current date in format "YYYY-MM_DD" but
NOW() give you the current date time in the format 'YYYY-MM_DD HH:MM: SS'
Comparisons operators are used to comparing one expression to another value or expression. It is just like = , < , > , => , =<, <>
- It removes all rows from a table.
- It does not require a WHERE clause.
- Truncate cannot be used with indexed views.
- It is performance wise faster.
- It removes Some or All rows from a table.
- A WHERE clause is used to remove particular rows according to the matched condition. All rows will be deleted when we did not use Where condition in Query.
- It removes rows one by at a time.
- It can be used with indexed views.
- It removes a table from the database.
- All table's rows, indexes, and privileges will also be removed when we used this command.
- The operation cannot be rolled back.
WHERE term is used for filtering rows, and it applies to every row but HAVING term is used to filter groups.
WHERE can be used without the GROUP BY but HAVING clause cannot be used without the GROUP BY.
SELECT DISTINCT name FROM Users;
// Here name is the field & Users is the Table name
SHOW INDEX FROM TABLE_NAME;
select count(*) from TABLE_NAME where 'status' = 1;
IS NULL checks to see if the cell is empty but
IS NOT NULL checks to see if the cell is not empty.
SELECT id FROM users WHERE 'user_type' IS NOT NULL;
SELECT id FROM users WHERE 'user_type' NOT IS NULL;
It is the best way to upload large CSV files through the LOAD DATA command.
LOAD DATA LOCAL INFILE "your file name"
INTO TABLE table name
FIELDS TERMINATED by ’,’
LINES TERMINATED BY ’\n’
Between operator is used to select a range of data between two values. It can be texts, numbers, and dates, etc.
Select * from TABLENAME where FIELDNAME between VALUE1 and VALUE2
IN operator is used to check for a value in the given set of values.
Select * from TABLENAME where FIELDNAME IN ('VALUE1','VALUE2',...)
UPDATE `your_table_name` SET new_field=old_field