1. What is MySQL?
Answer: MySQL is an open source DBMS which is developed and distributed by Oracle Corporation.
It is supported by most of the popular operating systems, such as Windows, Linux etc. It can be used to develop a different type of applications but it is mainly used for developing web applications.
MySQL uses GPL (GNU General Public License) license so that anyone can download and install it for developing those applications which will be published or distributed freely. But if a user wants to develop any commercial application using MySQL then he/she will need to buy the commercial version of MySQL.
2. What are the features of MySQL?
Answer: MySQL has several useful features which make it a popular database management software.
Some important features of MySQL are mentioned below.
- It is reliable and easy to use too.
- It is the suitable database software for both large and small application.
- Anyone can install and use it at no cost.
- It is supported by many well-known programming languages, such as PHP, Java, C++, PERL etc.
- It supports standard SQL (Structured Query Language).
- The open source license of MySQL is customizable. Hence, a developer can modify it according to the requirements of the application.
3. What Is Ddl, Dml And Dcl ?
Answer : If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.
4. Explain The Difference Between Mysql And Mysql Interfaces In Php?
Answer : mysql is the object-oriented version of mysql library functions.
5. How Do You Change A Password For An Existing User Via Mysqladmin?
Answer : mysqladmin -u root -p password “newpassword”
6. What Are Some Good Ideas Regarding User Security In Mysql?
Answer : There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.
7. Explain The Difference Between Myisam Static And Myisam Dynamic. ?
Answer : In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.
8. Explain Advantages Of Myisam Over Innodb?
Answer : Much more conservative approach to disk space management – each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
9. How can you find out the version of the installed MySQL?
Answer: The version of the installed MySQL server can be found out easily by running the following command from the MySQL prompt.
mysql> SHOW VARIABLES LIKE “%version%”;
10. What is the purpose of using a HEAP table?
Answer: The table which uses a hashed index and stores in the memory is called HEAP table. It works as a temporary table and it uses the indexes that make it faster than another table type.
When MySQL crashes for any reason then all the data stored in this table can be lost. It uses fixed length data types. Hence BLOB and TEXT data types are not supported by this table. It is a useful table for those MySQL tasks where speed is the most important factor and temporary data is used.
11. What is an index? How can an index be declared in MySQL?
Answer: An index is a data structure of MySQL table that is used to speed up the queries.
It is used by the database search engine to find out the records faster. One or more fields of a table can be used as an index key. Index key can be assigned at the time of table declaration or can be assigned after creating the table.
Example:
username and email fields are set as the index in the following create table statement.
The following command will show the index key information of the ‘users’ table.
SHOW INDEXES FROM users;
12. What is the difference between Primary Key and Unique Key?
Answer : Both Primary and Unique Key is implemented for Uniqueness of the column. Primary Key creates a clustered index of column where as an Unique creates unclustered index of column. Moreover, Primary Key doesn’t allow NULL value, however Unique Key does allows one NULL value.
13. How many TRIGGERS are possible in MySql?
Answer : There are only six triggers are allowed to use in MySQL database and they are.
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete
14. What is Heap table?
Answer : Tables that are present in the memory are called as HEAP tables. These tables are commonly known as memory tables. These memory tables never have values with data type like “BLOB” or “TEXT”. They use indexes which make them faster.
15. What’s The Difference Between Char_length And Length?Answer : The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.
16. How Mysql Optimizes Limit ?
Answer : In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING: If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BY’s.
As soon as MySQL has sent the first # rows to the client, it will abort the query.
LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
The size of temporary tables uses the LIMIT # to calculate how much space is needed to resolve the query.
17. What Is Transaction?Answer : A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).
18. How To Use Like Conditions?
Answer : A LIKE condition is also called pattern patch. There are 3 main rules on using LIKE condition:
- is used in the pattern to match any one character.
- % is used in the pattern to match any zero or more characters.
- ESCAPE clause is used to provide the escape character in the pattern.
19. What are the advantages of MySQL in comparison to Oracle?
Answer : MySQL is open source software available at zero cost.
– It is portable.
– GUI with command prompt.
– Administration is supported by MySQL Query Browser.
20. What is BLOB?
Answer : BLOB stands for binary large object.
– It that can hold a variable amount of data.
Thanks for subscribing! Please check your email for further instructions.