Some Information about MySQL

What is MySQL?

MySQL is a freely available database program which you can download and install on your computer. It is often mentioned in the same breath as PHP, since the two seem to have developed a symbiotic relationship and a great deal of effort has gone into making the two technologies work together. There are, however, other means of access to a MySQL database, including a command-line interface, and many other programming languages, including Perl and Java.

Also, when you are accessing MySQL via the web you are once again in the context of a client-server architecture ... issuing requests (or "queries") to the MySQL system over the web and receiving the replies that are sent back to you, also over the web.

Michael "Monty" Widenius was the principal developer of MySQL, and its development started in the mid-1990s, about the same time as PHP. Major milestones for MySQL were the following:

There are many commercial relational database systems available as well, many of which will set you back a few dollars to use. These commercial alternatives include Oracle, Postgres and Sybase.

Command-Line Access

To access MySQL in command-line mode on a multi-user system, you need to have a username and password. With these in hand you can enter, at the command prompt, the command

mysql -p -u uxx

which should make your uxx database immediately available, or possibly even just

mysql -p

after which you will have to execute the use uxx to get access to your uxx database.

Once you enter your password you will get the mysql prompt, which looks like this: 

mysql>

You are now ready to enter command-line commands.

Important Note

To use the LOAD command to load a local file of data (comma separated or tab separated, for example) you should log in with the following command (and use the second version if you want to see all the warnings that you may get when executing commands at the command line): 

mysql -p -u uxx --local-infile uxx
mysql -p -u uxx --local-infile uxx --show-warnings

Another Important Note

If you would like to see, by default, the details of any warnings you get, log in with the following qualifier (short form shown):

mysql -p --show-warnings

Types of MySQL Commands

The following is an alphabetical listing of what are probably the most frequently used MySQL commands, along with a general indication of what each one does. This is an alphabetical listing, but in terms of frequency of use, the most often used commands will likely be CREATE, INSERT, SELECT and SHOW.

Some MySQL Commands in More Detail

Since a database will normally consist of several (and perhaps many) different "tables" containing the information of interest, many of the most commonly used MySQL commands deal with the creation, modification, manipulation, and deletion, as well as retrieval and display of information in tables. These are SQL commands, commands in the Structured Query Language used to communicate with MySQL and provide instructions for dealing with the information in your database.

HELP;
Displays a list of commands for MySQL, but not for SQL in general.
EXIT;
Logs out of MySQL.
QUIT;
Same as EXIT.
CREATE DATABASE database_name;
[This command may not be allowed on your account.]
Creates a new database, but in a multi-user system the system administrator may have assigned you a single database, and limited your access to that one database, and if this is the case, you will not be able to use this command. This just means that all your tables will be in the one database to which you have been assigned. You can still have several different "conceptual databases", even though they will all reside in the same physical database.
SHOW databases;
Shows the databases to which you have access.
USE database_name;
Selects the database to which all subsequent commands will apply until another such command is given to change the database. Even if you only have a single database, you must give this command at the start of a session before giving any other commands.
SHOW TABLES;
Shows a list of all the tables in the current database.
CREATE TABLE table_name(column_name1 datatype1 constraints1[, ...]);
Creates a new table.
DESCRIBE table_name;
Displays a table showing the structure of the named table.
INSERT INTO table_name(column_name1[, ...]) VALUES (value1[, ...]);
Inserts values into a row of a table. The values must, of course, must match in order and type the structure of the table as shown by the DESCRIBE command.
LOAD DATA LOCAL INFILE 'name.ext' INTO TABLE table_name;
Loads the data in a textfile into a database table, provided the data is properly formatted (the TAB character is assumed to be the field separator in this case).
LOAD DATA LOCAL INFILE 'name.ext' INTO TABLE table_name FIELDS TERMINATED BY ',';
Loads the data in a textfile into a database table, provided the data is properly formatted (in particular, has its fields terminated by a delimiter character, represented here by ',').
SELECT * FROM table_name;
Displays all the data in a table (but the format will be really ugly if there is enough information in each row of the table to cause "line wrapping").
SELECT * FROM table_name \G (Note the absence of a semi-colon!)
Same as the above command, but with a much nicer format. (Also, you can give the "pager more" command at the mysql prompt to cause the output display to be shown a screenful at a time).
SELECT column_name1 [, ...] FROM table_name1 [, ...] [WHERE condition];
Finds and displays information from one or more tables, possibly based on a user-supplied condition.
SELECT DATABASE();
Selects and returns the current database.
SELECT NOW(), USER(), VERSION();
Selects and returns the current date and time, the current user, and the current version of MySQL.
UPDATE table_name SET column_name1=value1 [, ...] WHERE column_name=value;
Updates values in a row of a table.
RENAME TABLE old_name TO new_name;
Renames a table.
ALTER TABLE table_name ADD column_name [constraints];
Alters a table structure by adding a new column.
ALTER TABLE table_name RENAME AS new_table_name;
Alters a table by changing its name.
ALTER TABLE table_name MODIFY column_name new_column_type [constraints];
Alters a table by changing the data type of one of its columns.
ALTER TABLE table_name CHANGE column_name new_column_name new_column_type [constraints];
Alters a table by changing the name and data type of one of its columns.
ALTER TABLE table_name AUTO_INCREMENT = start_value;
Sets the start value for the autoincrement column.
DELETE FROM table_name;
[Be careful with this command!]
Deletes all rows (all data) from table_name.
DELETE FROM table_name WHERE column_name=value;
Deletes a row of values from a table.
DROP (TABLE | DATABASE) [IF EXISTS] name;
[Be careful with this command!]
Deletes an entire table or database.
SOURCE filename.sql;
Loads in and executes a file of MySQL commands.

