Explore topic-wise InterviewSolutions in .

If you have a date, and you WANT to increment it by 1 DAY, you can use the DATE_ADD(date, INTERVAL 1 DAY) function. You can also use the date interval ADD operation as “date + INTERVAL 1 DAY.

If you have a date, and you want to increment it by 1 day, you can use the DATE_ADD(date, INTERVAL 1 DAY) function. You can also use the date interval add operation as “date + INTERVAL 1 DAY.

You can CREATE a NEW view based on one or more existing TABLES by using the

“CREATE VIEW viewName AS selectStatement” .

You can create a new view based on one or more existing tables by using the

“CREATE VIEW viewName AS selectStatement” .

If you have an existing VIEW, and you dont want it anymore, you can delete it by USING the “DROP VIEW viewName” STATEMENT

If you have an existing view, and you dont want it anymore, you can delete it by using the “DROP VIEW viewName” statement

If you don’t need an existing index any more, you should delete it with the “DROP INDEX indexName ON tableName” statement. Here is an EXAMPLE SQL script :

If you don’t need an existing index any more, you should delete it with the “DROP INDEX indexName ON tableName” statement. Here is an example SQL script :

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.

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.

If you have a table with a lots of rows, and you know that one of the columns will be used often as a search CRITERIA, you can add an index for that COLUMN to improve the search performance. To add an index, you can use the “CREATE INDEX” statement as shown in the following script:

If you have a table with a lots of rows, and you know that one of the columns will be used often as a search criteria, you can add an index for that column to improve the search performance. To add an index, you can use the “CREATE INDEX” statement as shown in the following script:

ALTER TABLE TIP RENAME TO FAQ;

ALTER TABLE tip RENAME TO faq;

ALTER TABLE TIP CHANGE COLUMN subject TITLE VARCHAR(60);

ALTER TABLE tip CHANGE COLUMN subject title VARCHAR(60);

ALTER TABLE tip DROP COLUMN create_date;
Query OK, 1 row affected (0.48 SEC)
RECORDS: 1 Duplicates: 0 Warnings: 0

ALTER TABLE tip DROP COLUMN create_date;
Query OK, 1 row affected (0.48 sec)
Records: 1 Duplicates: 0 Warnings: 0

ALTER TABLE tip ADD COLUMN author VARCHAR(40);
Query OK, 1 ROW AFFECTED (0.18 sec)
Records: 1 Duplicates: 0 WARNINGS: 0

ALTER TABLE tip ADD COLUMN author VARCHAR(40);
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0

If you want show an article was posted “n hours n minutes and n seconds ago’, you can use the TIMEDIFF(NOWO, pastTime) function as shown in the FOLLOWING are:

If you want show an article was posted “n hours n minutes and n seconds ago’, you can use the TIMEDIFF(NOWO, pastTime) function as shown in the following are:

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.

A LIKE condition is also called pattern patch. There are 3 main rules on using LIKE condition:

An IN condition is single value again a LIST of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE. Some examples are :

An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE. Some examples are :

SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;
4123.457
SELECT CAST(1/3 AS CHAR);
0.3333

SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;
4123.457
SELECT CAST(1/3 AS CHAR);
0.3333

You can CONVERT numeric values to character STRINGS by using the CAST(value AS CHAR) function as SHOWN in the following examples:

You can convert numeric values to character strings by using the CAST(value AS CHAR) function as shown in the following examples:

If you want to ENTER Boolean values in SQL STATEMENTS, you USE (TRUE), (FALSE), (true), or (false). Here are some good examples:

If you want to enter Boolean values in SQL statements, you use (TRUE), (FALSE), (true), or (false). Here are some good examples:

If you want to enter CHARACTERS as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox). A HEX number STRING will be automatically converted into a CHARACTER string, if the expression context is a string. Here are some good EXAMPLES:

If you want to enter characters as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox). A HEX number string will be automatically converted into a character string, if the expression context is a string. Here are some good examples:

If you WANT CONCATENATE multiple character strings into one, you NEED to use the CONCAT() function. Here are some good examples:

If you want concatenate multiple character strings into one, you need to use the CONCAT() function. Here are some good examples:

There are a number of special characters that needs to be escaped (protected), if you want to include them in a character string. Here are some basic character escaping rules:

• The ESCAPE character () needs to be escaped as (\).
• The single quote (‘) needs to be escaped as (‘) or (“) in single-quote quoted strings.
• The double quote () needs to be escaped as (“) or (““) in double-quote quoted strings.
• The wild CARD character for a single character () needs to be escaped as (_).
• The wild card character for multiple characters (%) needs to be escaped as (%).
• The tab character needs to be escaped as (t).
• The new LINE character needs to be escaped as (n).
• The carriage RETURN character needs to be escaped as (R).

