Heim >Datenbank >MySQL-Tutorial >Simple MySQL and SQL exercises_MySQL

Simple MySQL and SQL exercises_MySQL

WBOY
WBOYOriginal
2016-06-01 13:13:361151Durchsuche

How to create a sample MySQL data base and user
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 232013 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 -pmysql> 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  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 employeesLIMIT 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 officesLIMIT 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 owhere 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
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel:浅析MySQL各种索引_MySQLNächster Artikel:MySQL数据库备份与还原