MySQL is most commonly entered through a telnet session.
Exercise
1. Telnet to cybil
At the prompt, type
mysql and press Enter
once loaded you should see a mysql prompt.
mysql>
Now we can open up a database and examine the tables it contains.
mysql> use test_1;
- use is the command to open a database
- test_1 is the name of a special database to which all mysql users have access.
- note the ; at the end of the line this is critical. Forget it and nothing will happen.
Now the database is open we can see what tables it contains
mysql> show tables;
We will be working with the wines table so lets examine that in more detail.
mysql> describe wines;
this should show you the structure of the wines table
+----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | producer | varchar(40) | YES | | NULL | | | supplier | varchar(40) | YES | | NULL | | | category | varchar(200) | YES | | NULL | | | year | varchar(4) | YES | | NULL | | | region | varchar(40) | YES | | NULL | | | cost | decimal(6,2) | YES | | NULL | | | price | decimal(6,2) | YES | | NULL | | | quantity | int(11) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 8 rows in set (0.07 sec) mysql>
Now you have seen the structure of the wines table, lets retrieve the records.
mysql> select * from wines;
This should give you a list of about 50 records.
Often we want to restrict what we want to see. Try this,
mysql> select category, price from wines;
Only the category and price fields should be displayed.
We can also restrict the records according to certain criteria,
mysql> select * from wines where price > 10;
Only wines selling for more than 10.00 should be displayed
To add records manually you would use
insert into wines (producer,supplier,category,region, cost,price) values ('value1','value2',etc);
To delete records manually from a table called friends type,
delete from friends where name='joe';
In this exercise you will create a table of your friends
Step 1. Creating a Table
use the command create table yourname (fname varchar(20),sex varchar(1), dob date);
Step 2. Adding Records
use the insert command to add at least 6 friends to your table.
(Note: Date fields need to be entered as yyyymmdd - example 19901225)
Step 3. Querying the table
use the select command to display all the females (or if you prefer, males) and their dob.
Step 4. Deleting tables
use the command drop table yourname; to remove your table.