MySQL Interview Basic Questions ..!!
MySQL Interview Questions
A rundown of top as often as possible asked MySQL talk with inquiries and answers are given underneath.
1) What is MySQL?
MySQL is a multi-threaded, multi-client SQL database the executives framework which has in excess of 11 million establishments. This is the world’s second most well known and broadly utilized open source database. It is fascinating how MySQL name was given to this inquiry language. The term My is instituted by the name of the girl of fellow benefactor Michael Widenius’ little girl, and SQL is the short type of Structured Query Language. Utilizing MySQL is free of expense for the engineer, yet endeavors need to pay a permit charge to Oracle.
Some time ago MySQL was at first possessed by a revenue driven firm MySQL AB, at that point Sun Micro-systems got it and afterward Oracle purchased Sun Micro-systems, so Oracle at present claims MySQL.
MySQL is an Oracle-upheld Relational Database Management System (RDBMS) which depends on organized inquiry language. MySQL underpins wide scopes of working frameworks most renowned of those incorporate Windows, Linux and UNIX. In spite of the fact that it is conceivable to build up a wide scope of use with MySQL, it is utilized for web applications and web based distributing. It is a key piece of an open source undertaking known as Lamp.
What is Lamp?
Light is a stage utilized for web improvement. Light utilizes Linux, Apache, MySQL, and PHP as a working framework, web server, database and article arranged scripting language individually. What’s more, consequently shortened as LAMP.
2) In which language MySQL has been composed?
C, C++ and SQL parser in yacc
3) What are the specialized details of MySQL?
MySQL has the accompanying specialized details –
Adaptable structure
Superior
Sensible and simple to utilize
Replication and high accessibility
Security and capacity the board
Drivers
Graphical Tools
MySQL Enterprise Monitor
MySQL Enterprise Security
JSON Support
Replication and High-Availability
Reasonability and Ease of Use
OLTP and Transactions
Geo-Spatial Support
4) What is the contrast among MySQL and SQL?
SQL is known as the standard inquiry language. It is utilized to associate with the database like MySQL. MySQL is a database that stores different kinds of information and protects it.
A PHP content is required to store and recover the qualities inside the database.
SQL is a code, though MySQL is a product or an application
SQL is utilized for the making of database the board frameworks while MySQL is utilized to empower information taking care of, putting away, erasing and adjusting information
5) What is the distinction among database and table?
There is a noteworthy distinction between a database and a table. The distinctions are as per the following:
Tables are an approach to speak to the division of information in a database while the database is a gathering of tables and information.
Tables are utilized to gather the information in connection with one another and make a data-set. This data-set will be utilized in the database. The information which are put away in the table in any structure is a piece of the database, however the switch isn’t valid.
A database is a gathering of sorted out information and furthermore includes which are utilized to get to them, though table is an accumulation of lines and segments which are utilized to store the information.
6) Why do we utilize the MySQL database server?
As a matter of first importance MYSQL server is allowed to use for designers and a little charge for undertakings.
MySQL server is open source.
The people group of MySQL is enormous and strong consequently any assistance seeing MySQL is settled at the earliest opportunity.
MySQL has entirely stable forms accessible, as MySQL has been in the market since quite a while so all bugs emerging in the past forms have been constantly expelled and a truly steady form is given after each update.
The MySQL database server is quick, dependable and simple to utilize. You can undoubtedly utilize and adjust the product. MySQL programming can be downloaded free of expense from the web.
7) What are the various tables present in MySQL?
There are numerous tables that stay present of course. Be that as it may, MyISAM is the default database motor utilized in MySQL. There are five sorts of tables that are available:
MyISAM
Store
Consolidation
INNO DB
ISAM
8) What is the distinction among CHAR and VAR-CHAR?
A rundown of contrasts among CHAR and VAR-CHAR:
Roast is variable-length though VARCHAR is of fixed length.
Singe and VARCHAR types are diverse away and recovery.
Singe segment length is fixed to the length that is announced while making a table. The length worth reaches from 1 and 255.
At the point when CHAR esteems are put away when they are correct cushioned utilizing spaces to a particular length. Trailing spaces are expelled when CHAR esteems are recovered.
Burn utilizes static memory portion while VAR-CHAR utilizes dynamic memory designation.
Singe is half quicker than VARCHAR.
9) What is the contrast among TRUNCATE and DELETE in MySQL?
TRUNCATE is a DDL order, DELETE is a DML direction.
It is absurd to expect to utilize Where direction with TRUNCATE yet you can utilize it with DELETE order.
TRUNCATE can’t be utilized with filed sees while DELETE can be utilized with recorded perspectives.
The DELETE direction is utilized to erase information from a table. It just erases the columns of information from the table while, truncate is hazardous direction and ought to be utilized cautiously on the grounds that it erases each line for all time from a table.
10) what number Triggers are conceivable in MySQL?
There are just six Triggers permitted to use in MySQL database.
Prior to Insert
After Insert
Prior to Update
After Update
Before Delete
After Delete
11) What is load table?
Tables that are available in memory is known as HEAP tables. When you make a load table in MySQL, you should need to determine the TYPE as HEAP. These tables are regularly known as memory tables. They are utilized for fast stockpiling on a brief premise. They don’t permit BLOB or TEXT fields.
12) What is BLOB and TEXT in MySQL?
Mass is an abbreviation represents an enormous double object. It is utilized to hold a variable measure of information.
There are four kinds of BLOB.
TINY-BLOB
Mass
MEDIUM-BLOB
LONG BLOB
The distinctions among all these are the greatest length of qualities they can hold.
Content is a case-inhumane BLOB. Content qualities are non-twofold strings (character string). They have a character set, and qualities are put away, and looked at dependent on the resemblance of the character set.
There are four kinds of TEXT.
TINY TEXT
Content
MEDIUM-TEXT
LONG-TEXT
13) What is a trigger in MySQL?
A trigger is a lot of codes that executes because of certain occasions.
14) What is the distinction between store table and brief table?
Store tables:
Store tables are found in memory. They are utilized for fast stockpiling on a brief premise. They don’t permit BLOB or TEXT fields.
Load tables don’t bolster AUTO_INCREMENT.
Lists ought to be NOT NULL.
Brief tables:
The brief tables are utilized to keep the transient information. Now and again it is helpful in cases to hold impermanent information. The Temporary table is erased after the present customer session ends.
Fundamental contrasts:
The store tables are shared among customers while transitory tables are not shared.
Load tables are simply one more stockpiling motor, while for transitory tables you need an extraordinary benefit (make brief table).
15) What is the contrast among FLOAT and DOUBLE?
Buoy stores drifting point numbers with precision up to 8 places and assigns 4 bytes, then again DOUBLE stores coasting point numbers with exactness up to 18 places and dispenses 8 bytes.
16) What are the benefits of MySQL in contrast with Oracle?
MySQL is a free, quick, solid, open source social database while Oracle is costly, despite the fact that they have given Oracle free version to draw in MySQL clients.
MySQL utilizes just barely under 1 MB of RAM on your PC while Oracle 9i establishment uses 128 MB.
MySQL is extraordinary for database empowered sites while Oracle is made for undertakings.
MySQL is convenient.
17) What are the weaknesses of MySQL?
MySQL isn’t so proficient for huge scale databases.
It doesn’t support COMMIT and STORED PROCEDURES capacities form under 5.0.
Exchanges are not taken care of in all respects effectively.
Usefulness of MySQL is profoundly needy of different ad-dons.
Improvement isn’t network driven.
18) What is the distinction among CHAR and VARCHAR?
Burn and VARCHAR are vary away and recovery.
Burn section length is fixed while VARCHAR length is variable.
The most extreme no. of character CHAR information type can hold is 255 character while VARCHAR can hold up to 4000 character.
Burn is half quicker than VARCHAR.
Burn utilizes static memory distribution while VARCHAR utilizes dynamic memory allotment.
19) What is the distinction among MySQL_connect and MySQL_pconnect?
Mysql_connect:
It opens another association with the database.
Each time you have to open and close database association, contingent upon the solicitation.
Opens page at whatever point it is stacked.
Mysql_pconnect:
In Mysql_pconnect, “p” represents persevering association so it opens the tireless association.
The database association can’t be shut.
It is increasingly helpful if your site has more traffic in light of the fact that there is no compelling reason to open and close association as often as possible and each time when page is stacked.
20) What does “i_am_a_dummy banner” do in MySQL?
The “i_am_a_dummy banner” empowers MySQL motor to decline any UPDATE or DELETE explanation to execute if the WHERE proviso is absent. Thus it can spare the software engineer from erasing the whole table my mix-up on the off chance that he doesn’t utilize WHERE condition.
20) What does “i_am_a_dummy banner” do in MySQL?
The “i_am_a_dummy banner” empowers MySQL motor to reject any UPDATE or DELETE proclamation to execute if the WHERE condition is absent. Consequently it can spare the software engineer from erasing the whole table my misstep on the off chance that he doesn’t utilize WHERE proviso.
21) How to get the present date in MySQL?
To get current date, utilize the accompanying language structure:
SELECT CURRENT_DATE();
22) What are the security alarms while utilizing MySQL?
Introduce antivirus and arrange the working framework’s firewall.
Never utilize the MySQL Server as the UNIX root client.
Change root username and secret key Restrict or impair remote access.
23) How to change a secret key for a current client by means of Mysqladmin?
Mysqladmin – u root – p secret key “newpassword”.
24) What is the distinction between Unix timestamps and MySQL timestamps?
All things considered both Unix timestamp and MySQL timestamp are put away as 32-piece whole numbers however MySQL timestamp is spoken to in discernible configuration of YYYY-MM-DD HH:MM:SS position.