Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

Chapter 10 . Databases And Data 277 Summary . MySQL includes six table types. Three are non-transactional, two are transactional, and one resides only in volatile memory. . The main table type in MySQL is MyISAM. MyISAM descends from the ISAM table type that includes fewer features. The MERGE table type is a collection of MySQL tables for quicker operation. . The two transactional table types in MySQL are BerkeleyDB and InnoDB. These table types use more resources than regular non-transactional tables but are safer in the event of error. . The HEAP table type is a temporary table that resides only in volatile memory. . The mysqladmin command is a powerful command for management and administration of a MySQL server. . With mysqladminyou can stop the server, troubleshoot performance problems, and create or delete databases. . There are two main methods for exporting data from MySQL, mysqldump and the SELECT … INTO OUTFILEstatement. . The mysqldump utility includes the ability to export both DDL and DML. Therefore, with mysqldump you can create a complete snapshot of a server including databases, tables, and the actual data. . The SELECT … INTO OUTFILEstatement is normally run from the MySQL CLI. With SELECT … INTO OUTFILEyou have the power of a regular SELECT statement while exporting data with many powerful options. . There are three main methods for importing data into MySQL. Data can be imported through the MySQL CLI, with the LOAD DATA INFILE statement, or with mysqlimport. . The MySQL CLI enables you to execute SQL statements by reading the contents of a file from the command line. . With the LOAD DATA INFILE statement you can import through the CLI with a number of options such as how to handle duplicate values. . The mysqlimport utility is a command-line version of LOAD DATA INFILE and includes the same options. The mysqlimport utility also includes an option to delete the data from the table prior to import. …
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

276 Part II . SQL Essentials mysqlimport The mysqlimport utility is a command-line program for importing data into a MySQL database. The mysqlimportutility is similar in usage to the LOAD DATA INFILE statement. The basic syntax for the mysqlimport utility is as follows: mysqlimport [OPTIONS] database textfile Tip As with most MySQL programs, you can obtain a syntax listing for mysqlimport by typing the command with no switches or arguments, or by using the -? or -help switch. Much like the MySQL CLI, mysqlimportuses the same switches for specifying the user, host, port, password, and so on. Cross-Chapter 8 covers the basic switches. Reference As mysqlimportis really a command-line version of LOAD DATA INFILE, the functionality is nearly the same. One exception not located in another statement or command is the -dor –delete switch. Using –delete, mysqlimportwill delete the contents of the table prior to importing. With LOAD DATA INFILE, if you specify a column or columns to import into you do so by adding them onto the end of the statement. With mysqlimport, this is added with the -cor –columns=switch. The REPLACEand IGNORE functionality is called with the -ror –replaceand -I -r –ignore, respectively. Much like the LOAD DATA INFILE, SELECT … INTO OUTFILE, and mysqldump utilities the use of switches for formatting is supported by mysqlimport. These are as follows: –fields-terminated-by= –fields-enclosed-by= –fields-optionally-enclosed-by= –fields-escaped-by= –lines-terminated-by= The –local or -L switch tells mysqlimport to look on the local client for the file to import. This is as opposed to the normal behavior where MySQL will search for the file on the server running mysqld. Notice that the switch is an uppercase L. The lowercase L (-l)switch is a synonym for the –lock-tablesfunction that locks all tables before proceeding with the import.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

Chapter 10 . Databases And Data 275 The LOAD DATA INFILEstatement enables you to determine the action when a collision occurs for records being imported to a unique column. The statement can either replace the existing record with the new one or ignore the new record entirely. The keywords to specify this behavior are REPLACE and IGNORE, respectively. Cross-Other LOAD DATA INFILE options (such as the FIELDS TERMINATED BY, and Reference LINES TERMINATED BY) work the same as in the SELECT … INTO OUTFILE statement and mysqldump. This chapter includes descriptions and syntax examples for these options. One additional behavior not found in the SELECT … INTO OUTFILE statement or mysqldumputility is IGNORE LINES. This modifier appears in the LINES TERMINATED BY clause. Using the IGNORE LINES modifier you can tell the LOAD DATA INFILE statement to ignore or skip over a certain number of lines at the beginning of the file to be imported. For example, if you have the column names as the first line of the file you can have the LOAD DATA INFILEstatement ignore that first line with the addition of IGNORE 1 LINES to the LOAD DATA INFILE STATEMENT. You can also specify only certain columns to import into. The column or column list comes at the end of the statement; if specifying more than one column, separate them with a comma. For example, to import one of the files from a previous example and only import the email_addresscolumn, the following statement is issued: LOAD DATA INFILE ecommercecustomer.txt INTO TABLE customer (email_address); The results from the preceding statement are shown in Figure 10-44. Figure 10-44: An example of using the LOAD DATA INFILE statement to import only one column
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

