mysql导入连接_Mysql基础含导入导出及连接学习实录

最新推荐文章于 2024-02-17 18:52:41 发布

菜小倪

最新推荐文章于 2024-02-17 18:52:41 发布

阅读量78

点赞数

文章标签： mysql导入连接

本文链接：https://blog.csdn.net/weixin_32286189/article/details/113289330

版权

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;

+-------+--------+------+-----+---------+----------------+

+-------+--------+------+-----+---------+----------------+

| cid | int(3) | NO | PRI | NULL | auto_increment |

+-------+--------+------+-----+---------+----------------+

2 rows in set (0.01 sec)

mysql> desc topic;

+-------+--------+------+-----+---------+----------------+

+-------+--------+------+-----+---------+----------------+

| tid | int(3) | NO | PRI | NULL | auto_increment |

| 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 |

| 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 |

| 503 |

| 501 |

| 502 |

+------+

13 rows in set (0.00 sec)

菜小倪

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql导入连接_Mysql基础含导入导出及连接学习实录

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 ' enclo...
复制链接

扫一扫