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
Recommend
More recommend
