5.2 Tutorial


Following the Swiss Army knife theory (20 percent of the functions give you 80 percent of the utility), a few SQL commands go a long way to facilitate learning MySQL/Perl/DBI.

To illustrate these, we create a simple database containing information about some (fictional) people. Eventually, we'll show how to enter this information from a form on the Web (see Chapter 7), but for now we interface with SQL directly.

First, try to make a connection to our MySQL server as the root MySQL user :

 $  mysql -u root  

N

The MySQL root user is different from the Linux root user. The MySQL root user is used to administer the MySQL server only.


If you see the following output:

 ERROR 2002: Can't connect to local MySQL server through socket  /var/lib/mysql/mysql.sock(2) 

it likely means the MySQL server is not running. If your system is set up securely, it shouldn't be running, because you had no reason, before now, for it to be running. Use chkconfig as root to make sure it starts the next time the machine boots, and then start it by hand as follows :

 #  chkconfig mysqld on  #  /etc/init.d/mysqld start  

Now you should be able to connect ( not logged in as the Linux root user):

 $  mysql -u root  

If not, see the MySQL log file at /var/log/mysqld.log . If so, you'll see a welcome message and the MySQL prompt:

 Welcome to the MySQL monitor. Commands end with ; or \g.  Your MySQL connection id is 3 to server version: 3.23.36  Type help; or \h for help. Type \c to clear the buffer  mysql> 

As suggested, enter help; at the prompt. A list of MySQL commands (not to be confused with SQL commands) will be displayed. These allow you to work with the MySQL server. For grins, enter status; to see the status of the server.

To illustrate these commands, we will create a database called people that contains information about people and their ages.

5.2.1 The SHOW DATABASES and CREATE DATABASE Commands

First, we need to create the new database. Check the current databases to make sure a database of that name doesn't already exist; then create the new one, and verify the existence of the new database:

 mysql>  SHOW DATABASES;  +----------+   Database   +----------+   mysql       test       +----------+  2 rows in set (0.00 sec)  mysql>  CREATE DATABASE people;  Query OK, 1 row affected (0.00 sec)  mysql>  SHOW DATABASES;  +----------+   Database   +----------+   mysql       people      test       +----------+  3 rows in set (0.00 sec) 

SQL commands and subcommands (in the previous example, CREATE is a command; DATABASE is its subcommand) are case-insensitive. The name of the database (and table and field) are case sensitive. It's a matter of style whether one uses uppercase or lowercase, but traditionally the SQL commands are distinguished by uppercase.

One way to think of a database is as a container for related tables . A table is a collection of rows, each row holding data for one record, each record containing chunks of information called fields .

5.2.2 The USE Command

Before anything can be done with the newly created database, MySQL has to connect to it. That's done with the USE command:

 mysql>  USE people;  

5.2.3 The CREATE TABLE and SHOW TABLES Commands

Each table within the database must be defined and created. This is done with the CREATE TABLE command.

Create a table named age_information to contain an individual's first name, last name, and age. MySQL needs to know what kind of data can be stored in these fields. In this case, the first name and the last name are character strings of up to 20 characters each, and the age is an integer:

 mysql>  CREATE TABLE age_information (  ->  lastname   CHAR(20),  ->  firstname  CHAR(20),  ->  age        INT  ->  );  Query OK, 0 rows affected (0.00 sec) 

It appears that the table was created properly (it says OK after all), but this can be checked by executing the SHOW TABLES command. If an error is made, the table can be removed with DROP TABLE .

When a database in MySQL is created, a directory is created with the same name as the database ( people , in this example):

 #  ls -l /var/lib/mysql  total 3  drwx------    2 mysql    mysql        1024 Dec 12 15:28 mysql  srwxrwxrwx    1 mysql    mysql           0 Dec 13 07:19 mysql.sock  drwx------    2 mysql    mysql        1024 Dec 13 07:24 people  drwx------    2 mysql    mysql        1024 Dec 12 15:28 test 

Within that directory, each table is implemented with three files:

 #  ls -l /var/lib/mysql/people  total 10  -rw-rw----    1 mysql    mysql        8618 Dec 13 07:24 age_information.frm  -rw-rw----    1 mysql    mysql           0 Dec 13 07:24 age_information.MYD  -rw-rw----    1 mysql    mysql        1024 Dec 13 07:24 age_information.MYI  mysql>  SHOW TABLES;  +------------------+   Tables_in_people   +------------------+   age_information    +------------------+  1 row in set (0.00 sec) 

