MySQL%20Interview%20Questions%20and%20Answers
Freshers / Beginner level questions & answers
Ques 1. What Is MySQL?
MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com.
Ques 2. What Is mSQL?
Mini SQL (mSQL) is a light weight relational database management system capable of providing rapid access to your data with very little overhead. mSQL is developed by Hughes Technologies Pty Ltd.
MySQL was started from mSQL and shared the same API.
Ques 3. What Is SQL?
SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.
Ques 4. What Is Table?
A table is a data storage structure with rows and columns.
Ques 5. What Is Column?
A column defines one piece of data stored in all rows of the table.
Ques 6. What Is Row?
A row is a unit of data with related data items stored as one item in one column in a table.
Ques 7. What Is Primary Key?
A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.
Ques 8. What Is Foreign Key?
A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.
Ques 9. What Is Index?
An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.
Ques 10. What Is View?
A view is a logical table defined by a query statement.
Ques 11. What Is Join?
Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.
Ques 12. What Is Union?
Join is data retrieval operation that combines multiple query outputs of the same structure into a single output.
Ques 13. What Is ISAM?
ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.
Ques 14. What Is MyISAM?
MyISAM is a storage engine used as the default storage engine for MySQL database. MyISAM is based on the ISAM (Indexed Sequential Access Method) concept and offers fast data storage and retrieval. But it is not transaction safe.
Ques 15. What Is InnoDB?
InnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).
Ques 16. What Is BDB (BerkeleyDB)?
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).
Ques 17. What Is CSV?
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.
Ques 18. What Is Transaction?
A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).
Ques 19. What Is Commit?
Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.
Ques 20. What Is Rollback?
Rollback is a way to terminate a transaction with all database changes not saving to the database server.
Ques 21. Explain what Is MySQL?
MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com. MySQL has the following main features:
* Works on many different platforms.
* APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl are available.
* Fully multi-threaded using kernel threads. It can easily use multiple CPUs if they are available.
* Provides transactional and non-transactional storage engines.
* Uses very fast B-tree disk tables (MyISAM) with index compression.
* A very fast thread-based memory allocation system.
* Very fast joins using an optimized one-sweep multi-join.
* In-memory hash tables, which are used as temporary tables.
* SQL functions are implemented using a highly optimized class library and should be as fast as possible. Usually there is no memory allocation at all after query initialization.
* The server is available as a separate program for use in a client/server networked environment. It is also available as a library that can be embedded (linked) into standalone applications. Such applications can be used in isolation or in environments where no network is available.
Ques 22. How To Install MySQL?
MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com. You can download a copy and install it on your local computer very easily. Here is how you can do this:
* Go to http://dev.mysql.com/downloads/mysql/5.1.html.
* Select the "Windows" and "Without installer" version.
* Find a mirror site and download "mysql-noinstall-5.1.50-win32.zip".
* Unzip the file, you will get a new sub-directory, ".mysql-5.1.50-win32".
* Move and rename this sub-directory to mysql.
* The installation is done and your MySQL server is ready.
Ques 23. How To Start MySQL Server?
If you want to start the MySQL server, you can run the "mysqld" program in a command window as shown in the following tutorial:
>cd mysqlbin
>mysqld
"mysqld" will run quietly without printing any message in you command window. So you will see nothing after entering the "mysqld" command. You should trust "mysqld" and believe that MySQL server is running ok on your local computer now.
Another way to start the MySQL server is double-click mysqlbinmysqld.exe on your file explorer window.
Ques 24. How Do You Know If Your MySQL Server Is Alive?
If you want to know whether your MySQL server is alive, you can use the "mysqladmin" program in a command window as shown in the following tutorial:
>cd mysqlbin
>mysqladmin -u root ping
mysqld is alive
The "mysqld is alive" message tells you that your MySQL server is running ok. If your MySQL server is not running, you will get a "connect ... failed" message.
Ques 25. How Do You Know the Version of Your MySQL Server?
If you want to know the version number of your MySQL server, you can use the "mysqladmin" program in a command window as shown in the following tutorial:
>cd mysqlbin
>mysqladmin -u root version
mysqladmin Ver 8.41 Distrib 5.0.24, for Win32 on ia32
Copyright (C) 2000 MySQL AB & MySQL Finland AB
& TCX DataKonsult AB
...
Server version 5.0.24-community
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 25 min 9 sec
Threads: 1 Questions: 2 Slow queries: 0 Opens: 12
Flush tables: 1 Open tables: 6
Queries per second avg: 0.001
The output in the above example tells you that the version number is 5.0.24.
Ques 26. How To Create a Test Table in Your MySQL Server?
If you want to create a wbtest table in your MySQL server, you can use the "mysql" program in a command window as shown in the following tutorial:
>cd mysqlbin
>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8 to server version: 5.0.24-
mysql> use wbtest
Database changed
mysql> CREATE TABLE wbtest (message VARCHAR(80));
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO wbtest
-> VALUES ('Welcome to GlobalGuideLine.com');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM wbtest;
+---------------------------------+
| message |
+---------------------------------+
| Welcome to GlobalGuideLine.come |
+---------------------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE wbtest;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
Ques 27. How To Shutdown MySQL Server?
If you want to shutdown your MySQL server, you can run the "mysqladmin" program in a command window as shown in the following tutorial:
>cd mysqlbin
>mysqladmin shutdown
Ques 28. What Tools Available for Managing MySQL Server?
MySQL comes with the following programs as administration tools for you to manage your MySQL server:
► mysqld - MySQL server daemon. You can use "mysqld" to start your MySQL server.
► mysqladmin - A command-line interface for administrators to perform server administration tasks.
► mysql - A command-line interface for end users to manage user data objects.
► mysqlcheck - A command-line interface for administrators to check and repair tables.
► mysqlshow - A command-line interface for end users to see information on tables and columns.
► mysqldump - A command-line interface for administrators or end users to export data from the server to files.
► mysqlimport - A command-line interface for administrators or end users to load data files into tables program tool to load data into tables.
Ques 29. What Is "mysqld"?
"mysqld" is MySQL server daemon program which runs quietly in background on your computer system. Invoking "mysqld" will start the MySQL server on your system. Terminating "mysqld" will shutdown the MySQL server. Here is a tutorial example of invoking "mysqld" with the "--console" option:
>cd mysqlbin
>mysqld --console
... 21:52:54 InnoDB: Started; log sequence number 0 43655
... 21:52:54 [Note] mysqld: ready for connections.
Version: '5.0.24-community' socket: '' port: 3306
MySQL Community Edition (GPL)
The returning message indicates that "mysqld" running now, and your MySQL server is ready to take client connections. To know about "mysqld", read other parts of this FAQ collection.
Ques 30. What Is "mysqladmin" in MySQL?
"mysqladmin" is a command-line interface for administrators to perform server administration tasks. It support a number of commonly used commands like:
► "mysqladmin shutdown" - Shuts down the server.
► "mysqladmin ping" - Checks if the server is alive or not.
► "mysqladmin status" - Displays several important server status values.
► "mysqladmin version" - Displays version information of the server.
► "mysqladmin create databaseName" - Creates a new database.
► "mysqladmin drop databaseName" - Drops an existing database.
Ques 31. How To Check Server Status with "mysqladmin"?
If you want to check the server status by with "mysqladmin", you can following this tutorial example:
>cd mysqlbin
>mysqladmin -u root status
Uptime: 223 Threads: 1 Questions: 1 Slow queries: 0
Opens: 12 Flush tables: 1 Open tables: 6
Queries per second avg: 0.004
The returning message indicates that the server is almost doing nothing at this moment.
Ques 32. How To Shut Down the Server with "mysqladmin"?
If you want to shut down the server with "mysqladmin", you can use the command "mysqladmin shutdown" as shown in the following tutorial example:
>cd mysqlbin
>mysqladmin -u root shutdown
If this command returns no messages, your MySQL server should be terminated successfully.
Ques 33. How To Use "mysql" to Run SQL Statements?
If you want to run SQL statement to your server with "mysql", you need to start "mysql" and enter your SQL statement at the "mysql" prompt. Here is a good tutorial exercise that shows you how to run two SQL statements with "mysql":
>cd mysqlbin
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 5.0.24
mysql> CREATE TABLE links (name VARCHAR(80));
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO links VALUES ('www.GlobalGuideLine.com');
Query OK, 1 row affected (0.02 sec)
mysql> quit;
Bye
Ques 34. How To Show All Tables with "mysql"?
If you want to see all the tables in a database, you run the non-SQL command "SHOW TABLES" at the "mysql" prompt. See the following tutorial exercise for example:
>cd mysqlbin
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14 to server version: 5.0.24
mysql> SHOW TABLES;
+----------------+
| WB_Test |
+----------------+
| EMP_List |
+----------------+
1 row in set (0.01 sec)
The output shows you that there is only one table in the "test" database.
Ques 35. What Is "mysqlcheck"?
"mysqlcheck" is a command-line interface for administrators to check and repair tables. Here are some sample commands supported by "mysqlcheck":
► "mysqlcheck databaseName tableName" - Checks the specified table in the specified database.
► "mysqlcheck databaseName" - Checks all tables in the specified database.
► "mysqlcheck --all-databases" - Checks all tables in all databases.
► "mysqlcheck --analyze databaseName tableName" - Analyzes the specified table in the specified database.
► "mysqlcheck --repair databaseName tableName" - Repairs the specified table in the specified database.
Ques 36. How To Analyze Tables with "mysqlcheck"?
If you want analyze tables with "mysqlcheck", you need to use the "--analyze" option. The following tutorial exercise shows you how to analyze all tables in "mysql" database:
>cd mysqlbin
>mysqlcheck -u root --analyze mysql
mysql.columns_priv Table is already up to date
mysql.db Table is already up to date
mysql.func Table is already up to date
mysql.help_category Table is already up to date
mysql.help_keyword Table is already up to date
mysql.help_relation Table is already up to date
mysql.help_topic Table is already up to date
mysql.host Table is already up to date
mysql.proc Table is already up to date
mysql.tables_priv Table is already up to date
mysql.time_zone Table is already up to date
mysql.time_zone_leap_second Table is already up to date
mysql.time_zone_name Table is already up to date
mysql.time_zone_transition Table is already up to date
mysql.time_zone_transition_type Table is already up to date
mysql.user Table is already up to date
Ques 37. What Is "mysqlshow"?
"mysqlshow" is a command-line interface for end users to see information on tables and columns. Here are some sample commands supported by "mysqlshow":
► "mysqlshow" - Shows all the databases.
► "mysqlshow databaseName" - Shows all the tables in the specified database.
► "mysqlshow databaseName tableName" - Shows all the columns in the specified table.
► "mysqlshow --verbose" - Shows all the databases with extra information.
► "mysqlshow --verbose my%" - Shows all the databases who's names match the pattern "my%" with extra information.
► "mysqlshow --verbose mysql time%" - Shows all the tables who's names match the pattern "time%" in "mysql" database with extra information.
Ques 38. What Is "mysqldump"?
"mysqldump" - A command-line interface for administrators or end users to export data from the server to files. Here are some sample commands supported by "mysqldump":
► "mysqldump databaseName tableName" - Dumps the specified table in the specified database.
► "mysqldump databaseName" - Dumps all the tables in the specified database.
Ques 39. How To Dump a Table to a File with "mysqldump"?
If you want to dump all rows in a table from the server to a file, you can use "mysqldump" with the "-f fileName" option as show in the following tutorial exercise:
>cd mysqlbin
>mysqldump -u root -r templinks.txt test links
>type templinks.txt
>type templinks.txt | more
-- MySQL dump 10.10
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.0.24-community
...
The dump file contains SQL statements that you can use to restore the table and its data content.
Ques 40. What Is "mysqlimport"?
"mysqlimport" - A command-line interface for administrators or end users to load data files into tables program tool to load data into tables. Here is a sample commands supported by "mysqlimport":
► "mysqlimport databaseName fileName" - Imports the data from the specified file to the specified database. The data will be loaded into the table who's name matches the specified file name.
Ques 41. How To Load Data Files into Tables with "mysqlimport"?
If you want to load a data file directly into a table, you need to prepare the data file as one line per data row, and use tab character as the column delimiter. The data file name should match the target table name. The following is a good tutorial exercise on using "mysqlimport":
>cd mysqlbin
>type templinks.tab
www.test.com
www.mysql.com
>mysqlimport -u root test templinks.tab
test.links: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
>mysql -u root -e "SELECT * FROM links" test
+-------------------------+
| name |
+-------------------------+
| www.test.com |
| www.mysql.com |
+-------------------------+
Ques 42. What Is the Command Line End User Interface - mysql?
"mysql", official name is "MySQL monitor", is a command-line interface for end users to manage user data objects. "mysql" has the following main features:
* "mysql" is command line interface. It is not a Graphical User Interface (GUI).
* "mysql" supports all standard SQL Data Definition Language (DDL) commands for the server to execute.
► "mysql" supports all standard SQL Data Manipulation Language (DML) commands for the server to execute.
► "mysql" supports many of non-SQL commands that "mysql" will execute by itself.
► "mysql" provides access to the server-side help system.
► "mysql" allows command files to be executed in a batch mode.
► "mysql" allows query output to be formatted as HTML tables.
► "mysql" allows query output to be formatted as XML elements.
Ques 43. What Are the "mysql" Command Line Options?
"mysql" offers a big list of command line options. Here are some commonly used options:
► "-?" - Displays a help message on how to use "mysql" and terminates the program.
► "-u userName" - Specifies a user name for the server to authenticate when connecting to the server.
► "-p[password]" - Specifies a password for the server to authenticate when connecting to the server.
► "-h hostName" - Specifies a host name where the MySQL server is running. The default host name "localhost".
► "-P portNumber" - Specifies a port number where the MySQL server is listening for connections. The default port number is 3306.
► "-e command" - Executes the specified command and terminates the program.
► "-t" - Specifies that the query output is displayed in text table format. This is the default display format for interactive mode.
► "-H" - Specifies that the query output is displayed in HTML table format.
► "-X" - Specifies that the query output is displayed in XML element format.
Ques 44. What Are the "mysql" Command Line Arguments?
"mysql" supports only one optional command line argument, "database". But "mysql" allows the operating system to redirect input and output streams at the command line level. Here are some good examples:
► "mysql databaseName" - Starts "mysql" in interactive mode and use the specified database.
► "mysql < fileName" - Starts "mysql" in batch mode and executes all commands in the specified file.
► "mysql < fileName > fileName" - Starts "mysql" in batch mode, executes all commands in the specified file, and write output to the specified file.
Here is a tutorial exercise of how to use the command line argument to specify the database to use:
>cd mysqlbin
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 5.0.24
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| links |
+----------------+
1 row in set (0.00 sec)
mysql> quit;
Bye
Ques 45. How Many SQL DDL Commands Are Supported by "mysql"?
There are 4 SQL Data Definition Language (DDL) commands that are supported by "mysql". They are listed below with short descriptions:
► "CREATE dataObjectType dataObjectName" - Creates new databases, tables, views, triggers, indexes, and other data objects.
► "RENAME dataObjectType dataObjectName" - Renames existing databases, tables, views, triggers, indexes, and other data objects.
► "ALTER dataObjectType dataObjectName" - Alters properties of existing databases, tables, views, triggers, indexes, and other data objects.
► "DROP dataObjectType dataObjectName" - Drops existing databases, tables, views, triggers, indexes, and other data objects.
Here is a tutorial exercise of how to use DDL commands to create a database and a table:
>cd mysqlbin
>mysql -u root
mysql> CREATE DATABASE ggl;
Query OK, 1 row affected (0.50 sec)
mysql> CREATE TABLE articles (name VARCHAR(80));
Query OK, 0 rows affected (0.25 sec)
mysql> DROP DATABASE ggl;
Query OK, 0 rows affected (0.41 sec)
Ques 46. How Many SQL DML Commands Are Supported by "mysql"?
There are 4 SQL Data Manipulation Language (DML) commands that are supported by "mysql". They are listed below with short descriptions:
* "INSERT INTO tableName ..." - Inserts new data rows into the specified table.
* "DELETE FROM tableName ..." - Deletes existing data rows from the specified table.
* "UPDATE tableName SET ..." - Updates existing data rows in the specified table.
* "SELECT expressionList FROM ..." - Selects rows from tables and returns rows of values resulted from the specified expression list.
Here is a tutorial exercise of how to use DML commands to insert and select data rows:
>cd mysqlbin
>mysql -u root test
mysql> CREATE TABLE links (id INTEGER, name VARCHAR(80));
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO links VALUES (1, 'www.withoutbook.com');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM links;
+------+-------------------------+
| id | name |
+------+-------------------------+
| 1 | www.GlobalGuideLine.com |
+------+-------------------------+
1 row in set (0.04 sec)
Ques 47. What Are the Non-Standard SQL Commands Supported by "mysql"?
There are many non-standard SQL commands that are supported by "mysql". Here is short list of some commonly used commands:
► "SHOW infoName" - Shows basic information of based on the specified information name.
► "SHOW infoName" - Shows basic information of based on the specified information name.
► "SET ..." - Sets new values to server or connection session variables.
► "GRANT ..." - Grants access privileges to users.
► "REVOKE ..." - Revokes access privileges from users.
► "CHECK TABLE tableName" - Checks the specified table for errors.
► "ANALYZE TABLE tableName" - Analyzes the specified table.
► "REPAIR TABLE tableName" - Repairs the specified table.
► "BACKUP TABLE tableName" - Backs up the specified table.
► "RESTORE TABLE tableName" - Restores the specified table.
► "USE databaseName" - Uses the specified database as the current database.
► "HELP topicName" - Returns help information on the specified topic.
Ques 48. How To Get Help Information from the Server?
While you are at the "mysql>" prompt, you can get help information from the server by using the "HELP" command. The tutorial exercise below shows several examples:
>cd mysqlbin
>mysql -u root
mysql> HELP;
...
List of all MySQL commands:
Note that all text commands must be end with ';'
? (?) Synonym for `help'.
clear (c) Clear command.
connect (r) Reconnect to the server.
...
mysql> HELP SHOW;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about
databases, tables, columns, or status information about
the server. This section describes those following:
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
...
mysql> HELP CREATE TABLE;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_option ...]
...
Ques 49. How To Run "mysql" Commands from a Batch File?
If you have group of "mysql" commands that need to be executed repeatedly, you can put them into a file, and run them from the file in "mysql" batch mode. Here is a batch file, templinks.sql, contains following commands:
USE wbtest;
INSERT INTO links VALUES (10, 'www.withoutbook.com');
SELECT * FROM links;
To run this batch file, you need to follow this tutorial:
>cd mysqlbin
>mysql -u root < templinks.sql
id name
1 www.withoutbook.com
10 www.withoutbook.com
Ques 50. How To Return Query Output in HTML Format?
By default, "mysql" returns query output in text table format. If you want to receive query output in HTML format, you need to use the "-H" command option. Here is a good tutorial exercise:
>cd mysqlbin
>mysql -u root -H test
mysql> SELECT * FROM links;
<TABLE BORDER=1><TR><TH>id</TH><TH>name</TH></TR>
<TR><TD>1</TD><TD>www.withoutbook.com</TD></TR>
<TR><TD>10</TD><TD>www.withoutbook.com</TD></TR></TABLE>
2 rows in set (0.00 sec)
Ques 51. How To Return Query Output in XML Format?
By default, "mysql" returns query output in text table format. If you want to receive query output in XML format, you need to use the "-X" command option. Here is a good tutorial exercise:
>cd mysqlbin
>mysql -u root -X test
mysql> SELECT * FROM links;
<?xml version="1.0"?>
<resultset statement="SELECT * FROM links">
<row>
<field name="id">1</field>
<field name="name">www.GlobalGuideLine.com</field>
</row>
<row>
<field name="id">10</field>
<field name="name">www.GlobalGuideLine.com</field>
</row>
</resultset>
Ques 52. What Is SQL in MySQL?
SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.
Ques 53. How Many Groups of Data Types?
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.
Ques 54. What Are String Data Types?
MySQL supports the following string data types:
► CHAR(n) same as CHARACTER(n) - Fixed width and " " padded characters strings. Default character set is ASCII.
► NCHAR(n) same as NATIONAL CHARACTER(n) - Fixed width and " " padded character strings with UTF8 character set.
► VARCHAR(n) same as CHARACTER VARYING(n) - Variable width character strings. Default character set is ASCII.
► NVARCHAR(n) same as NATIONAL CHARACTER VARYING(n) - Variable width character strings with UTF8 character set.
► BINARY(n) - Fixed width and 0x00 padded byte strings.
► VARBINARY(n) same as BINARY VARYING(n) - Variable width byte string.
► TINYBLOB - BLOB (Binary Large Object) upto 255 bytes.
► BLOB - BLOB (Binary Large Object) upto 64K bytes.
► MEDIUMBLOB - BLOB (Binary Large Object) upto 16M bytes.
► LONGBLOB - BLOB (Binary Large Object) upto 4G bytes.
► TINYTEXT - CLOB (Binary Large Object) upto 255 characters.
► TEXT - CLOB (Binary Large Object) upto 64K characters.
► MEDIUMTEXT - CLOB (Binary Large Object) upto 16M characters.
► LONGTEXT - CLOB (Binary Large Object) upto 4G characters.
► ENUM - An enumeration to hold one entry of some pre-defined strings.
► SET - A set to hold zero or more entries of some pre-defined strings.
Ques 55. What Are the Differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length string data types. But they have the following differences:
► CHAR's full name is CHARACTER.
► NCHAR's 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.
The following column definitions are the same:
CREATE TABLE faq (Title NCHAR(80));
CREATE TABLE faq (Title NATIONAL CHAR(80));
CREATE TABLE faq (Title NATIONAL CHARACTER(80));
CREATE TABLE faq (Title CHAR(80) CHARACTER SET utf8);
CREATE TABLE faq (Title CHARACTER(80) CHARACTER SET utf8);
Ques 56. What Are the Differences between CHAR and VARCHAR?
CHAR and VARCHAR are both ASCII character data types by default. But they have the following major differences:
► CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
► VARCHAR stores values in variable lengths. Values are not padded with any characters. But 1 or 2 extra bytes are added to store the length of the data.
The table below shows you a good comparison of CHAR and VARCHAR data types:
Value
''
'ab'
'abcd'
Value
''
'ab'
'abcd'
Ques 57. What Are the Differences between BINARY and VARBINARY?
Both BINARY and VARBINARY are both binary byte data types. But they have the following major differences:
► BINARY stores values in fixed lengths. Values are padded with 0x00.
► VARBINARY stores values in variable lengths. Values are not padded with any bytes. But 1 or 2 extra bytes are added to store the length of the data.
Ques 58. What Are Numeric Data Types?
MySQL supports the following numeric data types:
► BIT(n) - An integer with n bits.
► BOOL same as BOOLEAN - Boolean values stored in 1 bit.
► TINYINT - A small integer stored in 1 byte.
► SMALLINT - A small integer stored in 2 bytes.
► MEDIUMINT - A medium integer stored in 3 bytes.
► INT same as INTEGER - An integer stored in 4 bytes.
► BIGINT - An integer stored in 8 bytes.
► FLOAT - A single-precision floating-point number stored in 4 bytes.
► DOUBLE same as DOUBLE PRECISION - A double-precision floating-point number stored in 8 bytes.
► REAL - Same DOUBLE by default.
► DECIMAL(m,d) - A fixed-point number with m as the precision (total number of digits) and d as the scale (number of digits after the decimal point).
► Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.
Ques 59. What Are Date and Time Data Types?
MySQL supports the following date and time data types:
► DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD".
► DATETIME - A date with the time of day in the range of '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIMESTAMP - A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► YEAR - A year in 4 digits in the range of 1901 and 2155. Default YEAR format is "YYYY".
Ques 60. How To Calculate Expressions with SQL Statements?
There is no special SQL statements to calculate expressions. But you can use the "SELECT expression FROM DUAL" statement return the calculated value of an expression. "DUAL" is a dummy table in the server. The tutorial exercise below shows you some good examples:
SELECT 'Hello world!' FROM DUAL;
Hello world!
SELECT (1+2)*3/4 FROM DUAL;
2.2500
SELECT TRUE FROM DUAL;
1
SELECT TRUE AND FALSE FROM DUAL;
0
SELECT TIME(SYSDATE()) FROM DUAL;
21:30:26
Ques 61. How To Include Comments in SQL Statements?
If you want to include comments in a SQL statement, you can first enter "--", then enter your comment until the end of the line. The tutorial exercise below shows you some good examples:
SELECT 'Hello world!' FROM DUAL; -- My first SQL statement!
INSERT INTO links VALUES ('GlobalGuideLine.com'); -- Top rated!
CREATE TABLE faq (
id INTEGER, -- primary key
title VARCHAR(80) -- FAQ title
);
Ques 62. How To Include Character Strings in SQL statements?
If you want to include character strings in your SQL statements, you need to quote them in one of the following formats:
► Using single quotes. For example 'GlobalGuideLine.com'.
► Using double quotes. For example "ggl Center".
► Using single quotes prefixed with N for NATIONAL characters (same as UTF8 characters). For example N'Allo, Francois.'.
► Using single quotes prefixed with _utf8 for UTF8 characters. For example _utf8'Allo, Francois.'.
Ques 63. How To Escape Special Characters in SQL statements?
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).
Here are some examples of how to include special characters:
SELECT 'It''s Sunday!' FROM DUAL;
It's Sunday!
SELECT 'Allo, C'est moi.' FROM DUAL;
Allo, C'est moi.
SELECT 'MontTuetWedtThutFri' FROM DUAL;
Mon Tue Wed Thu Fri
Ques 64. How To Concatenate Two Character Strings?
If you want concatenate multiple character strings into one, you need to use the CONCAT() function. Here are some good examples:
SELECT CONCAT('Welcome',' to') FROM DUAL;
Welcome to
SELECT CONCAT('wb','center','.com') FROM DUAL;
WithoutBook.com
Ques 65. How To Include Numeric Values in SQL statements?
If you want to include a numeric value in your SQL statement, you can enter it directly as shown in the following examples:
SELECT 255 FROM DUAL; -- An integer
255
SELECT -6.34 FROM DUAL; -- A regular number
-6.34
SELECT -32032.6809e+10 FROM DUAL; -- A floating-point value
-3.20326809e+014
Ques 66. How To Enter Characters as HEX Numbers?
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 (0x). A HEX number string will be automatically converted into a character string, if the expression context is a string. Here are some good examples:
SELECT X'313233' FROM DUAL;
123
SELECT 0x414243 FROM DUAL;
ABC
SELECT 0x46594963656E7465722E636F6D FROM DUAL;
WithoutBook.com
Ques 67. How To Enter Numeric Values as HEX Numbers?
If you want to enter numeric values as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (0x). A HEX number string will be automatically converted into a numeric value, if the expression context is a numeric value. Here are some good examples:
SELECT X'10' + 16 FROM DUAL;
32
SELECT 0x1000 + 0 FROM DUAL;
4096
Ques 68. How To Enter Binary Numbers in SQL Statements?
If you want to enter character strings or numeric values as binary numbers, you can quote binary numbers with single quotes and a prefix of (B), or just prefix binary numbers with (0b). Binary numbers will be automatically converted into character strings or numeric values based on the expression contexts. Here are some good examples:
SELECT B'010000010100001001000011' FROM DUAL;
ABC
SELECT 0b1000 + 0 FROM DUAL;
8
Ques 69. What Are NULL Values?
NULL is a special value that represents no value. Here are basic rules about NULL values:
► NULL presents no value.
► NULL is not the same as an empty string ''.
► NULL is not the same as a zero value 0.
► NULL can be used as any data type.
► NULL should not be used in any comparison options.
► NULL has its own equality operator "IS".
► NULL has its own not-equality operator "IS NOT".
Ques 70. What Happens If NULL Values Are Involved in Expressions?
If NULL values are used in expressions, the resulting values will be NULL values. In other words:
► Arithmetic expressions with NULL values result NULL values.
► Comparison expressions with NULL values result NULL values.
► Logical expressions with NULL values result NULL values.
The tutorial exercise shows you some interesting examples:
SELECT NULL + NULL FROM DUAL;
NULL
SELECT NULL + 7 FROM DUAL;
NULL
SELECT NULL * 7 FROM DUAL;
NULL
SELECT NULL = NULL FROM DUAL;
NULL
SELECT 0 < NULL FROM DUAL;
NULL
SELECT '' > NULL FROM DUAL;
NULL
SELECT NULL AND TRUE FROM DUAL;
NULL
SELECT NULL OR TRUE FROM DUAL;
1
-- This is contradicting against the rules!
Ques 71. How To Convert Numeric Values to Character Strings?
You can convert numeric values to character strings by using the CAST(value AS CHAR) function as shown in the following examples:
SELECT CAST(4123.45700 AS CHAR) FROM DUAL;
4123.45700
-- How to get rid of the last 2 '0's?
SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;
4123.457
SELECT CAST(1/3 AS CHAR);
0.3333
-- Very poor conversion
Ques 72. How To Convert Character Strings to Numeric Values?
You can convert character strings to numeric values by using the CAST(string AS DECIMAL) or CAST(string AS SIGNED INTEGER) function as shown in the following examples:
SELECT CAST('4123.45700' AS DECIMAL) FROM DUAL;
4123.46
-- Very poor conversion
SELECT CAST('4.12345700e+3' AS DECIMAL) FROM DUAL;
4123.46
-- Very poor conversion
SELECT CAST('4123.45700' AS SIGNED INTEGER) FROM DUAL;
4123
SELECT CAST('4.12345700e+3' AS SIGNED INTEGER) FROM DUAL;
4
-- Very poor conversion
Ques 73. How To Use IN Conditions?
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 given in the tutorial exercise below:
SELECT 3 IN (1,2,3,4,5) FROM DUAL;
1
SELECT 3 NOT IN (1,2,3,4,5) FROM DUAL;
0
SELECT 'Y' IN ('F','Y','I') FROM DUAL;
1
Ques 74. How To Use LIKE Conditions?
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.
The following tutorial exercise provides you some good pattern matching examples:
SELECT 'WithoutBook.com' LIKE '%center%' FROM DUAL;
1
SELECT 'WithoutBook.com' LIKE '%CENTER%' FROM DUAL;
1
-- Case insensitive by default
SELECT 'WithoutBook.com' LIKE '%CENTER_com' FROM DUAL;
1
Ques 75. How To Use Regular Expression in Pattern Match Conditions?
If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern condition: REGEXP. The following tutorial exercise provides you some good examples:
SELECT 'WithoutBook.com' REGEXP '.*ggl.*' FROM DUAL;
1
SELECT 'WithoutBook.com' REGEXP '.*com$' FROM DUAL;
1
SELECT 'WithoutBook.com' REGEXP '^F.*' FROM DUAL;
1
Ques 76. How To Use CASE Expression?
There are 2 ways to use the CASE expression. The first way is to return one of the predefined values based on the comparison of a given value to a list of target values. The second way is to return one of the predefined values based on a list of conditions. Here is the syntax of both types of CASE expressions:
CASE value WHEN target_value THEN result
WHEN target_value THEN result
WHEN target_value THEN result
...
ELSE result
END
CASE WHEN condition THEN result
WHEN condition THEN result
WHEN condition THEN result
...
ELSE result
END
The tutorial exercise below gives two good examples:
SELECT CASE 'Sun' WHEN 'Mon' THEN 'Open'
WHEN "Fri" THEN "Open" ELSE 'Closed' END FROM DUAL;
Closed
SELECT CASE WHEN HOUR(CURRENT_TIME())<9 THEN
'Closed'
WHEN HOUR(CURRENT_TIME())>17 THEN 'Closed'
ELSE 'Open' END FROM DUAL;
Closed
Ques 77. What Are Date and Time Data Types in MySQL?
MySQL supports the following date and time data types:
► DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD".
► DATETIME - A date with the time of day in the range of '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIMESTAMP - A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► YEAR - A year in 4 digits in the range of 1901 and 2155. Default YEAR format is "YYYY".
Ques 78. How To Write Date and Time Literals?
MySQL offers a number of formats for you to use to enter date and time literals:
► ANSI standard format: "YYYY-MM-DD HH:MM:SS".
► Non-standard limiters. Like: "YYYY/MM/DD HH^MM^SS" or "YYYY.MM.DD HH-MM-SS".
► No limiters. Like: "YYYYMMDD" for a date or "HHMMSS" for a time.
► Decimal numbers. Like: 8 digits dddddddd for a date or 6 digits dddddd for a time.
The tutorial exercise below gives you some good examples:
SELECT DATE('1997-01-31') FROM DUAL;
1997-01-31
SELECT DATE('1997-01-31 09:26:50') FROM DUAL;
1997-01-31
SELECT TIME('1997-01-31 09:26:50') FROM DUAL;
09:26:50
SELECT DATE('1997/01/31 09^26^50') FROM DUAL;
1997-01-31
SELECT TIME('1997/01/31 09^26^50') FROM DUAL;
09:26:50
SELECT DATE('19970131') FROM DUAL;
1997-01-31
SELECT TIME('092650') FROM DUAL;
09:26:50
SELECT DATE(19970131) FROM DUAL; -- Crazy format
1997-01-31
SELECT TIME(092650) FROM DUAL; -- Crazy format
09:26:50
Ques 79. How To Enter Microseconds in SQL Statements?
If you want to enter microseconds in a SQL statements, you can enter them right after the time string as a 6-digit number delimited with '.'. '0' will be padded to right if not enough digits. Here are some good examples:
SELECT TIME('1997/01/31 09^26^50.123') FROM DUAL;
09:26:50.123000
SELECT TIME('1997/01/31 09^26^50.000123') FROM DUAL;
09:26:50.000123
Ques 80. How To Convert Dates to Character Strings?
You can convert dates to character strings using the DATE_FORMAT(date, format) function. MySQL supports the following basic formatting codes:
► %a Abbreviated weekday name (Sun..Sat)
► %b Abbreviated month name (Jan..Dec)
► %c Month, numeric (0..12)
► %D Day of the month with English suffix (0th, 1st, 2nd, 3rd,...)
► %d Day of the month, numeric (00..31)
► %e Day of the month, numeric (0..31)
► %f Microseconds (000000..999999)
► %H Hour (00..23)
► %h Hour (01..12)
► %I Hour (01..12)
► %i Minutes, numeric (00..59)
► %j Day of year (001..366)
► %k Hour (0..23)
► %l Hour (1..12)
► %M Month name (January..December)
► %m Month, numeric (00..12)
► %p AM or PM
► %r Time, 12-hour (hh:mm:ss followed by AM or PM)
► %S Seconds (00..59)
► %s Seconds (00..59)
► %T Time, 24-hour (hh:mm:ss)
► %W Weekday name (Sunday..Saturday)
► %w Day of the week (0=Sunday..6=Saturday)
► %Y Year, numeric, four digits
► %y Year, numeric (two digits)
Ques 81. How To Convert Character Strings to Dates?
If you have a character string that represents a date, and you want to convert it into a date value, you can use the STR_TO_DATE(string, format) function. STR_TO_DATE() shares the same formatting codes with DATE_FORMAT() function. The tutorial exercise below shows you some good examples:
SELECT STR_TO_DATE('Friday, January 31, 1997',
'%W, %M %e, %Y') FROM DUAL;
1997-01-31
SELECT STR_TO_DATE('Friday, January 31, 1997, 09:26:50 AM',
'%W, %M %e, %Y, %h:%i:%s %p') FROM DUAL;
1997-01-31 09:26:50
SELECT STR_TO_DATE('31-Jan-1997 09:26:50.000123',
'%d-%b-%Y %H:%i:%s.%f') FROM DUAL;
1997-01-31 09:26:50.000123
Ques 82. What Are Date and Time Intervals?
A date and time interval is special value to be used to increment or decrement a date or a time at a given date or time unit. A data and time interval should be expression in the format of "INTERVAL expression unit", where "unit" and "expression" should follow these rules:
unit - expression value format
MICROSECOND - MICROSECONDS
SECOND - SECONDS
MINUTE - MINUTES
HOUR - HOURS
DAY - DAYS
WEEK - WEEKS
MONTH - MONTHS
QUARTER - QUARTERS
YEAR -YEARS
SECOND_MICROSECOND - 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND - 'MINUTES.MICROSECONDS'
MINUTE_SECOND - 'MINUTES:SECONDS'
HOUR_MICROSECOND - 'HOURS.MICROSECONDS'
HOUR_SECOND - 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE - 'HOURS:MINUTES'
DAY_MICROSECOND - 'DAYS.MICROSECONDS'
DAY_SECOND - 'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE - 'DAYS HOURS:MINUTES'
DAY_HOUR - 'DAYS HOURS'
YEAR_MONTH - 'YEARS-MONTHS'
Ques 83. How To Increment Dates by 1 in MySQL?
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". The tutorial exercise below gives you some good examples:
SELECT DATE_ADD(DATE('1997-01-31'), INTERVAL 1 DAY)
FROM DUAL;
1997-02-01
SELECT DATE('1997-01-31') + INTERVAL 1 DAY FROM DUAL;
1997-02-01
Ques 84. How To Decrement Dates by 1 in MySQL?
If you have a date, and you want to decrement it by 1 day, you can use the DATE_SUB(date, INTERVAL 1 DAY) function. You can also use the date interval subtraction operation as "date - INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:
SELECT DATE_SUB(DATE('1997-03-01'), INTERVAL 1 DAY)
FROM DUAL;
1997-02-28
SELECT DATE('1997-01-31') - INTERVAL 1 DAY FROM DUAL;
1997-02-28
Ques 85. How To Calculate the Difference between Two Dates?
If you have two dates, and you want to know how many days between them, you can use the DATEDIFF(date1, date2) function as shown below:
SELECT DATEDIFF(DATE('1997-02-28'), DATE('1997-03-01'))
FROM DUAL;
-1
Ques 86. How To Calculate the Difference between Two Time Values?
If you have two time values, and you want to know the time difference between them, you can use the TIMEDIFF(time1, time2) function as shown below:
SELECT TIMEDIFF(TIME('19:26:50'), TIME('09:26:50'))
FROM DUAL;
10:00:00
SELECT TIMEDIFF('1997-03-01 19:26:50.000123',
'1997-02-28 09:26:50.000000') FROM DUAL;
34:00:00.000123
Ques 87. How To Present a Past Time in Hours, Minutes and Seconds?
If you want show an article was posted "n hours n minutes and n seconds ago", you can use the TIMEDIFF(NOW(), pastTime) function as shown in the following tutorial exercise:
SELECT TIMEDIFF(NOW(), '2006-07-01 04:09:49') FROM DUAL;
06:42:58
SELECT TIME_FORMAT(TIMEDIFF(NOW(), '2006-06-30 04:09:49'),
'%H hours, %i minutes and %s seconds ago.') FROM DUAL;
30 hours, 45 minutes and 22 seconds ago.
Ques 88. How To Extract a Unit Value from a Date and Time?
If you want to extract a specific date or time unit value out of a date or a time, you can use the EXTRACT(unit FROM expression) function. The tutorial exercise below gives you some good examples:
ELECT EXTRACT(DAY FROM NOW()) FROM DUAL;
28
ELECT EXTRACT(HOUR FROM NOW()) FROM DUAL;
23
ELECT EXTRACT(SECOND FROM NOW()) FROM DUAL;
36
Ques 89. What Are Date and Time Functions in MySQL?
MySQL offers a number of functions for date and time values:
► ADDDATE(date, INTERVAL expr unit) - Adding days to a date. Same as DATE_ADD().
► ADDTIME(time1, time2) - Adding two time values together.
► CURDATE() - Returning the current date. Same as CURRENT_DATE().
► CURTIME() - Returning the current time. Same as CURRENT_TIME().
► DATE(expression) - Returning the date from the expression.
► DATEDIFF(date1, date2) - Returning dates difference in days.
► DATE_ADD(date, INTERVAL expr unit) - Adding days to a date.
► DATE_SUB(date, INTERVAL expr unit) - Subtracting days from a date.
► DATE_FORMAT(date, format) - Returning a character string representing a date.
► DAY(date) - Returning an integer representing the day of the month. Same as DAYOFMONTH()
► DAYNAME(date) - Returning the name of week day.
► DAYOFMONTH(date) - Returning an integer representing the day of the month.
► DAYOFWEEK(date) - Returning an integer representing the day of the week.
► DAYOFYEAR(date) - Returning an integer representing the day of the year.
Ques 90. What Is TIMESTAMP in MySQL?
A TIMESTAMP data type allows you to record a date and time like DATETIME data type. But it has some interesting features when used on a table column:
► The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an INSERT statement.
► The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an UPDATE statement.
► If a TIMESTAMP column is assigned with NULL, the current date and time will be persisted to this column.
► You can turn off the default current date and time on INSERT by defining the column with "DEFAULT 0".
► The tutorial exercise below provides you 2 equivalent ways to create a TIMESTAMP column:
CREATE TABLE links (lastDate TIMESTAMP);
CREATE TABLE links (lastDate TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
Ques 91. How Many Ways to Get the Current Time?
There are 8 ways to get the current time:
SELECT NOW() FROM DUAL;
2006-07-01 10:02:41
SELECT CURRENT_TIME() FROM DUAL;
10:02:58
SELECT SYSDATE() FROM DUAL;
2006-07-01 10:03:21
mysql> SELECT CURRENT_TIMESTAMP() FROM DUAL;
2006-07-01 10:04:03
SELECT LOCALTIME() FROM DUAL;
2006-07-01 10:07:37
mysql> SELECT LOCALTIMESTAMP() FROM DUAL;
2006-07-01 10:08:08
mysql> SELECT UTC_TIME() FROM DUAL;
14:09:22
mysql> SELECT UTC_TIMESTAMP() FROM DUAL;
2006-07-01 14:09:49
Ques 92. What Are DDL Statements in MySQL?
DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements:
► CREATE - Creating a new database object.
► ALTER - Altering the definition of an existing data object.
► DROP - Dropping an existing data object.
Ques 93. How To Create a New Table in MySQL?
If you want to create a new table, you can use the "CREATE TABLE" statement. The following tutorial script shows you how to create a table called "tip":
mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE NULL);
Query OK, 0 rows affected (0.08 sec)
This scripts creates a testing table called "tip" with 4 columns in the current database.
Ques 94. What Happens If You No CREATE Privilege in a Database?
In order to create tables in a database, your user account must have the CREATE privilege for that database. Otherwise you will get an error as shown in the following tutorial exercise:
>cd mysqlbin
>mysql -u guest -ppub
mysql> use ggl;
Database changed
mysql> CREATE TABLE test (id integer);
ERROR 1142 (42000): CREATE command denied to user
'guest'@'localhost' for table 'test'
If you get this error, you need to see the DBA to obtain the CREATE privilege.
Ques 95. How To Get a List of All Tables in a Database?
If you want to see the table you have just created, you can use the "SHOW TABLES" command to get a list of all tables in database. The tutorial script gives you a good example:
mysql> SHOW TABLES;
+---------------+
| Tables_in_ggl |
+---------------+
| links |
| tip |
+---------------+
3 rows in set (0.00 sec)
Ques 96. How To Get a List of Columns in an Existing Table?
mysql> SHOW COLUMNS FROM test;
Ques 97. How To See the CREATE TABLE Statement of an Existing Table?
SHOW CREATE TABLE test;
Ques 98. How To Create a New Table by Selecting Rows from Another Table in MySQL?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "CREATE TABLE ... SELECT" statement. The tutorial script below gives you a good example:
mysql> INSERT INTO tip VALUES (1, 'Learn MySQL',
'Visit www.GlobalGuideLine.com','2006-07-01');
Query OK, 1 row affected (0.62 sec)
mysql> CREATE TABLE tipBackup SELECT * FROM tip;
Query OK, 1 row affected (0.49 sec)
Records: 1 Duplicates: 0 Warnings: 0
Ques 99. How To Add a New Column to an Existing Table in MySQL?
If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD COLUMN" statement. The tutorial script below shows you a good example:
mysql> ALTER TABLE test ADD COLUMN author VARCHAR(40);
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0
Ques 100. How To Delete an Existing Column in a Table?
If you have an existing column in a table and you do not need that column any more, you can delete it with "ALTER TABLE ... DROP COLUMN" statement. Here is a tutorial script to delete an existing column:
mysql> ALTER TABLE test DROP COLUMN create_date;
Query OK, 1 row affected (0.48 sec)
Records: 1 Duplicates: 0 Warnings: 0
Ques 101. How To Rename an Existing Column in a Table?
If you have an existing column in a table and you want to change the column name, you can use the "ALTER TABLE ... CHANGE" statement. This statement allows you to change the name of a column, and its definition. The tutorial script below gives you a good example:
mysql> ALTER TABLE test CHANGE COLUMN subject
title VARCHAR(60);
Query OK, 1 row affected (0.51 sec)
Records: 1 Duplicates: 0 Warnings: 0
Ques 102. How To Rename an Existing Table in MySQL?
If you want to rename an existing table, you can use the "ALTER TABLE ... RENAME TO" statement. The tutorial script below shows you a good example:
mysql> ALTER TABLE test RENAME TO wb;
Query OK, 0 rows affected (0.01 sec)
Ques 103. How To Drop an Existing Table in MySQL?
If you want to delete an existing table and its data rows, you can use the "DROP TABLE" statement as shown in the tutorial script below:
mysql> DROP TABLE tipBackup;
Query OK, 0 rows affected (0.00 sec)
Ques 104. How To Create a Table Index in MySQL?
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:
mysql> CREATE TABLE wb(id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE NULL);
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE INDEX wb_subject ON wb(subject);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
Ques 105. How To Get a List of Indexes of an Existing 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. The tutorial script below shows you a nice example:
mysql> SHOW INDEX FROM test;
Ques 106. How To Drop an Existing Index in MySQL?
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:
mysql> DROP INDEX wb_subject ON test;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
Ques 107. How To Create a New View in MySQL?
You can create a new view based on one or more existing tables by using the "CREATE VIEW viewName AS selectStatement" statement as shown in the following script:
mysql> CREATE TABLE comment (faqID INTEGER,
message VARCHAR(256));
Query OK, 0 rows affected (0.45 sec)
mysql> INSERT INTO comment VALUES (1, 'I like it');
Query OK, 1 row affected (0.00 sec)
mysql> CREATE VIEW faqComment AS SELECT f.id, f.title,
f.description, c.message FROM faq f, comment c
WHERE f.id = c.faqID;
Query OK, 0 rows affected (0.06 sec)
Ques 108. How To Drop an Existing View in MySQL?
If you have an existing view, and you don't want it anymore, you can delete it by using the "DROP VIEW viewName" statement as shown in the following script:
mysql> DROP VIEW faqComment;
Query OK, 0 rows affected (0.00 sec)
Most helpful rated by users: