SQL command
To execute an SQL statement at the mysql> prompt, type it in, add a semicolon (;) at the end to signify the end of the statement, and press Enter. The semicolon is the most common terminator, but you can also use \g (“go”) or \G as a synonym for the semicolon.
To execute a statement directly from the command line, specify it using the -e (or –execute) option. This is useful for “one-liners.” For example, to count the rows in the
limbs table, use this command:
% mysql -e "SELECT COUNT(*) FROM table_name" DB_name
If you want to execute multiple command, seperate them with semicolon.
Batch mode is convenient for executing a set of statements on repeated occasions without entering them manually each time. Batch mode makes it easy to set up cron jobs that run with no user intervention. SQL scripts also are useful for distributing statements to other people.
E.g a file artist.sql’s content is :
DROP TABLE IF EXISTS artist;
#@ _CREATE_TABLE_ARTIST_
CREATE TABLE artist
(
a_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # artist ID
name VARCHAR(30) NOT NULL, # artist name
PRIMARY KEY (a_id),
UNIQUE (name)
);
#@ _CREATE_TABLE_ARTIST_INSERT INTO artist (name) VALUES
(‘Da Vinci’),
(‘Monet’),
(‘Van Gogh’),
(‘Renoir’)
;SELECT * FROM artist;
DROP TABLE IF EXISTS painting;
#@ _CREATE_TABLE_PAINTING_
CREATE TABLE painting
(
a_id INT UNSIGNED NOT NULL, # artist ID
p_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # painting ID
title VARCHAR(100) NOT NULL, # title of painting
state VARCHAR(2) NOT NULL, # state where purchased
price INT UNSIGNED, # purchase price (dollars)
INDEX (a_id),
PRIMARY KEY (p_id)
);
#@ _CREATE_TABLE_PAINTING_# Use INSERT INTO … SELECT form to get proper artist ID corresponding
# to artist nameINSERT INTO painting (a_id,title,state,price)
SELECT a_id, ‘The Last Supper’, ‘IN’, 34
FROM artist WHERE name = ‘Da Vinci’;
INSERT INTO painting (a_id,title,state,price)
SELECT a_id, ‘Mona Lisa’, ‘MI’, 87
FROM artist WHERE name = ‘Da Vinci’;INSERT INTO painting (a_id,title,state,price)
SELECT a_id, ‘Starry Night’, ‘KY’, 48
FROM artist WHERE name = ‘Van Gogh’;
INSERT INTO painting (a_id,title,state,price)
SELECT a_id, ‘The Potato Eaters’, ‘KY’, 67
FROM artist WHERE name = ‘Van Gogh’;INSERT INTO painting (a_id,title,state,price)
SELECT a_id, ‘Les Deux Soeurs’, ‘NE’, 64
FROM artist WHERE name = ‘Renoir’;SELECT * FROM painting;
To feed this script to the database:
% mysql testdb < artist.sql
Alternatively, to read a file of SQL statements from within a mysql session, use a source filename command (or \. filename, which is synonymous):
mysql> use testdb; Database changed mysql> source artist.sql; mysql> \. artist.sql;
A file to be read by mysql need not be written by hand; it could be program generated. For example, the mysqldump utility generates database backups by writing a set of SQL statements that re-create the database. To reload mysqldump output, feed it to mysql. For example,:
% mysqldump testdb > dump.sql % mysql -h other-host.example.com testdb < dump.sql
Pip command
onnect the two programs directly with a pipe, avoiding the need for an intermediary file:
% mysqldump testdb | mysql -h other-host.example.com testdb
Output
Tabular(boxed) format:
% mysql mysql> SELECT * FROM limbs WHERE legs=0; +------------+------+------+ | thing | legs | arms | +------------+------+------+ | squid | 0 | 10 | | fish | 0 | 0 | | phonograph | 0 | 1 | +------------+------+------+ 3 rows in set (0.00 sec)
If you want to use batch (tab-delimited) output in interactive mode. To do this, use -B or –batch.
Batch (Tabdelimited) output
For noninteractive use (when the input or output is redirected), mysql writes tabdelimited output:
% echo "SELECT * FROM limbs WHERE legs=0" | mysql testdb thing legs arms squid 0 10 fish 0 0 phonograph 0 1
This may be not so readable when the data goes big. If you want to show in tabular output, use the -t (or –table) option to produce more readable tabular output.
Producing HTML or XML output
mysql generates an HTML table from each query result set if you use the -H (or –html) option.
For example, on Mac OS X, do this:
% mysql -H -e "SELECT * FROM limbs WHERE legs=0" testdb > limbs.html % open -a safari limbs.html
To generate an XML document instead of HTML, use the -X (or –xml) option:
% mysql -X -e "SELECT * FROM limbs WHERE legs=0" testdb
You can reformat XML to suit a variety of purposes by running it through XSLT transforms.
Use the transform like this:
% mysql -X -e "SELECT * FROM limbs WHERE legs=0" testdb \ | xsltproc mysql-xml.xsl - Query: SELECT * FROM limbs WHERE legs=0 Result set: squid, 0, 10 fish, 0, 0 phonograph, 0, 1
To create output that contains only data values, suppress the header row with the –skip-column-names option, or Specifying the “silent” option (-s or –silent) twice -ss.
FuMBP:~ Frank$ mysql -e "SELECT arms FROM limbs" test -u root -p Enter password: +------+ | arms | +------+ | 2 | | 0 | | 10 | | 0 | | 0 | | 0 | | 2 | | 1 | | 0 | | 2 | | NULL | +------+ FuMBP:~ Frank$ mysql -ss -e "SELECT arms FROM limbs" test -u root -p Enter password: 2 0 10 0 0 0 2 1 0 2 NULL
Specify the out put column deliminator
In noninteractive mode, mysql separates output columns by tabs and there is no option for specifying the output delimiter. Suppose that you want to create an output file for use by a program that expects values to be separated by colon characters (:) rather than tabs.
Under Unix, you can convert tabs to arbitrary delimiters by using a utility such as tr or sed. Any of the following commands change tabs to colons (TAB indicates where you
type a tab character):
% mysql testdb < inputfile | sed -e "s/TAB/:/g" > outputfile % mysql testdb < inputfile | tr "TAB" ":" > outputfile % mysql testdb < inputfile | tr "\011" ":" > outputfile
Format as CSV
This is useful for producing output in something like commaseparated values (CSV) format, which requires three substitutions:
1. Escape any quote characters that appear in the data by doubling them, so that when you use the resulting CSV file, they won’t be interpreted as column delimiters.
2. Change the tabs to commas.
3. Surround column values with quotes.
sed permits all three substitutions to be performed in a single command line:
% mysql cookbook < inputfile \ | sed -e 's/"/""/g' -e 's/TAB/","/g' -e 's/^/"/' -e 's/$/"/' > outputfile
That’s cryptic, to say the least. You can achieve the same result with other languages that may be easier to read. Here’s a short Perl script that does the same thing as the sed
command (it converts tab-delimited input to CSV output), and includes comments to document how it works:
#!/usr/bin/perl
# csv.pl: convert tab-delimited input to comma-separated values output
while (<>) # read next input line
{
s/"/""/g; # double quotes within column values
s/\t/","/g; # put "," between column values
s/^/"/; # add " before the first value
s/$/"/; # add " after the last value
print; # print the result
}
If you name the script csv.pl, use it like this:
% mysql cookbook < inputfile | perl csv.pl > outputfile
Control the verbose
To tell mysql to be more verbose, use -v or –verbose, specifying the option multiple times for increasing verbosity. Try the following commands to see how the output differs:
% echo "SELECT NOW()" | mysql % echo "SELECT NOW()" | mysql -v % echo "SELECT NOW()" | mysql -vv % echo "SELECT NOW()" | mysql -vvv
The counterparts of -v and –verbose are -s and –silent, which also can be used multiple times for increased effect.
User variables
To save a value returned by a SELECT statement, assign it to a user-defined variable. This enables you to refer to it in other statements later in the same session (but not across sessions). To assign a value to a user variable within a SELECT statement, use @var_name := value syntax.
Another use for a variable is to save the result from LAST_INSERT_ID() after creating a new row in a table that has an AUTO_INCREMENT column:
mysql> SELECT @last_id := LAST_INSERT_ID();
LAST_INSERT_ID() returns the most recent AUTO_INCREMENT value.
User variables hold single values. If a statement returns multiple rows, the value from the last row is assigned:
mysql> SELECT @name := thing FROM limbs WHERE legs = 0; +----------------+ | @name := thing | +----------------+ | squid | | 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, its value is NULL.
mysql> SELECT @name2 := thing FROM limbs WHERE legs < 0; Empty set (0.00 sec) mysql> SELECT @name2; +--------+ | @name2 | +------+ | NULL | +------+
To set a variable explicitly to a particular value, use a SET statement. SET syntax can use either := or = as the assignment operator:
mysql> SET @sum = 4 + 7; mysql> SELECT @sum; +------+ | @sum | +------+ | 11 | +------+
You can assign a SELECT result to a variable, provided that you write it as a scalar subquery (a query within parentheses that returns a single value):
mysql> SET @max_limbs = (SELECT MAX(arms+legs) FROM limbs);
Scope of the User variables
SET is also used to assign values to stored program parameters and local variables, and to system variables.
User variables can appear only where expressions are permitted, not where constants or literal identifiers must be provided. It’s tempting to attempt to use variables for such things as table names, but it doesn’t work. For example, if you try to generate a temporary table name using a variable as follows, it fails:
mysql> SET @tbl_name = CONCAT('tmp_tbl_', CONNECTION_ID());
mysql> CREATE TABLE @tbl_name (int_col INT);
ERROR 1064: You have an error in your SQL syntax near '@tbl_name (int_col INT)'
Delete all the duplicate rows but keep one:
The table name is profile, name is the column name.
mysql> SELECT * FROM profile; +----+------------+------------+-------+-----------------------+------+ | id | name | birth | color | foods | cats | +----+------------+------------+-------+-----------------------+------+ | 1 | Sybil | 1970-04-13 | black | lutefisk,fadge,pizza | 1 | | 2 | Nancy | 1969-09-30 | white | burrito,curry,eggroll | 3 | | 3 | Ralph | 1973-11-02 | red | eggroll,pizza | 4 | | 4 | Lothair | 1963-07-04 | blue | burrito,curry | 5 | | 9 | Gary | NULL | blue | NULL | NULL | | 10 | De'Mont | NULL | blue | NULL | NULL | | 14 | De'Frank | 1973-01-12 | NULL | eggroll | 4 | | 18 | De'Frankfu | 1973-01-12 | NULL | eggroll | 4 | | 24 | De'Frankfu | 1973-01-12 | NULL | eggroll | 4 | | 25 | De'Frankfu | 1973-01-12 | NULL | eggroll | 4 | | 26 | De'Frankfu | 1973-01-12 | NULL | eggroll | 4 | +----+------------+------------+-------+-----------------------+------+
DELETE n1 FROM profile n1, profile n2 WHERE n1.id > n2.id AND n1.name = n2.name