SSE 3200 Mysql lab Introduction SQL (Structured Query Language) is - - PDF document

SSE 3200 Mysql lab

Introduction

SQL (Structured Query Language) is a standard language for creating, accessing, and manipulating

• databases. A database is a collection of tables. Each table consists of several rows of data called
• records. Records in a table have special fields called keys to facilitate fast access.

Getting started

The database server runs on the machine called orion. You will start Mysql on orion. In the commands substitute your account for “nilufer”.

• 1. Open a terminal window and log on to orion.

ssh orion.csl.mtu.edu

• 2. Start mysql with your username and have it prompt for a password.

[nilufer@orion ˜]$ mysql -unilufer -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19819 Server version: 5.0.37 Source distribution Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the buffer. mysql>

• 3. Display the databases that you have access to.

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | airline | | nilufer | +--------------------+ 3 rows in set (0.00 sec) 1

• 4. Select your database to work on.

mysql> use nilufer; 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

• 5. Check to see if the database contains any tables. If there are any delete them using the

drop table command. mysql> show tables; +-------------------+ | Tables_in_nilufer | +-------------------+ | shop | +-------------------+ 1 row in set (0.00 sec) mysql> drop table shop; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec)

Creating and Accessing Tables

• 1. Create a table for florists.

mysql> create table florists (

• > CompanyId char(3),
• > CompanyName char(20),
• > Address char(20),
• > City char(10),
• > Zipcode char(5),
• > Phone char(10),
• > StartPrice real(6,2),
• > primary key(CompanyId)
• > );

Query OK, 0 rows affected (0.00 sec) 2

• 2. Check the properties of the newly created table.

mysql> describe florists; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | CompanyId | char(3) | NO | PRI | | | | CompanyName | char(20) | YES | | NULL | | | Address | char(20) | YES | | NULL | | | City | char(10) | YES | | NULL | | | Zipcode | char(5) | YES | | NULL | | | Phone | char(10) | YES | | NULL | | | StartPrice | double(6,2) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)

• 3. Insert some records into florists.

mysql> insert into florists values

• > (’KAU’, ’Kukkakauppa’, ’320 W. Quincy’, ’Hancock’, ’49930’,
• > ’9064822060’, 9.99);

Query OK, 1 row affected (0.00 sec) mysql> insert into florists values

• > (’BIR’, ’Blue Iris’, ’403 Shelden Av.’, ’Houghton’, ’49931’,
• > ’9064822583’, 19.99);

mysql> insert into florists values

• > (’CFL’, ’Calumet Floral’, ’221 Fifth Street’, ’Calumet’, ’49913’,
• > ’9063371711’, 9.99);

mysql> insert into florists values

• > (’ECO’, ’Econo Foods’, ’1000 Sharon Avenue’, ’Houghton’, ’49931’,
• > ’9064879675’, 1.99);

mysql> insert into florists values

• > (’SLE’, ’Flowers by Sleeman’, ’1201 Memorial Rd.’, ’Houghton’, ’49931’,
• > ’9064824023’, 17.99);

mysql> insert into florists values

• > (’KAU’, ’Kukkakauppa’, ’320 W. Quincy’, ’Hancock’, ’49930’,
• > ’9064822060’, 9.99);

3

mysql> insert into florists values

• > (’KAT’, ’Kathy\’s Country Flowers’, ’109 W. Quincy’, ’Hancock’, ’49930’,
• > ’9064826261’, 9.99);
• 4. Display the records in the table.

