精品专区-精品自拍9-精品自拍三级乱伦-精品自拍视频-精品自拍视频曝光-精品自拍小视频

網站建設資訊

NEWS

網站建設資訊

MySQL數據導入導出（用于個人學習與回顧）

該實例將練習MySQL的導入導出操作

創新互聯公司一直在為企業提供服務，多年的磨煉，使我們在創意設計，網絡營銷推廣到技術研發擁有了開發經驗。我們擅長傾聽企業需求，挖掘用戶對產品需求服務價值，為企業制作有用的創意設計體驗。核心團隊擁有超過10多年以上行業經驗，涵蓋創意，策化，開發等專業領域，公司涉及領域有基礎互聯網服務多線服務器托管、成都App定制開發、手機移動建站、網頁設計、網絡整合營銷。

實例：

  1. 將/etc/passwd文件導入userdb庫user表并給每條記錄加編號

  2. 將userdb庫user表中UID小于100的前10條記錄導出，存為/mydata/user1.txt文件

  • 新建userdb庫，切換到userdb庫，并且設置如下字段；

mysql>?create?database?userdb;
Query?OK,?1?row?affected?(0.00?sec)
mysql>?use?userdb;
Database?changed
mysql>?create?table?user(
????->?username?varchar(24)?not?null,
????->?password?varchar(48)?default?'x',
????->?uid?int(5)?not?null,
????->?gid?int(5)?not?null,
????->?fullname?varchar(48),
????->?homedir?varchar(64)?not?null,
????->?shell?varchar(24)?not?null
????->?);
Query?OK,?0?rows?affected?(0.01?sec)
mysql>?desc?user;
+----------+-------------+------+-----+---------+-------+
|?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra?|
+----------+-------------+------+-----+---------+-------+
|?username?|?varchar(24)?|?NO???|?????|?NULL????|???????|
|?password?|?varchar(48)?|?YES??|?????|?x???????|???????|
|?uid??????|?int(5)??????|?NO???|?????|?NULL????|???????|
|?gid??????|?int(5)??????|?NO???|?????|?NULL????|???????|
|?fullname?|?varchar(48)?|?YES??|?????|?NULL????|???????|
|?homedir??|?varchar(64)?|?NO???|?????|?NULL????|???????|
|?shell????|?varchar(24)?|?NO???|?????|?NULL????|???????|
+----------+-------------+------+-----+---------+-------+
7?rows?in?set?(0.01?sec)

  • 注：在MySQL 5.7.6版本之后，導入文件只能在secure_file_priv指定的文件夾下。如果直接導入會報錯。執行show variables like '%secure%'命令顯示文件目錄：

mysql>?load?data?infile?'/etc/passwd'?into?table?user?fields?terminated?by?':';
ERROR?1290?(HY000):?The?MySQL?server?is?running?with?the?--secure-file-priv?option?so?it?cannot?execute?this?statement
mysql>?show?variables?like?'%secure%';
+--------------------------+-----------------------+
|?Variable_name????????????|?Value?????????????????|
+--------------------------+-----------------------+
|?require_secure_transport?|?OFF???????????????????|
|?secure_auth??????????????|?ON????????????????????|
|?secure_file_priv?????????|?/var/lib/mysql-files/?|
+--------------------------+-----------------------+
3?rows?in?set?(0.00?sec)

  • ????執行導入操作

-將/etc/passwd文件復制到/var/lib/mysql-files/目錄下，

-讀取/var/lib/mysql-files/passwd文件內容，以“:”為分隔，導入到user表中：

[root@host50?~]#cp?/etc/passwd?/var/lib/mysql-files/
mysql>?LOAD?DATA?INFILE?'/var/lib/mysql-files/passwd'
->?INTO?TABLE?user
->?FIELDS?TERMINATED?BY?':';
Query?OK,?42?rows?affected?(0.11?sec)
Records:?42??Deleted:?0??Skipped:?0??Warnings:?0

注：上述操作中省略了行分隔 LINES TERMINATED BY '\n'，因為這是默認的情況（每行一條原始記錄），除非需要以其他字符分割行，才需要用到這個。

  • 確認導入結果

mysql>?select?count(*)?from?user;
+----------+
|?count(*)?|
+----------+
|???????42?|
+----------+
1?row?in?set?(0.00?sec)

mysql>?select?*?from?user?limit?10;
+----------+----------+-----+-----+----------+-----------------+----------------+
|?username?|?password?|?uid?|?gid?|?fullname?|?homedir?????????|?shell??????????|
+----------+----------+-----+-----+----------+-----------------+----------------+
|?root?????|?x????????|???0?|???0?|?root?????|?/root???????????|?/bin/bash??????|
|?bin??????|?x????????|???1?|???1?|?bin??????|?/bin????????????|?/sbin/nologin??|
|?daemon???|?x????????|???2?|???2?|?daemon???|?/sbin???????????|?/sbin/nologin??|
|?adm??????|?x????????|???3?|???4?|?adm??????|?/var/adm????????|?/sbin/nologin??|
|?lp???????|?x????????|???4?|???7?|?lp???????|?/var/spool/lpd??|?/sbin/nologin??|
|?sync?????|?x????????|???5?|???0?|?sync?????|?/sbin???????????|?/bin/sync??????|
|?shutdown?|?x????????|???6?|???0?|?shutdown?|?/sbin???????????|?/sbin/shutdown?|
|?halt?????|?x????????|???7?|???0?|?halt?????|?/sbin???????????|?/sbin/halt?????|
|?mail?????|?x????????|???8?|??12?|?mail?????|?/var/spool/mail?|?/sbin/nologin??|
|?operator?|?x????????|??11?|???0?|?operator?|?/root???????????|?/sbin/nologin??|
+----------+----------+-----+-----+----------+-----------------+----------------+
10?rows?in?set?(0.00?sec)

  • 為user表中的每條記錄添加自動編號，并驗證自動編寫結果

