Re: message table

From: Stefan Ram <ram_at_zedat.fu-berlin.de>
Date: 19 Sep 2015 14:16:23 GMT
Message-ID: <UNION-20150919151153_at_ram.dialup.fu-berlin.de>

none <invalid_at_nowhere.com> writes:
>| id | from | to | msg | date |
>| 0 | A | B | "msg1" | date1 |

  It might be questionable to use keywords   as column names and to use »date« for a   column that does not contain dates and to   start a numerical primary key with 0. However,

mysql> \W SET sql_mode = 'ANSI,TRADITIONAL'; Show warnings enabled.
Query OK, 0 rows affected (0.00 sec)

mysql> SET div_precision_increment = 4;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP SCHEMA IF EXISTS S; CREATE SCHEMA S; USE S; Query OK, 4 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

mysql>
mysql> CREATE TABLE MESSAGE
    -> ( MESSAGE INTEGER UNSIGNED PRIMARY KEY,
    ->   "FROM"  VARCHAR( 255 ),
    ->   "TO"    VARCHAR( 255 ),
    ->   MSG     TEXT,
    ->   "DATE"  VARCHAR( 255 ));

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO MESSAGE ( MESSAGE, "FROM", "TO",  MSG,     "DATE"  ) VALUES
    ->                     ( 0,       'A',    'B',  '"msg1"', 'date1' ),
    ->                     ( 1,       'A',    'C',  '"msg2"', 'date2' ),
    ->                     ( 2,       'C',    'B',  '"msg3"', 'date3' ),
    ->                     ( 3,       'A',    'B',  '"msg4"', 'date4' ),
    ->                     ( 4,       'D',    'A',  '"msg5"', 'date5' );

Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql>
mysql> SELECT * FROM MESSAGE;
+---------+------+------+--------+-------+
| MESSAGE | FROM | TO | MSG | DATE |
+---------+------+------+--------+-------+
| 0 | A | B | "msg1" | date1 |
| 1 | A | C | "msg2" | date2 |
| 2 | C | B | "msg3" | date3 |
| 3 | A | B | "msg4" | date4 |
| 4 | D | A | "msg5" | date5 |
+---------+------+------+--------+-------+ 5 rows in set (0.00 sec)

mysql>
mysql> CREATE VIEW FROMTO AS
    -> SELECT "FROM" AS USER, "TO" AS USER1, MESSAGE
    -> FROM MESSAGE;

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT * FROM FROMTO;
+------+-------+---------+
| USER | USER1 | MESSAGE |

+------+-------+---------+
| A | B | 0 |
| A | C | 1 |
| C | B | 2 |
| A | B | 3 |
| D | A | 4 |

+------+-------+---------+
5 rows in set (0.00 sec)

mysql>
mysql> CREATE VIEW TOFROM AS
    -> SELECT "TO" AS USER, "FROM" AS USER1, MESSAGE
    -> FROM MESSAGE;

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT * FROM TOFROM;
+------+-------+---------+
| USER | USER1 | MESSAGE |

+------+-------+---------+
| B | A | 0 |
| C | A | 1 |
| B | C | 2 |
| B | A | 3 |
| A | D | 4 |

+------+-------+---------+
5 rows in set (0.00 sec)

mysql>
mysql> CREATE VIEW COM AS
    -> SELECT USER, USER1, MESSAGE FROM FROMTO
    -> UNION
    -> SELECT USER, USER1, MESSAGE FROM TOFROM;

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SELECT * FROM COM ORDER BY MESSAGE; +------+-------+---------+
| USER | USER1 | MESSAGE |

+------+-------+---------+
| A | B | 0 |
| B | A | 0 |
| A | C | 1 |
| C | A | 1 |
| C | B | 2 |
| B | C | 2 |
| A | B | 3 |
| B | A | 3 |
| D | A | 4 |
| A | D | 4 |

+------+-------+---------+
10 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM COM ORDER BY USER;
+------+-------+---------+
| USER | USER1 | MESSAGE |

+------+-------+---------+
| A | B | 0 |
| A | C | 1 |
| A | B | 3 |
| A | D | 4 |
| B | A | 0 |
| B | C | 2 |
| B | A | 3 |
| C | B | 2 |
| C | A | 1 |
| D | A | 4 |

+------+-------+---------+
10 rows in set (0.00 sec)

mysql> Received on Sat Sep 19 2015 - 16:16:23 CEST