Basic Mysql

1- What are HEAP tables?

HEAP tables are used for high-speed storage on a temporary basis and are present in memory.

They do not allow BLOB or TEXT fields.
- Only comparison operators like =, <, >, = >, =< can be used with them.
- AUTO_INCREMENT is not supported by HEAP tables
- Indexes should be NOT NULL

2- What is the difference between BLOB and TEXT?

- In BLOB sorting and comparison is performed in case-sensitive for BLOB values.
- In TEXT types sorting and comparison is performed case-insensitive.

3- Default port number of MySQL?

3306

4- Difference between primary key and candidate key in Mysql?

Primary Key

It is a column that uniquely identifies a record. In Mysql, only one candidate key can behave like a Primary Key.

Candidate Key

It can be any column that can qualify as a unique key in the database. In MySQL, there can be multiple candidate keys in one table. Each candidate key can behave as a primary key.

5- Difference between mysql_connect and mysql_pconnect?

Mysql_connect:
- Opens a new connection to the database.
- The database connection can be closed.
- Opens the page every time the page is loaded.
Mysql_pconnect:
- Opens a persistent connection to the database.
- The database connection can not be closed.
- The page need not be opened every time the page is loaded.

6-Difference between TRUNCATE and DELETE in MySQL?

DELETE operation deletes the data of a table but we can perform Commit and Rollback to retrieve data. 'Where' condition can be used along with a delete statement.

TRUNCATE is used to delete the data of a table permanently. You can't perform Commit and Rollback statement with TRUNCATE. Also, 'Where' condition can't be used along with TRUNCATE statement.

7-What is federated tables in MySQL?

It is a table that points to a table in another MySQL database instance. It can be seen as a view of this remote database.

8- Difference between DBMS and RDBMS?

  • DBMS is persistent and accessible when the data is created or exists, but RDBMS tells about the relation between the table and other tables.
  • RDBS supports a tabular structure for data and relationship between them in the system whereas DBMS supports only the tabular structure.
  • DBMS provide uniform methods for application that has to be independently accessed, but RDBMS doesn’t provide methods like DBMS but provide relationship which link one entity with another.

9-Difference between timestamp and datetime in MySQL?

Timestamp in MySQL
Timestamp in MySQL is used when users want to convert the value from the current time zone to UTC zone for the sake of storage or UTC time zone to the current time zone for retrieval.

DateTime in MySQL
DateTime in MySQL is used to insert values ‘yyyy-mm-dd’ in the table where yyyy is the year, mm is the month and dd is the date. This keyword is used to retrieve or to display DATETIME values in Mysql.

10- Difference between NOW() and CURRENT_DATE()?

NOW () command is used to show the current year, month, date with hours, minutes, and seconds.

CURRENT_DATE() shows the current year, month, and date only.