Some MySQL Datatypes

MySQL provides a large selection of data types from which to choose when you are deciding what types the values in your table columns should have. Each column of each table is assigned a particular data type when the table is created. In addition to the data type itself, you can also choose to apply one or more modifiers, depending on the type and the requirements of your data.

A selection of commonly-used data types

CHAR(length)
A fixed-length character string from 0 to 255 characters long
VARCHAR(length)
A variable-length character string from 0 to 255 characters long
TEXT
A string with a maximum length of 65,535 characters
TINYINT
An 8-bit (1 byte) integer
INT (or INTEGER)
A 32-bit integer
DOUBLE(length, decimals)
A large (double precision) floating-point number
DECIMAL(length, decimals)
A floating-point number stored as a string (one byte per digit, decimal point (.), or minus sign (-)
DATE
A date in 'YYYY-MM-DD' format
TIME
A time in 'hh:mm:ss' format
DATETIME
A date and time (both parts required) in 'YYYY-MM-DD hh:mm:ss' format
TIMESTAMP
A timestamp (date and time) in 'YYYYMMDDhhmmss' format

A selection of commonly-used data type modifiers

NULL
Indicates values in this column can be missing.
NOT NULL
Indicates there can be no missing values in this column
DEFAULT value.
Provides a default value that will be assigned if no value is supplied for some row in this column.
UNSIGNED
Indicates values in this column must be non-negative (for an integer data type).
AUTO_INCREMENT
Indicates values in this column will be automatically generated in an ascending sequence (for an integer data type).
PRIMARY KEY
Indicates that values in this column will be used for the primary key in this table.

Additional Notes

  1. Database and table names may or may not be case-sensitive, depending on the host system.
  2. SQL keywords and function names are case-insensitive.
  3. Column and index names are case-insensitive.
  4. Use \? to get help on MySQL commands.
  5. Use \c to clear a multi-line query if you do not wish to continue entering it.
  6. Use \q to quit/exit MySQL.
  7. Use \! command to execute the operating system command.
  8. Use \W (that's an uppercase W) to see more detailed information when warnings are issued.
  9. If necessary, you can use "object notation" ("dot notation") to fully qualify a column name in a table (for example), as in table_name.column_name.

What you can do at the Linux command line

  1. You can "run" a file of SQL commands from the Linux command line by giving a command like 
    Linux_prompt> mysql -u username -p < file_of_SQL_commands.sql
    after which you will be prompted for the password for "username" and then the SQL commands in the file file_of_SQL_commands will be executed, provided no errors are encountered.

Access via phpMyAdmin (Tobias Ratschiller 1998)

Command-line access is, of course, a very primitive way to communicate with a MySQL database, but it is always useful to see how things are done at the command line and some users prefer to work there. For one thing, it often gives you much more direct control and sometimes you can do things at the command line that are either difficult or even impossible via a graphical user interface.

However, you should certainly be aware of the phpMyAdmin "front end" to MySQL which, as its name suggests, is a PHP-based graphical user interface utility for dealing with MySQL. It was started in 1998 by Tobias Ratschiller, but like many other projects that were started by an individual (PHP by Rasmus Lerdorf, for example) it now has a large community of both users and contributors.

To access phpMyAdmin on cs.smu.ca or ps.cs.smu.ca, simply browse to 

http://cs.smu.ca/phpmyadmin

or

http://cs.smu.ca/phpmyadmin

as the case may be, and enter the same username and password that you would use for command-line access. You will then be taken to a page from which you may interact in various ways with your current database(s) and/or create new ones, depending on your privilege settings. From this page you can also get help on either MySQL or phpMyAdmin.