Ques 51. 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 52. 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 53. 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 54. 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 55. 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)