UIS Home Search the UIS Website A-Z index
UIS Homepage

Information Technology Services University of Illinois Springfield

Getting Started with Mysql


Logging in to my SQL for the first time. To login to the MySQL Database you need to have logged on at uisacad.uis.edu and that you must have an account for the MySQL Server. If you do not have an account, please mail request at techsupport@uis.edu or go to to http://uisacad.uis.edu/mysql to automatically create an account and an email will be sent to your UIS account.

If you have already an MySQL account, log on at uisacad, and from the shell prompt type:

/usr/local/mysql/bin/mysql -u<yourNetID> -p

then enter your password when prompt appears

If your account matches with the password, the mysql prompt should be displayed as follows:

mysql>

You may also type /usr/local/mysql/bin/mysql -u -p at the unix prompt but this will expose your password if somebody is looking at your screen.


Changing Your Password In response to the mysql> prompt, type

mysql>SET PASSWORD FOR <netid>@localhost = PASSWORD(‘NewPassword’);

where is login name and the same as at uisacad, and <NewPassword> is the password you would like to use in the future. This command, like all other SQL commands, should be terminated with a semicolon.

Note that SQL is completely case-insensitive. Once you are in mysql> promot, you can use capitals or not in keywords like ALTER; but password is case sensitive.


Connecting to a Database, enter the command,
mysql>use<netid>

The <netid>is used as the default database assigned to each user. From there, each one can create tables and do triggers.


Creating a Table In mysql we can execute any SQL command. One simple type of command creates a table (relation). The form is

mysql>CREATE TABLE student (name VARCHAR(25), netid VARCHAR(7), birth DATE);

You may enter text on one line or on several lines. If your command runs over several lines, you will be prompted with line numbers until you type the semicolon that ends any command. (Warning: An empty line terminates the command but does not execute it. An example table-creation command is:

mysql>CREATE TABLE student (
name VARCHAR(25),
netid VARSHAR(7),
birth DATE
);

This command gives the same effect as the above one. It creates a table named student with three attributes. The first, named name, is an alphanumberic of 25 characters; the second, named netid, is also an alphanumeric character string of length 7; the third, named birth, is a date field type.

To check for tables that were created, type

show tables;
describe <TableName>;


Creating a Table With a Primary Key To create a table that declares attribute a to be a primary key:

CREATE TABLE <TableName> (…, a <type> PRIMARY KEY, b, …);

To create a table that declares the set of attributes (a,b,c) to be a primary key:

CREATE TABLE <TableName> (<attrs and their types>, PRIMARY KEY (a,b,c));


Inserting Tuples Having created a table, we can insert tuples into it. The simplest way to insert is with the INSERT command:

INSERT INTO <tableName> VALUES(<list of values for attributes, in order>);

For instance, we can insert the tuple (‘John Doe’,’uistud1s ’1985-01-01′) into relation to the student table by:

INSERT INTO student VALUES(‘John Doe’,’uistud1s ’1985-01-01′);


Getting the Value of a Relation We can see the tuples in a relation with the command:

SELECT *

FROM <tableName>;

For instance, after the above create and insert statements, the command

SELECT * FROM student;

produces the result

+———-+———+————+
| name | netid | birth |
+———-+———+————+
| John Doe | uistud1 | 1985-01-01 |
+———-+———+————+
1 row in set (0.00 sec)


To delete all the contents of your table, type:

delete from <tableName>;

Getting Rid of Your Tables To remove a table from your database, execute

DROP TABLE <tableName>;

We suggest you execute

DROP TABLE student;

after trying out this sequence of commands to avoid leaving a lot of garbage around that will be still there the next time you use the MySQL.


Getting Information About Your Database The system keeps information about your own database in certain system tables. The most important for now is USER_TABLES. You can recall the names of your tables by issuing the query:

SELECT <TableColumn> FROM <tableName>;

More information about your tables is available from the UserTable. To see all the attributes of the user’s table, try:

SELECT * FROM <TableName>;

It is also possible to recall the attributes of a table once you know its name. Issue the command:

DESCRIBE <TableName>;

to learn about the attributes of relation .


Quitting mysql. To leave MySQL, type

quit; or
\q

in response to the mysql> prompt.

This will bring you back to the shell prompt.


[return to top]