Basic MySQL Command-line info

The MySQL command-line interface gives interactive feedback, and has a few very nice features. Using the command-line tool can save a lot of time, especially for things that you won't do very often. It's also a fantastic environment to test new querries for correctness. Learning the command-line interface is not required, but it is often extremely helpful, and is highly recommended.

MySQL had and has some peculiar restrictions about naming databases, tables, and columns. To prevent problems now and in the future, we set the name of your database to be your domain name but with all dash '-' and dot '.' characters replaced by underscores.

Each database is set up with a single user having the same name as your FTP/Telnet login identity. The password will be what you specified when the database was set up. It is independant of your unix login, and although it can be the same as your login password, we recommend using something distinct from any/all other passwords you have.

No tables are created for you, so your first task should be to do that. The easiest way to get started will be to use the mysql command-line tool create one. To use the command-line interface, telnet to your server here, log in, and at the unix prompt enter:

mysql -u youruser -h -p yourdomain_com
This says that you want to run the command "mysql" with some options to define the user (-u) as your userid, the host (-h) as your domain name, that you want to enter a password (-p) and that the database you want to connect to is "yourdomain_com"

You will see a prompt for your password, which of course you must enter correctly to proceed. Once you have successfully logged in, you will see a welcome type banner, and the mysql command prompt, where you may enter commands: 'mysql>'

Every command must be terminated with a semicolon ';' or the two-character string '\g' before it is executed. You may also use the "go" command on a new line. Commands may span more than one line, which is why such a convention is necessary. For very complex querries, splitting the command into multiple lines makes life a lot easier. Since creating tables often results in fairly lengthy commands, we'll use multi-line syntax in these examples. When continuing a command across multiple lines, the prompt changes to an arrow '->'

This example will create a new table, named "movies" with the following fields:
name, a fixed-length text field, limited to 100 characters.
release, of type Year. studio, a fixed-length text field, limited to 50 characters.
review, a variable length text field, limited to 255 characters.
gross, an integral value.
mysql> create table movies (
    -> name char(100),
    -> release year,
    -> studio char(50),
    -> review varchar(50),
    -> gross int);
Query OK, 0 rows affected (0.03 sec)
Once that is done, you can verify that MySQL did what you asked by using the "describe" command.

mysql> describe movies;
| Field   | Type         | Null | Key | Default | Extra |
| name    | varchar(100) | YES  |     | NULL    |       |
| release | year(4)      | YES  |     | NULL    |       |
| studio  | varchar(50)  | YES  |     | NULL    |       |
| review  | varchar(50)  | YES  |     | NULL    |       |
| gross   | int(11)      | YES  |     | NULL    |       |
5 rows in set (0.00 sec)

All materials, including text and images, appearing in this site are © Addy & Associates. Any use without the express written permission of Addy & Associates is strictly prohibited.