INNER JOIN : The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables
There is hard limit of 4096 columns in a MySQL table, but this also depends on some other factors as well like, size of the every column in the table, a row in table can be maximum 65,535 bites.
3. How many Maximum options can be there in SET and ENUM?
There can be maximum 64 options in ENUM, as well as in SET.
4. What is the difference ENUM and SET?
The difference between is only like Radio button form and checkbox in HTML form, ENUM is like Radio button user can only select one, while SET is like the Checkbox, user are able to select more than one value from the SET.
5. What is the difference between UNION and UNION ALL?
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
UNION ALL will indeed be more performant, specifically due to the lack of the distinct sort.
UNION ALL can be slower than UNION in specific cases like, where the network such as the internet, is a bottleneck
6. What does myisamchk do?
It compresses the MyISAM tables, which reduces their disk or memory usage.
7 . How many TRIGGERS are allowed in MySql table?
8 . What is the difference between BLOB AND TEXT?
A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB differ only in the maximum length of the values they can hold.
The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT correspond to the four BLOB types and have the same maximum lengths and storage requirements. The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive fashion for BLOB values and case-insensitive fashion for TEXT values. In other words, a TEXT is a case-insensitive BLOB.
9. What are HEAP tables in MySQL?
HEAP tables are in-memory. They are usually used for high-speed temporary storage.
No TEXT or BLOB fields are allowed within HEAP tables.
You can only use the comparison operators = and ‹=›.
HEAP tables do not support AUTO_INCREMENT.
Indexes must be NOT NULL.
10. How do you find out which auto increment was assigned on the last insert?
SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function.
11. What is difference between Primary key and Candidate key?
are used to uniquely identify each row of the table. A table can have only one primary Key.
primary key is also a candidate key. There is no difference. By common convention one candidate key is designated as a “primary key” and that key is used for any foreign key references.
12. How would you change a table to InnoDB?
ALTER TABLE name_file ENGINE innodb;
13. How do you get current user in mysql?
14. Explain the difference between FLOAT, DOUBLE and REAL?
FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes.
DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes.
REAL is a synonym of FLOAT for now.
15. How do you change a password for an existing user via mysqladmin?
mysqladmin -u root -p password “newpassword”
16. How to export MySQL database to SQL file?
mysqldump -h [mysql hostname] -u [mysql username] -p [database name which to export] > [file name to which to export the dump with (.)dot sql extension]