mysql> SELECT @id := cust_id FROM customers WHERE cust_id=’ customer name ‘; mysql> DELETE FROM customers WHERE cust_id = @id; mysql> DELETE FROM orders WHERE cust_id = @id; The preceding SELECT statement assigns a column value to a variable, but variables also can be assigned values from arbitrary expressions. The following statement determines the highest sum of the arms and legs columns in the limbs table and assigns it to the @max_limbs variable: mysql> SELECT @max_limbs := MAX(arms+legs) FROM limbs; Another use for a variable is to save the result from LAST_INSERT_ID( ) after creating a new record in a table that has an AUTO_INCREMENT column: mysql> SELECT @last_id := LAST_INSERT_ID( ); LAST_INSERT_ID( ) returns the value of the new AUTO_INCREMENT value. By saving it in a variable, you can refer to the value several times in subsequent statements, even if you issue other statements that create their own AUTO_INCREMENT values and thus change the value returned by LAST_INSERT_ID( ). This is discussed further in Chapter 11. SQL variables hold single values. If you assign a value to a variable using a statement that returns multiple rows, the value from the last row is used: mysql> SELECT @name := thing FROM limbs WHERE legs = 0; +—————-+ | @name := thing | +—————-+ | squid | | octopus | | fish | | phonograph | +—————-+ mysql> SELECT @name; +————+ | @name | +————+ | phonograph | +————+ If the statement returns no rows, no assignment takes place and the variable retains its previous value. If the variable has not been used previously, that value is NULL: mysql> SELECT @name2 := thing FROM limbs WHERE legs < 0; Empty set (0.00 sec) mysql> SELECT @name2; +——–+ | @name2 | +——–+ | NULL |
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
Auto-completion allows you to cut down the amount of typing you do. However, if you don’t use this feature, reading name-completion information from the MySQL server may be counterproductive because it can cause mysql to start up more slowly when you have a lot of tables in your database. To tell mysql not to read this information so that it starts up more quickly, specify the -A (or –no-auto-rehash) option on the mysql command line. Alternatively, put a no-auto-rehash line in the [mysql] group of your MySQL option file: [mysql] no-auto-rehash To force mysql to read name completion information even if it was invoked in no-completion mode, issue a REHASH or # command at the mysql> prompt. 1.15 Using SQL Variables in Queries 1.15.1 Problem You want to save a value from a query so you can refer to it in a subsequent query. 1.15.2 Solution Use a SQL variable to store the value for later use. 1.15.3 Discussion As of MySQL 3.23.6, you can assign a value returned by a SELECT statement to a variable, then refer to the variable later in your mysql session. This provides a way to save a result returned from one query, then refer to it later in other queries. The syntax for assigning a value to a SQL variable within a SELECT query is @var_name := value, where var_name is the variable name and value is a value that you’re retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement. A common situation in which SQL variables come in handy is when you need to issue successive queries on multiple tables that are related by a common key value. Suppose you have a customers table with a cust_id column that identifies each customer, and an orders table that also has a cust_id column to indicate which customer each order is associated with. If you have a customer name and you want to delete the customer record as well as all the customer’s orders, you need to determine the proper cust_id value for that customer, then delete records from both the customers and orders tables that match the ID. One way to do this is to first save the ID value in a variable, then refer to the variable in the DELETE statements:[4] [4] In MySQL 4, you can use multiple-table DELETE statements to accomplish tasks like this with a single query. See Chapter 12 for examples.
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services
necessary and press Return. Then press Up Arrow twice more to recall the second line. Modify it, press Return, and the query will execute. Under Windows, mysql allows statement recall only for NT-based systems. For versions such as Windows 98 or Me, you can use the special mysqlc client program instead. However, mysqlc requires an additional library file, cygwinb19.dll. If you find a copy of this library in the same directory where mysqlc is installed (the bin dir under the MySQL installation directory), you should be all set. If the library is located in the MySQL lib directory, copy it into your Windows system directory. The command looks something like this; you should modify it to reflect the actual locations of the two directories on your system: C:> copy C:mysqllibcygwinb19.dll C:WindowsSystem After you make sure the library is in a location where mysqlc can find it, invoke mysqlc and it should be capable of input-line editing. One unfortunate consequence of using mysqlc is that it’s actually a fairly old program. (For example, even in MySQL 4.x distributions, mysqlc dates back to 3.22.7.) This means it doesn’t understand newer statements such as SOURCE. 1.14 Using Auto-Completion for Database and Table Names 1.14.1 Problem You wish there was a way to type database and table names more quickly. 1.14.2 Solution There is; use mysql’s name auto-completion facility. 1.14.3 Discussion Normally when you use mysql interactively, it reads the list of database names and the names of the tables and columns in your current database when it starts up. mysql remembers this information to provide name completion capabilities that are useful for entering statements with fewer keystrokes: Type in a partial database, table, or column name and then hit the Tab key. If the partial name is unique, mysql completes it for you. Otherwise, you can hit Tab again to see the possible matches. Enter additional characters and hit Tab again once to complete it or twice to see the new set of matches. mysql’s name auto-completion capability is based on the table names in the current database, and thus is unavailable within a mysql session until a database has been selected, either on the command line or by means of a USE statement.
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services
1.13.2 Solution Use mysql’s built-in query editor. 1.13.3 Discussion If you issue a long query only to find that it contains a syntax error, what should you do? Type in the entire corrected query from scratch? No need. mysql maintains a statement history and supports input-line editing. This allows you to recall queries so that you can modify and reissue them easily. There are many, many editing functions, but most people tend to use a small set of commands for the majority of their editing.[3] A basic set of useful commands is shown in the following table. Typically, you use Up Arrow to recall the previous line, Left Arrow and Right Arrow to move around within the line, and Backspace or Delete to erase characters. To add new characters to the line, just move the cursor to the appropriate spot and type them in. When you’re done editing, press Return to issue the query (the cursor need not be at the end of the line when you do this). [3] The input-line editing capabilities in mysql are based on the GNU Readline library. You can read the documentation for this library to find out more about the many editing functions that are available. For more information, check the Bash manual, available online at http://www.gnu.org/manual/. Editing Key Effect of Key Up Arrow Scroll up through statement history Down Arrow Scroll down through statement history Left Arrow Move left within line Right Arrow Move right within line Ctrl-A Move to beginning of line Ctrl-E Move to end of line Backspace Delete previous character Ctrl-D Delete character under cursor Input-line editing is useful for more than just fixing mistakes. You can use it to try out variant forms of a query without retyping the entire thing each time. It’s also handy for entering a series of similar statements. For example, if you wanted to use the query history to issue the series of INSERT statements shown earlier in Recipe 1.3 to create the limbs table, first enter the initial INSERT statement. Then, to issue each successive statement, press the Up Arrow key to recall the previous statement with the cursor at the end, backspace back through the column values to erase them, enter the new values, and press Return. To recall a statement that was entered on multiple lines, the editing procedure is a little trickier than for single-line statements. In this case, you must recall and reenter each successive line of the query in order. For example, if you’ve entered a two-line query that contains a mistake, press Up Arrow twice to recall the first line. Make any modifications
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
Temporarily Using a Table from Another Database To use a table from another database temporarily, you can switch to that database and then switch back when you’re done using the table. However, you can also use the table without switching databases by referring to the table using its fully qualified name. For example, to use the table other_tbl in another database other_db, you can refer to it as other_db.other_tbl. 1.12 Canceling a Partially Entered Query 1.12.1 Problem You start to enter a query, then decide not to issue it after all. 1.12.2 Solution Cancel the query using your line kill character or the c sequence. 1.12.3 Discussion If you change your mind about issuing a query that you’re entering, cancel it. If the query is on a single line, use your line kill character to erase the entire line. (The particular character to use depends on your terminal setup; for me, the character is Ctrl-U.) If you’ve entered a statement over multiple lines, the line kill character will erase only the last line. To cancel the statement completely, enter c and type Return. This will return you to the mysql> prompt: mysql> SELECT * -> FROM limbs -> ORDER BYc mysql> Sometimes c appears to do nothing (that is, the mysql> prompt does not reappear), which leads to the sense that you’re “trapped” in a query and can’t escape. If c is ineffective, the cause usually is that you began typing a quoted string and haven’t yet entered the matching end quote that terminates the string. Let mysql’s prompt help you figure out what to do here. If the prompt has changed from mysql> to “>, That means mysql is looking for a terminating double quote. If the prompt is ‘> instead, mysql is looking for a terminating single quote. Type the appropriate matching quote to end the string, then enter c followed by Return and you should be okay. 1.13 Repeating and Editing Queries 1.13.1 Problem The query you just entered contained an error, and you want to fix it without typing the whole thing again. Or you want to repeat an earlier statement without retyping it.
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
% mysql -h host -p -u user cookbook If you’ve already started a mysql session, you can select a database (or switch to a different one) by issuing a USE statement: mysql> USE cookbook; Database changed If you’ve forgotten or are not sure which database is the current one (which can happen easily if you’re using multiple databases and switching between them several times during the course of a mysql session), use the following statement: mysql> SELECT DATABASE( ); +————+ | DATABASE() | +————+ | cookbook | +————+ DATABASE( ) is a function that returns the name of the current database. If no database has been selected yet, the function returns an empty string: mysql> SELECT DATABASE( ); +————+ | DATABASE() | +————+ | | +————+ The STATUS command (and its synonym, s) also display the current database name, in additional to several other pieces of information: mysql> s Connection id: 5589 Current database: cookbook Current user: cbuser@localhost Current pager: stdout Using outfile: ‘’ Server version: 3.23.51-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset: latin1 Server characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 9 days 39 min 43 sec Threads: 4 Questions: 42265 Slow queries: 0 Opens: 82 Flush tables: 1 Open tables: 52 Queries per second avg: 0.054
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
mysql> SELECT NOW( ); +———————+ | NOW( ) | +———————+ | 2001-07-04 10:27:23 | +———————+ mysql> SELECT -> NOW( )g +———————+ | NOW( ) | +———————+ | 2001-07-04 10:27:28 | +———————+ Notice for the second query that the prompt changes from mysql> to -> on the second input line. mysql changes the prompt this way to let you know that it’s still waiting to see the query terminator. Be sure to understand that neither the ; character nor the g sequence that serve as query terminators are part of the query itself. They’re conventions used by the mysql program, which recognizes these terminators and strips them from the input before sending the query to the MySQL server. It’s important to remember this when you write your own programs that send queries to the server (as we’ll begin to do in the next chapter). In that context, you don’t include any terminator characters; the end of the query string itself signifies the end of the query. In fact, adding a terminator may well cause the query to fail with an error. 1.11 Selecting a Database 1.11.1 Problem You want to tell mysql which database to use. 1.11.2 Solution Name the database on the mysql command line or issue a USE statement from within mysql. 1.11.3 Discussion When you issue a query that refers to a table (as most queries do), you need to indicate which database the table is part of. One way to do so is to use a fully qualified table reference that begins with the database name. (For example, cookbook.limbs refers to the limbs table in the cookbook database.) As a convenience, MySQL also allows you to select a default (current) database so that you can refer to its tables without explicitly specifying the database name each time. You can specify the database on the command line when you start mysql: % mysql cookbook If you provide options on the command line such as connection parameters when you run mysql, they should precede the database name:
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
Change the PATH value to include the directory where mysql is installed. If this is C:mysqlbin, the resulting PATH setting looks like this: PATH=C:mysqlbin;C:WINDOWS;C:WINDOWSCOMMAND Or: SET PATH=C:mysqlbin;C:WINDOWS;C:WINDOWSCOMMAND Under Windows NT-based systems, another way to change the PATH value is to use the System control panel (use its Environment or Advanced tab, whichever is present). In other versions of Windows, you can use the Registry Editor application. Unfortunately, the name of the Registry Editor key that contains the path value seems to vary among versions of Windows. For example, on the Windows machines that I use, the key has one name under Windows Me and a different name under Windows 98; under Windows 95, I couldn’t find the key at all. It’s probably simpler just to edit AUTOEXEC.BAT. After setting an environment variable, you’ll need to cause the modification to take effect. Under Unix, you can log out and log in again. Under Windows, if you set PATH using the System control panel, you can simply open a new DOS window. If you edited AUTOEXEC.BAT instead, restart the machine. 1.10 Issuing Queries 1.10.1 Problem You’ve started mysql and now you want to send queries to the MySQL server. 1.10.2 Solution Just type them in, but be sure to let mysql know where each one ends. 1.10.3 Discussion To issue a query at the mysql> prompt, type it in, add a semicolon ( 
at the end to signify the end of the statement, and press Return. An explicit statement terminator is necessary; mysql doesn’t interpret Return as a terminator because it’s allowable to enter a statement using multiple input lines. The semicolon is the most common terminator, but you can also use g (”go”) as a synonym for the semicolon. Thus, the following examples are equivalent ways of issuing the same query, even though they are entered differently and terminated differently:[2] [2] Example queries in this book are shown with SQL keywords like SELECT in uppercase for distinctiveness, but that’s simply a typographical convention. You can enter keywords in any lettercase.
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
To set the value of PATH, use the instructions that pertain to your shell: For csh or tcsh, look for a setenv PATH command in your startup files, then add the appropriate directory to the line. Suppose your search path is set by a line like this in your .login file: setenv PATH /bin:/usr/bin:/usr/local/bin If mysql is installed in /usr/local/mysql/bin, add that directory to the search path by changing the setenv line to look like this: setenv PATH /usr/local/mysql/bin:/bin:/usr/bin:/usr/local/bin It’s also possible that your path will be set with set path, which uses different syntax: set path = (/usr/local/mysql/bin /bin /usr/bin /usr/local/bin) For a shell in the Bourne shell family such as sh, bash, or ksh, look in your startup files for a line that sets up and exports the PATH variable: export PATH=/bin:/usr/bin:/usr/local/bin The assignment and the export might be on separate lines: PATH=/bin:/usr/bin:/usr/local/bin export PATH Change the setting to this: export PATH=/usr/local/mysql/bin:/bin:/usr/bin:/usr/local/bin Or: PATH=/usr/local/mysql/bin:/bin:/usr/bin:/usr/local/bin export PATH Under Windows, check for a line that sets the PATH variable in your AUTOEXEC.BAT file. It might look like this: PATH=C:WINDOWS;C:WINDOWSCOMMAND Or like this: SET PATH=C:WINDOWS;C:WINDOWSCOMMAND
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
1.9 Setting Environment Variables 1.9.1 Problem You need to modify your operating environment, for example, to change your shell’s PATH setting. 1.9.2 Solution Edit the appropriate shell startup file. Under Windows NT-based systems, another alternative is to use the System control panel. 1.9.3 Discussion The shell or command interpreter you use to run programs from the command-line prompt includes an environment in which you can store variable values. Some of these variables are used by the shell itself. For example, it uses PATH to determine which directories to look in for programs such as mysql. Other variables are used by other programs (such as PERL5LIB, which tells Perl where to look for library files used by Perl scripts). Your shell determines the syntax used to set environment variables, as well as the startup file in which to place the settings. Typical startup files for various shells are shown in the following table. If you’ve never looked through your shell’s startup files, it’s a good idea to do so to familiarize yourself with their contents. Shell Possible startup files csh, tcsh .login, .cshrc, .tcshrc sh, bash, ksh .profile .bash_profile, .bash_login, .bashrc DOS prompt C:AUTOEXEC.BAT The following examples show how to set the PATH variable so that it includes the directory where the mysql program is installed. The examples assume there is an existing PATH setting in one of your startup files. If you have no PATH setting currently, simply add the appropriate line or lines to one of the files. If you’re reading this section because you’ve been referred here from another chapter, you’ll probably be more interested in changing some variable other than PATH. The instructions are similar because you use the same syntax. The PATH variable lists the pathnames for one or more directories. If an environment variable’s value consists of multiple pathnames, it’s conventional under Unix to separate them using the colon character (:). Under Windows, pathnames may contain colons, so the separator is the semicolon character ( .
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services