MySQL Interview Questions And Answers Set - 3

MySQL Interview Questions And Answers Set - 3



MySQL is a relational database management system.
A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. The tables are linked by defined relations making it possible to combine data from several tables on request. The SQL part of MySQL stands for "Structured Query Language" - the most common standardized language used to access databases.

MySQL is Open Source Software.
Open source means that it is possible for anyone to use and modify. Anybody can download MySQL from the Internet and use it without paying anything. Anybody so inclined can study the source code and change it to fit their needs. MySQL uses the GPL (GNU General Public License) http://www.gnu.org, to define what you may and may not do with the software in different situations. If you feel uncomfortable with the GPL or need to embed MySQL into a commercial application you can buy a commercially licensed version from us.

Why use MySQL?
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.

The technical features of MySQL
For advanced technical information, see section 7 MySQL Language Reference. MySQL is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and a programming interface. We also provide MySQL as a multi-threaded library which you can link into your application to get a smaller, faster, easier to manage product. MySQL has a lot of contributed software available.

It is very likely that you will find that your favorite application/language already supports MySQL. The official way to pronounce MySQL is ``My Ess Que Ell'' (not MY-SEQUEL). But we try to avoid correcting people who say MY-SEQUEL.

The Main Features of MySQL
The following list describes some of the important characteristics of MySQL:

Fully multi-threaded using kernel threads. That means it can easily use multiple CPUs if available.
C, C++, Eiffel, Java, Perl, PHP, Python and Tcl APIs.
Works on many different platforms.
Many column types: signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, and ENUM types.
Very fast joins using an optimized one-sweep multi-join.
Full operator and function support in the SELECT and WHERE parts of queries. Example:
mysql> SELECT CONCAT(first_name, " ", last_name) FROM tbl_name
WHERE income/dependents > 10000 AND age > 30;

SQL functions are implemented through a highly optimized class library and should be as fast as they can get! Usually there shouldn't be any memory allocation at all after query initialization.
Full support for SQL GROUP BY and ORDER BY clauses. Support for group functions (COUNT(), COUNT(DISTINCT), AVG(), STD(), SUM(), MAX() and MIN()).
Support for LEFT OUTER JOIN and RIGHT OUTER JOIN with ANSI SQL and ODBC syntax.
You can mix tables from different databases in the same query (as of Version 3.22).
A privilege and password system that is very flexible and secure and allows host-based verification. Passwords are secure because all password traffic is encrypted when you connect to a server.
ODBC (Open-DataBase-Connectivity) support for Win32 (with source). All ODBC 2.5 functions and many others. For example, you can use MS Access to connect to your MySQL server. See section 18 MySQL ODBC Support.
Very fast B-tree disk tables with index compression.
Up to 32 indexes per table are allowed. Each index may consist of 1 to 16 columns or parts of columns. The maximum index length is 500 bytes (this may be changed when compiling MySQL). An index may use a prefix of a CHAR or VARCHAR field. Fixed-length and variable-length records.
In-memory hash tables which are used as temporary tables.
Handles large databases. We are using MySQL with some databases that contain 50,000,000 records and we know of users that uses MySQL with 60,000 tables and about 5,000,000,000 rows
All columns have default values. You can use INSERT to insert a subset of a table's columns; those columns that are not explicitly given values are set to their default values.
Uses GNU Automake, Autoconf, and libtool for portability.
Written in C and C++. Tested with a broad range of different compilers.
A very fast thread-based memory allocation system.
No memory leaks. Tested with a commercial memory leakage detector (purify).
Includes myisamchk, a very fast utility for table checking, optimization, and repair. See section 15 Maintaining a MySQL Installation.
Full support for several different character sets, including ISO-8859-1 (Latin1), big5, ujis, and more. For example, the Scandinavian characters `@ringaccent{a}', `@"a' and `@"o' are allowed in table and column names.
All data are saved in the chosen character set. All comparisons for normal string columns are case insensitive.
Sorting is done according to the chosen character set (the Swedish way by default). It is possible to change this when the MySQL server is started up. To see an example of very advanced sorting, look at the Czech sorting code. MySQL supports many different character sets that can be specified at compile and run time.
Aliases on tables and columns are allowed as in the SQL92 standard.
DELETE, INSERT, REPLACE, and UPDATE return how many rows were changed (affected). It is possible to return the number of rows matched instead by setting a flag when connecting to the server.
Function names do not clash with table or column names. For example, ABS is a valid column name. The only restriction is that for a function call, no spaces are allowed between the function name and the `(' that follows it. See section 7.39 Is MySQL Picky About Reserved Words?.
All MySQL programs can be invoked with the --help or -? options to obtain online assistance.
The server can provide error messages to clients in many languages. See section 10.1 What Languages Are Supported by MySQL?. Clients may connect to the MySQL server using TCP/IP Sockets, Unix Sockets (Unixes), or Named Pipes (NT). The MySQL-specific SHOW command can be used to retrieve information about databases, tables, and indexes. The EXPLAIN command can be used to determine how the optimizer resolves a query.

Installing a MySQL Binary Distribution
You need the following tools to install a MySQL binary distribution:
GNU gunzip to uncompress the distribution.
A reasonable tar to unpack the distribution. GNU tar is known to work. Sun tar is known to have problems.
An alternative installation method under Linux is to use RPM (RedHat Package Manager) distributions.

If you run into problems, PLEASE ALWAYS USE mysqlbug when posting questions to mysql@lists.mysql.com. Even if the problem isn't a bug, mysqlbug gathers system information that will help others solve your problem. By not using mysqlbug, you lessen the likelihood of getting a solution to your problem! You will find mysqlbug in the `bin' directory after you unpack the distribution.