mysql> select * from florists; +-----------+----------------------+--------------------+----------+---------+------------+------------+ | CompanyId | CompanyName | Address | City | Zipcode | Phone | StartPrice | +-----------+----------------------+--------------------+----------+---------+------------+------------+ | BIR | Blue Iris | 403 Shelden Av. | Houghton | 49931 | 9064822583 | 9.99 | | CFL | Calumet Floral | 221 Fifth Street | Calumet | 49913 | 9063371711 | 9.99 | | ECO | Econo Foods | 1000 Sharon Avenue | Houghton | 49931 | 9064879675 | 1.99 | | SLE | Flowers by Sleeman | 1201 Memorial Rd. | Houghton | 49931 | 9064824023 | 17.99 | | KAU | Kukkakauppa | 320 W. Quincy | Hancock | 49930 | 9064822060 | 9.99 | | KAT | Kathy’s Country Flow | 109 W. Quincy | Hancock | 49930 | 9064826261 | 9.99 | +-----------+----------------------+--------------------+----------+---------+------------+------------+ 6 rows in set (0.00 sec)

• 5. You should not be able to enter records with duplicate keys.

mysql> insert into florists values

• >

(’KAU’, ’abc’, ’abc’, ’abc’, ’12345’, ’9061234567’, 9.99); ERROR 1062 (23000): Duplicate entry ’KAU’ for key 1 4

• 6. Display only two columns.

mysql> select address, city from florists; +--------------------+----------+ | address | city | +--------------------+----------+ | 403 Shelden Av. | Houghton | | 221 Fifth Street | Calumet | | 1000 Sharon Avenue | Houghton | | 1201 Memorial Rd. | Houghton | | 320 W. Quincy | Hancock | | 109 W. Quincy | Hancock | +--------------------+----------+ 6 rows in set (0.00 sec)

• 7. Display only one column. Notice that it will list duplicates.

mysql> select city from florists; +----------+ | city | +----------+ | Houghton | | Calumet | | Houghton | | Houghton | | Hancock | | Hancock | +----------+ 6 rows in set (0.00 sec)

• 8. Use distinct to eliminate duplicates.

mysql> select distinct city from florists; +----------+ | city | +----------+ | Houghton | | Calumet | | Hancock | +----------+ 3 rows in set (0.00 sec) 5

• 9. Display only the florists in Houghton.

mysql> select * from florists where city=’Houghton’; +-----------+--------------------+--------------------+----------+---------+------------+------------+ | CompanyId | CompanyName | Address | City | Zipcode | Phone | StartPrice | +-----------+--------------------+--------------------+----------+---------+------------+------------+ | BIR | Blue Iris | 403 Shelden Av. | Houghton | 49931 | 9064822583 | 9.99 | | ECO | Econo Foods | 1000 Sharon Avenue | Houghton | 49931 | 9064879675 | 1.99 | | SLE | Flowers by Sleeman | 1201 Memorial Rd. | Houghton | 49931 | 9064824023 | 17.99 | +-----------+--------------------+--------------------+----------+---------+------------+------------+ 3 rows in set (0.00 sec)

• 10. Display those that have a start price of $10.00 or under.

mysql> select * from florists where StartPrice <= 10; +-----------+----------------------+--------------------+----------+---------+------------+------------+ | CompanyId | CompanyName | Address | City | Zipcode | Phone | StartPrice | +-----------+----------------------+--------------------+----------+---------+------------+------------+ | BIR | Blue Iris | 403 Shelden Av. | Houghton | 49931 | 9064822583 | 9.99 | | CFL | Calumet Floral | 221 Fifth Street | Calumet | 49913 | 9063371711 | 9.99 | | ECO | Econo Foods | 1000 Sharon Avenue | Houghton | 49931 | 9064879675 | 1.99 | | KAU | Kukkakauppa | 320 W. Quincy | Hancock | 49930 | 9064822060 | 9.99 | | KAT | Kathy’s Country Flow | 109 W. Quincy | Hancock | 49930 | 9064826261 | 9.99 | +-----------+----------------------+--------------------+----------+---------+------------+------------+ 5 rows in set (0.00 sec) 6

• 11. Try combining conditions using and.

mysql> select * from florists where

• > city=’Houghton’ and StartPrice <= 10;

