|
Below are examples of MySQL select statements and
other commands I've found useful (with version 3.23.49-3).
It is not a tutorial on basic SQL;
I would recommend the SQL Language
section of the MySQL manual for that (see link below).
These commands are used from within the MySQL
client (e.g. after typing "mysql database_name" in a linux
terminal window).
#adding a unique index makes sure there
#are not two similar entries. For example, the below would
#prevent more than one matching folder/filename combination
mysql> alter table webpages add unique index path (name,folder);
#you've already created a table, but want to add a column
mysql>alter table table_name add column column_name column_type;
#a specific example might be
mysql>alter table pages add column avpd decimal(4,2);
#you've already created a table, but want to change
#a column's definition
mysql>alter table table_name change \
old_name new_name new_column_definition;
#selection statements can be limited by date fields.
mysql>select id from photos \
where taken>'2002-01-01' order by taken;
#in tables with an auto_increment key, the counter for
#new inserts can be reset to the highest current value:
mysql>alter table table_name auto_increment=1;
Inserting quoted text using the perl DBI
When text is inserted into a database it must be
enclosed in quotes. The text often contains quotes itself,
which must be escaped with a backslash. The text may
have unexpected characters, such as backslashes or
control characters, which must also be escaped.
It is sometimes difficult to properly escape all the necessary
characters so the database server will recognize them.
Fortunately, this can be handled
by allowing the perl DBI to do it for you.
The perl DBI allows you to use questionmark placeholders
in an SQL statement. When the statement is executed, variables
can be passes as arguments. The are inserted where the
questionmark was, and get all the necessary escapes.
For example, the prepare and execute methods are used below:
use DBI;
$dbh = DBI->connect('DBI:mysql:database', 'user', 'pass');
$string = "Cra\b\nzy, U{{{[[:escaped;\";'^&% char(tt)erz";
$sth = $dbh->prepare("INSERT INTO templates(html) VALUES (?)");
$sth->execute($string);
$dbh->disconnedt
|
