Combining Columns to Construct Composite Values

      
mysql>
mysql> CREATE TABLE mail
    -> (
    ->  t               DATETIME,       # when message was sent
    ->  senderUser      CHAR(8),        # sender (source user and host)
    ->  senderHost      CHAR(20),
    ->  recipientUser   CHAR(8),        # recipient (destination user and host)
    ->  recipientHost   CHAR(20),
    ->  size    BIGINT,         # message size in bytes
    ->  INDEX   (t)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO mail (t,senderHost,senderUser,recipientHost,recipientUser,size)
    ->  VALUES
    ->          ('2010-05-11 10:15:08','saturn','barb','mars','tricia',58274),
    ->          ('2010-05-12 12:48:13','mars','tricia','venus','gene',194925),
    ->          ('2010-05-12 15:02:49','mars','phil','saturn','phil',1048),
    ->          ('2010-05-13 13:59:18','saturn','barb','venus','tricia',271),
    ->          ('2010-05-14 09:31:37','venus','gene','mars','barb',2291),
    ->          ('2010-05-14 11:52:17','mars','phil','saturn','tricia',5781),
    ->          ('2010-05-14 14:42:21','venus','barb','venus','barb',98151),
    ->          ('2010-05-14 17:03:01','saturn','tricia','venus','phil',2394482),
    ->          ('2010-05-15 07:17:48','mars','gene','saturn','gene',3824),
    ->          ('2010-05-15 08:50:57','venus','phil','venus','phil',978),
    ->          ('2010-05-15 10:25:52','mars','gene','saturn','tricia',998532),
    ->          ('2010-05-15 17:35:31','saturn','gene','mars','gene',3856),
    ->          ('2010-05-16 09:00:28','venus','gene','mars','barb',613),
    ->          ('2010-05-16 23:04:19','venus','phil','venus','barb',10294),
    ->          ('2010-05-17 12:49:23','mars','phil','saturn','tricia',873),
    ->          ('2010-05-19 22:21:51','saturn','gene','venus','gene',23992)
    -> ;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT
    ->  DATE_FORMAT(t,'%M %e, %Y') AS date_sent,
    ->  CONCAT(senderUser,'@',senderHost) AS sender,
    ->  CONCAT(recipientUser,'@',recipientHost) AS recipient,
    ->  size FROM mail;
+--------------+---------------+---------------+---------+
| date_sent    | sender        | recipient     | size    |
+--------------+---------------+---------------+---------+
| May 11, 2010 | barb@saturn   | tricia@mars   |   58274 |
| May 12, 2010 | tricia@mars   | gene@venus    |  194925 |
| May 12, 2010 | phil@mars     | phil@saturn   |    1048 |
| May 13, 2010 | barb@saturn   | tricia@venus  |     271 |
| May 14, 2010 | gene@venus    | barb@mars     |    2291 |
| May 14, 2010 | phil@mars     | tricia@saturn |    5781 |
| May 14, 2010 | barb@venus    | barb@venus    |   98151 |
| May 14, 2010 | tricia@saturn | phil@venus    | 2394482 |
| May 15, 2010 | gene@mars     | gene@saturn   |    3824 |
| May 15, 2010 | phil@venus    | phil@venus    |     978 |
| May 15, 2010 | gene@mars     | tricia@saturn |  998532 |
| May 15, 2010 | gene@saturn   | gene@mars     |    3856 |
| May 16, 2010 | gene@venus    | barb@mars     |     613 |
| May 16, 2010 | phil@venus    | barb@venus    |   10294 |
| May 17, 2010 | phil@mars     | tricia@saturn |     873 |
| May 19, 2010 | gene@saturn   | gene@venus    |   23992 |
+--------------+---------------+---------------+---------+
16 rows in set (0.00 sec)

mysql>
mysql> drop table mail;
Query OK, 0 rows affected (0.00 sec)

mysql>

Related examples in the same category

1.	Important Column Attributes and Options
2.	Drop a column
3.	Chage column name
4.	Change column definition
5.	Change column data length
6.	Change column data type
7.	change column type and sequence in table
8.	Modify a column
9.	Adding a Column
10.	Changing a Column Definition
11.	Renaming a Table
12.	Dropping a Column
13.	Syntax for Adding a Column
14.	Returns detailed information about all columns of the table tablename
15.	Exchanging Rows and Columns
16.	To copy only some of the columns, name the ones you want in the SELECT part of the statement.
17.	To create columns in a different order than that in which they appear in the source table
18.	Get information only for the co_2 column, you can't use this query:
19.	The -e option specifies the query to execute, and -N tells MySQL not to write the row of column names that normally precedes query output.
20.	Copying Only Selected Columns from a Table
21.	Specifying Which Columns to Display
22.	Giving Names to Output Columns
23.	Aliases can be applied to any result set column, not just those that come from tables
24.	Put the comparison expression in the output column list, perhaps including the values that you're comparing
25.	Refer to the additional output column by position
26.	Take everything but the rightmost two columns
27.	Dropping, Adding, or Repositioning a Column
28.	To indicate a column at a specific position within the table, either use FIRST to make it the first column, or
29.	Changing a Column Definition or Name
30.	After the CHANGE keyword, you name the column you want to change, then specify the new definition, which inclu
31.	Changing a Column's Default Value
32.	Information only about a single column, use a LIKE clause that matches the column name
33.	-d option specifies a field delimiter of : and the -f option indicates that you want to cut column one and all
34.	Add a sequence column named id to the table
35.	Give each table a short alias and refer to table columns using the aliases:
36.	To exclude them, provide a column output list that names specifically only those columns
37.	To produce additional columns showing the total and average values of the books for each author in the author
38.	CREATE TABLE statement provides an example of a TEXT column named DescriptionDoc
39.	Create the Catalog table and includes two NOT NULL columns
40.	Reference a column
41.	Add and drop columns
42.	Giving Columns a New Heading with AS
43.	Alias column with spaces
44.	Insert data without column names
45.	Column definition with modifiers
46.	Sort by column name
47.	Sort more than one columns
48.	Get the descriptive data of the columns belonging to the PLAYERS table
49.	A table contains only one column.
50.	A column definition may include options to define the column data more precisely.
51.	More complex tables have multiple columns
52.	Adding and Dropping Columns
53.	Modifying Existing Columns
54.	Change the LastName column from CHAR(30) to CHAR(40) without renaming the column
55.	Column Types
56.	NULL Values and Column Definitions
57.	Use single quote with char type column
58.	Column alias with underscore
59.	Using Case clause to show meaningful value for column
60.	Select all columns from a table inside another database
61.	Concatenate columns
62.	Using qulified column name
63.	Compare two columns together
64.	Compare two columns and yield two values
65.	Column sequence in insert statemenet
66.	Change column type and settings during table copying
67.	Enlarge column during table copying
68.	Set column to use default value
69.	Get default value from a column
70.	Table column with comments
71.	Check table column settings
72.	Unique modifier for column definition
73.	Indicate unique after column definition
74.	Check column value
75.	Set type column
76.	To disallow NULL values in any of the columns, add NOT NULL to the definition of each one.

Combining Columns to Construct Composite Values : Column « Table Index « SQL / MySQL

Related examples in the same category