This example shows two MySQL datatypes: character strings and integers. Other MySQL data types include several types of integers (for a complete discussion of MySQL's data types, see www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Column_types):

TINYINT

-128 to 127 (signed) or 0 to 255 (unsigned)

SMALLINT

-32768 to 32767 (signed) or 0 to 65535 (unsigned)

MEDIUMINT

-8388608 to 8388607 (signed) or 0 to 16777215 (unsigned)

INTEGER (same as INT )

-2147483648 to 2147483647 (signed) or 0 to 4294967295 (unsigned)

BIGINT

-9223372036854775808 to 9223372036854775807 (signed) or 0 to 18446744073709551615 (unsigned)

and floating points:

FLOAT

DOUBLE

REAL (same as DOUBLE )

DECIMAL

NUMERIC (same as DECIMAL )

There are several data types to represent a date:

DATE

YYYY-MM-DD

DATETIME

YYYY-MM-DD HH:MM:SS

TIMESTAMP

YYYYMMDDHHMMSS or YYMMDDHHMMSS or YYYYMMDD or YYMMDD

TIME

HH:MM:SS

YEAR

YYYY or YY

The table age_information used the CHAR character data type. The following are the other character data types. Several have BLOB in their name ” a BLOB is a Binary Large OBject that can hold a variable amount of data. The types with TEXT in their name are just like their corresponding BLOB s except when matching is involved: The BLOB s are case-sensitive, and the TEXT s are case-insensitive.

VARCHAR

variable-length string up to 255 characters

TINYBLOB

TINYTEXT

maximum length 255 characters

BLOB

TEXT

maximum length 65535 characters

MEDIUMBLOB

MEDIUMTEXT

maximum length 16777215 characters

LONGBLOB

LONGTEXT

maximum length 4294967295 characters

5.2.4 The DESCRIBE Command

The DESCRIBE command gives information about the fields in a table. The fields created earlier ” lastname , firstname , and age ”appear to have been created correctly.

 mysql>  DESCRIBE age_information;  +-----------+----------+------+-----+---------+-------+   Field      Type      Null  Key  Default  Extra   +-----------+----------+------+-----+---------+-------+   lastname   char(20)  YES        NULL              firstname  char(20)  YES        NULL              age        int(11)   YES        NULL             +-----------+----------+------+-----+---------+-------+  3 rows in set (0.00 sec) 

The command SHOW COLUMNS FROM age_information; gives the same information as DESCRIBE age_information; but DESCRIBE involves less typing. (If you're really trying to save keystrokes, you could abbreviate DESCRIBE as DESC .)

5.2.5 The INSERT Command

For the table to be useful, we need to add information to it. We do so with the INSERT command:

 mysql>  INSERT INTO age_information  ->  (lastname, firstname, age)  ->  VALUES (Wall , Larry , 46);  Query OK, 1 row affected (0.00 sec) 

The syntax of the command is INSERT INTO , followed by the table in which to insert, a list within parentheses of the fields into which information is to be inserted, and the qualifier VALUES followed by the list of values in parentheses in the same order as the respective fields. [1]

[1] We did extensive research to determine that none of the names used in this chapter belong to real people.

5.2.6 The SELECT Command

SELECT selects records from the database. When this command is executed from the command line, MySQL prints all the records that match the query. The simplest use of SELECT is shown in this example:

 mysql>  SELECT * FROM age_information;  +----------+-----------+------+   lastname  firstname  age    +----------+-----------+------+   Wall      Larry        46   +----------+-----------+------+  1 row in set (0.00 sec) 

The * means "show values for all fields in the table"; FROM specifies the table from which to extract the information.

The previous output shows that the record for Larry Wall was added successfully. To experiment with the SELECT command, we need to add a few more records, just to make things interesting:

 mysql>  INSERT INTO age_information  ->  (lastname, firstname, age)  ->  VALUES (   Torvalds   ,   Linus   , 31);  Query OK, 1 row affected (0.00 sec)  mysql>  INSERT INTO age_information  ->  (lastname, firstname, age)  ->  VALUES (   Raymond   ,   Eric   , 40);  Query OK, 1 row affected (0.00 sec)  mysql>  SELECT * FROM age_information;  +----------+-----------+------+   lastname  firstname  age    +----------+-----------+------+   Wall      Larry        46    Torvalds  Linus        31    Raymond   Eric         40   +----------+-----------+------+  3 rows in set (0.00 sec) 

There are many ways to use the SELECT command ”it's very flexible. First, sort the table based on lastname :

 mysql>  SELECT * FROM age_information  ->  ORDER BY lastname;  +----------+-----------+------+   lastname  firstname  age    +----------+-----------+------+   Raymond   Eric         40    Torvalds  Linus        31    Wall      Larry        46   +----------+-----------+------+  3 rows in set (0.00 sec) 

Now show only the lastname field, sorted by lastname :

 mysql>  SELECT lastname FROM age_information  ->  ORDER BY lastname;  +----------+   lastname   +----------+   Raymond     Torvalds    Wall       +----------+  3 rows in set (0.00 sec) 

Show the ages in descending order:

 mysql>  SELECT age FROM age_information ORDER BY age DESC;  +------+   age    +------+     46      40      31   +------+  3 rows in set(0.00 sec) 

Show all the last names for those who are older than 35:

 mysql>  SELECT lastname FROM age_information WHERE age > 35;  +----------+   lastname   +----------+   Wall        Raymond    +----------+  2 rows in set (0.00 sec) 

Do the same, but sort by lastname :

 mysql>  SELECT lastname FROM age_information  ->  WHERE age > 35 ORDER BY lastname;  +----------+   lastname   +----------+   Raymond     Wall       +----------+  2 rows in set (0.00 sec) 

5.2.7 The UPDATE Command

Since the database is about people, information in it can change (people are unpredictable like that). For instance, although a person's birthday is static, their age changes. To change the value in an existing record, we can UPDATE the table. Let's say the fictional Larry Wall has turned 47:

 mysql>  SELECT * FROM age_information;  +----------+-----------+------+   lastname  firstname  age    +----------+-----------+------+   Wall      Larry        46    Torvalds  Linus        31    Raymond   Eric         40   +----------+-----------+------+  3 rows in set (0.00 sec)  mysql>  UPDATE age_information SET age = 47  ->  WHERE lastname =   Wall   ;  Query OK, 1 row affected (0.00 sec)  Rows matched: 1  Changed: 1  Warnings: 0  mysql>  SELECT * FROM age_information;  +----------+-----------+------+   lastname  firstname  age    +----------+-----------+------+   Wall      Larry        47    Torvalds  Linus        31    Raymond   Eric         40   +----------+-----------+------+  3 rows in set (0.00 sec) 

Be sure to use that WHERE clause; otherwise , if we had only entered UPDATE age_information SET age = 47 , all the records in the database would have been given the age of 47!

Although this might be good news for some people in these records (how often have the old-timers said "Oh, to be 47 years old again" ”OK, probably not), it might be shocking news to others.

This method works, but it requires the database to know that Larry is 46, turning 47. Instead of keeping track of this, for Larry's next birthday we simply increment his age:

 mysql>  SELECT * FROM age_information;  +----------+-----------+------+   lastname  firstname  age    +----------+-----------+------+   Wall      Larry        47    Torvalds  Linus        31    Raymond   Eric         40   +----------+-----------+------+  3 rows in set(0.00 sec)  mysql>  UPDATE age_information SET age = age + 1  ->  WHERE lastname =   Wall   ;  Query OK, 1 row affected (0.00 sec)  Rows matched: 1  Changed: 1  Warnings: 0  mysql>  SELECT * FROM age_information;  +----------+-----------+------+   lastname  firstname  age    +----------+-----------+------+   Wall      Larry        48    Torvalds  Linus        31    Raymond   Eric         40   +----------+-----------+------+  3 rows in set(0.00 sec) 

5.2.8 The DELETE Command

Sometimes we need to delete a record from the table (don't assume the worst ”perhaps the person just asked to be removed from a mailing list, which was opt-in in the first place, of course). This is done with the DELETE command:

 mysql>  DELETE FROM age_information WHERE lastname = Raymond;  Query OK, 1 row affected (0.00 sec)  mysql>  SELECT * FROM age_information;  +----------+-----------+------+   lastname  firstname  age    +----------+-----------+------+   Wall      Larry        48    Torvalds  Linus        31   +----------+-----------+------+  2 rows in set (0.00 sec) 

Eric is in good company here, so put him back:

 mysql>  INSERT INTO age_information  ->  (lastname, firstname, age)  ->  VALUES (   Raymond   ,   Eric   , 40);  Query OK, 1 row affected (0.00 sec)  mysql>  SELECT * FROM age_information;  +----------+-----------+------+   lastname  firstname  age    +----------+-----------+------+   Wall      Larry        48    Torvalds  Linus        31    Raymond   Eric         40   +----------+-----------+------+  3 rows in set (0.00 sec) 

5.2.9 Some Administrative Details

All these examples have been executed as the root MySQL user, which, as you might imagine, is not optimal from a security standpoint. A better practice is to create a MySQL user who can create and update tables as needed.

First, as a security measure, change the MySQL root password when logging in to the server:

 #  mysqladmin password IAmGod  

Now when mysql executes, a password must be provided using the -p switch. Here is what would happen if we forgot the -p :

 $  mysql -u root  ERROR 1045: Access denied for user: root@localhost (Using password: NO) 

Try again using -p . When prompted for the password, enter the one given previously:

Recall that the MySQL user is not the same as a Linux user. The mysqladmin command changes the password for the MySQL user only, not the Linux user. For security reasons, we suggest that the MySQL password never be the same as the password used to log in to the Linux machine. Also, the password IAmGod , which is clever, is a bad password for many reasons, including the fact that it is used as an example in this book. For a discussion on what makes a password good or bad, we suggest you read Hacking Linux Exposed [Hatch+ 02].

 $  mysql -u root -p  Enter password:  Welcome to the MySQL monitor. Commands end with ; or \g.  Your MySQL connection id is 15 to server version: 3.23.36  Type help; or  \h for help. Type \c to clear the buffer  mysql> 

Doing all the SQL queries in the people database as the MySQL root user is a Bad Idea (see HLE if you want proof of this). So let's create a new user. This involves modifying the database named mysql , which contains all the administrative information for the MySQL server, so first we use the mysql database and then grant privileges for a new user:

 mysql>  USE mysql;  Reading table information for completion of table and column names  You can turn off this feature to get a quicker startup with -A  Database changed  mysql>  GRANT SELECT,INSERT,UPDATE,DELETE  ->  ON people.*  ->  TO apache@localhost  ->  IDENTIFIED BY LampIsCool;  Query OK, 0 rows affected (0.00 sec) 

The user apache (the same user that runs the webserver ) is being granted the ability to do most everything within the database, including being able to delete entries in tables within the people database. However, apache cannot delete the people database, only entries within the tables in the database. The user apache can access the people database from localhost only (instead of being able to log in over the network from another machine).

The IDENTIFIED BY clause in the SQL command sets the apache user's password to LampIsCool . Setting the password is necessary only the first time permissions are granted for this user ”later, when the apache user is given permissions in other databases, the password doesn't need to be reset.

To verify that these changes were made, log in as apache :

 $  mysql -u apache -p  Enter password:  Welcome to the MySQL monitor. Commands end with ; or \g.  Your MySQL connection id is 27 to server version: 3.23.36  Type help; or \h for help. Type \c to clear the buffer  mysql>  USE people  Reading table information for completion of table and column names  You can turn off this feature to get a quicker startup with -A  Database changed  mysql>  SHOW TABLES;  +------------------+   Tables_in_people   +------------------+   age_information    +------------------+  1 row in set (0.00 sec)  mysql>  SELECT * FROM age_information;  +----------+-----------+------+   lastname  firstname  age    +----------+-----------+------+   Wall      Larry        48    Torvalds  Linus        31    Raymond   Eric         40   +----------+-----------+------+  3 rows in set (0.00 sec) 

5.2.10 Summary

As discussed, these commands are enough to do basic things with MySQL:

SHOW DATABASES

CREATE DATABASE

USE

CREATE TABLE

SHOW TABLES

DESCRIBE

INSERT

SELECT

UPDATE

DELETE

GRANT



Open Source Development with Lamp
Open Source Development with LAMP: Using Linux, Apache, MySQL, Perl, and PHP
ISBN: 020177061X
EAN: 2147483647
Year: 2002
Pages: 136

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net