The basic commands you must execute to install and use a MySQL binary distribution are:

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s mysql-VERSION-OS mysql
shell> cd mysql
shell> scripts/mysql_install_db
shell> chown -R mysql /usr/local/mysql
shell> chgrp -R mysql /usr/local/mysql
shell> bin/safe_mysqld --user=mysql &


You can add new users using the bin/mysql_setpermission script if you install the DBI and Msql-Mysql-modules Perl modules.
A more detailed description follows.

Pick the directory under which you want to unpack the distribution, and move into it. In the example below, we unpack the distribution under `/usr/local' and create a directory `/usr/local/mysql' into which MySQL is installed. (The following instructions therefore assume you have permission to create files in `/usr/local'. If that directory is protected, you will need to perform the installation as root.)

How to Get MySQL. MySQL binary distributions are provided as compressed tar archives and have names like `mysql-VERSION-OS.tar.gz', where VERSION is a number (for example, 3.21.15), and OS indicates the type of operating system for which the distribution is intended (for example, pc-linux-gnu-i586). Add a user and group for mysqld to run as:
shell> groupadd mysql
shell> useradd -g mysql mysql

These commands add the mysql group and the mysql user. The syntax for useradd and groupadd may differ slightly on different Unixes. They may also be called adduser and addgroup. You may wish to call the user and group something else instead of mysql.
Change into the intended installation directory:
shell> cd /usr/local>
Unpack the distribution and create the installation directory:
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s mysql-VERSION-OS mysql

The first command creates a directory named `mysql-VERSION-OS'. The second command makes a symbolic link to that directory. This lets you refer more easily to the installation directory as `/usr/local/mysql'.
Change into the installation directory:
shell> cd mysql

