Top 50 Mysql Interview Questions You Must Prepare 19.Mar.2024

It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

Row-level locking, tractions, foreign key constraints and crash recovery.

Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.

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.

Join is data retrieval operation that combines multiple query outputs of the same structure into a single output. By default the MySQL UNION removes all duplicate rows from the result set even if you don’t explicit using DISTINCT after the keyword UNION.

  SELECT customerNumber id, contactLastname name  FROM customers  UNION
  SELECT employeeNurrber id, firstname name  FROM employees

id name

103 Schmitt
112 King
114 Ferguson
119 Labrune
121 Bergulfsen

 

Both CHAR and NCHAR are fixed length string data types. But they have the following differences:

  • CHARs full name is CHARACTER.
  • NCHARs full name is NATIONAL CHARACTER.
  • By default, CHAR uses ASCII character set. So 1 character is always stored as 1 byte.
  • By default, NCHAR uses Unicode character set. NCHAR data are stored in UTF8 format. So 1 character could be stored as 1 byte or upto 4 bytes.
  • Both CHAR and NCHAR columns are defined with fixed lengths in units of characters.

When you’re not deleting by row ID. Such as in DELETE FROM techpreparation_com_questions ORDER BY timestamp LIMIT 1.

SELECT book_title FROM books LIMIT 25, @The first number in LIMIT is the offset, the second is the number.

SELECT DATE_FORMAT(techpreparation_timestamp, ‘%Y-%m-%d’) from techpreparation_questions; A similar TIME_FORMAT function deals with time.

MySQL (pronounced "my ess cue el") is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility.

It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.

A default value is used on initialization, a current timestamp is inserted on update of the row.

BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

% corresponds to 0 or more characters, _ is exactly one character.

BDB (BerkeleyDB) is traction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an Oracle company now).

What it me is that so of the data that you’re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.

ADDDATE(techpreparation_publication_date, INTERVAL 3 MINUTE)

mysql_fetch_array — Fetch a result row as an associative ARRAY, a numeric array, or both
mysql_fetch_object — Fetch a result row as an OBJECT.

SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

If you want to see the index you have just created for an existing table, you can use the “SHOW INDEX FROM tableName” command to get a list of all indexes in a given table.

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.

A traction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the traction concept to allow users to group one or more SQL statements into a single traction, so that the effects of all the SQL statements in a traction can be either all committed (applied to the database) or all rolled back (undone from the database).

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.

An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.

@VARCHARs with length less than 4 become CHARs
@CHARs with length more than 3 become VARCHARs.
@NOT NULL gets added to the columns declared as PRIMARY KEYs
@Default values such as NULL are specified for each column

It compressed the MyISAM tables, which reduces their disk usage.

SELECT COUNT (user_id) FROM users would only return the number of user_id’s.

Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.

mysql is the object-oriented version of mysql library functions.

If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name. The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache.

That field gets the current timestamp whenever the row gets altered.

ALTER TABLE techpreparation_questions CHANGE techpreparation_content techpreparation_CONTENT VARCHAR(50).

MySQL config variable max_heap_table_size.

You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);

A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL. Since computers are very good at handling large amounts of data, database management plays a central role in computing, as stand-alone utilities, or as parts of other applications.

Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.

MySQL is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. MySQL also has a very practical set of features developed in very close cooperation with our users. You can find a performance comparison of MySQL to some other database managers on our benchmark page. See section 12.7 Using Your Own Benchmarks. MySQL was originally developed to handle very large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. Though under constant development, MySQL today offers a rich and very useful set of functions. The connectivity, speed, and security make MySQL highly suited for accessing databases on the Internet.

ALTER TABLE techpreparation_questions ENGINE innodb;

999.99 to -99.9@Note that with the negative number the minus sign is considered one of the digits.

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.

A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.

Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.

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.

Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.

tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command note.

CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.

SHOW INDEX FROM techpreparation_questions;