+-----------+-------------+--------------------+----------+---------+------------+------------+ | CompanyId | CompanyName | Address | City | Zipcode | Phone | StartPrice | +-----------+-------------+--------------------+----------+---------+------------+------------+ | BIR | Blue Iris | 403 Shelden Av. | Houghton | 49931 | 9064822583 | 9.99 | | ECO | Econo Foods | 1000 Sharon Avenue | Houghton | 49931 | 9064879675 | 1.99 | +-----------+-------------+--------------------+----------+---------+------------+------------+ 2 rows in set (0.00 sec)

• 12. Try a condition with between.

mysql> select * from florists where StartPrice between 8 and 10; +-----------+----------------------+------------------+----------+---------+------------+------------+ | CompanyId | CompanyName | Address | City | Zipcode | Phone | StartPrice | +-----------+----------------------+------------------+----------+---------+------------+------------+ | BIR | Blue Iris | 403 Shelden Av. | Houghton | 49931 | 9064822583 | 9.99 | | CFL | Calumet Floral | 221 Fifth Street | Calumet | 49913 | 9063371711 | 9.99 | | KAU | Kukkakauppa | 320 W. Quincy | Hancock | 49930 | 9064822060 | 9.99 | | KAT | Kathy’s Country Flow | 109 W. Quincy | Hancock | 49930 | 9064826261 | 9.99 | +-----------+----------------------+------------------+----------+---------+------------+------------+ 4 rows in set (0.00 sec) 7

• 13. Insert a record and then delete it.

mysql> insert into florists values

• > (’ABC’, ’abc’, ’abc’, ’abc’, ’12345’, ’9061234567’, -1);

Query OK, 1 row affected (0.00 sec) mysql> select count(*) from florists; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) mysql> delete from florists where StartPrice < 0; uery OK, 1 row affected (0.00 sec) mysql> select count(*) from florists; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec)

• 14. Try ordering the records.

mysql> select CompanyName, Phone, StartPrice

• > from florists order by StartPrice ASC;

+----------------------+------------+------------+ | CompanyName | Phone | StartPrice | +----------------------+------------+------------+ | Econo Foods | 9064879675 | 1.99 | | Blue Iris | 9064822583 | 9.99 | | Calumet Floral | 9063371711 | 9.99 | | Kukkakauppa | 9064822060 | 9.99 | | Kathy’s Country Flow | 9064826261 | 9.99 | | Flowers by Sleeman | 9064824023 | 17.99 | +----------------------+------------+------------+ 6 rows in set (0.01 sec) 8

• 15. Try updating a record.

mysql> select CompanyName, Phone, StartPrice from florists; +----------------------+------------+------------+ | CompanyName | Phone | StartPrice | +----------------------+------------+------------+ | Blue Iris | 9064822583 | 9.99 | | Calumet Floral | 9063371711 | 9.99 | | Econo Foods | 9064879675 | 1.99 | | Flowers by Sleeman | 9064824023 | 17.99 | | Kukkakauppa | 9064822060 | 9.99 | | Kathy’s Country Flow | 9064826261 | 9.99 | +----------------------+------------+------------+ 6 rows in set (0.00 sec) mysql> update florists

• > set Phone = ’9063371711’
• > where CompanyName = ’Calumet Floral’;

Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select CompanyName, Phone, StartPrice from florists; +----------------------+------------+------------+ | CompanyName | Phone | StartPrice | +----------------------+------------+------------+ | Blue Iris | 9064822583 | 9.99 | | Calumet Floral | 9063371711 | 9.99 | | Econo Foods | 9064879675 | 1.99 | | Flowers by Sleeman | 9064824023 | 17.99 | | Kukkakauppa | 9064822060 | 9.99 | | Kathy’s Country Flow | 9064826261 | 9.99 | +----------------------+------------+------------+ 6 rows in set (0.00 sec)

• 16. Next time, we will access the database using Php;

mysql> exit; Bye [nilufer@orion ˜]$ 9