mysql>?alter?table?user?add?sn?int(4)?auto_increment?primary?key?first;
Query?OK,?0?rows?affected?(0.03?sec)
Records:?0??Duplicates:?0??Warnings:?0

mysql>?select?*?from?user?limit?10;
+----+----------+----------+-----+-----+----------+-----------------+----------------+
|?sn?|?username?|?password?|?uid?|?gid?|?fullname?|?homedir?????????|?shell??????????|
+----+----------+----------+-----+-----+----------+-----------------+----------------+
|??1?|?root?????|?x????????|???0?|???0?|?root?????|?/root???????????|?/bin/bash??????|
|??2?|?bin??????|?x????????|???1?|???1?|?bin??????|?/bin????????????|?/sbin/nologin??|
|??3?|?daemon???|?x????????|???2?|???2?|?daemon???|?/sbin???????????|?/sbin/nologin??|
|??4?|?adm??????|?x????????|???3?|???4?|?adm??????|?/var/adm????????|?/sbin/nologin??|
|??5?|?lp???????|?x????????|???4?|???7?|?lp???????|?/var/spool/lpd??|?/sbin/nologin??|
|??6?|?sync?????|?x????????|???5?|???0?|?sync?????|?/sbin???????????|?/bin/sync??????|
|??7?|?shutdown?|?x????????|???6?|???0?|?shutdown?|?/sbin???????????|?/sbin/shutdown?|
|??8?|?halt?????|?x????????|???7?|???0?|?halt?????|?/sbin???????????|?/sbin/halt?????|
|??9?|?mail?????|?x????????|???8?|??12?|?mail?????|?/var/spool/mail?|?/sbin/nologin??|
|?10?|?operator?|?x????????|??11?|???0?|?operator?|?/root???????????|?/sbin/nologin??|
+----+----------+----------+-----+-----+----------+-----------------+----------------+
10?rows?in?set?(0.00?sec)

  • 從MySQL數據庫中導出查詢結果

? ? ? ? ?-以將userdb庫user表中UID小于100的前10條記錄導出為/myload/user2.txt文件為例

? ? ? ?首先，修改配置文件中存放導出導入目錄及查看修改結果

[root@host50?~]#?mkdir??/myload??;??chown??mysql??/myload
[root@host50?~]#?vim??/etc/my.cnf
[mysqld]
secure_file_priv="/myload"
[root@dbsvr1?~]#?systemctl??restart?mysqld
mysql>?show?variables?like?"secure_file_priv";
+------------------+----------+
|?Variable_name????|?Value???????|
+------------------+----------+
|?secure_file_priv???|?/myload/?|

? ? ? 導出user表中UID小于100的前十條記錄

mysql>?select?*?from?userdb.user?where?uid<100
????->?into?outfile?'/myload/user.txt'
????->?fields?terminated?by?":";
Query?OK,?26?rows?affected?(0.00?sec)

? ? ? 確認導出結果

[root@host50?~]#?wc?-l?/myload/user.txt
26?/myload/user.txt
[root@host50?~]#?tail?/myload/user.txt?
25:tss:x:59:59:Account?used?by?the?trousers?package?to?sandbox?the?tcsd?daemon:/dev/null:/sbin/nologin
29:rpcuser:x:29:29:RPC?Service?User:/var/lib/nfs:/sbin/nologin
33:gdm:x:42:42::/var/lib/gdm:/sbin/nologin
35:sshd:x:74:74:Privilege-separated?SSH:/var/empty/sshd:/sbin/nologin
36:avahi:x:70:70:Avahi?mDNS/DNS-SD?Stack:/var/run/avahi-daemon:/sbin/nologin
37:postfix:x:89:89::/var/spool/postfix:/sbin/nologin
38:ntp:x:38:38::/etc/ntp:/sbin/nologin
39:tcpdump:x:72:72::/:/sbin/nologin
41:apache:x:48:48:Apache:/usr/share/httpd:/sbin/nologin
42:mysql:x:27:27:MySQL?Server:/var/lib/mysql:/bin/false


網站名稱：MySQL數據導入導出（用于個人學習與回顧）
文章分享：http://m.jcarcd.cn/article/goedeh.html
主站蜘蛛池模板： 国产黄在线视频免费 | 无码精品人妻一区二区成人 | 精品国产午夜肉伦 | 欧美日韩不卡在线 | 国产日韩欧美综合 | 欧美亚洲视频 | 日韩午夜成人影院 | 国产综合第一页 | 国产极品美 | 欧美一区二区不卡高 | 欧美日韩国产第一页 | 成人免费黄 | 无码av永久免费专区无毒 | 爱福利导航| 国产片在 | 国产日韩成 | 国产免费a视频 | 潘甜甜国产福 | 成人三级网站精品 | 国产乱视频伦在线 | 日韩视频中文字暮 | 91小视频在线观看 | 96国产| 日本中文字幕 | 日韩在线观看福利片 | 欧美亚洲图片日韩 | 国产精选在线播 | 91免费国产在线 | 三区视频在线 | 成人精品九九视频 | 国内精品自产拍 | 激情欧美日韩一 | 区不卡无毒影院 | 成人视屏一区 | 人善交vi | 成人黄动漫在线观看 | 国产精品自在自线 | 国产又粗又| 国产精品女同 | 欧美性愤潮xxxx | 国产精品天干天 |