10:00 ~ 12:00    test:~# vim /tmp/birs.txt
'alan'  '1956-02-08' #fields terminated by 'tab'
'yled'  '1989-05-03'
mysql> load data infile '/tmp/birs.txt' into table bir fields terminated by '\t ' enclosed by '''';
mysql> select * from bir;           
+------+------------+
| name | bir        |
+------+------------+
| john | 1956-02-03 |
| emma | 1963-02-03 |
| alan | 1956-02-08 |
| yled | 1989-05-03 |
test:~# vim /tmp/birs.txt
'alan'  '1956-02-08' #fields terminated by ' '
'yled'  '1989-05-03'
mysql> load data infile '/tmp/birs.txt' into table bir fields terminated by ' ' enclosed by '''';
mysql> select * from bir;           
+------+------------+
| name | bir        |
+------+------------+
| john | 1956-02-03 |
| emma | 1963-02-03 |
| alan | 1956-02-08 |
| yled | 1989-05-03 |
| alan | 1956-02-08 |
| yled | 1989-05-03 |
+------+------------+
6 rows in set (0.00 sec)

mysql> select * from t2 group by id;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | aaa  |
|  3 | bbb  |
|  4 | aaa  |
|  5 | ccc  |
|  6 | ddd  |
|  7 | bbb  |
+----+------+
7 rows in set (0.01 sec)
mysql> create table t22 select * from t2 group by id;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> drop table t2;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t22 rename to t2;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2;                                           
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | aaa  |
|  3 | bbb  |
|  4 | aaa  |
|  5 | ccc  |
|  6 | ddd  |
|  7 | bbb  |
+----+------+
7 rows in set (0.01 sec) 
select * from help_topic into outfile '/tmp/helptopic.txt' fields terminated by ',' enclosed by '"' lines terminated by
'\r\n';
mysql> insert into people values('233','wddd',5) on duplicate key update name='wddd',peopleid='233';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from people;
+----------+--------+-------+
| peopleid | name   | rowid |
+----------+--------+-------+
|        1 | wang   |     1 |
|       21 | wandg  |     2 |
|      211 | wandg  |     3 |
|      211 | wanddg |     4 |
|      233 | wddd   |     5 |
+----------+--------+-------+
5 rows in set (0.01 sec)
mysql> update people set peopleid='255',name='wdde' where rowid=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from people;                                     
+----------+--------+-------+
| peopleid | name   | rowid |
+----------+--------+-------+
|        1 | wang   |     1 |
|       21 | wandg  |     2 |
|      211 | wandg  |     3 |
|      211 | wanddg |     4 |
|      255 | wdde   |     5 |
+----------+--------+-------+
5 rows in set (0.00 sec)
mysql> desc catalog;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| cid   | int(3) | NO   | PRI | NULL    | auto_increment |
| cname | text   | NO   |     |         |                |
+-------+--------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> desc topic;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| tid   | int(3) | NO   | PRI | NULL    | auto_increment |
| tname | text   | YES  |     | NULL    |                |
| tdate | date   | YES  |     | NULL    |                |
| cid   | int(3) | YES  |     | NULL    |                |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> select cname,tname,tdate from catalog,topic where catalog.cid=topic.cid;
+-----------------+--------------------------+------------+
| cname           | tname                    | tdate      |
+-----------------+--------------------------+------------+
| sports          | daddobds ond mars        | 2112-03-19 |
| current affairs | daddobe landdds ond mars | 2112-04-19 |
| business        | probe landdds ond mars   | 2012-03-19 |
| technology      | probe lands on mars      | 2002-03-19 |
+-----------------+--------------------------+------------+
4 rows in set (0.00 sec)
mysql> select cname,tname,tdate from catalog,topic where catalog.cid=topic.cid and catalog.cname='sports';
+--------+-------------------+------------+
| cname  | tname             | tdate      |
+--------+-------------------+------------+
| sports | daddobds ond mars | 2112-03-19 |
+--------+-------------------+------------+
1 row in set (0.00 sec)
mysql> select cname,tname,tdate from catalog,topic where catalog.cid=topic.cid and catalog.cname='sports'and year
(topic.tdate)=2112;
+--------+-------------------+------------+
| cname  | tname             | tdate      |
+--------+-------------------+------------+
| sports | daddobds ond mars | 2112-03-19 |
+--------+-------------------+------------+
1 row in set (0.01 sec)
mysql> select topic.cid,cname,tname from catalog,topic where catalog.cid=topic.cid;
+------+-----------------+--------------------------+
| cid  | cname           | tname                    |
+------+-----------------+--------------------------+
|    1 | sports          | daddobds ond mars        |
|    2 | current affairs | daddobe landdds ond mars |
|    3 | business        | probe landdds ond mars   |
|    4 | technology      | probe lands on mars      |
+------+-----------------+--------------------------+
4 rows in set (0.00 sec)
mysql> select usrs.name,grps.name,usrs.uid,grps.gid from usrs,grps,ugs where usrs.uid=ugs.uid and grps.gid=ugs.gid;
+-------+-----------+------+------+
| name  | name      | uid  | gid  |
+-------+-----------+------+------+
| sam   | actors    |  107 |  502 |
| sue   | musicians |  100 |  503 |
| james | authors   |  110 |  501 |
| rita  | authors   |  112 |  501 |
| sue   | authors   |  100 |  501 |
| louis | actors    |  104 |  502 |
| sue   | actors    |  100 |  502 |
+-------+-----------+------+------+
7 rows in set (0.01 sec)
mysql> select * from menu;                                  
+----+--------------+--------+
| id | label        | parent |
+----+--------------+--------+
|  1 | services     |      0 |
|  2 | company      |      0 |
|  3 | media center |      0 |
|  4 | your account |      0 |
|  5 | community    |      0 |
|  6 | content pub  |      1 |
|  7 | business pub |      1 |
|  8 | xx pub       |      2 |
|  9 | xttx pub     |      2 |
| 10 | adxttx pub   |      2 |
| 11 | adxtdb       |      3 |
| 12 | add xtdb     |      3 |
| 13 | pp tdb       |      4 |
| 14 | 5sx tdb      |      5 |
| 15 | 5sx tdb      |      5 |
| 16 | dd5sx tdb    |     15 |
| 17 | dd5sx tdb    |     15 |
+----+--------------+--------+
17 rows in set (0.00 sec)
#Self-connect
mysql> select a.label as parent_label,b.label as child_label from menu as a,menu as b where a.id=b.parent;
+--------------+--------------+
| parent_label | child_label  |
+--------------+--------------+
| services     | content pub  |
| services     | business pub |
| company      | xx pub       |
| company      | xttx pub     |
| company      | adxttx pub   |
| media center | adxtdb       |
| media center | add xtdb     |
| your account | pp tdb       |
| community    | 5sx tdb      |
| community    | 5sx tdb      |
| 5sx tdb      | dd5sx tdb    |
| 5sx tdb      | dd5sx tdb    |
+--------------+--------------+
12 rows in set (0.00 sec)
 
#union/union all
mysql> select gid from grps union select gid from ugs;
+------+
| gid  |
+------+
|  501 |
|  502 |
|  503 |
|  504 |
+------+
4 rows in set (0.00 sec)
mysql> select gid from grps union all select gid from ugs;
+------+
| gid  |
+------+
|  501 |
|  502 |
|  503 |
|  504 |
|  502 |
|  502 |
|  503 |
|  501 |
|  501 |
|  501 |
|  501 |
|  502 |
|  502 |
+------+
13 rows in set (0.00 sec)