You will find several files and subdirectories in the mysql directory. The most important for installation purposes are the `bin' and `scripts' subdirectories.
`bin'
This directory contains client programs and the server You should add the full pathname of this directory to your PATH environment variable so that your shell finds the MySQL programs properly.
`scripts'
This directory contains the mysql_install_db script used to initialize the server access permissions.
If you would like to use mysqlaccess and have the MySQL distribution in some nonstandard place, you must change the location where mysqlaccess expects to find the mysql client. Edit the `bin/mysqlaccess' script at approximately line 18. Search for a line that looks like this:
$MYSQL = '/usr/local/bin/mysql'; # path to mysql executable

Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, you will get a Broken pipe error when you run mysqlaccess.
Create the MySQL grant tables (necessary only if you haven't installed MySQL before):
shell> scripts/mysql_install_db

Note that MySQL versions older than Version 3.22.10 started the MySQL server when you run mysql_install_db. This is no longer true! Change ownership of the installation directory to the user that you will run mysqld as:
shell> chown -R mysql /usr/local/mysql
shell> chgrp -R mysql /usr/local/mysql

The first command changes the owner attribute of the files to the mysql user, and the second changes the group attribute to the mysql group.
If you would like MySQL to start automatically when you boot your machine, you can copy support-files/mysql.server to the location where your system has its startup files. More information can be found in the support-files/mysql.server script itself.
After everything has been unpacked and installed, you should initialize and test your distribution.

You can start the MySQL server with the following command:

shell> bin/safe_mysqld --user=mysql &

MySQL - Quick Installation Overview
The basic commands you must execute to install a MySQL source distribution are:

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql
shell> make
shell> make install
shell> scripts/mysql_install_db
shell> chown -R mysql /usr/local/mysql
shell> chgrp -R mysql /usr/local/mysql
shell> /usr/local/mysql/bin/safe_mysqld --user=mysql &

If you start from a source RPM, then do the following:

shell> rpm --rebuild MySQL-VERSION.src.rpm

This will make a binary RPM that you can install.

You can add new users using the bin/mysql_setpermission script if you install the DBI and Msql-Mysql-modules Perl modules.
A more detailed description follows.

Pick the directory under which you want to unpack the distribution, and move into it.
If you are interested in using Berkeley DB tables with MySQL, you will need to obtain a patched version of the Berkeley DB source code. Please read the chapter on Berkeley DB tables before proceeding.
MySQL source distributions are provided as compressed tar archives and have names like `mysql-VERSION.tar.gz', where VERSION is a number like 3.23.33.
Add a user and group for mysqld to run as:
shell> groupadd mysql
shell> useradd -g mysql mysql

These commands add the mysql group, and the mysql user. The syntax for useradd and groupadd may differ slightly on different Unixes. They may also be called adduser and addgroup. You may wish to call the user and group something else instead of mysql.
Unpack the distribution into the current directory:
shell> gunzip < /path/to/mysql-VERSION.tar.gz | tar xvf -

This command creates a directory named `mysql-VERSION'.
Change into the top-level directory of the unpacked distribution:
shell> cd mysql-VERSION

Note that currently you must configure and build MySQL from this top-level directory. You can not build it in a different directory.
Configure the release and compile everything:
shell> ./configure --prefix=/usr/local/mysql
shell> make

When you run configure, you might want to specify some options. Run ./configure --help for a list of options. If configure fails, and you are going to send mail to
mysql@lists.mysql.com to ask for assistance, please include any lines from `config.log' that you think can help solve the problem. Also include the last couple of lines of output from configure if configure aborts. Post the bug report using the mysqlbug script.
Install everything:
shell> make install

You might need to run this command as root.
Create the MySQL grant tables (necessary only if you haven't installed MySQL before):
shell> scripts/mysql_install_db

Note that MySQL versions older than Version 3.22.10 started the MySQL server when you run mysql_install_db. This is no longer true!
Change ownership of the installation to the user that you will run mysqld as:
shell> chown -R mysql /usr/local/mysql
shell> chgrp -R mysql /usr/local/mysql

The first command changes the owner attribute of the files to the mysql user, and the second changes the group attribute to the mysql group.

If you would like MySQL to start automatically when you boot your machine, you can copy support-files/mysql.server to the location where your system has its startup files. More information can be found in the support-files/mysql.server script itself.

After everything has been installed, you should initialize and test your distribution:
shell> /usr/local/mysql/bin/safe_mysqld --user=mysql &

If that command fails immediately with mysqld daemon ended then you can find some information in the file `mysql-data-directory/'hostname'.err'. The likely reason is that you already have another mysqld server running.