|
[ Pobierz całość w formacie PDF ]
.Now the statement looks like this:SELECT * FROM user WHERE user LIKE sue% ; 124932-4 ch09.F 5/29/02 3:40 PM Page 200Part II &' SQL Essentials200Figure 9-27: Using the LIKE function instead of the equals sign forfinding information with a wildcardAnother wildcard, the underscore (_), can be used to substitute one character.Going back to the spelling of my name as an example, people frequently transposethe h and the r, misspelling the name as Suerhing.To find such instances, I coulduse two underscores together, like this:SELECT * FROM user WHERE user LIKE sue__ing ;As you can see from Figure 9-28, the results end up the same.Figure 9-28: Using the underscore as a wildcard to match onecharacter inside a SELECT statement 124932-4 ch09.F 5/29/02 3:40 PM Page 201Chapter 9 &' SQL According to MySQL201I ll come back to the WHEREmodifier.For now, I need to jump back to the actualselect portion of the SELECTstatement.Up until this point I ve been using the aster-isk to select all columns.You can also select many other items with the SELECTstatement including specific columns and the results of functions on columns.Forexample, if I wanted to only determine which hosts the user suehring is allowedto connect from, I could perform the following query (the results of which areshown in Figure 9-29):SELECT user,host FROM user where user = suehring ;Figure 9-29: Selecting certain columns with the SELECT statementSELECT functionsNumerous functions can be used in SELECTstatements some are useful to know(and have around for reference), regardless of whether you use them with adatabase.This section, however, highlights some functions that illustrate how pow-erful MySQL can be.Cross- In Appendix A all functions and operators for the SELECTstatement are covered inReferencedetail.As with many other portions of SELECTstatements, functions can be nested insideeach other.For example, the following statement uses two functions, the DATE_ADDand the NOWfunctions.The statement results in two days being added to the cur-rent date, as determined by the MySQL server:SELECT DATE_ADD(NOW(), INTERVAL 2 day);MySQL also uses functions such as greater than (>), less than () to accomplish this redi-rection.For example, to rerun the --all-databasesmacro from the example inFigure 10-37, the command would bemysqldump -A -p > outfile.txtPerforming that command produces a file that could be imported onto anotherserver or servers to create a snapshot of the database server at that point in time.The file could also be used as a backup as well.Figure 10-38 shows another run ofthe command, followed by a look at the resulting data.The databases functionThe second macro-level function with mysqldumpis the --databasesor -Bfunc-tion.This option takes databases as arguments, producing the same type of outputas the --all-databasesmacro.Specifically, the --databasesfunction includesall DDL to create the database(s) and table(s) as well as the USEstatements toconnect to those new databases.The difference between the --databasesand 134932-4 ch10.F 5/29/02 3:41 PM Page 270Part II &' SQL Essentials270the--all-databasesmacros is that you can use the --databasesoption tospecify only the databases you d like to use; the --all-databasesoption includesall databases.Figure 10-38: Taking the mysqldump command and redirecting theoutput to a fileThe --databasesfunction expects at least one database name to follow as an argu-ment.If you want to specify more than one database, separate the database namessingle spaces.In Figure 10-39 the --databasesfunction serves to produce a dumpof the MySQL grants database and the ecommerce database example.Normally Iwould redirect the output to a file (as shown in Figure 10-38); the command thatproduces these results is as follows:mysqldump -p --databases mysql ecommerceFigure 10-39: Using the --databases function to produce a dump oftwo databases 134932-4 ch10.F 5/29/02 3:41 PM Page 271Chapter 10 &' Databases And Data271Another switch, the --tablesswitch, is used with the --databasesfunction tospecify the tables in the database to include in the output.Suppressing creation of databasesWith the two macro level functions defined previously, the --all-databases switchand the --databasesswitch, you can prevent mysqldumpfrom issuing the state-ment to create the database or databases.The switch for this action is -nor --no-create-db.For example, if you are migrating from one server to another and already have thedatabases created there is no reason to include CREATEDATABASEstatements in theresults from mysqldump.Using the -nswitch, no CREATE DATABASEstatements willbe included.SELECT INTO OUTFILEAnother method for extracting or exporting data from MySQL is with the SELECT.INTO OUTFILEstatement.This statement is usually run from inside theMySQL CLI and can produce output much the same as mysqldump.The SELECT INTO OUTFILEstatement is really a SELECTstatement at heart, withthe INTO OUTFILEmodifier thrown onto the end to send the output of the queryinto a file.Therefore, modifiers such as grouping and ordering as well as the WHEREclause are available for use with the INTO OUTFILEstatement.Cross- Chapter 9 and Appendix B provide more information on the SELECT statement.ReferenceChapter 9 shows examples of the SELECT statement in action while Appendix Bprovides the syntax of the SELECT statement in MySQL.The output file when using the INTO OUTFILEmodifier is located in the database sdirectory on the server.For example, if your datadir for MySQL is/usr/local/varunderneath which appear the directories for the variousdatabases, the OUTFILEwill appear inside the actual database s directory unlessspecified otherwise.As with the mysqldump--tabutility, the SELECT INTO OUTFILEstatement pro-duces the data in a tab-delimited format.This makes it easy to import into a pro-gram such as Microsoft Access.TipUnlike the mysqldump utility, the SELECT INTO OUTFILE statement does notsupport the selection of all tables from multiple databases.If you want to exportthe data to another MySQL server, mysqldump is the better choice.Much the same as mysqldump, SELECT INTO OUTFILEsupports a number ofoptions that control how the data is exported.These options include FIELDS TER-MINATED BY, LINES TERMINATED BY, FIELDS ENCLOSED BY, and FIELDS ESCAPEDBY.These options work the same as with mysqldump. 134932-4 ch10.F 5/29/02 3:41 PM Page 272Part II &' SQL Essentials272The file specified in the SELECT.INTO OUTFILEstatement cannot alreadyexist.If the file exists, the statement will fail.Most of the battle with the SELECT.INTO OUTFILEstatement is getting theSELECTstatement correct.Syntax examples for the SELECT.INTO OUTFILEstatement follow, as shown in Figures 10-40 and 10-41.Figure 10-40: An example of a basic SELECT statement coupled withthe INTO OUTFILE modifier and then a look at the results in the fileFigure 10-41: Another SELECT statement using an ORDER BYcoupled with the INTO OUTFILE modifier and then a look at theresults in the fileThe output from the statement in Figure 10-40 (and many others exported throughMySQL) can be imported into other programs such as Microsoft Access.Most pro-grams can recognize the Tab character as a delimiter.An example of importing theoutput from Figure 10-41 is shown in Figure 10-42. 134932-4 ch10.F 5/29/02 3:41 PM Page 273Chapter 10 &' Databases And Data273Figure 10-42: Importing the data into MicrosoftAccess, produced from a SELECT INTO OUTFILEstatementImporting DataShowing an example of the import process at the end of the preceding section leadsdirectly into this section, Importing Data Into MySQL Databases
[ Pobierz całość w formacie PDF ]
zanotowane.pldoc.pisz.plpdf.pisz.plhanula1950.keep.pl
|