- What Is MySQL?
MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing content in a database. It is most noted for its quick processing, proven reliability, ease and flexibility of use.
- Why MySQL Is Used?
Some of the top reasons are given below.
Scalability and Flexibility
Web and Data Warehouse Strengths
Robust Transactional Support
Comprehensive Application Development
Strong Data Protection
Lowest Total Cost of Ownership
Open Source Freedom and 24 x 7 Support
- What Are The Technical Features Of MySQL?
You can have a complete list of technical features of MySQL from here.
- What is the default port for MySQL Server?
The default port for MySQL server is 3306.
- What Are Heap Tables?
Tables that are present in the memory are called as HEAP tables. When creating a HEAP table in MySql, user needs to specify the TYPE as HEAP. These tables are now more 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.
- Difference Between Float And Double?
They both represent floating point numbers. A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers.
MySQL uses four bytes for single-precision values and eight bytes for double-precision values. (Source)
- CHAR_LENGTH And LENGTH?
CHAR_LENGTH, as the name suggests, returns the number of characters / character count. The LENGTH returns the number of bytes / bytes count. To count the Latin characters, both lengths are the same. To count Unicode and other encodings, the lengths are different.
- What are the advantages of MySQL over Oracle?
Performance tuning capabilities
- What Is BLOB?
A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB , BLOB , MEDIUMBLOB , and LONGBLOB . These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT , TEXT , MEDIUMTEXT , and LONGTEXT .
- How Many Triggers Are Allowed In MySQL Table?
Six triggers are allowed in Mysql table:
- What is TEXT?
It’s an case-insensitive BLOB. There are four types of TEXT:
- Difference Between BLOB And Text?
BLOB is used for storing binary data while Text is used to store large string.
- How are MySQL timestamps seen to a user?
In a readable format : YYYY-MM-DD HH:MM:SS.
- How would you get the current date in Mysql?
By using command SELECT CURRENT_DATE();
- How one can concatenate strings in MySQL?
By using CONCAT (string1, string2, string3)
- How do you control the max size of a HEAP table?
Using MySQL config variable called max_heap_table_size.
- How would you enter Characters as HEX Numbers?
Enter HEX numbers with single quotes and a prefix of (X).
- You need to show all the indexes defined in a table say ‘user’ of Database say ‘Mysql’. How will you achieve this?
Using mysql> show index from user;
- How will you export tables as an XML file in MySQL?
Using command mysql -u USER_NAME –xml -e ‘SELECT * FROM table_name’ > table_name.xml
- How will you get current date in MySQL?
Using mysql> SELECT CURRENT_DATE();
- You want to see only certain rows from a result set from the beginning or end of a result set. How will you do it?
Using Command mysql> SELECT * FROM name LIMIT 1; (To Show 1 Record) (Source)
- Login In MySQL With Unix Shell?
[mysql dir]/bin/mysql -h hostname -u root -p pass
- Create a database on the mysql server with unix shell.
mysql> create database databasename;
- How you will list or view all databases from the mysql server?
mysql> show databases;
- How to use a database?
mysql> use databasename;
- See all the tables from a database of mysql server.
mysql> show tables;
- How to see table’s field formats or description of table .
mysql> describe tablename;
- Delete a database from mysql server.
mysql> drop database databasename;
- How you will get Sum of column?
mysql> SELECT SUM(*) FROM [table name];
- How to delete a table?
mysql> drop table tablename;
- Show all data from a table.
mysql> SELECT * FROM tablename;
- How to returns the columns and column information pertaining to the designated table?
mysql> show columns from tablename;
- Show certain selected rows with the value “xyz”.
mysql> SELECT * FROM tablename WHERE fieldname = “xyz”;
- How you will show unique records?
mysql> SELECT DISTINCT columnname FROM tablename;
- Show selected records sorted in an ascending or descending order?
mysql> SELECT col1,col2 FROM tablename ORDER BY col2 DESC;
mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC;
- How to Return total number of rows?
mysql> SELECT COUNT(*) FROM tablename;
- How to Change a users password from unix shell?
[mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’
- How to Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server?
mysqld_safe –skip-grant-tables &
mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
/etc/init.d/mysql start (Source)
- Restore database from backup.
[mysql dir]/bin/mysql -u username -password databasename < /tmp/databasename.sql.
- How to dump a table from a database?
[mysql dir]/bin/mysqldump -c -u username -password databasename tablename > /tmp/databasename.tablename.sql
- How to dump one database for backup?
[mysql dir]/bin/mysqldump -u username -password –databases databasename >/tmp/databasename.sql
- How to dump all databases for backup. Backup file is sql commands to recreate all db’s?
[mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql
- How to Load a CSV file into a table?
mysql> LOAD DATA INFILE ‘/tmp/name.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ (field1,field2,field3);
- How to Update database permissions?
mysql> flush privileges;
- List the objects that can be created using CREATE statement?
Below objects can be created using CREATE statement:
- How many columns can be used for creating Index?
Maximum of 16 indexed columns can be created for any standard table.
- What is InnoDB?
InnoDB is a storage engine for MySQL. MySQL 5.5 and later use it by default. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity). (Source: Wikipedia)
- What are the drivers in MySQL?