You can download an example data base sql file from here: http://www.mysqltutorial.org/mysql-sample-database.aspx. After unziping you should find following file:
rado2@ubuntu12-04:~$ ls -la mysqlsampledatabase.sql -rw-rw-r-- 1 rado2 rado2 190711 May 23 2013 mysqlsampledatabase.sql
rado2@ubuntu12-04:~$ more mysqlsampledatabase.sql /* http://www.mysqltutorial.org */ CREATE DATABASE /*!32312 IF NOT EXISTS*/`classicmodels` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `classicmodels`; /*Table structure for table `customers` */ DROP TABLE IF EXISTS `customers`; CREATE TABLE `customers` ( `customerNumber` int(11) NOT NULL, `customerName` varchar(50) NOT NULL, `contactLastName` varchar(50) NOT NULL, `contactFirstName` varchar(50) NOT NULL, ....
We don't want to use a root user to manipulate our data base records. To create a separate user you can run these commands:
$ mysql -u root -p mysql> use information_schema; mysql> CREATE USER 'rado2'@'localhost'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'rado2'@'localhost'; mysql> select * from USER_PRIVILEGES ;
To import and inspect the database we can use this commands:
$ mysql -u rado2 < mysqlsampledatabase.sql $ mysql -u rado2 mysql> show databases; mysql> show tables; +-------------------------+ | Tables_in_classicmodels | +-------------------------+ | customers | | employees | | offices | | orderdetails | | orders | | payments | | productlines | | products | +-------------------------+ 8 rows in set (0.00 sec) mysql> select * from employees LIMIT 5; +----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+ | employeeNumber | lastName | firstName | extension | email | officeCode | reportsTo | jobTitle | +----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+ | 1002 | Murphy | Diane | x5800 | dmurphy@classicmodelcars.com | 1 | NULL | President | | 1056 | Patterson | Mary | x4611 | mpatterso@classicmodelcars.com | 1 | 1002 | VP Sales | | 1076 | Firrelli | Jeff | x9273 | jfirrelli@classicmodelcars.com | 1 | 1002 | VP Marketing | | 1088 | Patterson | William | x4871 | wpatterson@classicmodelcars.com | 6 | 1056 | Sales Manager (APAC) | | 1102 | Bondur | Gerard | x5408 | gbondur@classicmodelcars.com | 4 | 1056 | Sale Manager (EMEA) | +----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+----------------------+ mysql> select * from offices LIMIT 5; +------------+---------------+-----------------+--------------------------+--------------+------------+---------+------------+-----------+ | officeCode | city | phone | addressLine1 | addressLine2 | state | country | postalCode | territory | +------------+---------------+-----------------+--------------------------+--------------+------------+---------+------------+-----------+ | 1 | San Francisco | +1 650 219 4782 | 100 Market Street | Suite 300 | CA | USA | 94080 | NA | | 2 | Boston | +1 215 837 0825 | 1550 Court Place | Suite 102 | MA | USA | 02107 | NA | | 3 | NYC | +1 212 555 3000 | 523 East 53rd Street | apt. 5A | NY | USA | 10022 | NA | | 4 | Paris | +33 14 723 4404 | 43 Rue Jouffroy D'abbans | NULL | NULL | France | 75017 | EMEA | | 5 | Tokyo | +81 33 224 5000 | 4-1 Kioicho | NULL | Chiyoda-Ku | Japan | 102-8578 | Japan | +------------+---------------+-----------------+--------------------------+--------------+------------+---------+------------+-----------+ mysql> show COLUMNS FROM employees +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | employeeNumber | int(11) | NO | PRI | NULL | | | lastName | varchar(50) | NO | | NULL | | | firstName | varchar(50) | NO | | NULL | | | extension | varchar(10) | NO | | NULL | | | email | varchar(100) | NO | | NULL | | | officeCode | varchar(10) | NO | MUL | NULL | | | reportsTo | int(11) | YES | MUL | NULL | | | jobTitle | varchar(50) | NO | | NULL | | +----------------+--------------+------+-----+---------+-------+ mysql> show COLUMNS FROM offices ; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | officeCode | varchar(10) | NO | PRI | NULL | | | city | varchar(50) | NO | | NULL | | | phone | varchar(50) | NO | | NULL | | | addressLine1 | varchar(50) | NO | | NULL | | | addressLine2 | varchar(50) | YES | | NULL | | | state | varchar(50) | YES | | NULL | | | country | varchar(50) | NO | | NULL | | | postalCode | varchar(15) | NO | | NULL | | | territory | varchar(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 9 rows in set (0.00 sec)
Exercise 1: select all employees from offices in USA only
mysql> SELECT * FROM employees as e, offices as o where e.officeCode = o.officeCode and o.country='USA'; +----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+--------------------+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+ | employeeNumber | lastName | firstName | extension | email | officeCode | reportsTo | jobTitle | officeCode | city | phone | addressLine1 | addressLine2 | state | country | postalCode | territory | +----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+--------------------+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+ | 1002 | Murphy | Diane | x5800 | dmurphy@classicmodelcars.com | 1 | NULL | President | 1 | San Francisco | +1 650 219 4782 | 100 Market Street | Suite 300 | CA | USA | 94080 | NA | | 1056 | Patterson | Mary | x4611 | mpatterso@classicmodelcars.com | 1 | 1002 | VP Sales | 1 | San Francisco | +1 650 219 4782 | 100 Market Street | Suite 300 | CA | USA | 94080 | NA | | 1076 | Firrelli | Jeff | x9273 | jfirrelli@classicmodelcars.com | 1 | 1002 | VP Marketing | 1 | San Francisco | +1 650 219 4782 | 100 Market Street | Suite 300 | CA | USA | 94080 | NA | | 1143 | Bow | Anthony | x5428 | abow@classicmodelcars.com | 1 | 1056 | Sales Manager (NA) | 1 | San Francisco | +1 650 219 4782 | 100 Market Street | Suite 300 | CA | USA | 94080 | NA | | 1165 | Jennings | Leslie | x3291 | ljennings@classicmodelcars.com | 1 | 1143 | Sales Rep | 1 | San Francisco | +1 650 219 4782 | 100 Market Street | Suite 300 | CA | USA | 94080 | NA | | 1166 | Thompson | Leslie | x4065 | lthompson@classicmodelcars.com | 1 | 1143 | Sales Rep | 1 | San Francisco | +1 650 219 4782 | 100 Market Street | Suite 300 | CA | USA | 94080 | NA | | 1188 | Firrelli | Julie | x2173 | jfirrelli@classicmodelcars.com | 2 | 1143 | Sales Rep | 2 | Boston | +1 215 837 0825 | 1550 Court Place | Suite 102 | MA | USA | 02107 | NA | | 1216 | Patterson | Steve | x4334 | spatterson@classicmodelcars.com | 2 | 1143 | Sales Rep | 2 | Boston | +1 215 837 0825 | 1550 Court Place | Suite 102 | MA | USA | 02107 | NA | | 1286 | Tseng | Foon Yue | x2248 | ftseng@classicmodelcars.com | 3 | 1143 | Sales Rep | 3 | NYC | +1 212 555 3000 | 523 East 53rd Street | apt. 5A | NY | USA | 10022 | NA | | 1323 | Vanauf | George | x4102 | gvanauf@classicmodelcars.com | 3 | 1143 | Sales Rep | 3 | NYC | +1 212 555 3000 | 523 East 53rd Street | apt. 5A | NY | USA | 10022 | NA | +----------------+-----------+-----------+-----------+---------------------------------+------------+-----------+--------------------+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+ 10 rows in set (0.00 sec)
References
http://www.mysqltutorial.org/mysql-sample-database.aspx
http://en.wikipedia.org/wiki/Join_%28SQL%29
https://answers.yahoo.com/question/index?qid=20080520200936AAmD1Mt
http://www.cyberciti.biz/tips/mysql-auto-completion-for-database-table-names.html
Posts
