Podobne
- Strona startowa
- McGraw.Hill,.Digital.Animation.Bible.Creating.Professional.Animation.with.3ds.Max.Lightwave.and.Maya.(2004).LiB
- [eBook] DirectX 3D Graphics Programming Bible
- Windows XP Bible
- Photoshop 6 Bible (eBook)
- Access 2002 Bible
- Redhat Linux 7.2 Bible
- Dreamweaver MX Bible
- Lofting Hugh Ogrod zoologiczny doktora Dolit
- Richard Dawkins Bog urojony
- H.P. Lovecraft 16 opowiadan
- zanotowane.pl
- doc.pisz.pl
- pdf.pisz.pl
- mizuyashi.htw.pl
Cytat
Do celu tam się wysiada. Lec Stanisław Jerzy (pierw. de Tusch-Letz, 1909-1966)
A bogowie grają w kości i nie pytają wcale czy chcesz przyłączyć się do gry (. . . ) Bogowie kpią sobie z twojego poukładanego życia (. . . ) nie przejmują się zbytnio ani naszymi planami na przyszłość ani oczekiwaniami. Gdzieś we wszechświecie rzucają kości i przypadkiem wypada twoja kolej. I odtąd zwyciężyć lub przegrać - to tylko kwestia szczęścia. Borys Pasternak
Idąc po kurzych jajach nie podskakuj. Przysłowie szkockie
I Herkules nie poradzi przeciwko wielu.
Dialog półinteligentów równa się monologowi ćwierćinteligenta. Stanisław Jerzy Lec (pierw. de Tusch - Letz, 1909-1966)
[ Pobierz całość w formacie PDF ]
.The LIKEmodifier is useful if you don t know the name of the database you re looking for or ifyou d simply like to limit the results to certain databases.Figure 9-1: Using a SHOW DATABASES statement to learn aboutthe databases on the MySQL server. 124932-4 ch09.F 5/29/02 3:40 PM Page 173Chapter 9 &' SQL According to MySQL173SHOW TABLESThe SHOW TABLESstatement and related modifiers enable you to find out whattables are in a given database.As with the SHOW DATABASESstatement, the SHOWTABLESstatement enables listing of tables that match a certain pattern via the LIKEmodifier.It is also possible to list tables from another database on the same server,even if you re connected to a different database at the time.In Figure 9-2, I am con-nected to the default mysqldatabase but I use the SHOW TABLESstatement to listtables from the ecommercedatabase.Figure 9-2: The SHOW TABLES statement is flexible enough thatyou can list tables from another database, even if you re notconnected to it at the time.SHOW OPEN TABLESThe SHOW OPEN TABLESstatement can assist in troubleshooting and evaluatingdatabase performance.The statement gives additional information such as numberof cached and in-use copies of the table.In Figure 9-3 I connect to the MySQLserver, show tables from the ecommercedatabase, and then connect with anotherthread to perform a simple selection from the customer table.Looking at the opentables from the ecommercedatabase, you can see that the Customertable is open.SHOW COLUMNSUsing the SHOW COLUMNSstatement, you can gather information about the columnsin a table.For some uses of the SHOW COLUMNSstatement, the DESCRIBEstatementcan be substituted.As with other SHOWstatements, the SHOW COLUMNSstatementsupports the use of wildcards.As with the SHOW TABLES statement, the optionalFROM modifier can be used to look at columns from anotherdatabase s tables. 124932-4 ch09.F 5/29/02 3:40 PM Page 174Part II &' SQL Essentials174Figure 9-3: Looking at open tables in a databaseSHOW FULL COLUMNSUsing the SHOW FULL COLUMNSstatement, you can determine what privileges youhave for the columns in that table.In Figure 9-4, I look at the privileges for my user-name, suehring, in the columns of the Customertable.Figure 9-4: Using the SHOW FULL COLUMNS command to lookat the privileges the current user has for the table 124932-4 ch09.F 5/29/02 3:40 PM Page 175Chapter 9 &' SQL According to MySQL175SHOW INDEXThe SHOW INDEXstatement also supports the FROM optionalargument.The output from the SHOW INDEXcommand is worth some explanation.Figure 9-5 illustrates the output from the SHOW INDEXstatement run against theCustomertable from the ecommercedatabase.Figure 9-5: Output from the SHOW INDEX statement on thecustomer table of the ecommerce databaseNoteI am using the E or vertical output type for the SHOW INDEX statement toproduce output that is easier to understand.From the output in Figure 9-5, you can see the table name is Customerand thevalue of Non_uniqueis 0.A value of 0 in the Non_uniquefield means that the indexcannot contain duplicates, which is the case for the Customertable.The Key_nameis PRIMARY,which shows that this index is actually the Primary Key index for thetable.As no records are in the table yet, the Seq_in_indexvalue is 1.TheSeq_in_indexis an abbreviation for Sequence In Index.The value in the Column_namefield shows the name of the indexed column (in thiscase, the email_addesscolumn).The collation column indicates the sorting forthe index (in this case, A for ascending); this value could also be NULL, which wouldindicate no sorting.Cardinalityindicates the number of unique values in theindex.Because no records exist in this table, the value is 0 in the example.The Sub_partfield indicates how much of a column is indexed when only a certainnumber of characters are indexed.This value is NULLwhen the entire key isindexed, as is the case in the example.Finally, the Commentfield serves to deter-mine whether the index is a FULLTEXTindex, which it is not for the example shown. 124932-4 ch09.F 5/29/02 3:40 PM Page 176Part II &' SQL Essentials176SHOW TABLE STATUSThe SHOW TABLE STATUSstatement is much like other SHOWstatements; it willaccept wildcards.If no LIKEwildcard argument is given, the statement returns thetable status for each table in the database.This can be quite a bit of information, soI recommend using a pager to split the output or using a LIKEwildcard to limitthe output to the table(s) you want information about.Cross- If you re unsure of how to use a pager to divide data, refer to Chapter 8.ReferenceUsing the SHOW TABLE STATUSstatement, along with the LIKEmodifier to limit theoutput, I can examine the output in greater detail, as in Figure 9-6.Figure 9-6: Limiting the output of the SHOW TABLE STATUSstatement with the LIKE modifierThe name of the table, Customer, should be obvious.The Typeof the table is thedefault for this database server, MyISAM.Cross- Other table types are covered in Chapter 10.ReferenceThe Row_formatfor the table is Dynamicbut could also be Fixedor Compressed.Because this table contains no data, the Rows, Avg_row_length, and Data_lengthall have 0values.A couple of infrequently used values are the Max_data_lengthvalue (which indicates the maximum acceptable value for the data file) and theIndex_lengthvalue (which indicates the length of the index).The Data_freevalue would indicate any allocated-but-unused bytes of data in the database.A sometimes-useful value is the Auto_incrementfield, which in this instance is NULLbecause this table contains no auto-incremented columns.I ve been involved in cases 124932-4 ch09.F 5/29/02 3:40 PM Page 177Chapter 9 &' SQL According to MySQL177where a database operator updates an auto-increment field and thus throw it out ofsync.Using the SHOW TABLE STATUSstatement I can find out what the next value isand work from there to determine what it needs to be with a SELECTstatement.The Create_timeand Update_timeshow when the table was created and when itwas updated, if different.As you can see from the example in Figure 9-6, the tablehasn t been updated.The value of the Check_timeis NULLfor this table, whichindicates that it hasn t been checked using a tool such as myisamchkor an OPTI-MIZETABLEstatement.The Create_optionsfield is also blank as there were noextra options given to the CREATEstatement that produced this table.Finally, thevalue of the Commentfield is blank as well because no comment was given with theCREATEstatement for this table.The SHOW STATUSstatement lists the status of many server variables.The output isthe same as the mysqladminextended-statuscommand.Similarly, the SHOWVARIABLES [LIKE ]statement also has information that can be obtainedthrough a mysqladmincommand.The SHOW VARIABLESstatement shows varioussettings for variables of the MySQL Server.Both statements return quite a bit ofinformation so I recommend using a pager in the CLI to make the output more man-ageable.Cross- For more information on the SHOW STATUS and SHOW VARIABLES statements,Referencesee Appendix A or in Chapter 10.The SHOW PROCESSLISTstatement shows the current threads open on thedatabase including the first 100 characters of the query that the thread is running.Given the FULLmodifier, the statement shows the entire query.SHOW GRANTS FORThe SHOW GRANTS FOR statement, which I refer to simply as SHOW GRANTS,enables you to find out quickly the privileges that a given user has on a server and the statements you would have to issue if you were to give that user thosesame privileges.For example, one task I am confronted with frequently is giving a user access to anew database system as new servers are implemented.The user almost alwaysneeds the same access as they have on the existing system
[ Pobierz całość w formacie PDF ]