[ Pobierz całość w formacie PDF ]
.For example, if you perform an INSERToperation using the -eswitch in non-interactive mode, you won t receive a message that tells you the number of rowsinserted.When you insert rows in interactive mode, the server gives you feedbackthat includes the number of rows from the statement.Some CLI switches are useful only in non-interactive mode, most notably is the -eor --executeswitch.An example of the -eswitch is shown in Figure 8-4.In theexample in Figure 8-4, the -pswitch specifies that MySQL should prompt for a pass-word and the word mysqlat the end of the command indicates the database I d liketo connect to and within which I plan to execute the query that appears in quotesafter the -eswitch.(I provide more information on these switches, and on the syn-tax of the CLI, later in this chapter.) 114932-4 ch08.F 5/29/02 3:39 PM Page 136Part II &' SQL Essentials136Figure 8-4: Using the -e switch to execute a query innon-interactive modeAlthough it is possible to execute other commands with the -eswitch, I don tnormally recommend executing DDL (Data Definition Language) commands such asALTER TABLEwith the -eswitch.MySQL still prints error messages back to thecommand line, but I ve found that DDL commands usually require more than onecommand to be run (for example, a command to describe a table) before alterationcan begin.Command recall reusing the command or statement you last typed by pressingthe up-arrow on the keyboard is one of the best features of the MySQL CLI.Thisfeature saves countless keystrokes when you make a typographical or spelling errorin the middle of a long query and have to run it again.Additionally, command recallmakes large and complex queries easier to work with; if you find that the output isnot exactly as you want it, simply use the up-arrow and tweak the query when itappears on-screen.Starting the CLI the BasicsBy now you ve probably already been introduced to the CLI through some earlierchapters or the introduction in this chapter.Don t worry if you haven t read thosechapters; the CLI itself makes only a brief appearance there.The command to start the MySQL CLI is contained normally in the bin directory ofthe MySQL installation.However, it may be located in another directory dependingon how you installed MySQL or the operating system you are using.In Linux, I veseen the command located in /usr/bin, /usr/local/bin,/usr/local/mysql/binand somewhere in the /varmount. 114932-4 ch08.F 5/29/02 3:39 PM Page 137Chapter 8 &' Command Line Interface (CLI)137How you start the MySQL CLI depends largely on what you want to do.To workwith a database interactively, you have to supply credentials to establish a connec-tion.Normally those credentials come in the form of a username and a password though they can include an optional name of the database you re working with, aswell as the hostname (or IP address) of the server.In Figure 8-5, for example, I mconnecting to the local server (localhost) as username suehringand telling theprogram to perform two tasks: Ask me for a password and connect me to theecommerceserver.Figure 8-5: Connecting as username suehring to the ecommercedatabase and specifying a passwordCross- The examples throughout this chapter assume that the user account you re usingReferencehas sufficient access privileges to perform the operations.If this is not the case,consult Chapter 12 for a description of how to add a user account with specifiedprivileges.Specifying the usernameWhen using mysqlin Linux, normally you need not add the -u(username) switch.If the -uusername switch is not specified, the mysqlcommand uses the usernamefrom your environment variables.For that authentication to work, the MySQLserver must have a user entry with correct privileges in its user table.In Figure 8-6,the -uswitch is not used, so MySQL grabs the username suehringfrom the envi-ronment variables.Because a user suehringexists in the MySQL database and hasthe proper privileges, I can connect to the database server after supplying thecorrect password. 114932-4 ch08.F 5/29/02 3:39 PM Page 138Part II &' SQL Essentials138Figure 8-6: MySQL uses the username of the current user if the-u switch is not used.Accordingly, if you are using the CLI as the rootuser, MySQL attempts to connectas the rootuser if the -uswitch is not used.If you want to override the default user and have the MySQL CLI authenticate as adifferent user every time, you can add the username=clause to theMySQL configuration file.Cross- For details on how to set and change default variables for the MySQL CLI, see theReference MySQL CLI Environment Variables section later in this chapter.Specifying the passwordOne source of confusion for some is the use of the -pswitch to specify the pass-word.Often people believe that what follows the -pis actually the password butthis isn t always the case.The -pswitch simply tells the mysqlprogram to promptfor the password if none is given.In the example in Figure 8-5, a space appears afterthe -pswitch it s prompting you for the password.If you want to specify thepassword on the command line, you can do so by placing the password immedi-ately after the -pswitch, as shown in Figure 8-7.CautionSpecifying the password on the command line should be considered a securityrisk because the password is right in plain sight for a would-be attacker to see.Additionally, the --passwordswitch can be substituted for the -pswitch and isused much the same way.If you provide just the --passwordswitch you areprompted for the password.However, unlike the -pswitch, the --passwordsyntaxrequires you to specify the password on the command line by using an equals sign(=), as shown in Figure 8-8. 114932-4 ch08.F 5/29/02 3:39 PM Page 139Chapter 8 &' Command Line Interface (CLI)139Figure 8-7: Specifying the password immediately following the-p switch prevents the CLI from prompting for a password.Figure 8-8: The syntax for the --password switch is slightly differentfrom the -p switch.The password=switch can be added into the MySQL configuration fileand thus saved between sessions.This is also a security risk.Alternatively, youcould simply place the word passwordunder the [mysql]section of the configura-tion file and you are prompted for the password just as if you used the -pswitch.(See this chapter s MySQL CLI Environment Variables section for more aboutworking with CLI variables.)Specifying the hostIn much the same way that MySQL uses the current user if the -uswitch is notused, not using the -hswitch makes MySQL assume you mean to connect to theserver on the localhost (the current machine).If you want to connect to a MySQLserver on another machine, you can specify the IP address or hostname of theother MySQL server via the -hswitch, as shown in Figure 8-9. 114932-4 ch08.F 5/29/02 3:39 PM Page 140Part II &' SQL Essentials140Figure 8-9: Use the -h switch to connect to another MySQL serveror leave it blank to connect to the local machine.If you want to set a default host other than localhost without having to specifythe -h switch every time you can set the host inside the configurationfile, see the discussion of environment variables (later in this chapter) for detailson how to set and change default variables for the MySQL CLI.Any host specified ina configuration file is overridden if you specify another -h on the com-mand line.Specifying the databaseThe MySQL CLI enables you to specify the database to use without the addition ofany switches to the command line.The command expects the database to be speci-fied as the last part of the command line
[ Pobierz całość w formacie PDF ]