There are a number of special characters that needs to be escaped (protected), if you want to include them in a character string. Here are some basic character escaping rules:

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.

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

MySQL support 3 groups of data types as listed below:

String Data Types - CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, VARBINARY, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM, SET.

Numeric Data Types - BIT, TINYINT, BOOLEAN, SMALLINT, MEDIUMINT, INTEGER, BIGINT, FLOAT, DOUBLE, REAL, DECIMAL.

Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.

MySQL support 3 groups of data types as listed below:

String Data Types - CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, VARBINARY, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM, SET.

Numeric Data Types - BIT, TINYINT, BOOLEAN, SMALLINT, MEDIUMINT, INTEGER, BIGINT, FLOAT, DOUBLE, REAL, DECIMAL.

Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.

ROLLBACK is a way to terminate a TRANSACTION with all DATABASE changes not SAVING to the database server.

Rollback is a way to terminate a transaction with all database changes not saving to the database server.

COMMIT is a WAY to terminate a transaction with all DATABASE changes to be saved PERMANENTLY to the database SERVER.

Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.

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.

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.

BDB (BerkeleyDB) is TRANSACTION safe STORAGE engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, INC. (an Oracle company now).

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

lnnoDB is a transaction safe storage engine DEVELOPED by Innobase OY (an Oracle COMPANY now).

lnnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).

ISAM (Indexed Sequential ACCESS Method) was DEVELOPED by IBM to store and retrieve data on secondary storage SYSTEMS like TAPES.

ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.

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.

id name

103 Schmitt
112 KING
114 FERGUSON
119 Labrune
121 Bergulfsen

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.

id name

103 Schmitt
112 King
114 Ferguson
119 Labrune
121 Bergulfsen

Join is data retrieval OPERATION that combines rows from MULTIPLE tables under CERTAIN MATCHING conditions to form a SINGLE row.

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

MyISAM : This is default. BASED on Indexed Sequntial Access Method. The above SQL will create a MyISA table.

ISAM : same

HEAP : Fast data access, but will LOOSE data if there is a crash. Cannot have BLOB, TEXT & AUTO INCRIMENT fields

BDB : Supports Transactions USING COMMIT & ROLLBACK. Slower that others.

InoDB : same as BDB

MyISAM : This is default. Based on Indexed Sequntial Access Method. The above SQL will create a MyISA table.

ISAM : same

HEAP : Fast data access, but will loose data if there is a crash. Cannot have BLOB, TEXT & AUTO INCRIMENT fields

BDB : Supports Transactions using COMMIT & ROLLBACK. Slower that others.

InoDB : same as BDB

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.

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.

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.

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.

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.

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.

DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.

When combining LIMIT # with DISTINCT, MySQL will stop as soon as it FINDS # unique rows.

If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has FOUND the FIRST match.

In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular ROW in t1) when the first row in t2 is found.

DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.

When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.

If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the first match.

In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the first row in t2 is found.

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.

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.

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.

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.

MySQL is a very FAST, multi-threaded, multi-user, and robust SQL (Structured Query LANGUAGE) DATABASE SERVER.

MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server.

CHAR(3), SINCE MySQL AUTOMATICALLY ADJUSTED the DATA type.

CHAR(3), since MySQL automatically adjusted the data type.

A default value is USED on initialization, a current TIMESTAMP is INSERTED on update of the ROW.

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

On initialization places a ZERO in that COLUMN, on future UPDATES PUTS the current value of the TIMESTAMP in.

On initialization places a zero in that column, on future updates puts the current value of the timestamp in.

The column exhibits the same BEHAVIOR as a SINGLE TIMESTAMP column in a table with no other timestamp COLUMNS.

The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.

Create two COLUMNS of type TIMESTAMP and USE the second one for your REAL DATA.

Create two columns of type TIMESTAMP and use the second one for your real data.

That field GETS the current timestamp whenever the ROW gets ALTERED.

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

999.99 to -99.99. Note that with the NEGATIVE number the MINUS sign is CONSIDERED ONE of the DIGITS.

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

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.

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.

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.

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.

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.

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.

BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT

SERIAL is an ALIAS for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

Introduced in MySQL 5.0, FEDERATED TABLES ALLOW ACCESS to the tables located on other DATABASES on other servers.

Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.

Those are the special tables, DATA for which is saved into comma-separated VALUES FILES. They cannot be INDEXED.

Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.