274 Part II . SQL Essentials Figure 10-43: Importing the contents of a file into a database through the MySQL CLI LOAD DATA INFILE The complement to the SELECT … INTO OUTFILE statement is the LOADDATA INFILE statement. This statement is normally run from the MySQL CLI to import data into a table. The syntax for this statement is as follows: LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE filename.txt [REPLACE | IGNORE] INTO TABLE
[FIELDS [TERMINATED BY t ] [[OPTIONALLY] ENCLOSED BY ] [ESCAPED BY \ ] ] [LINES TERMINATED BY n ] [IGNORE LINES] [(,…)] The LOW_PRIORITYkeyword causes the LOAD DATA statement to wait until no other clients are reading from the table. The CONCURRENT keyword allows other clients to read from the table while the LOAD DATA statement is executing. Files are normally assumed to be on the server machine. With the LOCAL keyword, however, the file is read from the client host that executes the command. The filename specified must be an absolute path, whether in the data directory of MySQL or in the actual database s directory on the server. Caution Using the LOCAL keyword blocks you from stopping the import process once begins.
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

Chapter 10 . Databases And Data 273 Figure 10-42: Importing the data into Microsoft Access, produced from a SELECT INTO OUTFILE statement Importing Data Showing an example of the import process at the end of the preceding section leads directly into this section, Importing Data Into MySQL Databases. This section examines three methods for importing data into MySQL: From the CLI, with the LOAD DATA INFILE statement, and with the mysqlimport utility. CLI The MySQL CLI includes the ability to import data. In reality, the CLI allows any SQL statements in a file to be read in via the command line. The syntax for this feature is as follows: mysql [OPTIONS] database < filename The options would include things like the user, host, and password for the MySQL CLI to authenticate just as with any other use of the CLI. The file is expected to contain valid SQL statements, many times INSERT or UPDATE statements produced with mysqldump. For example, Figure 10-43 shows the contents of a file to import into the ecommerce database. The contents of the file were produced with mysqldump. Figure 10-43 also shows the command that will insert these into the database. It really is that simple.
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

272 Part II . SQL Essentials The file specified in the SELECT … INTO OUTFILEstatement cannot already exist. If the file exists, the statement will fail. Most of the battle with the SELECT … INTO OUTFILEstatement is getting the SELECT statement correct. Syntax examples for the SELECT … INTO OUTFILE statement follow, as shown in Figures 10-40 and 10-41. Figure 10-40: An example of a basic SELECT statement coupled with the INTO OUTFILE modifier and then a look at the results in the file Figure 10-41: Another SELECT statement using an ORDER BY coupled with the INTO OUTFILE modifier and then a look at the results in the file The output from the statement in Figure 10-40 (and many others exported through MySQL) can be imported into other programs such as Microsoft Access. Most programs can recognize the Tab character as a delimiter. An example of importing the output from Figure 10-41 is shown in Figure 10-42.
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

Chapter 10 . Databases And Data 271 Another switch, the –tables switch, is used with the –databasesfunction to specify the tables in the database to include in the output. Suppressing creation of databases With the two macro level functions defined previously, the –all-databases switch and the –databasesswitch, you can prevent mysqldumpfrom issuing the statement to create the database or databases. The switch for this action is -nor –nocreate- db. For example, if you are migrating from one server to another and already have the databases created there is no reason to include CREATEDATABASE statements in the results from mysqldump. Using the -nswitch, no CREATE DATABASE statements will be included. SELECT INTO OUTFILE Another method for extracting or exporting data from MySQL is with the SELECT … INTO OUTFILE statement. This statement is usually run from inside the MySQL CLI and can produce output much the same as mysqldump. The SELECT INTO OUTFILEstatement is really a SELECT statement at heart, with the INTO OUTFILEmodifier thrown onto the end to send the output of the query into a file. Therefore, modifiers such as grouping and ordering as well as the WHERE clause are available for use with the INTO OUTFILEstatement. Cross-Chapter 9 and Appendix B provide more information on the SELECT statement. Reference Chapter 9 shows examples of the SELECT statement in action while Appendix B provides the syntax of the SELECT statement in MySQL. The output file when using the INTO OUTFILE modifier is located in the database s directory on the server. For example, if your datadir for MySQL is /usr/local/var underneath which appear the directories for the various databases, the OUTFILE will appear inside the actual database s directory unless specified otherwise. As with the mysqldump–tab utility, the SELECT INTO OUTFILE statement produces the data in a tab-delimited format. This makes it easy to import into a program such as Microsoft Access. Tip Unlike the mysqldump utility, the SELECT INTO OUTFILE statement does not support the selection of all tables from multiple databases. If you want to export the data to another MySQL server, mysqldump is the better choice. Much the same as mysqldump, SELECT INTO OUTFILE supports a number of options that control how the data is exported. These options include FIELDS TERMINATED BY, LINES TERMINATED BY, FIELDS ENCLOSED BY, and FIELDS ESCAPED BY. These options work the same as with mysqldump.
In case you need quality webspace to host and run your web applications, try our personal web hosting services.