Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodels;
Database changed
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int | NO | PRI | NULL | |
| orderDate | date | NO | | NULL | |
| requiredDate | date | NO | | NULL | |
| shippedDate | date | YES | | NULL | |
| returneddate | year | YES | | NULL | |
| status | varchar(29) | YES | | NULL | |
| customerNumber | int | NO | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
mysql> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber | int | NO | PRI | NULL | |
| customerName | varchar(50) | NO | | NULL | |
| contactLastName | varchar(50) | NO | | NULL | |
| contactFirstName | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
| addressLine1 | varchar(50) | NO | | NULL | |
| addressLine2 | varchar(50) | YES | | NULL | |
| city | varchar(50) | NO | | NULL | |
| state | varchar(50) | YES | | NULL | |
| postalCode | varchar(15) | YES | | NULL | |
| country | varchar(50) | NO | | NULL | |
| salesRepEmployeeNumber | int | YES | MUL | NULL | |
| creditLimit | decimal(10,2) | YES | | NULL | |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
mysql> create view custord as
-> select phone ,city ,orderdate ,ordernumber from customers c,orders o where c.customerNumber=o.customerNumber;
Query OK, 0 rows affected (0.17 sec)
mysql> select * from custord limit 30;
+--------------+------------+------------+-------------+
| phone | city | orderdate | ordernumber |
+--------------+------------+------------+-------------+
| 40.32.2555 | Nantes | 2003-05-20 | 10123 |
| 40.32.2555 | Nantes | 2004-09-27 | 10298 |
| 40.32.2555 | Nantes | 2004-11-25 | 10345 |
| 7025551838 | Las Vegas | 2003-05-21 | 10124 |
| 7025551838 | Las Vegas | 2004-08-06 | 10278 |
| 7025551838 | Las Vegas | 2004-11-29 | 10346 |
| 03 9520 4555 | Melbourne | 2003-04-29 | 10120 |
| 03 9520 4555 | Melbourne | 2003-05-21 | 10125 |
| 03 9520 4555 | Melbourne | 2004-02-20 | 10223 |
| 03 9520 4555 | Melbourne | 2004-11-24 | 10342 |
| 03 9520 4555 | Melbourne | 2004-11-29 | 10347 |
| 40.67.8555 | Nantes | 2004-07-23 | 10275 |
| 40.67.8555 | Nantes | 2004-10-29 | 10315 |
| 40.67.8555 | Nantes | 2005-02-03 | 10375 |
| 40.67.8555 | Nantes | 2005-05-31 | 10425 |
| 07-98 9555 | Stavern | 2003-01-29 | 10103 |
| 07-98 9555 | Stavern | 2003-10-10 | 10158 |
| 07-98 9555 | Stavern | 2004-10-15 | 10309 |
| 07-98 9555 | Stavern | 2004-11-05 | 10325 |
| 4155551450 | San Rafael | 2003-03-26 | 10113 |
| 4155551450 | San Rafael | 2003-07-02 | 10135 |
| 4155551450 | San Rafael | 2003-08-08 | 10142 |
| 4155551450 | San Rafael | 2003-11-12 | 10182 |
| 4155551450 | San Rafael | 2004-03-11 | 10229 |
| 4155551450 | San Rafael | 2004-07-20 | 10271 |
| 4155551450 | San Rafael | 2004-08-20 | 10282 |
| 4155551450 | San Rafael | 2004-10-21 | 10312 |
| 4155551450 | San Rafael | 2004-11-19 | 10335 |
| 4155551450 | San Rafael | 2004-12-10 | 10357 |
| 4155551450 | San Rafael | 2005-01-19 | 10368 |
+--------------+------------+------------+-------------+
30 rows in set (0.00 sec)
mysql> create OR REPLACE view custord as
-> select phone ,city ,orderdate ,ordernumber,status from customers c,orders o where c.customerNumber=o.customerNumber;
Query OK, 0 rows affected (0.24 sec)
mysql> select * from custord limit 30;
+--------------+------------+------------+-------------+------------+
| phone | city | orderdate | ordernumber | status |
+--------------+------------+------------+-------------+------------+
| 40.32.2555 | Nantes | 2003-05-20 | 10123 | Shipped |
| 40.32.2555 | Nantes | 2004-09-27 | 10298 | Shipped |
| 40.32.2555 | Nantes | 2004-11-25 | 10345 | Shipped |
| 7025551838 | Las Vegas | 2003-05-21 | 10124 | Shipped |
| 7025551838 | Las Vegas | 2004-08-06 | 10278 | Shipped |
| 7025551838 | Las Vegas | 2004-11-29 | 10346 | Shipped |
| 03 9520 4555 | Melbourne | 2003-04-29 | 10120 | Shipped |
| 03 9520 4555 | Melbourne | 2003-05-21 | 10125 | Shipped |
| 03 9520 4555 | Melbourne | 2004-02-20 | 10223 | Shipped |
| 03 9520 4555 | Melbourne | 2004-11-24 | 10342 | Shipped |
| 03 9520 4555 | Melbourne | 2004-11-29 | 10347 | Shipped |
| 40.67.8555 | Nantes | 2004-07-23 | 10275 | Shipped |
| 40.67.8555 | Nantes | 2004-10-29 | 10315 | Shipped |
| 40.67.8555 | Nantes | 2005-02-03 | 10375 | Shipped |
| 40.67.8555 | Nantes | 2005-05-31 | 10425 | In Process |
| 07-98 9555 | Stavern | 2003-01-29 | 10103 | Shipped |
| 07-98 9555 | Stavern | 2003-10-10 | 10158 | Shipped |
| 07-98 9555 | Stavern | 2004-10-15 | 10309 | Shipped |
| 07-98 9555 | Stavern | 2004-11-05 | 10325 | Shipped |
| 4155551450 | San Rafael | 2003-03-26 | 10113 | Shipped |
| 4155551450 | San Rafael | 2003-07-02 | 10135 | Shipped |
| 4155551450 | San Rafael | 2003-08-08 | 10142 | Shipped |
| 4155551450 | San Rafael | 2003-11-12 | 10182 | Shipped |
| 4155551450 | San Rafael | 2004-03-11 | 10229 | Shipped |
| 4155551450 | San Rafael | 2004-07-20 | 10271 | Shipped |
| 4155551450 | San Rafael | 2004-08-20 | 10282 | Shipped |
| 4155551450 | San Rafael | 2004-10-21 | 10312 | Shipped |
| 4155551450 | San Rafael | 2004-11-19 | 10335 | Shipped |
| 4155551450 | San Rafael | 2004-12-10 | 10357 | Shipped |
| 4155551450 | San Rafael | 2005-01-19 | 10368 | Shipped |
+--------------+------------+------------+-------------+------------+
30 rows in set (0.00 sec)
mysql> drop view costord;
ERROR 1051 (42S02): Unknown table 'classicmodels.costord'
mysql> drop view custord;
Query OK, 0 rows affected (0.13 sec)
mysql> show full tables;
+-------------------------+------------+
| Tables_in_classicmodels | Table_type |
+-------------------------+------------+
| accounts | BASE TABLE |
| book | BASE TABLE |
| customers | BASE TABLE |
| custview | VIEW |
| employees | BASE TABLE |
| library | BASE TABLE |
| list | VIEW |
| offices | BASE TABLE |
| orderdetails | BASE TABLE |
| orders | BASE TABLE |
| payments | BASE TABLE |
| productlines | BASE TABLE |
| products | BASE TABLE |
+-------------------------+------------+
13 rows in set (0.00 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.